mysqldump+binlog+gtid 实现数据库的增量备份

文章目录

• mysqldump备份数据库
• • 高级备份参数：
  • binlog日志的GTID新特性
  • • 示例：演示跨binlog文件截取日志。

mysqldump备份数据库

完全备份+增加备份，速度相对较慢，适合中小型数据库、MyISAM是温备份，InnoDB是热备份

mysqldump 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级 时相对比较合适，这也是最常用的备份方法。

备份策略可以这么定：第一次完全备份，每天一次增量备份，每周再做一次完全备份，如此一直重复。而对于重要的且繁忙的系统来说，则可能需要每天一次全量备份，每小时一次增量备份，甚至更频繁。为了不影响线 上业务，实现在线备份，并且能增量备份，最好的办法就是采用主从复制机制(replication)，在 slave 机器上做备份。

1. 示例一：备份恢复单个数据库

   备份：mydqldump -u 用户 –p’密码’ –default-character-set=Iatin1 数据库名 [|gzip] > 备份文件名

   示例：mysqldump -uroot -p'123456' --default-character-set=utf8 -B school > /tmp/school.$(date +%F).sql

   注意：-B 作用：创建数据库和切换到数据库，恢复时不用创建数据库和删表。备份多个库，-B 数据库1 数 据库2 …

   恢复：

   1）MySQL中用source命令

   source /backup/db/school.sql

   2）mysql命令恢复

   mysql -uroot -p123456 company < /backup/db/school.sql

2. 示例二：备份恢复单个表

   备份：mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名

   示例：mysqldump -uroot -p school Books >/tmp/Books.$(date+%F).sql

   备份多个表：mysqldump -u 用户名 -p 数据库名 表名1 表名2 > 备份的文件名

3. 示例三：备份数据结构

   -d只备份库结构，不包含数据内容

