MySQL Restore backup reading notes

1.
Any execution that is longer than the wait_timeout or Interactive_timeout option is worth backing up, causing the session to be closed, which also implicitly executes the unlock tables command.
2.
A common drawback to the backup strategy using flush TABLES with READ Lock is that they require two separate threads to complete the backup process. Run the flush TABLES with the READ lock command,
Then exiting from the current connection will automatically execute a unlock tables command. After a successful return from flush TABLES with READ Lock, any backup options must be executed in a different concurrent thread, only
Unlock TABLES can only be performed when the applicable backup option is complete.
3.
The risk of using the flush TABLES with READ Lock command in a high concurrency system is likely to take a long time because there are other lengthy statements that need to be executed, preferably monitored and terminated, for
The influence of the Linetype application is also not negligible.
4.
Common scenarios for MySQL backup:
* File System Cold Backup

|--> Advantages: The process is simple and allows the use of any file system backup tool to perform backups.

|--> Cons: MySQL instances are not available during backup
The recovery process requires a similar system (operating system, directory structure)
When you restart MySQL, the memory pool of the MySQL instance is reinitialized to provide the most worrying performance for SQL statements
* SQL Export (dump)

| The mysqldump command with the--lock tables option locks only one schema at a time, and if the application writes to a different mode and uses a storage engine that does not support transactions, the
Inconsistent data may be generated during the backup process.

|-->, Cons: It supports a compatible solution across operating systems (a backup on Linux that can be restored in Windows), which is a static backup (1. Primary binaries, 2. Primary
Binary file location), the data is in ASII format (you can view the backup file with a text editor). is ideal for smaller databases, 5GB-20GB
Between. Not suitable for very time-demanding recoveries (because the operation to recover with mysqldump output is single-threaded), and the mysqldump command uses
MySQL's C/S protocol, which does not necessarily have to be performed on the same server, helps reduce I/O write requirements and disk capacity, but increases command execution time and
Network utilization.
* Table Extraction

|--> asii format Backup is another form of generating a table-by-sheet data file (a table of data files), which is both a snapshot of the data. Snapshots are not available for system-wide, but for
Time series data, write data and archive data at once, especially if the data is partitioned manually, it is ideal.
The mysqldump command has greater flexibility with respect to the underlying file permissions, which are required for the output file.

* File System Hot Snapshot

|--> is not actually a MySQL-specific strategy, but rather a disk-based operating system command that uses Logical Volume Manager (LVM) on a direct-attached drive.

* InnoDB Hot Backup

|--> for a MySQL instance with only InnoDB, there are two products that can be non-blocking hot-backed. MySQL Enterprise Backup (MEB), both InnoDB hot backup, and xtrabackup.
Note: In addition to supporting applications for InnoDB, MySQL meta-mode and MyISAM backups of any other tables are supported, but tables need to be locked.

5.
* Point-in-time recovery Technology (PITR): It is performed by applying MySQL binaries to a recovered snapshot.

* Note: The system administrator uses the operating system command to delete, MySQL binary log file is a possible disaster practice, when deleting binary log files, be sure to use the appropriate MySQL command.

* Disaster Recovery---> Dr

* Average recovery time--->mttr

* Average detection time--->mttr

* Recovery point target---->RPO (organization specified point in time, data must be recovered at this time)

* Recovery time Objective--->rto (the duration of the acceptable recovery condition)

* Service Level agreement--->SLA (includes responsibility for both technical and business decision-making)

6.
Replication limitations of MySQL:
|---> Replication lag (show slave status command determines MySQL replication information including lag information)

|---> Consistency (Percona Toolkit pt_table_checksum Open Source tool for checking data consistency)

|---> Mode consistency, using layman detection mode differences

The MyISAM recovery process is an index rebuild of a given table, which is why a crash report on the MyISAM table is not given at system startup. Instead, the table is accessed through a given index
The reason for the data.

The *myisam-recover configuration option provides some properties that do not cause a crash for the MyISAM table.

*mysql reboot for performance impact--innodb buffer pool, MyISAM main memory buffer, including key cache, is emptied.

* A cold file system copy or file snapshot restore is the installation of all MySQL data and configuration files, which need to be performed when the MySQL installation is not running.

