mysqldump备份

目录

• 1.mysqldump备份原理
  • 1.1备份类型
  • 1.2备份方式
  • 1.3逻辑备份和物理备份的比较
• 2.mysqldump常用参数
  • 基本参数
  • 备份示例
  • 备份后压缩
• 3.mysqldump恢复示例
  • 3.1准备昨天23点的全备
  • 3.2准备临时库
  • 3.3模拟昨天23点到10点之间的数据变化
  • 3.4删库
  • 3.5将全备恢复到临时库
  • 3.6mysqlbinlog截取二进制日志
  • 3.7将截取的sql，恢复到临时库进行测试
  • 3.8将故障的库或表导出，恢复到生产
• 4.关于mysqlbinlog使用
  • mysqlbinlog
  • show binlog events in
• 5.根据mysqldump全备中获取库和表的备份
• 6.binlog的备份

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

参数名	缩写	含义	默认值
--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就是起始位置(为2注释这一行，不然影响手动重构主从)

--single-transaction 触发一致性备份(一个事务REPEATABLE READ中导出数据，确保产生一致性的备份数据，相当于一次性快照,对innodb能确保一致性，)

--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

5.根据mysqldump全备中获取库和表的备份

1、获得表结构
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql

2、获得INSERT INTO 语句，用于数据的恢复

# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &

3.获取单库的备份

# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql

6.binlog的备份

nohup mysqlbinlog -h192.168.190.128 -uroot -p123456 -P3306 -R --raw --stop-never mysql-bin.000001 &

异步备份；在主从环境中，推荐连接到主库备份主库的binlog

参数说明:

​ -u -p -h -P 连接指定的数据库

​ -R或--read-from-remote-server：开启binlog备份，并在指定的节点上拷贝binlog到本地

​ --raw：被拷贝过来的binlog保存方式是二进制方式

​ --stop-never：持续从指定节点上拉取binlog

​ mysql-bin.000001：从哪个binlog开始备份

停止备份：

ps -ef |grep mysqlbinlog

kill 进程号