您的位置: 首页  >  文章  >  MySQL日志--二进制日志binlog

MySQL日志--二进制日志binlog

分类: 文章 • 2024-03-12 22:57:08

MySQL日志--二进制日志binlog

Binary是二进制日志，记录的是被修改数据的语句。

MySQL日志--二进制日志binlog

Bin log里面包含的统统是被修改的，如果没有对数据进行增删改的操作，那么是不需要记录到bin log日志里面的。如果是查询数据是不需要记录到这里的，那么bin log日志主要是出于什么目的呢？Bin log日志也称其为复制日志，主要是在某些场景下面来使用的。

 

比如在淘宝里面。用户下订单的可能性比用户搜索商品的可能性要低，红色代表一台服务器，蓝色代表另外三台服务器。用户向红色的服务器下订单的这个数据量远远要小于用户搜索产品的数据，如果从蓝色的一台服务器进行搜索产品可能无法提供淘宝这么大规模的搜索，即在一秒内为几百万用户通过搜索得到的结果。这个时候怎么办？就会专门拿一台服务器作为数据保存的，如图上红色的服务器，用来写数据的，现在商家发布了一个新产品，在蓝色的三台服务器上没有数据的，怎么才能被用户搜索到？这个时候可以通过主从复制方式将对红色服务器上数据库做的修改传到另外蓝色三台服务器上的数据库里面。怎么传信息？因为红色服务器上的数据库插入的数据，更新的数据，修改的数据。增删改三种行为。这些操作都记录到bin log日志里面。这个时候bin log通过网络协议的同步复制给这些主机，这样实现数据的同步。

之后多个用户查询的时候，有的用户可能在蓝色的第一台服务器上进行查询，或者第二台，或者第三台，这样就可以实现负载均衡。

Bin log对搜索是不需要缓存机制的，对数据没有修改的是不需要产生网络数据流同步给其他数据库，所以select行为是不需要记录到bin log里面的。

MySQL日志--二进制日志binlog

Bin log不仅仅使用在主从复制还可以用来实现对数据误操作的恢复。

 

假设在星期天对数据库做了全量备份，接着到了周一用户对数据库里面的数据做了修改的操作产生了新的数据，蓝色的部分表示变化的部分，到了周二的时候用户做了误操作，将所有的数据干掉了，这个时候怎么办?很简单,因为之前星期天做了备份，可以将图上红色部分的数据进行还原，但是还有星期一产生修改的数据还没还原已经周二也有用户修改了数据，怎么办？由于bin log对做的数据的修改都有序号和时间点，比如在周二11点50分钟第32秒这个时刻做了误操作，那么在11点49分之前所有的binlog里面的记录就相当于星期一之前所有对数据修改的操作。那么在11点51分之后也有用户对数据进行修改，也可以通过binlog日志对其还原。即绕过误操作时间点可以通过binlog进行分段的方式提取有效的数据，将其重做到数据库里面。所以binlog也可以实现备份与还原。

MySQL日志--二进制日志binlog

Binlog启动很简单，在/ect/my.cnf的[mysqld]里面加上log-bin的开关。

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

 

[mysql]

user=root

password=123456

 

在配置文件加上log-bin选项，每次启动的时候都会重新生成mysqld-bin这个二进制文件。从1开始一直增加序号。从1-3代表每次服务器的启停。

[[email protected] test]# ls /var/lib/mysql

auto.cnf     ib_logfile1    mysqld-bin.000001  mysqld-bin.index   test

ibdata1      localhost.log  mysqld-bin.000002  mysql.sock

ib_logfile0  mysql          mysqld-bin.000003 performance_schema

如果mysql服务启动过很多次以后想知道最近启动生成的是哪个文件，可以通过mysqld-bin.index这个文件查看，这个是索引文件。

[[email protected] mysql]# cat mysqld-bin.index

./mysqld-bin.000001

./mysqld-bin.000002

./mysqld-bin.000003

罗列出服务器启动产生的bin log文件 最近产生的文件是***为3，下次重启产生的新文件***是4。

[[email protected] mysql]# file mysqld-bin.*

mysqld-bin.000001: MySQL replication log

mysqld-bin.000002: MySQL replication log

mysqld-bin.000003: MySQL replication log

mysqld-bin.index:  ASCII text

查看mysqld-bin的文件类型，可以看到是用来做复制的日志文件。注意是不可以以写字板的方式打开mysqld-bin文件，因为这个是二进制文件类型。

 

Binlog默认是以主机名或者进程名字加上bin，后面跟上nnn...，nnn是会变化的，随着服务的启动而变化。

 

Mysql提供了工具，mysqlbinlog这条命令，这是用来专门发现二进制里面数据是怎么操作的。

 

 

 

现在数据库开启了binlog日志，登入数据库做一系列的操作。

mysql> use test;

Database changed

mysql> create table binlog_test(id int);

Query OK, 0 rows affected (0.06 sec)

 

mysql> insert into binlog_test values(1);

Query OK, 1 row affected (0.01 sec)

 

 

 

