mysql逻辑备份 mysqldump和mydumper实践

1.mysqldump

mysqldump -uroot -p'Passw0rd*' testdb1  --single-transaction --set-gtid-purged=off > 1.sql    注意：--single-transaction不会锁表，如果
不加这个参数会在全表上加S锁，不允许更新和删除，还有--set-gtid-purged=off，这两个参数生产环境一定要加上。
-G -E -R 参数是什么意思 ,是触发器 事件 存储过程
 -G, --triggers              Dump triggers
  -E, --events                Dump events
  -R, --routines              Dump stored procedures and functions

用source或者不用登录导入数据
mysql> source 1.sql
mysql -uroot -p'Passw0rd*' -D testdb2 < 1.sql

mysqldump单线程  一张表一张表的导出，且导出文件很大
文件大 经常中断，且导出后不知道是不是完整的，另外导出文件最后一行一定要显示Dump completed，才说明导出是完整的。
[mysql@p0-dtpoc-dtpoc-cache-redis03-ma01 ~]$ tail -1 1.sql 
-- Dump completed on 2025-02-25 10:58:04

所以生产环境整库导出的时候一般般用mydumper

2.mydumper

1.首先创建mydumper用户，并赋权
mysql> create user mydumper@'%' identified by 'mydumper123!';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to mydumper@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> exit

2.在跳板机客户端执行mydumper
nohup mydumper -u mydumper -p 'password' -h IP --trx-consistency-only -t 4 -v 3 -o test_bk &
--trx-consistency-only 代表本次备份只对事务表做一致性保证 
-t 4 线程数量为4
-o test_bk 输出文件
-B testdb1 备份的数据库名字
-v, --verbose               Verbosity of output, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2 输入信息的日志级别

[mysql@p0-dtpoc-mysql-jump01 test_bk3]$ mydumper -u mydumper -p 'password' -h IP -B testdb1 --trx-consistency-only -t 4 -v 3 -o test_bk4

** (mydumper:25197): WARNING **: 13:59:05.115: Using trx_consistency_only, binlog coordinates will not be accurate if you are writing to non transactional tables.
** Message: 13:59:05.126: Connected to a MySQL server
** Message: 13:59:05.130: Started dump at: 2025-02-25 13:59:05

** Message: 13:59:05.131: Written master status
** Message: 13:59:05.137: Thread 1 connected using MySQL connection ID 89
** Message: 13:59:05.147: Thread 2 connected using MySQL connection ID 90
** Message: 13:59:05.154: Thread 3 connected using MySQL connection ID 91
** Message: 13:59:05.166: Thread 4 connected using MySQL connection ID 92
** Message: 13:59:05.168: Transactions started, unlocking tables
** Message: 13:59:05.170: Thread 1 dumping data for `testdb1`.`test1`
** Message: 13:59:05.171: Thread 2 dumping schema for `testdb1`.`test1`
** Message: 13:59:05.171: Thread 3 shutting down
** Message: 13:59:05.171: Thread 4 shutting down
** Message: 13:59:05.171: Thread 1 shutting down
** Message: 13:59:05.172: Thread 2 shutting down
** Message: 13:59:05.172: Finished dump at: 2025-02-25 13:59:05

[mysql@p0-dtpoc-mysql-jump01 test_bk3]$ cd test_bk4/
[mysql@p0-dtpoc-mysql-jump01 test_bk4]$ ls -ltr
total 16
-rw-rw-r-- 1 mysql mysql 201 Feb 25 13:59 testdb1.test1.sql
-rw-rw-r-- 1 mysql mysql  69 Feb 25 13:59 testdb1-schema-create.sql
-rw-rw-r-- 1 mysql mysql 194 Feb 25 13:59 testdb1.test1-schema.sql
-rw-rw-r-- 1 mysql mysql 179 Feb 25 13:59 metadata
[mysql@p0-dtpoc-mysql-jump01 test_bk4]$

3.使用myloder导入数据。

myloader -u mydumper -p 'password' -h IP -B testdb2 -o -e -d ./test_bk2 -t 4 -v 3

[mysql@p0-dtpoc-mysql-jump01 ~]$ myloader -u mydumper -p 'password' -h IP -B testdb2 -o -e -d ./test_bk9 -t 4 -v 3
** Message: 14:16:16.989: 4 threads created
** Message: 14:16:16.989: Dropping table or view (if exists) `testdb2`.`test1`
** Message: 14:16:17.000: Creating table `testdb2`.`test1`
** Message: 14:16:17.021: Thread 1 restoring `testdb1`.`test1` part 0
** Message: 14:16:17.021: Thread 4 shutting down
** Message: 14:16:17.021: Thread 2 shutting down
** Message: 14:16:17.021: Thread 3 shutting down
** Message: 14:16:17.028: Thread 1 shutting down