Useful MySQL / MariaDB Backup and Restore Tools - mydumper & myloader－DanBrother的部落格

Useful MySQL / MariaDB Backup and Restore Tools - mydumper & myloader

☉ Download the latest "mydumper" RPM from the following URL:

[root@MariaDB ~]# cd /source
[root@MariaDB source]# wget --no-check-certificate \
https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-2.el7.x86_64.rpm
--2019-02-25 13:21:45-- https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-2.el7.x86_64.rpm
Resolving github.com (github.com)... 192.30.253.112
Connecting to github.com (github.com)|192.30.253.112|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://github-production-release-asset-2e65be.s3.amazonaws.com/45695616/79bc2fe6-5cfe-11e8-9ac3-8bd5468930ca?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20190523%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20190523T053611Z&X-Amz-Expires=300&X-Amz-Signature=68f79dc329bde8aabb0346279a787976974eb9cb4bebb76418bf57c27fb88d71&X-Amz-SignedHeaders=host&actor_id=0&response-content-disposition=attachment%3B%20filename%3Dmydumper-0.9.5-2.el7.x86_64.rpm&response-content-type=application%2Foctet-stream [following]
--2019-02-25 13:21:46-- https://github-production-release-asset-2e65be.s3.amazonaws.com/45695616/79bc2fe6-5cfe-11e8-9ac3-8bd5468930ca?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAIWNJYAX4CSVEH53A%2F20190523%2Fus-east-1%2Fs3%2Faws4_request&X-Amz-Date=20190523T053611Z&X-Amz-Expires=300&X-Amz-Signature=68f79dc329bde8aabb0346279a787976974eb9cb4bebb76418bf57c27fb88d71&X-Amz-SignedHeaders=host&actor_id=0&response-content-disposition=attachment%3B%20filename%3Dmydumper-0.9.5-2.el7.x86_64.rpm&response-content-type=application%2Foctet-stream
Resolving github-production-release-asset-2e65be.s3.amazonaws.com (github-production-release-asset-2e65be.s3.amazonaws.com)... 52.216.131.3
Connecting to github-production-release-asset-2e65be.s3.amazonaws.com (github-production-release-asset-2e65be.s3.amazonaws.com)|52.216.131.3|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1688520 (1.6M) [application/octet-stream]
Saving to: ‘mydumper-0.9.5-2.el7.x86_64.rpm’

100%[=============================================================================================================================================>] 1,688,520 287KB/s in 8.5s

2019-02-25 13:21:55 (195 KB/s) - ‘mydumper-0.9.5-2.el7.x86_64.rpm’ saved [1688520/1688520]

☉ Install the mydumper RPM as follows:
[root@MariaDB source]# rpm -ivh mydumper-0.9.5-2.el7.x86_64.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mydumper-0.9.5-2 ################################# [100%]

[root@MariaDB source]# rpm -qa |grep mydumper
mydumper-0.9.5-2.x86_64

Backup Tool: mydumper Usage Examples:
[root@MariaDB source]# mydumper --help
Usage:
mydumper [OPTION?] multi-threaded MySQL dumping

Help Options:
-?, --help Show help options

