MySQL Dual master Interoperability +keeplived High-availability architecture (partial)

One, MySQL dual master mutual Preparation +keeplived high-availability architecture Introduction

MySQL master-slave replication architecture can be a great guarantee of high availability of MySQL, in a master multi-slave architecture can also use read-write separation to allocate reading operations from the library, reducing the main library pressure. However, in this architecture, the main library fails with a manual upgrade from the library to the primary library. In environments where write operations are required, failure of the master library becomes a single point of failure in the master-slave architecture. Therefore, the primary master is required to prepare the architecture to avoid the failure of the primary node and write operation.

In a dual-master architecture, each MySQL acts as the primary server and acts as the slave server for each other. Write operations on any one server are replicated to another server, guaranteeing the reliability of the data.

A virtual IP (VIP) is bound on one of the machines, with keeplived on the basis of a dual-master interaction. The use of VIP unified external services, you can avoid the simultaneous writing of data at two nodes cause conflict. At the same time, when the keeplived master node fails, keeplived automatically switches the VIP to the standby node, which makes the primary server highly available.

MySQL Dual master Interoperability +keeplived High-availability architecture diagram

Second, MySQL dual-Master Interoperability Architecture Deployment

The MySQL master-slave architecture was deployed in the previous MySQL master-slave copy operation. On this basis, you need to open relay-log on the original master, on the original slave server to open Log-bin, while the Mysql248 on the designated Mysql249 for their own primary server and open slave.

1. Open the Relay-log on the original master and specify the libraries that are not copied

12345678910

[[email protected] mysql-5.6.30]# grep -A8 ‘mysqld‘ my.cnf [mysqld]port = 3306server_id = 1log-bin=mysql-bin#以下部分为在原基础上新增的内容relay-log = mysql-relay-binreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%

2. Open the Log-bin on the original slave and specify the libraries that are not copied

[Email protected] mysql-5.6.30]# grep-a8 ' mysqld ' my.cnf

123456789

[mysqld]port = 3306server_id = 2relay-log = mysql-relay-bin#以下部分为在原基础上新增的内容log-bin=mysql-binreplicate-wild-ignore-table=mysql.%replicate-wild-ignore-table=test.%replicate-wild-ignore-table=information_schema.%

3. Specify Mysql249 as your own home server on the Mysql248 server and turn on slave

1234567891011121314151617181920212223

####  ① 在Mysql249上查看当前master信息，并建立复制用户mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000001 |      493 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+1 row inset(0.00 sec)mysql> grant replication slave on *.* to ‘repl_user‘@‘192.168.175.%‘identified by‘123456‘;Query OK, 0 rows affected (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)#### ② 在Mysql248上指定Mysql249为自己的主服务器，开启slavemysql> change master to \    -> master_host=‘192.168.175.249‘,    -> master_user=‘repl_user‘,    -> master_password=‘123456‘,    -> master_log_file=‘mysql-bin.000001‘,    -> master_log_pos=493;Query OK, 0 rows affected, 2 warnings (0.02 sec)mysql> start slave;Query OK, 0 rows affected (0.03 sec)

4. Check the dual master mutual preparation

① uses show slave status\g on both servers to query the main library information as well as the IO process, SQL process working status. If the query results for both servers are Slave_io_running:yes,slave_sql_running:yes, then the current dual-master interoperability status is normal.

② build a table on the Mysql248 database, check if the Mysql249 is in sync, and build a table on the Mysql249 to check if the Mysql248 is in sync.

MySQL Dual master Interoperability +keeplived High-availability architecture (partial)