In order to data security, the database needs regular backup, which we all know, but when the database backup, the most afraid of write operation, because this is the most likely to lead to data inconsistency, Songge cited a simple example we look at:
Suppose that during the database backup, a user placed an order, and the following problems might occur:
- Inventory tables deduct inventory.
- Back up inventory tables.
- Back up order table data.
- Order table adds an order.
- The user table deducts the account balance.
- Back up the user table.
If you follow the logic above, the order table in the backup file is missing one record. If you use this backup file to recover data in the future, one record will be missing, causing data inconsistency.
To solve this problem, MySQL provides many solutions, so let’s go through each one and analyze the pros and cons.
1. The full library is read-only
SQL > alter database read only; alter database read only; alter database read only;
show variables like 'read_only';
Copy the code
As you can see, read_only is OFF by default. Let’s change it to ON and execute the following SQL:
set global read_only=1;
Copy the code1 indicates ON, and 0 indicates OFF. The command output is as follows:
This is not valid for the super user, so after setting this, we will exit the session, create a new user without super privileges, log in as the new user, execute an insert SQL, and the result is as follows:
As you can see in this error message, MySQL is read-only (query-only) and cannot execute the current SQL.
Add read-only properties, you don’t have to worry about data inconsistency during backup.
But read_only is usually used to identify whether a MySQL instance is a primary or secondary library:
- Read_only =0: indicates that the instance is the master library. The database administrator, the DBA, may periodically write business-neutral data to the instance to determine whether the primary library is writable and available, which is a common way to detect whether the primary library instance is alive.
- Read_only =1, indicating that the instance is a slave library. Every once in a while, you tend to only read from the slave library, such as SELECT 1; Do this by probing from the vault.
Therefore, the read_only property is not really suitable for backup, and if the read_only property is used to set the entire library to readonly, the database will remain readonly if the client fails. This can result in the entire library being unwritable for a long time, which is a high risk.
Therefore, this scheme is not qualified.
2. Global lock
Global lock, as the name implies, is to lock the entire library, locked up the library can not add, delete, change, can only read.
So let’s see how to use global locking. MySQL provides a method for adding a global read lock by using flush tables with read lock (FTWRL). You can use this command when you need to make the entire library read-only, after which other threads’ add, delete, or modify operations will be blocked.
As you can see from the figure, use Flush tables with read lock; Instructions can lock tables; Use unlock tables; Command to complete the unlock operation (the session will be automatically unlocked when disconnected).
FTWRL is an improvement over the solution in section 1: if the client is abnormally disconnected after executing the FTWRL command, MySQL automatically releases the global lock and the library returns to a state that can be updated normally, rather than being read-only.
But!!
A global lock means that the entire database is read-only for the duration of the backup, and that services will be shut down for the duration of the backup.
So it’s not the best solution.
3. The transaction
REPEATABLE READ (REPEATABLE READ) is one of the four isolation levels of the database, which is also the default isolation level of MySQL.
At this isolation level, if the user executes the same SELECT statement several times in another transaction, the result is always the same. (Because changes to the data generated by an ongoing transaction cannot be seen externally).
In InnoDB, in other words, the storage engine supports transactions, then we can in the backup database before you open the transaction, will first create a consistent view at this time, and then the whole transaction execution are in conformance with this view, but also because of the support MVCC, backup during the business still can update operations on data, And these update operations are not seen by the current transaction.
At the repeatable read isolation level, even if other transactions update the table data, it does not affect the transaction read results of the backup database. This is the isolation of the four features of transactions, so that the data backed up during the backup is always the data when the transaction was started.
Mysqldump = mysqlDump = mysqlDump = mysqlDump = mysqldump
To see the – single-transaction parameter in action, we can start by turning on general_log, which is the General Query Log that records MySQL server operations. When the client connects, disconnects, or receives SQL statements from the client, logs are written to the general_log. Enabling the general_log may cause performance loss. However, enabling the log in the development and test environment can help speed up troubleshooting of problems.
As you can see from the following query, general_log is disabled by default:
You can modify the configuration file my.cnf(Linux)/my.ini (Windows) to add or change the value of general_log (such as the existing configuration item) to 1 under mysqld. The change will take effect after restarting the MySQL service.
You can also enable general log by running set global general_log = ON ON the MySQL terminal without restarting MySQL.
After this function is enabled, the default log directory is the data directory of mysql and the default host name is.log.
Next, we will perform a backup without the — single-transaction parameter as follows:
mysqldump -h localhost -uroot -p123 test08 > test08.sql
Copy the code
Notice the default general_log location.
Let’s add the single transaction parameter:
mysqldump -h localhost -uroot -p123 --single-transaction test08 > test08.sql
Copy the code
If you look at the blue part of the log, you can see that the transaction is actually started first and then the backup is started, compared to the part of the log without the — single-transaction parameter.
4. Summary
In summary, transaction backup seems to be a good option, but there is a limitation to this solution. It only works with transactional engines like InnoDB. For storage engines like MyISAM, if you want to backup, you should use global locks.