Master-Slave library synchronization based on the mysql5.6 version

System: centos6.4

MySQL version: 5.6.17

Main Library: 192.168.31.111 from library: 192.168.31.235

Main Library Operations:

1. Configure MY.CNF file to open binary log

Log_bin = On

server_id = 1

2. Set up account rep for synchronizing database

Grant Replication Slave on * * to ' rep ' @ ' 192.168.31.% ' identified by ' Redhat ';

Select User,host,password from Mysql.user;

3. Lock table Read-only for the main library, fully back up the main library

Mysql>flush tables with read lock;

Mysql-uroot-p-E "show master status;" >/backup/mysql_bak.log: Record the location of the binary log file

The next step is to fully back up the database:

Mysqldump-uroot-p-a-b |gzip >/backup/mysql_bak. ' Date +%f '. sql.gz

Then contact the lock of the table

Mysql>unlock tables;

######################################################################################

From library operations:

1. Upload the backup's main library data to the/tmp directory from the library using SCP

Scp/backup/mysql_bak. ' Date +%f '. sql.gz 192.168.31.235:/tmp/

2, set the server_id from the library

server_id = 2

3. Unzip the data and restore the data to the library

gzip-d mysql_bak. ' Date +%f '. sql.gz

MySQL < Mysql_bak. ' Date +%f '. sql

4. Then execute the master statement

Mysql>mysql>change Master to master_host= ' 192.168.31.111 ', master_user= ' rep ', master_password= ' Redhat ', master _log_file= ' on.000004 ', master_log_pos=120;

Mysql>start slave;

Mysql>show slave Status\g

When the status information from the library appears, the following two yes:

Slave_io_running:yes

Slave_sql_running:yes

Then master-Slave synchronization has also been basically successful, attached success:

650) this.width=650; "src=" http://s4.51cto.com/wyfs02/M02/7F/4A/wKiom1cYwuXiV4cNAACdPVgS2bU295.jpg "title=" 1.jpg " alt= "Wkiom1cywuxiv4cnaacdpvgs2bu295.jpg"/>

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M01/7F/48/wKioL1cYw8Sz3BEYAADA6QC7fqE432.jpg "title=" 2.jpg " alt= "Wkiol1cyw8sz3beyaada6qc7fqe432.jpg"/>

Another: Attach a log of failures of previous times

2016-04-17 10:38:40 33785 [Warning] storing MySQL user name or password information in the master Info repository are not s Ecure and is therefore not recommended. Consider using the USER and PASSWORD connection options for START SLAVE; See the ' START SLAVE Syntax ' on the MySQL Manual for more information.

2016-04-17 10:38:40 33785 [Warning] Slave sql:if A crash happens this configuration does not guarantee that the relay log Info'll be consistent, error_code:0

2016-04-17 10:38:40 33785 [Note] Slave SQL thread initialized, starting replication in log ' on.000004 ' at position 382, re Lay log './nginx-relay-bin.000001 ' Position:4

2016-04-17 10:38:40 33785 [Note] Slave I/O thread:connected to master ' [email protected]:3306 ', replication started in log ' on.000004 ' at position 382

2016-04-17 10:38:40 33785 [ERROR] Error reading packet from server:misconfigured master-server_id is not set (Server_ errno=1236)

2016-04-17 10:38:40 33785 [ERROR] Slave I/o: Got fatal ERROR 1236 from master if reading data from binary log: ' Misconfi Gured master-server_id is not set ', error_code:1236

2016-04-17 10:38:40 33785 [Note] Slave I/O thread exiting, read up to log ' on.000004 ', position 382

By the above log error message know that misconfigured master-server_id is not set,server_id is not set, so check the master and slave library server_id

Then the second attempt results or error:

2016-04-17 11:00:03 33785 [ERROR] Error reading packet from Server:log event entry exceeded Max_allowed_packet; Increase max_allowed_packet on Master; The first event ' on.000004 ' at the "The last event", read from './on.000004 ' At, the last byte read from './on.000004 ' A T 349. (server_errno=1236)

2016-04-17 11:00:03 33785 [ERROR] Slave I/o: Got fatal ERROR 1236 from master when reading data from binary log: ' Log even T entry exceeded Max_allowed_packet; Increase max_allowed_packet on Master; The first event ' on.000004 ' at the "The last event", read from './on.000004 ' At, the last byte read from './on.000004 ' A T 349. ', error_code:1236

2016-04-17 11:00:03 33785 [Note] Slave I/O thread exiting, read up to log ' on.000004 ', position 330

The log information understands that the position location of the binary log is not causing it, and then it is restarted on the main library.

Show master status; get the right position

Then start the start slave again;

And then it worked.

Note: When the attempt is unsuccessful, be sure to check the error log information, where

Master-Slave library synchronization based on the mysql5.6 version