方法一SQL 命令检查最常用连接到MySQL后执行-- 查看 binlog 状态SHOWVARIABLESLIKElog_bin;输出示例---------------------- | Variable_name | Value | ---------------------- | log_bin | ON | ----------------------Value ON✅ 表示已开启Value OFF❌ 表示未开启方法二查看详细的 Binlog 配置-- 查看所有 binlog 相关配置SHOWVARIABLESLIKE%bin%;关键参数说明-- 查看 binlog 格式必须是 ROWSHOWVARIABLESLIKEbinlog_format;-- 应该显示ROW-- 查看 binlog 文件位置SHOWVARIABLESLIKElog_bin_basename;-- 显示/var/lib/mysql/mysql-bin-- 查看当前正在写入的 binlog 文件SHOWMASTERSTATUS;-- 显示当前文件和位置方法三检查 Binlog 文件是否存在系统级别在Linux系统中直接查看binlog文件# 查看 MySQL 数据目录mysql-eSHOW VARIABLES LIKE datadir;# 进入数据目录查看 binlog 文件ls-lh/var/lib/mysql/|grepmysql-bin如果看到mysql-bin.000001这样的文件说明binlog已开启。方法四使用 Python 脚本检查importpymysqldefcheck_binlog_status(host,user,password,port3306):connpymysql.connect(hosthost,useruser,passwordpassword,portport)cursorconn.cursor()# 检查 log_bincursor.execute(SHOW VARIABLES LIKE log_bin)log_bincursor.fetchone()# 检查 binlog_formatcursor.execute(SHOW VARIABLES LIKE binlog_format)binlog_formatcursor.fetchone()# 检查 binlog 文件cursor.execute(SHOW MASTER STATUS)master_statuscursor.fetchone()conn.close()return{log_bin:log_bin[1]iflog_binelseOFF,binlog_format:binlog_format[1]ifbinlog_formatelseUnknown,has_binlog_files:master_statusisnotNone}# 使用示例resultcheck_binlog_status(127.0.0.1,root,your_password)print(flog_bin:{result[log_bin]})print(fbinlog_format:{result[binlog_format]})print(fHas binlog files:{result[has_binlog_files]})方法五组合检查脚本推荐创建一个完整的检查脚本#!/usr/bin/env python3importpymysqlimportsysdefcheck_mysql_binlog(host,user,password,port3306):try:connpymysql.connect(hosthost,useruser,passwordpassword,portport)cursorconn.cursor()# 1. 检查 log_bincursor.execute(SHOW VARIABLES LIKE log_bin)log_bincursor.fetchone()# 2. 检查 binlog_formatcursor.execute(SHOW VARIABLES LIKE binlog_format)binlog_formatcursor.fetchone()# 3. 检查 binlog_row_imagecursor.execute(SHOW VARIABLES LIKE binlog_row_image)row_imagecursor.fetchone()# 4. 检查是否有 binlog 文件cursor.execute(SHOW MASTER STATUS)master_statuscursor.fetchone()conn.close()print(*50)print(MySQL Binlog 状态检查)print(*50)print(flog_bin :{log_bin[1]iflog_binelseOFF})print(fbinlog_format :{binlog_format[1]ifbinlog_formatelseUnknown})print(fbinlog_row_image :{row_image[1]ifrow_imageelseUnknown})ifmaster_status:print(f当前 binlog 文件 :{master_status[0]})print(f当前位置 :{master_status[1]})else:print(当前 binlog 文件 : 无)print(*50)# 判断是否满足 CDC 要求is_ready(log_binandlog_bin[1]ONandbinlog_formatandbinlog_format[1].upper()ROWandmaster_statusisnotNone)ifis_ready:print(✅ MySQL 已正确配置可以使用 CDC)else:print(❌ MySQL 未正确配置需要修改配置)ifnotlog_binorlog_bin[1]!ON:print( - 需要开启 log_bin)ifnotbinlog_formatorbinlog_format[1].upper()!ROW:print( - 需要设置 binlog_formatROW)ifnotmaster_status:print( - 未找到 binlog 文件)returnis_readyexceptExceptionase:print(f❌ 连接失败:{e})returnFalseif__name____main__:# 修改为你的 MySQL 配置check_mysql_binlog(host127.0.0.1,userroot,passwordyour_password,port3306)如果未开启如何开启 Binlog1. 修改 MySQL 配置文件找到 MySQL 配置文件通常在/etc/mysql/my.cnf或/etc/my.cnf[mysqld] # 开启 binlog log-bin mysql-bin server-id 1 # 必须设置唯一值 binlog-format ROW # 必须设置为 ROW binlog-row-image FULL # 推荐设置为 FULL expire-logs-days 7 # 日志保留7天可选8.4.11 以后无效 binlog_expire_logs_seconds604800 # 新版本用这个存储保留时间 max-binlog-size 100M # 单个文件大小可选 # 如果你只需要监听特定数据库可以添加 # binlog-do-db your_database # 只记录指定数据库2. 重启 MySQL# Ubuntu/Debiansudosystemctl restart mysql# CentOS/RHELsudosystemctl restart mysqld# 或使用 servicesudoservicemysql restart3. 验证配置SHOWVARIABLESLIKElog_bin;SHOWVARIABLESLIKEbinlog_format;SHOWMASTERSTATUS;SHOW VARIABLES LIKE ‘%bin%’;bind_address *binlog_cache_size 32768binlog_checksum CRC32binlog_direct_non_transactional_updates OFFbinlog_encryption OFFbinlog_error_action ABORT_SERVERbinlog_expire_logs_seconds 2592000binlog_format ROWbinlog_group_commit_sync_delay 0binlog_group_commit_sync_no_delay_count 0binlog_gtid_simple_recovery ONbinlog_max_flush_queue_time 0binlog_order_commits ONbinlog_rotate_encryption_master_key_at_startup OFFbinlog_row_event_max_size 8192binlog_row_image FULLbinlog_row_metadata MINIMALbinlog_row_value_optionsbinlog_rows_query_log_events OFFbinlog_stmt_cache_size 32768binlog_transaction_dependency_history_size 25000binlog_transaction_dependency_tracking COMMIT_ORDERinnodb_api_enable_binlog OFFlog_bin ONlog_bin_basename /var/lib/mysql/binloglog_bin_index /var/lib/mysql/binlog.indexlog_bin_trust_function_creators OFFlog_bin_use_v1_row_events OFFlog_statements_unsafe_for_binlog ONmax_binlog_cache_size 18446744073709547520max_binlog_size 1073741824max_binlog_stmt_cache_size 18446744073709547520mysqlx_bind_address *sql_log_bin ONsync_binlog 1
