利用mysqldump和二进制日志实现数据库较精确的备份还原

备份什么

• 数据
• 二进制日志、InnoDB的事务日志
• 程序代码（存储过程、函数、触发器、事件调度器）
• 服务器的配置文件
  从上面可以看到，我们需要备份四处地方，我们这里只介绍较重要的数据和二进制日志的备份还原

1，创建二进制日志的存放目录（该目录最好在一个独立分区里），设置该目录的所有者所有组为mysql，权限未设的话二进制日志起不来的

[root@localhost ~]# mkdir /data/binlog
[root@localhost ~]# chown mysql.mysql /data/binlog

2，在mysql配置文件/etc/my.cnf下设定二进制日志的目录，目录路径即为刚才创建的目录，后面跟上二进制日志的前缀，二进制日志格式最好为row或者mixed模式，配置完成重启mysql服务

[root@localhost ~]# systemctl restart mariadb

3，将数据文件备份，并用–master-data选项来实现记录备份时二进制日志的位置，此选项很重要
注：备份文件最好放到其他存储设备上

[root@localhost ~]# mysqldump -A --master-data=2 | gzip > /backup/all_`date +%F`.sql.gz
[root@localhost ~]# ll /backup/
total 140
-rw-r--r-- 1 root root 141226 Nov 26 09:59 all_2019-11-26.sql.gz

4，为了体现二进制日志的效果，备份后修改数据库信息，为hellodb.teachers表添加两行

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
MariaDB [hellodb]> insert teachers value(5,'liuermihou',255,'M');
MariaDB [hellodb]> insert teachers value(6,'jiangliuer',20,'M');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | liuermihou    | 255 | M      |
|   6 | jiangliuer    |  20 | M      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

5，模拟数据库的损坏—删除数据库

[root@localhost ~]# rm -rf /var/lib/mysql/*
[root@localhost ~]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

6，解压缩数据库的备份，并查看备份时二进制日志的位置信息，位置为2019-11-26 9:59:32

[root@localhost ~]# gzip -d /backup/all_2019-11-26.sql.gz
[root@localhost ~]# ll /backup/
total 512
-rw-r--r-- 1 root root 523477 Nov 26 09:59 all_2019-11-26.sql
[root@localhost ~]# tail -1 /backup/all_2019-11-26.sql
-- Dump completed on 2019-11-26  9:59:32

7，生成数据库相关配置文件，重启数据库服务
注：centos7重启服务可自动生成数据库相关文件，centos8需要mysql_install_db --user=mysql手动生成

[root@localhost ~]# mysql_install_db --user=mysql
Neither host 'localhost.localdomain' nor 'localhost' could be looked up with
'/usr/libexec/resolveip'
Please configure the 'hostname' command to return a correct
hostname.
If you want to solve this at a later stage, restart this script
with the --force option

The latest information about mysql_install_db is available at
https://mariadb.com/kb/en/installing-system-tables-mysql_install_db
[root@localhost ~]# systemctl restart mariadb

8，进入数据库，查看二进制文件信息，004号二进制日志是重启服务新生成的，不用管

[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       772 |
| mysql-bin.000002 |     30379 |
| mysql-bin.000003 |   1038814 |
| mysql-bin.000004 |       245 |
+------------------+-----------+
4 rows in set (0.00 sec)

9，查看001号二进制日志文件，可以看到001号二进制日志包含2019-11-26 9:59:32这个时间点

[root@localhost ~]# mysqlbinlog /data/binlog/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#191126  9:57:42 server id 1  end_log_pos 245 	Start: binlog v 4, server v 5.5.64-MariaDB created 191126  9:57:42 at startup
ROLLBACK/*!*/;
BINLOG '
lobcXQ8BAAAA8QAAAPUAAAAAAAQANS41LjY0LU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACWhtxdEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAjuTC+w==
'/*!*/;
# at 245
#191126 10:07:15 server id 1  end_log_pos 316 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1574734035/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;

10，结合之前备份时的二进制日志位置信息将备份后的二进制日志导出

[root@localhost ~]# mysqlbinlog /data/binlog/mysql-bin.000001 --start-datetim="2019-11-26  9:59:32" > /backup/inc.sql
[root@localhost ~]# mysqlbinlog /data/binlog/mysql-bin.000002 >> /backup/inc.sql
[root@localhost ~]# mysqlbinlog /data/binlog/mysql-bin.000003 >> /backup/inc.sql

11，导入数据库备份（相当于完全备份的还原，此时恢复到了未修改hellodb.teachers之前的状态）

MariaDB [(none)]> source /backup/all_2019-11-26.sql

12，导入二进制日志（相当于增量备份，此时恢复到了最近一次重启数据库的状态）

MariaDB [mysql]> source /backup/inc.sql

13，查看hellodb.teachers表，可以看到之前新增的两行也在，还原成功

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | liuermihou    | 255 | M      |
|   6 | jiangliuer    |  20 | M      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

生产环境实战备份策略
InnoDB建议备份策略

mysqldump –uroot –A –F –E –R --single-transaction --master-data=1 --flushprivileges 
--triggers --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM建议备份策略

mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges --triggers 
--default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql