1.1 introduction to MHA

1.1.1 introduction to MHA software

At present, MHA (Master High Availability) is a relatively mature solution in MySQL high availability. It is developed by Japan DeNA company youshimaton (now working for Facebook company). It is an excellent set of high availability software for failover and master-slave improvement in MySQL high availability environment. In the process of MySQL failover, MHA can automatically complete the database failover within 10 ~ 30 seconds, and in the process of failover, MHA can ensure the consistency of data to the greatest extent, so as to achieve high availability in the real sense.

MHA can realize automatic fault detection and failover in a short time, usually within 10-30 seconds; In the replication framework, MHA can well solve the problem of data consistency in the replication process. Because there is no need to add additional servers in the existing replication, only one manager node is needed, and one manager can manage multiple sets of replication, so it can greatly save the number of servers; In addition, it has the advantages of simple installation, asexual loss, and no need to modify the existing replication deployment.

MHA also provides the function of online main library switching, which can safely switch the currently running main library to a new main library (by upgrading the slave library to the main library), which can be completed in about 0.5-2 seconds.

The software consists of two parts: MHA Manager (management node) and MHA Node (data node). MHA manager can be deployed on a separate machine to manage multiple master slave clusters, or on a slave node. The MHA Node runs on each MySQL server. The MHA manager will regularly detect the master node in the cluster. When the master fails, it can automatically promote the slave of the latest data to the new master, and then point all other slave to the new master again. The entire failover process is completely transparent to the application.

In the process of MHA automatic failover, MHA tries to save binary logs from the down main server to ensure no loss of data to the greatest extent, but this is not always feasible. For example, if the primary server hardware fails or cannot be accessed through ssh, MHA cannot save binary logs, only fails over and loses the latest data. Using MySQL 5.5 semi synchronous replication can greatly reduce the risk of data loss.

MHA can be combined with semi synchronous replication. If only one slave has received the latest binary log, MHA can apply the latest binary log to all other slave servers, so it can ensure the data consistency of all nodes.

At present, MHA mainly supports a master-slave architecture. To build MHA, it is required that there must be at least three database servers in a replication cluster, one master and two slaves, that is, one serves as a master, one as a standby master and the other as a slave library. Because at least three servers are required, Taobao has also been transformed on this basis in consideration of machine cost. At present, Taobao TMHA has supported one master and one slave.

1.1.2 working principle of MHA

Description of working principle: 1. Save all binlog events on the master. 2. Find the slave with the latest binlog location. 3. Recover data to other slave through relay log. 4. Promote the slave with the latest binlog location to master. 5. Point other Slave Slave Slave Slave to the original slave 01 of the new master and start master-slave replication. 6. Recover the saved binlog to the new master

1. MHA function description of monitoring all node nodes:

2. Automatic failover

The premise is that there must be three nodes and two slave libraries

(1) the main premise is selected in the order of the configuration file, but if the main database behind this node is more than 100M, the relay log will not be selected

(2) if you set the weight, you will always switch to this node; Generally, in the case of multi location and multi center, the weight is generally set at the local node.

(3) select s1 as the new master

(4) save binlog of main database

3. Rebuild master-slave

(1) remove the problematic nodes from MHA

Carry out the first stage data compensation, and complete the missing part of S2 by 90

(2)s1 switches the role to the new master and points s2 to the new master S1

　　　　 s2 change master to s1

(3) second stage data compensation

Apply the binlog of the saved new master and the missing part of the original master to the new master.

(4) the virtual IP drifts to the new host and is transparent to the application without perception

(5) notify the administrator of failover

1.1.3 MHA high availability architecture diagram

1.1.4 introduction to MHA tools

MHA software consists of two parts, Manager toolkit and Node toolkit. The specific instructions are as follows:

The Manager toolkit mainly includes the following tools:

masterha_check_ssh             #Check the SSH key of MHA^
masterha_check_repl            #Check the master-slave copy
masterha_manger                #Start MHA
masterha_check_status          #Detect the operation status of MHA^
masterha_mast er_monitor       #Check whether the master is down
masterha_mast er_switch        #Manual failover -
masterha_conf_host             #Manually add server double interest
masterha_secondary_check       #Establish a TCP connection from a remote server v
masterha_stop                  #Stop MHA

The Node toolkit mainly includes the following tools:

save_binary_1ogs       #Save binlog of the down master
apply_diff_relay_logs   #Identify differences in relay log s
filter_mysqlbinlog           #To prevent rollback events, MHA no longer uses this tool
purge_relay_logs           #Clearing the relay log will not block the SQL thread

1.1.5 advantages of MHA

1. Automatic failover

2. The main database crashes, and there is no data inconsistency

3. There is no need to make major changes to the current mysql environment

4. No additional servers need to be added

5. Excellent performance, can work with semi synchronous and asynchronous replication frameworks

6. As long as the replication supports the storage engine, mha supports it

1.2 environmental description

In this experiment, three hosts are needed. The system and software are described as follows.

1.2.1 system environment description

db01 host (master)

