将来的你
一定会感谢现在拼命努力的自己

Mysql之binlog日志说明及利用binlog日志恢复数据操作记录

一般来说开启binlog日志大概会有1%的性能损耗。

binlog日志有两个最重要的使用场景 
1)MySQL主从复制:MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves来达到 
master-slave数据一致的目的。 
2)自然就是数据恢复了,通过使用mysqlbinlog工具来使恢复数据。

binlog日志包括两类文件 
1)二进制日志索引文件(文件名后缀为.index)用于记录所有的二进制文件 
2)二进制日志文件(文件名后缀为.00000*)记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。

二、开启binlog日志功能 
1)编辑打开mysql配置文件/etc/mys.cnf 
[root@vm-002 ~]# vim /etc/my.cnf 
在[mysqld] 区块添加 
log-bin=mysql-bin 确认是打开状态(mysql-bin 是日志的基本名或前缀名)

注意:每次服务器(数据库)重启,服务器会调用flush logs;,新创建一个binlog日志!

2)重启mysqld服务使配置生效 
[root@vm-002 ~]# /etc/init.d/mysqld stop 
[root@vm-002 ~]# /etc/init.d/mysqld restart 
Stopping mysqld: [ OK ] 
Starting mysqld: [ OK ]

3)查看binlog日志是否开启 
mysql> show variables like 'log_%'; 
+---------------------------------+---------------------+ 
| Variable_name | Value | 
+---------------------------------+---------------------+ 
log_bin | ON | 
| log_bin_trust_function_creators | OFF | 
| log_bin_trust_routine_creators | OFF | 
| log_error | /var/log/mysqld.log | 
| log_output | FILE | 
| log_queries_not_using_indexes | OFF | 
| log_slave_updates | OFF | 
| log_slow_queries | OFF | 
| log_warnings | 1 | 
+---------------------------------+---------------------+ 
9 rows in set (0.00 sec)

三、常用的binlog日志操作命令 
1)查看所有binlog日志列表 
mysql> show master logs; 
+------------------+-----------+ 
| Log_name | File_size | 
+------------------+-----------+ 
| mysql-bin.000001 | 149 | 
| mysql-bin.000002 | 4102 | 
+------------------+-----------+ 
2 rows in set (0.00 sec)

2)查看master状态,即最后(最新)一个binlog日志的编号名称,及其最后一个操作事件pos结束点(Position)值 
mysql> show master status; 
+------------------+----------+--------------+------------------+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000002 | 4102 | | | 
+------------------+----------+--------------+------------------+ 
1 row in set (0.00 sec)

3)flush刷新log日志,自此刻开始产生一个新编号的binlog日志文件 
mysql> flush logs; 
Query OK, 0 rows affected (0.13 sec)

mysql> show master logs; 
+------------------+-----------+ 
| Log_name | File_size | 
+------------------+-----------+ 
| mysql-bin.000001 | 149 | 
| mysql-bin.000002 | 4145 | 
| mysql-bin.000003 | 106 | 
+------------------+-----------+ 
3 rows in set (0.00 sec)

注意:每当mysqld服务重启时,会自动执行此命令,刷新binlog日志;在mysqldump备份数据时加 -F 选项也会刷新binlog日志;

4)重置(清空)所有binlog日志 
mysql> reset master; 
Query OK, 0 rows affected (0.12 sec)

mysql> show master logs; 
+------------------+-----------+ 
| Log_name | File_size | 
+------------------+-----------+ 
| mysql-bin.000001 | 106 | 
+------------------+-----------+ 
1 row in set (0.00 sec)

四、查看binlog日志内容,常用有两种方式 
1)使用mysqlbinlog自带查看命令法: 
注意: 
-->binlog是二进制文件,普通文件查看器cat、more、vim等都无法打开,必须使用自带的mysqlbinlog命令查看 
-->binlog日志与数据库文件在同目录中 
-->在MySQL5.5以下版本使用mysqlbinlog命令时如果报错,就加上 “--no-defaults”选项

