通过mariadb二进制日志实现数据库增量备份

何为增量备份，简单理解就是使用日志记录每天数据库的操作情况，只需要每天把这个日志里的数据库操作还原到数据库中，从而避免每天都进行完全备份，这种情况下，每周进行一次完全备份即可
首先我们需要配置以下mariadb的配置文件，我使用的是yum安装，其配置文件位于/etc/my.cnf，内容如下

[mysqld]
log-bin=mysql-bin                   #只需要增加这行就可以了
#binlog_format=row
#skip-grant
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

进入mariadb进行操作

[root@localhost mysql]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or g.
Your MariaDB connection id is 4
Server version: 5.5.52-MariaDB MariaDB Server

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

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

MariaDB [(none)]> use bp
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [bp]> show tables;
+--------------+
| Tables_in_bp |
+--------------+
| mytest       |
| test         |
+--------------+
2 rows in set (0.00 sec)

MariaDB [bp]> create table bptest(id int ,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

MariaDB [bp]> insert into bptest values(1,'a');
Query OK, 1 row affected (0.00 sec)

MariaDB [bp]> insert into bptest values(2,'b');
Query OK, 1 row affected (0.01 sec)

MariaDB [bp]> select * from bptest;
+------+------+
| id   | name |
+------+------+
|    1 | a    |
|    2 | b    |
+------+------+
2 rows in set (0.01 sec)

MariaDB [bp]> flush logs;                       #这里我还有点不明白，我是简单理解为日志的开始位置
Query OK, 0 rows affected (0.01 sec)

MariaDB [bp]> insert into bptest values(3,'c');
Query OK, 1 row affected (0.01 sec)

MariaDB [bp]> insert into bptest values(4,'d');
Query OK, 1 row affected (0.01 sec)

MariaDB [bp]> flush logs;                       #日志结束位置，该日志文件我们可以在/var/lib/mysql里面找到
Query OK, 0 rows affected (0.02 sec)

MariaDB [bp]> delete from bptest where id =3;
Query OK, 1 row affected (0.01 sec)

MariaDB [bp]> delete from bptest where id=1;
Query OK, 1 row affected (0.00 sec)

MariaDB [bp]> flush logs;
Query OK, 0 rows affected (0.02 sec)

MariaDB [bp]> truncate table bptest;#为了让效果更明显，我们直接清空表内容
Query OK, 0 rows affected (0.13 sec)

MariaDB [bp]> select * from bptest;
Empty set (0.00 sec)

我们可以进入/var/lib/mysql文件夹内查看，可以看到mysql-bin.000001,mysql-bin.000002文件
接下来我们来看一下日志文件内容

[root@localhost mysql]# mysqlbinlog 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
#170725  2:04:19 server id 1  end_log_pos 245   Start: binlog v 4, server v 5.5.52-MariaDB created 170725  2:04:19
BINLOG '
kwl3WQ8BAAAA8QAAAPUAAAAAAAQANS41LjUyLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAKUTwPA==
'/*!*/;
# at 245
#170725  2:04:51 server id 1  end_log_pos 311   Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1500973491/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN /*!*/;
# at 311
#170725  2:04:51 server id 1  end_log_pos 404   Query   thread_id=4 exec_time=0 error_code=0
use `bp`/*!*/;
SET TIMESTAMP=1500973491/*!*/;
insert into bptest values(3,'c') /*!*/;
# at 404
#170725  2:04:51 server id 1  end_log_pos 431   Xid = 47
COMMIT/*!*/;
# at 431
#170725  2:04:56 server id 1  end_log_pos 497   Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1500973496/*!*/;
BEGIN /*!*/;
# at 497
#170725  2:04:56 server id 1  end_log_pos 590   Query   thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1500973496/*!*/;
insert into bptest values(4,'d') /*!*/;
# at 590
#170725  2:04:56 server id 1  end_log_pos 617   Xid = 48
COMMIT/*!*/;
# at 617
#170725  2:05:00 server id 1  end_log_pos 660   Rotate to mysql-bin.000002  pos: 4
DELIMITER ;
# End of log file ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@localhost mysql]#

在这个日志文件里面我们可以看到sql语句，且这些语句都位于mariadb操作里面的flush logs之间
现在我们就来进行备份的还原吧
现在我们使用mysql-bin.000001进行操作

[root@localhost mysql]# mysqlbinlog mysql-bin.000001|mysql -uroot -p
Enter password: 
[root@localhost mysql]# 

执行完毕，没有报错，我们再进数据库里面看看是否成功还原备份

MariaDB [bp]> select * from bptest;  #还原前
Empty set (0.00 sec)

MariaDB [bp]> select * from bptest;  #还原后
+------+------+
| id | name | +------+------+
|    3 | c    |
| 4 | d | +------+------+
2 rows in set (0.00 sec)

MariaDB [bp]>