Mysqldump使用场景

优点	缺点	场景
逻辑备份，版本兼容性好。原生备份工具，备份恢复使用体验好。	单线程备份，耗时比较长。所有内容放在一个文件，不利于单表恢复。	一般小于50G的数据库适合使用。升级或迁移数据时版本跨度大适合使用。因为备份是SQL语句，可以在不同数据库产品中使用。

场景1 --all-database 备份

带有以下参数进行--all-database 备份
/data/mysql/base/5.7.32/bin/mysqldump -uroot -S/data/mysql/data/6802/mysqld.sock -p123456 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --hex-blob --triggers --routines --events --all-databases > host62_36_alldata.sql
备份文件信息：
-rw-r--r-- 1 root root 383M Feb 22 10:51 host62_36_alldata.sql
1.好处是此备份文件可以恢复到时间点是：2022年2月22号10点51分钟。
2.备份动作为：我们通过开启数据库参数general_log来观察。以下是关键动作：
2022-02-22T10:51:41.989491+08:00        407941 Query    FLUSH /*!40101 LOCAL */ TABLES
2022-02-22T10:51:42.020720+08:00        407941 Query    FLUSH TABLES WITH READ LOCK  # 关闭所有打开的表并且对所有数据库表加一个全局读锁。
2022-02-22T10:51:42.043616+08:00        407941 Query    SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ  # 设置会话隔离级别为REPEATABLE READ
2022-02-22T10:51:42.043772+08:00        407941 Query    START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */  # 因为要开启一致性快照事务，所以必须将务事务的隔离级别设置成REPEATABLE READ [使之后的SELECT都能读取到一开始同一个的READ VIEW，不受之后其他事务修改或者未提交事务的影响。]
2022-02-22T10:51:42.043876+08:00        407941 Query    SHOW VARIABLES LIKE 'gtid\_mode'  # 查看是否开启GTID模式
2022-02-22T10:51:42.047414+08:00        407941 Query    SELECT @@GLOBAL.GTID_EXECUTED
2022-02-22T10:51:42.047680+08:00        407941 Query    SHOW MASTER STATUS  # 获取当前binlog的位置信息
2022-02-22T10:51:42.047781+08:00        407941 Query    UNLOCK TABLES  # 获取当前binlog的位置信息
2022-02-22T10:51:42.047926+08:00        407941 Query    SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_S
CHEMA.FILES WHERE FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL
_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2022-02-22T10:51:42.048535+08:00        407941 Query    SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE F
ROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE = 'DATAFILE' ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2022-02-22T10:51:42.048924+08:00        407941 Query    SHOW DATABASES

3.备份动作和备份参数的对应关系：
--single-transaction 在开始备份前，执行start transaction命令，以此来获取一致性备份，该参数仅对innodb存储引擎有效。
--master-data=2 用于记录一致性备份的位点，用于搭建从库。
--flush-logs    用于刷新数据库日志，通过二进制文件时间戳分辨备份前与备份后

参数	作用
–single-transaction	开启一致性快照事务，使用InnoDB多版本控制向查询呈现数据库在某个时间点的快照。
–master-data=2	会将change master 语句写入dump文件中，只不过会被注释掉，在从库导入后，配置主从需要指定文件名和位置。建议使用该值
–flush-logs	刷新一个新的日志文件，可用于增量备份：全备之后将新binary log存储起来

sys.processlist 不能使用时怎么办？

带有以下参数进行--all-database 备份：
--triggers
--routines
--events
1、备份完成后在一个新实例中导入备份文件，执行以下查询命令：
select * from sys.processlist;
ERROR 1356 (HY000): View 'sys.processlist' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
报错原因：使用mysqldump在执行--all-databases不会备份mysql.proc下的系统自身的存储过程，导致导入到新机器后，部分sys下的视图没法正常使用；这是一个BUG，并且只存在于MySQL5.7。[5.7.32已复现]
 
修复方法：
方法一  对应版本的mysql_upgrade命令升级系统表
/data/mysql/base/5.7.32/bin/mysql_upgrade -s -uroot -S/data/mysql/data/6802/mysqld.sock -p123456
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
The --upgrade-system-tables option was used, databases won't be touched.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
The sys schema is already up to date (version 1.5.2).
Found 0 sys functions, but expected 22. Re-installing the sys schema.
Upgrading the sys schema.
Upgrade process completed successfully.
Checking if update is needed.


参数说明：
-s, --upgrade-system-tables
                      Only upgrade the system tables, do not try to upgrade the
                      data.


验证：select count(*) from sys.processlist;
+----------+
| count(*) |
+----------+
|       51 |
+----------+


方法二 :查出要备份的库名,使用--databases备份(后面跟上需要备份的库名)
实例如下：
SELECT
        GROUP_CONCAT(schema_name SEPARATOR ' ') 
    FROM 
        information_schema.schemata 
    WHERE 
        schema_name NOT IN ('performance_schema','information_schema');

场景2 想看到导入时的进度，可以用什么手段？

方式一：使用PV工具 [Centos系统可以通过yum安装]

