MySQL database InnoDB Engine master-slave replication synchronization experience, mysqlinnodb

MySQL database InnoDB Engine master-slave replication synchronization experience, mysqlinnodb

Recently, the company's MySQL architecture has been upgraded, from the original one-master-many slave to the DRBD + Heartbeat dual-master-many slave, and a new e-commerce website project is coming soon, DRBD + Heartbeat dual master and one slave are used. Because this process is different from the previous MyISAM engine, I will summarize my experiences here:

1) The replication process of MySQL is an asynchronous synchronization process, not completely master-slave synchronization, so there is a delay in the synchronization process. If the read/write splitting service is enabled, we recommend that you monitor the delay;

2) the master and slave machines of MySQL should remember that the server-id should be inconsistent. If the same, the following error will occur during replication:

Copy codeThe Code is as follows:
Fatal error: The slave I/O threadstopsbecause master and slavehave equal MySQL server ids; these ids mustbedifferent for replication to work (or the -- replicate-same-server-id optionmustbe used on slave but this doesnot always make sense; please check themanualbefore using it ).

This problem is well handled. Change the server-id of the server Load balancer to be different from that of the master machine.

3) one of my previous mistakes is that the slave machine uses its own binary log to complete the replication process. In fact, this is not the case. According to the working principle of replication: the slave server is to copy the binary log of the master server to its own relay log, that is, the relay-log (that is, the name of the centos3-relay-bin.000002), and then apply the update application to its own database, therefore, the slave machine does not need to enable binary logs, so the process will be successful. unless the master architecture is prepared, the slave machine needs to enable binary logs, this problem has always led me to think that binary must be enabled when the slave machine builds the replication environment.

4) When authorizing on the master machine, try to grant only one or several fixed machine permissions so that they only have the replication slav and replication client permissions, and try not to grant permissions. In addition, although the database is generally operated through the Intranet, the more we authorize the MySQL database over the Intranet, the more we should pay attention to security;

5) if the replication construction process follows the normal process, it is generally easy to implement successfully. If an error occurs, check the network environment and permissions, generally, the entire construction process should be smooth.

At the early stage of database design, I have defined the e-commerce database engine as InnoDB. In addition to the original system tables in the database, all other tables are converted from MyISAM to InnoDB for two reasons:

1) e-commerce businesses involve transaction payment. In such basic OLTP applications, InnoDB should be the preferred storage engine for core application tables;
2) When the DRBD system is restarted, the process will be slow and the table will be read frequently. If the table engine is MyISAM, it is very likely to be damaged. In order to cause unnecessary problems, I converted the table engine of the database from MyISAM to the table of the InnoDB engine.

DRBD + Heartbeat + MySQL refer to the previous working documents and the setup was successful. The error 1062 was reported when the replication environment was set up. The detailed process is as follows:
In the initial stage, refer to the MySQL manual for operations. Take the snapshot backup of the master machine and use the -- single-transaction option. Then, 1062 errors are frequently reported during synchronization. The error log is as follows:
Copy codeThe Code is as follows:
Last_ SQL _Error: Error 'duplicate entry 'd36ad91bff36308de540bbd9ae6f4279 'for key'primary'' on query. defaultdatabase: 'myproject '. query: 'insert INTO 'Lee _ session' ('session _ id', 'IP _ address', 'user _ agent', 'last _ activity ', 'User _ data') VALUES ('d36ad91bff36308de540bbd9ae6f4279 ', '2017. 153.201.218 ', 'mozilla/123', 4.0 ,'')'

Later, I changed my mind and used the -- master-data option to take the master snapshot for backup. The command is as follows:
Copy codeThe Code is as follows:
Mysqldump-uroot -- quick -- flush-logs -- master-data = 1-p myproject> myproject. SQL

Note: -- master-data is used to back up an SQL file using this parameter. We recommend a slavereplication. If the value is 1, the SQL file records the change master statement; when the value is 2, the change master is written as an SQL comment, -- master-data automatically uses the lock-all-tables option when the -- single-transaction option is not used (that is, the second-generation options are not used together ). How can I find LOG_FILE and LOG_POS in SQL? You can use the following command (note that the word change must be written in uppercase:
Copy codeThe Code is as follows:
Grep "CHANGE" myproject. SQL

The command output is as follows:
Copy codeThe Code is as follows:
Change master TOMASTER_LOG_FILE = 'mysql-bin.000008 ', MASTER_LOG_POS = 106;

The next replication process will not be detailed. After synchronization is complete, we will observe for a long time and no error 1062 will be reported again, as shown below:

Copy codeThe Code is as follows:
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waitingformaster to send event
Master_Host: 192.168.11.174
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000008
Read_Master_Log_Pos: 27880
Relay_Log_File: centos3-relay-bin.000002
Relay_Log_Pos: 28025
Relay_Master_Log_File: mysql-bin.000008
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 27880
Relay_Log_Space: 28182
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_ SQL _Errno: 0
Last_ SQL _Error:
1 row in set (0.00 sec)

In the work, the master-slave replication and synchronization experience of the InnoDB engine database is also involved in the backup and replication of the InnoDB database. A more common practice is to stop the database for replication, although it is also a way of thinking to solve the problem, it is not allowed in some special application scenarios due to downtime maintenance, we should try to use mysqldump as a logical backup method to take master host snapshots.