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

This article mainly introduces the MySQL Data Dumper analysis of the commonly used tools for MySQL database backup, hoping to supplement and update some knowledge for you. If you have other questions to understand, you can continue to follow my updated article in the industry information.

When it comes to MySQL database backup, MySQL Data Dumper (project) is also a commonly used tool, which has two executable programs: mydumper, responsible for exporting data, and myloader, responsible for importing data. Mydumper has more features than mysqldump, which you can experience in the following analysis of the options.

As it is a third-party tool, let's take a look at the installation and the problems that may be encountered.

A. mydumper needs to rely on some development libraries, which can be installed using yum.

Root@db01: ~ # yum install glib* zlib* pcre*-y

b. Add dynamic link libraries needed to connect to MySQL.

Root@db01: ~ # cat / etc/ld.so.conf.d/mysql.conf

/ opt/mysql/lib

Root@db01: ~ # ldconfig

Root@db01: ~ # ldconfig-- print-cache | grep 'mysql'

Libmysqlclient.so.18 (libc6,x86-64) = > / opt/mysql/lib/libmysqlclient.so.18

Root@db01: ~ # ls-1 / opt/mysql/lib/libmysqlclient.so.18

Lrwxrwxrwx 1 rootroot 26 Aug 25 14:21 / opt/mysql/lib/libmysqlclient.so.18-> libmysqlclient_r.so.18.1.0

c. Compile and install.

Root@db01: ~ # cmake-DCMAKE_INSTALL_PREFIX=/usr/local/mydumper

Root@db01: ~ # make install

Add the path to the executable command to the environment variable PATH.

Mysql@db01: ~ $grep 'PATH'. Bash _ profile

PATH=/usr/local/mydumper/bin:/opt/mysql/bin/:$PATH:$HOME/bin

Export PATH

d. Type mydumper enter on the command line and see the return message below. The installation is normal.

Mysql@db01: ~ $mydumper

* * (mydumper:723): CRITICAL * *: Error connecting to database: Access denied foruser 'root'@'localhost' (using password:NO)

Mysql@db01: ~ $myloader

* * (myloader:5288): CRITICAL * *: a directory needs to be specified, see-- help

If the following error occurs, there may be a problem with step b.

Mysql@db01: ~ $mydumper

Mydumper: errorwhile loading shared libraries: libmysqlclient.so.18: cannot open shared objectfile: No such file or directory

The following is the information about the database tables used in the demonstration:

(root@localhost) [(none)] > SELECT table_schema, table_name, engine FROM information_schema.tables WHERE (engine = 'InnoDB' OR engine =' MyISAM') AND table_schema NOT IN ('mysql',' performance_schema', 'information_schema')

+-+

| | table_schema | table_name | engine | |

+-+

| | product | pr1 | MyISAM | |

| | product | pr2 | MyISAM | |

| | product | pr3 | InnoDB | |

| | stage | st1 | InnoDB | |

| | stage | st2 | InnoDB | |

| | test | tb1 | InnoDB | |

| | test | tb2 | InnoDB | |

+-+

7 rows in set (0.01 sec)

Mydumper also has a lot of options, like analyzing mysqldump, divide it into several groups to see the meaning of key options.

Connection Options group

This set of options indicates how to connect to the database.

-h,-- host The host to connect to

-u,-- user Username with privileges to run the dump

-pmam Mustang password User password

-P,-- port TCP/IPport to connect to

-S,-- socket domainsocket file to use for connection

Debug Options group

The group indicates where the log is placed and the level of the log.

-Ljon Murtel logfile Log file name to use, by defaultstdout is used

-vmam Murray verbose Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

Filtering Options group

The group indicates which database objects to back up and what additional processing (compression, segmentation, etc.) to the backup files.

-Bjorty Mustang database Database to dump

-T.mai Mui Mui TablesList Comma delimitedtable list to dump (does not exclude regex option)

-Omam Murtel outputdir Directory to outputfiles to

-default Attempted size ofINSERT statement in bytes, default 1000000

-r,-- rows Try to split tables into chunks ofthis many rows. This option turns off-chunk-filesize

Filesize Split tables into chunks of this output filesize. This value is in MB

