Setting MySQL synchronization correctly is easy!

This article mainly describes how to set up MySQL synchronization (replication), including preparing servers and setting up Synchronization servers. If you are interested in the actual operation steps, you can get a better understanding of the following articles.

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 = 1log-binset-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 the permission settings on the Master to synchronize MySQL 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/MySQLroot $ 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) and then copy the data to the Slave server to unbind it, set the correct permissions and owner, and then execute the "unlock tables" statement to release the lock.

2. Set Slave

Modify my. cnf and add the following lines:

Server-id = 2master-host = rep1 # master server name master-user = rep # synchronization account name, testmaster-password = rep # synchronization account password by default, the default value is null master-port = 3306 # The TCP/IP port Number of the master server. The default value is 3306set-variable = replicate-ignore-db = MySQL #. If there are multiple synchronous database names, set-variable = replicate-do-db = yejr # Name of the database to be synchronized. If there are multiple database names, set multiple times to check whether the Server Load balancer instance can be correctly connected to the Master instance and have corresponding permissions.

 
 

  
  
root$MySQL -hrep1 -urep -prepmysql>SHOW GRANTS;  
  
  
+------------------------------------------------------------------------------+| Grants for rep@rep2   
  
  
|+------------------------------------------------------------------------------+| GRANT SELECT,   
  
  
FILE, REPLICATION SLAVE ON *.* TO 'rep'@'rep2' IDENTIFIED BY PASSWORD '  
  
  
*9FF2C222F44C7BBA5CC7E3BE8573AA4E1776278C'   
  
  
|+------------------------------------------------------------------------------+  
 
 

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

 
 

  
  
mysql -hlocalhost -urootmysql>SHOW SLAVE STATUS\G  
  
  
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: 01 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.

Original article title: how to set MySQL synchronization (replication)

Connection: http://www.cnblogs.com/cyw080/archive/2009/12/21/1629190.html