A double-click Hot standby for MySQL database setup in Linux environment

Preparing the server

Because the Binlog format may not be the same in different versions of MySQL (binary log), the best combination is the MySQL version of the primary (master) server and the same or lower from the (Slave) server version, and the master server version must not be higher than the from server version.
The two server versions I used to test are Mysql-5.5.17.

2. Mysql Build master-Slave server dual-machine hot standby configuration steps

2.1 Environmental Description
A server (master server Master): 59.151.15.36
b Server (from server slave): 218.206.70.146
MySQL version of master-slave server is 5.5.17
The Linux environment
A copy of the database content that the primary server needs to synchronize is uploaded to the slave server to ensure that the database content in both servers is consistent at the beginning.
However, as explained here, because I am using MySQL after the installation of the database test, so there is no table in the two servers, only the test is not built in the same empty table tb_mobile;
The SQL statements are as follows:
Mysql> CREATE TABLE Tb_mobile (mobile VARCHAR comment ' mobile phone number ', Time timestamp DEFAULT now () comment ' Times ');
2.2 Primary Server Master configuration
2.2.1 Creating a synchronization user
Enter the MySQL operator interface to establish a connection account on the primary server for the slave server, which must grant replication slave permissions. Since MySQL version 3.2 can be used by the replication to the dual-machine hot standby function operation.
The operation instructions are as follows:
mysql> Grant replication Slave on.To ' replicate ' @ ' 218.206.70.146 ' identified by ' 123456 ';
mysql> flush Privileges;
Once the synchronization connection account has been created, we can see if the connection is successful by accessing the primary server (master) database with the Replicat account on the slave server (Slave).
Enter the following command on the slave server (Slave):
[Email protected] ~]# mysql-h59.151.15.36-ureplicate-p123456
If the following results appear, you can log on successfully, indicating that both servers are ready to operate on a two-machine hot standby.

2.2.2 Modifying the MySQL configuration file
If the above preparations are done, we can make changes to the MySQL configuration file, first find the MySQL configuration all in the directory, generally after the installation of the MySQL service, the configuration file will be copied one by one copies out into the/ect directory, and the configuration file named: my.cnf. That is, the exact configuration file directory is/ETC/MY.CNF
(MySQL installed under Linux with RPM package will not install the/etc/my.cnf file,
As for why there is no such file and MySQL can start and function normally, at the point there are two statements,
The first argument is that MY.CNF is just a parameter file at MySQL startup, and without it, MySQL will start with the built-in default parameters,
The second argument is that MySQL automatically uses the my-medium.cnf file in the/usr/share/mysql directory at startup, which is limited to the RPM package installation of MySQL,
Workaround, just copy the my-medium.cnf file from the/usr/share/mysql directory to the/etc directory and rename it to MY.CNF. )
After locating the profile my.cnf open, modify it under [mysqld]:
[Mysqld]
Server-id = 1//Unique ID
Log-bin=mysql-bin//One of these two lines is the original, you can not move, add the following two lines. Specifying log files
BINLOG-DO-DB = test//Log database
binlog-ignore-db = mysql//log-not logged database
2.2.3 Restart MySQL Service
After modifying the configuration file, after saving, restart the MySQL service, if successful, no problem.

2.2.4 Viewing the primary server status
After entering the MySQL service, the command can be used to view the master status and enter the following command:

Note the parameters inside, especially the first two file and position, which can be useful to configure the master-slave relationship from the server (Slave).
Note: The lock table is used here, in order to generate the environment in the new data, so that from the server location synchronization location, the initial synchronization is completed, remember to unlock.

2.3 Configuration from server slave
2.3.1 Modifying a configuration file
Because this is in the main-from the way to achieve the MySQL dual-machine hot standby, so in the slave server is not in the establishment of synchronization account, directly open the configuration file my.cnf to modify, the same reason is the same as the modification of the main server, just need to modify the parameters are different. As follows:
[Mysqld]
Server-id = 2
Log-bin=mysql-bin
REPLICATE-DO-DB = Test
Replicate-ignore-db = Mysql,information_schema,performance_schema
2.3.2 Restart MySQL Service
After modifying the configuration file, after saving, restart the MySQL service, if successful, no problem.

2.3.3 specifying the synchronization location with the change Mster statement
This step is the most critical step, after entering the MySQL operator interface, enter the following command:
Mysql>stop slave; It is important to stop the slave service thread first, and if you do not do this it will cause the following operations to be unsuccessful.
Mysql>change Master to

