Dual-machine high-availability, load-balancing, MySQL (read-write separation, master-slave automatic switching) architecture design

A few days ago, the user wrote to help achieve such a framework: only two machines, the need to implement one of the crash after another can take over the service of this machine, and two machines in normal service, both machines can be used. The following architecture was designed.

Introduction to Architecture

This architecture is mainly by the keepalived realize the dual machine high availability, maintains an outside network VIP, an intranet VIP. Normal situation, the external network VIP and the intranet VIP are bound in the Server1 server, the Web request sent to the Server1 Nginx,nginx for the static resource request directly in the native retrieval and return, for PHP dynamic request, The load is balanced to server1 and Server2. For SQL requests, such requests are sent to the Atlas MySQL middleware, and after the request is received by Atlas, the requests involving the write operation are sent to the intranet VIP, and the read request operation is sent to the Server2, which realizes the read and write separation.

When the primary server server1 down, keepalived detected, immediately the network VIP and the intranet VIP bound to Server2, and the server2 of MySQL switch to the main library. At this time because the outside VIP has been transferred to the Server2,web request will be sent to Server2 Nginx. Nginx detects a server1 outage and no longer forwards the request to the Server1 php-fpm. After the SQL request as usual sent to the local Atlas,atlas write operation sent to the intranet VIP, read operation sent to Server2 MySQL, because the intranet VIP has been bound to Server2, server2 MySQL simultaneously accept write operation and read operation.

When the primary server Server1 recovery, keepalived does not preempt Server2 VIP, continue to normal service. We can switch the Server1 MySQL to the master, or we can change it from.

Architecture Requirements

To implement this architecture, you need three conditions:

1. The server can set the intranet IP, and set the intranet IP interoperability;
2. The server can be arbitrarily bound IDC assigned to our use of the external network IP, that is, the external network IP is not bound to MAC address;
3. MySQL server supports Gtid, which is MySQL-5.6.5 or later.

Environment description

Server1

eth0:10.96.153.110 (external IP) eth1:192.168.3.100 (internal IP)

Server2

eth0:10.96.153.114 (external IP) eth1:192.168.3.101 (internal IP)

The system is CentOS-6.

External vip:10.96.153.239 Internal vip:192.168.3.150

Hosts settings

/etc/hosts:192.168.3.100 server1192.168.3.101 Server2

Nginx PHP MySQL Installation

The installation of these software is recommended to be done using ezhttp.

Nginx Configuration

Server1 Configuration

HTTP {[...]    Upstream Php-server {           server 192.168.3.101:9000;           Server 127.0.0.1:9000;           keepalive;    } [...] server {    [...]        Location ~ \.php$ {                        fastcgi_pass   php-server;                        Fastcgi_index  index.php;                        Fastcgi_param  script_filename  $document _root$fastcgi_script_name;                        Include        fastcgi_params;        }    [...] }[...]}

Server2 Configuration

HTTP {[...]    Upstream Php-server {           server 192.168.3.100:9000;           Server 127.0.0.1:9000;           keepalive;    } [...] server {    [...]        Location ~ \.php$ {                        fastcgi_pass   php-server;                        Fastcgi_index  index.php;                        Fastcgi_param  script_filename  $document _root$fastcgi_script_name;                        Include        fastcgi_params;        }    [...] }[...]}

The primary purpose of these two configurations is to set load balancing for PHP requests.

MySQL Configuration

MySQL util installation

We need to install the master-slave configuration tool in MySQL util to achieve master-slave switching.

Cd/tmpwget http://dev.mysql.com/get/Downloads/MySQLGUITools/mysql-utilities-1.5.3.  Tar.gztar xzf mysql-utilities-1.5.3.tar.gzcd mysql-utilities-1.5.3python setup.py buildpython setup.py Install 

MySQL MY.CNF configuration

Server1:

[MySQL] [...] Protocol=tcp[...] [...] [Mysqld] [...] # BINARY LOGGING #log-bin =/usr/local/mysql/data/mysql-binexpire-logs-days =Sync-binlog = 1binlog-format= Rowlog-slave-updates=truegtid-mode=onenforce-gtid-consistency =truemaster-info-repository= tablerelay-log-info-repository=tablesync-master-info=1server-id=1report-host=server1report-port=3306[...]  

Server2:

