mysql 中的 binary 对应 java mysql _binary

转载

mob6454cc634aa4 2023-08-12 20:28:50

文章标签 数据库 mysql 运维服务器 centos 文章分类 MySQL 数据库

导游

概述
优缺
开启binlog日志
查看&删除二进制日志
查看binlog日志文件内容

mysqlbinlog命令

二进制日志恢复数据

概述

MySQL二进制日志（Binary Log，简称binlog）记录着数据库中所有更新数据的SQL语句，也就是数据库的增，删，改操作语句，将其写入到二进制日志中，以时间的形式保存。

优缺

优点：

恢复数据库
可用于MySQL数据库的主从架构

缺点：

消耗一部分性能
花费时间维护二进制日志

开启binlog日志

注：案例中MySQL版本为8.0.13

mysql --version
mysql  Ver 8.0.13 for Linux on x86_64 (Source distribution)

查看二进制日志是否开启

mysql> show variables like 'log_bin%';
+---------------------------------+-------------------------------+
| Variable_name                   | Value                         |
+---------------------------------+-------------------------------+
| log_bin                         | ON                            |
| log_bin_basename                | /data/mysql/data/binlog       |
| log_bin_index                   | /data/mysql/data/binlog.index |
| log_bin_trust_function_creators | OFF                           |
| log_bin_use_v1_row_events       | OFF                           |
+---------------------------------+-------------------------------+

log_bin为OFF表示binlog日志没有开启。

开启二进制日志
MySQL二进制日志默认是关闭的，可以通过编辑/etc/my.cnf配置文件

server-id=1
log-bin=/data/mysql/log/mysql-bin
expire_logs_days = 30
max_binlog_size = 200M

配置项说明：
sever-id：设置MySQL数据库id，MySQL5.7及以上版本需加
log-bin：binlog日志存放路径
expire_logs_days：binlog日志的保存时间，超过将会自动删除
max_binlog_size：binlog日志文件大小，根据环境而定

添加完成后，重启MySQL，首先确认有没有创建二进制日志文件

查看binlog文件

ls /data/mysql/log/
mysql-bin.000001  mysql-bin.index  mysqld.log

其中mysql-bin.000001 就是binlog文件，而mysql-bin.index的功能是记录所有Binary Log的绝对路径，保证MySQL各种线程能够顺利的根据他找到所有需要的Binary Log文件。

cat /data/mysql/log/mysql-bin.index 
/data/mysql/log/mysql-bin.000001

查看binlog是否开启

mysql> show variables like 'log_bin%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| log_bin                         | ON                              |
| log_bin_basename                | /data/mysql/log/mysql-bin       |
| log_bin_index                   | /data/mysql/log/mysql-bin.index |
| log_bin_trust_function_creators | OFF                             |
| log_bin_use_v1_row_events       | OFF                             |
+---------------------------------+---------------------------------+

其中log_bin为NO，表示二进制日志已经开启了。

除以上配置项外，binlog还有其他一些附加的选项参数：

max_binlog_size：设置binlog的最大存储上限，一般设置为512M或者1G，一般不能超过1G。当日志达到该上限时，MySQL 会重新创建一个日志开始继续记录。不过偶尔也有超出该设置的binlog 产生，一般都是因为在即将达到上限时，产生了一个较大的事务，为了保证事务安全，MySQL 不会将同一个事务分开记录到两个binlog 中。

注：已在my.cnf文件中设置为200MB

mysql> show variables like '%max_binlog_size%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| max_binlog_size | 209715200 |
+-----------------+-----------+

binlog_cache_size：一个事务在没有提交之前，产生的日志记录到cache中，等到事务提交的时候，则把日志持久化到磁盘。一般来说，如果我们的数据库中没有什么大事务，写入也不是特点频繁，2MB~4MB是个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size，默认值32768。

mysql> show variables like 'binlog_cache_size%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| binlog_cache_size | 32768 |
+-------------------+-------+

binlog_format：二进制日志类型，共三种，分别是：STATEMENT，ROW，MIXED。

mysql> show variables like '%binlog_f%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+

查看&删除二进制日志

查看binlog日志

mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       155 |
+------------------+-----------+

查看binlog日志内容

mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         124 | Server ver: 8.0.13, Binlog ver: 4 |
| mysql-bin.000001 | 124 | Previous_gtids |         1 |         155 |                                   |
+------------------+-----+----------------+-----------+-------------+-----------------------------------+

查看master日志状态

注：MySQL开启主从复制时使用

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 155
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set:

刷新binlog日志

注：刷新完成后，会自动创建一个新的二进制日志，是在当前二进制日志名称后基础上+1，例如，mysql-bin.000001刷新完成后名称为mysql-bin.000002，之后的数据更新的SQL语句将存放在mysql-bin.000002中。

mysql> flush logs;
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       202 |
| mysql-bin.000002 |       155 |
+------------------+-----------+

清空binlog日志

注：其实过程是将之前的二进制日志删除，然后重新创建一个二进制日志，用于存放数据更新的SQL语句。

mysql> reset master;
mysql> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       155 |
+------------------+-----------+

查看binlog日志文件内容

二进制日志为二进制格式存储，使用此格式可以存储更多的信息，并且可以使用写入二进制日志的效率更高。但缺点是无法直接linux命令（如cat，less等）查看二进制日志。

例如：

