MySQL main master synchronization mechanism +keepalived to achieve MySQL high availability

I. Overview

Mysql-ha currently has a lot of implementations, today with the MySQL double master+keepalived to achieve mysql-ha.

in the mysql-ha environment, MySQL is the main slave relationship, which guarantees the consistency of the two MySQL data.

then use keepalived to implement virtual IP, through keepalived to realize the MySQL fault automatically switch.

 

Second, the experimental environment

virtual machine	operating system	vcpu/", "	memory/g	HDD/g	virtual IP
1	1
Mysql02	192.168.1.101	Ubuntu14.04	1	1	20

This test reference document: http://blog.csdn.net/ajaxuser/article/details/8453342

Third, mysql-server-5.6 installation

The installation process is omitted

Iv. Configuring MySQL Dual Master Architecture

4.1, in on two servers Create an account for "replication" at the same time

Mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT on *. * to [e-mail protected] ' 192.168.1.% ' identified by ' 1qaz#edc ';

650) this.width=650; "src=" Https://note.youdao.com/yws/res/1215/ECAA5272E13E48B395A24C154690D16D "style=" width : 554px; "alt=" ecaa5272e13e48b395a24c154690d16d "/>

Description: Copy account: copy_user Password: 1QAZ#EDC

Note: The IP address needs to be created on both servers, please change to the actual IP address

4.2. Change the configuration file

4.2.1, mysql01 server configuration

[Email protected]:~# vim/etc/mysql/my.cnf

What you need to modify is as follows:

bind-address = 0.0.0.0 #设为0.0.0.0, allows any IP connection.

Server-id = 1

Log_bin =/var/log/mysql/mysql-bin.log

Relay_log =/var/log/mysql/mysql-relay-bin

Log_slave_updates = 1

650) this.width=650; "src=" Https://note.youdao.com/yws/res/1218/430FF09A4AE448D5B03874603BCD5D3F "style=" width : 477px; "alt=" 430ff09a4ae448d5b03874603bcd5d3f "/>

4.2.2, mysql02 server configuration

[Email protected]:~# vim/etc/mysql/my.cnf

What you need to modify is as follows:

bind-address = 0.0.0.0 #设为0.0.0.0, allows any IP connection.

Server-id = 2

Log_bin =/var/log/mysql/mysql-bin.log

Relay_log =/var/log/mysql/mysql-relay-bin

Log_slave_updates = 1

650) this.width=650; "src=" Https://note.youdao.com/yws/res/1220/9D99436258784B59807C73F56FFCD6F3 "style=" width : 504px; "alt=" 9d99436258784b59807c73f56ffcd6f3 "/>

4.3, two servers to restart the MySQL service

/etc/init.d/mysql restart

650) this.width=650; "src=" Https://note.youdao.com/yws/res/1219/FA45E47F39E04124A28104BF02DFAF0B "style=" width : 554px; "alt=" fa45e47f39e04124a28104bf02dfaf0b "/>

4.4. Start replication on two servers separately

4.4.1, MYSQL01 Configuration

mysql> Change Master to master_host= ' 192.168.1.101 ', master_user= ' copy_user ', master_password= ' 1qaz#edc ';

650) this.width=650; "src=" Https://note.youdao.com/yws/res/1217/4F55A82F0B3A465D8439499D0EF49F6C "style=" width : 554px; "alt=" 4f55a82f0b3a465d8439499d0ef49f6c "/>

4.4.2, MYSQL02 Configuration

mysql> Change Master to master_host= ' 192.168.1.100 ', master_user= ' copy_user ', master_password= ' 1qaz#edc ';

650) this.width=650; "src=" Https://note.youdao.com/yws/res/1213/7A4A804634C34AD19145C63A8BF3DF4E "style=" width : 554px; "alt=" 7a4a804634c34ad19145c63a8bf3df4e "/>

4.5.1, mysql01 start from replication

mysql> start Slave

4.5.2, mysql02 start from replication

mysql> start Slave

4.6, check the status on both servers, yes indicates the configuration is successful

650) this.width=650; "src=" Https://note.youdao.com/yws/res/1209/525D626ED4DB459A84B175BA3AF4A56D "style=" width : 554px; "alt=" 525d626ed4db459a84b175ba3af4a56d "/>

now any one MySQL data changes, another one Database data updates are also followed.

V. Configuration keepalived

5.1. Install keepalived on two servers respectively

5.1.1, MYSQL01 installation

650) this.width=650; "src=" https://note.youdao.com/yws/res/1216/B6956092C849479B9FBC455DAB7F3824 "style=" width : 554px; "alt=" b6956092c849479b9fbc455dab7f3824 "/>

5.1.2, MYSQL02 installation