预先查询一下数据库的大小
SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema; 
     2.

nohup /data/mysql/base/5.7.32/bin/mysqldump -uroot -S/data/mysql/data/6802/mysqld.sock -p123456 --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --hex-blob --triggers --routines --events --all-databases| pv --progress --size 500m > host62_36_alldata04.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure. &

[===============================================================================================> ] 76%
-rw-r--r-- 1 root root 400657285 Feb 22 13:47 host62_36_alldata02.sql
-rw-r--r-- 1 root root 400657285 Feb 22 14:16 host62_36_alldata03.sql
-rw-r--r-- 1 root root 400657285 Feb 22 14:22 host62_36_alldata04.sql
-rw-r--r-- 1 root root 400657285 Feb 22 10:51 host62_36_alldata.sql

方法二：使用importProgress工具 [只支持单线程的程序，如Mysqldump] 这种方式看不了进度信息

使用示例，(数据大约100G,便于观察，测试结果是这个数据量大，不适合)

nohup /data/mysql/base/5.7.31/bin/mysqldump -uroot -S /data/mysql/data/3310/mysqld.sock -p'123456' --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --hex-blob --triggers --routines --events --all-databases > host62_11_alldata01.sql &

ps -ef|grep mysqldump 
root 29498 13372 32 16:01 pts/1 00:00:07 /data/mysql/base/5.7.31/bin/mysqldump -uroot -S /data/mysql/data/3310/mysqld.sock -px xxxxxxxxxxxxx --default-character-set=utf8mb4 --single-transaction --master-data=2 --flush-logs --hex-blob --triggers --routines --events --all-databases

./importProgress 29498  # 29498 是备份命令的进程号

输出展示：

  Import ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━   0% 0:00:00 0:00:00 ? 0/0 bytes （最终备份是成功的，但是没有看到进度信息）

方法三：添加-v 参数

 -v, --verbose       Print info about the various stages.

效果展示：

-- Connecting to localhost...
-- main : logs flushed successfully!
-- Starting transaction...
-- Setting savepoint...
-- Retrieving table structure for table columns_priv...
-- Sending SELECT query...
-- Retrieving rows...

场景3 导入时进行限流，减小对系统的影响，可以用什么手段？

方法一：使用PV工具，mysql client+PV+会话参数导入

在62.56主机上yum 安装，并使用之前的备份文件进行恢复。

输出展示：(因为62.56的GTID不是空的，所以设置了reset master;其他参数是防止导入超时的)

(echo 'reset master;set @@autocommit=1;set @@wait_timeout=28800;set @@interactive_timeout=28800;'; pv -W -L 10M host62_36_alldata02.sql) | /data/mysql/base/5.7.32/bin/mysql -uroot -S/data/mysql/data/6802/mysqld.sock -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
382MiB 0:00:38 [ 10MiB/s] [=====================================================================================>] 100%

参数说明：
-W：在需要密码输入时有用，可等待密码输出完成，再开启监控进度条
-L:限流，将传输限制在每秒最大字节的范围内

方法二： importProgress 工具 只适用于导入。

执行导入命令之后，需要使用ps -ef|grep mysql  命令区分出导入命令的进程号

root 3132 19683 0 Feb22 pts/1 00:00:00 /data/mysql/base/5.7.32/bin/mysql -uroot -S/data/mysql/data/6802/mysqld.sock -px xxxx  # 连接进程
root 3301 19474 13 17:01 pts/0 00:00:00 /data/mysql/base/5.7.32/bin/mysql -uroot -S/data/mysql/data/6802/mysqld.sock -px xxxx # 这个是我们的导入进程

输出展示：

./importProgress 3301
Import ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 100% 0:00:00 0:00:23 12.1 MB/s 400.7/400.7 MB

进行主从复制搭建

导入：

/data/mysql/base/5.7.32/bin/mysql -uroot -S/data/mysql/data/6802/mysqld.sock -p123456 <host62_36_alldata05.sql

建立复制关系：

change master to master_host='10.186.62.36',master_user='universe_op',master_password='123456',master_port=6802,master_auto_position=1;

查看状态：

show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.186.62.36
Master_User: universe_op
Master_Port: 6802
Connect_Retry: 60
Master_Log_File: mysql-bin.000021
Read_Master_Log_Pos: 234
Relay_Log_File: mysql-relay.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000021
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

升级系统表：

 /data/mysql/base/5.7.32/bin/mysql_upgrade -s  -uroot -S /data/mysql/data/6802/mysqld.sock -p123456 --force

参数补充：

参数名	简写	说明	默认值
–add-drop-database		每个数据库创建之前添加drop数据库语句	未开启
–add-drop-table		创建表之前添加drop表语句	开启
–events	-E	导出事件	未开启
–routines	-R	导出存储过程和自定义函数	未开启
–triggers		导出触发器	开启
–extended-insert	-e	使用具有多个VALUES列的INSERT语法（可有效降低文件大小）	开启
–force	-f	在导出时忽略出现的SQL错误	未开启
–tz-utc		在导出顶部设置时区TIME_ZONE=’+00:00’	开启

完结撒花。