*mysql Enterprise Backup (MEB) Recovery: Recovery with MEB for a static backup is a simple command, but some preliminary preparation must be performed:
* * Stop MySQL instance
Delete any existing data directory
Create a clean Data directory, or grant users permission to create data directories
Run Mysqlbackup Copy-back

* Before performing a xtrabackup restore first to stop MySQL, and to ensure that the existing data directory does exist is empty, he will not check that MySQL is not running/

* Note: Here is a technique for managing binary logs that executes a flush logs command during backup execution, which produces a new binary log file during backup, and makes it easy to determine
The starting position of the binary log that will be used.

7.
MySQL configuration options: Data management: datadir--> default, is the storage directory for all databases, tables, INNODB data, server logs, and binary log files in the system. DataDir in the
The directory represents the database. For Linux, this directory is:/var/lib/mysql

Basedir Basedir--> is the location of the MySQL installation directory in the file system and puts her in path for easy access to MySQL server and client programs.
This directory in Linux is:/usr

Warning: Before you create any database objects, you should set the innodb_file_per_table variable first, there is no way to have a mixed model, and a safe transfer from one system table space
The only way to table space is to dump all the data, deactivate all the objects, and then rebuild the database objects and reload all the data.

In MySQL, in order to reclaim space, the first thing to consider is to delete the binary files, manually delete the problem is that the MySQL file reference is not deleted, the correct way is to use purge master life
This removes the physical file and the internal definition.

* To better manage the system, a well-configured MySQL installation should clearly separate the MySQL data directory, MySQL binary log and MySQL relay log directory.
It is important to test the backup on an external system to ensure that no damage occurs at the time of the backup.

*rds Recovery (Amazon Web Service provides a remote database service for MySQL) is limited by the inability to physically access the database server, although there are API interfaces that can be used to change the settings of MySQL configuration items and check
Look at MySQL's error log, but you can't see the system resources or anything else you're using. (ex: Binary log)

* Reasons for common outages: storage System problems are the primary factor, while operating systems and networks are also the cause of outages, sans and RAID storage systems are not backup solutions.

* When using the pipe command, the first advantage is that the output file is automatically compressed during execution and does not require any additional temporary disk space, which is helpful when disk space is limited, and the performer bar
The command takes extra time to be its disadvantage, used in combination with mysqldump, locks all tables, affects application access/

*linux's nice and Ionice commands can change the priority of work on a system and reduce the system impact of certain commands. When you use a Linux pipeline and a combination of the available charms, you can achieve cross-network
Instead of writing any backup information on the database server. ------by leveraging Netcat (NC), you can pass files directly through TCP/UDP on a given port, which usually requires determining
The destination server is capable of receiving traffic and requires a certain port for additional firewall access.

*MEB does not support connections to remote hosts, although MEB has a--host configuration option that applies only to one scenario: This option exists in a [client] configuration segment and does not produce an error during execution
Message, so the role of this option needs to be analyzed, and remote connections are used when validating the results of the analysis.

* Generate an encrypted mysqldump backup with Ezncrypt.

8.

MySQL in cloud computing:
for the MySQL database solution (except for a MySQL implementation running stock, the rest is running in a virtual environment)
Amazon,hp,google, All provide MySQL cloud deployments based on the use of the core MySQL server.
* Amazon relational database services:
*amazon relational databases Service (RDS):
|--> Benefits: Has the ability to activate automatic monitoring of updates to MySQL software, (Use the--auto-minor-ver
sion-upgrade-true option). You can update or return the actual RDS size
capability without additional work.
|--> Limitations: You cannot directly access the MySQL configuration file (my.cnf) to access the change parameters through the rds-modify-db
-parameter-group command. No user can have super privileges. Cannot access the degree binary
log. The error log cannot be accessed.
*google Cloud SQL:
|-->http://effectivemysql.com/article/setting-up-google-cloud-sql/

*HP Cloud Database as a Service (DBAAs):
|-->http://hpcloud.com
* The use of cloud computing does not mean that a disaster will not occur. The cloud has been able to ensure that the right Dr operations are more prevalent, and that the use of the cloud requires security-related
concerns about accessing user business data. Proper encryption is important.