MySQL manual version 5.0.20-mysql sync (v)

Mysql

6.9 Sync FAQ

Q: Master is still running, how can I configure slave without stopping it?

A: You need to design several option parameters. If you have a backup of MASTER and record the data snapshot binary log file name and the offset location (run show MASTER STATUS to view the results), perform the following steps:

Determines that the slave specifies a unique server number.

Execute the following statement on the slave to change some of the option values to the actual values:

Mysql> Change MASTER to

-> master_host= ' Master_host_name ',

-> master_user= ' Master_user_name ',

-> master_password= ' Master_pass ',

-> master_log_file= ' Recorded_log_file_name ',

-> master_log_pos=recorded_log_position;

Executes the START SLAVE statement on the SLAVE.

If you do not have backup master data in advance, you can quickly create a backup in the following ways. All of the following operations are on master.

Submit statement:

Mysql> FLUSH TABLES with READ LOCK;

Make sure that the lock persists, and execute the following command (or something similar):

Shell> Tar Zcf/tmp/backup.tar.gz/var/lib/mysql

Execute the following statement to record the results of the output, followed by:

Mysql> show MASTER STATUS;

Release Lock:

Mysql> UNLOCK TABLES;

Another way to do this is to create a SQL dump file for master. Simply execute the mysqldump--master-data command on master, and then load the derived SQL dump file into slave. However, doing so will make the binary data snapshot a little slower.

Whichever of these methods is used, the data snapshot of master is created and then the binary log file name and the offset location are recorded. A binary data snapshot of the same backup can be used on several other slave. Once you get a snapshot of master, as long as Master's binary log is intact, you can then start setting up slave. The two limitations that determine whether you need to wait for a longer time are to save the binary log on disk space on master and slave to crawl update events from master.

You can also use the LOAD DATA from MASTER. This statement makes it easy to get a snapshot of the data on the slave and immediately adjusts the binary log file name and offset position. In the future, we recommend using the LOAD DATA from MASTER to set up slave. Warning, it can be used only for MyISAM tables, and may hold a read lock for a long time. Since it has not yet reached the desired efficiency, it is best to make a binary data snapshot directly after executing FLUSH tables with READ LOCK if the datasheet is large.

Q: Does slave always need to connect to master?

A: no, not necessary. Slave can be shut down or disconnected from master for several hours or even days, and then reconnect to get the update action log. For example, you can set up a mater/slave relationship on a dial-up link, and dialing may be a sporadic, occasional connection. The implication is that, at any given time, unless special metrics are used, slave is not guaranteed to always be synchronized with master. In the future, there is an option to block master unless at least one slave is in sync.

Q: How do you know how late it is than master? In other words, how do you know when the slave last synchronized?

A: If SLAVE is 4.1.1 or higher, just look at the Seconds_behind_master field in show SLAVE STATUS results. For older versions, you can use the following methods. If the show Processlist statement on slave shows that the SQL thread (the slave thread for MySQL 3.23) is running, this means that the thread reads at least one update action event from master. For details, please see "6.3 Replication Implementation Details".

When the SQL thread executes an Update action event read on master, it changes its time to the time of the event (which is why TIMESTAMP also synchronizes). In

Show processlist the Time field in the result, the slave SQL thread displays the number of seconds that the timestamp of the last synchronization and the actual time of the slave native are in seconds. You can determine the time of the last synchronization based on this value. Note that if slave has been disconnected from master for several hours and then reconnected, you can see the value of the SQL thread in the slave show processlist result is similar to 3600. This is because slave is executing the statement one hours ago.