查看mysql的数据存放目录,从下面结果可知是/var/lib//mysql 
[root@vm-002 ~]# ps -ef|grep mysql 
root 9791 1 0 21:18 pts/0 00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/mysqld.pid --basedir=/usr --user=mysql 
mysql 9896 9791 0 21:18 pts/0 00:00:00 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock 
root 9916 9699 0 21:18 pts/0 00:00:00 mysql -px xxxx 
root 9919 9715 0 21:23 pts/1 00:00:00 grep --color mysql

[root@vm-002 ~]# cd /var/lib/mysql/ 
[root@vm-002 mysql]# ls 
ibdata1 ib_logfile0 ib_logfile1 mysql mysql-bin.000001 mysql-bin.000002 mysql-bin.index mysql.sock ops test

使用mysqlbinlog命令查看binlog日志内容,下面截取其中的一个片段分析: 
[root@vm-002 mysql]# mysqlbinlog mysql-bin.000002 
.............. 
# at 624 
#160925 21:29:53 server id 1 end_log_pos 796 Query thread_id=3 exec_time=0 error_code=0 
SET TIMESTAMP=1474810193/*!*/; 
insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2') #执行的sql语句 
/*!*/; 
# at 796 
#160925 21:29:53 server id 1 end_log_pos 823 Xid = 17 #执行的时间 
.............

解释: 
server id 1 : 数据库主机的服务号; 
end_log_pos 796: sql结束时的pos节点 
thread_id=11: 线程号

2)上面这种办法读取出binlog日志的全文内容比较多,不容易分辨查看到pos点信息 
下面介绍一种更为方便的查询命令: 
命令格式: 
mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]; 
参数解释: 
IN 'log_name' :指定要查询的binlog文件名(不指定就是第一个binlog文件) 
FROM pos :指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算) 
LIMIT [offset,] :偏移量(不指定就是0) 
row_count :查询总条数(不指定就是所有行)

mysql> show master logs; 
+------------------+-----------+ 
| Log_name | File_size | 
+------------------+-----------+ 
| mysql-bin.000001 | 125 | 
| mysql-bin.000002 | 823 | 
+------------------+-----------+ 
2 rows in set (0.00 sec)