[MySQL] [...] Protocol=tcp[...] [Mysqld] [...] # BINARY LOGGING #log-bin =/usr/local/mysql/data/mysql-binexpire-logs-days = 14sync-binlog = 1binlog-format= Rowlog-slave-updates=truegtid-mode=onenforce-gtid-consistency =truemaster-info-repository= Tablerelay-log-info-repository=tablesync-master-info=1server-id=2report-host=server2report-port=3306[...]

These two configurations are primarily set up with Binlog and enable Gtid-mode, and you need to set different Server-id and Report-host.

Open root Account Remote permissions:

We need to set the root account remote access permissions on both MySQL servers.

Mysql> Grant All on * * to ' root ' @ ' 192.168.3.% ' identified by ' xp29at5f37 ' with Grant option;mysql> grant all on * * To ' root ' @ ' server1 ' identified by ' xp29at5f37 ' and Grant option;mysql> grant all on * * to ' root ' @ ' Server2 ' identifie D by ' xp29at5f37 ' with grant option;mysql> flush privileges;

Set MySQL master/slave

Execute the following command on any one:

Mysqlreplicate--master=root:[email protected]:3306--slave=root:[email protected]:3306--rpl-user=rpl:o67DhtaW# Master on Server1: ... connected.# slave on server2: ... connected.# Checking for binary logging on master...# Setting up replication...# ... done.

Show Master-Slave relationships

Mysqlrplshow--master=root:[email protected]--discover-slaves-login=root:xp29at5f37# master on Server1: ... connected.# finding slaves for master:server1:3306# Replication topology graphserver1:3306 (master) |+---server2:3306-( SLAVE)

Check master and slave status

Mysqlrplcheck--master=root:[email protected]--slave=root:[email protected]# Master on Server1: ... connected.# slave on Server2: Connected.  file [pass]checking InnoDB compatibility [pass]checking storage engines compatibility [pass]checking lower_case _table_names settings [pass]checking slave delay (seconds behind master) [pass]# ... done.

Creating a master-slave switch script in Server2

Vi/data/sh/mysqlfailover.sh#!/bin/bashmysqlrpladmin--slave=root:[email protected]:3306 failoverchmod +x/data/ Sh/mysqlfailover.sh

keepalived Configuration

Keepalived installation (two PCs)

Yum-y Install keepalivedchkconfig keepalived on

Keepalived Configuration (SERVER1)

Vi/etc/keepalived/keepalived.confvrrp_sync_group vg_1 {group {inside_networkoutside_network}} vrrp_instance Inside_ Network {State backupinterface eth1virtual_router_id 51priority 101advert_int 1authentication {auth_type Passauth_pass 3489}virtual_ipaddress {192.168.3.150/24}nopreempt} vrrp_instance outside_network {State BACKUPinterface eth0virtual _router_id 50priority 101advert_int 1authentication {auth_type passauth_pass 3489}virtual_ipaddress {10.96.153.239/24 }nopreempt}

Keepalived Configuration (Server2)

Vrrp_sync_group vg_1 {group {inside_networkoutside_network}} vrrp_instance inside_network {State BACKUPinterface eth1virtual_router_id 51priority 100advert_int 1authentication {auth_type passauth_pass 3489}virtual_ipaddress { 192.168.3.150}notify_master/data/sh/mysqlfailover.sh} vrrp_instance outside_network {State BACKUPinterface eth0virtual_router_id 50priority 100advert_int 1authentication {auth_type passauth_pass 3489}virtual_ipaddress { 10.96.153.239/24}}

This keepalived configuration needs to be noted:

1. The state of both servers is set to Backup,server1 to increase the nopreempt configuration, and the Server1 priority is higher than server2, which is used to realize that when Server1 resumes from the outage, it does not preempt the VIP;
2. Server2 setting notify_master/data/sh/mysqlfailover.sh means that Server2 takes over Server1 and executes the script to promote the Server2 of MySQL.

Atlas Settings

Atlas Installation

Download the latest version here, https://github.com/Qihoo360/Atlas/releases

Cd/tmpwget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64. rpmrpm-i atlas-2.2.1.el6.x86_64.rpm

Atlas Configuration

cd/usr/local/mysql-proxy/confcp test.cnf MY.CNFVI my.cnf

Adjust the following parameters,

proxy-backend-addresses = 192.168.3.150:3306proxy-read-only-backend-addresses = 192.168.3.101:3306pwds = root: Qtyu1btxoo074itvx0ur9q==event-threads = 8

