MySQL学习笔记——binlog
1.docker部署MySQL
amd64的机器可以使用centos的MySQL5.7的镜像：https://hub.docker.com/r/centos/mysql-57-centos7/
arm64和amd64的机器也可以使用MySQL8.0的镜像：https://hub.docker.com/layers/library/mysql/8.0.29/images/sha256-44f98f4dd825a945d2a6a4b7b2f14127b5d07c5aaa07d9d232c2b58936fb76dc
启动MySQL5.7的容器
docker run --name mysqltest -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.44
启动MySQL8.0的容器
docker run --name mysqltest -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.29
如果想指定mysql配置和data挂载路径，可以先进入容器中将mysql的配置先拷贝出来
进入容器查看MySQL的配置路径
sh-4.4# mysql --help | grep my.cnf
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
参考：Docker安装MySQL 并挂载数据及配置文件,设置远程访问权限
将配置/etc/my.cnf拷贝到宿主机
docker cp mysqltest:/etc/my.cnf /Users/lintong/Downloads/mysql8.0/config/
指定mysql配置和data挂载路径启动docker mysql
mysql8.0
docker run --name mysqltest \ -v /Users/lintong/Downloads/mysql8.0/config/my.cnf:/etc/my.cnf \ -v /Users/lintong/Downloads/mysql8.0/data:/var/lib/mysql \ -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.29
mysql5.7
docker run --name mysqltest \ -v /Users/lintong/Downloads/mysql5.7/config/my.cnf:/etc/my.cnf \ -v /Users/lintong/Downloads/mysql5.7/data:/var/lib/mysql \ -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7.44
2.开启binlog
查看binlog是否开启，MySQL8.0默认是开启的
mysql> show variables like '%log_bin%'; +---------------------------------+--------------------------------+ | Variable_name | Value | +---------------------------------+--------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysql-bin | | log_bin_index | /var/lib/mysql/mysql-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+--------------------------------+
低版本默认是关闭的
如果是mysql5.7的话，需要在my.cnf配置中添加如下配置，参考：MySQL-开启binlog
[mysqld]
log-bin=mysql-bin
server-id=1
其他配置
#设置日志格式 binlog_format = mixed #设置binlog清理时间 expire_logs_days = 5 #binlog每个日志文件大小 max_binlog_size = 50m #binlog缓存大小 binlog_cache_size = 4m #最大binlog缓存大小 max_binlog_cache_size = 512m
可以在data目录下看到生成的binlog文件
使用命令查看binlog列表
mysql> show binary logs; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 177 | | mysql-bin.000002 | 2947794 | | mysql-bin.000003 | 154 | +------------------+-----------+ 3 rows in set (0.01 sec)
查看当前记录的binlog文件的文件名和偏移
mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000004 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
可以使用mysqlbinlog命令来查看binlog，如果想镜像中自带mysqlbinlog命令，可以使用debian的镜像，比如
docker run --name mysqltest \ -v /Users/lintong/Downloads/mysql5.7/config/my.cnf:/etc/my.cnf \ -v /Users/lintong/Downloads/mysql5.7/data:/var/lib/mysql \ -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7-debian
查看指定binlog文件的内容，可以看到这里先创建了一个test database，然后create了一张名为user的表
mysql> show binlog events in 'mysql-bin.000005';
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000005 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.42-log, Binlog ver: 4 |
| mysql-bin.000005 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000005 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 219 | Query | 1 | 313 | create database test |
| mysql-bin.000005 | 313 | Anonymous_Gtid | 1 | 378 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000005 | 378 | Query | 1 | 675 | use `test`; create table user
(
id bigint unsigned auto_increment comment ''
primary key,
username varchar(128) not null comment '',
email varchar(128) not null comment ''
)
comment '' charset = utf8mb4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
查看binlog的格式，binlog的格式有3种，分别为STATEMENT，ROW和MIXED
mysql> SHOW VARIABLES LIKE '%binlog_format%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | binlog_format | ROW | +---------------+-------+ 1 row in set (0.01 sec)
可以使用mysqlbinlog命令将binlog导成sql文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000005 > /tmp/binlog005.sql
也可以指定开始和结束时间来导出binlog，或者指定position
mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-datetime='2024-08-16 00:00:00' --stop-datetime='2024-08-16 23:00:00' /var/lib/mysql/mysql-bin.000005 > /tmp/binlog005.sql
查看mysql的时区，可以看出使用的是UTC时间
mysql> show variables like '%time_zone%'; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | UTC | | time_zone | SYSTEM | +------------------+--------+ 2 rows in set (0.02 sec) mysql> select now(); +---------------------+ | now() | +---------------------+ | 2024-08-17 15:29:48 | +---------------------+ 1 row in set (0.00 sec)
本文只发表于博客园和tonglin0325的博客，作者：tonglin0325，转载请注明原文链接：https://www.cnblogs.com/tonglin0325/p/5265788.html
