MYSQL使用mydumper备份恢复操作简介
1. 环境准备
第一步是进行下载安装包的操作. 在github以及官网上面有相关的安装介质.
官网为:
http://www.mydumper.org/
建议也可以使用 github上面的 mydumper 下载相关的文件.
注意我本次下载了三个文件:
-rw-r--r-- 1 root root 2588180 7月 9 14:28 mydumper-0.10.7-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 2667695 7月 9 14:28 mydumper-0.10.7-1.el8.x86_64.rpm
-rw-r--r-- 1 root root 71615 7月 9 14:28 mydumper-0.10.7.tar.gz
第二步进行简单的安装
经过简单验证, 不同的centos7和centos8可以直接使用进行安装.
安装完成之后会自动形成至少两个命令
mydumper和myloader
2.备份操作
备份操作比较简单
time mydumper -u root -p 'yourpassword!' -h 127.0.0.1 -P 3306 -B yourdatabasename -o /home/yourdirectory/
3. 恢复操作
注意第一步需要先创建相关的数据库
创建数据库的操作为:
create database yourdatabasename;
create user 'yourdatabasename'@'%' identified WITH mysql_native_password by 'yourpassword';
grant all privileges on *.* to 'yourdatabasename'@'%' with grant option;
flush privileges ;
为了 提高恢复速度可以暂时设置如下参数:
set global innodb_flush_log_at_trx_commit=0;
set sql_log_bin = off；
注意这些参数 恢复完建议立即关闭,或者是重启一遍数据库使之取消生效.
执行恢复相关操作
注意 这里面发现部分 视图可能有问题. 需要再次确认.
time myloader -u root -p 'yourpassword' -h yourserverip -P 3306 -B yourdatabasename -d /home/yourdatabasename/
4. 相关参数
mydumper 相关参数
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
-M, --table-checksums Dump table checksums with the data
-d, --no-data Do not dump table data
--order-by-primary Sort the data by Primary Key or Unique key if no primary key exists
-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.
--long-query-retries Retry checking for long queries, default 0 (do not retry)
--long-query-retry-interval Time to wait before retrying the long query check in seconds, default 60
-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
-X, --snapshot-count number of snapshots, default 2
-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
--set-names Sets the names, use it at your own risk, default binary
-z, --tidb-snapshot Snapshot to use for TiDB
--sync-wait WSREP_SYNC_WAIT value to set at SESSION level
--where Dump only selected records.
-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
--ssl-mode Desired security state of the connection to the server: DISABLED, PREFERRED, REQUIRED, VERIFY_CA, VERIFY_IDENTITY
--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
--tls-version Which protocols the server permits for encrypted connections
myloader 相关参数
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
--innodb-optimize-keys Creates the table without the indexes and it adds them at the end
--set-names Sets the names, use it at your own risk, default binary
-L, --logfile Log file name to use, by default stdout is used
--purge-mode This specify the truncate mode which can be: NONE, DROP, TRUNCATE and DELETE
--sync-before-add-index If --innodb-optimize-keys is used, this option will force all the data threads to complete before starting the create index phase
--disable-redo-log Disables the REDO_LOG and enables it after, doesn't check initial status
-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
--ssl-mode Desired security state of the connection to the server: DISABLED, PREFERRED, REQUIRED, VERIFY_CA, VERIFY_IDENTITY
--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
--tls-version Which protocols the server permits for encrypted connections