[root@db01 ~]# cat /etc/redhat-release 
CentOS release 6.9 (Final)
[root@db01 ~]# uname -r
2.6.32-696.el6.x86_64
[root@db01 ~]# /etc/init.d/iptables status
iptables: Firewall is not running.
[root@db01 ~]# getenforce 
Disabled
[root@db01 ~]# hostname -I
10.0.0.51 172.16.1.51

db02 host (slave1)

 1 [root@db02 ~]# cat /etc/redhat-release 
 2 CentOS release 6.9 (Final)
 3 [root@db02 ~]# uname -r
 4 2.6.32-696.el6.x86_64
 5 [root@db02 ~]# /etc/init.d/iptables status
 6 iptables: Firewall is not running.
 7 [root@db02 ~]# getenforce 
 8 Disabled
 9 [root@db02 ~]# hostname -I
10 10.0.0.52 172.16.1.52

db03 host (slave1, MHA managers, Atlas nodes)

 1 [root@db02 ~]# cat /etc/redhat-release 
 2 CentOS release 6.9 (Final)
 3 [root@db02 ~]# uname -r
 4 2.6.32-696.el6.x86_64
 5 [root@db02 ~]# /etc/init.d/iptables status
 6 iptables: Firewall is not running.
 7 [root@db02 ~]# getenforce 
 8 Disabled
 9 [root@db02 ~]# hostname -I
10 10.0.0.52 172.16.1.52

1.2.2 mysql software description

mysql 5.6.36 is newly installed on all three servers:

[root@db01 ~]# mysql --version
mysql  Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using  EditLine wrapper

For specific installation methods of mysql database, please refer to: http://www.cnblogs.com/clsn/p...

1.3 master slave replication configuration based on GTID

1.3.1 prerequisites

　　 🔊 binlog should be enabled for both master and slave databases

　　 🔊 The server ID of master database and slave database must be different

　　 🔊 There should be master-slave replication users

1.3.2 configuring master-slave replication

db01 my.cnf file

[root@db01 ~]# cat /etc/my.cnf
[mysqld]
basedir=/application/mysql
datadir=/application/mysql/data
socket=/tmp/mysql.sock
log-error=/var/log/mysql.log
log-bin=/data/mysql/mysql-bin
binlog_format=row
secure-file-priv=/tmp 
server-id=51
skip-name-resolve  # Skip domain name resolution
gtid-mode=on    # Enable gtid type, otherwise it is a normal replication architecture
enforce-gtid-consistency=true    #Enforce consistency of gtids
log-slave-updates=1     # Whether the slave update is logged (required in 5.6)
relay_log_purge = 0 
[mysql]
socket=/tmp/mysql.sock

db02 my.cnf file

 1 [root@db02 ~]# cat /etc/my.cnf
 2 [mysqld]
 3 basedir=/application/mysql
 4 datadir=/application/mysql/data
 5 socket=/tmp/mysql.sock
 6 log-error=/var/log/mysql.log
 7 log-bin=/data/mysql/mysql-bin
 8 binlog_format=row
 9 secure-file-priv=/tmp 
10 server-id=52
11 skip-name-resolve
12 gtid-mode=on
13 enforce-gtid-consistency=true
14 log-slave-updates=1
15 relay_log_purge = 0 
16 [mysql]
17 socket=/tmp/mysql.sock

db03 my.cnf file

 1 [root@db03 ~]# cat /etc/my.cnf
 2 [mysqld]
 3 basedir=/application/mysql
 4 datadir=/application/mysql/data
 5 socket=/tmp/mysql.sock
 6 log-error=/var/log/mysql.log
 7 log-bin=/data/mysql/mysql-bin
 8 binlog_format=row
 9 secure-file-priv=/tmp 
10 server-id=53
11 skip-name-resolve
12 gtid-mode=on
13 enforce-gtid-consistency=true
14 log-slave-updates=1
15 relay_log_purge = 0 
16 skip-name-resolve
17 [mysql]
18 socket=/tmp/mysql.sock

Create a replication user (51 as the master node, 52 and 53 as the slave)

GRANT REPLICATION SLAVE ON *.* TO repl@'10.0.0.%' IDENTIFIED BY '123';

Open replication from library

change master to 
    master_host='10.0.0.51',
    master_user='repl',
    master_password='123',
    MASTER_AUTO_POSITION=1;

Start copy from library

start slave;

1.3.3 technical description of gtid replication

Introduction to MySQL GTID

The full name of GTID is global transaction identifier, which can be translated into global transaction identifier. GTID is created when the transaction on the original master is committed. GTID needs to be unique in the global active standby topology. GTID consists of two parts:

　GTID = source_id:transaction_id

　　source_id is used to identify the source server, and server is used_ UUID, which is generated at the first startup and written to the configuration file data / auto In CNF

　　transaction_ The ID is determined according to the transaction committed on the source server.

GTID event structure

Structure of GTID in binary log

The life cycle of a GTID * includes:*

1. The transaction is executed on the master database and submitted. A gtid (the uuid of the master database and the minimum transaction serial number not used on the server) is assigned to the transaction, and the gtid is written to the binlog.

2. The standby database reads the gtid in the relaylog and sets the session level gtid_ The value of next to tell the standby database that this value must be used for the next transaction