master_host= ' 59.151.15.36 ', master_user= ' replicate ', master_password= ' 123456 ',
master_log_file= ' Mysql-bin.000016 ', master_log_pos=107;
Note: master_log_file, Master_log_pos is determined by the status value identified by the master server (master). It's just called attention. Master_log_file corresponds to file, Master_log_pos corresponds to position. Mysql 5.x or later does not support the option to specify the primary server in the configuration file.
problems encountered, if you follow the above steps, the following occurs:

to reset the slave. The instruction is as follows
Mysql>stop slave;
Mysql>reset slave; The
stops the slave thread from starting again. After success, you can turn on the slave thread.
Mysql>start Slave;
2.3.4 View Slave server (Slave) Status
View
mysql> show Slave status\g with the following instructions:

Viewing the following two key values is yes, which means that the setting is successful from the server.
Slave_io_running:yes
Slave_sql_running:yes
2.4 Test synchronization
has been said before in the database test that there is only one table Tb_mobile no data, We can check whether the database of the next two servers has data:
master:59.151.15.36

slave:218.206.70.146

OK, now you can insert data in the Master server to see if it can be synchronized.
master:59.151.15.36

slave:218.206.70.146

can be seen from the above two, the data inserted on the Master server can be found on the Slave server, This means that the dual-machine hot standby configuration is successful.

3. Mysql Establish Master-master server hot standby configuration step
   Server or use back the two servers
   3.1 create a synchronization user
   at the same time establish a connection account in the master and slave server, the account must be granted repliation Slave permissions. This is because server A and Server B are mainly from each other, so we have to set up a synchronization user separately.
   Server A:
   mysql> grant replication Slave on . to ' replicate ' @ ' 218.206.70.146 ' identified by ' 123456 ';
   mysql> flush Privileges;
   Server B:
   mysql> grant replication Slave on . to ' replicate ' @ ' 59.151.15.36 ' identified by ' 123456 ';
   mysql> flush Privileges;
   3.2 Modify configuration file my.cnf
   Server A
   [mysqld]
   Server-id = 1
   log-bin=mysql-bin
   binlog-do-db = Test
   binlog-ignore-db = MySQL
   #主-the main form needs to add more parts
   log-slave-updates
   Sync_binlog = 1
   Auto_increment_offset = 1
   Auto_increment_increment = 2
   replicate-do-db = Test
   Replicate-ignore-db = Mysql,information_schema
   Server B:
   [mysqld]
   Server-id = 2
   log-bin=mysql-bin
   replicate-do-db = Test
   Replicate-ignore-db = MySQL, Information_schema,performance_schema

#主-The main form needs to add more parts
BINLOG-DO-DB = Test
binlog-ignore-db = MySQL
Log-slave-updates
Sync_binlog = 1
Auto_increment_offset = 2
Auto_increment_increment = 2
3.3 Restart the MySQL service on Server A and Server B, respectively
Restart the server the same way as above, there is no explanation.
3.4 Check the status of a server and B server as the primary server respectively
Server A:

Server B:

3.5 Specify the synchronization location on the a server and the B server, respectively, with change master
Server A:
Mysql>change Master to

Master_host= ' 218.206.70.146 ', master_user= ' replicate ', master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000011 ', master_log_pos=497;
Server B:
Mysql>change Master to
Master_host= ' 59.151.15.36 ', master_user= ' replicate ', master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000016 ', master_log_pos=107;
3.6 Restart from the service thread on a and B servers respectively
Mysql>start slave;
3.7 Viewing from server status on a and B servers, respectively
Mysql>show slave status\g;
Viewing the following two key values are yes, which means that the setting is successful from the server.
Slave_io_running:yes
Slave_sql_running:yes
3.8 Test Master-Master Synchronization example
Test Server A:
Insert a statement on Server A as shown in:

Then check on server B to see if the synchronization is as shown:

Test Server B:
Insert a statement on Server B as shown in:

Then, from server A, see if there is synchronization data as shown in:

Finally, it can be seen from the results that the master-master form of dual-machine hot standby can be successfully realized.

