mysqldump备份
1.mysqldump备份原理
mysqldump备份过程
可以临时性的general log，备份完成后观察整个日志中的备份过程
flush tables;关闭所有打开的表
flush tables with read lock;加全局读锁
set session transaction isolation level repeatable read; 将隔离级别转为RR
start transaction; 开启事务
show master status; 获取正在使用的二进制文件
unlock tables; 释放全局读锁，这时就可以做写操作
show create database db;
savepoint sp; 回滚点
show create table t1;
select * from t1;
rollback to sp; 回滚到sp回滚点
release savepoint sp;
mysqldump的备份原理：通过设置READ LOCK获取数据库全局锁后，RR事务隔离级别下记录当前的日志文件名和日志位置position，然后释放掉全局锁。接下来创建一个事务的回滚点，所有数据的获取都是获取的是这个sp回滚点数据。最后释放掉回滚点sp。当然，对于MyISAM存储引擎，备份是直接锁全表的。
值得注意点：从分析mysqldump过程中我们可以知道，此命令在开始时刻会带来数据库瞬时的锁定（FLUSH TABLES WITH READ LOCK），虽然锁定时间是非常短暂的，但是却会带来非常大的数据库隐患，因为在此过程中，如果执行有DDL语句，就会导致此命令堵塞并最终异常退出。所以在做备份时间节点的选择上，需要根据数据库环境选择在负载压力最小，且没有以上操作时候进行备份。
1.1备份类型
热备
在数据库正常业务时,备份数据,并且能够一致性恢复（只能是innodb）
对业务影响非常小
温备
锁表备份,只能查询不能修改（myisam）
影响到写入操作
冷备
关闭数据库业务,数据库没有任何变更的情况下,进行备份数据.
业务停止
1.2备份方式
逻辑备份
基于SQL语句进行备份
mysqldump *****
mysqlbinlog *****
物理备份
基于磁盘数据文件备份
xtrabackup(XBK) ：percona 第三方 *****
MySQL Enterprise Backup（MEB）
1.3逻辑备份和物理备份的比较
mysqldump (MDP)
优点：
1.不需要下载安装
2.备份出来的是SQL，文本格式，可读性高,便于备份处理
3.压缩比较高，节省备份的磁盘空间
缺点：
4.依赖于数据库引擎，需要从磁盘把数据读出
然后转换成SQL进行转储，相当耗费资源，数据量大的话效率较低
建议：
50G以内的数据量级，可以使用mysqldump
xtrabackup(XBK)
优点：
1.类似于直接cp数据文件，不需要管逻辑结构，相对来说性能较高
缺点：
2.可读性差
3.压缩比低，需要更多磁盘空间
建议：
>50G2.mysqldump常用参数
官网参数查询：
| 参数名 | 缩写 | 含义 | 默认值 |
|---|---|---|---|
| --user | -u | 连接服务器时使用的账户 | |
| --password | -p | 密码 | |
| --port | -P | 端口号 | 3306 |
| --all-database | -A | 备份mysql上的所有库 | |
| --database | -B | 备份指定的库 | |
| --add-drop-database | 每个数据库创建之前添加drop数据库语句 | 未开启 | |
| --add-drop-table | 创建表之前添加drop语句 | 开启 | |
| --events | -E | 导出事件 | 未开启 |
| --routines | -R | 导出存储过程及自定义函数 | 未开启 |
| --triggers | 导出触发器 | 开启 | |
| --extended-insert | -e | 使用具有多个values列的insert语法 | 开启 |
| --ignore-table | 不导出指定表，指定忽略多个表时，需要重复指定多次 | ||
| --no-data | -d | 不导出任何数据，只会导出数据库表结构 | |
| --no-create-info | -t | 指导出数据，而不添加create table语句 | |
| --force | -f | 在导出过程中忽略出现的sql错误 | 未开启 |
| --tz-utc | 在导出顶部设置时区TIME_ZONE=‘+00:00‘ | 开启 | |
| --where | -w | 只转储给定的where条件选择的记录 | |
| --set-gtid-purged | 是否添加SET @@GLOBAL.GTID_PURGED输出 | ||
| --single-transaction | 通过在一个事务中导出所有表从而创建一个一致性的快照，使用于innodb引擎 | ||
| --master-data | 该选项将当前服务器的binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER命令；如果为2，将会注释CHANGE MASTER |
基本参数
-A 备份当前实例下所有的数据库
-B 备份指定的数据库
备份某个库的某些表： 库名 table1 table2 备份库中的指定表
-E 备份事件
-R 备份存储过程和函数
--triggers 备份触发器
--master-data=2 备份时记录当时的position号或者gtid号；这个position就是起始位置
--single-transaction 触发一致性备份
--set-gtid-purged=on 主从复制环境中需要，常规备份不需要
备份示例
备份文件的名称要有规范，例如：数据库+端口号+日期.sql
可以在备份时使用--flush-logs刷一下binlog，方便日后日用binlog
备份当前实例的所有库
mysqldump -uroot -p123456 -A -R -E --triggers --master-data=2 --flush-logs --single-transaction > ./all_3306_`date +%Y%m%d`.sql
备份当前实例下的指定数据库
-B 指定
mysqldump -uroot -p123456 -B db -R -E --triggers --master-data=2 --single-transaction > ./db_3306_`date +%Y%m%d`.sql
备份指定数据库中的一些表
dbname table1 table2 ...
mysqldump -uroot -p123456 db t1 -R -E --triggers --master-data=2 --single-transaction > ./db_t1_3306_`date +%Y%m%d`.sql
单独备份每个表
information_schema.tables
执行输出的脚本即可
select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," --master-data=2 --single-transaction --set-gtid-purged=0 -R -E --triggers>/backup/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in (‘sys‘,‘information_schema‘,‘performance_schema‘) into outfile ‘/var/lib/mysql-files/mysqldump.sh‘;
备份后压缩
示例：压缩备份
之后gzip -d解压即可
mysqldump -uroot -p123456 -A -R -E --triggers --master-data=2 --single-transaction | gzip -c > all_3306_`date +%Y%m%d`.sql.gz
示例：压缩备份在远程服务器
比如说我要备在129这台主机上
mysqldump -uroot -p123456 -A -R -E --triggers --master-data=2 --single-transaction | gzip -c | ssh root@192.168.190.129 ‘cat > /tmp/all_3306_`date +%Y%m%d`.sql.gz‘
3.mysqldump恢复示例
案例：上午10点钟，因为误操作删库了，最近一次的全备是凌晨一点的。
思路：
? 1.停止业务，防止二次伤害,flush-logs这样故障操作日志都在之前的binlog文件里
? 2.用一个临时库，先恢复最近的全备
? 3.截取从最近全备到10点删库之间的binlog,恢复到临时库，进行测试
方法一：直接将临时库顶替生产库
方法二：想误删的相关库和表导出，在导入到原生产库，测试后恢复业务
3.1准备昨天23点的全备
全备的同时--flush-logs
mysqldump -uroot -p123456 -A -R -E --triggers --master-data=2 --flush-logs --single-transaction > ./all_3306.sql
[root@localhost ~]# ls
all_3306.sql
#全备文件中截取的二级制文件位置
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000002‘, MASTER_LOG_POS=194;
3.2准备临时库
使用多实例的3307端口
systemctl start mysqld3307
3.3模拟昨天23点到10点之间的数据变化
use test
mysql> select * from t1;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 1 |
+---+---+
2 rows in set (0.00 sec)
-- 插入数据模拟全备之后的数据变化
mysql> insert into t1 values(3,1),(4,1);
Query OK, 2 rows affected (0.08 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
+---+---+
4 rows in set (0.00 sec)
3.4删库
mysql> drop database test;
Query OK, 1 row affected (0.07 sec)
3.5将全备恢复到临时库
设置sql_log_bin=0，将之后的操作不写入binlog
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source all_3306.sql;
-- 查看数据
mysql> use test;
mysql> select * from t1;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 1 |
+---+---+
2 rows in set (0.00 sec)
3.6mysqlbinlog截取二进制日志
#在000003中，起始位置194
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000002‘, MASTER_LOG_POS=194;
#查看当前binlog位置，在000002文件
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 618
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 667ad4e0-fcc9-11eb-af3e-000c290c7222:1-5
1 row in set (0.00 sec)
#找到删除语句的位置526，stop-position会默认上一个
mysql> show binlog events in ‘/data/mysql/mysql3306/logs/mysql-bin.000002‘\G
*************************** 9. row ***************************
Log_name: mysql-bin.000002
Pos: 526
Event_type: Query
Server_id: 1
End_log_pos: 618
Info: drop database test
9 rows in set (0.00 sec)
#在原库截取二进制日志
[root@localhost ~]# mysqlbinlog --start-position=194 --stop-position=526 /data/mysql/mysql3306/logs/mysql-bin.000002 > db.sql
3.7将截取的sql，恢复到临时库进行测试
source db.sql;
mysql> select * from t1;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
+---+---+
4 rows in set (0.01 sec)
3.8将故障的库或表导出，恢复到生产
将库导出
[root@localhost ~]# mysqldump -uroot -p123456 -P 3307 --set-gtid-purged=off -B test -R -E --triggers --master-data=2 --single-transaction > test_t1.sql
生产上恢复
mysql> source test_t1.sql;
mysql> select * from test.t1;
+---+---+
| a | b |
+---+---+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
+---+---+
4 rows in set (0.00 sec)
4.关于mysqlbinlog使用
mysqlbinlog
语法：
恢复语法格式：
# mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
常用选项：
``--start-position=953 起始pos点
``--stop-position=1437 结束pos点
``--start-datetime=``"2017-6-20 13:18:54"` `起始时间点
``--stop-datetime=``"2017-6-20 13:21:53"` `结束时间点
``--database=juzidb 指定只恢复juzidb数据库(一台主机上往往有多个数据库，只限本地log日志)
不常用选项：
``-u --user=name Connect to the remote server ``as` `username.连接到远程主机的用户名
``-p --password[=name] Password to connect to remote server.连接到远程主机的密码
``-h --host=name Get the binlog ``from` `server.从远程主机上获取binlog日志
``--read-``from``-remote-server Read binary logs ``from` `a MySQL server.从某个MySQL服务器上读取binlog日志
按时间段查看
对于MIXED(部分SQL语句)和ROW模式是以base-64方式记录，SQL部分会显示为base64编码，要正常显示还得加上两个参数：
-v --base64-output=DECODE-ROWS
--database=test 指定库
mysqlbinlog -v --base64-output=DECODE-ROWS --start-datetime="2021-08-18 09:00:00" --stop-datetime="2021-08-18 12:00:00" --database=test /data/mysql/mysql3306/logs/mysql-bin.000002
show binlog events in
使用show binlog events in查看找到pos
mysql -S /data/mysql/mysql3306/mysql.sock -e "show binlog events in ‘/data/mysql/mysql3306/logs/mysql-bin.000002‘\G" > test.binlog
#太大的binlog不适合这样
语法：
show binlog events
[IN ‘log_name‘]
[FROM pos]
[LIMIT [offset,] row_count];
选项解析：
IN ‘log_name‘ 指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos 指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset,] 偏移量(不指定就是0)
row_count 查询总条数(不指定就是所有行)
示例;
A.查询第一个(即mysql-bin.000001)的binlog日志：
mysql> show binlog events\G
B.指定查询 mysql-bin.00002 这个文件:
mysql> show binlog events ``in` `‘mysql-bin.000002‘``\G
C.指定查询 mysql-bin.000002 这个文件，从pos点:534620开始查起：
mysql> show binlog events ``in` `‘mysql-bin.000002‘` `from` `534620\G
D.指定查询 mysql-bin.000002 这个文件，从pos点:534620开始查起，查询10条
mysql> show binlog events ``in` `‘mysql-bin.000002‘` `from` `534620 limit 10\G
E.指定查询 mysql-bin.000002 这个文件，从pos点:534620开始查起，偏移2行，查询10条
mysql> show binlog events ``in` `‘mysql-bin.000002‘` `from` `534620 limit 2,10\G
原文：https://www.cnblogs.com/shipment/p/15156351.html