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

Mysqldump in MySQL is really a classic and effective tool. We often use it, but we don't know what we are doing in the process of running in the background. In fact, if you want to get these basic information, you don't have to look at the code. We can get a glimpse of it through the log.

If you want to see some of the more detailed logs, just open a general log. As for the performance, don't worry too much, general log we turn it on and off.

There are also some differences among different versions. I chose the version of MySQL 5.7.13 and took a brief look at the log inside.

First of all, I chose RC for the transaction isolation level of this environment.

# mysqladmin var | grep isol

| | tx_isolation | READ-COMMITTED |

When exporting, use the following command to export:

Mysqldump-- single-transaction-- databases mobile_billing > test.sql

The log is as follows, let's read it selectively.

Query / *! 40100 SET @ @ SQL_MODE='' * /

Query / *! 40103 SET TIME_ZONE='+00:00' * /

It should be noted that mysqldump changes the isolation level to RR by default, and then the transaction opened has the consistent snapshot option, which is only valid for the RR isolation level.

Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

Query START TRANSACTION / *! 40100 WITH CONSISTENT SNAPSHOT * /

Because GTID is enabled, this rule will be followed here, and the data exported later will be based on the transaction of this GTID.

Query SELECT @ @ GLOBAL.GTID_EXECUTED

Query UNLOCK TABLES

The following takes into account the differences in storage, such as undo, universal tablespaces, etc., which are temporarily useless in this environment, so the results are all empty.

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 AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE =' DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN ('mobile_billig') GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS INITIAL_SIZE, EXTRA ORDER BY LOGFILE_GROUP_NAME

The scope of influence of the partition is taken into account.

Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (' mobile_billig')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME

Here are the steps to export the data. You will set a savepoint at the beginning, then export the data in the table, rollback to the previous savepoint point after completion, and continue to export the next table until it is finished, savepoint will be released, so that the data obtained is based on the same benchmark.

Query SHOW VARIABLES LIKE 'ndbinfo\ _ version'

Init DB mobile_billing

Get the database building statement, and there is no option to add if not exists by default.

Query SHOW CREATE DATABASE IF NOT EXISTS `mobile_ billing`

Turn on save point

Query SAVEPOINT sp

Get the list of data tables under the specified library

Query show tables

Loop through the list to get the status of the table for further processing.

Query show table status like 'open\ _ sdk\ _ doc\ _ version'

The accepted number of sql_quote_show_create has two values (1, 0). The default is 1, which means that the table name and column name will be wrapped in ``. This server parameter can only be set at the session level and does not support global setting (my.cnf setting is not supported)

Query SET SQL_QUOTE_SHOW_CREATE=1

Query SET SESSION character_set_results = 'binary'

Get the table creation statement, and there is no option to add if not exists by default.

Query show create table `open_sdk_doc_ version`

Set character set

Query SET SESSION character_set_results = 'utf8'

Get field information

Query show fields from `open_sdk_doc_ version`

Query show fields from `open_sdk_doc_ version`

The resulting data will be in the form of insert into, where the role of sql_no_cache is to avoid caching query results

Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `open_sdk_doc_ version`

Query SET SESSION character_set_results = 'binary'

Query show create table `open_sdk_doc_ version`

Query SET SESSION character_set_results = 'utf8'

Query ROLLBACK TO SAVEPOINT sp

. . .

Continue to the next table

Until it is complete, save point will be released

Query ROLLBACK TO SAVEPOINT sp

Query RELEASE SAVEPOINT sp

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.