为了让大家更好的学习mysql的配置文件，以及日志管理，我先将my.cnf自定好，然后重启mysql服务，代码给大家贴出来。
[root@db01 mysql]# vim my.cnf
[mysqld]
basedir=/application/mysql #mysql根目录
datadir=/application/mysql/data #数据目录
socket=/application/mysql/tmp/mysql.sock #sock文件地址
port=3306 #启动端口号
server_id=10 #server-id来唯一的标识某个数据库实例
log-error=/application/mysql/data/db01.err #错误日志信息
log-bin=/application/mysql/data/binlog/mysql-bin #设置二进制日志文件
binlog-format=ROW #二进制信息记录形式
sync_binlog=1 # 1表示commit时写入binlog，0达到binlog-cache内存大小写入binlog
skip_name_resolve #禁止域名解析
default_storage_engine=innodb #设置默认存储引擎为innodb
innodb_data_file_path=ibdata1:12M;ibdata2:50M:autoextend #存储数据节点设置
character-set-server=utf8 #默认字符集
autocommit=0 #是否自动提交：0否，1是前面也讲到了binlog是用来作数据备份和恢复用的，那么我们时间操作一下，binlog到底是怎么样记录日志信息。
- 查看当前binlog信息：
#当前使用mysql-bin.000002日志文件，定位在120。mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 120 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
其实我们可以去binlog目录查看日志文件：[root@db01 mysql]# cd data/binlog/ [root@db01 binlog]# ll total 12 -rw-rw---- 1 mysql mysql 143 Aug 12 16:13 mysql-bin.000001 -rw-rw---- 1 mysql mysql 120 Aug 12 16:13 mysql-bin.000002 -rw-rw---- 1 mysql mysql 96 Aug 12 16:13 mysql-bin.index [root@db01 binlog]# - 创建测试数据库：
mysql> create database table01; Query OK, 1 row affected (0.11 sec) mysql> use table01; Database changed mysql> create table t1(id int,name varchar(20)); Query OK, 0 rows affected (0.11 sec) - 再次查看二进制日志信息
#此时二进制信息已经新增了日志，定位到343。mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 343 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
#show binlog events in 'mysql-bin.000002';查看二进制文件详情信息mysql> show binlog events in 'mysql-bin.000002'; +------------------+-----+-------------+-----------+-------------+---------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+---------------------------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 10 | 120 | Server ver: 5.6.36-log, Binlog ver: 4 | | mysql-bin.000002 | 120 | Query | 10 | 223 | create database table01 | | mysql-bin.000002 | 223 | Query | 10 | 343 | use `table01`; create table t1(id int,name varchar(20)) | +------------------+-----+-------------+-----------+-------------+---------------------------------------------------------+ 3 rows in set (0.00 sec)#Pos：开始位置号
#end_log_pos：结束位置点
#event_type：事件类型，每个文件的开始都存在一个format_desc，相当于文件头信息，也就是说，所有二进制信息都是从120位置开始记录。
#通过详情信息我们可以看到：
120-223之间是创建create database table01;
223-343之间：use table01;create table t1(id int ,name varchar(20));
-
当新增数据后：
mysql> show binlog events in 'mysql-bin.000002'; +------------------+------+-------------+-----------+-------------+---------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+-------------+-----------+-------------+---------------------------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 10 | 120 | Server ver: 5.6.36-log, Binlog ver: 4 | | mysql-bin.000002 | 120 | Query | 10 | 223 | create database table01 | | mysql-bin.000002 | 223 | Query | 10 | 343 | use `table01`; create table t1(id int,name varchar(20)) | | mysql-bin.000002 | 343 | Query | 10 | 418 | BEGIN | | mysql-bin.000002 | 418 | Table_map | 10 | 469 | table_id: 70 (table01.t1) | | mysql-bin.000002 | 469 | Write_rows | 10 | 522 | table_id: 70 flags: STMT_END_F | | mysql-bin.000002 | 522 | Table_map | 10 | 573 | table_id: 70 (table01.t1) | | mysql-bin.000002 | 573 | Write_rows | 10 | 620 | table_id: 70 flags: STMT_END_F | | mysql-bin.000002 | 620 | Xid | 10 | 651 | COMMIT /* xid=14 */ | | mysql-bin.000002 | 651 | Query | 10 | 726 | BEGIN | | mysql-bin.000002 | 726 | Table_map | 10 | 777 | table_id: 70 (table01.t1) | | mysql-bin.000002 | 777 | Write_rows | 10 | 824 | table_id: 70 flags: STMT_END_F | | mysql-bin.000002 | 824 | Table_map | 10 | 875 | table_id: 70 (table01.t1) | | mysql-bin.000002 | 875 | Write_rows | 10 | 924 | table_id: 70 flags: STMT_END_F | | mysql-bin.000002 | 924 | Table_map | 10 | 975 | table_id: 70 (table01.t1) | | mysql-bin.000002 | 975 | Write_rows | 10 | 1025 | table_id: 70 flags: STMT_END_F | | mysql-bin.000002 | 1025 | Xid | 10 | 1056 | COMMIT /* xid=25 */ | +------------------+------+-------------+-----------+-------------+---------------------------------------------------------+ 17 rows in set (0.00 sec)#因为我们在my.cnf中采用的binlog_fromat=row模式，所以ddl直接显示语句，而dml（insert之类的语句）则显示的是table_id: 70 (table01.t1)之类的我们看不懂的语句。
查看二进制源文件：同时我们可以从mysql-bin.000002源文件查看信息：
[root@db01 mysql]# cd data/binlog/
[root@db01 binlog]# mysqlbinlog mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190812 16:13:10 server id 10 end_log_pos 120 CRC32 0x657b4e4d Start: binlog v 4, server v 5.6.36-log created 190812 16:13:10 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
lh9RXQ8KAAAAdAAAAHgAAAABAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACWH1FdEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAU1O
e2U=
'/*!*/;
# at 120
#190812 16:38:13 server id 10 end_log_pos 223 CRC32 0xf887e7a7 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1565599093/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
.
.
省略部分
.
.
# at 1025
#190812 16:53:27 server id 10 end_log_pos 1056 CRC32 0xf0f2e88a Xid = 25
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@db01 binlog]# 但是这种查看方式太乱了，简直让人瞎眼，下面提供一种方式去查看源文件。
mysqlbinlog --base64-output=decode-rows -v my-bin.000002 #查看所有二进制信息
mysqlbinlog --start-position=120 --stop-position=721 my-bin.000002 #截取二进制信息我们来试试吧：
[root@db01 binlog]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190812 16:13:10 server id 10 end_log_pos 120 CRC32 0x657b4e4d Start: binlog v 4, server v 5.6.36-log created 190812 16:13:10 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
# at 120
#190812 16:38:13 server id 10 end_log_pos 223 CRC32 0xf887e7a7 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1565599093/*!*/;
SET @@session.pseudo_thread_id=1/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database table01
/*!*/;
# at 223
#190812 16:38:46 server id 10 end_log_pos 343 CRC32 0x7e8cfa15 Query thread_id=1 exec_time=1 error_code=0
use `table01`/*!*/;
SET TIMESTAMP=1565599126/*!*/;
create table t1(id int,name varchar(20))
/*!*/;
# at 343
#190812 16:51:19 server id 10 end_log_pos 418 CRC32 0xf8bdcbfa Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1565599879/*!*/;
BEGIN
/*!*/;
# at 418
#190812 16:51:19 server id 10 end_log_pos 469 CRC32 0x20b7e6bb Table_map: `table01`.`t1` mapped to number 70
# at 469
#190812 16:51:19 server id 10 end_log_pos 522 CRC32 0x6c9738c7 Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `table01`.`t1`
### SET
### @1=1
### @2='为道日损'
# at 522
#190812 16:51:36 server id 10 end_log_pos 573 CRC32 0xb6a78e09 Table_map: `table01`.`t1` mapped to number 70
# at 573
#190812 16:51:36 server id 10 end_log_pos 620 CRC32 0x4fa78d67 Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `table01`.`t1`
### SET
### @1=2
### @2='客户'
# at 620
#190812 16:51:53 server id 10 end_log_pos 651 CRC32 0x9c5f88a9 Xid = 14
COMMIT/*!*/;
# at 651
#190812 16:52:36 server id 10 end_log_pos 726 CRC32 0x480ffdc1 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1565599956/*!*/;
BEGIN
/*!*/;
# at 726
#190812 16:52:36 server id 10 end_log_pos 777 CRC32 0x7bafa8ec Table_map: `table01`.`t1` mapped to number 70
# at 777
#190812 16:52:36 server id 10 end_log_pos 824 CRC32 0x23444f45 Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `table01`.`t1`
### SET
### @1=3
### @2='客户'
# at 824
#190812 16:52:47 server id 10 end_log_pos 875 CRC32 0xa9be154f Table_map: `table01`.`t1` mapped to number 70
# at 875
#190812 16:52:47 server id 10 end_log_pos 924 CRC32 0x50a82c5b Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `table01`.`t1`
### SET
### @1=4
### @2='客00户'
# at 924
#190812 16:53:19 server id 10 end_log_pos 975 CRC32 0xc451fba2 Table_map: `table01`.`t1` mapped to number 70
# at 975
#190812 16:53:19 server id 10 end_log_pos 1025 CRC32 0xca3d0235 Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `table01`.`t1`
### SET
### @1=5
### @2='客444户'
# at 1025
#190812 16:53:27 server id 10 end_log_pos 1056 CRC32 0xf0f2e88a Xid = 25
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@db01 binlog]# #因为二进制日志信息比较重要，所以不给大家省代码，大家将就看看。
我们从中取一段讲解一下其中的意思：
# at 418
#190812 16:51:19 server id 10 end_log_pos 469 CRC32 0x20b7e6bb Table_map: `table01`.`t1` mapped to number 70
# at 469
#190812 16:51:19 server id 10 end_log_pos 522 CRC32 0x6c9738c7 Write_rows: table id 70 flags: STMT_END_F
### INSERT INTO `table01`.`t1`
### SET
### @1=1
### @2='为道日损'@1：表示第一列 ，@2表示第二列
由此可见，我们可以从最后四行中很直观的看出，这个insert 操作对于该表修改了什么数据，修改后的数据是什么。
截取二进制文件：mysqlbinlog --start-position=875 --stop-position=924 mysql-bin.000002
[root@db01 binlog]# mysqlbinlog --start-position=875 --stop-position=924 mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190812 16:13:10 server id 10 end_log_pos 120 CRC32 0x657b4e4d Start: binlog v 4, server v 5.6.36-log created 190812 16:13:10 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
lh9RXQ8KAAAAdAAAAHgAAAABAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACWH1FdEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAU1O
e2U=
'/*!*/;
# at 875
#190812 16:52:47 server id 10 end_log_pos 924 CRC32 0x50a82c5b Write_rows: table id 70 flags: STMT_END_F
BINLOG '
3yhRXR4KAAAAMQAAAJwDAAAAAEYAAAAAAAEAAgAC//wEAAAACOWuojAw5oi3WyyoUA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;同时，我们可以将其到处为sql文件。
root@db01 binlog]# mysqlbinlog --start-position=875 --stop-position=924 mysql-bin.000002 >/tmp/binlog.sql
[root@db01 binlog]# cd /tmp/
[root@db01 tmp]# ll
total 4
-rw-r--r-- 1 root root 965 Aug 12 17:31 binlog.sql
[root@db01 tmp]# cat binlog.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190812 16:13:10 server id 10 end_log_pos 120 CRC32 0x657b4e4d Start: binlog v 4, server v 5.6.36-log created 190812 16:13:10 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
lh9RXQ8KAAAAdAAAAHgAAAABAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACWH1FdEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAU1O
e2U=
'/*!*/;
# at 875
#190812 16:52:47 server id 10 end_log_pos 924 CRC32 0x50a82c5b Write_rows: table id 70 flags: STMT_END_F
BINLOG '
3yhRXR4KAAAAMQAAAJwDAAAAAEYAAAAAAAEAAgAC//wEAAAACOWuojAw5oi3WyyoUA==
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
用binlog恢复数据：
1.先将插入数据部分的日志导出：
mysqlbinlog --start-position=418 --stop-position=1056 mysql-bin.000002 >/tmp/binlog.sql2.删除表t1中的数据（不是删除表哦）
mysql> delete from t1;
Query OK, 5 rows affected (0.02 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t1;
Empty set (0.00 sec)3.设置临时关闭二进制日志（如果这个步骤出错，请commit一下）
mysql> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)4.导入备份的二进制文件，并查看数据。
mysql> source /tmp/binlog.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t1;
+------+--------------+
| id | name |
+------+--------------+
| 1 | 为道日损 |
| 2 | 客户 |
| 3 | 客户 |
| 4 | 客00户 |
| 5 | 客444户 |
+------+--------------+
5 rows in set (0.00 sec)#如果我们恢复的过程中，不想将之前的误操作也在恢复中，那么我只需截取其中想要的的二进制进行恢复即可。
二进制管理其它操作：
刷线二进制日志：
flush logs删除二进制：
- 默认情况下，不会删除旧的日志文件。
- 根据存在时间删除日志：
SET GLOBAL expire_logs_days = 7;或
PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;- 根据文件名删除日志：
PURGE BINARY LOGS TO 'mysql-bin.000010';
最后修改于 2019-08-12 16:14:41
如果觉得我的文章对你有用，请随意赞赏
扫一扫支付
