Mysqldump principle 3

When the database is operational in the current network, the database should be hot prepared frequently. One way to ensure data integrity and consistency during recovery is to lock the table before the backup, but the lock table can affect the running business.

Mysqldump is the most commonly used backup tool in MySQL, and you can see many options with Mysqldump--help.

After mysqldump start the backup, perform other update database operations, will the results of the mysqldump backup include changes to the database before the end of the backup? In a point-in-time backup, the most desirable result is that the backup results are the database snapshots at the beginning of the backup. By choosing the right options for backup, mysqldump can ensure data consistency without impacting the operation of the business.

Mysqldump The resulting backup, which is ultimately to be restored in conjunction with Binlog. Mysqldump can also get the binlog recovery point accurately.

So how do you ensure data consistency and generate backups in mysqldump? The following is a common example to explain the principle of mysqldump. (Example for InnoDB storage engine only)

Mysqldump–uuser-p--skip-opt-q-R--single-transaction--default-character-set=utf8--master-data=2--create-option- -no-autocommit–s ${sock}-B ${dbname} > Backup.sql

After executing the above command, you will get a backup.sql file that can be used for recovery. In Bauckup.sql, there are a series of CREATE statements and INSERT statements, and the process of recovering is creating the original database and table, and insert all table data directly into the table.

Before executing the mysqldump command, execute set global General_log = on in MySQL to open the universal log, which is turned off by default. The common log records all SQL statement information that the MySQL server responds to, and the mysqldump command actually constructs a backup file Backup.sql by constructing a series of SQL statements and sending it to the database server, using the server's response information.

is part of the general log after executing the above mysqldump command.

The first line, execute connect is through the mysqldump option in the-U,-p,-s for port, user authentication, and then connect to the server.

The flush tables, flush tables with read lock, unlock tables, and the show master status therein are the response options --master-data. Use the show master status to get the Binlog position through an instant lock table. In Backup.sql, you can find information similar to the following.

An instant lock-up table is to ensure that the correct binlog position is obtained. After flush tables, the current database snapshot is what we want to back up and get Binlog location information through show Master status. So how do you ensure that the current database snapshot is backed up? With the option --single-transaction, the mysqldump processing is the start transaction. Because of the MVCC mechanism of InnoDB, start transacion generates a transaction ID that allows the transaction ID to be filtered to update the database after the transaction, resulting in a backup of the current snapshot.

There is a detail to note, flush tables; flush tables with read lock; Why not add read lock directly, what are the benefits of writing this? In fact, this can be as little as possible to reduce the impact of lock, reduce conflict.

Another detail to note is that the start transaction is placed between the flush tables and the unlock tables and cannot be placed either in front or behind. After flush tables, the current database snapshot is what we want to back up, and the show master status gets the Binlog location information. The start transaction is obtained by beginning a transaction, which, if placed in front or behind, can result in data loss or repeated insertion of data.

In addition, there are a lot of general log information, come to the current snapshot of the database, tables, stored procedures and data, and so on. The following is an example of only part of a T1 table in the test library.

Show CREATE database if not EXISTS ' test '; The server response statement gets the creation statement of the test library

CREATE DATABASE/*!32312 IF not exists*/' test ' ;

Plus if not exists guarantees that the test Coucay creation test does not exist at create.

Show Tables gets all the tables in the test library.

The Show table status like ' T1 ' obtains state information for T1 tables for further processing.

Show CREATE table ' T1 ' generates a T1 table creation statement.

SELECT/*!40001 SQL_NO_CAC HE */* from ' T1; The statement gets all the data from the table T1, and the INSERT statement is generated in Backup.sql, which executes the insert operation when the data is restored. The role of Sql_no_cache is to avoid the query result cache (not the query results in the cache).

In the general log, you can also see a series of statements sent to the server after mysqldump processing, and then mysqldump the results from the server to be processed to get the backup file Backup.sql.

Mysqldump principle 3