mysql> show binlog events in 'mysql-bin.000002'\G; 
*************************** 1. row *************************** 
Log_name: mysql-bin.000002 
Pos: 4 
Event_type: Format_desc 
Server_id: 1 
End_log_pos: 106 
Info: Server ver: 5.1.73-log, Binlog ver: 4 
*************************** 2. row *************************** 
Log_name: mysql-bin.000002 
Pos: 106 
Event_type: Query 
Server_id: 1 
End_log_pos: 188 
Info: use `ops`; drop table customers 
*************************** 3. row *************************** 
Log_name: mysql-bin.000002 
Pos: 188 
Event_type: Query 
Server_id: 1 
End_log_pos: 529 
Info: use `ops`; CREATE TABLE IF NOT EXISTS `member` ( 
`id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
`name` varchar(16) NOT NULL, 
`sex` enum('m','w') NOT NULL DEFAULT 'm', 
`age` tinyint(3) unsigned NOT NULL, 
`classid` char(6) DEFAULT NULL, 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
*************************** 4. row *************************** 
Log_name: mysql-bin.000002 
Pos: 529 
Event_type: Query 
Server_id: 1 
End_log_pos: 596 
Info: BEGIN 
*************************** 5. row *************************** 
Log_name: mysql-bin.000002 
Pos: 596 
Event_type: Intvar 
Server_id: 1 
End_log_pos: 624 
Info: INSERT_ID=1 
*************************** 6. row *************************** 
Log_name: mysql-bin.000002 
Pos: 624 
Event_type: Query 
Server_id: 1 
End_log_pos: 796 
Info: use `ops`; insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2') 
*************************** 7. row *************************** 
Log_name: mysql-bin.000002 
Pos: 796 
Event_type: Xid 
Server_id: 1 
End_log_pos: 823 
Info: COMMIT /* xid=17 */ 
7 rows in set (0.00 sec)

ERROR: 
No query specified

mysql>

上面这条语句可以将指定的binlog日志文件,分成有效事件行的方式返回,并可使用limit指定pos点的起始偏移,查询条数! 
如下操作示例: 
a)查询第一个(最早)的binlog日志: 
mysql> show binlog events\G;

b)指定查询 mysql-bin.000002这个文件: 
mysql> show binlog events in 'mysql-bin.000002'\G;

c)指定查询 mysql-bin.000002这个文件,从pos点:624开始查起: 
mysql> show binlog events in 'mysql-bin.000002' from 624\G;

d)指定查询 mysql-bin.000002这个文件,从pos点:624开始查起,查询10条(即10条语句) 
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10\G;

e)指定查询 mysql-bin.000002这个文件,从pos点:624开始查起,偏移2行(即中间跳过2个),查询10条 
mysql> show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;

五、利用binlog日志恢复mysql数据

以下对ops库的member表进行操作 
mysql> use ops; 
mysql> CREATE TABLE IF NOT EXISTS `member` ( 
-> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
-> `name` varchar(16) NOT NULL, 
-> `sex` enum('m','w') NOT NULL DEFAULT 'm', 
-> `age` tinyint(3) unsigned NOT NULL, 
-> `classid` char(6) DEFAULT NULL, 
-> PRIMARY KEY (`id`) 
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 
Query OK, 0 rows affected (0.10 sec)

mysql> show tables; 
+---------------+ 
| Tables_in_ops | 
+---------------+ 
| member | 
+---------------+ 
1 row in set (0.00 sec)

mysql> desc member; 
+---------+---------------------+------+-----+---------+----------------+ 
| Field | Type | Null | Key | Default | Extra | 
+---------+---------------------+------+-----+---------+----------------+ 
| id | int(10) unsigned | NO | PRI | NULL | auto_increment | 
| name | varchar(16) | NO | | NULL | | 
| sex | enum('m','w') | NO | | m | | 
| age | tinyint(3) unsigned | NO | | NULL | | 
| classid | char(6) | YES | | NULL | | 
+---------+---------------------+------+-----+---------+----------------+ 
5 rows in set (0.00 sec)

事先插入两条数据 
mysql> insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2'); 
Query OK, 2 rows affected (0.08 sec) 
Records: 2 Duplicates: 0 Warnings: 0 
mysql> select * from member; 
+----+-----------+-----+-----+---------+ 
| id | name | sex | age | classid | 
+----+-----------+-----+-----+---------+ 
| 1 | wangshibo | m | 27 | cls1 | 
| 2 | guohuihui | w | 27 | cls2 | 
+----+-----------+-----+-----+---------+ 
2 rows in set (0.00 sec)

下面开始进行场景模拟: 
1) 
ops库会在每天凌晨4点进行一次完全备份的定时计划任务,如下: 
[root@vm-002 ~]# crontab -l 
0 4 * * * /usr/bin/mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz

这里手动执行下,将ops数据库备份到/opt/backup/ops_$(date +%F).sql.gz文件中: 
[root@vm-002 ~]# mysqldump -uroot -p -B -F -R -x --master-data=2 ops|gzip >/opt/backup/ops_$(date +%F).sql.gz 
Enter password: 
[root@vm-002 ~]# ls /opt/backup/ 
ops_2016-09-25.sql.gz 

赞(0) 打赏
声明:本站发布的内容(图片、视频和文字)以原创、转载和分享网络内容为主,若涉及侵权请及时告知,将会在第一时间删除,联系邮箱:contact@3yyy.top。文章观点不代表本站立场。本站原创内容未经允许不得转载:idsan笔记 » Mysql之binlog日志说明及利用binlog日志恢复数据操作记录
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