开启 二进制日志功能
$ 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 password11$ 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-p11mysql>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=0SETTIMESTAMP=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下面的时间，但是查看的是“20072118:50:18”执行命令时候，时间中间加一个横杠“20-07-2118: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;'
作者：graceful_black
链接：https://www.jianshu.com/p/7953ec5e620b
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
