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

MySQL logical backup mysqldump is a common backup method, so what is the backup principle of myqldump? How do you ensure the consistency of backup data?

In order to see what mysql does when logically backing up mysqldump, we open the full log!

Then we started our backup operation: mysqldump-uroot-p123456-- master-data=2-- single-transaction-A > / tmp/yh2.sql

Here are two important parameters:

-- single-transaction

When the dump operation is started, a snapshot is created, which is equivalent to the current consistency view, that is, it is treated as a transaction throughout the dump process, which ensures that the data read under the same transaction is consistent. However, if there are other sessions doing DDL operations (ALTER/DROP/RENAME/TRUNCATE TABLE) at this time, it will break the consistency of the data, so you need to add a lock-tables lock table to ensure the consistency of the data.

-- master-data

This command is to get the binlog file name and position location of the master at the time of dump. When it is equal to 1, the output of change master is displayed. When equal to 2, comment out the output of this command. From this we can know that when equal to 1 dump out of the data, recovery on the slave is very convenient.

Now let's take a look at the full volume of the log we backed up just now. The daily quality is better. I posted some of the main ones.

2017-12-07T07:32:24.917291Z 40 Connect root@localhost on using Socket

2017-12-07T07:32:24.917690Z 40 Query /! 40100 SET @ @ SQL_MODE='' /

2017-12-07T07:32:24.926840Z 40 Query /! 40103 SET TIME_ZONE='+00:00' /

2017-12-07T07:32:24.927033Z 40 Query FLUSH /! 40101 LOCAL / TABLES

2017-12-07T07:32:24.928911Z 40 Query FLUSH TABLES WITH READ LOCK

2017-12-07T07:32:24.928994Z 40 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

2017-12-07T07:32:24.929079Z 40 Query START TRANSACTION /! 40100 WITH CONSISTENT SNAPSHOT /

2017-12-07T07:32:24.929252Z 40 Query SHOW VARIABLES LIKE 'gtid_mode'

2017-12-07T07:32:24.992104Z 40 Query SELECT @ @ GLOBAL.GTID_EXECUTED

2017-12-07T07:32:24.992528Z 40 Query SHOW MASTER STATUS

2017-12-07T07:32:24.992613Z 40 Query UNLOCK TABLES

2017-12-07T07:32:24.992735Z 40 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME

2017-12-07T07:32:24.995255Z 40 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

2017-12-07T07:32:24.996104Z 40 Query SHOW DATABASES

2017-12-07T07:32:24.997945Z 40 Query SHOW VARIABLES LIKE 'ndbinfo_version'

2017-12-07T07:32:24.999984Z 40 Init DB mysql

2017-12-07T07:32:25.000096Z 40 Query SHOW CREATE DATABASE IF NOT EXISTS mysql

2017-12-07T07:32:25.000211Z 40 Query SAVEPOINT sp

2017-12-07T07:32:25.000314Z 40 Query show tables

Omit...

2017-12-07T07:32:25.142111Z 40 Query use yhte

2017-12-07T07:32:25.142187Z 40 Query select @ @ collation_database

2017-12-07T07:32:25.142272Z 40 Query SHOW TRIGGERS LIKE 't'

2017-12-07T07:32:25.142579Z 40 Query SET SESSION character_set_results = 'utf8'

2017-12-07T07:32:25.142651Z 40 Query ROLLBACK TO SAVEPOINT sp

2017-12-07T07:32:25.142714Z 40 Query RELEASE SAVEPOINT sp

2017-12-07T07:32:25.142775Z 40 Init DB yhtest

2017-12-07T07:32:25.142830Z 40 Query SHOW CREATE DATABASE IF NOT EXISTS yhtest

2017-12-07T07:32:25.142991Z 40 Query SAVEPOINT sp

2017-12-07T07:32:25.143060Z 40 Query show tables

2017-12-07T07:32:25.143298Z 40 Query show table status like 't1'

2017-12-07T07:32:25.143799Z 40 Query SET SQL_QUOTE_SHOW_CREATE=1

2017-12-07T07:32:25.143872Z 40 Query SET SESSION character_set_results = 'binary'

2017-12-07T07:32:25.143972Z 40 Query show create table T1

2017-12-07T07:32:25.144064Z 40 Query SET SESSION character_set_results = 'utf8'

2017-12-07T07:32:25.144154Z 40 Query show fields from T1

2017-12-07T07:32:25.144543Z 40 Query show fields from T1

2017-12-07T07:32:25.144951Z 40 Query SELECT /! 40001 SQL_NO_CACHE / FROM T1

2017-12-07T07:32:25.145135Z 40 Query SET SESSION character_set_results = 'binary'

2017-12-07T07:32:25.145207Z 40 Query use yhtest

2017-12-07T07:32:25.145282Z 40 Query select @ @ collation_database

2017-12-07T07:32:25.145366Z 40 Query SHOW TRIGGERS LIKE 't1'

2017-12-07T07:32:25.145668Z 40 Query SET SESSION character_set_results = 'utf8'