Application Options:
-B, --database Database to dump
-T, --tables-list Comma delimited table list to dump (does not exclude regex option)
-O, --omit-from-file File containing a list of database.table entries to skip, one per line (skips before applying regex option)
-o, --outputdir Directory to output files to
-s, --statement-size Attempted size of INSERT statement in bytes, default 1000000
-r, --rows Try to split tables into chunks of this many rows. This option turns off --chunk-filesize
-F, --chunk-filesize Split tables into chunks of this output file size. This value is in MB
-c, --compress Compress output files
-e, --build-empty-files Build dump files even if no data available from table
-x, --regex Regular expression for 'db.table' matching
-i, --ignore-engines Comma delimited list of storage engines to ignore
-N, --insert-ignore Dump rows with INSERT IGNORE
-m, --no-schemas Do not dump table schemas with the data
-d, --no-data Do not dump table data
-G, --triggers Dump triggers
-E, --events Dump events
-R, --routines Dump stored procedures and functions
-W, --no-views Do not dump VIEWs
-k, --no-locks Do not execute the temporary shared read lock. WARNING: This will cause inconsistent backups
--no-backup-locks Do not use Percona backup locks
--less-locking Minimize locking time on InnoDB tables.
-l, --long-query-guard Set long query timer in seconds, default 60
-K, --kill-long-queries Kill long running queries (instead of aborting)
-D, --daemon Enable 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 dump to allow dumping of TIMESTAMP data when a server has data in different time zones or data is being moved between servers with different time zones, defaults to on use --skip-tz-utc to disable.
--skip-tz-utc
--use-savepoints Use savepoints to reduce metadata locking issues, needs SUPER privilege
--success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist
--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
--complete-insert Use complete INSERT statements that include column names
-h, --host The host to connect to
-u, --user Username with the necessary privileges
-p, --password User password
-a, --ask-password Prompt For User password
-P, --port TCP/IP port to connect to
-S, --socket UNIX domain socket file to use for connection
-t, --threads Number of threads to use, default 4
-C, --compress-protocol Use compression 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
--defaults-file Use a specific defaults file
--ssl Connect using SSL
--key The path name to the key file
--cert The path name to the certificate file
--ca The path name to the certificate authority file
--capath The path name to a directory that contains trusted SSL CA certificates in PEM format
--cipher A list of permissible ciphers to use for SSL encryption

☉ Backup All Databases: (the specified backup directory need not exist)

[root@MariaDB ~]# mydumper -u root -p dba -o /backup/mysql/all_dbs/

☉ Backup the "test" database only (the specified backup directory need not exist), as follows:

[root@MariaDB ~]# mydumper -u root -p dba -B test -o /backup/mysql/test/
[root@MariaDB mysql]# ls -ld /backup/mysql/*
drwx------ 2 root root 4096 Feb 25 13:03 /backup/mysql/all_dbs
drwx------ 2 root root 286 Feb 25 13:06 /backup/mysql/test

[root@MariaDB ~]# cd /backup/mysql/test/
[root@MariaDB test]# ls -l
total 40
-rw-r--r-- 1 root root 146 Feb 25 13:06 metadata
-rw-r--r-- 1 root root 93 Feb 25 13:06 test-schema-create.sql
-rw-r--r-- 1 root root 305 Feb 25 13:06 test.test_tab1-schema.sql
-rw-r--r-- 1 root root 161 Feb 25 13:06 test.test_tab1.sql
-rw-r--r-- 1 root root 305 Feb 25 13:06 test.test_tab2-schema.sql
-rw-r--r-- 1 root root 161 Feb 25 13:06 test.test_tab2.sql
-rw-r--r-- 1 root root 305 Feb 25 13:06 test.test_tab3-schema.sql
-rw-r--r-- 1 root root 161 Feb 25 13:06 test.test_tab3.sql
-rw-r--r-- 1 root root 251 Feb 25 13:06 test.test_tab-schema.sql
-rw-r--r-- 1 root root 160 Feb 25 13:06 test.test_tab.sql

[root@MariaDB test]# cat metadata
Started dump at: 2019-02-25 13:06:44
SHOW MASTER STATUS:
Log: MariaDB-bin.000069
Pos: 2164
GTID:0-1-321

Finished dump at: 2019-02-25 13:06:44

[root@MariaDB test]# cat test-schema-create.sql
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;

☉ Backup multiple tables in the "test" dtabase (the specified backup directory need not exist), as follows:

[root@MariaDB ~]# mydumper -u root -p dba -B test -T test_tab1,test_tab2,test_tab3 -o /backup/mysql/multiple_tables/
[root@MariaDB ~]# ls -l /backup/mysql/multiple_tables/
total 32
-rw-r--r-- 1 root root 146 Feb 25 13:30 metadata
-rw-r--r-- 1 root root 93 Feb 25 13:30 test-schema-create.sql
-rw-r--r-- 1 root root 305 Feb 25 13:30 test.test_tab1-schema.sql
-rw-r--r-- 1 root root 161 Feb 25 13:30 test.test_tab1.sql
-rw-r--r-- 1 root root 305 Feb 25 13:30 test.test_tab2-schema.sql
-rw-r--r-- 1 root root 161 Feb 25 13:30 test.test_tab2.sql
-rw-r--r-- 1 root root 305 Feb 25 13:30 test.test_tab3-schema.sql
-rw-r--r-- 1 root root 161 Feb 25 13:30 test.test_tab3.sql
[root@MariaDB ~]#

