MySQL 增量备份

开启 二进制日志功能

$ vim /etc/my.cnf
添加 log_bin=/usr/local/mysql/mysql-bin

01.png

02.png

$ systemctl restart mysqld.service

03.png

查看日志

$ ls -l /usr/local/mysql/mysql-bin*

04.png

添加数据库、表、记录信息

mysqladmin -u root password yaoban;
// 如MySQL没设置密码，执行以上命令，修改密码为yaoban

05.png

$ mysql -u root -p

06.png

mysql> CREATE DATABASE class;

07.png

mysql> USE class;
mysql> CREATE TABLE class_info (banjihao CHAR(6) NOT NULL,xuehao INT(6) NOT NULL,xingming CHAR(10) NOT NULL,xingbie CHAR(4) NOT NULL);

08.png

mysql> INSERT INTO class_info VALUES ('2311','00353','zhangsan','nan');
mysql> INSERT INTO class_info VALUES ('2311','00354','lisi','nv');
mysql> INSERT INTO class_info VALUES ('2311','00355','wangwu','nv');

09.png

mysql> SELECT * FROM class_info;

10.png

进行一次完全备份

$ mkdir /mysql_backup
$ mysqldump -u root -p class class_info > /mysql_backup/class-class_info-$(date +%F).sql
$ ls /mysql_backup/
$ mysqladmin -u root -p flush-logs
$ ls -l /usr/local/mysql/mysql-bin.*

11.png

继续录入新的数据并进行增量备份

mysql> USE class;
mysql> INSERT INTO class_info VALUES ('2321','446','zhaoliu','nan');
mysql> INSERT INTO class_info VALUES ('2321','447','sunqi','nan');
mysql> SELECT * FROM class_info;

12.png

$ mysqladmin -u root -p flush-logs
$ ls -l /usr/local/mysql/mysql-bin.*
$ cp /usr/local/mysql/mysql-bin.000002 /mysql_backup/

13.png

启动MySQL服务的二进制日志
$ vim /etc/my.cnf
添加    log_bin=/usr/local/mysql/mysql-bin
$ systemctl restart mysqld.service
$ ls -l /usr/local/mysql/mysql-bin.*
$ mysqladmin -u root password 11
$ mysql -u root -p


添加数据库、表、录入信息
mysql> create database client;
mysql> use client;
mysql> create table user_info (sfz char(20),xm char(20),xb char(4),yhID int);
mysql> insert into user_info values ('006','zhangsan','nan','016');
mysql> insert into user_info values ('007','lisi','nv','017');
mysql> insert into user_info values ('008','wangwu','nv','018');
mysql> select * from user_info;
mysql> exit


完成一次完成备份
$ mkdir /mysql_backup
$ mysqldump -u root -p client user_info > /mysql_backup/client-user_info-$(date +%F).sql
$ ls /mysql_backup/
$ mysqladmin -u root -p flush-logs
$ ls -l /usr/local/mysql/mysql-bin.*


继续录入新的数据并进行增量备份
$ mysql -u root -p11
mysql> use client;
mysql> insert into user_info values ('009','zhaoliu','nan','019');
mysql> insert into user_info values ('010','sunqi','nv','020');
mysql> select * from user_info;
mysql> exit
$ mysqladmin -u root -p flush-logs
$ ll /usr/local/mysql/mysql-bin.*
$ cp /usr/local/mysql/mysql-bin.000002 /mysql_backup/


误操作删除user_info表
$ mysql -u root -p -e 'drop table client.user_info;'
$ mysql -u root -p -e 'select * from client.user_info;'


恢复操作
$ mysql -u root -p client < /mysql_backup/client-user_info-2020-07-21.sql 
$ mysql -u root -p -e 'select * from client.user_info;'
$ mysqlbinlog --no-defaults /mysql_backup/mysql-bin.000002 | mysql -u root -p
$ mysql -u root -p -e 'select * from client.user_info;'


基于位置恢复
$ mysql -u root -p -e 'drop table client.user_info;'
$ mysql -u root -p -e 'select * from client.user_info;'
$ mysql -u root -p client < /mysql_backup/client-user_info-2020-07-21.sql
$ mysqlbinlog --no-defaults /mysql_backup/mysql-bin.000002
>>>
# at 453
#200721 18:50:18 server id 1  end_log_pos 586 CRC32 0xf5726f6c  Query   thread_id=5exec_time=0  error_code=0
SET TIMESTAMP=1595328618/*!*/;
insert into user_info values ('010','sunqi','nv','020')
（找到和这一段类似的内容，基于位置恢复输入at 后面的数字，基于时间恢复输入at下面的时间）
<<<
$ mysqlbinlog --no-defaults --stop-position='453' /mysql_backup/mysql-bin.000002 | mysql -u root -p
（注：“453”是位置，就是上面说的那个数字）
$ mysql -u root -p -e 'select * from client.user_info;'

$ mysql -u root -p -e 'drop table client.user_info;'
$ mysql -u root -p client < /mysql_backup/client-user_info-2020-07-21.sql 
$ mysqlbinlog --no-defaults --start-position='453' /mysql_backup/mysql-bin.000002 | mysql -u root -p
$ mysql -u root -p -e 'select * from client.user_info;'

基于时间点恢复
$ mysql -u root -p -e 'drop table client.user_info;'
$ mysql -u root -p client < /mysql_backup/client-user_info-2020-07-21.sql
$ mysqlbinlog --no-defaults --stop-datetime='20-07-21 18:50:18' /mysql_backup/mysql-bin.000002 | mysql -u root -p
（注：那个时间，也是上面说at下面的时间，但是查看的是“200721 18:50:18”执行命令时候，时间中间加一个横杠“20-07-21 18:50:18”）
$ mysql -u root -p -e 'select * from client.user_info;'


$ mysql -u root -p -e 'drop table client.user_info;'
$ mysql -u root -p client < /mysql_backup/client-user_info-2020-07-21.sql
$ mysqlbinlog --no-defaults --start-datetime='20-07-21 18:50:18' /mysql_backup/mysql-bin.000002 | mysql -u root -p
$ mysql -u root -p -e 'select * from client.user_info;'