2017-12-07T07:32:25.145740Z 40 Query ROLLBACK TO SAVEPOINT sp

2017-12-07T07:32:25.145813Z 40 Query show table status like 't122'

2017-12-07T07:32:25.146389Z 40 Query SET SQL_QUOTE_SHOW_CREATE=1

2017-12-07T07:32:25.146464Z 40 Query SET SESSION character_set_results = 'binary'

2017-12-07T07:32:25.146533Z 40 Query show create table T122

2017-12-07T07:32:25.146621Z 40 Query SET SESSION character_set_results = 'utf8'

2017-12-07T07:32:25.146702Z 40 Query show fields from T122

2017-12-07T07:32:25.147099Z 40 Query show fields from T122

2017-12-07T07:32:25.147395Z 40 Query SELECT /! 40001 SQL_NO_CACHE / FROM T122

2017-12-07T07:32:25.147545Z 40 Query SET SESSION character_set_results = 'binary'

2017-12-07T07:32:25.147615Z 40 Query use yhtest

2017-12-07T07:32:25.147690Z 40 Query select @ @ collation_database

2017-12-07T07:32:25.147771Z 40 Query SHOW TRIGGERS LIKE 't122'

2017-12-07T07:32:25.148128Z 40 Query SET SESSION character_set_results = 'utf8'

2017-12-07T07:32:25.148201Z 40 Query ROLLBACK TO SAVEPOINT sp

2017-12-07T07:32:25.148273Z 40 Query show table status like 'yh2'

2017-12-07T07:32:25.149056Z 40 Query SET SQL_QUOTE_SHOW_CREATE=1

2017-12-07T07:32:25.149129Z 40 Query SET SESSION character_set_results = 'binary'

2017-12-07T07:32:25.149199Z 40 Query show create table yh2

2017-12-07T07:32:25.149582Z 40 Query SET SESSION character_set_results = 'utf8'

2017-12-07T07:32:25.149665Z 40 Query show fields from yh2

2017-12-07T07:32:25.150010Z 40 Query show fields from yh2

2017-12-07T07:32:25.150293Z 40 Query SELECT /! 40001 SQL_NO_CACHE / * FROM yh2

2017-12-07T07:32:25.150443Z 40 Query SET SESSION character_set_results = 'binary'

2017-12-07T07:32:25.150513Z 40 Query use yhtest

2017-12-07T07:32:25.150587Z 40 Query select @ @ collation_database

2017-12-07T07:32:25.150668Z 40 Query SHOW TRIGGERS LIKE 'yh2'

2017-12-07T07:32:25.151022Z 40 Query SET SESSION character_set_results = 'utf8'

2017-12-07T07:32:25.151096Z 40 Query ROLLBACK TO SAVEPOINT sp

2017-12-07T07:32:25.151158Z 40 Query RELEASE SAVEPOINT sp

2017-12-07T07:32:25.158814Z 40 Quit

Through the full log, we can roughly see the execution process of mysqldump.

1 you can see that the dump command link officially enters the database.

2 flushtables operation. This operation synchronizes the in-memory cached table structure data to disk.

3 do the FLUSH TABLES WITH READ LOCK operation to obtain a global lock to ensure that the data is consistent at this time.

4 restore the transaction isolation level of the current session to the default RR mode, making the current transaction readable repeatedly.

5 start a transaction and set it to the snapshot level.

6. Query whether the GTID of the database is enabled.

7 get the file name of binlog and the location of position.

8 release the global lock.

9 query the status of the test1 library through the select statement.

10 look up the dictionary.

11 enter the test1 library to be backed up.

12 View the database building statement.

13 create a transaction recovery point sp.

14 check which tables are in the libraries that need to be backed up.

15 View the status of table T1.

16 format table and column names.

17 sets the character set to binary.

18 View the table creation statement of T1.

19 sets the character set to UTF8.

20 output all the information of the table.

21 query the data in T1. If the data in the table is very large, mysql will use limit to obtain it in segments.

Set the character set to binary.

23 enter the test1 library.

24 View the encoding format of the database.

25 View the triggers of the T1 table.

Set the character set to UTF8.

27ROLLBAKC to sp transaction point.

28 the same operation gets the T2 table data in the test1 library.

Release the transaction rollback point sp when all the data has been obtained.

Analyze the mysqldump process:

FLUSH /! 40101 LOCAL / TABLES

Closes all open tables, forces all tables in use to be closed, and flushes the query cache.

FLUSH TABLES WITH READ LOCK

Perform the flush tables operation, and add a global read lock, many children's shoes may wonder, these two commands seem to repeat, why not add a lock when performing the flush tables operation for the first time?

Let's look at the explanation in the source code:

/

We do first a FLUSH TABLES. If a long update is running, the FLUSH TABLES

Will wait but will not stall the whole mysqld, and when the long update is

Done the FLUSH TABLES WITH READ LOCK will start and succeed quickly. So

FLUSH TABLES is to lower the probability of a stage where both mysqldump