上面做了插入操作，这个操作会被记录到binlog日志里面即 mysqld-bin.000003文件里面。

[[email protected] mysql]# cd /var/lib/mysql

[[email protected] mysql]# mysqlbinlog mysqld-bin.000003

敲完上面的命令可以出现下面所以的信息。

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

create table binlog_test(id int)

/*!*/;

# at 226

#171025 15:18:43 server id 1  end_log_pos 305 CRC32 0x729ef9a2 Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1508915923/*!*/;

BEGIN

/*!*/;

# at 305

#171025 15:18:43 server id 1  end_log_pos 412 CRC32 0x91ae39dc Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1508915923/*!*/;

insert into binlog_test values(1)

/*!*/;

# at 412

#171025 15:18:43 server id 1  end_log_pos 443 CRC32 0x73adf23c Xid = 11

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET [email protected]_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

[[email protected] mysql]# clear

 

[[email protected] mysql]# mysqlbinlog mysqld-bin.000003

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @[email protected]@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#171025 15:02:24 server id 1  end_log_pos 120 CRC32 0x29506e83 Start: binlog v 4, server v 5.6.38-log created 171025 15:02:24 at startup

# Warning: this binlog is either in use or was not closed properly.

ROLLBACK/*!*/;

BINLOG '

ADfwWQ8BAAAAdAAAAHgAAAABAAQANS42LjM4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAN/BZEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAYNu

UCk=

'/*!*/;

# at 120

#171025 15:18:24 server id 1  end_log_pos 226 CRC32 0x203596fc Query thread_id=2 exec_time=0 error_code=0

use `test`/*!*/;

SET TIMESTAMP=1508915904/*!*/;

SET @@session.pseudo_thread_id=2/*!*/;

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=8/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

create table binlog_test(id int)

/*!*/;

# at 226

#171025 15:18:43 server id 1  end_log_pos 305 CRC32 0x729ef9a2Querythread_id=2exec_time=0error_code=0

SET TIMESTAMP=1508915923/*!*/;

BEGIN

/*!*/;

# at 305

#171025 15:18:43 server id 1  end_log_pos 412 CRC32 0x91ae39dcQuerythread_id=2exec_time=0error_code=0

SET TIMESTAMP=1508915923/*!*/;

insert into binlog_test values(1)

/*!*/;

# at 412

#171025 15:18:43 server id 1  end_log_pos 443 CRC32 0x73adf23c Xid = 11

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET [email protected]_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

 

上面红色可以看到对binglog_test这张表进行插入操作的时候会在binlog里面产生记录。

 

之后再测试更新操作

mysql> update binlog_test set id=id+1;

Query OK, 1 row affected (0.08 sec)

Rows matched: 1  Changed: 1  Warnings: 0

 

# at 443

#171025 15:38:04 server id 1  end_log_pos 522 CRC32 0x08ef0476Querythread_id=3exec_time=0error_code=0

SET TIMESTAMP=1508917084/*!*/;

BEGIN

/*!*/;

# at 522

#171025 15:38:04 server id 1  end_log_pos 626 CRC32 0x9aca7f93Querythread_id=3exec_time=0error_code=0

SET TIMESTAMP=1508917084/*!*/;

update binlog_test set id=id+1

/*!*/;

# at 626

#171025 15:38:04 server id 1  end_log_pos 657 CRC32 0xf128e69fXid = 18

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET [email protected]_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

可以看到更新也产生了记录。

 

 

 

此时做了误操作将所有的数据删除了，查看binlog日志。

mysql> delete from binlog_test;

Query OK, 1 row affected (0.02 sec)

# at 443

#171025 15:38:04 server id 1  end_log_pos 522 CRC32 0x08ef0476 Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1508917084/*!*/;

BEGIN

/*!*/;

# at 522

#171025 15:38:04 server id 1  end_log_pos 626 CRC32 0x9aca7f93 Query thread_id=3 exec_time=0 error_code=0

SET TIMESTAMP=1508917084/*!*/;

update binlog_test set id=id+1

/*!*/;

# at 626

#171025 15:38:04 server id 1  end_log_pos 657 CRC32 0xf128e69f Xid = 18

COMMIT/*!*/;

# at 657

#171025 15:43:37 server id 1  end_log_pos 736 CRC32 0xc0765ff4 Query thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1508917417/*!*/;

BEGIN

/*!*/;

# at 736

#171025 15:43:37 server id 1  end_log_pos 833 CRC32 0xfa21639d Query thread_id=4 exec_time=0 error_code=0

SET TIMESTAMP=1508917417/*!*/;

delete from binlog_test      在2017-10-25号15：43：37对数据库做了误操作，可以在这个时间点之前对数据进行恢复以及在这个时间点之后对数据进行恢复。即跳过这个时间点。

/*!*/;

# at 833

#171025 15:43:37 server id 1  end_log_pos 864 CRC32 0x7a903515 Xid = 27

COMMIT/*!*/;

DELIMITER ;

# End of log file

 

最后注意做select查询操作不会记录在binlog日志。

相关推荐