3. The standby database checks whether the gtid has been used by it and records it in its own binlog. slave needs to guarantee that the previous transaction does not use this gtid, and that the gtid has been read separately at this time, but the uncommitted transaction does not use this gtid

4. Due to gtid_next is not empty. Instead of generating a new gtid, slave uses the gtid obtained from the main library. This ensures that the same transaction gtid in a replication topology remains unchanged. Due to the global uniqueness of gtid, through gtid, we can easily promote the new primary database and the new standby database for some complex replication topologies during automatic switching. For example, we can determine the replication coordinates of the new standby database by pointing to a specific gtid.

GTID is a replication method used to replace the previous classic;

　　　　MySQL5.6.2 support MySQL 5 6.10 post improvement;

Advantages of GTID over traditional replication:

1. A transaction corresponds to a unique ID, and a GTID will only be executed once on a server

2.GTID is used to replace the traditional copy method. The biggest difference between GTID copy and ordinary copy mode is that there is no need to specify the binary file name and location

3. Reduce manual intervention and service failure time. When the host hangs up, use software to promote a standby machine from many standby machines as the host

GTID restrictions:

1. Non transaction engine is not supported

2. Create table is not supported Copy the select statement (the main database reports an error directly)

Principle: (two SQL statements will be generated, one is DDL creation table SQL and the other is insert into data SQL.

Since DDL will lead to automatic submission, this sql requires at least two gtids. However, in GTID mode, only one GTID can be generated for this sql.)

3. One SQL is not allowed to update one transaction engine table and non transaction engine table at the same time

4. It is required that a GTID be enabled or disabled in a GTID group

5. Restart is required to start GTID (except 5.7)

6. After GTID is enabled, the original traditional copy method will no longer be used

7. create temporary table and drop temporary table statements are not supported

8. SQL is not supported_ slave_ skip_ counter

1.3.4 COM_BINLOG_DUMP_GTID

The primary range of identifiers of transactions sent from the slave to the host for execution

　　 Master send all other transactions to slave

The same GTID cannot be executed twice. If there is the same GTID, it will be skip ped automatically.

slave1: send your own UUID1:1 to the master, and then receive UUID1:2 and UUID1:3 event s

slave2: send your own UUID1:1 and UUID1:2 to the master, and then receive the UUID1:3 event

GTID composition

GTID is actually composed of UUID+TID. UUID is the unique identifier of a MySQL instance. TID represents the number of transactions that have been committed on the instance, and increases monotonically with the submission of transactions

GTID = source_id : transaction_id
7E11FA47-31CA-19E1-9E56-C43AA21293967:29

1.3.5 [example 2] MySQL GTID replication configuration

Master node my CNF file

# vi /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
server-id=1
log-bin=mysql-bin
socket=/tmp/mysql.sock
binlog-format=ROW
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1

From node my CNF file

# vi /etc/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql
server-id=2
binlog-format=ROW
gtid-mode=on
enforce-gtid-consistency=true
log-bin=mysql-bin
log_slave_updates = 1
socket=/tmp/mysql.sock

Profile annotation

server-id=x                    # The id numbers of all servers in the same replication topology must be unique
binlog-format=RO               # Binary log format, strongly recommended as ROW
gtid-mode=on                   # Enable gtid type, otherwise it is a normal replication architecture
enforce-gtid-consistency=true  # Enforce consistency of gtids
log-slave-updates=1            # Is slave update logged

Copy user preparation (Master node)

mysql>GRANT REPLICATION SLAVE ON *.* TO rep@'10.0.0.%' IDENTIFIED BY '123';

Enable replication (Slave node)

mysql>start slave;
mysql>show slave status\G

The master-slave replication test can now be carried out.

1.4 deployment of MHA

This deployment of MHA is based on the successful construction of GTID replication. Ordinary master-slave replication can also build MHA architecture.

1.4.1 environmental preparation (all node operations)

Install dependent packages

yum install perl-DBD-MySQL -y

Download mha software, mha official website: https://code.google.com/archi...

github download address: https://github.com/yoshinorim...

*Download package*

mha4mysql-manager-0.56-0.el6.noarch.rpm

mha4mysql-manager-0.56.tar.gz

mha4mysql-node-0.56-0.el6.noarch.rpm

mha4mysql-node-0.56.tar.gz

Install node on all nodes

rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

Create mha administrative user

grant all privileges on *.* to mha@'10.0.0.%' identified by 'mha';

\#Created on the master library, the slave library will be automatically copied (viewed on the slave Library)

Create command soft connection (important)

If the command soft connection is not created, an error will be reported when detecting mha replication

ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /application/mysql/bin/mysql /usr/bin/mysql

1.4.2 deployment management node (MHA manager)

Deploy the management node on mysql-db03

# To install epel source, the software needs
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo 
# Install manager dependency package
yum install -y perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
# Install manager management software
rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm

Create required directory

mkdir -p /etc/mha
mkdir -p /var/log/mha/app1    ---->Can manage multiple sets of master-slave replication

Edit MHA manager * profile*

[root@db03 ~]# cat  /etc/mha/app1.cnf
[server default]                        
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/mysql
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root

