1 MySQL Cluster Master-Slave Replication Setup Guide

 

Purpose of master-slave replication: MySQL master-slave servers can synchronize databases, tables, and table content. You can also specify to synchronize only certain databases or tables, or exclude specific databases and tables from synchronization.

The synchronization principle: Master-slave database synchronization is primarily achieved through binary logs.

Download the latest version from the MySQL official website: dev.mysql.com

Environment used in this article:

Master MySQL server: 10.1.68.110

Installation package: mysql-cluster-gpl-7.0.35-linux-x86_64-glibc23.tar.gz

Slave MySQL server: 10.1.68.101

Installation package: mysql-cluster-gpl-7.0.35-linux-x86_64-glibc23.tar.gz

Note: It is best for the master and slave servers to use the same version. If not possible, it is recommended that the slave server version be higher than the master server version.

Additionally, mysql-5.6.10 version master-slave configuration differs from the mysql-cluster version, mainly in the my.cnf configuration file. The start slave command is also different, so pay attention.

This article documents the configuration for both versions.

1.1 Configure Master MySQL Server

 

1.       Install the master MySQL (refer to the INSTALL-BINARY file in the tar package, it’s very simple)

shell> groupadd mysql shell> useradd -r -g mysql mysql shell> tar zxf  mysql-cluster-gpl-7.2.9-linux2.6-x86_64.tar.gz shell> mv mysql-cluster-gpl-7.2.9-linux2.6-x86_64 /opt/mysql-master shell> cd /opt/mysql-master shell> chown -R mysql.mysql * shell> scripts/mysql_install_db –user=mysql shell> chown -R root . shell> chown -R mysql data # Next command is optional shell> cp support-files/my-medium.cnf /etc/my.cnf shell> bin/mysqld_safe & If startup fails, try executing the scripts/mysql_install_db –user=mysql command again and then start. # Next command is optional, the following script file will only be generated after mysqld has started successfully shell> cp support-files/mysql.server /etc/init.d/mysql.server

2.       Configure the master’s my.cnf file (basically no configuration is needed as defaults are already set)

# vi /etc/my.cnf

Key configurations log-bin=mysql-bin server-id   = 1 binlog-do-db=icinga binlog-do-db=DB2     If backing up multiple databases, repeat this option binlog-do-db=DB3  // Database to be synchronized. If this line is absent, it means synchronize all databases binlog-ignore-db=mysql  // Ignored databases

Save and exit

3.       The only thing left is to create an account allowing slave server access: backup

mysql> grant replication slave on *.* to 'backup'@'10.1.68.102' identified by'1234qwer';

Query OK, 0 rows affected (0.00 sec)

If there are multiple slave servers, modify the IP and execute multiple times.

4.       Restart the master server’s MySQL service.

1.2 Configure Slave MySQL Server

 

1.         Install the slave MySQL (refer to the INSTALL-BINARY file in the tar package, it’s very simple)

(Omitted)

2.         Configure the slave’s /etc/my.cnf file

Key configurations log-bin=mysql-bin server-id       = 2 master-host     =   10.1.68.110 master-user     =   backup master-password =   1234qwer master-port     =  3306 replicate-do-db=icinga replicate-do-db=DB2 binlog-do-db=DB3  // Database to be synchronized. If this line is absent, it means synchronize all databases binlog-ignore-db=mysql  // Ignored databases

Save and exit

3.         Restart the slave’s MySQL service. The basic slave configuration is complete.

4.         If the slave server’s offset differs from the master, manually modify the offset

(1) First, check the master’s status information

mysql> show master status;

(2) Log into the slave and modify the slave’s Position according to the master’s Position

mysql> slave stop;

mysql> change master to master_host='10.1.68.110',master_user='backup',master_password='1234qwer',master_log_file='mysql-bin.000011',master_log_pos=62291;

mysql> slave start;

 

1.3  Verify Synchronization

 

1.         Create a table in the icinga database and insert data

mysql> create table abc(id int,name char(10)); Query OK, 0 rows affected (0.40 sec) mysql> insert into abc values(1,'shenxiaoran'); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from abc;

2.         Check the slave MySQL server to see if the table and content have been synchronized

mysql> show tables;

Then check the table content; the data has also been synchronized, as shown below

At this point, the MySQL database master-slave synchronization is complete.

 

1.4 Common Master-Slave Synchronization Commands

 

1.4.1 Master Server Common Commands

1.       Check master status and Position information, used to keep the slave consistent with the master.

mysql> show master status;

1.4.2 Slave Server Common Commands

mysql> show slave status;

mysql> slave stop;

mysql> slave start;

mysql> show processlist;

2 MySQL-5.6.10 Master-Slave Replication Setup

 

Deployment environment:

OS: CentOS release 6.3 (Final)

Master MySQL-master server: 192.168.123.75

Installation package: mysql-5.6.10.tar.gz source package

Slave MySQL-slave server: 192.168.123.105

Installation package: mysql-5.6.10.tar.gz source package

2.1 Install Master MySQL-Master and Slave MySQL-Slave Servers Separately

 

mysql5.6.10.tar.gz is the latest source package version on the MySQL official website as of April 10, 2013. The installation method for this version differs from traditional mysql installation methods; you must manually install cmake first.

Download cmake from the official website: http://cmake.org/cmake/resources/software.html

./bootstrap

make

make install

Next, install mysql. I’ve written an automated installation shell script here.

2.2 Configure Master MySQL-Master Server

 