cat /data/mysql/log/mysql-bin.000001                                
`þbinVԡ^x|8.0.13Vԡ^               
**4

mysqlbinlog命令

想要查看二进制日志文件中具体的内容，还得借助MySQL组件中的mysqlbinlog这个工具。

语法格式：mysqlbinlog 参数文件路径

具体参数可以通过- -help来自行查看，若是查看文件内容，只需要加上-v参数即可。

进入mysql数据库，执行一些SQL语句，并查看二进制日志内容。

create database test;
use test;
create table binlog(id int,name varchar(11));
insert into binlog values(1,'测试');

查看二进制日志内容

mysqlbinlog -v /data/mysql/log/mysql-bin.000001 
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200611 15:02:53 server id 1  end_log_pos 124 CRC32 0xb8a56743 	Start: binlog v 4, server v 8.0.13 created 200611 15:02:53 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
HdfhXg8BAAAAeAAAAHwAAAABAAQAOC4wLjEzAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAd1+FeEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgFDZ6W4
'/*!*/;
# at 124
#200611 15:02:53 server id 1  end_log_pos 155 CRC32 0xa2e2079b 	Previous-GTIDs
# [empty]
# at 155
#200611 15:03:11 server id 1  end_log_pos 228 CRC32 0x553603b4 	Anonymous_GTID	last_committed=0	sequence_number=1	rbr_only=no	original_committed_timestamp=1591858992368103	immediate_commit_timestamp=1591858992368103	transaction_length=181
# original_commit_timestamp=1591858992368103 (2020-06-11 15:03:12.368103 CST)
# immediate_commit_timestamp=1591858992368103 (2020-06-11 15:03:12.368103 CST)
/*!80001 SET @@session.original_commit_timestamp=1591858992368103*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 228
#200611 15:03:11 server id 1  end_log_pos 336 CRC32 0xa17df524 	Query	thread_id=10	exec_time=1	error_code=0	Xid = 45
SET TIMESTAMP=1591858991/*!*/;
SET @@session.pseudo_thread_id=10/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create database test
/*!*/;
# at 336
#200611 15:03:38 server id 1  end_log_pos 409 CRC32 0xad6609b8 	Anonymous_GTID	last_committed=1	sequence_number=2	rbr_only=no	original_committed_timestamp=1591859018545646	immediate_commit_timestamp=1591859018545646	transaction_length=205
# original_commit_timestamp=1591859018545646 (2020-06-11 15:03:38.545646 CST)
# immediate_commit_timestamp=1591859018545646 (2020-06-11 15:03:38.545646 CST)
/*!80001 SET @@session.original_commit_timestamp=1591859018545646*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 409
#200611 15:03:38 server id 1  end_log_pos 541 CRC32 0x0bbfaccc 	Query	thread_id=10	exec_time=0	error_code=0	Xid = 50
use `test`/*!*/;
SET TIMESTAMP=1591859018/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table binlog(id int,name varchar(11))
/*!*/;
# at 541
#200611 15:03:54 server id 1  end_log_pos 616 CRC32 0xd6ca34b1 	Anonymous_GTID	last_committed=2	sequence_number=3	rbr_only=yes	original_committed_timestamp=1591859034487911	immediate_commit_timestamp=1591859034487911	transaction_length=288
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1591859034487911 (2020-06-11 15:03:54.487911 CST)
# immediate_commit_timestamp=1591859034487911 (2020-06-11 15:03:54.487911 CST)
/*!80001 SET @@session.original_commit_timestamp=1591859034487911*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 616
#200611 15:03:54 server id 1  end_log_pos 693 CRC32 0xa6d0298b 	Query	thread_id=10	exec_time=0	error_code=0
SET TIMESTAMP=1591859034/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
BEGIN
/*!*/;
# at 693
#200611 15:03:54 server id 1  end_log_pos 751 CRC32 0x388491fc 	Table_map: `test`.`binlog` mapped to number 88
# at 751
#200611 15:03:54 server id 1  end_log_pos 798 CRC32 0xf79be854 	Write_rows: table id 88 flags: STMT_END_F

BINLOG '
WtfhXhMBAAAAOgAAAO8CAAAAAFgAAAAAAAEABHRlc3QABmJpbmxvZwACAw8CIQADAQEAAgEh/JGE
OA==
WtfhXh4BAAAALwAAAB4DAAAAAFgAAAAAAAEAAgAC/wABAAAABua1i+ivlVTom/c=
'/*!*/;
### INSERT INTO `test`.`binlog`
### SET
###   @1=1
###   @2='测试'
# at 798
#200611 15:03:54 server id 1  end_log_pos 829 CRC32 0x41beb43c 	Xid = 51
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

在二进制日志记录中，有两种查看的方式：

1）第一种是基于时间查看，每条保存的SQL语句都有唯一的时间保存。
2）第二种是基于所在位置，每条保存的SQL语句再前面都有一个at并用唯一id标记。

例如以上例子中，at228 下就是创建test数据库的SQL语句，插入的第一条记录则在at751后。

二进制日志恢复数据

查看的方式有两种，恢复的方式也有两种，基于时间点恢复，基于位置恢复。个人比较喜欢基于位置恢复。

首先，删除创建的test数据库，模拟数据丢失。

mysql> drop database test;

刷新一个新的二进制日志

mysql> flush logs;

之前查看二进制日志可以知道，at228-at751是全部的测试语句，之后就是其他SQL语句及误操作的删除语句。

基于位置恢复：
–start-position 从二进制日志中读取指定position 事件位置作为开始。

–stop-position 从二进制日志中读取指定position 事件位置作为结束。

注：将其理解为从at哪里开始，到at哪里结束即可

拿此案例为例，恢复语句为

mysqlbinlog --start-position="228" --stop-position="798" /data/mysql/log/mysql-bin.000001 | mysql -uroot

可以发现–stop-position=“798” ，而不是751，这是因为结束机制的问题，不会执行指定结束位置的SQL语句，所以结束位置要稍大于原位置。

再次进入数据库，查看是否恢复

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。