4. 示例四：增量备份

   前提：

   1）my.cnf，是要开启MySQL log-bin日志功能，重启MySQL log_bin = /data/mysql/data/mysql-bin

   2）存在一个完全备份，生产环境一般凌晨某个时刻进行全备

   示例：mysqldump -uroot -p --default-character-set=utf8 --single-transaction -F -B school |gzip > /server/backup/school_$(date +%F).sql.gz InnoDB 表在备份时，通常启用选项 --single-transaction 来保证备份的一致性

   MySQL增量恢复案例图解:

   

   准备数据库和表：

   create database it DEFAULT CHARACTER SET utf8;  
   

   学生表：Student（Sno,Sname,Ssex,Ssage,Ssdept）(学号-主键，姓名，性别，年龄，所在系)：

   CREATE TABLE `Student` ( `Sno` int(10) NOT NULL COMMENT '学号', `Sname` varchar(16) NOT NULL COMMENT '姓名', `Ssex` char(2) NOT NULL COMMENT '性别', `Sage` tinyint(2) NOT NULL DEFAULT '0' COMMENT '学生年龄', `Sdept` varchar(16) DEFAULT 'NULL' COMMENT '学生所在系别', PRIMARY KEY (`Sno`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; 
   

   表中插入数据：

   INSERT INTO `Student` VALUES (1, '陆亚', '男', 24, '计算机网络'),(2, 'tom', '男', 26, '英语'),(3, '张阳', '男', 21, '物流管理'), (4, 'alex', '女', 22, '电子商务'); 
   

   半夜零点手工全备:

   mysqldump -uroot -p123456 -F -B it --default-character-set=utf8 --singletransaction -e | gzip > /server/backup/mysql_backup_`date +%F`.sql.gz 
   

   备份后继续插入数据：

   sleep 60
   INSERT INTO Student values(0005,'xumubin','男',29,'中文专业'), (0006,'wangzhao','男',21,'导弹专业'); 
   

   模拟用户破坏数据：

   sleep 30
   drop database it; 
   

   增量备份–恢复过程

   1、检查凌晨备份

   2、检查全备后的所有binlog

    ls -lrt /usr/local/mysql/data/mysql-bin.* 
   

   3、立即刷新并备份出binlog

   mysqladmin -uroot -p flush-logs 
   cp /usr/local/mysql/data/mysql-bin.000004 /server/backup/ 
   #  提示：根据时间点及前一个binlog可以知道发现问题时刻前binlog日志为mysql-bin.000004 
   

   4、恢复binlog生成sql语句

   mysqlbinlog mysql-bin.000004 > bin.log 
   

   5、恢复凌晨备份

   6、恢复增量备份

   mysqlbinlog增量恢复方式

   基于时间点恢复

   1）指定开始时间到结束时间

   myslbinlog mysqlbin.000008 --start-datetime=’2014-10-45 01:10:46’ --stopdatetime=’2014-10-45 03:10:46’-r time.sql 
   

   2）指定开始时间到文件结束

   myslbinlog mysqlbin.000008 --start-datetime=’2014-10-45 01:10:46’ -d esen -r time.sql 
   

   3）从文件开头到指定结束时间

   myslbinlog mysqlbin.000008 --stop-datetime=’2014-10-45 03:10:46’ -d esen -r time.sql 
   

   基于位置点的增量恢复

   1）指定开始位置到结束位置

   myslbinlog mysqlbin.000008 --start-position=510 --stop-position=1312 -r pos.sql 
   

   2）指定开始位置到文件结束

   myslbinlog mysqlbin.000008 --start-position=510 -r pos.sql 
   

   3）从文件开始位置到指定结束位置

   myslbinlog mysqlbin.000008 --stop-position=1312 -r pos.sq
   

高级备份参数：

-R		备份存储过程及函数 
--triggers		备份触发器 
-E		备份事件 
-F		在备份开始时,刷新一个新binlog日志 
--master-data=2		以注释的形式,保存备份开始时间点的binlog的状态信息 

示例：[root@mysql mysql]# mysqldump -uroot -p -A -R --triggers --masterdata=2 > /tmp/full.sql 

[root@mysql mysql]# grep 'CHANGE MASTER' /tmp/full.sql | head -1 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=766; 

功能：

（1）在备份时，会自动记录，二进制日志文件名和位置号

​ 0 默认值

​ 1 以change master to命令形式，可以用作主从复制

​ 2 以注释的形式记录，备份时刻的文件名+postion号

（2）自动锁表

（3）如果配合--single-transaction，只对非InnoDB表进行锁表备份，InnoDB表进行“热“备， 实际上是实现快照备份。

--single-transaction

innodb 存储引擎开启热备(快照备份)功能

master-data 可以自动加锁

（1）在不加--single-transaction ，启动所有表的温备份，所有表都锁定

（2）加上--single-transaction ,对innodb进行快照备份,对非innodb表可以实现自动锁表功能

示例: 备份必加参数 
mysqldump -uroot -p -A -R -E --triggers --master-data=2 --singletransaction --set-gtid-purged=OFF > /data/backup/full.sql 

--set-gtid-purged=auto

auto ， on ，off

使用场景:

1. --set-gtid-purged=OFF,可以使用在日常备份参数中.

   mysqldump -uroot -p -A -R -E --triggers --master-data=2 --singletransaction --set-gtid-purged=OFF >/data/backup/full.sql 
   

2. auto , on:在构建主从复制环境时需要的参数配置

   mysqldump -uroot -p -A -R -E --triggers --master-data=2 --singletransaction --set-gtid-purged=ON >/data/backup/full.sql 
   

--max-allowed-packet=# 

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --singletransaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql 

--max-allowed-packet=# 

The maximum packet length to send to or receive from server.

binlog日志的GTID新特性

什么是GTID？

GTID(Global Transaction ID)，全局事务标识符。是对于一个已提交事务的编号，并且是一个全局唯一 的编号。它是MySQL 5.6加入的一个强大特性，目的在于能够实现主从自动定位和切换，而不像以前需要指定文件和位置。

GTID的格式与存储？

1. 单个GTID

   GTID与主库上提交的每个事务相关联。此标识符不仅对发起事务的库是唯一的，而且在给定复制拓扑中的所有库中都是唯一的。GTID用冒号分隔的一对坐标表示，例如：

   8eed0f5b-6f9b-11e9-94a9-005056a57a4e:23
   前一部分是主库的server_uuid，后面一部分是主库上按提交事务的顺序确定的序列号，提交的事务序号从1
   开始。
   GTID = server_uuid ：transaction_id
   

2. GTID集

   GTID集是包括一个或多个单个GTID或GTID范围的集合。源自同一服务器的一系列GTID可以折叠为单个表达式，例如：

   8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-321

   上面的示例表示源自server_uuid为8eed0f5b-6f9b-11e9-94a9-005056a57a4e服务器的1-321个事务。源自同一服务器的多个单GTID或GTID范围可以同时包含在由冒号分隔的单个表达式中，例如： 8eed0f5b-6f9b-11e9-94a9-005056a57a4e:1-3:11:47-49

3. mysql.gtid_executed表

   mysql.gtid_executed表结构如下：

   mysql> desc mysql.gtid_executed;
   +----------------+------------+------+-----+---------+-------+
   | Field | Type | Null | Key | Default | Extra |
   +----------------+------------+------+-----+---------+-------+
   | source_uuid | char(36) | NO | PRI | NULL | |
   | interval_start | bigint(20) | NO | PRI | NULL | |
   | interval_end | bigint(20) | NO | | NULL | |
   +----------------+------------+------+-----+---------+-------+
   

   mysql.gtid_executed表记录的是服务器上已经执行事务的GTID。三个字段分别表示发起事务的服务器UUID、UUID集的起始和结束事务ID。对于单个GTID，后两个字段的值相同。

如何开启？

vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true

重启服务查看：

mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name | Value |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_next | AUTOMATIC |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

DDL和DML语句查看gtid

# DDL一个语句产生一个gtid
mysql> create database db3;
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------
------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------
------------------------+
| mysql-bin.000013 | 310 | | | 6181523d-bc2e11ea-a78b-000c29221146:1 |
+------------------+----------+--------------+------------------+----------------
------------------------+
1 row in set (0.00 sec)

mysql> use db3
Database changed

mysql> create table t1 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------
--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------
--------------------------+
| mysql-bin.000013 | 471 | | | 6181523d-bc2e11ea-a78b-000c29221146:1-2 |
+------------------+----------+--------------+------------------+----------------
--------------------------+
1 row in set (0.00 sec)

mysql> create table t2 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------
--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------
--------------------------+
| mysql-bin.000013 | 632 | | | 6181523d-bc2e11ea-a78b-000c29221146:1-3 |
+------------------+----------+--------------+------------------+----------------
--------------------------+
1 row in set (0.00 sec)

mysql> create table t3 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------
--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------
--------------------------+
| mysql-bin.000013 | 793 | | | 6181523d-bc2e11ea-a78b-000c29221146:1-4 |
+------------------+----------+--------------+------------------+----------------
--------------------------+
1 row in set (0.00 sec)

# DML一个事务产生一个gtid

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------
--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------
--------------------------+
| mysql-bin.000013 | 1128 | | | 6181523d-bc2e11ea-a78b-000c29221146:1-5 |
+------------------+----------+--------------+------------------+----------------
--------------------------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+----------------
--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------
--------------------------+
| mysql-bin.000013 | 1379 | | | 6181523d-bc2e11ea-a78b-000c29221146:1-6 |
+------------------+----------+--------------+------------------+----------------
--------------------------+
1 row in set (0.00 sec)

基于GTID进行查看binlog

mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000013';
+------------------+------+----------------+-----------+-------------+-----------
--------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info
|
+------------------+------+----------------+-----------+-------------+-----------
--------------------------------------------------------+
| mysql-bin.000013 | 4 | Format_desc | 123 | 123 | Server
ver: 5.7.14-log, Binlog ver: 4 |
| mysql-bin.000013 | 123 | Previous_gtids | 123 | 154 |
|
| mysql-bin.000013 | 154 | Gtid | 123 | 219 | SET
@@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:1' |
| mysql-bin.000013 | 219 | Query | 123 | 310 | create
database db3 |
| mysql-bin.000013 | 310 | Gtid | 123 | 375 | SET
@@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:2' |
| mysql-bin.000013 | 375 | Query | 123 | 471 | use
`db3`; create table t1 (id int) |
| mysql-bin.000013 | 471 | Gtid | 123 | 536 | SET
@@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:3' |
| mysql-bin.000013 | 536 | Query | 123 | 632 | use
`db3`; create table t2 (id int) |
| mysql-bin.000013 | 632 | Gtid | 123 | 697 | SET
@@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:4' |
| mysql-bin.000013 | 697 | Query | 123 | 793 | use
`db3`; create table t3 (id int) |
| mysql-bin.000013 | 793 | Gtid | 123 | 858 | SET
@@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:5' |
| mysql-bin.000013 | 858 | Query | 123 | 929 | BEGIN
|
| mysql-bin.000013 | 929 | Table_map | 123 | 973 | table_id:
108 (db3.t1) |
| mysql-bin.000013 | 973 | Write_rows | 123 | 1013 | table_id:
108 flags: STMT_END_F |
| mysql-bin.000013 | 1013 | Table_map | 123 | 1057 | table_id:
108 (db3.t1) |
| mysql-bin.000013 | 1057 | Write_rows | 123 | 1097 | table_id:
108 flags: STMT_END_F |
| mysql-bin.000013 | 1097 | Xid | 123 | 1128 | COMMIT /*
xid=21 */ |
| mysql-bin.000013 | 1128 | Gtid | 123 | 1193 | SET
@@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:6' |
| mysql-bin.000013 | 1193 | Query | 123 | 1264 | BEGIN
|
| mysql-bin.000013 | 1264 | Table_map | 123 | 1308 | table_id:
109 (db3.t2) |
| mysql-bin.000013 | 1308 | Write_rows | 123 | 1348 | table_id:
109 flags: STMT_END_F |
| mysql-bin.000013 | 1348 | Xid | 123 | 1379 | COMMIT /*
xid=26 */ |
+------------------+------+----------------+-----------+-------------+-----------
--------------------------------------------------------+
22 rows in set (0.00 sec)

具备GTID后,截取查看某些事务日志: --include-gtids --exclude-gtids

示例：演示跨binlog文件截取日志。

1. 第一次操作：

   mysql> show master status;
   +------------------+----------+--------------+------------------+----------------
   --------------------------+
   | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   Executed_Gtid_Set |
   +------------------+----------+--------------+------------------+----------------
   --------------------------+
   | mysql-bin.000013 | 1379 | | | 6181523d-bc2e11ea-a78b-000c29221146:1-6 |
   +------------------+----------+--------------+------------------+----------------
   --------------------------+
   1 row in set (0.00 sec)
   
   mysql> create database gtid;
   Query OK, 1 row affected (0.01 sec)
   
   mysql> use gtid
   Database changed
   
   mysql> create table t1(id int);
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> begin;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> insert into t1 values(1);
   Query OK, 1 row affected (0.00 sec)
   
   mysql> commit;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> flush logs;
   Query OK, 0 rows affected (0.00 sec)
   

2. 第二次操作：

   mysql> create table t2(id int);
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> begin;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> insert into t2 values(1);
   Query OK, 1 row affected (0.00 sec)
   
   mysql> commit;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> flush logs;
   Query OK, 0 rows affected (0.01 sec)
   

3. 第三次操作：

   mysql> create table t3(id int);
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> begin;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> insert into t3 values(1);
   Query OK, 1 row affected (0.00 sec)
   
   mysql> commit;
   Query OK, 0 rows affected (0.01 sec)
   
   mysql> drop database gtid;
   Query OK, 3 rows affected (0.01 sec)
   

   使用binlog日志恢复误删除的gitd数据库。

   首先要确定gtid的起始和结束。

4. mysql> show binlog events in 'mysql-bin.000013';
   ...
   | mysql-bin.000013 | 1379 | Gtid | 123 | 1444 | SET
   @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:7' |
   | mysql-bin.000013 | 1444 | Query | 123 | 1538 | create
   database gtid
   ...
   开始：
   文件：mysql-bin.000013
   gtid：'6181523d-bc2e-11ea-a78b-000c2921146:7'
   

   mysql> show master status;
   +------------------+----------+--------------+------------------+----------------
   ---------------------------+
   | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   Executed_Gtid_Set |
   +------------------+----------+--------------+------------------+----------------
   ---------------------------+
   | mysql-bin.000015 | 766 | | | 6181523d-bc2e11ea-a78b-000c29221146:1-14 |
   +------------------+----------+--------------+------------------+----------------
   ---------------------------+
   1 row in set (0.00 sec)
   
   mysql> show binlog events in 'mysql-bin.000015';
   +------------------+-----+----------------+-----------+-------------+------------
   --------------------------------------------------------+
   | Log_name | Pos | Event_type | Server_id | End_log_pos | Info
   |
   +------------------+-----+----------------+-----------+-------------+------------
   --------------------------------------------------------+
   | mysql-bin.000015 | 4 | Format_desc | 123 | 123 | Server
   ver: 5.7.14-log, Binlog ver: 4 |
   | mysql-bin.000015 | 123 | Previous_gtids | 123 | 194 | 6181523dbc2e-11ea-a78b-000c29221146:1-11 |
   | mysql-bin.000015 | 194 | Gtid | 123 | 259 | SET
   @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:12' |
   | mysql-bin.000015 | 259 | Query | 123 | 356 | use
   `gtid`; create table t3(id int) |
   | mysql-bin.000015 | 356 | Gtid | 123 | 421 | SET
   @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:13' |
   | mysql-bin.000015 | 421 | Query | 123 | 493 | BEGIN
   |
   | mysql-bin.000015 | 493 | Table_map | 123 | 538 | table_id:
   112 (gtid.t3) |
   | mysql-bin.000015 | 538 | Write_rows | 123 | 578 | table_id:
   112 flags: STMT_END_F |
   | mysql-bin.000015 | 578 | Xid | 123 | 609 | COMMIT /*
   xid=50 */ |
   | mysql-bin.000015 | 609 | Gtid | 123 | 674 | SET
   @@SESSION.GTID_NEXT= '6181523d-bc2e-11ea-a78b-000c29221146:14' |
   | mysql-bin.000015 | 674 | Query | 123 | 766 | drop
   database gtid |
   +------------------+-----+----------------+-----------+-------------+------------
   --------------------------------------------------------+
   11 rows in set (0.00 sec)
   
   确定结束：
   文件：mysql-bin.000015
   gtid：'6181523d-bc2e-11ea-a78b-000c29221146:14'
   

   其次，binlog使用gtid截取日志

   确定起始范围：7-13

   文件：mysql-bin.000013 mysql-bin.000014 mysql-bin.000015

   [root@mysql ~]# cd /var/lib/mysql
   [root@mysql mysql]# mysqlbinlog --skip-gtids --include-gtids='6181523d-bc2e-11ea-a78b000c29221146:7-13' mysql-bin.000013 mysql-bin.000014 mysql-bin.000015 -r /tmp/gtid1.sql
   
   --skip-gtids    GTID的幂等性
   开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了
   接着上面，截取日志时添加--skip-gtids。
   

   最后，使用binlog日志恢复

   mysql> set sql_log_bin=0; #设为0后，在Master数据库上执行的语句都不记录binlog
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> source /tmp/gtid1.sql
   
   mysql> set sql_log_bin=1;
   Query OK, 0 rows affected (0.00 sec)
   
   mysql> show databases like 'gtid';
   Empty set (0.00 sec)