InnoDB engine database master-slave replication synchronization new share _ MySQL

InnoDB engine database master-slave replication synchronization new sharing bitsCN.com recently upgraded the company's MySQL architecture, from the original master-slave multi-slave to DRBD + Heartbeat dual-master multi-slave, there is a new e-commerce website project to be launched, using DRBD + Heartbeat dual master and one slave, because this process is different from the previous MyISAM engine, therefore, 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:
Fatal error: The slave I/O thread stops because master and slavehave equal MySQL server ids; these ids must be different for replication to work (or the -- replicate-same-server-id option must be used on slave but this doesnot always make sense; please check the manual before 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:
Last_ SQL _Error: Error 'Duplicate entry 'd36ad91bff36308de540bbd9ae6f4279 'for key'primary'' on query. default database: '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:
Mysqldump-uroot -- quick -- flush-logs -- master-data = 1-p myproject> myproject.sqlbitsCN.com