Using Keepalived to build dual-master MySQL

Using MySQL dual master + keepalived is a very good solution. In the MySQL-HA environment, MySQL is mutually master-slave, which ensures data consistency between the two MySQL databases.

Using MySQL dual master + keepalived is a very good solution. In the MySQL-HA environment, MySQL is mutually master-slave, which ensures data consistency between the two MySQL databases.

Using MySQL dual master + keepalived is a very good solution. In the MySQL-HA environment, MySQL is mutually master-slave, which ensures data consistency between the two MySQL databases, then, use keepalived to implement virtual IP addresses, and use the service monitoring function provided by keepalived to automatically switch MySQL during failure.

Next, I will share with you the architecture in a production environment to be launched. Let's take a look at how MySQL-HA is implemented in this architecture. The environment topology is as follows:

MySQL-VIP: 10.10.10.21

MySQL-master1: 10.10.10.17

MySQL-master2: 10.10.10.18

OS Version: RedHat6.2

MySQL version: mysql-5.1.59

Keepalived version: keepalived-1.1.20

1. MySQL master-master Configuration

1. Modify the MySQL configuration file

To enable the binlog function for both MySQL instances, enable the function by adding log-bin = MySQL-bin in the [MySQLd] section of the MySQL configuration file.

The server-ID of the two MySQL instances cannot be the same. By default, the serverID of the two MySQL instances is 1 and you need to change one of them to 2.

Master1 Configuration:

# Vim/etc/my. cnf

Log-bin = mysql-bin // enable the binlog Function

Log =/usr/local/mysql/var/mysql. log // prints mysql SQL statements.

Server-id = 1 //

Binlog-do-db = mysql // name of the database to be synchronized

Auto-increment = 2

Auto-increment-offset = 2

Master2 Configuration:

# Vim/etc/my. cnf

Log-bin = mysql-bin // enable the binlog Function

Log =/usr/local/mysql/var/mysql. log // prints mysql SQL statements.

Server-id = 2

Binlog-do-db = mysql // name of the database to be synchronized

Auto-increment = 2

Auto-increment-offset = 2

2. Create an authorized user

Create an authorized user on 10.10.10.17

Grant replicationslave on *. * to test @ '10. 10.10.% 'identified by '123 ';

Create an authorized user on the 10.10.10.18 Server

Grant replicationslave on *. * to test @ '10. 10.10.% 'identified by '123 ';

3. Set 10.10.10.17 to the master server of 10.10.10.18.

Set 10.10.10.17 as your primary server on 10.10.10.18.

Mysql> show master status; (17 server configuration)

1 + ------------------ + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000027 | 106 | mysql |

+ ------------------ + ---------- + -------------- + ------------------ +

1 row in set (0.01 sec)

MySQL> change master to master_host = '10. 10.10.17 ', master_user = 'test', master_password = '000000', master_log_file = 'mysql-bin.000027', master_log_pos = 123456;

Query OK, 0 rows affected (0.05 sec)

MySQL> start slave;

Query OK, 0 rows affected (0.00 sec)

Mysql> show slave status \ G

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes \ if both items are yes, the master-master configuration is successful.

Set 10.10.10.18 to 10.10.10.17.

Set 10.10.10.18 as your primary server on 10.10.10.17.

Mysql> show master status; (18 server configuration)

1 + ------------------ + ---------- + -------------- + ------------------ +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ------------------ + ---------- + -------------- + ------------------ +

| Mysql-bin.000027 | 106 | mysql |

+ ------------------ + ---------- + -------------- + ------------------ +

1 row in set (0.01 sec)

MySQL> change master to master_host = '10. 10.10.18 ', master_user = 'test', master_password = '000000', master_log_file = 'mysql-bin.000027', master_log_pos = 123456;

Query OK, 0 rows affected (0.05 sec)

MySQL> start slave;

Query OK, 0 rows affected (0.00 sec)

Mysql> show slave status \ G

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes \ if all two items are yes, the master-master configuration is successful.

Test successful:

If the above configurations are correct, data update on any MySQL instance will be synchronized to another MySQL instance (mysql database only)