[server1]
hostname=10.0.0.51
port=3306

[server2]
hostname=10.0.0.52
port=3306

[server3]
hostname=10.0.0.53
port=3306

[detailed description of configuration file]

 [server default]
 2 #Set the working directory of manager
 3 manager_workdir=/var/log/masterha/app1
 4 #Set the log of manager
 5 manager_log=/var/log/masterha/app1/manager.log 
 6 #Set the location where the master saves binlog so that MHA can find the master's log. Here is the mysql data directory
 7 master_binlog_dir=/data/mysql
 8 #Switch script when setting automatic failover
 9 master_ip_failover_script= /usr/local/bin/master_ip_failover
10 #Set the switching script for manual switching
11 master_ip_online_change_script= /usr/local/bin/master_ip_online_change
12 #Set the password of the root user in mysql, which is the password of the monitoring user created in the previous article
13 password=123456
14 #Set monitoring user root
15 user=root
16 #Set the time interval for monitoring the main database and sending ping packets. When there is no response three times, it will automatically fail over
17 ping_interval=1
18 #Set the save location of binlog when remote mysql switches
19 remote_workdir=/tmp
20 #Set the password of the replication user
21 repl_password=123456
22 #Set the replication user name in the replication environment 
23 repl_user=rep
24 #Set the script of the alarm sent after switching
25 report_script=/usr/local/send_report
26 #Once there is a problem between the monitoring of MHA and server02, MHA Manager will try to log in to server02 from server03
27 secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02 --user=root --master_host=server02 --master_ip=10.0.0.51 --master_port=3306
28 #Set the script to close the fault host after the fault occurs (the main function of this script is to close the host and put it in the event of brain fissure, which is not used here)
29 shutdown_script=""
30 #Set the login user name of ssh
31 ssh_user=root 
32 
33 [server1]
34 hostname=10.0.0.51
35 port=3306
36 
37 [server2]
38 hostname=10.0.0.52
39 port=3306
40 #Set as the candidate master. If this parameter is set, the slave database will be promoted to the master database after the master-slave switch occurs, even if the master database is not the latest slave in the cluster
41 candidate_master=1
42 #By default, if a slave lags behind the master's 100m relay logs, MHA will not select the slave as a new master, because the recovery of the slave takes a long time. Set check_repl_delay=0,MHA triggers the switch. When selecting a new master, the replication delay will be ignored. This parameter is set to candidate_ The host with master = 1 is very useful because the candidate master must be a new master in the process of switching
43 check_repl_delay=0

Configure ssh trust (key distribution, executed on all nodes)

# Generate key
ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
# Distribute the public key, including yourself
for i in 1 2 3 ;do ssh-copy-id -i /root/.ssh/id_dsa.pub root@10.0.0.5$i ;done

After the distribution is completed, test whether the distribution is successful

for i in 1 2 3 ;do ssh 10.0.0.5$i  date ;done
 or