And most client connections are stalled. Of course, if a second long

Update starts between the two FLUSHes, we have that bad stall.

/

In short, it is to prevent long transaction operations from causing FLUSH TABLES WITH READ LOCK operations to be locked, but at the same time blocking other client operations.

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

Set the transaction isolation level for the current session to RR,RR to avoid unrepeatable reads and phantom reads.

START TRANSACTION /! 40100 WITH CONSISTENT SNAPSHOT /

Take a snapshot of the current database, which is determined by single-transaction, which is in mysqldump. Let's compare the difference between adding this parameter and not adding it. The log is as follows:

There are-- single-transaction parameters:

None-- single-transaction parameter:

As you can see, when we do not add the parameter-- single-transaction parameter, there will be less setting of the interval level, less opening of consistent snapshots of things, and less unlock tables.

SHOW MASTER STATUS

This is determined by-- master-data, which records the status information of binlog at the start of the backup, including MASTER_LOG_FILE and MASTER_LOG_POS

UNLOCK TABLES releases the lock.

Another operation during the backup is to set the SavePoint. In fact, this does not block ddl operations on the backed up tables during the backup because of the metadata lock.

/

ROLLBACK TO SAVEPOINT in-single-transaction mode to release metadata

Lock on table which was already dumped. This allows to avoid blocking

Concurrent DDL on this table without sacrificing correctness, as we

Won't access table second time and dumps created by-single-transaction

Mode have validity point at the start of transaction anyway.

Note that this doesn't make-single-transaction mode with concurrent

DDL safe in general case. It just improves situation for people for whom

It might be working.*

/

Imitate this step and test it.

Test 1:

In isolation level RR mode, open a session An and do the following:

Open a session B and do the following:

Open a session C to view the status, show processlist

We can see that the B session will be blocked by the A session metadata, and at this time, if there are other sessions accessing the T1 table, it will also be blocked by the B session!

Test 2

In session A, do the following:

Execute in B session

We see that the DDL operation of the B session can be performed successfully.

In session A, do the following

Execute in B session

B sessions can also be executed successfully.

Test 3:

A session does the following:

B session execution

At this time, we can see that B session is blocked by A session, and through show processlist;, we can also see that B session is waiting for a metadata lock.

We run rollback to SAVEPOINT sp in A session.

At this time, we saw that the B session was executed successfully.

Setting a SavePoint through SAVEPOINT sp; is equivalent to releasing the metadata lock of the table immediately after the completion of the select, instead of waiting for the session to submit, which can prevent DDL from being unable to obtain the metadata lock for a long time, thus causing other query operations of the table to wait.

From these three tests, we can also see that transactions opened by START TRANSACTION WITH CONSISTENT SNAPSHOT can only be ended through commit or rollback, not ROLLBACK TO SAVEPOINT sp.

It should be noted that if alter table occurs before select from T1, that is, the second test case, DDL can be submitted successfully, then we will report an error when we do the select from T1 query.

If it happens in the backup, it will also report an error!

Summary:

At this point, we can summarize the backup principle of mysqldump: after the database global lock is obtained by setting READ LOCK, the current log file name and log location position are recorded under the RR transaction isolation level, and then the global lock is released. Next, create a rollback point of the transaction, and all the data obtained is the sp rollback point data. Finally, release the rollback point sp. Of course, for the MyISAM storage engine, backups lock the entire table directly.

It is worth noting: from the process of analyzing mysqldump, we can know that this command will bring instantaneous FLUSHTABLES WITH READ LOCK to the database at the beginning. Although the locking time is very short, it will bring great hidden trouble to the database, because in the process, if there is a DDL statement executed, it will cause the command to block up and eventually exit abnormally. Therefore, in the choice of backup time node, we need to choose the backup when the load pressure is the least and there is no above operation according to the database environment.

The essence of mysqldump is to obtain the data of a table through select * from tab.

Mysqldump is only suitable for business trough. If frequent data operations are performed during backup, Undo tablespaces will become larger and larger. Undo tablespaces are placed in shared tablespaces by default, while ibdata features will not shrink once they are increased.

The efficiency of mysqldump is still relatively low. START TRANSACTION /! 40100 WITH CONSISTENT SNAPSHOT / can only end after all table backups are completed. In fact, a more efficient approach is to submit a table after backup, which can release the space occupied by snapshots of Undo tablespaces as soon as possible. But by doing so, it is not possible to achieve a consistent backup of all tables.

Why there is no commit operation after the backup is completed

/ *

No reason to explicitely COMMIT the transaction, neither to explicitely

UNLOCK TABLES: these will be automatically be done by the server when we

Disconnect now. Saves some code here, some network trips, adds nothing to

Server.

, /

As can be seen from the above screenshot, when we open the first session without committing, the second session dump is in waiting for table flush state, and the third session is in waiting for table flush state when reading the same table as the first session, instead of being blocked by dump. When the third session reads other tables, it can be read normally and will not be blocked by dump.

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.