Restore Tool: myloader Usage Examples:

[root@MariaDB test]# myloader --help
Usage:
myloader [OPTION?] multi-threaded MySQL loader

Help Options:
-?, --help Show help options

Application 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 restore data
-h, --host The host to connect to
-u, --user Username with the necessary privileges
-p, --password User password
-a, --ask-password Prompt For User password
-P, --port TCP/IP port to connect to
-S, --socket UNIX domain socket file to use for connection
-t, --threads Number of threads to use, default 4
-C, --compress-protocol Use compression 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
--defaults-file Use a specific defaults file

☉ Drop the "test" database, as follows:

[root@MariaDB ~]# mysql -uroot -p -e "drop database test"
Enter password:

☉ Confirm the "test" database has been dropped, as follows:

[root@MariaDB ~]# mysql -uroot -p -e "show databases like 'test'"
Enter password:

☉ Restore the "test" database, as follows:

[root@MariaDB ~]# myloader -u root -p dba -B test -d /backup/mysql/test/

[root@MariaDB ~]# mysql -uroot -p -e "show databases like 'test'"
Enter password:
+-----------------+
| Database (test) |
+-----------------+
| test |
+-----------------+

[root@MariaDB ~]# mysql -uroot -p -e "use test ; show tables like 'test_tab';"
Enter password:
+---------------------------+
| Tables_in_test (test_tab) |
+---------------------------+
| test_tab |
+---------------------------+

☉ Drop the "test_tab" table in the "test" database, as follows:

[root@MariaDB ~]# mysql -uroot -p -e "drop table test.test_tab;"
Enter password:

☉ Confirm that "test_tab" table in the "test" database has been dropped, as follows:

[root@MariaDB ~]# mysql -uroot -p -e "use test ; show tables like 'test_tab';"
Enter password:

☉ Restore the "test_tab" table in the "test" database, as follows:

[root@MariaDB ~]# myloader -u root -p dba -B test -o test_tab -d /backup/mysql/test/

☉ Confirm that the "test_tab" table in the "test" database has been restored, as follows:

[root@MariaDB ~]# mysql -uroot -p -e "use test ; show tables like 'test_tab';"
Enter password:
+---------------------------+
| Tables_in_test (test_tab) |
+---------------------------+
| test_tab |
+---------------------------+

[root@MariaDB ~]# mysql -uroot -p -e "select * from test.test_tab;"
Enter password:
+------+------+
| c1 | c2 |
+------+------+
| abc | def |
| aa | bb |
+------+------+

☉ Drop the "test_tab1", "test_tab2" & "test_tab3" tables in the "test" database, as follows:

[root@MariaDB ~]# mysql -uroot -p -e "drop table test.test_tab1,test.test_tab2,test.test_tab3;"
Enter password:

☉ Confirm the "test_tab1", "test_tab2" & "test_tab3" tables in the "test" database have been dropped, as follows:

[root@MariaDB ~]# mysql -uroot -p -e "use test ; show tables like 'test_tab_';"
Enter password:

☉ Restore the "test_tab1", "test_tab2" & "test_tab3" tables in the "test" database, as follows:

[root@MariaDB ~]# myloader -u root -p dba -B test -o test_tab1,test_tab2,test_tab3 -d /backup/mysql/multiple_tables/

☉ Confirm the "test_tab1", "test_tab2" & "test_tab3" tables in the "test" database have been restored, as follows:

[root@MariaDB ~]# mysql -uroot -p -e "use test ; show tables like 'test_tab_';"
Enter password:
+----------------------------+
| Tables_in_test (test_tab_) |
+----------------------------+
| test_tab1 |
| test_tab2 |
| test_tab3 |
+----------------------------+

[root@MariaDB ~]# mysql -uroot -p -e \
> "select "1" as tab_num,c1,c2 from test.test_tab1 \
> UNION ALL \
> select "2",c1,c2 from test.test_tab2 \
> UNION ALL \
> select "3",c1,c2 from test.test_tab3;"
Enter password:
+---------+------+------+
| tab_num | c1 | c2 |
+---------+------+------+
| 1 | abc | def |
| 1 | aa | bb |
| 2 | abc | def |
| 2 | aa | bb |
| 3 | abc | def |
| 3 | aa | bb |
+---------+------+------+