MySQL5.6 master-slave replication Configuration

MySQL5.6 master-slave replication Configuration

Environment Description:

Two MySQL5.6.21 Servers installed in CentOS6.5: master and slave

Master Server:

IP: 192.168.80.51

Port: 3316

Slave Server:

IP: 192.168.80.52

Port: 3316

Configuration steps:

1. Modify the my. cnf configuration file of the master, enable logging, and set the server-id.

2. Modify the my. cnf configuration file of slave, enable reading binary logs, and set server-id.

3. Start the connection between the slave and the master on the slave.

4. Check whether the configuration is successful or not.

1. Modify the my. cnf configuration file of the master and add the following configuration under [mysqld:

[Mysqld]

Log-bin =/usr/local/mysql56/binlog/master-bin

Log-bin-index =/usr/local/mysql56/binlog/master-bin.index

Server-id = 1

Note:

Log-bin is configured to enable binary logs and write the logs on the configured path. The logged logs are recorded in master-bin.000001.

Log-bin-index is the directory of the binary log file. Each line in this file is the path of the binary log file.

Server-id is the service ID of the MySQL server, used to distinguish other servers in the master-slave configuration.

After successful configuration, restart the mysqld service.

Note: If the file name of log-bin is not defined, it will be named after the hostname host name. Once the host name is modified, an error will be reported because the binary file is not found. Therefore, it is best to display the specified file name.

2. Modify the my. cnf configuration file of slave and add the following configuration under [mysqld:

Server-id = 11

Relay-log =/usr/local/mysql56/binlog/slave-relay-bin

Relay-log-index =/usr/local/mysql56/binlog/slave-relay-bin.index

Server-id is the same as the master, used to distinguish different mysql servers.

Relay-log is used to record received relay binary logs. Relay-log-index is used to save the received log Path index. If the specified file name is not displayed, the hostname is used.

After saving, You can restart the mysqld service.

3. Start the connection between the slave and the master on the slave.

Log on to mysql on slave and perform operations under mysql>.

1.1. mysql> stop slave; // stop slave.

2.2 mysql> change master

-> Master_host = '192. 168.80.51 ', // note that a comma exists.

-> Master_port = 3316,

-> Master_user = 'root ',

-> Master_password = 'root ';

1.3. mysql> start slave;

4. Check whether the configuration is successful or not.

Mysql> show slave status \ G;

In the printed status, view the values of the following two parameters:

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

If the values of the two parameters are yes, the operation is normal. You can add a database or a table to the master database to check whether the slave database exists.

If either of the two parameters is not Yes, it indicates that there is a problem. You can view the Database Error Log File on slave to view the error cause.

Common error causes:

1. The server-id is consistent.

2. Insufficient user permissions. The required permissions include replication slave, RELOAD, create user, and SUPER.

Grant replication slave, RELOAD, create user, super on *. * TO {USER }@{ IP_ADDR} with grant option;

3. Database UUID consistency. If mysql is installed through batch copy and installation, it is possible that the database UUID is consistent, enter the datadir directory of the database, and modify auto. cnf. Modify the value in uuid and restart the mysqld service.

4. The Master database port is blocked by the firewall.

This article permanently updates the link address: