New methods for configuring synchronous replication in MySQL5.6 and solutions to common problems

New methods for configuring synchronous replication in MySQL5.6 and solutions to common problems

MySQL5.6 adds a method that does not need to set the log_bin file and log location. Before that, we need to view the master machine information (show master status), and then set it in slave.

Master_host = '10. 163.213.228 ', master_user = 'repl', master_password = 'sangfordb', master_log_file = 'mysql-bin.000015', master_log_pos = 193952;

After mysql5.6 is used, you do not need to configure this method. You only need to set the my. cnf file.

Log-bin = mysql-bin

Binlog_format = row

Log_slave_updates

Gtid-mode = ON

Enforce-gtid-consistency = ON

Then set in slave:

Change master

Master_host = 'masterip', master_user = 'replicationuser', master_password = 'Password', master_AUTO_POSITION = 1;

Masterip, replicationuser, and password correspond to the information of your master respectively.

FAQs:

1. Slave_IO_State in show slave status: Waiting to reconnect after a failed registration on master

Solution:

Run

Grant replication slave on *. * to 'repl' @ '%' identified by 'Password ';

Flush privileges;

Then stop slave and start slave again.

2. Slave_IO_Running: No in show slave status

Last_IO_Error: 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.

Solution: the uuid of each database should be different. Modify the uuid of the auto. cnf file (under the mysql data DIRECTORY:

[Auto]

Server-uuid = 6dcee5be-8cdb-11e2-9408-90e2ba2e2ea6

Follow the hexadecimal format to change it and restart mysql.

3. In the slave database, slave_ SQL _running is NO.

Generally, the error statement is displayed in Last_ SQL _Error:

Slave_IO_Running: connects to the master database and reads logs from the master database to the local computer to generate local log files.

Slave_ SQL _Running: reads the local log file and executes the SQL commands in the log.

At this time, many online users will teach you to SET global SQL _slave_skip_counter = n;, but in fact, an error will be reported when this statement is executed because gtid-mode = ON is started.

Solution: Skip the statement that reports an error

View the last two lines of show slave status:

Retrieved_Gtid_Set indicates the transaction that has been pulled from the master;

Executed_Gtid_Set indicates the transaction that has been executed

Find the record in Executed_Gtid_Set that is consistent with the Retrieved_Gtid_Set ID, such as ca83d308-2ea5-11e4-b85f-00163e042f50: 1-5, indicates that only the fifth transaction is executed, that is, the transaction reports an error, so skip this transaction. Execute the following statements in sequence:

Stop slave;

Set gtid_next = 'ca83d308-2ea5-11e4-b85f-00163e042f50: 6 ';

Begin; commit;

Set gtid_next = "AUTOMATIC ";

Start slave;

Then view the show slave status \ G

If the following status is displayed

PS: when starting a master-slave backup, make sure that the master-slave data is consistent, because the slave database encounters any errors, even if an update statement is executed, a record in the master database cannot be found in the slave database, problem 2 is reported, and slave update is stopped.

-------------------------------------- Split line --------------------------------------

Install MySQL in Ubuntu 14.04

MySQL authoritative guide (original book version 2nd) Clear Chinese scan PDF

Ubuntu 14.04 LTS install LNMP Nginx \ PHP5 (PHP-FPM) \ MySQL

Build a MySQL Master/Slave server in Ubuntu 14.04

Build a highly available distributed MySQL cluster using Ubuntu 12.04 LTS

Install MySQL5.6 and Python-MySQLdb in the source code of Ubuntu 12.04

MySQL-5.5.38 universal binary Installation

-------------------------------------- Split line --------------------------------------

This article permanently updates the link address: