MySQL Small white series _06 backup and Recovery-Mydumper and Myloader

http://blog.csdn.net/leshami/article/details/46815553
Https://www.cnblogs.com/zhoujinyi/p/3423641.html

Compiling and installing Mydumper

Https://launchpad.net/mydumper
Https://github.com/maxbube/mydumper

wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz  cmake .makemake install

2 files generated under/usr/local/bin: Mydumper and Myloader

Backing up result sets

• All backups are in one directory, and no directory is named with a date event: export-20180223-102900
  
• The directory has metadata files that record the main library binlog files and locations at the time of the backup

[[email protected] mydumper]# cat metadataStarted dump at: 2018-02-23 11:56:52SHOW MASTER STATUS:        Log: binlog.000009        Pos: 403849416        GTID:Finished dump at: 2018-02-23 11:56:52

• Backup contains built-in SQL, SQL built-in table, table content SQL

Mydumper Working process

1. Connecting to the target database
2. Determine if there are long queries by show Processlist, and decide whether to exit or kill long queries according to Long-query-guard and Kill-long-queries
3. Lock MyISAM table, FTWRL
4. Open transaction for InnoDB engine table, start transaction
5. Create Worker Child Threads
6. Identify candidate tables and insert Innodb_table,non_innodb_table,table_schemas linked list (table structure), respectively, according to category
7. Join the candidate table through G_async_queue_push to the task queue, with the worker sub-thread reading the table information from the queue and performing the data export
8. Perform unlock tables and unlock immediately after processing the MyISAM table to reduce lockout time
9. Wait for worker to exit
   

Mydumper command Options

http://blog.itpub.net/22664653/viewspace-2132759/

[[email protected] mydumper-0.9.1]# mydumper--helpusage:mydumper [OPTION ...] multi-threaded MySQL dumpingHelp  Options:-?,--Help Show help optionsapplication Options:-B,--database database to dump  Specifies the backed up database, does not specify to back up all databases, including MySQL, but does not contain performance_schema-t,--tables-list Comma delimited table list to dump (does Not exclude regex option) specifies the table to be backed up, with multiple tables separated by commas-O,--outputdir directory to outputs files to the specified directory output, and unspecified if the current path is Directory: Export-20180223-102900-s,--statement-size attempted size of INSERT statement in bytes, default 1000000 specify Build The number of bytes in the INSERT statement, the default 1000000 test does not find the difference,-R,--rows Try to split tables into chunks of the this many rows. This option turns off--chunk-filesize-f,--chunk-filesize Split tables to chunks of this output file size. This value was in mb-c,--compress compress output files-e,--build-empty-files build dump files even If no data available from table backup empty tableStructure-X,--regex Regular expression for ' db.table ' matching-i,--ignore-engines Comma Delimited li ST of storage Engines to ignore-m,--no-schemas does not dump table schemas with the data back-up table structure, backup only No-data does not dump table data is not backed up, only the table structure-G,--triggers dump triggers backup trigger-E,--event s Dump Events Backup event-R,--routines Dump stored procedures and functions backup stored procedure and function-K,--no-  Locks does not execute the temporary shared read lock.  Warning:this will cause inconsistent backups without a shared lock, causing inconsistent data--less-locking Minimize locking time on InnoDB  Tables. -L,--long-query-guard set long query timer in seconds, default 60 sets maximum query time, defaults to 60s, prevents lock-K,--kill-long-queries K Ill long running queries (instead of aborting) Kill long Query-D,--daemon Enable daemon mode using daemon mode-I,-- Snapshot-interval interval between each dump snapshot (in minutes), requires--daemon, default 60-l,--logfile Log file name to use, by default stdout is used--TZ-UTC SET time_zone= ' +00:00 ' at top of the dump to allow dumping of the TIMESTAMP data when a server had data in differ ENT time zones or data is being moved between servers with different time zones, defaults to on use--SKIP-TZ-UTC to Disab  Le. --SKIP-TZ-UTC--use-savepoints use savepoints to reduce metadata locking issues, needs SUPER PR Ivilege--success-on-1146 not increment error count and Warning instead of Critical in case of table doesn ' t ex IST--lock-all-tables use lock TABLE for all, instead of ftwrl-u,--updated-since use Update_time to                  Dump only tables updated in the last U days--trx-consistency-only transactional consistency only-h,--host              The host to connect To-u,--user Username with privileges to run the dump-p,--password User pasSword-p,--port TCP/IP port to connect to-s,--socket UNIX domain socket file-to-use F or CONNECTION-T,--threads number of threads to use, default 4 defaults to 4 thread-C,--compress-protocol use COM               Pression on the MySQL connection-v,--version Show The program version and Exit-v,--verbose verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2

Common mydumper Backup command backs up all databases

mydumper --user=root --password=‘123456‘ --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --outputdir=/backup/mydumper --compress --verbose=3 --logfile=/backup/mydumper/mydumper.log

Back up the specified library specify table-B

mydumper --user=root --password=‘123456‘ --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex ‘^(?!(mysql))‘ -B db1 -T t1,t2 --outputdir=/backup/mydumper --compress --verbose=3 --logfile=/backup/mydumper/mydumper.log

Backup table without table structure-only data, only dbname.tabname.sql-m

