mariadb增量备份

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

[mysqld]log-bin=mysql-bin                   #只需要增加这行就可以了#binlog_format=row#skip-grantdatadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-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.logpid-file=/var/run/mariadb/mariadb.pid## include all files from the config directory#!includedir /etc/my.cnf.d

进入mariadb进行操作

[root@localhost mysql]# mysql -uroot -pEnter password: Welcome to the MariaDB monitor.  Commands end with ; or \g.Your MariaDB connection id is 4Server version: 5.5.52-MariaDB MariaDB ServerCopyright (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 bpReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [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:19BINLOG 'kwl3WQ8BAAAA8QAAAPUAAAAAAAQANS41LjUyLU1hcmlhREIAbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAA2QAEGggAAAAICAgCAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAKUTwPA=='/*!*/;# at 245#170725  2:04:51 server id 1  end_log_pos 311   Query   thread_id=4 exec_time=0 error_code=0SET 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=0use `bp`/*!*/;SET TIMESTAMP=1500973491/*!*/;insert into bptest values(3,'c')/*!*/;# at 404#170725  2:04:51 server id 1  end_log_pos 431   Xid = 47COMMIT/*!*/;# at 431#170725  2:04:56 server id 1  end_log_pos 497   Query   thread_id=4 exec_time=0 error_code=0SET 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=0SET TIMESTAMP=1500973496/*!*/;insert into bptest values(4,'d')/*!*/;# at 590#170725  2:04:56 server id 1  end_log_pos 617   Xid = 48COMMIT/*!*/;# at 617#170725  2:05:00 server id 1  end_log_pos 660   Rotate to mysql-bin.000002  pos: 4DELIMITER ;# End of log fileROLLBACK /* 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 -pEnter 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]>