mysql数据通过binlog恢复

1、首先要确保数据库启动mysqlbinlogmysql> 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.sqlEnter 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)完成数据恢复