Detailed tutorials of mysql master-slave configuration under linux

1. Modify MySQL configuration:

Main library configuration

Server-id = 3

Binlog-do-db=xmcp_gxfc # the db need to sync

Binlog-ignore-db = mysql # databases that do not require synchronization

Binlog-ignore-db = redmine # databases that do not require synchronization

Log_slave_updates = 1

Binlog_format=mixed

Relay_log = / usr/local/mysql/relay_log/mysql-relay-bin

Read_only = 1

2 create an account

Grant replication slave on. To 'slave2'@'%' identified by' FjAfj6#xajot#K%V'

Grant replication slave on. To 'slave3'@'%' identified by' FjAfj6#xajot#K%V'

Update database permissions

Mysql > flush privileges

Mysql > show master status

Record that File is mysql-bin.000001

Recorded a position of 154,

3. Modify the slave MySQL configuration:

From the library configuration:

Server-id = 5

Log-bin = mysql-bin

Replicate-do-db=xmcp_gxfc

Binlog_format=mixed

Relay_log=/usr/local/mysql/relay_log/mysql-relay-bin

Read_only = 1

4. Execute the synchronization command

Execute the synchronization command, set the main database ip, synchronization account password, synchronization location

Mysql > change master to master_host='10.2.2.2',master_user='slave2',master_password='FjAfj6#xajot#K%V',master_log_file='mysql-bin.000001',master_log_pos=154

Turn on synchronization function

Mysql > start slave

5. Check the slave database status:

Mysql > show slave status\ G

Note: Slave_IO_Running and Slave_SQL_Running processes must be running normally, that is, YES status, otherwise synchronization failed. These two items can be used to determine whether the slave server hung up or not.

Mysql > SET GLOBAL server_id=2

6 、 Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

Cause analysis:

The replication of mysql 5.6introduces the concept of uuid. The server_uuid in each replication structure has to be guaranteed to be different, but the server_uuid is the same after viewing the direct copy data folder, show variables like'% server_uuid%'.

Solution:

Find the auto.cnf file under the data folder, modify the UUID value in it, make sure that the uuid of each db is different, and restart db.

Scenario 2: when creating a master-slave relationship, copy uses the same my.cnf file and reports an error.

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids

Cause analysis:

Like server_uuid, servier_id has to make sure it's different.

Solution:

Find the server_id in the my.cnf configuration file, modify the server_id of the slave library to ensure that it is different from other db in the replication structure, and restart db.