- 开启 mysql binlog 日志功能
vim /etc/my.cnf添加下面内容
log-bin=/localmnt/backup/mysqlbinlog/mysql-bin
- 重启mysql
systemctl restart mysqld
- 全量备份mysql
mysqldump -u root -pxxx database_name | gzip > /localmnt/database_name.sql.gz
- 如果数据被删除了，首先通过全量备份导入数据
mysql -uroot -pxxx database_name < /localmnt/database_name.sql
- 通过查询 binlog 日志，找出需要恢复的时间点
mysqlbinlog binlog_files | more
通过时间点恢复
mysqlbinlog --stop-position="102" --start-position="367" mysql-bin.000001 | mysql -uroot -pxxx database_name
mysqlbinlog --start-datetime="2018-04-20 10:01:00" --stop-datetime="2005-04-21 10:01:00" mysql-bin.000001 | mysql -u root -pxxx database_name
- 重置binlog日志
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 106 | | |
+------------------+----------+--------------+------------------+
