一、二进制日志简介
备份一般采取全库备份加日志备份的方式。例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在MySQL故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。
1.binlog简介
二进制日志记录着该数据库的所有增删改的操作(前提是要在自己的服务器上开启binlog)，还包括这些操作的执行时间。为了显示这些二进制内容，可以使用mysqlbinlog命令查看。
2.binlog的用途
(1)主从同步
(2)恢复数据库
3.开启binary log功能
通过编辑my.cnf文件开启二进制日志。
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | OFF |
+---------------+-------+
1 row in set (0.04 sec)
mysql> exit
Bye
[root@mysql ~]# vim /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
pid-file=/usr/local/mysql/data/mysqld.pid
log-error=/usr/local/mysql/data/mysqld.err
socket=/tmp/mysql.sock
log_bin=mysql-bin
server_id=1
[root@mysql ~]# systemctl restart mysqld
[root@mysql data]# pwd
/usr/local/mysql/data
[root@mysql data]# ls
auto.cnf ib_buffer_pool mysql performance_schema sys
ca-key.pem ibdata1 mysql-bin.000001 private_key.pem
ca.pem ib_logfile0 mysql-bin.index public_key.pem
client-cert.pem ib_logfile1 mysqld.err server-cert.pem
client-key.pem ibtmp1 mysqld.pid server-key.pem
只写log_bin，不写后面的文件名和路径时，默认存放在/usr/local/mysql/data目录下，文件名为主机名-bin.000001命名。每次重启mysql服务或执行mysql> flush logs;命令都会生成一个新的二进制日志文件，这些日志文件的number会不断地递增。除了生成上述的文件外还会生成一个名为filename.index的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。配置保存以后重启mysql服务。
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.01 sec)
4.查看产生的binary log
注意：查看binlog内容是为了恢复数据，bin-log因为是二进制文件，不能通过文件内容查看命令直接打开查看，mysql提供两种查看方式。
先对数据库进行一下增删改的操作，否则log里面数据有点空。
mysql> reset master; #清空所有的二进制文件，从000001开始;
Query OK, 0 rows affected (0.00 sec)
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table tb1(
-> id int primary key auto_increment,
-> name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into tb1(name) values('lisi');
Query OK, 1 row affected (0.04 sec)
mysql> insert into tb1(name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)
5.重新开始一个新的日志文件
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> delete from tb1 where id=2;
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1(name) values('tom');
Query OK, 1 row affected (0.01 sec)
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | lisi |
| 3 | tom |
+----+------+
2 rows in set (0.00 sec)
6.查看MySQL服务器上的二进制文件
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 1097 |
| mysql-bin.000002 | 681 |
+------------------+-----------+
2 rows in set (0.00 sec)
7.查看二进制日志信息的命令
语法格式
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
(1)查看二进制日志信息(默认查看第一个二进制日志)
mysql> show binlog events;
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.32-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 | 313 | create database test |
| mysql-bin.000001 | 313 | Anonymous_Gtid | 1 | 378 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 378 | Query | 1 | 522 | use `test`; create table tb1(
id int primary key auto_increment,
name varchar(20)) |
| mysql-bin.000001 | 522 | Anonymous_Gtid | 1 | 587 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 587 | Query | 1 | 659 | BEGIN |
| mysql-bin.000001 | 659 | Table_map | 1 | 708 | table_id: 108 (test.tb1) |
| mysql-bin.000001 | 708 | Write_rows | 1 | 753 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000001 | 753 | Xid | 1 | 784 | COMMIT /* xid=11 */ |
| mysql-bin.000001 | 784 | Anonymous_Gtid | 1 | 849 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 849 | Query | 1 | 921 | BEGIN |
| mysql-bin.000001 | 921 | Table_map | 1 | 970 | table_id: 108 (test.tb1) |
| mysql-bin.000001 | 970 | Write_rows | 1 | 1019 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000001 | 1019 | Xid | 1 | 1050 | COMMIT /* xid=12 */ |
| mysql-bin.000001 | 1050 | Rotate | 1 | 1097 | mysql-bin.000002;pos=4 |
+------------------+------+----------------+-----------+-------------+------------------------------------------------------------------------------------+
17 rows in set (0.00 sec)
(2)查看指定的二进制日志中的事件
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 1 | 291 | BEGIN |
| mysql-bin.000002 | 291 | Table_map | 1 | 340 | table_id: 108 (test.tb1) |
| mysql-bin.000002 | 340 | Delete_rows | 1 | 389 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000002 | 389 | Xid | 1 | 420 | COMMIT /* xid=14 */ |
| mysql-bin.000002 | 420 | Anonymous_Gtid | 1 | 485 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 485 | Query | 1 | 557 | BEGIN |
| mysql-bin.000002 | 557 | Table_map | 1 | 606 | table_id: 108 (test.tb1) |
| mysql-bin.000002 | 606 | Write_rows | 1 | 650 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000002 | 650 | Xid | 1 | 681 | COMMIT /* xid=15 */ |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
12 rows in set (0.00 sec)
(3)包含其他选项的查看方式
mysql> show binlog events in 'mysql-bin.000002' from 219 limit 1,3; #1为偏移量;
+------------------+-----+-------------+-----------+-------------+---------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------+
| mysql-bin.000002 | 291 | Table_map | 1 | 340 | table_id: 108 (test.tb1) |
| mysql-bin.000002 | 340 | Delete_rows | 1 | 389 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000002 | 389 | Xid | 1 | 420 | COMMIT /* xid=14 */ |
+------------------+-----+-------------+-----------+-------------+---------------------------------+
3 rows in set (0.00 sec)
8.其他二进制命令
SHOW BINARY LOGS等价于SHOW MASTER LOGS
PURGE BINARY LOGS：用于删除二进制日志。
如：PURGE BINARY LOGS TO 'mysql-bin.00010'; #把这个文件之前的其他文件都删除掉;
PURGE BINARY LOGS BEFORE '2016-08-28 22:46:26'; #把指定时间之前的二进制文件删除;
RESET MASTER：清空index文件中列出的所有二进制日志，重置index文件为空，并创建一个新的二进制日志文件，一般用于MASTER首次启动时。
RESET SLAVE：使SLAVE忘记其在MASTER二进制日志文件中的复制位置，它会删除master.info、relay-log.info和所有中继日志文件并开始一个新的中继日志文件，以便于开始一个干净的复制。
注意：使用RESET SLAVE前需先关闭SLAVE复制线程。
上述方式可以查看到服务器上存在的二进制日志文件及文件中的事件，但是想查看到文件中具体的内容并应用于恢复场景还得借助mysqlbinlog这个工具。
9.数据恢复
语法格式
mysqlbinlog [options] log-files
输出内容会因日志文件的格式以及mysqlbinlog工具使用的选项不同而不同。
mysqlbinlog的可用选项可参考man手册。二进制日志文件的格式包含行模式、语句模式和混合模式(也即有服务器决定在什么情况下记录什么类型的日志)，基于语句的日志中事件信息包含执行的语句等，基于行的日志中事件信息包含的是行的变化信息等。混合模式的日志中两种类型的事件信息都会记录。
为了便于查看记录了行变化信息的事件在当时具体执行了什么样的SQL语句可以使用mysqlbinlog工具的-v(–verbose)选项，该选项会将行事件重构成被注释掉的伪SQL语句，如果想看到更详细的信息可以将该选项给两次如-vv，这样可以包含一些数据类型和元信息的注释内容。
[root@mysql data]# mysqlbinlog mysql-bin.000001
...
at 849
#210121 19:41:46 server id 1 end_log_pos 921 CRC32 0x2f70dd18 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1611229306/*!*/;
BEGIN
/*!*/;
# at 921
#210121 19:41:46 server id 1 end_log_pos 970 CRC32 0x3f748331 Table_map: `test`.`tb1` mapped to number 108
# at 970
#210121 19:41:46 server id 1 end_log_pos 1019 CRC32 0xeeebcf43 Write_rows: table id 108 flags: STMT_END_F
BINLOG '
emgJYBMBAAAAMQAAAMoDAAAAAGwAAAAAAAEABHRlc3QAA3RiMQACAw8CPAACMYN0Pw==
emgJYB4BAAAAMQAAAPsDAAAAAGwAAAAAAAEAAgAC//wCAAAACHpoYW5nc2FuQ8/r7g==
'/*!*/;
# at 1019
#210121 19:41:46 server id 1 end_log_pos 1050 CRC32 0xe7679adb Xid = 12
COMMIT/*!*/;
...
加一个v选项
[root@mysql data]# mysqlbinlog -v mysql-bin.000001
...
# at 849
#210121 19:41:46 server id 1 end_log_pos 921 CRC32 0x2f70dd18 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1611229306/*!*/;
BEGIN
/*!*/;
# at 921
#210121 19:41:46 server id 1 end_log_pos 970 CRC32 0x3f748331 Table_map: `test`.`tb1` mapped to number 108
# at 970
#210121 19:41:46 server id 1 end_log_pos 1019 CRC32 0xeeebcf43 Write_rows: table id 108 flags: STMT_END_F
BINLOG '
emgJYBMBAAAAMQAAAMoDAAAAAGwAAAAAAAEABHRlc3QAA3RiMQACAw8CPAACMYN0Pw==
emgJYB4BAAAAMQAAAPsDAAAAAGwAAAAAAAEAAgAC//wCAAAACHpoYW5nc2FuQ8/r7g==
'/*!*/;
### INSERT INTO `test`.`tb1`
### SET
### @1=2
### @2='zhangsan'
# at 1019
#210121 19:41:46 server id 1 end_log_pos 1050 CRC32 0xe7679adb Xid = 12
COMMIT/*!*/;
...
加两个v
[root@mysql data]# mysqlbinlog -vv mysql-bin.000001
...
# at 849
#210121 19:41:46 server id 1 end_log_pos 921 CRC32 0x2f70dd18 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1611229306/*!*/;
BEGIN
/*!*/;
# at 921
#210121 19:41:46 server id 1 end_log_pos 970 CRC32 0x3f748331 Table_map: `test`.`tb1` mapped to number 108
# at 970
#210121 19:41:46 server id 1 end_log_pos 1019 CRC32 0xeeebcf43 Write_rows: table id 108 flags: STMT_END_F
BINLOG '
emgJYBMBAAAAMQAAAMoDAAAAAGwAAAAAAAEABHRlc3QAA3RiMQACAw8CPAACMYN0Pw==
emgJYB4BAAAAMQAAAPsDAAAAAGwAAAAAAAEAAgAC//wCAAAACHpoYW5nc2FuQ8/r7g==
'/*!*/;
### INSERT INTO `test`.`tb1`
### SET
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2='zhangsan' /* VARSTRING(60) meta=60 nullable=1 is_null=0 */
# at 1019
#210121 19:41:46 server id 1 end_log_pos 1050 CRC32 0xe7679adb Xid = 12
COMMIT/*!*/;
...
mysqlbinlog还可以通过–read-from-remote-server选项从远程服务器读取二进制日志文件，这时需要一些额外的连接参数，如-h，-P，-p，-u等，这些参数仅在指定了–read-from-remote-server后有效。
二、mysqlbinlog恢复数据
无论是本地二进制日志文件还是远程服务器上的二进制日志文件，无论是行模式、语句模式还是混合模式的二进制日志文件，被mysqlbinlog工具解析后都可直接应用于MySQL Server进行基于时间点、位置或数据库的恢复。
下面演示如何使用binlog恢复之前删除的数据(id=2那条记录)。
注意：在实际生产环境中，如果遇到需要恢复数据库的情况，不要让用户能访问到数据库，以避免新的数据插入进来，以及在主从的环境下，关闭主从。
1.查看binlog文件，从中找出delete from test.tb1 where id=2。
[root@mysql data]# mysqlbinlog -v mysql-bin.000002
...
# at 219
#210121 19:42:47 server id 1 end_log_pos 291 CRC32 0xa53e5648 Query thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1611229367/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 291
#210121 19:42:47 server id 1 end_log_pos 340 CRC32 0xd1ef2b48 Table_map: `test`.`tb1` mapped to number 108
# at 340
#210121 19:42:47 server id 1 end_log_pos 389 CRC32 0x7ae5cb25 Delete_rows: table id 108 flags: STMT_END_F
BINLOG '
t2gJYBMBAAAAMQAAAFQBAAAAAGwAAAAAAAEABHRlc3QAA3RiMQACAw8CPAACSCvv0Q==
t2gJYCABAAAAMQAAAIUBAAAAAGwAAAAAAAEAAgAC//wCAAAACHpoYW5nc2FuJcvleg==
'/*!*/;
### DELETE FROM `test`.`tb1`
### WHERE
### @1=2
### @2='zhangsan'
# at 389
#210121 19:42:47 server id 1 end_log_pos 420 CRC32 0xaa898d93 Xid = 14
COMMIT/*!*/;
...
可以看出delete事件发生的position是219，事件结束的position是420。
直接用bin-log日志将数据库恢复到删除位置219之前，然后跳过故障点，再进行恢复下面所有的操作。
由于之前没有做过全库备份，所以要使用所有binlog日志恢复，所以生产环境中需要很长时间恢复，导出相关binlog文件。
[root@mysql data]# mysqlbinlog mysql-bin.000001 > /opt/mysql-bin.000001.sql
[root@mysql data]# mysqlbinlog --stop-position=219 mysql-bin.000002 > /opt/219.sql
[root@mysql data]# mysqlbinlog --start-position=420 mysql-bin.000002 > /opt/420.sql
[root@mysql data]# ls /opt/
219.sql 420.sql mysql-bin.000001.sql rh
2.删除test数据库
mysql> drop database test;
Query OK, 1 row affected (0.01 sec)
3.利用binlog恢复数据
[root@mysql data]# mysql -uroot -pasd123 < /opt/mysql-bin.000001.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql data]# mysql -uroot -pasd123 < /opt/219.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@mysql data]# mysql -uroot -pasd123 < /opt/420.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
4.恢复完成后，检查表的数据是否完整
mysql> select * from test.tb1;
+----+----------+
| id | name |
+----+----------+
| 1 | lisi |
| 2 | zhangsan |
| 3 | tom |
+----+----------+
3 rows in set (0.00 sec)
可以看到数据完整的恢复了。
5.mysqlbinlog选项示例
常见选项
--start-datetime：从二进制日志中读取指定时间戳或者本地计算机时间之后的日志事件。
--stop-datetime：从二进制日志中读取指定时间戳或者本地计算机时间之前的日志事件。
--start-position：从二进制日志中读取指定position事件位置作为开始。
--stop-position：从二进制日志中读取指定position事件位置作为截止。
6.关于两个delete随机分布的数据恢复方法
假设对数据库进行如下操作:
insert----delete----insert----delete----insert
我们可以采用三段论的方法进行数据恢复。
第一次delete的起始点和结束点(a,b)
两次delete之间的insert的起始点与结束点(c,d)
第二次delete的起始点和结束点(e,f)
备份时进行如下操作:
[root@mysql data]# mysqlbinlog --stop-position=a mysql-bin.000002 > /tmp/a.sql
[root@mysql data]# mysqlbinlog --start-position=c --stop-position=d mysql-bin.000002 > /tmp/cd.sql
[root@mysql data]# mysqlbinlog --start-position=f mysql-bin.000002 > /tmp/f.sql
三、mysqldump介绍
mysql用于备份和数据转移的一个工具。它主要产生一系列的SQL语句，可以封装到文件，该文件包含所有重建数据库所需要的SQL命令如create database，create table，insert等。可以用来实现轻量级的快速迁移或恢复数据库。
mysqldump是将数据表导成SQL脚本文件，在不同的MySQL版本之间升级时相对比较合适，这也是最常用的备份方法。
mysqldump一般在数据量很小的时候(几个G)可以用于备份。当数据量比较大的情况下，就不建议用mysqldump工具进行备份。
1.数据库的导出
mysqldump可以针对单个表、多个表、单个数据库、多个数据库、所有数据库进行导出的操作。
mysqldump [options] db_name [tbl_name ...] #导出指定数据库或单个表
mysqldump [options] --databases db_name ... #导出多个数据库
mysqldump [options] --all-databases #导出所有
导出数据库test
mysqldump -uroot -p --flush-logs test > /opt/test.sql #--flush-logs选项就会在完整备份的时候重新开启一个新binlog
2.备份单库中的数据
注意这是备份库中的表，所以恢复时需指明数据库。
mysqldump -uroot -pasd123 test tb1 > /tmp/tb1.sql
mysql -uroot -pasd123 test < /tmp/tb1.sql
3.备份多个库中的数据
mysqldump -uroot -pasd123 --databases test test1 test2 > /tmp/test.sql
mysql -uroot -pasd123 < /tmp/test.sql
4.备份所有库的数据
mysqldump -uroot -pasd123 --all-databases > /tmp/all.sql
mysql -uroot -pasd123 < /tmp/all.sql
5.备份多库中的某个表
注意ignore-table参数表示不对指定的表备份。
mysqldump -uroot -pasd123 --databases test test1 --ignore-table=test.{tb2,tb3} --ignore-table=test1.{tb1,tb2} > /tmp/tb12.sql
mysql -uroot -pasd123 < /tmp/tb12.sql
6.备份MySQL核心库
需要重新初始化。
四、mysqldump全库备份+binlog的数据恢复
1.环境准备
检查开启binlog，先创建一些原始数据。
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)
mysql> use test_db;
Database changed
mysql> create table tb1(id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into tb1(name) values('tom1');
Query OK, 1 row affected (0.04 sec)
mysql> insert into tb1(name) values('tom2');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | tom1 |
| 2 | tom2 |
+----+------+
2 rows in set (0.00 sec)
2.备份方案(mysqldump全库备份+binlog还原)
备份方案：
每周一凌晨1点全库备份
备份步骤：
(1)创建备份目录
mkdir /opt/mysqlbackup
mkdir /opt/mysqlbackup/daily
(2)全库备份
模拟周一的完整备份数据库任务
[root@localhost data]# mysqldump -uroot -pasd123 --flush-logs test_db > /opt/mysqlbackup/test_db_`date +%Y%m%d_%H%M%S`.sql
[root@localhost data]# ll /opt/mysqlbackup/
总用量 4
drwxr-xr-x 2 root root 6 1月 29 18:19 daily
-rw-r--r-- 1 root root 1875 1月 29 18:19 test_db_20210129_181953.sql
(3)备份mysqldump全库备份之前的binlog日志文件
注意：生产环境中可能不只一个binlog文件。
cp /usr/local/mysql/data/mysql-bin.000001 /opt/mysqlbackup/daily/
mysql -uroot -pasd123 -e "purge binary logs to 'mysql-bin.000002'"
(4)模拟操作失误，将数据修改错误。
mysql> use test_db;
Database changed
mysql> delete from tb1 where id=1;
Query OK, 1 row affected (0.01 sec)
mysql> insert into tb1(name) values('tom3');
Query OK, 1 row affected (0.00 sec)
(5)备份mysqldump之后的binlog日志文件
cp /usr/local/mysql/data/mysql-bin.000002 /opt/mysqlbackup/daily/
(6)现在使用mysqldump的全库备份和binlog来恢复数据。
使用mysqldump的备份进行全库恢复
mysql -uroot -pasd123 test_db < /opt/mysqlbackup/test_db_20210129_181953.sql
(7)查询数据
mysql> select * from test_db.tb1;
+----+------+
| id | name |
+----+------+
| 1 | tom1 |
| 2 | tom2 |
+----+------+
2 rows in set (0.00 sec)
从结果可以看到使用mysqldump备份将数据还原到了备份时的状态，刚才删除的数据(id=1)恢复了，但备份后产生的数据却丢失了所以还得利用binlog进一步还原。
因为删除是在全库备份后发生的，而mysqldump全库备份时使用–flush-logs选项，所以只需要分析全库备份后的binlog即mysql-bin.000002。
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000002 | 2040 |
+------------------+-----------+
1 row in set (0.00 sec)
(8)查看mysql-bin.000002中的事件，可以看到有删除事件。
mysql> show binlog events in 'mysql-bin.000002';
...
| mysql-bin.000002 | 219 | Query | 1 | 294 | BEGIN |
| mysql-bin.000002 | 294 | Table_map | 1 | 346 | table_id: 108 (test_db.tb1) |
| mysql-bin.000002 | 346 | Delete_rows | 1 | 391 | table_id: 108 flags: STMT_END_F |
| mysql-bin.000002 | 391 | Xid | 1 | 422 | COMMIT /* xid=44 */
...
使用mysqlbinlog命令可以查看备份的binlog文件的详细事件。
(9)恢复流程
直接用bin-log日志将数据库恢复到删除位置前，然后跳过故障点，再进行恢复删除后的所有操作。
先用mysqlbinlog命令找到delete那条语句的位置。
[root@localhost data]# mysqlbinlog -v mysql-bin.000002
...
# at 219
#160911 17:19:55 server id 1 end_log_pos 294 CRC32 0x84590493 Query
thread_id=66 exec_time=0 error_code=0
SET TIMESTAMP=1473585595/*!*/;
SET @@session.pseudo_thread_id=66/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0,
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET
@@session.character_set_client=33,@@session.collation_connection=33,@@session.coll
ation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 294
#160911 17:19:55 server id 1 end_log_pos 346 CRC32 0x5cdccf9e Table_map:
`test_db`.`tb1` mapped to number 118
# at 346
#160911 17:19:55 server id 1 end_log_pos 391 CRC32 0x320c4935 Delete_rows: table
id 118 flags: STMT_END_F
BINLOG '
uyHVVxMBAAAANAAAAFoBAAAAAHYAAAAAAAEAB3Rlc3RfZGIAA3RiMQACAw8CPAACns/cXA==
uyHVVyABAAAALQAAAIcBAAAAAHYAAAAAAAEAAgAC//wBAAAABHRvbTE1SQwy
'/*!*/;
### DELETE FROM `test_db`.`tb1`
### WHERE
### @1=1
### @2='tom1'
# at 391
#160911 17:19:55 server id 1 end_log_pos 422 CRC32 0x5e4a6699 Xid = 2739
COMMIT/*!*/;
...
可以看到误操作delete的开始postion为219，结束position是422。从二进制日志中读取指定position=219事件位置作为截止，即把数据恢复到delete删除前。
mysqlbinlog --stop-position=219 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -uroot -pasd123
从二进制日志中读取指定position=422事件位置作为开始，即跳过删除事件，恢复删除事件之后对数据的正常操作。
mysqlbinlog --start-position=422 /opt/mysqlbackup/daily/mysql-bin.000002 | mysql -uroot -pasd123
(10)查看恢复结果
mysql> select * from test_db.tb1;
+----+------+
| id | name |
+----+------+
| 1 | tom1 |
| 2 | tom2 |
| 3 | tom3 |
+----+------+
3 rows in set (0.00 sec)
可以看出数据恢复到正常状态。
生产环境中mysql数据库的备份是周期性重复的操作，通常是要编写脚本实现，通过crond计划任务周期性执行备份脚本。
3.mysqldump备份方案
周日凌晨1点全库备份
周一到周六凌晨每隔4个小时增量备份一次
(1)设置crontab任务，每天执行备份脚本。
crontab –e
#每个星期日凌晨1:00执行完全备份脚本
0 1 * * 0 /root/mysqlfullbackup.sh >/dev/null 2>&1
#周一到周六每隔4个小时增量备份一次
0 */4 * * 1-6 /root/mysqldailybackup.sh >/dev/null 2>&1
(2)mysqlfullbackup.sh脚本内容
[root@localhost ~]# vim mysqlfullbackup.sh
#!/bin/sh
#定义数据库目录
mysqlDir=/usr/local/mysql
#定义用于备份数据库的用户名和密码
user=root
userpwd=asd123
dbname=test_db
#定义备份目录
databackupdir=/opt/mysqlbackup
[ ! -d $databackupdir ] && mkdir $databackupdir
#定义邮件正文文件
emailfile=$databackupdir/email.txt
#定义邮件地址
email=root@localhost.localdomain
#定义备份日志文件
logfile=$databackupdir/mysqlbackup.log
DATE=`date -I`
echo "" > $emailfile
echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
cd $databackupdir
#定义备份文件名
dumpfile=mysql_$DATE.sql
gzdumpfile=mysql_$DATE.sql.tar.gz
#使用mysqldump备份数据库，根据具体情况设置参数
$mysqlDir/bin/mysqldump -u$user -p$userpwd --flush-logs -x $dbname > $dumpfile
#压缩备份文件
if [ $? -eq 0 ];then
tar zcf $gzdumpfile $dumpfile >> $emailfile 2>&1
echo "BackupFileName:$gzdumpfile" >> $emailfile
echo "DataBase Backup Success!" >> $emailfile
rm -f $dumpfile
else
echo "DataBase Backup Fail!" >> $emailfile
fi
#写日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile
#发送邮件通知
cat $emailfile | mail -s "MySQL Backup" $email
(3)mysqldailybackup.sh脚本内容
[root@localhost ~]# vim mysqldailybackup.sh
#!/bin/sh
#定义数据库目录和数据目录
mysqldir=/usr/local/mysql
datadir=$mysqldir/data
#定义用于备份数据库的用户名和密码
user=root
userpwd=asd123
#定义备份目录，每日备份文件备份到$dataBackupDir/daily
databackupdir=/opt/mysqlbackup
dailybackupdir=$databackupdir/daily
[ ! -d $dailybackupdir ] && mkdir -p $databackupdir/daily
#定义邮件正文文件
emailfile=$databackupdir/email.txt
#定义邮件地址
email=root@localhost.localdomain
#定义日志文件
logfile=$databackupdir/mysqlbackup.log
echo "" > $emailfile
echo $(date +"%y-%m-%d %H:%M:%S") >> $emailfile
#刷新日志，使数据库使用新的二进制日志文件
$mysqldir/bin/mysqladmin -u$user -p$userpwd flush-logs
cd $datadir
#得到二进制日志列表
filelist=`cat mysql-bin.index`
icounter=0
for file in $filelist
do
icounter=`expr $icounter + 1`
done
nextnum=0
ifile=0
for file in $filelist
do
binlogname=`basename $file`
nextnum=`expr $nextnum + 1`
#跳过最后一个二进制日志（数据库当前使用的二进制日志文件）
if [ $nextnum -eq $icounter ];then
echo "Skip lastest!" > /dev/null
else
dest=$dailybackupdir/$binlogname
#跳过已经备份的二进制日志文件
if [ -e $dest ];then
echo "Skip exist $binlogname!" > /dev/null
else
#备份日志文件到备份目录
cp $binlogname $dailybackupdir
if [ $? -eq 0 ];then
ifile=`expr $ifile + 1`
echo "$binlogname backup success!" >> $emailfile
fi
fi
fi
done
if [ $ifile -eq 0 ];then
echo "No Binlog Backup!" >> $emailfile
else
echo "Backup $ifile File(s)." >> $emailfile
echo "Backup MySQL Binlog OK!" >> $emailfile
fi
#发送邮件通知
cat $emailfile | mail -s "MySQL Backup" $email
#写日志文件
echo "--------------------------------------------------------" >> $logfile
cat $emailfile >> $logfile
五、使用xtrabackup进行MySQL数据库备份
mysqldump备份方式是采用逻辑备份，其最大的缺陷就是备份和恢复速度都慢，对于一个小于50G的数据库而言，这个速度还是能接受的，但如果数据库非常大，那再使用mysqldump备份就不太适合了。这时就需要一种好用又高效的工具，xtrabackup就是其中一款，号称免费版的InnoDB HotBackup。
Xtrabackup实现是物理备份，而且是物理热备。
目前主流的有两个工具可以实现物理热备：ibbackup和xtrabackup；ibbackup是商业软件，需要授权，非常昂贵。而xtrabackup功能比ibbackup还要强大，但却是开源的。
Xtrabackup提供了两种命令行工具：
xtrabackup：专用于备份InnoDB和XtraDB引擎的数据；
innobackupex：这是一个perl脚本，在执行过程中会调用xtrabackup命令，这样用该命令即可实现备份InnoDB，也可以备份MyISAM引擎的对象。
Xtrabackup是由percona提供的mysql数据库备份工具。
特点
(1)备份过程快速、可靠；
(2)备份过程不会打断正在执行的事务；
(3)能够基于压缩等功能节约磁盘空间和流量；
(4)自动实现备份检验；
(5)还原速度快。
官方链接地址
可以下载源码编译安装，也可以下载适合的RPM包或使用yum进行安装或者下载二进制源码包。
1.安装xtrabackup
[root@mysql ~]# rpm -qa libgcrypt #需要和libgcrypt版本相对应
libgcrypt-1.5.3-14.el7.x86_64
#由于网速原因，可以提前下载好。
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.15/binary/tarball/percona-xtrabackup-2.4.15-Linux-x86_64.libgcrypt153.tar.gz
2.解压
[root@mysql src]# pwd
/usr/local/src
[root@mysql src]# ls
percona-xtrabackup-2.4.15-Linux-x86_64.libgcrypt153.tar.gz
[root@mysql src]# tar zxf percona-xtrabackup-2.4.15-Linux-x86_64.libgcrypt153.tar.gz
3.进入解压目录复制bin下的所有程序到/usr/bin
[root@mysql src]# cd percona-xtrabackup-2.4.15-Linux-x86_64/
[root@mysql percona-xtrabackup-2.4.15-Linux-x86_64]# cp bin/* /usr/bin/
Xtrabackup中主要包含两个工具
xtrabackup：用于热备份innodb，xtradb表中数据，支持在线热备份，可以在不加锁的情况下备份Innodb数据表，不过此工具不能操作Myisam引擎表。
innobackupex：是将xtrabackup进行封装的perl脚本，能同时处理Innodb和Myisam，但在处理Myisam时需要加一个读锁。由于操作Myisam时需要加读锁，会堵塞线上服务的写操作，而Innodb没有这样的限制，所以数据库中Innodb表类型所占的比例越大越有利。
4.安装相关插件
[root@mysql ~]# yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64 perl-Digest-MD5
5.下载percona-toolkit并安装
#提前下载
[root@mysql ~]# wget https://downloads.percona.com/downloads/percona-toolkit/3.3.0/binary/redhat/7/x86_64/percona-toolkit-3.3.0-1.el7.x86_64.rpm
[root@mysql ~]# rpm -ivh percona-toolkit-3.3.0-1.el7.x86_64.rpm
6.xtrabackup完全备份+binlog增量备份
(1)创建备份目录
[root@mysql ~]# mkdir -p /opt/mysqlbackup/{full,inc}
full：全备存放的目录；
inc：增量备份存放的目录；
完全备份
基本语法
innobackupex --defaults-file=MY.CNF --user=NAME --socket=SOCKET --port=PORT --password=WORD /path/to/BACKUP-DIR/
执行下面的命令进行完全备份(socket文件需指定，不然会报错。)
[root@mysql ~]# innobackupex --defaults-file=/etc/my.cnf -S /tmp/mysql.sock --user=root --password=asd123 /opt/mysqlbackup/full
注意
–defaults-file=/etc/my.cnf指定mysql的配置文件，如果指定则必须是第一个参数。
/path/to/BACKUP-DIR/指定备份所存放的目标目录，备份过程会创建一个以当时备份时间命名的目录存放备份文件。出现如下提示表示成功。
...
210129 19:51:55 Executing UNLOCK TABLES
210129 19:51:55 All tables unlocked
210129 19:51:55 [00] Copying ib_buffer_pool to /opt/mysqlbackup/full/2021-01-29_19-51-44/ib_buffer_pool
210129 19:51:55 [00] ...done
210129 19:51:55 Backup created in directory '/opt/mysqlbackup/full/2021-01-29_19-51-44/'
MySQL binlog position: filename 'mysql-bin.000002', position '2301'
210129 19:51:55 [00] Writing /opt/mysqlbackup/full/2021-01-29_19-51-44/backup-my.cnf
210129 19:51:55 [00] ...done
210129 19:51:55 [00] Writing /opt/mysqlbackup/full/2021-01-29_19-51-44/xtrabackup_info
210129 19:51:55 [00] ...done
xtrabackup: Transaction log of lsn (2773594) to (2773603) was copied.
210129 19:51:55 completed OK!
(2)查看备份后的文件
在备份的同时，备份数据会在备份目录下创建一个以当前日期时间为名字的目录存放备份文件。
[root@mysql ~]# cd /opt/mysqlbackup/full/
[root@mysql full]# ll
总用量 0
drwxr-x--- 6 root root 238 1月 29 19:51 2021-01-29_19-51-44
[root@mysql full]# ll 2021-01-29_19-51-44/
总用量 12340
-rw-r----- 1 root root 487 1月 29 19:51 backup-my.cnf
-rw-r----- 1 root root 290 1月 29 19:51 ib_buffer_pool
-rw-r----- 1 root root 12582912 1月 29 19:51 ibdata1
drwxr-x--- 2 root root 4096 1月 29 19:51 mysql
drwxr-x--- 2 root root 8192 1月 29 19:51 performance_schema
drwxr-x--- 2 root root 8192 1月 29 19:51 sys
drwxr-x--- 2 root root 50 1月 29 19:51 test_db
-rw-r----- 1 root root 22 1月 29 19:51 xtrabackup_binlog_info
-rw-r----- 1 root root 135 1月 29 19:51 xtrabackup_checkpoints
-rw-r----- 1 root root 526 1月 29 19:51 xtrabackup_info
-rw-r----- 1 root root 2560 1月 29 19:51 xtrabackup_logfile
(3)各文件说明
xtrabackup_checkpoints
备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(日志序列号)范围信息；每个InnoDB页(通常为16k大小)都会包含一个日志序列号。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的。
[root@mysql 2021-01-29_19-51-44]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2773594
last_lsn = 2773603
compact = 0
recover_binlog_info = 0
flushed_lsn = 2773603
xtrabackup_binlog_info
mysql服务器当前正在使用的二进制日志文件及自备份这一刻为止二进制日志事件的位置。
[root@mysql 2021-01-29_19-51-44]# cat xtrabackup_binlog_info
mysql-bin.000002 2301
xtrabackup_info
二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
[root@mysql 2021-01-29_19-51-44]# cat xtrabackup_info
uuid = 6231d8c8-6228-11eb-b8ff-000c29b43d44
name =
tool_name = innobackupex
tool_command = --defaults-file=/etc/my.cnf -S /tmp/mysql.sock --user=root --password=... /opt/mysqlbackup/full
tool_version = 2.4.15
ibbackup_version = 2.4.15
server_version = 5.7.32-log
start_time = 2021-01-29 19:51:53
end_time = 2021-01-29 19:51:55
lock_time = 0
binlog_pos = filename 'mysql-bin.000002', position '2301'
innodb_from_lsn = 0
innodb_to_lsn = 2773594
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N
xtrabackup_logfile(cat查看显示乱码)
备份中用到的xtrabackup的可执行文件。
backup-my.cnf
备份命令用到的配置选项信息。
[root@mysql 2021-01-29_19-51-44]# cat backup-my.cnf
# This MySQL options file was generated by innobackupex.
# The MySQL server
[mysqld]
innodb_checksum_algorithm=crc32
innodb_log_checksum_algorithm=strict_crc32
innodb_data_file_path=ibdata1:12M:autoextend
innodb_log_files_in_group=2
innodb_log_file_size=50331648
innodb_fast_checksum=false
innodb_page_size=16384
innodb_log_block_size=512
innodb_undo_directory=./
innodb_undo_tablespaces=0
server_id=1
redo_log_version=1
server_uuid=47a57d36-2a54-11eb-a6fc-000c29b43d44
master_key_id=0
在使用innobackupex进行备份时，还可以使用- -no-timestamp选项来阻止命令自动创建一个以时间命名的目录；这样innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。
(4)相关选项说明
--user指定连接数据库的用户名;
--password指定连接数据库的密码;
--defaults-file指定数据库的配置文件，innobackupex要从其中获取datadir等信息;
--database指定要备份的数据库，这里指定的数据库只对MyISAM表有效，对于InnoDB数据来说都是全备(所有数据库中的InnoDB数据都进行了备份，不是只备份指定的数据库，恢复时也一样);
/opt/mysqlbackup/full是备份文件的存放位置;
注意：备份数据库的用户需要具有相应权限，如果要使用一个最小权限的用户进行备份，则可基于如下命令创建此类用户。
mysql> create user 'bkpuser'@'localhost' identified by 'Test123!';
Query OK, 0 rows affected (0.00 sec)
mysql> revoke all privileges,grant option from 'bkpuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> grant reload,lock tables,replication client,process on *.* to 'bkpuser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
至此全备完全成功，然后向mysql某个库插入几条数据，然后进行增量备份。
增量备份
(1)对完全备份的后数据库更改进行二进制日志增量备份，查看完全备份时binlog日志位置(position)。
[root@mysql 2021-01-29_19-51-44]# cat xtrabackup_binlog_info
mysql-bin.000002 2301
(2)模拟数据库修改
mysql> select * from test_db.tb1;
+----+------+
| id | name |
+----+------+
| 1 | tom1 |
| 2 | tom2 |
| 3 | tom3 |
+----+------+
3 rows in set (0.00 sec)
mysql> insert into tb1(name) values('tom4');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb1(name) values('tom5');
Query OK, 1 row affected (0.00 sec)
(3)增量备份二进制文件
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000002 | 3724 |
+------------------+-----------+
1 row in set (0.00 sec)
[root@mysql ~]# mysqlbinlog --start-position=2301 /usr/local/mysql/data/mysql-bin.000002 > /opt/mysqlbackup/inc/`date +%F`.sql
(4)模拟数据库损坏
这里直接使用删除数据目录文件来模拟损坏。
[root@mysql ~]# rm -rf /usr/local/mysql/data/*
(5)还原完全备份
准备(prepare)一个完全备份
一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处于不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
在准备过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。
innobakupex命令的–apply-log选项可用于实现上述功能。
如下面的命令：–apply-log指明是将日志应用到数据文件上，完成之后将备份文件中的数据恢复到数据库中。
[root@mysql ~]# innobackupex --apply-log /opt/mysqlbackup/full/2021-01-29_19-51-44/
注意：/opt/mysqlbackup/full/2021-01-29_19-51-44/是备份文件所在目录名称。
如果执行正确，其最后输出的几行信息通常如下。
InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 2301, file name mysql-bin.000002
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.19 started; log sequence number 2774037
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2774056
210129 20:24:50 completed OK!
在实现“准备”的过程中，innobackupex通常还可以使用–use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。
innobackupex命令的–copy-back选项用于执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程。
innobackupex通过backup-my.cnf来获取DATADIR目录的相关信息。
[root@mysql ~]# innobackupex --copy-back /opt/mysqlbackup/full/2021-01-29_19-51-44/
这里的–copy-back指明是进行数据恢复。数据恢复完成之后，需要修改相关文件的权限mysql数据库才能正常启动。如果执行正确，其输出信息的最后几行通常如下。
210129 20:27:26 [01] Copying ./ib_buffer_pool to /usr/local/mysql/data/ib_buffer_pool
210129 20:27:26 [01] ...done
210129 20:27:26 [01] Copying ./xtrabackup_info to /usr/local/mysql/data/xtrabackup_info
210129 20:27:26 [01] ...done
210129 20:27:26 [01] Copying ./xtrabackup_binlog_pos_innodb to /usr/local/mysql/data/xtrabackup_binlog_pos_innodb
210129 20:27:26 [01] ...done
210129 20:27:26 [01] Copying ./xtrabackup_master_key_id to /usr/local/mysql/data/xtrabackup_master_key_id
210129 20:27:26 [01] ...done
210129 20:27:26 [01] Copying ./ibtmp1 to /usr/local/mysql/data/ibtmp1
210129 20:27:27 [01] ...done
210129 20:27:27 completed OK!
查看修改还原后的数据目录权限
[root@mysql ~]# ll /usr/local/mysql/data/
总用量 122924
-rw-r----- 1 root root 290 1月 29 20:27 ib_buffer_pool
-rw-r----- 1 root root 12582912 1月 29 20:27 ibdata1
-rw-r----- 1 root root 50331648 1月 29 20:27 ib_logfile0
-rw-r----- 1 root root 50331648 1月 29 20:27 ib_logfile1
-rw-r----- 1 root root 12582912 1月 29 20:27 ibtmp1
drwxr-x--- 2 root root 4096 1月 29 20:27 mysql
drwxr-x--- 2 root root 8192 1月 29 20:27 performance_schema
drwxr-x--- 2 root root 8192 1月 29 20:27 sys
drwxr-x--- 2 root root 50 1月 29 20:27 test_db
-rw-r----- 1 root root 22 1月 29 20:27 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 526 1月 29 20:27 xtrabackup_info
-rw-r----- 1 root root 1 1月 29 20:27 xtrabackup_master_key_id
当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户mysql，启动mysqld之前需要修改数据文件的属主和属组。
[root@mysql ~]# chown -R mysql:mysql /usr/local/mysql/data/
重启MySQL
# systemctl restart mysqld
验证还原后的数据
mysql> select * from test_db.tb1;
+----+------+
| id | name |
+----+------+
| 1 | tom1 |
| 2 | tom2 |
| 3 | tom3 |
+----+------+
3 rows in set (0.00 sec)
(6)还原增量备份
为了防止还原时产生大量的二进制日志，在还原时可临时关闭二进制日志后再还原。
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql> source /opt/mysqlbackup/inc/2021-01-29.sql
或者在命令行
mysql –uroot –p < /opt/mysqlbackup/inc/2021-01-29.sql
mysqlbinlog /opt/mysqlbackup/inc/2021-01-29.sql | mysql –uroot -p
(7)重新开启二进制日志并验证还原数据。
mysql> set sql_log_bin=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test_db.tb1;
+----+------+
| id | name |
+----+------+
| 1 | tom1 |
| 2 | tom2 |
| 3 | tom3 |
| 4 | tom4 |
| 5 | tom5 |
+----+------+
5 rows in set (0.00 sec)
7.xtrabackup完全备份+xtrabacup增量备份
前面进行增量备份时，使用的还是老方法：备份二进制日志。
其实xtrabackup还支持进行增量备份。
(1)xtrabackup的备份原理
在InnoDB内部会维护一个redo日志文件，也可以叫事务日志文件(transaction log，事务日志)。事务日志会存储每一个InnoDB表数据的记录修改。当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。xtrabackup在启动时会记住log sequence number(LSN)，并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。这就是xtrabackup的备份过程所以每个InnoDB的页面都会包含一个LSN信息，每当相关的数据发生改变，相关的页面的LSN就会自动增长。
这正是InnoDB表可以进行增量备份的基础。xtraBackup基于InnoDB的crash-recovery功能。它会复制innodb的data file，由于不锁表，复制出来的数据是不一致的，在恢复的时候使用crash-recovery，使得数据恢复一致。
当InnoDB启动的时候，它会先去检查data file和transaction log，并且会做两步操作。
1.It applies committed transaction logentries to the data files
2.it performs an undo operation on anytransactions that modified data but did not commit.
所以在prepare过程中，XtraBackup使用复制到的transactions log对备份出来的innodb data file进行crash recovery。
测试环境准备
创建一个测试数据库，并创建一张表输入几行数据。
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table xx(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into xx values(1,'tom1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into xx values(2,'tom2');
Query OK, 1 row affected (0.00 sec)
(1)xtrabacup进行完全备份
[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=asd123 --port=3306 --backup --target-dir=/opt/mysqlbackup/full/full_incre_$(date +%Y%m%d_%H%M%S)
参数解释
--defaults-file指定数据库的配置文件，如果使用该参数必须做为第一个参数；
--socket指定socket文件路径；
--user指定连接数据库的用户名；
--password指定连接数据库的密码；
--port指定连接数据库的端口号；
--backup实施备份到target-dir；
--target-dir=name备份文件的存放目录路径。innobackupex要从其中获取datadir等信息；
--database指定要备份的数据库，这里指定的数据库只对MyISAM表和InnoDB表的表结构有效，对于InnoDB数据来说都是全备(所有数据库中的InnoDB数据都进行了备份，不是只备份指定的数据库，恢复时也一样)；
/opt/mysqlbackup/full/是备份文件的存放位置。
(2)查看完全备份文件
[root@mysql ~]# ll /opt/mysqlbackup/full/
总用量 0
drwxr-x--- 7 root root 250 1月 29 20:52 full_incre_20210129_205221
(3)先录入些数据，实现第一次增量数据。
mysql> use test;
Database changed
mysql> insert into xx values(3,'tom3');
Query OK, 1 row affected (0.00 sec)
(4)xtrabackup进行增量备份1
[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=asd123 --port=3306 --backup --target-dir=/opt/mysqlbackup/inc/incre_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/full/full_incre_20210129_205221/
其中，–incremental-basedir指定上次完整备份或者增量备份文件的位置(即如果是第一次增量备份则指向完全备份所在目录，在执行过增量备份之后再一次进行增量备份时，其–incremental-basedir应该指向上一次的增量备份所在的目录)。
(5)查看增量备份文件
[root@mysql ~]# ll /opt/mysqlbackup/inc/
总用量 0
drwxr-x--- 7 root root 276 1月 29 20:59 incre_20210129_205924
注意：这里的增量备份只针对的是InnoDB，对于MyISAM来说，还是完整备份。
(6)向表中再插入几行数据，继续第二次增量备份。
mysql> use test;
Database changed
mysql> insert into xx values(4,'tom4');
Query OK, 1 row affected (0.01 sec)
(7)进行第二次增量备份
[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=asd123 --port=3306 --backup --target-dir=/opt/mysqlbackup/inc/incre_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/inc/incre_20210129_205924/
注意：第二次增量备份–incremental-basedir指向上一次增量备份文件的位置。
(8)查看增量备份文件
[root@mysql ~]# ll /opt/mysqlbackup/inc/
总用量 0
drwxr-x--- 7 root root 276 1月 29 20:59 incre_20210129_205924
drwxr-x--- 7 root root 276 1月 29 21:03 incre_20210129_210340
(9)xtrabacup进行增量恢复
为了验证比对，先删除两个增量备份前表里面的数据
mysql> use test;
Database changed
mysql> delete from xx where id=3;
Query OK, 1 row affected (0.00 sec)
(10)完整备份恢复
在进行恢复前，如果完整备份在远程主机上，首先将完整备份复制到本地主机上，如果是tar包，则需要先解包，解包命令为：tar –izxf xxx.tar，这里必须使用-i参数(忽略存档中的0字节块(通常意味着文件结束))。
[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --socket=/tmp/mysql.sock --user=root --password=asd123 --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_20210129_205221
(11)恢复到第一次增量的时刻
增量备份恢复的步骤和完整备份恢复的步骤基本一致，只是应用日志的过程稍有不同。增量备份恢复时，是先将所有的增量备份挨个应用到完整备份的数据文件中，然后再将完整备份中的数据恢复到数据库中。
[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --socket=/tmp/mysql.sock --user=root --password=asd123 --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_20210129_205221/ --incremental-dir=/opt/mysqlbackup/inc/incre_20210129_205924/
(12)恢复到第二次增量备份前面
[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --socket=/tmp/mysql.sock --user=root --password=asd123 --apply-log-only --target-dir=/opt/mysqlbackup/full/full_incre_20210129_205221/ --incremental-dir=/opt/mysqlbackup/inc/incre_20210129_210340/
(13)恢复整个库
[root@mysql ~]# xtrabackup --defaults-file=/etc/my.cnf --prepare --socket=/tmp/mysql.sock --user=root --password=asd123 --target-dir=/opt/mysqlbackup/full/full_incre_20210129_205221/
(14)停止mysql数据库
[root@mysql ~]# systemctl stop mysqld
(15)开始rsync数据文件
[root@mysql ~]# cd /opt/mysqlbackup/full/full_incre_20210129_205221/
[root@mysql full_incre_20210129_205221]# rsync -rvt --exclude 'xtrabackup_checkpoints' --exclude 'xtrabackup_logfile' ./ /usr/local/mysql/data/
当数据恢复至DATADIR目录以后，确保所有数据文件的属主和属组均为正确的用户mysql，启动mysqld之前需要修改数据文件的属主和属组。
(16)授予mysql访问权限
[root@mysql full_incre_20210129_205221]# chown -R mysql:mysql /usr/local/mysql/data/
(17)启动mysql服务
[root@mysql full_incre_20210129_205221]# systemctl start mysqld
(18)验证看到以前在备份之后删除的数据已经通过2次增量备份恢复过来了。
mysql> select * from test.xx;
+------+------+
| id | name |
+------+------+
| 1 | tom1 |
| 2 | tom2 |
| 3 | tom3 |
| 4 | tom4 |
+------+------+
4 rows in set (0.00 sec)
8.innobackupex全库备份+innobackupex增量备份
测试环境准备
创建一个测试数据库，并创建一张表输入几行数据。
mysql> create database test2;
Query OK, 1 row affected (0.00 sec)
mysql> use test2;
Database changed
mysql> create table yy(id int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into yy values(1,'kim1');
Query OK, 1 row affected (0.00 sec)
mysql> insert into yy values(2,'kim2');
Query OK, 1 row affected (0.00 sec)
(1)innobackupex先做完全备份
[root@mysql ~]# innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=asd123 /opt/mysqlbackup/full/full_incre_$(date +%Y%m%d_%H%M%S) --no-timestamp
(2)查看完全备份文件
[root@mysql ~]# ll /opt/mysqlbackup/full/
总用量 0
drwxr-x--- 8 root root 263 1月 29 21:25 full_incre_20210129_212515
(3)先录入增量数据
mysql> use test2;
Database changed
mysql> insert into yy values(3,'kim3');
Query OK, 1 row affected (0.00 sec)
(4)进行增量备份
[root@mysql ~]# innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=asd123 --incremental /opt/mysqlbackup/inc/incre_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/full/full_incre_20210129_212515/ --no-timestamp
(5)查看增量备份文件
[root@mysql ~]# ll /opt/mysqlbackup/inc/
总用量 0
drwxr-x--- 8 root root 289 1月 29 21:29 incre_20210129_212939
基于全备和第一个增量备份来做第二次增量备份
(6)先录入增量数据录入
mysql> use test2;
Database changed
mysql> insert into yy values(4,'kim4');
Query OK, 1 row affected (0.00 sec)
(7)开始进行第二次增量备份
[root@mysql ~]# innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=asd123 --incremental /opt/mysqlbackup/inc/incre_$(date +%Y%m%d_%H%M%S) --incremental-basedir=/opt/mysqlbackup/inc/incre_20210129_212939/ --no-timestamp
(8)查看增量备份文件
[root@mysql ~]# ll /opt/mysqlbackup/inc/
总用量 0
drwxr-x--- 8 root root 289 1月 29 21:29 incre_20210129_212939
drwxr-x--- 8 root root 289 1月 29 21:32 incre_20210129_213220
(9)innobackupex做增量恢复
先删除两次增量数据，用来查看验证恢复结果
mysql> use test2;
Database changed
mysql> delete from yy where id=3;
Query OK, 1 row affected (0.00 sec)
(10)开始做恢复，恢复全备份
[root@mysql ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/full_incre_20210129_212515/
–redo-only用于准备增量备份内容把数据合并到全备份目录，配合incremental-dir增量备份目录使用。
(11)基于全备份进行第一次增量备份的恢复
[root@mysql ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/full_incre_20210129_212515/ --incremental-dir=/opt/mysqlbackup/inc/incre_20210129_212939/
(12)基于全备份和第一次增量备份，恢复第二次增量备份
[root@mysql ~]# innobackupex --apply-log --redo-only /opt/mysqlbackup/full/full_incre_20210129_212515/ --incremental-dir=/opt/mysqlbackup/inc/incre_20210129_213220/
(13)恢复整个数据库，停止数据库。
[root@mysql ~]# systemctl stop mysqld
(14)清空数据目录下所有文件
[root@mysql ~]# mkdir -p /tmp/mysqldatabak
[root@mysql ~]# mv /usr/local/mysql/data/* /tmp/mysqldatabak/
(15)将恢复好的数据按照配置文件的需求拷贝到相应目录
[root@mysql ~]# innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=asd123 --copy-back /opt/mysqlbackup/full/full_incre_20210129_212515/
(16)当数据恢复至DATADIR目录以后，需要确保所有数据文件的属主和属组均为正确的用户mysql，启动mysqld之前需要修改数据文件的属主和属组。
[root@mysql ~]# chown -R mysql:mysql /usr/local/mysql/data/
(17)启动mysql服务
[root@mysql ~]# systemctl start mysqld
(18)登录mysql界面查看数据是否已经恢复
mysql> use test2;
Database changed
mysql> select * from yy;
+------+------+
| id | name |
+------+------+
| 1 | kim1 |
| 2 | kim2 |
| 3 | kim3 |
| 4 | kim4 |
+------+------+
4 rows in set (0.00 sec)
附加：Xtrabackup的“流”及“备份压缩”功能
Xtrabackup对备份的数据文件支持“流”功能，即可以将备份的数据通过STDOUT传输给tar程序进行归档，而不是默认直接保存至某备份目录中。使用此功能，仅需要使用–stream选项。
[root@mysql ~]# innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=asd123 --stream=tar /opt/mysqlbackup/full/ | gzip > /opt/mysqlbackup/full/full_`date +%F_%H%M%S`.tar.gz
[root@mysql ~]# ll /opt/mysqlbackup/full/
总用量 624
-rw-r--r-- 1 root root 636890 1月 29 21:48 full_2021-01-29_214847.tar.gz
扫一扫
951
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
