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.压缩比低，需要更多磁盘空间
建议：
>50G<TB

2.mysqldump常用参数

官网参数查询：https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

基本参数

-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

mysqldump备份