-cmam Mustang compress Compress output files

-emam house house, house house, emptyhouse files Build dump files even if no data availablefrom table

-x,-- regex Regular expression for 'db.table'matching

-m _ m _ r _ Q _ no _ schemas Do not dump tableschemas with the data

-dmam, Mustang, Mustco, data Do not dump tabledata

-Garmer Mutti Muttons Dump triggers

-E-- events Dump events

-R,-- routines Dump stored procedures and functions

Transactional Options group

This group is mainly concerned with how to lock a backup. Use this command line to test mydumper-- regex'^ (?! (mysql))'--threads=1 [Option], and combine general log to see how mydumper works.

1. Let's see what happens when there is no option.

Master thread, get GLOBAL READ LOCK, turn on consistency reading, and get the coordinates of the binary log.

1587512Query FLUSH TABLES WITH READ LOCK

1587512Query START TRANSACTION / *! 40108 WITHCONSISTENT SNAPSHOT * /

1587512Query SHOW MASTER STATUS

Dump thread, set the isolation level of things to REPEATABLE READ, and enable consistent reading of things for backup of non-transaction data tables.

1587513Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1587513Query START TRANSACTION / *! 40108 WITHCONSISTENT SNAPSHOT * /

1587513Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `product`.`pr1`

1587513Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `product`.`pr2`

The Master thread releases the lock after the Dump thread backs up the non-transaction data table.

1587512Query UNLOCK TABLES / * FTWRL * /

Dump thread to continue the backup of data tables for other things.

2.-KJR, KJR, KR, Q, I, K, I, R, G, C, C, G, C, C WARNING: This willcause inconsistent backups

When using this option, mydumper will have a prompt similar to the following:

* * (mydumper:4095): WARNING * *: Executing in no-locks mode, snapshot will notbeconsistent

Its main action process is as follows:

Master thread, open the consistent read thing, get the coordinates of the binary log.

1586766Query START TRANSACTION / *! 40108 WITHCONSISTENT SNAPSHOT * /

1586766Query SHOW MASTER STATUS

Dump thread, set the isolation level of things to REPEATABLE READ, and enable consistent reading of things for data table backup.

1586767Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1586767Query START TRANSACTION / *! 40108 WITHCONSISTENT SNAPSHOT * /

Because the FLUSH TABLES WITH READ LOCK is not performed in this process, the binary log coordinates may be inaccurate; when (multiple threads) open a consistent read, the data table may change, which will cause the backup data to be inconsistent.

3.--less-locking Minimize locking time onInnoDB tables.

Master thread, get GLOBAL READ LOCK, turn on consistency reading, and get the coordinates of the binary log.

1588054Query FLUSH TABLES WITH READ LOCK

1588054Query START TRANSACTION / *! 40108 WITHCONSISTENT SNAPSHOT * /

1588054Query SHOW MASTER STATUS

Dump2 thread, set the transaction isolation level to REPEATABLE READ, and enable consistent reading of things.

1588056Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1588056Query START TRANSACTION / *! 40108 WITHCONSISTENT SNAPSHOT * /

Dump1 thread to lock the non-transaction data table.

1588055 Query LOCK TABLES `product`.`pr1` READ LOCAL, `product`.`pr2` READ LOCAL

Master thread, release the lock.

1588054Query UNLOCK TABLES / * FTWRL * /

Dump1 thread to back up non-transaction data tables.

1588055Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `product`.`pr1`

1588055Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `product`.`pr2`

The Dump1 thread releases the lock after the backup is completed.

1588055Query UNLOCK TABLES / * Non Innodb * /

Dump2 thread to continue the backup of data tables for other things.

4.--use-savepoints Use savepoints toreduce metadata locking issues, needs SUPER privilege

This option means that the metadata lock is released as soon as possible, and the other procedures are the same as 1.

1601611 Query SAVEPOINT mydumper

1601611 Query ROLLBACK TO SAVEPOINT mydumper

5.--lock-all-tables Use LOCK TABLE forall, instead of FTWRL

Master thread, get those databases and database tables, then lock the data tables that need to be backed up, open the consistency read transaction, and then get the coordinates of the binary log.

