何为增量备份,简单理解就是使用日志记录每天数据库的操作情况,只需要每天把这个日志里的数据库操作还原到数据库中,从而避免每天都进行完全备份,这种情况下,每周进行一次完全备份即可
首先我们需要配置以下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]>