【将数据库qz库里的员工薪资表备份到/opt目录下的员工薪资.sql】
[root@192~]# mysqldump -uroot -p5514 qz 员工薪资 > /opt/员工薪资.sql
mysqldump: [Warning]Using a password on the command line interface can be insecure.[root@192~]# cd /opt/[root@192 opt]# ls
dis mysql-5.7.20 mysql-bbs.sql nginx-1.12.2.tar.gz php-7.1.10.tar.bz2 rh
Discuz_X3.4_SC_UTF8.zip mysql-all.sql nginx-1.12.2 php-7.1.10 qz.sql 员工薪资.sql
【通过-e选项执行连接数据库后的删除和查看命令】
[root@192 opt]# mysql -uroot -p5514 -e 'drop table qz.员工薪资;'
mysql: [Warning]Using a password on the command line interface can be insecure.[root@192 opt]# mysql -uroot -p5514 -e 'show tables from qz;'
mysql: [Warning]Using a password on the command line interface can be insecure.
【将/opt/员工薪资.sql 恢复到数据库的qz库里】
[root@192 opt]# mysql -u root -p qz < /opt/员工薪资.sql
Enter password:
[root@192 opt]# mysql -uroot -p5514 -e 'show tables from qz;'
mysql: [Warning]Using a password on the command line interface can be insecure.+--------------+| Tables_in_qz |+--------------+| 员工薪资 |+--------------+
【刷新生成一个新的二进制日志mysql-bin.000010用于记录】
[root@192data]# mysqladmin -uroot -p5514 flush-logs
mysqladmin: [Warning]Using a password on the command line interface can be insecure.[root@192data]# ls
auto.cnf ibdata1 ibtmp1 mysql-bin.000002 mysql-bin.000005 mysql-bin.000008 mysql-bin.index sys
bbs ib_logfile0 mysql mysql-bin.000003 mysql-bin.000006 mysql-bin.000009 performance_schema
ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql-bin.000004 mysql-bin.000007 mysql-bin.000010 qz
【新建一个表并插入4条记录并查看相对应的位置点及时间点】
mysql>createtable qqq (id int(40));
mysql>insertinto qqq values(001);
mysql>insertinto qqq values(002);
mysql>insertinto qqq values(003);
mysql>insertinto qqq values(004);
mysql>showtables;+--------------+| Tables_in_qz |+--------------+| 员工薪资 || q || qa || qq || qqq || qw |+--------------+
【查看后发现需要跳过的操作节点开始于994,停止与1091】
[root@192data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000010# at 994#210420 12:23:45 server id 1 end_log_pos 1091 CRC32 0xecdfe8b5 Query thread_id=3 exec_time=0 error_code=0SETTIMESTAMP=1618892625/*!*/;insertinto qqq values(003)/*!*/;
【再刷新一个新的二进制日志文件mysql-bin.000011用于记录后面删表操作】
【如果不生成新二进制日志文件则再恢复时会把下面删表的操作也执行】
[root@192data]# mysqladmin -uroot -p5514 flush-logs
mysqladmin: [Warning]Using a password on the command line interface can be insecure.[root@192data]# ls
auto.cnf ibdata1 ibtmp1 mysql-bin.000002 mysql-bin.000005 mysql-bin.000008 mysql-bin.000011 qz
bbs ib_logfile0 mysql mysql-bin.000003 mysql-bin.000006 mysql-bin.000009 mysql-bin.index sys
ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql-bin.000004 mysql-bin.000007 mysql-bin.000010 performance_schema
【恢复数据停止到节点994。因为插入数据3的操作记录开始于994,停止于1091】
[root@192data]# mysqlbinlog --no-defaults --stop-position='994' mysql-bin.000010 | mysql -uroot -p5514
mysql>showtables;+--------------+| Tables_in_qz |+--------------+| 员工薪资 || q || qa || qq || qqq || qw |+--------------+
mysql>select*from qqq;+------+| id |+------+|1||2|+------+
【开始于节点1091,则跳过插入3的操作步骤】
[root@192data]# mysqlbinlog --no-defaults --start-position='1091' mysql-bin.000010 | mysql -uroot -p5514
mysql>select*from qqq;+------+| id |+------+|1||2||4|+------+
基于时间点恢复
【查看二进制日志文件找到插入数据3的时间开始于停止节点】
[root@192data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000010# at 994#210420 12:23:45 server id 1 end_log_pos 1091 CRC32 0xecdfe8b5 Query thread_id=3 exec_time=0 error_code=0SETTIMESTAMP=1618892625/*!*/;insertinto qqq values(003)/*!*/;# at 1091#210420 12:23:45 server id 1 end_log_pos 1122 CRC32 0xfc8d8f38 Xid = 146COMMIT/*!*/;# at 1122#210420 12:23:47 server id 1 end_log_pos 1187 CRC32 0xe7e85633 Anonymous_GTID last_committed=4 sequence_number=5 rbr_only=noSET @@SESSION.GTID_NEXT='ANONYMOUS'/*!*/;
【恢复到时间节点2021-04-2012:23:45前的数据】
[root@192data]# mysqlbinlog --no-defaults --stop-datetime='2021-04-20 12:23:45' mysql-bin.000010 | mysql -uroot -p5514
mysql: [Warning]Using a password on the command line interface can be insecure.
mysql>showtables;+--------------+| Tables_in_qz |+--------------+| 员工薪资 || q || qa || qq || qqq || qw |+--------------+6rowsinset(0.00 sec)
mysql>select*from qqq;+------+| id |+------+|1||2|+------+2rowsinset(0.01 sec)
【跳过时间节点2021-04-2012:23:45的数据再开始恢复】
[root@192data]# mysqlbinlog --no-defaults --start-datetime='2021-04-20 12:23:47' mysql-bin.000010 | mysql -uroot -p5514
mysql: [Warning]Using a password on the command line interface can be insecure.
mysql>select*from qqq;+------+| id |+------+|1||2||4|+------+3rowsinset(0.00 sec)