Attention:

proxy-backend-addresseSet as intranet VIP
proxy-read-only-backend-addressesIP set to Server2
root:qtyU1btXOo074Itvx0UR9Q==Set the user and password for the database, which is generated by the password /usr/local/mysql-proxy/bin/encrypt Xp29at5F37 . For more detailed explanation of the parameters, please review the Atlas configuration.

Launch Atlas

/usr/local/mysql-proxy/bin/mysql-proxy--defaults-file=/usr/local/mysql-proxy/conf/my.cnf

Then configure MySQL in the program to configure the 127.0.0.1:1234 just fine.

Server1 Main outage Test

To test if the keepalived is working properly, let's simulate server1 downtime. Executes the shutdown shutdown command on the Server1. At this point we log in Server2, execute the IP addr command, the output is as follows:

1:LO:MTU 16436 qdisc noqueue State unknownlink/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope ho St Loinet6:: 1/128 scope Hostvalid_lft Forever preferred_lft forever2:eth0:mtu, Qdisc pfifo_fast State up Qlen 1000l Ink/ether 00:0c:29:81:9d:42 BRD ff:ff:ff:ff:ff:ffinet 10.96.153.114/24 BRD 10.96.153.255 Scope Global Eth0inet 10.96.153.239/24 Scope Global Secondary ETH0INET6 FE80::20C:29FF:FE81:9D42/64 scope Linkvalid_lft Forever Preferred_lft FOREVER3:ETH1:MTU Qdisc pfifo_fast State up Qlen 1000link/ether 00:0c:29:81:9d:4c BRD ff:ff:ff:ff:ff:ffinet 192.168 .3.101/24 BRD 192.168.3.255 Scope Global eth1inet 192.168.3.150/32 scope global Eth1inet6 FE80::20C:29FF:FE81:9D4C/64 SCO PE Linkvalid_lft Forever Preferred_lft Forever We see the external VIP 10.96.153.239 and internal IP 192.168.3.150 has shifted to Server2, Prove that the keepalived is operating normally.

Test whether to automatically switch the master and slave, log in to the Server2 MySQL server, perform the show Status command, as follows:

Set (0.00 sec)

We found that from the state has been empty, the proof has been switched to the main.

Test server1 whether to preempt VIP, why test this? If the VIP is preempted after the Server1 recovery, and our backend in Atlas is set to VIP, the SQL write will be sent to Server1 MySQL after Server1 startup, and the Server1 MySQL data is older than Server2. So this can result in inconsistent data, which is a very important test.

Let's start with the Server1, then execute the IP addr, and the output is as follows:

1:LO:MTU 16436 qdisc noqueue State unknownlink/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope ho St Loinet6:: 1/128 scope Hostvalid_lft Forever preferred_lft forever2:eth0:mtu, Qdisc pfifo_fast State up Qlen 1000l Ink/ether 00:0c:29:f1:4f:4e BRD ff:ff:ff:ff:ff:ffinet 10.96.153.110/24 BRD 10.96.153.255 Scope Global ETH0INET6 FE80: : 20C:29FF:FEF1:4F4E/64 scope Linkvalid_lft Forever preferred_lft forever3:eth1:mtu qdisc pfifo_fast State up Qlen 1 000link/ether 00:0c:29:f1:4f:58 BRD ff:ff:ff:ff:ff:ffinet 192.168.3.100/24 BRD 192.168.3.255 scope Global Eth1inet6 FE80 :: 20C:29FF:FEF1:4F58/64 scope Linkvalid_lft Forever Preferred_lft Forever

We see that Server1 did not preempt the VIP, testing normal. But the other depressed is that the virtual machine environment has not been tested successfully, do not know why.

How to recover Server1

Set server1 MySQL to from, Server1 after recovering from the outage, MySQL data is already old in server2 data, then we first set up Server1 MySQL from.

Mysqlreplicate--master=root:[email protected]:3306--slave=root:[email protected]:3306--rpl-user=rpl:o67DhtaW# Master on Server2: ... connected.# slave on server1: ... connected.# Checking for binary logging on master...# Setting up replication...# ... done.

You see the prompt is set up successfully.

Get Server1 MySQL Data data synchronization situation, server1 MySQL just recover from the outage, it is possible that the data is far behind the server2 MySQL, so we first look at the data synchronization between them. Log in to Server1 MySQL and execute the following sql:

Yesslave_sql_running:yes

We note that the value of Read_master_log_pos is 2894, log in to Server2 MySQL, and execute the following sql:

Mysql> Show Master statusg*************************** 1. Row ***************************file:mysql-bin.000004position:2894binlog_do_db:binlog_ignore_db:executed_gtid_ Set:9347e042-9044-11e4-b4f0-000c29f14f4e:1-7,f5bbfc15-904a-11e4-b519-000c29819d42:1-61 row in Set (0.00 sec)

Note the value of position, and compare with Read_master_log_pos, if the two values are very similar or equal, the data has been synchronized almost, you can switch operations, if the difference is very far, you need to wait for them to complete synchronization.

Masking MySQL Write operations

We need to disable the SQL write operation when switching, and if not, it will cause inconsistent data when switching. Masking write operations we operate on Atlas. To perform a login in Server2 the Atlas command:

Type |+-------------+--------------------+-------+------+| 1 | 192.168.3.150:3306 | Up | RW | | 2 | 192.168.3.101:3306 | Up | Ro |+-------------+--------------------+-------+------+2 rows in Set (0.00 sec)

Execute select * from backends; we see the backend ID is 1, so we execute set OFFLINE 1;

mysql> SET OFFLINE 1;+-------------+--------------------+---------+------+| Backend_ndx | Address | State | Type |+-------------+--------------------+---------+------+| 1 | 192.168.3.150:3306 | Offline | RW |+-------------+--------------------+---------+------+1 row in Set (0.00 sec)

Mysql> SELECT * from backends;+-------------+--------------------+---------+------+| Backend_ndx | Address | State | Type |+-------------+--------------------+---------+------+| 1 | 192.168.3.150:3306 | Offline | RW | | 2 | 192.168.3.101:3306 | Up | Ro |+-------------+--------------------+---------+------+2 rows in Set (0.00 sec)

The client cannot write to the data at this time.

Recovery Server1 MySQL Main

At server2:3306# discovering slave at server1:3306# Found slave:server1:3306# Checking privileges.# performing switchover From master at server2:3306 to slave at server1:3306.# Checking candidate slave prerequisites.# Checking slaves Configura  tion to master.# waiting for slaves-catch up-to-old master.# stopping slaves.# performing STOP on all slaves.# demoting Old Master to is a slave to the new master.# switching slaves to new master.# starting all slaves.# performing START on a ll slaves.# Checking slaves for errors.# switchover complete.

Check again to see if the recovery was successful.

Mysqlrplcheck--master=root:[email protected]--slave=root:[email protected]# Master on Server1: ... connected.# slave on Server2: Connected. Test Description Status---------------------------------------------------------------------------Checking for Binary logging on master [pass]are there binlog exceptions? [Pass] Replication user exists? [Pass] Checking server_id values [pass]checking server_uuid values [pass]is slave connected to master? [Pass] Check Master information file [pass]checking InnoDB compatibility [pass]checking storage engines compatibility [PASS]CHEC King lower_case_table_names Settings [pass]checking slave delay (seconds behind master) [pass]# ... done.

Set the VIP back to Server1 and execute on the Server2 machine:

/etc/init.d/keepalived Restart

Then perform IP addr on both machines to view the IP binding status.

Set up Server2 Atlas backend on-line

Log on on Server2 mysql -h127.0.0.1 -P2345 -uuser -ppwd , and then execute settings on- SET ONLINE 1; line (here 1 is the ID of the backend, can be used to SELECT * FROM backends; view)

mysql> SET ONLINE 1;
+-------------+--------------------+---------+------+
| Backend_ndx | Address | State | Type |
+-------------+--------------------+---------+------+
| 1 | 192.168.3.150:3306 | Unknown | RW |
+-------------+--------------------+---------+------+
1 row in Set (0.00 sec)

Mysql> SELECT * from backends;+-------------+--------------------+-------+------+| Backend_ndx | Address | State | Type |+-------------+--------------------+-------+------+| 1 | 192.168.3.150:3306 | Up | RW | | 2 | 192.168.3.101:3306 | Up | Ro |+-------------+--------------------+-------+------+2 rows in Set (0.00 sec)

Here server1 is restored to the main.

Dual-machine high-availability, load-balancing, MySQL (read-write separation, master-slave automatic switching) architecture design