How to set MySQL synchronization (Replication) _ MySQL

How to set MySQL synchronization (Replication) Author: Ye Jinrong (Email: imysql # gmail.com), Source: http://imysql.cn, reprint please indicate the author and the source, and cannot be used for commercial purposes, offenders must investigate.

MySQL provides the database synchronization function, which is of great help to achieve database redundancy, backup, recovery, and load balancing. This document describes common synchronization settings.

I. prepare servers

Because the binlog formats of different MySQL versions (binary logs) may be different, the best combination is that the MySQL version of the Master is the same or lower than the Slave version, the Master version must not be higher than the Slave version.

In this article, we assume that the Master server and Slave server (Slave) versions are 5.0.15 and the operating system is Linux Ubuntu 5.0.x.

Assume that the host name of the synchronization Master is rep1, the Slave host name is rep2, the two MySQL basedir directories are/usr/local/mysql, and the datadir is both: /usr/local/mysql/data.

II. set synchronization server

1. set synchronization Master

Each synchronization server must have a unique id. Otherwise, the synchronization will fail. Next, modify my. cnf and add the following lines:

Server-id = 1
Log-bin
Set-variable = binlog-ignore-db = mysql

Then add an account on the Master for synchronization, as shown below:

Mysql> grant replication slave on *. * TO rep @ rep2 identified by 'Rep ';

If you want to have the permission to execute the "load table from master" or "load data from master" statement on Slave, you must grant the global FILE and SELECT permissions:

Mysql> grant file, SELECT, replication slave on *. * TO rep @ rep2 identified by 'Rep ';

The third line indicates that the update log of the database mysql is not recorded, which avoids permission settings on the Master node from being synchronized to the Slave. if there are no restrictions on this, you do not need to set this parameter.

Next, back up the data on the Master, first execute the following SQL statement:

Mysql> flush tables with read lock;

Do not exit the terminal, otherwise the lock will not take effect; then export data, you can directly package and compress the data file, or use the mysqldump tool to do it. we recommend the former method, which is faster and easier.

Root $ cd/usr/local/mysql
Root $ tar zcf data.tar.gz./data (here it may also be "var" and other directories that actually store data files, depending on the actual situation)

Then, copy the data to the Slave server, Unbind It, set the correct permissions and owner, and execute the "unlock tables" statement to release the lock.

2. set Slave

Modify my. cnf and add the following lines:

Server-id = 2
Master-host = rep1 # master server name
Master-user = rep # synchronization account name. The default value is test.
Master-password = rep # synchronization account password, which is blank by default
Master-port = 3306 # master server's TCP/IP port number. the default value is 3306.
Set-variable = replicate-ignore-db = mysql # skips the name of the database to be synchronized. if there are multiple database names, set them multiple times.
Set-variable = replicate-do-db = yejr # Name of the database to be synchronized. if there are multiple database names, set them multiple times.

Next, check whether the server load balancer instance can be correctly connected to the Master instance and have corresponding permissions.

Root $ mysql-hrep1-urep-prep
Mysql> show grants;
+ Certificate ------- +
| Grants for rep @ rep2 |
+ Certificate ------- +
| Grant select, FILE, replication slave on *. * TO 'rep '@ 'rep2 'identified BY password' * 9FF2C222F44C7BBA5CC7E3BE8573AA4E1776278C' |
+ Certificate ------- +

Now you can start Slave. Log on to Slave and check the synchronization status:

Mysql-hlocalhost-uroot
Mysql> show slave status/G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: rep1
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 98
Relay_Log_File: relay.000003
Relay_Log_Pos: 232
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 232
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: 0
1 row in set (0.00 sec)

We can see that the values of Slave_IO_Running and Slave_ SQL _Running are "Yes", which indicates that Slave's I/O and SQL threads are running normally.

So far, the synchronization settings are successful.

3. FAQ on synchronization settings

Please check the http://imysql.cn /? Q = node/61.