Shulou(Shulou.com)06/01 Report--

Walker science and technology Luo Xiaobo

Is there a hole in mysqldump?

As you all know, mysqldump backup can use-- single-transaction +-- master-data two options to perform backup (to be honest, for convenience, I used mysqldudmp remote backup for a long time before), so that during the backup process, you can not only try not to lock the table, but also get the binlog pos location, backup files can be used for data recovery, can also be used to build a backup database. It looks so beautiful, but in fact, you find yourself in the pit by accident.

1.3.1. Kengichi

When using-- single-transaction +-- master-data, the myisam table is continuously inserted and used to build a repository.

First of all, make the data rows of the myisam table more than 100W on the A library.

Library An opens a new ssh session 2. Use the following script to continuously insert the table t_luoxiaobo2 (the table is a myisam table). For the limited space, please go to the following link for notes:

Http://5d096a11.wiz03.com/share/s/1t2mEh0a-kl_2c2NZ33kSiac1Rgvxq1vgkhL21ibWU2cLidk

Library An opens a new ssh session 3 to clear the query log:

Library An is now in ssh session 3, using mysqldump to back up the entire instance

After the backup is complete, Library A stops the continuous counting script in ssh session 2.

Library An is in ssh session 2, check the binlog pos in the backup file

Library An is in ssh session 3. Looking at the query log, you can find that after UNLOCK TABLES, select * … Before the t _ luoxiaobo2 table, there is data inserted into the table:

Now, we use this backup file to build a backup database on library B, and start replication. We can find the following replication error:

From the above results, we can see that the primary key conflicts, that is to say, the data in the backed up table t_luoxiaobo2 is not consistent with the binlog pos point obtained in the backup file. We are now in the B library to query the data in this table that is greater than or equal to the conflicting primary key. From the following results, we can see that if the backup file strictly complies with the consistency requirements, the data in the backup file must be consistent with the binlog pos point, but now The data in the backup file is 5 lines more than the binlog pos points obtained:

Now, let's remove the-- single-transaction option, re-perform the steps above in this section, rebuild the slave library, and see if there are any questions (limited here, the steps are omitted, and only the final result is posted):

From the show slave status output above, we can see that the backup after the-- single-transaction option is removed and used to build the backup library is normal. In addition, if we re-check the query log on the A library, we can also find that only the flush statement is found, but not unlock tables or set session transaction... 、 start transaction.. Statement, indicating that the consistent snapshot transaction is not opened during the backup process, the isolation level is not modified, and the global read lock is added throughout the process. When the mysqldump backup process ends and exits, the mysql server automatically reclaims the lock resources:

You may say, our database environment is very standard, there is no myisam table, there will not be this problem, OK, like one.

1.3.2. Pit two

When using-- single-transaction +-- master-data, the innodb table executes online ddl, and the backup file is used to build the repository (note that the database instance in this section is different from the previous section).

This time we manipulate the Innodb table and first get the data of the t_luoxiaobo table to millions of rows on the A library.

Library An in ssh session 2, use the following script to continuously DDL the table t_luoxiaobo (the table is the innodb table). For the limited space, please go to the following link for notes:

Http://5d096a11.wiz03.com/share/s/1t2mEh0a-kl_2c2NZ33kSiac0tjwkE3KHkhU2_9gwt3mTldI

Library An is in ssh session 3, clear the query log:

Library An is now in ssh session 3, backing up the entire instance using mysqldump:

Library An is in ssh session 2, stop adding scripts to DDL.

Library An is in ssh session 2, view the binlog pos in the backup file:

Now, we use this backup file to build the backup library in the B library and start the replication. You can see from the following results that the replication status is normal:

Now let's go back to the A library and insert some test data into the table t_luoxiaobo:

Query the replication status and data in the table t _ luoxiaobo on the B library:

Everything seems to be all right here, right? Are you happy? Wait a minute, please maintain the fine tradition of DBA, let's use the pt-table-checksum tool on the main library to check:

As can be seen from the above information, the detection DIFFS of table luoxiaobo.t_luoxiaobo is listed as 16, which represents the data difference between master and slave. Don't worry, let's first query the number of rows of this table in the AB library. As can be seen from the following results, the difference between the master and slave data of the table is 2097152 rows!

