New methods for configuring synchronous replication in mysql5.6 and solutions to common problems, new methods for mysql5.6

New methods for configuring synchronous replication in mysql5.6 and solutions to common problems, new methods for mysql5.6

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 'sangfordb ';

Flush privileges;

Then stop slave and start slave again.

2. 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.

MySql56 Optimized Configuration

I allocated 256 of the memory for this broken Virtual Machine. When I inserted data using the stored procedure, 2 million would be 10 minutes.
I want to know how you insert data. Is it innodb? Is the automatically submitted value 0 or 1?

The last step of MySQL56 installation is stuck? This is

I told you to uninstall mysql and delete the environment variables in the system. You can delete the MySQL folder on drive C and then reinstall it.