(turn) The realization of MySQL dual-machine hot standby

The realization of MySQL dual-machine hot standby

Original: http://www.zjian.me/web/php/mysql%E5%8F%8C%E6%9C%BA%E7%83%AD%E5%A4%87%E7%9A%84%E5%AE%9E%E7%8E%B0/

The MySQL database does not have a mechanism for incremental backups, which is a big problem when the amount of data is too large. Fortunately, MySQL database provides a mechanism of master-slave backup, in fact, all the data of the primary database is written to the backup database at the same time. Implement a hot backup of the MySQL database.

To realize the dual-machine hot standby, first of all to understand the master-slave database server version requirements. The version of MySQL to be hot prepared is higher than 3.2. There is also a basic principle that the data version from the database can be higher than the version of the primary server database, but not lower than the database version of the primary server.

Of course, to achieve MySQL dual-machine hot standby, in addition to the MySQL itself with the replication function can be achieved, but also can be used heartbeat this open source software to achieve. However, this article is mainly about how to use MySQL's own replication to realize the MySQL dual-machine hot standby function.

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

Test Server version: 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): 192.168.1.101

b Server (from server slave): 192.168.1.102

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:

1	mysql> create table tb_mobile( mobile VARCHAR(20) comment‘手机号码‘, timetimestamp DEFAULT now() comment‘时间‘);

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:

12	mysql> grant replication slave on *.* to ‘replicate‘@‘192.168.1.102‘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):

1	[[email protected] ~]# mysql -h192.168.1.101 -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

After locating the profile my.cnf open, modify it under [mysqld]:

12345

[mysqld]server-id= 1log-bin=mysql-bin                //其中这两行是本来就有的，可以不用动，添加下面两行即可binlog-do-db = testbinlog-ignore-db = mysql

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:

12345

[mysqld]server-id= 2log-bin=mysql-binreplicate-do-db = testreplicate-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:

1234

mysql>stop slave;          // Stop slave service thread, this is important, if not doing this will cause the following operations to be unsuccessful. mysql>change master to >master_host= ' 192.168.1.101 ' ,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.

If you follow the above steps, you will see the following situation:
To reset the slave. Instructions are as follows

12	mysql>stop slave;mysql>reset slave;

Then stop the slave thread to start again. After success, you can turn on the slave thread.

Mysql>start slave;

2.3.4 View from server (Slave) status

Use the following command to view

1	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

2.4 Test synchronization

I've already said it before. In the database test there is only one table Tb_mobile no data, we can first see whether the database of the next two servers have data, and then can be inserted in the master server to see if the data can be synchronized.
If the data inserted on the master server can be found on the slave server, this indicates that the dual-machine hot standby configuration is successful.

3. Mysql Build Master-master server dual-Machine hot standby configuration steps

The server still uses the two servers back now

3.1 Creating a synced user

Also establish a connection account on the master-slave server, which must grant 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:

12	mysql> grant replication slave on *.* to ‘replicate‘@‘192.168.1.102‘identified by ‘123456‘;mysql> flush privileges;

Server B:

12	mysql> grant replication slave on *.* to ‘replicate‘@‘192.168.1.101‘identified by ‘123456‘;mysql> flush privileges;

3.2 Modifying the configuration file my.cnf

Server A

12345

[mysqld]server-id= 1log-bin=mysql-bin binlog-do-db = testbinlog-ignore-db = mysql

#主-The main form needs to add more parts

123456

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:

123456

[mysqld]server-id= 2log-bin=mysql-bin master-slave needreplicate-do-db = testreplicate-ignore-db = mysql,information_schema,performance_schema

#主-The main form needs to add more parts

123456

binlog-do-db = testbinlog-ignore-db = mysqllog-slave-updatessync_binlog = 1auto_increment_offset = 2auto_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

3.5 Specify the synchronization location on the a server and the B server, respectively, with change master

Server A:

123	mysql>change master to>master_host=‘192.168.1.102‘,master_user=‘replicate‘,master_password=‘123456‘,> master_log_file=‘ mysql-bin.000011 ‘,master_log_pos=497;

Server B:

123	mysql>change master to>master_host=‘192.168.1.101‘,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

1	mysql>start slave;

3.7 Viewing from server status on a and B servers, respectively

1	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:

After you insert a statement on Server A, view the synchronization on Server B.

Test Server B:

Insert a statement on Server B and then view the synchronization data from server A.

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.

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.

(turn) The realization of MySQL dual-machine hot standby