A test of mysql master-slave synchronization has occurred.

At three o'clock P.M., the server was shut down and recovered several minutes later. remote connection to the Windows virtual machine showed abnormal shutdown.

 

The website is abnormal, and the database is damaged due to abnormal shutdown and data needs to be repaired.

Log on to the slave server and show slave status \ G. The synchronization status is normal.

Close the mysql of the master server and run

/Mysql/bin/myisamchk-r/mysql/data/bbs/*. MYI

Restore database

Start mysql

Service mysql start

The website is normal. log on to the server and check the show slave status \ G.

 

Slave_IO_Running: No

Slave_ SQL _Running: Yes

Something went wrong with synchronization.

The problem with a slave server is the opposite. The slave database is located at the very end, and the master and slave databases are a server, which is damaged by power loss data.

 

Slave_IO_Running: Yes

Slave_ SQL _Running: No

 

 

Why is the synchronization normal before the fix?

 

 

After the master server crashes, it was originally considered to immediately change the database connection to the slave database. However, because the configuration is not in the master-slave mode, synchronization between the master and slave is normal, if you need to reconfigure the master and slave databases after switching back, you still need to choose to delay some time to repair the master database. Who knows that the synchronization ended after the restoration?

 

There is a problem to understand:

In the future, you must change the mode to the master-slave mode. It is convenient to switch between the master database and the slave database at any time when the master database is in the active state. After the master database is fixed, the slave database is switched back.

 

It seems that we have to stay up late for configuration tonight.

 

First, sort out the master-slave configuration steps:

Three msyql servers, two of which are the master and slave servers, and the other can act as the slave server, which is only used for regular data backup. The master and slave servers must be able to switch between each other at any time. They must not be virtual machines on the same server. Because the master server also fails when both mysql instances are lost, the significance of switching is lost.

 

Server A: 58.55.142.168

The two mysql servers a1 and a2 are on the Intranet.

A1: 10.0.0.82

A2: 10.0.0.81

Server B1: 161.18.41.72

1. main configuration a1vi/etc/my. cnf determine the following parameter server-id = 1 # Start logbinlog-bin = mysql-bin # log file retention days, to avoid the continuous growth of logs, the hard disk expire-logs-days = 7 # The database needs to be synchronized. Multiple databases are separated by commas to find that replicate-do-db = bbs is enabled, and the ucenter cannot be synchronized, replicate-ignore-db = mysql and test. The synchronization is normal after re-enabling, but some data is missing from the master database.) # replicate-do-db = bbs, ucenter # exclude synchronized database replicate-ignore-db = mysql, test # configure whether update operations on the slave database are written into the binary file. # If this slave database needs to be used as the master database of another slave database, you need to set this parameter so that the slave database can synchronize logs. Log-slave-updates 2. main configuration B1 vi/etc/my. cnf determine the following parameters: server-id = 3log-bin = mysql-binexpire-logs-days = 7replicate-ignore-db = mysql, testlog-slave-updates 3. configure a2 vi/etc/my. cnfserver-id = 2replicate-ignore-db = mysql, test # It seems that you do not need to enable the function only as the logbin configuration) log-bin = mysql-binexpire-logs-days = 7 # This does not need to be enabled # log-slave-updates synchronization settings refer to: slave

Change master to MASTER_HOST = '192. 18.41.72 ', MASTER_PORT = 161, MASTER_USER = 'slave', MASTER_PASSWORD = '2016 ';

Start slave;

In addition, the previous slave library must delete the master.info, relay-log.info, and localhost-relay-bin.xxxxx files under the data Directory.

Then start mysql to reconfigure slave Replication

 

Change master to MASTER_HOST = '58. 55.1420.8 ', MASTER_PORT = 3306, MASTER_USER = 'slave', MASTER_PASSWORD = '123 ';

Start slave;

 

 

Note that When configuring synchronization, the master database must lock the table to prevent data writing. When both databases are in normal synchronization status, unlock the table again, if the lock table is invalid, You can temporarily disconnect the website from mysql.

Test results: 10.0.0.81 a synchronization error occurred shortly after the local slave database was synchronized. I don't know if it was caused by connecting to the database on a webpage to check the synchronization status. It seems that the discuz Forum click to generate a write operation, I lock the database to read-only status and cannot access it. Synchronization is normal at 161.18.41.72, but when you switch the website connection to this database, the master database 10.0.0.82 shows that the synchronization status is normal, but the data is not synchronized. If you switch back to the synchronization status on both sides, an error is returned.   Slave_IO_Running: Yes Slave_ SQL _Running: No Mutual Master/Slave configuration seems to have failed. Currently, the above configuration is still a single master/Slave configuration.

 

 

Use two virtual machines to test mutual master and slave, and modify some configurations. 10.0.0.81 10.0.0.83   10.0.0.81Vi/etc/my. cnf   Server-id = 1 Log-bin = mysql-bin Replicate-ignore-db = test, mysql, information_schema, cece_schema Log-slave-updates Auto_increment_offset = 1 Auto_increment_increment = 2 slave-skip-errors = all 10.0.0.83Vi/etc/my. cnf   Server-id = 3 Log-bin = mysql-bin Replicate-ignore-db = test, mysql, information_schema, cece_schema Log-slave-updates   Auto_increment_offset = 2 Auto_increment_increment = 2 Slave-skip-errors = all

Data is inserted into databases on both sides, and synchronization is successful.

 

Log-slave-updatesThe = on parameter can also be used.

Slave-skip-errors = allSkip errors to avoid synchronization termination due to errors

 

These two parameters mainly solve the primary key conflict in Mysql replication technology. Auto_increment_offset = N machines Auto_increment_increment = 5 total number of machines Assume that there are five machines, so the sequence of the first machine is:, 11 ,...; The sequence of the second machine is: 2, 7, 12 ,...; The sequence of the third machine is: 3, 8, 13 ,... And so on. In this way, the auto-increment primary key will not be repeated.