The my.cnf file for Mysql-5.6.10 version has no parameters by default; you need to configure them yourself.

# vi /etc/my.cnf

Key configurations log-bin = mysql-bin # These are commonly set, remove the # and set as required. basedir = /usr/local/mysql datadir = /usr/local/mysql/data port = 3306 server_id = 106 // Note: the new version uses underscores socket = /tmp/mysql.sock bind-address = 0.0.0.0 // Bind and listen on 0.0.0.0:3306, default is :::1 binlog-do-db = cacti  // Repeat for multiple databases. If absent, all databases are backed up. binlog-do-db = db2 binlog-ignore-db = mysql  // Ignored databases, will not be replicated

Save and exit

5.       The only thing left is to create an account allowing slave server access: mysync

mysql> grant replication slave on *.* to 'mysync'@'192.168.123.105' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

If there are multiple slave servers, modify the IP and execute multiple times.

6.       Restart the master server’s MySQL service.

Test connection from slave to master MySQL

# bin/mysql -h 192.168.123.75 -u mysync -p123456

mysql>

Connection test successful.

2.3 Configure Slave MySQL-Slave Server

 

# vi /etc/my.cnf

log_bin = mysql-bin server_id = 107 Note: the following parameters can no longer be used, otherwise mysql will fail to start master-host = 10.1.68.110 master-user = backup master-password = 1234qwer master-port = 3306

1.       Log into the master server’s mysql, query master status

mysql> show master status;

+——————+———-+————–+——————+——————-+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+——————+———-+————–+——————+——————-+

| mysql-bin.000002 |      120 | cacti        | mysql            |                   |

+——————+———-+————–+——————+——————-+

1 row in set (0.00 sec)

Note: After executing this step, do not operate the master server MYSQL to prevent the master server status value from changing.

2.       Log into the slave mysql server, configure the binary log file and Position (which is the size of the master’s binary log file) to be consistent with the master.

mysql> change master to master_host='192.168.123.75',master_user='mysync',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos=120;

Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

3.       Check the slave mysql server status

mysql> show slave status;

The key is to check Slave_IO_Running | Slave_SQL_Running

If the result shows Yes              | Yes

It indicates that the mysql master-slave configuration has been completed successfully.

 

2.4 Test Master-Slave Synchronization

 

1．  Import the backed-up database into the master mysql server, and check if the slave mysql server synchronizes.

# bin/mysql < /root/cacti_sql_bak

2．  Check if the database on the slave mysql server has updated

mysql> show databases;

+——————–+

| Database           |

+——————–+

| information_schema |

| cacti              |

| mysql              |

| performance_schema |

| test               |

+——————–+

5 rows in set (0.01 sec)

From the above, we can see that even before the master mysql server’s database has been fully imported, the slave mysql server has already begun synchronization.

 

3  FAQ

 

3.1 If the result of the show slave status command looks normal, but you still can’t see the synchronized database, what is the reason?

 

mysql> show slave status; Partial results after execution are as follows:

Slave_IO_State: Waiting for master to send event

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Yet the database still fails to synchronize. What is the reason?

Solution:

1） Try restarting the master database;

2） Before restarting the slave database, you must first delete the master.info file in the data directory, because master.info records the information about the last connection to the master library. If not deleted, even if changes are made in my.cnf and the Position (offset) is manually modified, synchronization will still fail because it reads the erroneous offset information from the master.info file.

# rm -f data/master.info

# support-files/mysql.server start

Starting MySQL.           [  OK  ]

Checking the slave again, the synchronization finally succeeded.

mysql> show processlist;

id|User          |Host|db      |Command  |Time      |state

1| system user |       | NULL   | Connect |1196 | Waiting for master to send event

2 | system user |    | NULL   | Connect |-55692 | Slave has read all relay log; waiting for the slave I/O thread to

3 | root   |localhost|icinga| Query|  0 |NULL | show processlist

3.2 How to Fix the Following Errors When Running show slave status

 

Running the mysql> show slave status; command, you see the following output

Slave_IO_Running:Connecting

Slave_SQL_Running:Yes 

Seeing this issue indicates that the slave server cannot properly connect to the master server. The cause is generally related to one of the following scenarios:

l  Iptables firewall is blocking port 3306

l  The GRANT authorization command on the master MySQL server is incorrect

l  The master MySQL server is not listening on 0.0.0.0:3306, preventing other servers from connecting to the master MySQL

Solution:

First, disable iptables;

Then, check the GRANT authorization command on the master MySQL server. It is correct, so this possibility is ruled out.

Next, check the listening port of the master MySQL. The default is :::3306, which might be causing the issue.

Modify the master server’s my.cnf file and add the following statement

bind-address = 0.0.0.0

Restart the mysql.server service

Run netstat –an|more again to check port 3306, which now shows 0.0.0.0:3306, indicating a successful modification.

Log in to the slave MySQL server and connect remotely

# bin/mysql -h 192.168.123.75 -umysync -p123456

Your MySQL connection id is 8

Server version: 5.6.10-log Source distribution

mysql>

Connection successful.

Finally, re-execute the master-slave operations:

Master MySQL server: mysql> show master status;

Slave MySQL server: mysql> stop slave;

Slave MySQL server: mysql> change master to……;

Slave MySQL server: mysql> start slave;

Slave MySQL server: mysql> show slave status;

Slave_IO_Running:Yes

Slave_SQL_Running:Yes 

Testing the master-slave synchronization again, it succeeds.