Shulou(Shulou.com)06/01 Report--

First, background introduction

Before MySQL5.6, master-slave replication was achieved through binlog and position. When host A goes down, host B becomes the new master node, so you need to use SQL statement: CHANGE MASTER TO MASTER_HOST='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS='xxx' on host C. The difficulty of pointing your replication source to host B is that the binlog name and location of the same transaction are different on each machine, so how to find the master_log_file and master_log_pos location of the current synchronization stop point of host C on host B becomes a problem.

So MySQL produced GTID after 5.6.2, that is, the global transaction ID (global transaction ID), in the form of DomainID-ServerID-TransactionID, it is necessary to ensure that the server_id of each MySQL server is different and that the same GTID transaction is the same on each node.

II. GTID and binlog

It should be noted that the gtid configuration of Mariadb is different from that of MySQL, and they are not compatible. MaraDB is enabled by default after 10.0.2. Even though the slave server uses binlog and position for master-slave replication, it still uses GTID for tracking.

This means that the original slave configuration can be easily switched to GTID mode.

STOP SLAVE

CHANGE MASTER TO master_host='xxxx', master_port=3306, master_user='xxx',master_password='xxx',master_use_gtid=current_pos

START SLAVE

It is not complicated to switch from GTID mode to the previous binlog mode.

STOP SLAVE

CHANGE MASTER TO MASTER_HOST='xxx', MASTER_LOG_FILE='xxx', MASTER_LOG_POS='xxx'

START SLAVE

III. Introduction to master_use_gtid

Master_use_gtid = {slave_pos | current_pos | no} there are three options:

Slave_pos:slave copies the position of the last GTID of Master to the local, and the Slave host can view the position of the last GTID through the gtid_slave_pos variable.

Current_pos: suppose there are two AB hosts, and An is Master. When A fails, B becomes Master,An and readds as Slave. A has never played the role of slave before, so there is no GTID number copied before, so gtid_slave_pos is empty. This option is used in order to allow A to automatically add as Slave. This option is used in most cases because it is easy to use, regardless of whether the server used to be a Master or Slave role. However, be careful not to let the slave server write the transaction to the binlog log.

It is recommended that you enable gtid_strict_mode on the server so that things that are not generated by Master will be rejected. If binlog is not enabled from the server, the above two methods are equivalent.

No: turn off the GTID function

IV. Introduction of environment and Maradb configuration

This experiment uses CentOS7.4, the database version is MariaDB-10.2.14, and the topology is shown in the following figure:

In this simulation, when host A fails, host C redirects the Master host to host B, and when host An is repaired, it rejoins the cluster as Slave

III. Operation steps

Install MariaDB-10.2.14 on 1.3servers (brief)

2.A Host operation

(1) Edit configuration file

[root@host3 ~] # vim / etc/my.cnf.d/server.cnf

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip_name_resolve=ON

Relay_log=mysql-relaylog

Relay_log_index=mysql-relaylog

Relay_log_purge=OFF

Slow_query_log=ON

Server-id=10

Innodb_file_per_table=ON

Binlog_format=ROW

Log_bin=mysql-binlog

Log_slave_updates=ON

Gtid_strict_mode=ON

(2) start and enter MySQL

[root@host3 ~] # systemctl start mariadb.service

[root@host3 ~] # mysql

(3) create an account for master-slave replication

MariaDB [(none)] > grant replication slave on *. * to 'bak'@'172.16.10.%' identified by' bakpass'

MariaDB [(none)] > flush privileges

(4) back up the current database and send it to host B

[root@host3] # mysqldump-uroot-- single-transaction-- databases=hellodb-- masterdata=2-- quick > / tmp/hello.sql

[root@host3] # scp-r / tmp/hellodb.sql root@172.16.10.40:/tmp

(5) perform any DML operation after that to view the current gtid_binlog_pos

MariaDB [hellodb] > show global variables like 'gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_binlog_pos | 0-10-40 | |

| | gtid_binlog_state | 0-10-40 | |

| | gtid_current_pos | 0-10-40 | |

| | gtid_domain_id | 0 | |

| | gtid_ignore_duplicates | OFF |

| | gtid_slave_pos |

| | gtid_strict_mode | ON |

+-+ +

3.B Host operation (replicated as backup)

(1) Edit configuration file

[root@host4 ~] # vim / etc/my.cnf.d/server.cnf

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip_name_resolve=ON

Relay_log=mysql-relaylog

Relay_log_index=mysql-relaylog

Relay_log_purge=OFF

Slow_query_log=ON

Server-id=20

Innodb_file_per_table=ON

Binlog_format=ROW

Log_bin=mysql-binlog

Log_slave_updates=ON

Gtid_strict_mode=ON

(2) View the gtid_slave_pos location of host An at the time of backup

[root@host4 ~] # cat / tmp/hello.sql

(3) Log in to MySQL and create an account for master-slave replication

[root@host4 ~] # systemctl start mariadb.service

[root@host4 ~] # mysql

MariaDB [(none)] > grant replication slave on *. * to 'bak'@'172.16.10.%' identified by' bakpass'

MariaDB [(none)] > flush privileges

(4) restore and synchronize the database by backup

MariaDB [(none)] > source / tmp/hellodb.sql

MariaDB [(none)] > SET GLOBAL gtid_slave_pos ='0-10-38'

MariaDB [(none)] > CHANGE MASTER TO master_host='172.16.10.30', master_port=3306, master_user='bak', master_password='bakpass',master_use_gtid=slave_pos

