How to set up MySQL sync (replication)

MySQL provides synchronization of the database, which is very helpful for us to realize database redundancy, backup, recovery, load balancing and so on. This article describes common methods for synchronizing settings.

First, prepare the server

Because the Binlog format may be different between versions of MySQL (binary log), the best combination is that the MySQL version of Master and the slave version are the same or lower, and the master version must not be higher than the slave version.

In this article, we assume that the primary server (hereinafter referred to as Master) and the version from the server (hereinafter referred to as slave) 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,slave host name: rep2,2 mysql basedir directories are all/usr/local/mysql,datadir:/usr/local/mysql/data.

Second, set up the synchronization server

1. Set Sync Master

Each synchronization server must have a unique number set, or synchronization will not work properly. Next, start modifying my.cnf, adding the following lines:

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

Then add an account on master that is dedicated to synchronization, as follows:

Mysql>grant REPLICATION SLAVE On * * to [e-mail protected] identified by ' rep ';

If you want to have permission to execute on the slave " LOAD TABLE FROM MASTER" or"LOAD DATA FROM MASTER" 语句的话，必须授予全局的 FILE 和 SELECT 权限：

Mysql>grant file,select,replication SLAVE On * * to [e-mail protected] identified by ' rep ';

The third line indicates that the database MySQL update log is not logged, which avoids the synchronization of the permission settings on master to slave, and if there are no restrictions on this, you can not set this parameter.

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

READ LOCK;

不要退出这个终端，否则这个锁就不生效了；接着导出数据，可以直接打包压缩数据文件，也可以使用mysqldump工具来做，推荐前者的方法，这样更为快捷简便。

root$cd /usr/local/mysql
data.tar.gz./data (This may also be the "var" Directory of other actual data files, depending on the facts)

Then copy the data to the slave server, untie, set up the correct permissions and the owner, and then execute the "UNLOCK TABLES" statement to release the lock.

2. Set slave

To modify the MY.CNF, add the following lines:

Server-id = 2
Master-host = Rep1 #主服务器名
Master-user = Rep  #同步账户名, default is test
Master-password = Rep #同步帐户密码, default is empty
Master-port = 3306 #主服务器的 TCP/IP port number, default is 3306
Set-variable=replicate-ignore-db=mysql #略过同步的数据库名, if there are more than one, please set multiple
Set-variable=replicate-do-db=yejr #想要同步的数据库名, if there are more than one, please set multiple

Next, check on the slave to see if you can connect to master correctly and have the appropriate permissions.

Root$mysql-hrep1-urep-prep
Mysql>show GRANTS;
+------------------------------------------------------------------------------+
| Grants for [email protected]                                                                                                                  |
+------------------------------------------------------------------------------+

PASSWORD ' *9ff2c222f44c7bba5cc7e3be8573aa4e1776278c '      |
+------------------------------------------------------------------------------+

Now, you can start slave. After successful startup, log in 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)

As you can see, the values for the slave_io_running and slave_sql_running two columns are "Yes", which indicates that the Slave I/O and SQL threads are working properly.

At this point, the sync setting is successful.

How to set up MySQL sync (replication)