设置binlog
编辑/etc/my.cnf文件，在[mysqld]标签里面添加
log-bin=mysql-bin
server-id=1
在这个情况下，我们的数据库是全部备份。
如果想备份特定的数据库，需要添加参数
binlog-do-db
不需要备份的数据库添加参数
binlog-ignore-db
实战
1.设置test数据库binlog日志，在/etc/my.cnf里面设置如下属性(这样设置不能恢复test数据库，只能test数据库里面的恢复表)
log-bin = mysql-bin
server-id = 1
read-only=0
binlog-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
2.重启mysqld服务
service mysqld restart
3.随意创建数据，检查数据库里面是否产生binlog
mysql> create table password(password varchar(255))
-> ;
Query OK, 0 rows affected (0.53 sec)[root@bogon mysql]# ls
auto.cnf client-cert.pem ibdata1 ibtmp1 mysql-bin.000002 mysql-bin.000005 mysql.sock.lock public_key.pem sys
ca-key.pem client-key.pem ib_logfile0 mysql mysql-bin.000003 mysql-bin.index performance_schema server-cert.pem test
ca.pem ib_buffer_pool ib_logfile1 mysql-bin.000001 mysql-bin.000004 mysql.sock private_key.pem server-key.pem
4.删除test数据库中的表
drop database test;
create database test;
5.恢复数据（手动恢复）
[root@bogon mysql]# mysqlbinlog --start-datetime="2017-04-20 9:59:59" mysql-bin.000005 | mysql -u root -p
Enter password:
ERROR 1146 (42S02) at line 50: Table 'test.user' doesn't exist
[root@bogon mysql]# mysqlbinlog --start-datetime="2017-04-20 9:59:59" mysql-bin.000002 | mysql -u root -p
Enter password:
[root@bogon mysql]# mysqlbinlog --start-datetime="2017-04-20 9:59:59" mysql-bin.000003 | mysql -u root -p
Enter password:
[root@bogon mysql]# mysqlbinlog --start-datetime="2017-04-20 9:59:59" mysql-bin.000004 | mysql -u root -p
Enter password:
[root@bogon mysql]# mysqlbinlog --start-datetime="2017-04-20 9:59:59" mysql-bin.000005 | mysql -u root -p
Enter password:
ERROR 1050 (42S01) at line 31: Table 'password' already exists
[root@bogon mysql]# mysql -uroot -pmysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| password |
+----------------+
1 row in set (0.00 sec)
mysqlbinlog操作总结
1.从binary log文件中执行事件会导致数据的修改，表示要重做数据
[root@bogon /]# mysqlbinlog binlog_files | mysql -u root -p
2.查看binlog的时间及位置信息我们可以讲binlog内容输出到终端或文本文件
[root@localhost /]# mysqlbinlog binlog_files | more
[root@localhost /]# mysqlbinlog binlog_files > tmpfile
3.将二进制日志输出到文件非常有用，比如我们想要删除某些我们不想执行的语句。
通过文本文件导入到数据库
[root@localhost /]# mysql -u root -p < tmpfile
4.多个binlog log日志的还原最好将所有文件使用一个连接完成，如果使用不同连接的话有时会导致不安全
[root@localhost /]# mysqlbinlog binlog.000001 | mysql -u root -p # DANGER!!
[root@localhost /]# mysqlbinlog binlog.000002 | mysql -u root -p # DANGER!!
如果第一个日志包含创建临时表语句CREATE TEMPORARY TABLE，第二个日志要使用该临时表，第一个导入binlog日志的进程退出后临时表会被删除，执行第二个日志文件要使用临时表时会因找不到而报 “unknown table.”
建议的方法：
- 所有二进制文件放在单个连接里
[root@localhost /]# mysqlbinlog binlog.000001 binlog.000002 ... | mysql -u root -p
- 将所有二进制文件写在一个文件里执行
[root@localhost /]# mysqlbinlog binlog.000001 > /tmp/statements.sql
[root@localhost /]# mysqlbinlog binlog.000002 >> /tmp/statements.sql
[root@localhost /]# mysql -u root -p -e "source /tmp/statements.sql"
使用方法二如果二进制文件里包含GTID信息需要过滤掉
[root@localhost /]# mysqlbinlog --skip-gtids binlog.000001 > /tmp/dump.sql
[root@localhost /]# mysqlbinlog --skip-gtids binlog.000002 >> /tmp/dump.sql
[root@localhost /]# mysql -u root -p -e "source /tmp/dump.sql"
通过事件的时间来恢复
- 我们可以通过参数--start-datetime 和 --stop-datetime指定恢复binlog日志的起止时间点，时间使用DATETIME格式。
比如在时间点2005-04-20 10:00:00我们删除掉一个库，我们要恢复该时间点前的所有日志
[root@localhost /]# mysqlbinlog --stop-datetime="2005-04-20 9:59:59" /usr/local/mysql/data/binlog.123456 | mysql -u root -p
- 我们可能几个小时后才发现该错误，后面又有一系列的增删查改等操作，我们还需要恢复后续的binlog，我们可以指定起始时间
[root@localhost /]# mysqlbinlog --start-datetime="2005-04-20 10:01:00" /usr/local/mysql/data/binlog.123456 | mysql -u root -p
- 通过该种方法恢复我们需要通过查看binlog日志知道发生误操作的确切时间点，查看日志我们可以先将日志输出到文本里
[root@localhost /]# mysqlbinlog /usr/local/mysql/data/binlog.123456 > /tmp/mysql_restore.sql
通过事件的位置来恢复
我们可以通过参数--start-position 和 --stop-position指定恢复binlog日志的起止位置点，通过位置的恢复需要我们有更加精细的操作，例如在某个时间点我们执行了错误的语句，且这个时间点前后都有大并发操作，要确定破坏性sql的时间点，我们可以先导出大致的时间段的日志到文件以缩小查找范围，再去分析和确定
[root@localhost /]# mysqlbinlog --start-datetime="2005-04-20 9:55:00" --stop-datetime="2005-04-20 10:05:00" /usr/local/mysql/data/binlog.123456 > /tmp/mysql_restore.sql
确定好需要跳过的位置之后，我们就可以进行恢复了
[root@localhost /]# mysqlbinlog --stop-position=368312 /usr/local/mysql/data/binlog.123456 | mysql -u root -p
[root@localhost /]# mysqlbinlog --start-position=368315 /usr/local/mysql/data/binlog.123456 | mysql -u root -p
文章评论