Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to ensure the consistency of backup data in MySQL". The explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "MySQL how to ensure the consistency of backup data".

Preface

For the sake of data security, the database needs to be backed up regularly, which we all know. However, when the database is backed up, we are most afraid of writing operations, because this is the most likely to lead to data inconsistencies. Brother Song, let's give you a simple example:

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

Inventory table deduction inventory.

Back up the inventory table.

Back up the order table data.

Order table adds an order.

The account balance is deducted from the user table.

Back up the user table.

If you follow the logic above, there is one less record in the order table in the backup file. If you use this backup file to recover data in the future, one record will be missing, resulting in data inconsistency.

In order to solve this problem, many solutions are provided in MySQL, and we will explain them one by one and analyze their advantages and disadvantages.

1. Full library read-only

To solve this problem, the easiest way to solve this problem is to set the database to read-only and not write during database backup, so that you do not have to worry about data inconsistency, and the method of setting read-only for the whole database is also very simple. First, let's execute the following SQL to look at the values of the corresponding variables:

Show variables like 'read_only'

As you can see, by default, read_only is OFF, that is, the closed state. Let's change it to ON and execute the following SQL:

Set global read_only=1

1: ON,0 means OFF. The execution result is as follows:

This read_only is not valid for super users, so after the setup is complete, we exit the session, create a user without super permission, log in with the new user, and execute an insert SQL after the login is successful. The result is as follows:

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

By adding a read-only attribute, you don't have to worry about data inconsistencies during backup.

But read_only is usually used to identify whether a MySQL instance is a master or a slave:

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

Read_only=1, indicating that the instance is a slave library. Every once in a while, only slave libraries are read, such as select 1; in this way, slave libraries are probed.

Therefore, the attribute read_only is not suitable for backup, and if you use the read_only attribute to set the entire library to readonly, if an exception occurs on the client, the database will always remain in the state of readonly, which will cause the entire library to be unwritable for a long time, and the risk is very high.

Therefore, this scheme is not up to standard.

two。 Global lock

Global lock, as the name implies, is to lock up the entire library, the locked library can not be added, deleted or changed, can only be read.

So let's see how to use global locks. MySQL provides a way to add a global read lock, and the command is flush tables with read lock (FTWRL). You can use this command when you need to make the entire library read-only, and then operations such as additions, deletions, deletions and modifications of other threads will be blocked.

As you can see from the figure, the table can be locked using the flush tables with read lock; instruction; the unlocking operation can be completed with the unlock tables; instruction (and it will be unlocked automatically when the session is disconnected).

Compared with the scheme in the first section, FTWRL has a little improvement, that is, if the client disconnects abnormally after executing the FTWRL command, MySQL will automatically release the global lock, and the whole library will return to a state that can be updated normally, instead of being read-only all the time.

But!

The addition of a global lock means that the entire database is read-only during the backup, so the business can only be shut down during the database backup.

So this approach is not the best solution.

3. Business

I don't know if my friends still remember the isolation level of the database that Brother Song shared with you. One of the four isolation levels is REPEATABLE READ, which is also the default isolation level of MySQL.

At this isolation level, if a user executes the same SELECT statement several times in another transaction, the result is always the same. (because the data changes generated by the transaction being executed cannot be seen externally).

In other words, in a transactional storage engine like InnoDB, we can open the transaction before backing up the database. At this time, a consistency view is created, and then the consistency view is used throughout the transaction execution. Moreover, due to the support of MVCC, the business can still update the data during the backup, and these update operations will not be seen by the current transaction.

Under the repeatable isolation level, even if other transactions update the table data, it will not affect the transaction read results of the backup database, which is the isolation of the four major features of transactions. in this way, the data backed up during the backup is always the data when the transaction is started.

The specific operation is also very simple, when using mysqldump to back up the database, add the-- single-transaction parameter.

To see the effect of the-- single-transaction parameter, we can first turn on general_log,general_log, or General Query Log, which records the operation of the MySQL server. When the client connects, disconnects and receives SQL statements from the client, logs will be written to general_log. Enabling general_log will lose some performance, but opening logs in development and test environments can help us speed up troubleshooting.

We can see that general_log is not enabled by default through the following query:

By modifying the configuration file my.cnf (Linux) / my.ini (Windows), we can add or modify (if the configuration item already exists) the value of general_log to 1 under mysqld, and restart the MySQL service after modification.

You can also open general log by executing set global general_log = ON on the MySQL terminal, which does not have to restart MySQL.

When enabled, the default log directory is mysql's data directory, and the file name defaults to the hostname .log.

Next, let's perform a backup without the-- single-transaction parameter, as follows:

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

Notice the location of the default general_log.

Next, let's add-- single-transaction parameter to see:

Mysqldump-h localhost-uroot-P123-- single-transaction test08 > test08.sql

If you look at the part I selected in blue, you can see that the transaction is indeed opened first and then the backup starts. Compared with the log without the-- single-transaction parameter, there is more open transaction part.

Thank you for your reading, the above is the content of "how to ensure the consistency of backup data in MySQL". After the study of this article, I believe you have a deeper understanding of how to ensure the consistency of backup data in MySQL. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.