• notice
  • Congratulations on the launch of the Sought Tech site

Time zone problem with mysql copy

I received a request today to establish replication for MySQL internationally.This is actually nothing, no matter how bad the network environment is, MySQL can handle it.But there is a time zone problem error!

The error is as follows:

[Warning] Slave I/O: Get master clock failed with error: Lost connection to MySQL server during query, Error_code: 2
   [Warning] Slave I/O: Master command COM_REGISTER_SLAVE failed: failed registering on master, reconnecting to try again, log '1.000001' at position 48920, Error_code: 1597

The slave cannot obtain the clock of the master.The MySQL  time_zone variable is equal to=SYSTEM by default, which means the system time zone is used.How to modify the time zone: set global time_zone='+00:00' or set global time_zone='-11:00'; Simply modifying the time zone of the DB may cause confusion in the local program, so it is finally decided to modify the time zone of the system.This problem was handed over to our operation and maintenance (the time zone of crontab should also be fixed), and then restarted the DB to fix it

A brief summary of the considerations for creating replication is given here:

1, server_id must be unique

2, binlog and relay_log names should be customized.Cannot use hostname.log (modifying the system hostname may cause the database to fail to start)

3.The database time zone should be consistent

4.For users dedicated to replication, accounts should be established on both master and slave at the same time (to facilitate failover and prepare for future automation)

5.When performing database filtering on the slave side, it is recommended to use: replicate__wild_do_table (so that the replication of the statement mode does not need to consider the impact of using the use statement.Please refer to: http://weipengfei.blog.51cto.com/1511707/1070485

This article is from the blog "Technology Achieves Dreams", please keep this source http://weipengfei.blog.51cto.com/1511707/1170332


Technical otaku

Sought technology together

Related Topic


Leave a Reply