记一次用binlog恢复mysql数据记录

1.开启binlog日志记录
在my.cnf(ubuntu 下是/etc/mysql/my.cnf),添加
[mysqld]
log-bin=mysql-bin

启动mysql
service mysql restart

查看时候是否生效
show binary logs;
SHOW VARIABLES LIKE "%log_bin%";

mysql> SHOW VARIABLES LIKE "%log_bin%"
    -> ;
+---------------------------------+--------------------------------+
| Variable_name                   | Value                          |
+---------------------------------+--------------------------------+
| log_bin                         | ON                             |
| log_bin_basename                | /var/lib/mysql/mysql-bin       |
| log_bin_index                   | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)


2.对数据做一次完整备份,用mysqldump或者Percona Xtrabackup。

3.导出binlog日志。
mysqlbinlog --stop-datetime="2017-11-15 01:29:30" /var/lib/mysql/mysql-bin.000004 > /home/yy/bk_11_15_01_29.sql
选项支持
--start-datetime
--stop-datetime
--start-position
--stop-position

4.恢复到另一台数据库。
首先把刚才做的完整备份恢复到这台数据库。在用binlog恢复到确切的时间点。
mysql -uroot -p < /home/yy/bk_11_15_01_29.sql