What happened? You might say, isn't it always like this when you use mysqldump? There's nothing wrong with it.

Recall that from the demonstration process of "the function of the WITH CONSISTENT SNAPSHOT statement" mentioned in our previous article "how much do you know about the backup process of mysqldump and innobackupex (II)", we can see that the load of DDL was deliberately added. I still remember that when we demonstrated the role of mysqldump in using savepoint, after using the start transaction with consistent snapshot statement to explicitly open a transaction, before the transaction executed select, the table could not query data after DDL was executed by other sessions. We know that when mysqldump backs up data, it is under a consistent snapshot transaction opened by the start transaction with consistent snapshot statement that select statements are used to query data for backup.

To confirm this problem, let's open the query log and check the start transaction with consistent snapshot statement and select. Whether there is a DDL statement between, as follows:

Now, when we open the backup file and find the backup statement location of the table t_luoxiaob, we can see that the INSERT statement is not generated:

When you get here, is it a sudden tightening of heartstrings? So... If you decide to continue to use mysqldump, be sure to check the consistency of master and standby data after building the backup database in the future!

1.3.3. Is there any way to improve these problems?

Before looking for a solution, let's take a look at the role and usage limitations of mysqldump's backup options-- single-transaction and-- master-data [= value].

-- single-transaction

* this option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to server to show that a transaction snapshot is opened before backing up the data. It is only applicable to transaction tables such as InnoDB, because the backup is within a transaction snapshot, which makes the backed up data consistent with the data when the transaction snapshot was taken, and does not block any application's access to server.

* in order to ensure a valid backup file (correct table content and binary log location) when performing a single transaction backup, no other connections should be made using DDL statements such as ALTER TABLE,CREATE TABLE,DROP TABLE,RENAME TABLE,TRUNCATE. This can cause the consistent state to be broken, which may result in incorrect query or backup failure when mysqldump executes SELECT retrieval table data.

* Note: this option applies only to transaction engine tables. For MyISAM or MEMORY tables, the data of these engine tables may still change during backup because transactions are not supported.

-- master-data [= value]

* when backing up with this option, a change master to statement is generated in the backup file. The binlog pos is used to back up server's own binlog pos. You can use the backup file to set another server (the server that restored the backup file) as the slave library for backing up server.

* similar to the-- dump-slave option, if the option value is 2, the CHANGE MASTER TO statement is written to the backup file as an SQL comment, so it is for reference only; this comment has no effect when the backup file is reloaded. If the option value is 1, the statement is not commented and takes effect (executed) when the backup file is reloaded. If no option value is specified, the default value is 1.

* the user who specifies this option requires RELOAD permission, and server must enable binary logging because this location is obtained using show master status (if the log_bin parameter is not enabled, the show master status output information is empty), not using show slave status.

The *-- master-data option automatically turns off the-- lock-tables option. The-- lock-all-tables is also opened, unless the-- single-transaction option is specified, and after the-- single-transaction option is specified, the global read lock is added only within the backup start time.

So... -- the single-transaction option makes it clear that if this option is used, if DDL occurs during a backup, the backup data consistency may be broken and the correct content cannot be retrieved by select. In addition, this option applies only to transaction engine tables, not to non-transaction engines. As a DBA, most of the time, it is very helpless. Although there are all kinds of norms, there is a fish out of the net. At this time, life has to go on and the work has to be done well. Is there any way to alleviate this problem? There are:

As in the steps demonstrated above, remove the-single-transaction option for backup. When you use the-- master-data option alone, the-lock-all-tables will be automatically enabled. During the backup process, the entire instance will lock the table, so that the backup data will not be inconsistent with the acquired binlog pos points, so that data conflicts will not occur when the backup is used to build a backup database. But the problem is obvious: the database is not available during the backup, and if you use this approach, you need to back up at least during the business trough.

Use the innobackupex backup tool.

In the next article "how much do you know about the backup process of mysqldump and innobackupex (4)" we will continue to introduce "innobackupex". Please look forward to the wonderful content.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.