MySQL Experience (eight) the configuration and recovery of the online master-slave replication cluster and the master configuration

Perform master-slave configuration online

Steps

1. Lock table

FLUSH TABLES with READ LOCK;

2. Copy data file, or export

3. Record the log location of master

Master_log_file

Master_log_pos

4. Unlock the main library

UNLOCK TABLES;

5. Start Master and slave configuration

6. After configuring the master and the boot, we mount from the library to the cluster

Other

From the Library Data folder, there is a master.info file that is unique from the machine.

Reset from library: used to let a subordinate server forget its replication location in the primary server's binary log, it deletes master.info and relay-log.info files, as well as all relay logs, and launches a new relay log, which you can perform when you don't need to master.

Reset slave;

Reconfigure the Master library (delete binlog)

Reset Master;

viewing replication logs

Show Binlog events in ' mysql-bin.000004 ' \g;

Primary master Configuration

Attention to Configuration

[Mysqld]

# Step value

auto_increment_increment=2

# Start Offset

Auto_increment_offset=1

# causes replicated events from the server to be logged in its own binary log

Log_slave_updates=1

Automatically delete Binlog

Set up automatic clean up MySQL binlog log, configure MY.CNF:

Expire_logs_days = 10

Master-slave recovery on line

1 first to lock the table of the main library

FLUSH TABLES with READ LOCK;

2 then copy the main library's physical files to the library, keeping the data files consistent. (It is recommended to use mysqldump to ensure data consistency)

3) record the main library of the

Master_log_file

Master_log_pos

4 Unlock the main library of the table, although the configuration from the library did not complete, but write data is not related, we have recorded the Bin-log offset position.

UNLOCK TABLES;

5 from the Library new configuration master

Additional Reading

From the Library Data folder, there is a master.info file that is unique from the machine.

Reset from library: used to let a subordinate server forget its replication location in the primary server's binary log, it deletes master.info and relay-log.info files, as well as all relay logs, and launches a new relay log, which you can perform when you don't need to master.

RESET SLAVE;

On-Line primary primary replication recovery

Ditto, lock table, copy same physical file (recommended use mysqldump, can guarantee data consistency), record 2

Master_log_file

Master_log_pos

and unlock it.

Configuring Master-Slave synchronization

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/