Mysql——高可用集群部署

目录

一、源码编译mysql

二、mysql的主从复制

2.1、主从复制

2.2、延迟复制

2.3、慢查询日志

2.4、MySQL的并行复制

三、MySQL半同步模式

四、mysql高可用组复制

五、mysql-router

六、mysql高可用MHA

七、为MHA添加VIP功能

一、源码编译mysql

1、安装依赖

[root@mysql1 ~]# yum install cmake gcc-c++ openssl-devel ncurses-devel.x86_64 libtirpc-devel-1.3.3-8.el9_4.x86_64.rpm rpcgen.x86_64

2、解压源码包

[root@mysql1 ~]# tar zxf mysql-boost-5.7.44.tar.gz 
[root@mysql1 ~]# cd mysql-5.7.44/

3、源码编译安装MySQL

[root@mysql1 mysql-5.7.44]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/data/mysql -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_EXTRA_CHARSETS=all -DDEFAULT_CHARSET=utf8mb4 -DDEFAULT_COLLATION=utf8mb4_unicode_ci -DWITH_BOOST=/root/mysql-5.7.44/boost/boost_1_59_0/
[root@mysql1 mysql-5.7.44]# make -j2
[root@mysql1 mysql-5.7.44]# make install

生成mysql数据目录：

[root@mysql1 ~]# cd /usr/local/mysql/
[root@mysql1 mysql]# useradd -s /sbin/nologin -M mysql
[root@mysql1 mysql]# mkdir /data/mysql -p
[root@mysql1 mysql]# chown mysql.mysql -R /data/mysql

生成启动文件： 

[root@mysql1 mysql]# cd support-files/
[root@mysql1 support-files]# cp mysql.server /etc/init.d/mysqld

 编辑配置文件：

[root@mysql1 support-files]# vim /etc/my.cnf

编辑环境变量： 

[root@mysql1 mysql]# vim ~/.bash_profile 
[root@mysql1 mysql]# source ~/.bash_profile 

初始化： 

[root@mysql1 mysql]# mysqld --user mysql --initialize
2024-08-22T02:43:33.182882Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-22T02:43:33.876344Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-22T02:43:33.939173Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-22T02:43:33.996930Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 530f15cf-6030-11ef-80da-000c29f9980d.
2024-08-22T02:43:33.998428Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-22T02:43:34.355930Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-22T02:43:34.355957Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-22T02:43:34.357064Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-22T02:43:34.381234Z 1 [Note] A temporary password is generated for root@localhost: CCwv;7jj9nQC

[root@mysql1 mysql]# vim passwd
[root@mysql1 mysql]# cat passwd 
CCwv;7jj9nQC

启动mysql： 

[root@mysql1 mysql]# chkconfig mysqld on
[root@mysql1 mysql]# mysql_secure_installation
[root@mysql1 mysql]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql1.err'.
 SUCCESS! 
[root@mysql1 mysql]# mysql -uroot -p

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

二、mysql的主从复制

2.1、主从复制

1、 配置master