MariaDB [(none)] > start slave

(5) Verification effect

MariaDB [hellodb] > show global variables like 'gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_binlog_pos | 0-10-40 | |

| | gtid_binlog_state | 0-20-37 charge 0-10-40 |

| | gtid_current_pos | 0-10-40 | |

| | gtid_domain_id | 0 | |

| | gtid_ignore_duplicates | OFF |

| | gtid_slave_pos | 0-10-40 | |

| | gtid_strict_mode | ON |

+-+ +

You can see that gtid_binlog_pos has been consistent with host A. Before importing the database and creating replication accounts, there are two values for gtid_binlog_state on host B. it is officially recommended to enable the gtid_strict_mode option or temporarily disable sql_log_bin.

4. Set up the C server (synchronize as a new server)

(1) Edit the configuration file (the server can only be used as a Slave role binlog)

[root@host5 ~] # vim / etc/my.cnf.d/server.cnf

[mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

Skip_name_resolve=ON

Relay_log=mysql-relaylog

Relay_log_index=mysql-relaylog

Relay_log_purge=OFF

Slow_query_log=ON

Server-id=20

Innodb_file_per_table=ON

Binlog_format=ROW

Log_bin=mysql-binlog

Log_slave_updates=ON

Gtid_strict_mode=ON

(2) start and enter MySQL

[root@host5 ~] # systemctl start mariadb.service

[root@host5 ~] # mysql

(3) synchronous recovery of database with empty server

MariaDB [(none)] > SET GLOBAL gtid_slave_pos = ""

MariaDB [(none)] > CHANGE MASTER TO master_host='172.16.10.30', master_port=3306, master_user='bak', master_password='bakpass',master_use_gtid=current_pos

MariaDB [(none)] > start slave

(4) Verification effect

MariaDB [(none)] > show global variables like 'gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_binlog_pos | 0-10-40 | |

| | gtid_binlog_state | 0-10-40 | |

| | gtid_current_pos | 0-10-40 | |

| | gtid_domain_id | 0 | |

| | gtid_ignore_duplicates | OFF |

| | gtid_slave_pos | 0-10-40 | |

| | gtid_strict_mode | ON |

+-+ +

= the above completed master-slave environment construction =

5. Stop the MySQL service of host An and simulate the failure

[root@host3 ~] # systemctl stop mariadb.service

6. At this point, promote host B to host Master, and do the operation.

MariaDB [hellodb] > stop slave

MariaDB [hellodb] > show global variables like 'gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_binlog_pos | 0-10-40 | |

| | gtid_binlog_state | 0-20-37 charge 0-10-40 |

| | gtid_current_pos | 0-10-40 | |

| | gtid_domain_id | 0 | |

| | gtid_ignore_duplicates | OFF |

| | gtid_slave_pos | 0-10-40 | |

| | gtid_strict_mode | ON |

+-+ +

MariaDB [hellodb] > delete from students where stuid=21

MariaDB [hellodb] > show global variables like 'gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_binlog_pos | 0-20-41 |

| | gtid_binlog_state | 0-10-40, 0-20-41 |

| | gtid_current_pos | 0-20-41 |

| | gtid_domain_id | 0 | |

| | gtid_ignore_duplicates | OFF |

| | gtid_slave_pos | 0-10-40 | |

| | gtid_strict_mode | ON |

+-+ +

7. Point the Master of the C host with A to B, and observe the changes

MariaDB [(none)] > show global variables like 'gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_binlog_pos | 0-10-40 | |

| | gtid_binlog_state | 0-10-40 | |

| | gtid_current_pos | 0-10-40 | |

| | gtid_domain_id | 0 | |

| | gtid_ignore_duplicates | OFF |

| | gtid_slave_pos | 0-10-40 | |

| | gtid_strict_mode | ON |

+-+ +

MariaDB [(none)] > stop slave

MariaDB [(none)] > CHANGE MASTER TO master_host='172.16.10.40', master_port=3306, master_user='bak', master_password='bakpass',master_use_gtid=current_pos

MariaDB [(none)] > start slave

MariaDB [(none)] > show global variables like 'gtid%'

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_binlog_pos | 0-20-41 |

| | gtid_binlog_state | 0-10-40, 0-20-41 |

| | gtid_current_pos | 0-20-41 |

| | gtid_domain_id | 0 | |

| | gtid_ignore_duplicates | OFF |

| | gtid_slave_pos | 0-20-41 |

| | gtid_strict_mode | ON |

+-+ +

At this time, the gtid_slave_pos of the C host has changed.

MariaDB [(none)] > show variables like 'gtid_slave_pos'

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_slave_pos | 0-20-41 |

+-+ +

8. Then add host A to the cluster operation as Slave

[root@host3 tmp] # systemctl start mariadb.service

[root@host3 tmp] # mysql

MariaDB [(none)] > CHANGE MASTER TO master_host='172.16.10.40', master_port=3306, master_user='bak', master_password='bakpass',master_use_gtid=current_pos

MariaDB [(none)] > show variables like 'gtid_slave_pos'

+-+ +

| | Variable_name | Value |

+-+ +

| | gtid_slave_pos | 0-20-41 |

+-+ +

So far, all operations have been completed.

Supplementary note:

1.master_use_gtid = {slave_pos | current_pos} it is still not clear which one to use. The documentation says that current_pos is suitable for most environments, so this value is used later.

Semi-synchronous method can be used between 2.Master and Candidate to reduce data inconsistency.

3. One unit using binlog1 and GTID can work normally in dual main mode.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.