mysqldump命令
逻辑备份工具。文本形式保存备份，可读性较强。 备份逻辑： 将建库、建表、数据插入语句导出，包存至一个sql文件中。 比较适合于：数据量较小的场景，单表数据行千万级别以内。百G以内的小型数据库.跨版本、跨平台迁移。 可以本地、可以远程备份。 注意： 一般情况下，恢复需要耗费的时间是备份耗费时间的3-5倍。
连接参数：
代码语言：javascript复制mysqldump -u -p -S -h -P-u 用户 -p 密码 -S 指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock -h IP地址 -P 端口（默认3306）
备份参数：
-A：全备份
[root@cs ~]# mysqldump -uroot -p123 -A >/data/backup/full.sql-B：单库或者多库备份
[root@cs ~]# mysqldump -uroot -p123 -B test cs >/data/backup/db.sql单表或者多表备份
代码语言：javascript复制[root@cs ~]# mysqldump -uroot -p123 -B world city country >/data/backup/biao.sql注意： -A 和 -B 都带有了 create database 和use 语句，直接恢复即可 单表或多表备份方式, 没有 create database 和use 语句，所以要手工进行建库和use，再恢复数据。
高级功能参数
参数一： --master-data=2
- 自动记录binlog位置点 b. 自动加GRL锁(FTWRL ,flush tables with read lock) c. 配合--single-transaction ,减少锁的时间。
参数二： single-transaction
- 对于InnoDB表，利用MVCC中一致性快照进行备份。备份数据时,不加锁 b. 备份期间如果出现DDL操作，导致备份数据不一致 问题： mysqldump是严格意义上的热备吗？ 8.0 之后 master-data和single-transaction,对于InnoDB数据备份时是快照备份的. 备份表结构等数据时,还是FTWRL过程备份. --single-transaction 只是针对InnoDB表数据进行一致性快照备份。 问题： mysqldump备份需要锁表吗？ 是有的。global read lock
参数三： -R -E --triggers 备份特殊对象 存储过程 函数 触发器 事件
参数四： --max_allowed_packet=64M 最大允许数据包的大小。
标准化备份
代码语言：javascript复制[root@cs backup]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/data/backup/full_`date %F`.sql
Enter password: 实验：通过mysqldump全备 binlog实现PIT数据恢复
环境背景： 小型的业务数据库，50G，每天23:00全备，定期binlog异地备份。 故障场景： 周三下午2点，开发Navicat连接数据库实例错误，导致生产数据被误删除（DROP） 恢复思路：
- 挂维护页。
- 检查备份、日志可用。
- 如果只是部分损坏，建议找一个应急库进行恢复
- 全备恢复
- 日志截取并恢复
- 恢复后数据校验 （业务测试部门验证）
- 立即备份（停机冷备）
- 恢复架构系统
- 撤维护页，恢复业务
环境搭建
代码语言：javascript复制mysql> create database mdb;
Query OK, 1 row affected (0.00 sec)
mysql> use mdb;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> create table t2 (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> create table t3 (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into t2 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into t3 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
[root@cs ~]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/tmp/full_`date %F`.sql
Enter password:
mysql> create table t4 (id int);
Query OK, 0 rows affected (0.09 sec)
mysql> insert into t4 values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database mdb;
Query OK, 4 rows affected (0.06 sec)恢复过程
查看备份获取二进制位置点
代码语言：javascript复制mysql> show binary logs;
------------------ -----------
| Log_name | File_size |
------------------ -----------
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 201 |
| mysql-bin.000003 | 201 |
| mysql-bin.000004 | 3513 |
------------------ -----------
4 rows in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000004';
------------------ ------ ---------------- ----------- ------------- ----------------------------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
------------------ ------ ---------------- ----------- ------------- ----------------------------------
|........................................................................................................
| mysql-bin.000004 | 1580 | Query | 1 | 1671 | create database mdb |
| mysql-bin.000004 | 1671 | Anonymous_Gtid | 1 | 1736 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 1736 | Query | 1 | 1832 | use `mdb`; create table t1 (id int) |
| mysql-bin.000004 | 1832 | Anonymous_Gtid | 1 | 1897 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 1897 | Query | 1 | 1993 | use `mdb`; create table t2 (id int) |
| mysql-bin.000004 | 1993 | Anonymous_Gtid | 1 | 2058 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 2058 | Query | 1 | 2154 | use `mdb`; create table t3 (id int) |
| mysql-bin.000004 | 2154 | Anonymous_Gtid | 1 | 2219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 2219 | Query | 1 | 2290 | BEGIN |
| mysql-bin.000004 | 2290 | Table_map | 1 | 2334 | table_id: 131 (mdb.t1) |
| mysql-bin.000004 | 2334 | Write_rows | 1 | 2384 | table_id: 131 flags: STMT_END_F |
| mysql-bin.000004 | 2384 | Xid | 1 | 2415 | COMMIT /* xid=1405 */ |
| mysql-bin.000004 | 2415 | Anonymous_Gtid | 1 | 2480 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 2480 | Query | 1 | 2551 | BEGIN |
| mysql-bin.000004 | 2551 | Table_map | 1 | 2595 | table_id: 132 (mdb.t2) |
| mysql-bin.000004 | 2595 | Write_rows | 1 | 2645 | table_id: 132 flags: STMT_END_F |
| mysql-bin.000004 | 2645 | Xid | 1 | 2676 | COMMIT /* xid=1406 */ |
| mysql-bin.000004 | 2676 | Anonymous_Gtid | 1 | 2741 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 2741 | Query | 1 | 2812 | BEGIN |
| mysql-bin.000004 | 2812 | Table_map | 1 | 2856 | table_id: 133 (mdb.t3) |
| mysql-bin.000004 | 2856 | Write_rows | 1 | 2906 | table_id: 133 flags: STMT_END_F |
| mysql-bin.000004 | 2906 | Xid | 1 | 2937 | COMMIT /* xid=1407 */ |
| mysql-bin.000004 | 2937 | Anonymous_Gtid | 1 | 3002 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 3002 | Query | 1 | 3098 | use `mdb`; create table t4 (id int) |
| mysql-bin.000004 | 3098 | Anonymous_Gtid | 1 | 3163 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 3163 | Query | 1 | 3234 | BEGIN |
| mysql-bin.000004 | 3234 | Table_map | 1 | 3278 | table_id: 366 (mdb.t4) |
| mysql-bin.000004 | 3278 | Write_rows | 1 | 3328 | table_id: 366 flags: STMT_END_F |
| mysql-bin.000004 | 3328 | Xid | 1 | 3359 | COMMIT /* xid=4749 */ |
| mysql-bin.000004 | 3359 | Anonymous_Gtid | 1 | 3424 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 3424 | Query | 1 | 3513 | drop database mdb |
------------------ ------ ---------------- ----------- ------------- ---------------------------------------
恢复备份
mysql> source /tmp/full_2021-08-25.sql;
二进制恢复
[root@cs tmp]# mysqlbinlog --skip-gtids --start-position=1580 --stop-position=3424 /var/lib/mysql/mysql-bin.000004 >/tmp/bin1.sql
mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1;100G mysqldump全备恢复时间很长，误删除的表10M大小 ，有什么思路可以快速恢复？
代码语言：javascript复制思路：
a. 从全备中，将单表 建表语句和insert语句提取出来 ，进行恢复
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `oldguo`/!d;q' /data/backup/mdp/full.sql>/data/createtable.sql
grep -i 'INSERT INTO `oldguo`' /data/backup/mdp/full.sql >/data/data.sql
b. 从binlog中单独截取单表的所有binlog，进行恢复。
binlog2sql 截取单表binlog，恢复。物理备份工具使用-Percona Xtrabackup（PXB）
物理备份工具，支持全备和增量备份。 备份逻辑：
- 数据库运行期间，拷贝数据表空间.
- 拷贝的同时，会将备份期间的redo进行备份 恢复逻辑 ：
模拟了InnoDB Crash Recovery 功能，需要要将备份进行处理（前滚和回滚）后才能恢复
安装yum 源
代码语言：javascript复制[root@cs ~]# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y安装XtraBackup 2.4版本的
代码语言：javascript复制[root@cs ~]# yum install -y percona-xtrabackup-24.x86_641.全量备份
代码语言：javascript复制[root@cs backup]# innobackupex --user=root --password=123 /tmp/backup &>/tmp/xbk.log自主定制备份路径名
代码语言：javascript复制[root@cs backup]# innobackupex --user=root --password=123 --no-timestamp /data/backup/full &>/tmp/xbk.log全备的恢复 准备备份（Prepared） 将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚掉。模拟了CSR的过程
代码语言：javascript复制[root@cs lib]# innobackupex --apply-log /tmp/backup/full/恢复备份 前提：
代码语言：javascript复制1、被恢复的目录是空
2、被恢复的数据库的实例是关闭
systemctl stop mysqld创建新目录
代码语言：javascript复制[root@cs backup]# mkdir /var/lib/mysql数据拷贝
代码语言：javascript复制[root@cs lib]# innobackupex --copy-back /tmp/backup/full/启动数据库
代码语言：javascript复制systemctl start mysqldinnobackupex 增量备份(incremental)
代码语言：javascript复制（1）增量备份的方式，是基于上一次备份进行增量。
（2）增量备份无法单独恢复。必须基于全备进行恢复。
（3）所有增量必须要按顺序合并到全备中。前期准备
代码语言：javascript复制mysql> create database test charset utf8;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t1;
------
| id |
------
| 1 |
| 2 |
| 3 |
------
3 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)增量备份命令
代码语言：javascript复制# 全备 （周日）
[root@cs tmp]# innobackupex --user=root --password=123 --no-timestamp /tmp/backup/full &> /tmp/xbk.log
# 模拟数据变化 （周一）
mysql> create database cs;
Query OK, 1 row affected (0.00 sec)
mysql> use cs;
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#周一备份
[root@cs tmp]# innobackupex --user=root --password=123 --no-timestamp --incremental /tmp/backup/inc1 --incremental-basedir=/tmp/backup/full &>/tmp/inc1.log
#模拟周二数据（cs）
mysql> create table t2 (id int);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into t2 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#周二备份
[root@cs backup]# innobackupex --user=root --password=123 --no-timestamp --incremental /tmp/backup/inc2 --incremental-basedir=/tmp/backup/inc1 &>/tmp/inc2.log
# 模拟周三数据变化
mysql> create table t3 (id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t3 values(1),(2),(3);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database cs;
Query OK, 3 rows affected (0.04 sec)恢复到周三误drop之前的数据状态
代码语言：javascript复制恢复思路：
1. 挂出维护页，停止当天的自动备份脚本
2. 检查备份：周日full 周一inc1 周二inc2，周三的完整二进制日志
3. 进行备份整理（细节），截取关键的二进制日志（从备份——误删除之前）
4. 测试库进行备份恢复及日志恢复
5. 应用进行测试无误，开启业务
6. 此次工作的总结开始恢复
代码语言：javascript复制(1) 全备的整理
[root@cs backup]# innobackupex --apply-log --redo-only /tmp/backup/full/
(2) 合并inc1到full中
[root@cs backup]# innobackupex --apply-log --redo-only --incremental-dir=/tmp/backup/inc1 /tmp/backup/full/
(3) 合并inc2到full中
[root@cs backup]# innobackupex --apply-log --incremental-dir=/tmp/backup/inc2 /tmp/backup/full/
(4) 最后一次整理全备
[root@cs backup]# innobackupex --apply-log /tmp/backup/full
(5) 二进制截取
判断起点
[root@cs backup]# cat inc2/xtrabackup_binlog_info
mysql-bin.000001 2463
判断终点
mysql> show binlog events in 'mysql-bin.000001';
------------------ ------ ---------------- ----------- ------------- ---------------------------------------
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
------------------ ------ ---------------- ----------- ------------- ---------------------------------------
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.34-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 1 | 326 | create database test charset utf8 |
| mysql-bin.000001 | 326 | Anonymous_Gtid | 1 | 391 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 391 | Query | 1 | 489 | use `test`; create table t1 (id int) |
| mysql-bin.000001 | 489 | Anonymous_Gtid | 1 | 554 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 554 | Query | 1 | 626 | BEGIN |
| mysql-bin.000001 | 626 | Table_map | 1 | 671 | table_id: 122 (test.t1) |
| mysql-bin.000001 | 671 | Write_rows | 1 | 721 | table_id: 122 flags: STMT_END_F |
| mysql-bin.000001 | 721 | Xid | 1 | 752 | COMMIT /* xid=68 */ |
| mysql-bin.000001 | 752 | Anonymous_Gtid | 1 | 817 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 817 | Query | 1 | 905 | create database cs |
| mysql-bin.000001 | 905 | Anonymous_Gtid | 1 | 970 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 970 | Query | 1 | 1064 | use `cs`; create table t1 (id int) |
| mysql-bin.000001 | 1064 | Anonymous_Gtid | 1 | 1129 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1129 | Query | 1 | 1199 | BEGIN |
| mysql-bin.000001 | 1199 | Table_map | 1 | 1242 | table_id: 123 (cs.t1) |
| mysql-bin.000001 | 1242 | Write_rows | 1 | 1292 | table_id: 123 flags: STMT_END_F |
| mysql-bin.000001 | 1292 | Xid | 1 | 1323 | COMMIT /* xid=81 */ |
| mysql-bin.000001 | 1323 | Anonymous_Gtid | 1 | 1388 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1388 | Query | 1 | 1474 | drop database cs |
| mysql-bin.000001 | 1474 | Anonymous_Gtid | 1 | 1539 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1539 | Query | 1 | 1627 | create database cs |
| mysql-bin.000001 | 1627 | Anonymous_Gtid | 1 | 1692 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1692 | Query | 1 | 1786 | use `cs`; create table t1 (id int) |
| mysql-bin.000001 | 1786 | Anonymous_Gtid | 1 | 1851 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 1851 | Query | 1 | 1921 | BEGIN |
| mysql-bin.000001 | 1921 | Table_map | 1 | 1964 | table_id: 423 (cs.t1) |
| mysql-bin.000001 | 1964 | Write_rows | 1 | 2014 | table_id: 423 flags: STMT_END_F |
| mysql-bin.000001 | 2014 | Xid | 1 | 2045 | COMMIT /* xid=113 */ |
| mysql-bin.000001 | 2045 | Anonymous_Gtid | 1 | 2110 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 2110 | Query | 1 | 2204 | use `cs`; create table t2 (id int) |
| mysql-bin.000001 | 2204 | Anonymous_Gtid | 1 | 2269 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 2269 | Query | 1 | 2339 | BEGIN |
| mysql-bin.000001 | 2339 | Table_map | 1 | 2382 | table_id: 728 (cs.t2) |
| mysql-bin.000001 | 2382 | Write_rows | 1 | 2432 | table_id: 728 flags: STMT_END_F |
| mysql-bin.000001 | 2432 | Xid | 1 | 2463 | COMMIT /* xid=136 */ |
| mysql-bin.000001 | 2463 | Anonymous_Gtid | 1 | 2528 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 2528 | Query | 1 | 2622 | use `cs`; create table t3 (id int) |
| mysql-bin.000001 | 2622 | Anonymous_Gtid | 1 | 2687 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 2687 | Query | 1 | 2757 | BEGIN |
| mysql-bin.000001 | 2757 | Table_map | 1 | 2800 | table_id: 1034 (cs.t3) |
| mysql-bin.000001 | 2800 | Write_rows | 1 | 2850 | table_id: 1034 flags: STMT_END_F |
| mysql-bin.000001 | 2850 | Xid | 1 | 2881 | COMMIT /* xid=159 */ |
| mysql-bin.000001 | 2881 | Anonymous_Gtid | 1 | 2946 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 2946 | Query | 1 | 3032 | drop database cs |
----------------- ------ ---------------- ----------- ------------- ---------------------------------------
恢复3. 截取周二 23:00 到drop 之前的 binlog
[root@cs backup]# mysqlbinlog --start-position=2463 --stop-position=2946 /var/lib/mysql/mysql-bin.000001 >/tmp/backup/bin.sql
4. 进行恢复
[root@cs lib]# innobackupex --copy-back /tmp/backup/full/
[root@cs lib]# chown -R mysql.mysql mysql
[root@cs lib]# systemctl start mysqld
二进制恢复
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /tmp/backup/bin.sql;
Query 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, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Database changed
Query 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, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query 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, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.03 sec)
Query 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, 0 rows affected (0.00 sec)
Query 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, 0 rows affected (0.00 sec)
Query 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, 0 rows affected (0.00 sec)
mysql> show tables;
--------------
| Tables_in_cs |
--------------
| t1 |
| t2 |
| t3 |
--------------
3 rows in set (0.00 sec)
mysql> set sql_log_bin=1;