1586979Query SELECT TABLE_SCHEMA, TABLE_NAMEFROM information_schema.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMANOT IN (' information_schema', 'performance_schema',' data_dictionary') AND NOT (TABLE_SCHEMA = 'mysql' AND (TABLE_NAME =' slow_log' OR TABLE_NAME = 'general_log'))

1586979Query LOCK TABLE `product`.`pr1` READ, `product`.`pr2` READ, `product`.`pr3` READ, `stage`.`st1` READ, `stage`.`st2`Read, `test`.`tb1` READ, `test`.`tb2` READ

1586979Query START TRANSACTION / *! 40108 WITHCONSISTENT SNAPSHOT * /

1586979Query SHOW MASTER STATUS

Dump thread, set the isolation level of things to REPEATABLE READ, and enable consistent reading of things for backup of non-transaction data tables.

1586980Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1586980Query START TRANSACTION / *! 40108 WITHCONSISTENT SNAPSHOT * /

1586980Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `product`.`pr1`

1586980Query SELECT / *! 40001 SQL_NO_CACHE * / * FROM `product`.`pr2`

The Master thread releases the lock after the Dump thread backs up the non-transaction data table.

1586979Query UNLOCK TABLES / * FTWRL * /

Dump thread to continue the backup of data tables for other things.

This locking method, if there are more database tables, the locking efficiency is not high.

6.--trx-consistency-only Transactionalconsistency only

When using this option, mydumper will have a prompt similar to the following:

* * (mydumper:2573): WARNING * *: Using trx_consistency_only, binlog coordinateswill not be accurate if you are writing to non transactional tables

Master thread, get GLOBAL READ LOCK, turn on consistency reading, and get the coordinates of the binary log.

1588315Query FLUSH TABLES WITH READ LOCK

1588315Query START TRANSACTION / *! 40108 WITHCONSISTENT SNAPSHOT * /

1588315Query SHOW MASTER STATUS

Dump thread, set the transaction isolation level to REPEATABLE READ, and enable consistent reading of things.

1588316Query SET SESSION TRANSACTION ISOLATIONLEVEL REPEATABLE READ

1588316Query START TRANSACTION / *! 40108 WITHCONSISTENT SNAPSHOT * /

Master thread, release the lock.

1588315Query UNLOCK TABLES / * trx-only * /

Dump thread to back up the data table.

In this way, from lock to release lock, the time is the shortest and the efficiency is the highest.

According to the above analysis, the order of the influence of the locking process is as follows:

-- lock-all-tables > without this group =-- use-savepoints >-- less-locking >-- trx-consistency-only >-- no-locks

Performance Options group

This group specifies the number of threads and how to handle long queries.

-tjime Murray threads Number of threads touse, default 4

Set long query timerin seconds, default 60

-Kremlin murmuri longtime Kill long runningqueries Kill long runningqueries (instead of aborting)

Now that you have finished understanding the parameters, look at two practical examples.

1. Back up databases other than database mysql.

Mysql@db01:~/dbbackup$ mydumper-- outputdir=20170826-- compress-- build-empty-files--regex'^ (mysql))'--triggers-- events-- routines-- logfile=error.txt--use-savepoints-- trx-consistency-only-- threads=4-- verbose=3

two。 Back up all databases.

Mysql@db01:~/dbbackup$ mydumper-outputdir=20170826-compress-build-empty-files--triggers-events-routines-long-query-guard=60-kill-long-queries--logfile=error.txt-use-savepoints trx-consistency-only-threads=4--verbose=3

After option analysis and practice, the characteristics of mydumper are summarized:

1. Multithreaded backup, which can specify the number of threads, is also the key to faster than mysqldump.

two。 There are many considerations about the consistency of backup data, which is mainly reflected in the backup of non-transaction data tables.

3. When analyzing the option, no character set is specified. After looking at the general log, we find that the / *! 40101 SET NAMES binary*/, is handled in this way, which saves the overhead of converting the character set.

4. Provides options on how to deal with long queries.

Myloader does not have much to explain, take a look at the option explanation, practice can.

The efficiency of mydumper in backup has been greatly improved, but it still converts the data into SQL statements, which is often called logical backup.

After reading the above MySQL Data Dumper analysis of common tools for MySQL database backup, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.