• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

MySQL时区设置导致主从复制报错

MySQL SEian.G 7个月前 (01-18) 218次浏览 已收录 0个评论
文章目录[隐藏]

问题背景

在云服务器上构建一个云数据库RDS数据库的从库,构建的方式也相对比较简单,和搭建主从的复制没啥区别,这里不做具体介绍;

构建完成后,业务上线之后,构建的从库出现主从复制报错的问题,具体的报错信息如下所示:

备注:由于云数据库RDS由于没有开启GTID,所以只能基于偏移量进行主从复制

(有些强一致性金融级云数据库RDS默认GTID是关闭的)

mysql>show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: xxxxx
                  Master_User: xxxxx
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 192682856
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 182919607
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: db_log
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%,sys.%
                   Last_Errno: 1298
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000001, end_log_pos 189340186. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 189340026
              Relay_Log_Space: 186263083
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1298
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000001, end_log_pos 189340186. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2230469996
                  Master_UUID: 101414c4-4c0a-11ec-bd6b-0c42a1f03afe
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 220115 13:48:22
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

查看performance_schema.replication_applier_status_by_worker表查看具体的报错信息如下:

mysql>select * from performance_schema.replication_applier_status_by_worker\G
*************************** 1. row ***************************
         CHANNEL_NAME: 
            WORKER_ID: 1
            THREAD_ID: NULL
        SERVICE_STATE: OFF
LAST_SEEN_TRANSACTION: ANONYMOUS
    LAST_ERROR_NUMBER: 1298
   LAST_ERROR_MESSAGE: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000001, end_log_pos 189340186; Error 'Unknown or incorrect time zone: 'Asia/Shanghai'' on query. Default database: 'inter_dg_log'. Query: 'BEGIN'
 LAST_ERROR_TIMESTAMP: 2022-01-15 13:48:22

从报错信息看,是由于binlog中存在设置时区time zone为Asia/Shanghai而导致的报错:

Error ‘Unknown or incorrect time zone: ‘Asia/Shanghai”

接下来我们解析一下Binlog日志,确认一下具体执行的SQL语句

# at 182919672
#220115 11:11:08 server id 2230469996  end_log_pos 189340186 CRC32 0x4c174b2d   Query   thread_id=7046077       exec_time=0     error_code=0
SET TIMESTAMP=1642216268/*!*/;
SET @@session.time_zone='Asia/Shanghai'/*!*/;
BEGIN
/*!*/;
# at 182919767
#220115 11:11:08 server id 2230469996  end_log_pos 189340301 CRC32 0xabbdbdb8   Rows_query

通过解析binlog文件,确实发现binlog中有设置时区的语句:SET @@session.time_zone=’Asia/Shanghai’

按道理的话,设置session级别应该是支持的,那为什么会出现报错呢?

接下来,我们查看一下,目前从库设置的时区

mysql>show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +08:00 |
+------------------+--------+
2 rows in set (0.01 sec)

从库实例默认时区格式是’+8:00’的格式

默认这个时区设置是没有的,mysql默认不支持’Asia/Shanghai’这种时区格式

mysql>set global time_zone='Asia/Shanghai';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'Asia/Shanghai'

那如何解决这个问题呢?

解决方案

需要从mysql官网下载一个时区文件,下载地址:https://dev.mysql.com/downloads/timezones.html

MySQL时区设置导致主从复制报错

下载完成后,解压后是一个SQL文件,将SQL文件导入到系统库mysql中,然后就支持设置支持’Asia/Shanghai’这种时区格式

mysql>set session time_zone='Asia/Shanghai';
Query OK, 0 rows affected (0.00 sec)

mysql>show variables like '%time_zone%';
+------------------+---------------+
| Variable_name    | Value         |
+------------------+---------------+
| system_time_zone | CST           |
| time_zone        | Asia/Shanghai |
+------------------+---------------+
2 rows in set (0.00 sec)

那么从库复制报错的问题也可以解决了,只需要stop slave;start slave;即可;

MySQL时区设置导致主从复制报错


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:MySQL时区设置导致主从复制报错
喜欢 (2)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址