[root@mysql1 ~]# vim /etc/my.cnf
[root@mysql1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW MASTER STATUS
    -> ;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      595 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2、配置salve1

[root@mysql2 ~]# vim /etc/my.cnf
[root@mysql2 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.25.254.11',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=350;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

 测试：

mysql>  CREATE DATABASE gyj;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE TABLE gyj.userlist(
    -> username varchar(20) not null,
    -> password varchar(50) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into gyj.userlist value('user1','111');
Query OK, 1 row affected (0.00 sec)

mysql> select * from gyj.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+
1 row in set (0.00 sec)

2.2、延迟复制

1、添加slave2

新建虚拟机，从master拷贝mysql文件给新虚拟机，作为slave2。

[root@mysql1 ~]# cd /usr/local/
[root@mysql1 local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src
[root@mysql1 local]# rsync -al -r mysql root@172.25.254.13:/usr/local/

slave2的基础配置：

[root@mysql3 local]# cd mysql/
[root@mysql3 mysql]# ls
bin  docs  include  lib  LICENSE  man  mysql-test  README  README-test  share  support-files

[root@mysql3 mysql]# useradd -s /sbin/nologin -M mysql
[root@mysql3 mysql]#  mkdir /data/mysql -p
[root@mysql3 mysql]# chown mysql.mysql -R /data/mysql

#生成启动文件
[root@mysql3 mysql]# cd support-files/
[root@mysql3 support-files]# cp mysql.server /etc/init.d/mysqld

 编辑配置文件：

#编辑配置文件
[root@mysql3 mysql]# vim /etc/my.cnf

 编辑环境变量：

[root@mysql3 support-files]# vim ~/.bash_profile 
[root@mysql3 support-files]# source ~/.bash_profile

 mysql初始化并启动：

[root@mysql3 support-files]# mysqld --user mysql --initialize
[root@mysql3 support-files]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql3.err'.
. SUCCESS! 

#修改密码
[root@mysql3 support-files]# mysql_secure_installation

2、从master节点备份数据给slave2:

查看master的数据库表中数据：

 进行拷贝：

#拷贝数据库
[root@mysql1 mysql]# mysqldump -uroot -pmysql gyj > gaoyingjie.sql

#查看拷贝的数据库
[root@mysql1 mysql]# ls
bin   gaoyingjie.sql  lib      man         README       share
docs  include         LICENSE  mysql-test  README-test  support-files

#将拷贝出的文件远程传输给slave2
[root@mysql1 mysql]# scp -r gaoyingjie.sql root@172.25.254.13:/usr/local/mysql

在slave2中：

#新建一个名为gyj的database
[root@mysql3 support-files]# mysql -uroot -pmysql -e "create database gyj;"

#查看可拷贝过来的文件
[root@mysql3 mysql]# ls
bin   gaoyingjie.sql  lib      man         README       share
docs  include         LICENSE  mysql-test  README-test  support-files

#将拷贝文件传给新建的database
[root@mysql3 mysql]# mysql -uroot -p gyj <gaoyingjie.sql
Enter password:

#查询该数据库的gyj.userlist表 ，与master的表中数据一致
[root@mysql3 mysql]#  mysql -uroot -pmysql -e "select * from gyj.userlist;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
+----------+----------+

3、配置slave2的slave功能：

在master中查看日志pos

在slave2:

mysql>  CHANGE MASTER TO MASTER_HOST='172.25.254.11', MASTER_USER='repl',MASTER_PASSWORD='repl', MASTEER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1238;

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW SLAVE STATUS\G;

查看是否同步数据：

#在master插入一条数据
mysql> insert into gyj.userlist values('user2','222');
Query OK, 1 row affected (0.06 sec)


#在slave查看是否有数据
mysql> select * from gyj.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
+----------+----------+
2 rows in set (0.00 sec)

 4、在slave2端添加延迟复制语句

mysql>  STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO MASTER_DELAY=60;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G;

 5、测试

当master删除数据时，slave2会延迟60s再删除数据。

master:删除user2

slave2:查询到的仍然是原始数据

2.3、慢查询日志

慢查询，顾名思义，执行很慢的查询 ，当执行SQL 超过 long_query_time 参数设定的时间阈值（默认 10s ）时，就被认为是慢查询，这个 SQL语句就是需要优化的；

慢查询被记录在慢查询日志里 ，慢查询日志默认是不开启的 ，如果需要优化SQL语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的。

#默认不开启
mysql> SHOW variables like "slow%";
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_launch_time    | 2                           |
| slow_query_log      | OFF                         |
| slow_query_log_file | /data/mysql/mysql1-slow.log |
+---------------------+-----------------------------+
3 rows in set (0.00 sec)

1、开启慢查询日志

mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.01 sec)

mysql> SET long_query_time=4;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES like "long%";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES like "slow%";
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| slow_launch_time    | 2                           |
| slow_query_log      | ON                          |
| slow_query_log_file | /data/mysql/mysql1-slow.log |
+---------------------+-----------------------------+
3 rows in set (0.00 sec)

 2、查看慢查询日志

[root@mysql1 mysql]# cat /data/mysql/mysql1-slow.log 
/usr/local/mysql/bin/mysqld, Version: 5.7.44-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /data/mysql/mysql.sock
Time                 Id Command    Argument

3、测试慢查询

2.4、MySQL的并行复制

默认情况下 slave 中使用的是 sql 单线程回放

在 master 中时多用户读写，如果使用 sql 单线程回放那么会造成组从延迟严重

开启 MySQL 的多线程回放可以解决上述问题

1、在slave1编辑配置文件

[root@mysql2 mysql]# vim /etc/my.cnf
[root@mysql2 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL... SUCCESS! 

查看slave1的线程有16个：

三、MySQL半同步模式

1、配置gtid

[root@mysql1 mysql]# vim /etc/my.cnf
[root@mysql1 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL............ SUCCESS! 
Starting MySQL. SUCCESS! 

master：

slave1:

slave2:

2、停止slave

两台slave都要停：

[root@mysql2 mysql]# mysql -uroot -pmysql

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

3、开启slave的gtid

mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.11', MASTER_USER='repl',MASTER_PASSWORD='repl', MASTERR_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW SLAVE STATUS\G;

4、启动半同步模式

(1)、master端开启半同步

安装半同步插件：

[root@mysql1 mysql]# mysql -uroot -pmysql
#安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.07 sec)

 查看插件情况：

mysql> SELECT PLUGIN_NAME, PLUGIN_STATUS
    -> FROM INFORMATION_SCHEMA.PLUGINS
    -> WHERE PLUGIN_NAME LIKE '%semi%';
+----------------------+---------------+
| PLUGIN_NAME          | PLUGIN_STATUS |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE        |
+----------------------+---------------+
1 row in set (0.05 sec)

打开半同步功能：

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

 查看半同步状态：

 编辑master配置文件

[root@mysql1 mysql]# vim /etc/my.cnf
[root@mysql1 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL........... SUCCESS! 
Starting MySQL. SUCCESS! 

 (2)、slave端开启半同步

#安装插件
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.05 sec)

mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)

#重启io线程，半同步才能生效
mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | ON         |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+
8 rows in set (0.05 sec)

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';

编辑配置文件：（必须在mysql加载模块后才能添加此配置）

slave1与slave2相同

[root@mysql2 mysql]# vim /etc/my.cnf
[root@mysql2 mysql]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

 5、测试

• 在master中写入数据：

mysql> insert into gyj.userlist values('user3','333');
Query OK, 1 row affected (0.03 sec)

mysql> select * from gyj.userlist;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 111      |
| user2    | 222      |
| user3    | 333      |
+----------+----------+
3 rows in set (0.00 sec)
#查看
mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';

 连接到2台slave，已同步1笔数据：

• 模拟故障： 

将slave1和slave2的IO进程停掉

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

在master端插入数据，会有延迟：

 自动转入异步模式OFF，并且未同步数据有2笔：

四、mysql高可用组复制

全部主机停掉mysql:

/etc/init.d/mysqld stop

全部主机删除mysql的数据目录：

rm -rf /data/mysql/*

编辑所有主机musql的配置文件：

配置全部主机的本地解析：

vim /etc/hosts

 初始化mysql:

 mysqld --user=mysql --initialize

启动mysql:

/etc/init.d/mysqld start

 登录mysql并修改密码：

mysql -uroot -p初始化后生成的密码
alter user root@localhost identified by 'gyj';"

配置mysql

mysql> CREATE USER rpl_user@'%' IDENTIFIED BY 'gyj';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql>  CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='gyj' FOR CHANNEL 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.02 sec)


#只在第一台主机中开启，后面的主机直接执行START GROUP_REPLICATION;
mysql> SET GLOBAL group_replication_bootstrap_group=ON; #用以指定初始成员，只在第
一台主机中执行
Query OK, 0 rows affected (0.00 sec)

mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected, 1 warning (2.19 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

#查看组成员信息
mysql> SELECT * FROM performance_schema.replication_group_members;

测试：

• 在任意一台主机写入数据

可以在其他主机看到该数据：

• 当其中一台主机挂掉后

 查询组成员可以看出没有mysql2，因为组成员还是大于n/2，所以还是可以写入数据的：

 没有挂掉的mysql3也可以看到数据：

当mysql2重启后：

 在mysql1上查看组成员信息发现没有mysql2,，是因为组成员重启后不自动启动组复制：

 需要对mysql2手动开启组复制：

五、mysql-router

上传并安装mysql-router

 rpm -ivh mysql-router-community-8.4.0-1.el7.x86_64.rpm

 编辑配置文件：

[root@mysql1 ~]# vim /etc/mysqlrouter/mysqlrouter.conf 
[root@mysql1 ~]# systemctl start mysqlrouter.service

 建立测试用户测试：

mysql> create user gyj@'%' identified by 'gyj';
Query OK, 0 rows affected (10.04 sec)

mysql> GRANT ALL ON  gyj.* to gyj@'%';
Query OK, 0 rows affected (0.00 sec)

[root@mysql-router ~]# mysql -ulee -plee -h 172.25.254.11 -P 7001

六、mysql高可用MHA

1、安装MHA所需软件

新建mha主机：mysql-mha(ip172.25.254.50)

mysql-mha主机上传并解压MHA:

[root@mysql-mha ~]# unzip MHA-7.zip 

全部主机取消dns解析：

[root@mysql1 ~]# vim /etc/ssh/sshd_config 
[root@mysql1 ~]# systemctl restart sshd

实现四台主机的免密认证:

[root@mysql1 ~]# ssh-keygen -t rsa
[root@mysql1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.12
[root@mysql1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.13
[root@mysql1 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.50

[root@mysql2 ~]# ssh-keygen
[root@mysql2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.11
[root@mysql2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.13
[root@mysql2 ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.50

[root@mysql-mha ~]# ssh-keygen
[root@mysql-mha ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.11
[root@mysql-mha ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.12
[root@mysql-mha ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub root@172.25.254.13

在mysql-mha主机上下载所需要软件：

[root@mysql-mha ~]# cd MHA-7/
[root@mysql-mha MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
mha4mysql-manager-0.58.tar.gz                   perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm     perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm          perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm  perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm       perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@mysql-mha MHA-7]# yum install *.rpm -y

将其中的mha4mysql-node-0.58-0.el7.centos.noarch.rpm拷贝给其余三台主机：

[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.11:/root

[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.12:/root
   
[root@mysql-mha MHA-7]# scp mha4mysql-node-0.58-0.el7.centos.noarch.rpm root@172.25.254.13:/root
 

在三台主机中下载安装mha4mysql-node-0.58-0.el7.centos.noarch.rpm：

[root@mysql1 ~]# yum install mha4mysql-node-0.58-0.el7.centos.noarch.rpm -y

2、搭建主从架构

master节点：

/etc/init.d/mysqld stop
rm -rf /data/mysql/*
vim /etc/my.cnf

初始化并登录mysql： 

[root@mysql1 ~]# mysqld --user mysql --initialize
[root@mysql1 ~]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/mysql/mysql1.err'.
 SUCCESS! 
[root@mysql1 ~]# mysql -uroot -p'Vo%khweBl5jZ'


#修改密码
mysql> alter user root@localhost identified by 'mysql';
Query OK, 0 rows affected (0.00 sec)

配置mysql:

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'gyj';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;
Query OK, 0 rows affected (0.00 sec)

在两台slave中：

/etc/init.d/mysqld stop
rm -rf /data/mysql/*
vim /etc/my.cnf

 两台slave初始化并登录mysql:

mysqld --user mysql --initialize
mysql -uroot -p'iUP4cDQW:hT)'

mysql> alter user root@localhost identified by 'gyj';
Query OK, 0 rows affected (0.00 sec)

配置mysql并开启主从模式：

mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.11', MASTER_USER='repl',
    -> MASTER_PASSWORD='gyj', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)


mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL rpl_semi_sync_slave_enabled =1;
Query OK, 0 rows affected (0.00 sec)

mysql> STOP SLAVE IO_THREAD;
Query OK, 0 rows affected (0.04 sec)

mysql> START SLAVE IO_THREAD;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)

3、配置MHA的管理环境

新建配置文件目录，从模板拷贝：

[root@mysql-mha ~]# mkdir /etc/masterha

[root@mysql-mha ~]# cd MHA-7/
[root@mysql-mha MHA-7]# ls
mha4mysql-manager-0.58-0.el7.centos.noarch.rpm  perl-Mail-Sender-0.8.23-1.el7.noarch.rpm
mha4mysql-manager-0.58.tar.gz                   perl-Mail-Sendmail-0.79-21.el7.noarch.rpm
mha4mysql-node-0.58-0.el7.centos.noarch.rpm     perl-MIME-Lite-3.030-1.el7.noarch.rpm
perl-Config-Tiny-2.14-7.el7.noarch.rpm          perl-MIME-Types-1.38-2.el7.noarch.rpm
perl-Email-Date-Format-1.002-15.el7.noarch.rpm  perl-Net-Telnet-3.03-19.el7.noarch.rpm
perl-Log-Dispatch-2.41-1.el7.1.noarch.rpm       perl-Parallel-ForkManager-1.18-2.el7.noarch.rpm
[root@mysql-mha MHA-7]# tar zxf mha4mysql-manager-0.58.tar.gz

[root@mysql-mha ~]# cd MHA-7/mha4mysql-manager-0.58/samples/conf/
[root@mysql-mha conf]# cat masterha_default.cnf app1.cnf > /etc/masterha/app1.cnf

编辑配置文件：

[root@mysql-mha conf]# vim /etc/masterha/app1.cnf 

添加172.25.254.20作为配置文件内检测是否能连接的备用ip，当mha检查如果连接不到master172.25.254.11时，会尝试链接172.25.254.20来判断是哪一方的链接出现问题。

[root@mysql1 ~]# ip a a 172.25.254.20 dev eth0

4、检验配置

[root@mysql-mha ~]# masterha_check_ssh --conf=/etc/masterha/app1.cnf

 检测数据主从复制情况 ：

#在master节点
mysql> GRANT ALL ON *.* TO root@'%' identified by 'mysql';
#在mysql-mha
[root@mysql-mha ~]# masterha_check_repl --conf=/etc/masterha/app1.cnf

5、故障切换

确保以上检测通过后才能进行切换。

无故障手动切换：

[root@mysql-mha ~]# masterha_master_switch --conf=/etc/masterha/app1.cnf --master_state=alive --new_master_host=172.25.254.12 --new_master_port=3306 --orig_master_is_new_slave --running_updates_limit=10000

可以看到手动切换到172.25.254.12上，变成新的master: 

 master故障手动切换：

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

[root@mysql-mha ~]# masterha_master_switch --master_state=dead --conf=/etc/masterha/app1.cnf --dead_master_host=172.25.254.11 --dead_master_port=3306 --new_master_host=172.25.254.12 --new_master_port=3306 --ignore_last_failover

 在172.25.254.13中查看master已经变成172.25.254.12

当172.25.254.11恢复时：需要手动添加到组从关系内成为新的slave

[root@mysql1 ~]# /etc/init.d/mysqld start
Starting MySQL. SUCCESS! 

[root@mysql1 ~]# mysql -uroot -pmysql

#添加到主从关系内，成为salve
mysql> CHANGE MASTER TO MASTER_HOST='172.25.254.12', MASTER_USER='repl',
    -> MASTER_PASSWORD='gyj', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

master（172.25.254.12）出现故障后，自动切换：

#监测
[root@mysql-mha ~]# masterha_manager --conf=/etc/masterha/app1.cnf

[root@mysql-mha ~]# cat /etc/masterha/manager.log

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

在172.25.254.13中看到master变成了172.25.254.11：

当172.25.254.12恢复时：需要手动添加到主从关系中成为新的slave

mysql>  CHANGE MASTER TO MASTER_HOST='172.25.254.11', MASTER_USER='repl',
    -> MASTER_PASSWORD='gyj', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS\G;

七、为MHA添加VIP功能

清除锁文件

[root@mysql-mha ~]# rm -rf /etc/masterha/app1.failover.complete 

上传vip脚本文件：

[root@mysql-mha ~]# ls
anaconda-ks.cfg  master_ip_failover  master_ip_online_change  MHA-7  MHA-7.zip
[root@mysql-mha ~]# cp master_ip_failover master_ip_online_change /usr/local/bin/
[root@mysql-mha ~]# chmod +x /usr/local/bin/master_ip_*
 

编辑vip脚本：改为自己的vip

[root@mysql-mha ~]# vim /usr/local/bin/master_ip_failover 
[root@mysql-mha ~]# vim /usr/local/bin/master_ip_online_change 

在master上添加vip:

启动监控：

[root@mysql-mha ~]#  masterha_manager --conf=/etc/masterha/app1.cnf
[root@mysql-mha ~]# cat /etc/masterha/manager.log

 将master:172.25.254.11停掉：172.25.254.12会变成新的master，并且vip会转移到172.25.254.12