MariaDB备份恢复①:mysqldump + 二进制日志

2018-05-21|Categories: Database|

确保二进制日志已启用

本次实验的恢复操作必须依赖二进制日志,所以必须确保二进制日志已经开启:变量log_binsql_log_bin的值都为ON

mysql -uroot -p -e 'show variables like "log_bin"; show variables like "sql_log_bin";'
Enter password:
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
+---------------+-------+

定期执行完全备份

本次实验中,初始的数据库除了MariaDB内置的三个系统数据库之外,只有一个用户数据库tysql

MariaDB [tysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tysql              |
+--------------------+
4 rows in set (0.00 sec)

tysql数据库来自《SQL必知必会》这本书,下载导入方法如下:

# 下载
mkdir /src && cd $_
wget http://forta.com/books/0672336073/TeachYourselfSQL_MySQL.zip

# 解压
unzip TeachYourselfSQL_MySQL.zip

# 转换文件格式
dos2unix *.txt

# 创建数据库
mysql -e 'create database tysql;'

# 导入第一个文件(创建空表)
mysql tysql < create.txt

# 导入第二个文件(填充刚创建的表)
mysql tysql < populate.txt

tysql数据库有五张表:

MariaDB [tysql]> show tables;
+-----------------+
| Tables_in_tysql |
+-----------------+
| Customers       |
| OrderItems      |
| Orders          |
| Products        |
| Vendors         |
+-----------------+
5 rows in set (0.00 sec)

基于这个初始状态执行完全备份:

mkdir /db_backups

mysqldump -uroot -p -A -F --master-data --single-transaction > /db_backups/full_backup_$(date +%F-%H%M%S).sql

我最初是从一份PDF里复制了命令:

mysqldump –A –F –E –R –x --master-data=1 --flush-privileges --triggers --hex-blob > backup.sql

但执行后却报错:

mysqldump: Got error: 1049: Unknown database '–A' when selecting the database

这个错误信息弄得我莫名其妙,经过搜索才发现,原来在复制的命令中,不是中横线-,而是另外一个外形相似的字符,正式名称是「En Dash」,Unicode编号是U+2013!而中横线的正式名称是「Hyphen-Minus」,Unicode编号是U+002D

这个经历给我的教训是,不要轻易复制别人的代码,否则很容易出现这种似是而非的错误,而且很难排查。

确认备份文件已生成:

$ ls -l /db_backups/
-rw-r--r-- 1 root root 487343 May 21 20:09 /db_backups/full_backup_2018-05-21-200934.sql

模拟对数据库的修改

插入记录

Orders表有五条原始记录:

MariaDB [tysql]> select * from Orders;
+-----------+---------------------+------------+
| order_num | order_date          | cust_id    |
+-----------+---------------------+------------+
|     20005 | 2012-05-01 00:00:00 | 1000000001 |
|     20006 | 2012-01-12 00:00:00 | 1000000003 |
|     20007 | 2012-01-30 00:00:00 | 1000000004 |
|     20008 | 2012-02-03 00:00:00 | 1000000005 |
|     20009 | 2012-02-08 00:00:00 | 1000000001 |
+-----------+---------------------+------------+
5 rows in set (0.00 sec)

插入一条记录:

insert into Orders values ('20010', now(), '1000000002');

注意,这里使用了now()这个内建函数来插入时间。

最新记录:

MariaDB [tysql]> select * from Orders;
+-----------+---------------------+------------+
| order_num | order_date          | cust_id    |
+-----------+---------------------+------------+
|     20005 | 2012-05-01 00:00:00 | 1000000001 |
|     20006 | 2012-01-12 00:00:00 | 1000000003 |
|     20007 | 2012-01-30 00:00:00 | 1000000004 |
|     20008 | 2012-02-03 00:00:00 | 1000000005 |
|     20009 | 2012-02-08 00:00:00 | 1000000001 |
|     20010 | 2018-05-21 21:05:35 | 1000000002 |
+-----------+---------------------+------------+
6 rows in set (0.00 sec)

意外删除一个数据库

drop database tysql;

新建一个数据库

create database Testing;

现在数据库变成了下面这样:

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| Testing            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

然后在Testing库新建一张表t1

create table Testing.t1 (Id int, Name varchar(20));

再添加两条记录:

insert into Testing.t1 values (1, 'MariaDB'), (2, 'MySQL');

Testing.t1表的内容如下:

MariaDB [(none)]> select * from Testing.t1;
+------+---------+
| Id   | Name    |
+------+---------+
|    1 | MariaDB |
|    2 | MySQL   |
+------+---------+
2 rows in set (0.01 sec)

发现故障,采取紧急措施,阻止情况恶化

这里假设误删的数据非常重要,必须恢复。

全局加读锁

flush tables with read lock

禁止其他用户访问

iptables -A INPUT -p tcp --dport 3306 -j REJECT

生成新的二进制日志

flush logs;

这是为了隔离包含错误操作的日志。

从二进制日志生成用于恢复的SQL文件

查看完全备份的截止位置

$ grep -m1 -i 'master_log_pos' full_backup_2018-05-21-200934.sql
CHANGE MASTER TO MASTER_LOG_FILE='vm71-bin.000005', MASTER_LOG_POS=383;

输出结果表明,完全备份的截止位置是二进制日志vm71-bin.000005383字节,这个数字后面还要用到。

备份截止位置之后的二进制日志

mkdir /binlog_bak

cp /data/mariadb/vm71-bin.00000{5..8} /binlog_bak -v

把二进制日志导出为SQL文件

cd /binlog_bak

mysqlbinlog --start-position=383 vm71-bin.000005 > binlog_exported.sql

如有多个日志则追加导出到同一个SQL文件:

for i in {6..8}; do mysqlbinlog vm71-bin.00000$i >> binlog_exported.sql; done

注释SQL文件中的错误操作语句

sed -i -r 's/^(drop database tysql)$/#\1/' binlog_exported.sql

开始恢复数据库

暂时停止记录二进制日志

set sql_log_bin = off;

数据库解除读锁

unlock tables;

导入完全备份

MariaDB [(none)]> source /db_backups/full_backup_2018-05-21-200934.sql

导入由二进制日志导出的SQL文件

MariaDB [(none)]> source /binlog_bak/binlog_exported.sql

重新开始记录二进制日志

set sql_log_bin = on;

恢复用户访问

这里假设iptables规则只有前面添加的那一条:

iptables -F

恢复数据后的检查

被删除的tysql已恢复:

MariaDB [tysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| Testing            |
| information_schema |
| mysql              |
| performance_schema |
| tysql              |
+--------------------+
5 rows in set (0.00 sec)

而删库之前添加到Orders表的记录也正确恢复了,尤其是order_date字段的值,当时通过now()函数添加,恢复后仍然是首次添加的那个时间点,而不是恢复记录的时间点:

MariaDB [tysql]> select * from tysql.Orders;
+-----------+---------------------+------------+
| order_num | order_date          | cust_id    |
+-----------+---------------------+------------+
|     20005 | 2012-05-01 00:00:00 | 1000000001 |
|     20006 | 2012-01-12 00:00:00 | 1000000003 |
|     20007 | 2012-01-30 00:00:00 | 1000000004 |
|     20008 | 2012-02-03 00:00:00 | 1000000005 |
|     20009 | 2012-02-08 00:00:00 | 1000000001 |
|     20010 | 2018-05-21 21:05:35 | 1000000002 |
+-----------+---------------------+------------+
6 rows in set (0.00 sec)

这是因为MariaDB server的二进制日志格式已经设置为MIXED,而不是STATEMENT

MariaDB [tysql]> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.00 sec)

唯一的问题是,Testing.t1表中出现了重复记录:

MariaDB [tysql]> select * from Testing.t1;
+------+---------+
| Id   | Name    |
+------+---------+
|    1 | MariaDB |
|    2 | MySQL   |
|    1 | MariaDB |
|    2 | MySQL   |
+------+---------+
4 rows in set (0.00 sec)

对于这种情况,解决方法无非两种:

  • 一是在恢复前仔细检查二进制日志,精确选择用于恢复的起始位置、结束位置,防止产生重复记录。
  • 二是在恢复后用SQL语句查找并删除重复记录。

第一种方法只能通过人工一行行查看日志,效率低下,也容易出错;第二种方法不管是效率还是准确性都远超第一种。

t1表出现重复记录的主要原因是所有字段都没有任何约束。如果要改进,最简单的是把Id字段设置成主键(Primary key),进一步可以把Name字段设置成唯一键(Unique key)。

但这次实验的主要目的是MariaDB的备份恢复过程,简单的表结构并不影响实验结果,因此就没有添加这些约束。

删除重复记录

删除重复记录的方法不止一种,这里通过创建第二张表来完成,需要三步。

第一步,创建结构相同的第二张表:

use Testing;

create table t1_copy like t1;

第二步,使用子查询筛选出不重复的记录,然后插入第二张表:

insert into t1_copy select distinct * from t1;

第三步,删除原始表,然后把第二张表重命名为原始表:

drop table t1;

alter table t1_copy rename to t1;

此时再查看,重复记录已删除:

MariaDB [Testing]> select * from t1;
+------+---------+
| Id   | Name    |
+------+---------+
|    1 | MariaDB |
|    2 | MySQL   |
+------+---------+
2 rows in set (0.00 sec)

至此,通过mysqldump的完全备份,配合二进制日志实现MariaDB的备份恢复就已经完成了。

Leave A Comment