4. Configuration parameter Description
   Server-id
   The ID value uniquely identifies the master and slave servers in the replication cluster, so they must be different. The master_id must be a positive integer value between 1 and 232-1, and the slave_id value must be a positive whole number between 2 and 232-1.
   Log-bin
   Indicates that opening binlog, open this option can be written to slave relay-log through I/O, is also a prerequisite to replication.
   Binlog-do-db
   Represents a database that requires logging of binary logs. If more than one data can be separated by commas, or multiple BINLOG-DO-DG options are used.
   Binglog-ingore-db
   Represents a database that does not require logging of binary logs, if multiple databases can be separated by commas, or the multi-binglog-ignore-db option is used.
   Replicate-do-db
   Represents a database that needs to be synchronized, if multiple data can be separated by commas, or multiple replicate-do-db options are used.
   Replicate-ignore-db
   Represents a database that does not need to be synchronized, if more than one database can be separated by commas, or multiple replicate-ignore-db options are used.
   Master-connect-retry
   Master-connect-retry=n indicates that the connection from the server to the primary server is not successful, and then waits n seconds (s) before it is managed (the default setting is 60s). If the Mater.info file exists from the server, it ignores the options.
   Log-slave-updates
   Configure whether the update operation from the library is written to the binaries, and if this is done from the library and other main libraries from the library, then this parameter needs to be hit so that the log can be synchronized from the library from the library.
   Slave-skip-errors
   During the copy process, SQL error in Binglo due to various reasons, by default, the copy is stopped from the library and the user is involved. You can set Slave-skip-errors to define the error number, and you can pass if the error encountered during the copy process is a defined error number. If the from library is used for backup, setting this parameter will present inconsistent data, do not use. If you are sharing the query pressure of the main library, consider it.
   --slave-skip-errors=[err_code1,err_code2,... | All|ddl_exist_errors]
   Command-Line Format--slave-skip-errors=name
   Option-file Format slave-skip-errors
   System Variable Name slave_skip_errors
   Variable Scope Global
   Dynamic Variable No
   Permitted Values
   Type string
   Default OFF
   Valid Values OFF
   [List of error codes]
   All
   Ddl_exist_errors

MySQL 5.6 as well as MySQL Cluster NDB 7.3 Support an additional shorthand value
Ddl_exist_errors, which is equivalent to the error code list 1007,1008,1050,1051,
1054,1060,1061,1068,1094,1146.
Examples:

--slave-skip-errors=1062,1053
--slave-skip-errors=all
--slave-skip-errors=ddl_exist_errors
Sync_binlog=1 Or N
The default value for Sync_binlog is 0, and in this mode, MySQL is not synced to disk. In this case, MySQL relies on the operating system to flush binary log binaries, just as the operating system refreshes other files. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the last statement in the Binlog is lost. To prevent this, you can use the Sync_binlog global variable to synchronize the Binlog with the hard disk after every n binlog write. When the Sync_binlog variable is set to 1 is the safest, because in the case of crash crashes, your binary log binary logs can only lose up to one statement or one transaction. However, this is also the slowest way (unless the disk has cache caches with battery backup power, which makes it very fast to sync to disk).
Even if Sync_binlog is set to 1, there is a possibility of inconsistency between the table content and the Binlog content when a crash occurs. If you use the InnoDB table, the MySQL server processes the commit statement, which writes the entire transaction to Binlog and commits the transaction to InnoDB. If a crash occurs between two operations, the transaction is InnoDB rolled back, but still exists in Binlog. You can use the-innodb-safe-binlog option to increase the consistency between InnoDB table content and Binlog. (Note:-innodb-safe-binlog is not required in MySQL version 5.1; This option is obsolete because of the introduction of XA transaction support), which provides greater security for Binlog (sync_binlog=1) per transaction and (the default is True) the InnoDB log is synchronized with the hard disk, and the effect of this option is that after the crash restarts, after the transaction is rolled back, the MySQL server cuts the rollback from the Binlog innodb transaction. This ensures that the Binlog feedback innodb the exact data of the table, etc., and keeps the slave server in sync with the primary server (without taking the rollback statement).
Auto_increment_offset and Auto_increment_increment
Auto_increment_increment and Auto_increment_offset are used for primary-primary server (Master-to-master) replication and can be used to control the operation of the Auto_increment column. All two variables can be set to global or local variables, and each value can be assumed to be an integer value between 1 and 65,535. Setting one of the variables to 0 causes the variable to be 1.
These two variables affect the way auto_increment columns: auto_increment_increment The increment value of the value in the control column, Auto_increment_offset determines the starting point of the Auto_increment column value.
If the value of Auto_increment_offset is greater than the value of auto_increment_increment, the value of Auto_increment_offset is ignored. For example, if there is some data in the table, it will be used as the initial value of the largest self-increment currently available.

How to troubleshoot MySQL master-slave synchronization errors in SQL
Solve:
Stop slave;
#表示跳过一步错误, the following numbers are variable
Set global sql_slave_skip_counter = 1;
Start slave;
Then use mysql> Show slave status\g to view:
Slave_io_running:yes
Slave_sql_running:yes
OK, now the master-slave synchronization state is normal.

A double-click Hot standby for MySQL database setup in Linux environment