650) this.width=650; "src=" Https://note.youdao.com/yws/res/1214/75A01D2CFCD0498C862E341AD9DB15FF "style=" width : 554px; "alt=" 75a01d2cfcd0498c862e341ad9db15ff "/>

5.2. Start configuring the keepalived.conf configuration file

Note 1: The configuration file is in the/etc/keepalived directory.

Note The point 2:/etc/keepalived directory is automatically created when you install keepalived

Note that the 3:keepalived.conf configuration file does not exist by default, you need to create it yourself.

5.2.1, creating and configuring Keepalived.conf on the MYSQL01 host

[Email protected]:/etc/keepalived# vim keepalived.conf

650) this.width=650; "src=" Https://note.youdao.com/yws/res/1211/D9113F1613B144498822A2CB397B5D8D "style=" width : 554px; "alt=" d9113f1613b144498822a2cb397b5d8d "/>

5.2.2, creating and configuring Keepalived.conf on the MYSQL02 host

This configuration is basically the same as above, but there are two different places: Priority 90, real_server for native IP

650) this.width=650; "src=" Https://note.youdao.com/yws/res/1212/07114BE0B96C4BACAE911EB57EDC799E "style=" width : 553px; "alt=" 07114be0b96c4bacae911eb57edc799e "/>

5.3, respectively, in the/data/directory of two servers to write the detection service down after the script to be executed mysql.sh

5.3.1, MYSQL01 Configuration

[Email protected]:~ #mkdir/data

[Email protected]:~ #vim mysql.sh

[Email protected]:~ #chmod 777 mysql.sh

650) this.width=650; "src=" Https://note.youdao.com/yws/res/1221/4ED58F1A4E564BD9850BE51DFD782EB8 "style=" width : 409px; "alt=" 4ed58f1a4e564bd9850be51dfd782eb8 "/>

5.3.2, MYSQL02 Configuration

[Email protected]:~ #mkdir/data

[Email protected]:~ #vim mysql.sh

[Email protected]:~ #chmod 777 mysql.sh

650) this.width=650; "src=" Https://note.youdao.com/yws/res/1221/4ED58F1A4E564BD9850BE51DFD782EB8 "style=" width : 409px; "alt=" 4ed58f1a4e564bd9850be51dfd782eb8 "/>

5.3.3, mysql.sh script description

This script is used by the configuration file Notify_down option above, Keepalived uses the Notify_down option to check the service status of Real_server and fires this script when a Real_server service failure is found The Pkill keepalived force kills the keepalived process, thus realizing the automatic transfer of MySQL fault. In addition, we do not have to worry about two MySQL will also provide data update operation, because each MySQL on the keepalived configuration only native MySQL Ip+vip, not two MySQL ip+vip.

5.4. Start keepalived on two hosts respectively

5.4.1, MYSQL01 Host

[email protected]:~#keepalived-d or sudo keepalived start

5.4.2, MYSQL02 Host

[email protected]:~#keepalived-d or sudo keepalived start

5.5, conducting tests

5.5.1, Ping the virtual IP onthe physical machine

650) this.width=650; "src=" https://note.youdao.com/yws/res/1207/6BF237B71903485A89AFAC353D864088 "style=" width : 554px; "alt=" 6bf237b71903485a89afac353d864088 "/>

5.5.2, use the top command on the MYSQL01 host to view related processes and resource usage

650) this.width=650; "src=" Https://note.youdao.com/yws/res/1208/757A89E4984E4185A06C6963AAC9650D "style=" width : 554px; "alt=" 757a89e4984e4185a06c6963aac9650d "/>

5.5.3, use the top command on the MYSQL02 host to view related processes and resource usage

650) this.width=650; "src=" https://note.youdao.com/yws/res/1210/C94C21DD031B40868A792BFAFE0BE201 "style=" width : 553px; "alt=" c94c21dd031b40868a792bfafe0be201 "/>

5.6. Talk about the test

client connects MySQL(or ping 192.168.1.102) with VIP (192.168.1.102)

1. Two services are working properly, on two servers with netstat-nat view,

you can see that the current connection is 192.168.1.100, because it has a high priority. This is the active server and101 is the standby server.

2. when the MySQL service is down,keepalived can complete failover,

all new MySQL connections will then connect to 192.168.1.101.

Similarly, when 101 's mysql service is down, all new MySQL connections will connect to 192.168.1.100.

3. after the repair, because the weight is relatively high,will become an Active server,101 will be used as a standby server.

of course, you can set the Nopreempt parameter on the ( higher priority server ) , set this server to not preempt,

This ensures that 101 continues to act as an active server after the repair of the service.

The keepalived.conf file is in the attachment.

This article is from "My Ops and my original" blog, so be sure to keep this source http://trtan.blog.51cto.com/8272891/1863591

MySQL main master synchronization mechanism +keepalived to achieve MySQL high availability