一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第10天，点击查看活动详情。
开启bin-log
mysql8默认开启bin-log，无需任何操作
[root@master mysql]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html
[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
bin-log目录
默认目录
/var/lib/mysql
[root@master mysql]# pwd
/var/lib/mysql
[root@master mysql]# ls
auto.cnf ca-key.pem '#ib_16384_0.dblwr' ib_logfile0 mysql performance_schema server-key.pem
binlog.000001 ca.pem '#ib_16384_1.dblwr' ib_logfile1 mysql.ibd private_key.pem sys
binlog.000002 client-cert.pem ib_buffer_pool ibtmp1 mysql.sock public_key.pem undo_001
binlog.index client-key.pem ibdata1 '#innodb_temp' mysql.sock.lock server-cert.pem undo_002
使用查询工具查看
# 日志配置
mysql> show global variables like 'log_bin%';
+---------------------------------+-----------------------------+
| Variable_name | Value |
+---------------------------------+-----------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/binlog |
| log_bin_index | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+-----------------------------+
5 rows in set (0.00 sec)
# 已有的日志
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 | 667 | No |
| binlog.000002 | 1044 | No |
+---------------+-----------+-----------+
2 rows in set (0.00 sec)
# 当前写入的日志
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 1044 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
# 默认查看第一个日志
mysql> show binlog events;
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000001 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.24, Binlog ver: 4 |
| binlog.000001 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000001 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 235 | Query | 1 | 477 | ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$INec\\\Z}^z W|[Z{f|_eBk6vCxtom5ckGttIg.NaHCdLcX78dZFgKcrBNcnM79' /* xid=5 */ |
| binlog.000001 | 477 | Anonymous_Gtid | 1 | 554 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000001 | 554 | Query | 1 | 644 | flush privileges |
| binlog.000001 | 644 | Stop | 1 | 667 | |
+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
# 指定查询日志文件
mysql> show binlog events in 'binlog.000002';
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| binlog.000002 | 4 | Format_desc | 1 | 125 | Server ver: 8.0.24, Binlog ver: 4 |
| binlog.000002 | 125 | Previous_gtids | 1 | 156 | |
| binlog.000002 | 156 | Anonymous_Gtid | 1 | 235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 235 | Query | 1 | 481 | CREATE USER 'feng'@'192.168.137.%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$8!
1\\Mx%rA*kDIh#Bv6ZmhQxtx2.rs4euc6g4s1GyRD/nejjswnh26V7eux1' /* xid=8 */ |
| binlog.000002 | 481 | Anonymous_Gtid | 1 | 558 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 558 | Query | 1 | 648 | FLUSH PRIVILEGES |
| binlog.000002 | 648 | Anonymous_Gtid | 1 | 725 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 725 | Query | 1 | 877 | GRANT ALL PRIVILEGES ON *.* TO 'feng'@'192.168.137.%' /* xid=42 */ |
| binlog.000002 | 877 | Anonymous_Gtid | 1 | 954 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000002 | 954 | Query | 1 | 1044 | FLUSH PRIVILEGES |
+---------------+-----+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)
使用mysqlbinlog查看
[root@master mysql]# mysqlbinlog binlog.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#220425 19:15:50 server id 1 end_log_pos 125 CRC32 0x908d89b4 Start: binlog v 4, server v 8.0.24 created 220425 19:15:50 at startup
ROLLBACK/*!*/;
BINLOG '
5oJmYg8BAAAAeQAAAH0AAAAAAAQAOC4wLjI0AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAADmgmZiEwANAAgAAAAABAAEAAAAYQAEGggAAAAICAgCAAAACgoKKioAEjQA
CigBtImNkA==
'/*!*/;
# at 125
#220425 19:15:50 server id 1 end_log_pos 156 CRC32 0x92fcb23c Previous-GTIDs
# [empty]
# at 156
#220425 19:18:18 server id 1 end_log_pos 235 CRC32 0xbd53e556 Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no original_committed_timestamp=1650885498256212 immediate_commit_timestamp=1650885498256212 transaction_length=321
# original_commit_timestamp=1650885498256212 (2022-04-25 19:18:18.256212 CST)
# immediate_commit_timestamp=1650885498256212 (2022-04-25 19:18:18.256212 CST)
/*!80001 SET @@session.original_commit_timestamp=1650885498256212*//*!*/;
/*!80014 SET @@session.original_server_version=80024*//*!*/;
/*!80014 SET @@session.immediate_server_version=80024*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 235
#220425 19:18:18 server id 1 end_log_pos 477 CRC32 0x9d97c32d Query thread_id=8 exec_time=0 error_code=0 Xid = 5
SET TIMESTAMP=1650885498.250571/*!*/;
SET @@session.pseudo_thread_id=8/*!*/;
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=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$INec\\\Z}^z W|[Z{f|_eBk6vCxtom5ckGttIg.NaHCdLcX78dZFgKcrBNcnM79'
/*!*/;
# at 477
#220425 21:23:54 server id 1 end_log_pos 554 CRC32 0x26638b9f Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=no original_committed_timestamp=1650893034285738 immediate_commit_timestamp=1650893034285738 transaction_length=167
# original_commit_timestamp=1650893034285738 (2022-04-25 21:23:54.285738 CST)
# immediate_commit_timestamp=1650893034285738 (2022-04-25 21:23:54.285738 CST)
/*!80001 SET @@session.original_commit_timestamp=1650893034285738*//*!*/;
/*!80014 SET @@session.original_server_version=80024*//*!*/;
/*!80014 SET @@session.immediate_server_version=80024*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 554
#220425 21:23:54 server id 1 end_log_pos 644 CRC32 0x69fc5e26 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1650893034/*!*/;
flush privileges
/*!*/;
# at 644
#220425 21:25:42 server id 1 end_log_pos 667 CRC32 0x2bb01192 Stop
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*/;
- 指定开始结束时间
mysqlbinlog --start-datetime="2022-04-26 15:24:00" --stop-datetime="2022-04-26 15:25:00" binlog.000002
世上最重要的事，不在于我们在何处，而在于我们朝着什么方向走。