[root@db03 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
 The last line of information is the following words, that is, the distribution is successful:
Thu Dec 28 18:44:53 2017 - [info] All SSH connection tests passed successfully.

1.4.3 start mha

After the above deployment, the mha architecture has been built

# Start mha
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

After successful startup, check the status of the main library

[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf 
app1 (pid:3298) is running(0:PING_OK), master:10.0.0.51

1.4.4 switching master test

Check which main library is now

[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf 
app1 (pid:11669) is running(0:PING_OK), master:10.0.0.51

Manually stop the main library

[root@db01 ~]# /etc/init.d/mysqld stop 
Shutting down MySQL..... SUCCESS!

Check the changes of log information while stopping the data

[root@db03 ~]# tailf /var/log/mha/app1/manager

Fri Dec 29 15:51:14 2017 - [info] All other slaves should start replication from
here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='xxx';

**Repair master-slave**

① Start the original master library,add to change master to information

[root@db01 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS!
mysql> CHANGE MASTER TO MASTER_HOST='10.0.0.52', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';
mysql> start slave;

② View master-slave replication status

mysql> show slave status\G

               Master_Host: 10.0.0.52
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes

**repair mha**

① modify app1.cnf Configuration file, adding back the rejected host

[root@db03 ~]# cat /etc/mha/app1.cnf
[binlog1]
hostname=10.0.0.53
master_binlog_dir=/data/mysql/binlog/
no_master=1

[server default]
manager_log=/var/log/mha/app1/manager
manager_workdir=/var/log/mha/app1
master_binlog_dir=/data/mysql
master_ip_failover_script=/usr/local/bin/master_ip_failover
password=mha
ping_interval=2
repl_password=123
repl_user=repl
ssh_user=root
user=mha

[server1]
hostname=10.0.0.51
port=3306

[server2]
hostname=10.0.0.52
port=3306

[server3]
hostname=10.0.0.53
port=3306

② mha Check replication status

[root@db03 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.

③ start-up mha program

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

**Successfully switched to this main library**

[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:11978) is running(0:PING_OK), master:10.0.0.52

**After the experiment, switch the main library back to db01.**

① stop it mha

[root@db03 ~]# masterha_stop --conf=/etc/mha/app1.cnf
Stopped app1 successfully.

② Stop all slave Libraries slave(All library operations)

stop slave;
reset slave all;

③ Redo master-slave replication(db02,db03)

CHANGE MASTER TO
　　MASTER_HOST='10.0.0.51',
　　MASTER_PORT=3306,
　　MASTER_AUTO_POSITION=1,
　　MASTER_USER='repl',
　　MASTER_PASSWORD='123';

④ start-up slave

start slave;

​         After startup, check whether the slave library is two yes show slave status\G 

⑤ mha Check master-slave replication

[root@db03 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.

⑥ start-up mha

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

　　Check whether the switching is successful

[root@db03 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:12127) is running(0:PING_OK), master:10.0.0.51

​     There is a switch back to this primary node db01

### 1.4.5 setting weights

modify[server1]Weight of

[server1]
hostname=10.0.0.51
port=3306
candidate_master=1
check_repl_delay=0

Configuration description

candidate_master=1 ---- "switch to the host with high priority anyway, which is generally used when the host performance is different
check_repl_delay=0 ---- "no matter how long the data delay is, the candidate database with high priority should be switched there

**Note:**

> 1,Multi place and multi center, set the local node as high weight
>
> 2,In an environment with semi synchronous replication, set the semi synchronous replication node to high weight
>
> 3,Which machine do you think is suitable for the master node with higher configuration and better performance

## 1.5 configure VIP drift

### 1.5.1 two ways of IP drift

 　🐶 adopt keepalived The way to manage virtual IP Drift of

　　 🐶 adopt MHA Built in script mode to manage virtual IP Drift of

### 1.5.2 MHA script mode

**modify mha****configuration file**

[root@db03 ~]# grep "script" /etc/mha/app1.cnf
[server default]
master_ip_failover_script=/usr/local/bin/master_ip_failover

​     Add in the main configuration VIP script

**Script content**

[root@db03 ~]# cat /usr/local/bin/master_ip_failover

!/usr/bin/env perl

use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (

$command,          $ssh_user,        $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip,    $new_master_port

);
my $vip = '10.0.0.55/24';
my $key = '0';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

GetOptions(

'command=s'          => \$command,
'ssh_user=s'         => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s'   => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s'  => \$new_master_host,
'new_master_ip=s'    => \$new_master_ip,
'new_master_port=i'  => \$new_master_port,

);

exit &main();

sub main {

print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";

if ( $command eq "stop" || $command eq "stopssh" ) {

    my $exit_code = 1;
    eval {
        print "Disabling the VIP on old master: $orig_master_host \n";
        &stop_vip();
        $exit_code = 0;
    };
    if ($@) {
        warn "Got Error: $@\n";
        exit $exit_code;
    }
    exit $exit_code;
}
elsif ( $command eq "start" ) {

    my $exit_code = 10;
    eval {
        print "Enabling the VIP - $vip on the new master - $new_master_host \n";
        &start_vip();
        $exit_code = 0;
    };
    if ($@) {
        warn $@;
        exit $exit_code;
    }
    exit $exit_code;
}
elsif ( $command eq "status" ) {
    print "Checking the Status of the script.. OK \n";
    exit 0;
}
else {
    &usage();
    exit 1;
}

}

sub start_vip() {

`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;

}
sub stop_vip() {

 return 0  unless  ($ssh_user);
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;

}

sub usage {

print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";

}

　　*The script comes with the software, and the script acquisition method is as follows: mha**In the source package samples**There is a template of the script in the directory. You can use it by modifying the template. Path such as: mha4mysql-manager-0.56/samples/scripts* 

　　Script modification content

my $vip = '10.0.0.55/24';
my $key = '0';
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";

Add execute permission to script, otherwise mha Unable to start

chmod +x /usr/local/bin/master_ip_failover

**Manual binding VIP(****Main library)**

ifconfig eth0:0 10.0.0.55/24

　　inspect

[root@db01 ~]# ip a s eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000

link/ether 00:0c:29:6c:7a:11 brd ff:ff:ff:ff:ff:ff
inet 10.0.0.51/24 brd 10.0.0.255 scope global eth0
inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:0
inet6 fe80::20c:29ff:fe6c:7a11/64 scope link 
   valid_lft forever preferred_lft forever

​     **thus vip****Drift configuration complete** 

### 1.5.3 test virtual IP drift

see db02 of slave information

![img](/img/bVErc7) View Code Current master-slave state

Stop the main library

[root@db01 ~]# /etc/init.d/mysqld stop

stay db03 View from library on slave information

![img](/img/bVErc7) View Code Master slave information after stopping the master library

stay db01 View on vip information

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000

link/ether 00:0c:29:6c:7a:11 brd ff:ff:ff:ff:ff:ff
inet 10.0.0.51/24 brd 10.0.0.255 scope global eth0
inet6 fe80::20c:29ff:fe6c:7a11/64 scope link 
   valid_lft forever preferred_lft forever

stay db02 View on vip information

[root@db02 ~]# ip a s eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000

link/ether 00:0c:29:d6:0a:b3 brd ff:ff:ff:ff:ff:ff
inet 10.0.0.52/24 brd 10.0.0.255 scope global eth0

inet 10.0.0.55/24 brd 10.0.0.255 scope global secondary eth0:0

inet6 fe80::20c:29ff:fed6:ab3/64 scope link 
   valid_lft forever preferred_lft forever

​     So far, VIP The drift test is successful

## 1.6 configuring binlog server

### 1.6.1 configuring binlog server

1)Preliminary preparation:

> 1,Prepare a new one mysql example(db03),GTID Must be on.
>
> 2,future binlog The receiving directory cannot be the same as the main database binlog Same directory

2)stop it mha

masterha_stop --conf=/etc/mha/app1.cnf

3)stay app1.cnf open binlogserver function

[binlog1]
no_master=1
hostname=10.0.0.53                         ----> host DB03
master_binlog_dir=/data/mysql/binlog/  ----> binlog Save directory

4)open binlog Receive directory, pay attention to permissions

mkdir -p /data/mysql/binlog/
chown -R mysql.mysql /data/mysql

Enter the directory to start the program

cd /data/mysql/binlog/ &&\
mysqlbinlog -R --host=10.0.0.51 --user=mha --password=mha --raw --stop-never mysql-bin.000001 &

　　Parameter Description:-R Remote host

5)start-up mha

nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 &

### 1.6.2 test binlog backup

\#View binlog in binlog directory

[root@db03 binlog]# ll
total 44
-rw-r--r-- 1 root root 285 Mar 8 03:11 mysql-bin.000001

\#Log in to the main library

[root@mysql-db01 ~]# mysql -uroot -p123

\#Refresh binlog

mysql> flush logs;

\#Check the binlog directory again

[root@db03 binlog]# ll
total 48
-rw-r--r-- 1 root root 285 Mar 8 03:11 mysql-bin.000001
-rw-r--r-- 1 root root 143 Mar 8 04:00 mysql-bin.000002

## 1.7 mysql middleware Atlas

### 1.7.1 introduction to atlas

　　Atlas By Qihoo 360 company Web A platform developed and maintained by the infrastructure team of the platform Department MySQL Data middle layer project of the protocol. It's in MySQL Officially launched MySQL-Proxy 0.8.2 Based on the version, a large number of modifications have been made bug，Many features have been added. At present, the project has been widely used in 360 company, with many applications MySQL The service has been connected Atlas The platform carries billions of read and write requests every day.

　　At the same time, more than 50 companies have deployed in the production environment Atlas，More than 800 people have joined our developer exchange group, and these numbers are increasing. And easy to install. The configuration notes are written in great detail, all in Chinese.

　　Atlas Official link: https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md

　　Atlas Download link: https://github.com/Qihoo360/Atlas/releases

### 1.7.2 main functions

　　Read write separation, slave load balancing, automatic table splitting IP filter

　　SQL Statement black and white list DBA Smooth up and down line DB,Automatic removal of downtime DB

*Atlas**Relative to the official MySQL-Proxy**Advantages of*

> 1.All in the main process Lua Code use C rewrite, Lua Management interface only
>
> 2.Rewrite network model and thread model
>
> 3.It realizes the real connection pool
>
>  
>
> 4.The lock mechanism is optimized and the performance is improved dozens of times 

### 1.7.3 usage scenarios

　　Atlas It is a front-end application and back-end application MySQL Middleware between databases, which makes application programmers no longer need to care about the separation of reading and writing, sub table and so on MySQL Relevant details can focus on writing business logic and making DBA The operation and maintenance work of the is transparent to the front-end application, online and offline DB Front end applications are not aware.

![file](/img/bVcLBxB)
　　Atlas Is an application and MySQL Middleware between. On the back end DB It seems, Atlas It is equivalent to the client connecting it. From the perspective of front-end applications, Atlas Equivalent to one DB. 

　　Atlas As the server communicates with the application, it realizes MySQL Client and server protocols, as both client and server MySQL Communications. It blocks the application DB At the same time, in order to reduce MySQL It also maintains the connection pool.

### 1.7.4 introduction to other schemes of enterprise read-write separation and sub database and sub table

>   Mysql-proxy(oracle)
>
>   Mysql-router(oracle)
>
>   Atlas (Qihoo 360)
>
>   Atlas-sharding (Qihoo 360)
>
>   Cobar(It's Alibaba( B2B)Department development)
>
>   Mycat(Based on Alibaba open source Cobar Product development)
>
>   TDDL Smart Client The way of (Taobao)
>
>   Oceanus(58 Local database middleware)
>
>   OneProxy(Lou Fangxin, former chief architect of Alipay)
>
>   vitess(Database middleware developed by Google)
>
>   Heisenberg(Baidu)
>
>   TSharding(Bai Hui, mushroom Street)
>
>   Xx-dbproxy(Jinshan Kingshard,Dangdang sharding-jdbc )
>
>   amoeba

### 1.7.5 installing Atlas

　　Software acquisition address: https://github.com/Qihoo360/Atlas/releases

*be careful:*

> 1,Atlas Can only be installed on a 64 bit system
>
> 2,Centos 5.X install Atlas-XX.el5.x86_64.rpm，Centos 6.X install Atlas-XX.el6.x86_64.rpm. 
>
> 3,back-end mysql Version should be greater than 5.1，Recommended use Mysql 5.6 above
>
> 　　Atlas (ordinary) : Atlas-2.2.1.el6.x86_64.rpm
>
> 　　Atlas (Sub table) : Atlas-sharding_1.0.1-el6.x86_64.rpm

 Download and install atlas

wget https://github.com/Qihoo360/A...
rpm -ivh Atlas-2.2.1.el6.x86_64.rpm

​     The installation is now complete

### 1.7.6 configuring Atlas profiles

　　atlas The password in the configuration file needs to be encrypted and can be encrypted with the encryption tool provided with the software

cd /usr/local/mysql-proxy/conf/
/Usr / local / MySQL proxy / bin / encrypt password ----- > create encryption password

Production ciphertext password:

[root@db03 bin]# /usr/local/mysql-proxy/bin/encrypt 123
3yb5jEku5h4=
[root@db03 bin]# /usr/local/mysql-proxy/bin/encrypt mha
O2jBXONX098=

Edit profile

vim /usr/local/mysql-proxy/conf/test.cnf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 10.0.0.55:3306
proxy-read-only-backend-addresses = 10.0.0.52:3306,10.0.0.53:3306
pwds = repl:3yb5jEku5h4=,mha:O2jBXONX098=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log=ON
proxy-address = 0.0.0.0:33060
admin-address = 0.0.0.0:2345
charset=utf8

The configuration file contains all Chinese comments. Here is a more detailed explanation:

![img](/img/bVErc7) View Code Atlas Profile description

### 1.7.7 launching Atlas

Write a atlas Of course, you can also write scripts, which can be managed directly and manually:

/Usr / local / MySQL proxy / bin / MySQL proxyd test start # start
/Usr / local / MySQL proxy / bin / MySQL proxyd test stop # stop
/Usr / local / MySQL proxy / bin / MySQL proxyd test restart # restart

　　be careful: test Is the name of the profile

Script content:

![img](/img/bVErc7) View Code Atas Management script

Check whether the port is normal

[root@db03 ~]# netstat -lntup|grep mysql-proxy
tcp 0 0 0.0.0.0:33060 0.0.0.0:* LISTEN 2125/mysql-proxy
tcp 0 0 0.0.0.0:2345 0.0.0.0:* LISTEN 2125/mysql-proxy

### 1.7.8 Atlas management operation

　　Login management interface

[root@db03 ~]# mysql -uuser -ppwd -h127.0.0.1 -P2345

　　View help information

mysql> SELECT * FROM help;

　　View the back-end agent Library

3 rows in set (0.00 sec)

　　Smooth removal mysql

mysql> REMOVE BACKEND 2;
Empty set (0.00 sec)

　　 Check for removal

2 rows in set (0.00 sec)

　　Save to profile

mysql> SAVE CONFIG;

　　Add nodes back

mysql> add slave 10.0.0.52:3306;
Empty set (0.00 sec)

　　Check whether it is added successfully

3 rows in set (0.00 sec)

　Save to profile

mysql> SAVE CONFIG;

### 1.7.9 connect to the database to view the load

adopt atlas Log in the data. Note that the user and password on the database are used

shell> mysql -umha -pmha -h127.0.0.1 -P33060

First query server_id

1 row in set (0.00 sec)

Second query server_id

1 row in set (0.00 sec)

​     ***You can see from the above that the load is successful\***

### 1.7.10 description of read-write separation

　　Atlas It will transparently send transaction statements and write statements to the master library for execution, and read statements to the slave library for execution. The following statements will be executed in the main library:

Statements in explicit transactions

> autocommit=0 All statements when
>
> contain select GET_LOCK()Statement of
>
> except SELECT,SET,USE,SHOW,DESC,EXPLAIN Outside.

**Load balancing configuration from library**

Proxy read only backend addresses = IP1: port1 @ weight, ip2:port2 @ weight

### 1.7.11 Atlas advanced functions

**Automatic sub table**

　　use Atlas When using the table splitting function of, you first need to create a table in the configuration file test.cnf set up tables Parameters.

tables Parameter setting format: database name.Table name.Sub table field.Number of sub tables, such as:

> Your database is called school，Watch name stu，Sub table fields are called id，There are two tables in total, so write it as school.stu.id.2，If there are other sub tables, they can be separated by commas.
>
> You need to manually create two sub tables( stu_0,stu_1，Note that the sub table sequence number starts from 0).
>
> All child tables must be DB The same one database Inside.
>
> When passed Atlas Execute( SELECT,DELETE,UPDATE,INSERT,REPLACE)During operation, Atlas According to the sub table results( id%2=k)，Navigate to the corresponding sub table( stu_k). 
>
> For example, execute select * from stu where id=3;，Atlas Automatically from stu_1 This sub table returns the query results.
>
> But if you do SQL Statement( select * from stu;)Don't take it with you id，You will be prompted to execute stu Table does not exist.

Atles Description of functions

> Atlas The functions of automatic table creation and cross database sub table are not supported at present.
>
> Atlas Currently, the statements supporting split tables are SELECT,DELETE,UPDATE,INSERT,REPLACE. 

**IP****Filtering: client-ips**

　　This parameter is used to implement IP Filtering function.

　　In the traditional development mode, applications are directly connected DB，therefore DB The machine that will deploy the application(such as web The server)of IP Make access authorization.

After introducing the middle layer, because of the connection DB Yes Atlas，therefore DB Change to deployment Atlas Of the machine IP For access authorization, if any client can connect Atlas，It will bring potential risks.

　　client-ips Parameters are used to control the connection Atlas Of the client IP，Can be accurate IP，It can also be IP Paragraphs, separated by commas, can be written on one line.

　　For example: client-ips=**192.168**.**1.2**, **192.168**.**2** 

　　That means 192.168.1.2 this IP And 192.168.2.*Of this paragraph IP Can connect Atlas，other IP Cannot be connected. If this parameter is not set, any IP Can be connected Atlas. If set client-ips Parameter, and Atlas Hanging in front LVS，Must be set lvs-ips Parameter, otherwise it can not be set lvs-ips. 

**SQL****Statement black and white list function:** Atlas Will be shielded without where Conditional delete and update Operation, and sleep Function.

## 1.8 atlas sharding version

### 1.8.1 version introduction

　　Sharding The basic idea of is to divide the data in a data table into multiple parts, Stored on different hosts(There are many strategies for segmentation), So as to alleviate the problem of performance and capacity of a single machine.

　　sharding It's a kind of horizontal segmentation, It is suitable for scenarios with huge single table data. at present atlas Support static

　　sharding programme, Automatic migration of data and dynamic joining of data groups are not supported for the time being.

　　Atlas In table sharding, The same database can be shared at the same time sharding Table and not sharding Table of, no sharding Table data does not exist sharding In database group.

　　at present Atlas sharding support insert, delete, select, update sentence, Only cross shard Business of. All write operations, such as insert, delete, update Only one group can be hit at a time, Otherwise, it will be reported"ERROR 1105 (HY000):write operation is only allow to one dbgroup!"error.

　　because sharding Replaced Atlas Table splitting function, So in Sharding Inside the branch, Atlas The function of stand-alone sub table has been removed, to configure tables Will no longer be valid.

### 1.8.2 atlas sharding architecture

![file](/img/bVcLBxC)
### 1.8.3 Sharding configuration example

　　Atlas Support non sharding Follow sharding The same table exists for Atlas in, 2.2.1 The previous configuration can be run directly. Previous configurations such as

proxy-backend-addresses = 192.168.0.12:3306
proxy-read-only-backend-addresses = 192.168.0.13:3306,192.168.0.14:3306 ...

　　This is configured with a master And two slave,This belongs to non sharding Group of, All non sharding All tables and statements will be sent to this group. 

　　So not before Sharding of Atlas The table can be used seamlessly on the new version,

​     **be careful:** wrong Sharding Only one group can be configured, and sharding Multiple groups can be configured. The following configuration, Configured Sharding Group of, Note the difference from the above configuration

[shardrule-0]
table = test.sharding_test

　　Sub table name with database+Table name composition t

ype = range

　　sharding Type: range or hash

shard-key = id

　　sharding field

groups = 0:0-999,1:1000-1999

　　Segmented group，If it is range Type sharding，be groups The format is: group_id:id Range. If it is hash Type sharding，be groups The format is: group_id. for example groups = 0, 1

[group-0]
proxy-backend-addresses=192.168.0.15:3306
proxy-read-only-backend-addresses=192.168.0.16:3306
[group-1]
proxy-backend-addresses=192.168.0.17:3306
proxy-read-only-backend-addresses=192.168.0.18:3306

### 1.8.4 Sharding restrictions

**About supported statements**

　　Atlas sharding Only right sql Statement provides limited support, Currently support basic Select, insert/replace, delete,update sentence,Support all Where grammar(SQL-92 standard), I won't support it DDL(create drop alter)And some management statements,DDL Please connect directly MYSQL implement, Please only Atlas Upper execution Select, insert, delete, update(CRUD)sentence.

　　For the following statements, If the statement hits more than one dbgroup, Atlas No support was provided(If the statement hits only one dbgroup, as select count(*) from test where id < 1000, among dbgroup0 The range is 0 - 1000, Then these features are supported)Limit Offset(support Limit) 

> Order by
>
> Group by Join
>
> ON
>
> Count, Max, Min Equal function

**Add node**

　　be careful: Only supported for the time being range Node expansion in mode, hash Due to the need for data migration, No support for the time being.

　　The extension node ensures that the range of the original node does not change, If already dbgroup0 Is range 0 - 999, dbgroup1 RANGE 1000-1999, You can increase the range at this time>2000 Node of. If a node is added, it is 2000 - 2999, Modify profile, restart Atlas that will do.


> Source: blog Garden  http://dwz.date/d8qV

>  Welcome to follow the official account [code Nong Hua Hua] to learn and grow together
> I will always share Java Dry goods will also share free learning materials, courses and interview brochures