1、首先要确保数据库启动mysqlbinlog
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql>
只有log_bin状态时ON的情况下才是开启状态。
2、数据库备份时，需要加上 --flush-logs --hex-blob --master-data=2参数，这样每次备份时都会重新生成新的mysqlbinlog，有利于后续的备份恢复。
[root@localhost mysql-t1]# ll -th mysql-bin.*
-rw-r----- 1 mysql mysql 1.9K 7月 24 21:05 mysql-bin.000001
-rw-r----- 1 mysql mysql 19 7月 24 20:23 mysql-bin.index
[root@localhost mysql-t1]# mysqldump -h192.168.10.235 -uroot -p --all-databases --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 >/data/app/bin/b.sql
Enter password:
[root@localhost mysql-t1]# ll -th mysql-bin.*
-rw-r----- 1 mysql mysql 38 7月 24 21:10 mysql-bin.index
-rw-r----- 1 mysql mysql 154 7月 24 21:10 mysql-bin.000002
-rw-r----- 1 mysql mysql 2.0K 7月 24 21:10 mysql-bin.000001
3、在服务器恢复后，先将当天的数据库binlog日志倒入到sql文件。(方法任选一个)
mysqlbinlog /data/app/mysql-t1/mysql-bin.000002 >/root/a.sql
如果只是需要恢复该文件中的部分数据，那么可以
mysqlbinlog --start-position=292 --stop-position=709 /data/app/mysql-t1/mysql-bin.000002>/root/a.sql
另外建议先将今天的这个binlog备份在别的地方，以防操作失误导致恢复起来费劲。
4、数据恢复：
将备份数据导入数据库，然后再将binlog的sql导入数据库即可完成数据库恢复。
mysql备份时的数据
mysql> select * from base_roles;
+---------+-----------+--------------------+
| role_id | role_name | role_desc |
+---------+-----------+--------------------+
| 1 | Admin | Administrator |
| 10 | User | Authenticated User |
| 50 | ag_editor | Alert Group Editor |
| 111 | 233 | Authenticated User |
| 10000 | Anonymous | Anonymous User |
+---------+-----------+--------------------+
5 rows in set (0.00 sec)
恢复binlog日志
mysql> source /root/a.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from base_roles;
+---------+-----------+--------------------+
| role_id | role_name | role_desc |
+---------+-----------+--------------------+
| 1 | Admin | Administrator |
| 10 | User | Authenticated User |
| 50 | ag_editor | Alert Group Editor |
| 111 | 233 | Authenticated User |
| 121 | 222 | Authenticated User |
| 10000 | Anonymous | Anonymous User |
+---------+-----------+--------------------+
6 rows in set (0.00 sec)
完成数据恢复