mydumper --user=root --password=‘123456‘ --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex ‘^(?!(mysql))‘ -B db1 -m --outputdir=/backup/mydumper --verbose=3 --logfile=/backup/mydumper/mydumper.log

Backup table without table data-only table structure, only Dbname-schema-create.sql and dbname.tabname-schema.sql-d

mydumper --user=root --password=‘123456‘ --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex ‘^(?!(mysql))‘ -B db1 -d --outputdir=/backup/mydumper --verbose=3 --logfile=/backup/mydumper/mydumper.log

Remote compression and use of compression protocol-C

mydumper --user=root --password=‘123456‘ --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex ‘^(?!(mysql))‘ -B db1 -d --outputdir=/backup/mydumper -c -C tar --verbose=3 --logfile=/backup/mydumper/mydumper.log

Matching multiple table backups

mydumper --user=root --password=‘123456‘ --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex ‘^(?!(mysql))‘ -B db1 --regex=db* --outputdir=/backup/mydumper --verbose=3 --logfile=/backup/mydumper/mydumper.log

Filter multiple libraries

mydumper --user=root --password=‘123456‘ --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex ‘^(?!(mysql|db2))‘ --outputdir=/backup/mydumper --verbose=3 --logfile=/backup/mydumper/mydumper.log

Even the empty table, also the backup structure-E

mydumper --user=root --password=‘123456‘ --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --regex ‘^(?!(mysql))‘ -B db1 -e --outputdir=/backup/mydumper --verbose=3 --logfile=/backup/mydumper/mydumper.log

-rw-r--r--. 1 root root 0 Feb 11:48 db1.t3.sql

Myloader restoring myloader Command parameters

[[email protected] mydumper]# myloader--helpusage:myloader [OPTION ...] multi-threaded MySQL loaderhelp Options:-  ?,--Help Show help optionsapplication Options:-D,--directory directory of the             Dump to Import-q,--queries-per-transaction number of queries per transaction, default 1000-o,--overwrite-tables  Drop tables If they already exist-b,--database an alternative database-to-restore into -S,--source-db Database to RESTORE-E,--enable-binlog enable binary logging of the rest Ore data-h,--host the host to connect To-u,--user Username with Privil Eges to run the dump-p,--password User password-p,--port TCP/IP port to Co                     Nnect to-s,--socket UNIX domain socket file to use for connection-t,--threads Number of threads toUse, default 4-c,--compress-protocol with compression on the MySQL connection-v,--version Show the program version and Exit-v,--verbose verbosity of output, 0 = silent, 1 = errors, 2 = W Arnings, 3 = info, default 2

Library Backup and Recovery

• Back up a library
  mydumper --user=root --password=‘123456‘ --host=172.16.2.154 --socket=/data/my3306/run/mysql.sock -B db1 --outputdir=/backup/mydumper --compress -C --verbose=3 --logfile=/backup/mydumper/mydumper.log
• Restore a library to its original instance
  myloader --user=root --password=‘123456‘ --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --directory=/backup/mydumper --verbose=3 -s db1 -B db11

[[email protected] mydumper]# myloader --user=root --password=‘123456‘ --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --directory=/backup/mydumper  --verbose=3 -B db1 -o** Message: 4 threads created** Message: Dropping table or view (if exists) `db1`.`t1`** Message: Creating table `db1`.`t1`** Message: Dropping table or view (if exists) `db1`.`t2`** Message: Creating table `db1`.`t2`** Message: Dropping table or view (if exists) `db1`.`t3`** Message: Creating table `db1`.`t3`** Message: Thread 2 restoring `db1`.`t2` part 0** Message: Thread 1 restoring `db1`.`t1` part 0** Message: Thread 4 shutting down** Message: Thread 3 shutting down** Message: Thread 2 shutting down** Message: Thread 1 shutting down

• Recovering a library to another instance
  myloader --user=root --password=‘123456‘ --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --directory=/backup/mydumper --verbose=3 -B db11

Recover a library from a full backup

• Backup Full Library
  mydumper --user=root --password=‘123456‘ --host=172.16.2.154 --socket=/data/my3306/run/mysql.sock --outputdir=/backup/mydumper --compress -C --verbose=3 --logfile=/backup/mydumper/mydumper.log
• Restore the specified library
  myloader --user=root --password=‘123456‘ --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --directory=/backup/mydumper --verbose=3 -s db3

• Restore the specified library and rename it
  myloader --user=root --password=‘123456‘ --host=127.0.0.1 --socket=/data/my3306/run/mysql.sock --directory=/backup/mydumper --verbose=3 -s db3 -B db33

  Above two if you import the same instance at the same time, the imported library will error

  mysql> select * from t1;ERROR 1412 (HY000): Table definition has changed, please retry transaction

Recover a table from a full backup

mysql> drop table t1;Query OK, 0 rows affected (0.02 sec)mysql> source /backup/mydumper/db3.t1-schema.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.05 sec)mysql> source /backup/mydumper/db3.t1.sqlQuery OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 0 rows affected (0.00 sec)Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> select * from t1;+------+-------+-------+| id   | name1 | name2 |+------+-------+-------+|    1 | yzw1  | yzw11 ||    2 | yzw2  | yzw22 ||    3 | yzw3  | yzw33 ||    4 | yzw4  | yzw44 |+------+-------+-------+4 rows in set (0.00 sec)

MySQL Small white series _06 backup and Recovery-Mydumper and Myloader