MySQL Master-slave configuration

Reference: http://www.cnblogs.com/kylinlin/p/5258719.html

1. Environment

Os:ubuntu 15.10

ip:192.168.68.23 (from)/192.168.68.24 (master)

Mysql:5.6.31-0ubuntu0.15.10.1-log

2. Primary server Configuration

• Modify MySQL Configuration

' Sudo vi/etc/mysql/mysql.conf.d/mysqld.cnf '

# Enable remote Connectionbind-address = 0.0.0.0...# 2017-5-19 16:35server-id = 1log_bin =/var/log/mysql/mysql-bin.log

• Restart MySQL

' sudo service mysql restart '

• Authorized Master-Slave replication users

' Grant replication Slave on * * to ' replication ' @ ' 192.168.68.% ' identified by ' replication '; '

• ' Flush tables with read lock; '

• ' Show master status; '

• Back up the entire library and upload it to the slave server

' Mysqldump-uroot-p--all-databases >/tmp/mysql_bak.sql '

' Scp/tmp/mysql_bak.sql [email protected]:/tmp/mysql_bak.sql '

• ' Unlock tables; '

3. Configure the slave server

• Modify MySQL Configuration

' Sudo vi/etc/mysql/mysql.conf.d/mysqld.cnf '

# Enable remote Connectionbind-address = 0.0.0.0...# 2017-5-19 16:35 This value needs to be different from the primary server Server-id = 2

• Restart MySQL

' sudo service mysql restart '

• Initializing the entire library

' Mysql-uroot-p </tmp/mysql_bak.sql '

• Add recognition from configuration

Mysql> Change MASTER to
Master_host= ' 192.168.68.24 ',
master_port=3306,
Master_user= ' Replication ',
master_password= ' Replication ',
Master_log_file= ' mysql-bin.000001 ',
master_log_pos=120;

• Start master/Slave

' Start slave; '

• View Master and slave status

' Show slave status \g '

Slave_io_running/slave_sql_running for Yes indicates success

4. Problems encountered

The slave I/O thread stops because master and slave have equal MySQL server UUIDs; These uuids must is different for replication?

Solution: http://www.chriscalender.com/resolving-error-master-and-slave-have-equal-mysql-server-uuids/

A) ' mv/var/lib/mysql/auto.cnf/tmp/auto.cnf '

b) ' sudo service mysql restart '

MySQL Master-slave configuration