How does MySQL ensure the consistency of backup data?

Preface

For data security, the database needs to be backed up regularly. Everyone knows this. However, when backing up the database, you are most afraid of write operations, because this is the most likely to cause data inconsistency. Brother Song gives a simple example Let’s take a look at an example:

Assume that during the database backup period, a user places an order, then the following problems may occur:

• Inventory table deduction inventory.

• Back up the inventory table.

• Back up order table data.

• Add orders to the order table.

• The user table deducts the account balance.

• Back up the user table.

If you follow the above logic, the order table in the backup file will be missing a record. If you use this backup file to restore data, one record will be missing, resulting in data inconsistency.

In order to solve this problem, MySQL provides many solutions. Let us explain them one by one and analyze their advantages and disadvantages.

1. Full database read-only

To solve this problem, the easiest way we can think of is to set the database to be read-only during database backup and cannot be written, so that we don’t have to worry about data inconsistency. The method to set the whole database to be read-only is also very simple. First, we execute the following SQL and look at the values ​​of the corresponding variables:

show variables like 'read_only';

You can see that by default, read_only is OFF, that is, closed state. We first change it to ON and execute the following SQL:

set global read_only=1;

1 means ON, 0 means OFF, and the execution result is as follows:

Thisread_only is not valid for the super user, so after the setting is completed, we exit this session, then create a user without super permissions, log in with the new user, log in After success, execute an insert SQL and the result is as follows:

As you can see, this error message says that the current MySQL is read-only (can only query) and cannot Execute the current SQL.

With the read-only attribute, you don’t have to worry about data inconsistency during backup.

Butread_only We usually use it to identify whether a MySQL instance is the master or slave library:

• read_only=0, indicating that the instance is the master library. The database administrator DBA may write some non-business data to the instance every once in a while to determine whether the main library is writable and available. This is a common way to detect whether the main library instance is alive.

• read_only=1, indicating that the instance is a slave library. Usually, when regularly exploring the slave library, only some read operations will be performed, such as executing statements such as "select 1;".

So, the read_only attribute is not suitable for backup, and if the read_only attribute is used, the entire library will be set to readonly After that, if an exception occurs on the client, the database will remain in the readonly state, which will cause the entire library to be in an unwritable state for a long time, which is a high risk.

Therefore this solution is not qualified.

2. Global lock

Global lock, as the name suggests, locks the entire library. The locked library cannot be added, deleted or modified, only read.

Then let’s see how to use global locks. MySQL provides a method to increase the global read lock. The command is flush tables with read lock (FTWRL). When you need to make the entire library in a read-only state, you can use this command, and then operations such as additions, deletions, and modifications by other threads will be blocked.

As you can see from the picture, use the flush tables with read lock; command to lock the table; use unlock tables; command can complete the unlocking operation (it will also be automatically unlocked when the session is disconnected).

Compared with the solution in the first section, FTWRL has made some progress, that is: if the client disconnects abnormally after executing the FTWRL command, MySQL will automatically release the global lock, and the entire library can be updated normally. status instead of remaining in read-only status.

but! ! !

Adding a global lock means that the entire database is in a read-only state during the backup period, so the business can only be stopped during the database backup period.

So this method is not the best solution.

3. Transactions

I don’t know if you friends still remember the isolation level of the database that Brother Song shared with you before. One of the four isolation levels is repeatable read ( REPEATABLE READ), which is also the default isolation level of MySQL.

If a user executes the same SELECT statement multiple times in another transaction under this isolation level, the result will always be the same. (Because the data changes caused by the executing transaction cannot be seen by the outside world).

换言之，在 InnoDB 这种支持事务的存储引擎中，那么我们就可以在备份数据库之前先开启事务，此时会先创建一致性视图，然后整个事务执行期间都在用这个一致性视图，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作，并且这些更新操作不会被当前事务看到。

在可重复读的隔离级别下，即使其他事务更新了表数据，也不会影响备份数据库的事务读取结果，这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

具体操作也很简单，使用 mysqldump 备份数据库的时候，加上 -–single-transaction 参数即可。

为了看到 -–single-transaction 参数的作用，我们可以先开启 general_log，general_log 即 General Query Log，它记录了 MySQL 服务器的操作。当客户端连接、断开连接、接收到客户端的 SQL 语句时，会向 general_log 中写入日志，开启 general_log 会损失一定的性能，但是在开发、测试环境下开启日志，可以帮忙我们加快排查出现的问题。

通过如下查询我们可以看到，默认情况下 general_log 并没有开启：

我们可以通过修改配置文件 my.cnf(Linux)/my.ini（Windows），在 mysqld 下面增加或修改（如已存在配置项）general_log 的值为1，修改后重启 MySQL 服务即可生效。

也可以通过在 MySQL 终端执行 set global general_log = ON 来开启 general log，此方法可以不用重启 MySQL。

开启之后，默认日志的目录是 mysql 的 data 目录，文件名默认为 主机名.log。

接下来，我们先来执行一个不带 -–single-transaction 参数的备份，如下：

mysqldump -h localhost -uroot -p123 test08 > test08.sql

大家注意默认的 general_log 的位置。

接下来我们再来加上 -–single-transaction 参数看看：

mysqldump -h localhost -uroot -p123 --single-transaction test08 > test08.sql

大家看我蓝色选中的部分，可以看到，确实先开启了事务，然后才开始备份的，对比不加 -–single-transaction 参数的日志，多了开启事务这一部分。

The above is the detailed content of How does MySQL ensure the consistency of backup data?. For more information, please follow other related articles on the PHP Chinese website!