MySQL环境准备

主机名	wanIP	lanIP	角色
db01	10.0.0.51	172.16.1.51	主库,MHA客户端
db02	10.0.0.52	172.16.1.52	从库,MHA客户端
db03	10.0.0.53	172.16.1.53	从库,MHA客户端
db04	10.0.0.54	172.16.1.54	从库,MHA客户端
MHA-manager	10.0.0.53	172.16.1.53	MHA服务端

1.停库
[root@db01 ~]# /etc/init.d/mysqld stop
2.删除data目录
[root@db01 ~]# rm -fr /application/mysql/data
3.初始化配置文件
[root@db01 support-files]# cp my-default.cnf /etc/my.cnf
cp: overwrite ‘/etc/my.cnf’? y
4.初始化MySQL
[root@db01 support-files]# cd /application/mysql/scripts/
[root@db01 scripts]# ./mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data
5.启动数据库
[root@db01 scripts]# /etc/init.d/mysqld start

MySQL高可用方案

1.双主 + keepalived

2.MMM

3.MHA

4.MGR（半同步复制）

MHA原理

当Master出现故障时，它可以自动将最新数据的Slave提升为新的Master,然后将所有其他的Slave重新指向新的Master。

MHA架构介绍

img

*   1.MHA是C/S结构的服务

*   2.MHA的manager可以装在任何一台服务器上（尽量不要装在主库上）

*   3.一个MHA可以管理多套MySQL集群（上百套）

*   4.服务端：manager，客户端：node

MHA的工具

MHA manager 服务端工具

[root@db01 ~]# tar xf mha4mysql-manager-0.56.tar.gz
[root@db01 ~]# cd /root/mha4mysql-manager-0.56/bin
[root@db01 bin]# ll
total 40
-rwxr-xr-x 1 4984 users 1995 Apr  1  2014 masterha_check_repl
-rwxr-xr-x 1 4984 users 1779 Apr  1  2014 masterha_check_ssh
-rwxr-xr-x 1 4984 users 1865 Apr  1  2014 masterha_check_status
-rwxr-xr-x 1 4984 users 3201 Apr  1  2014 masterha_conf_host
-rwxr-xr-x 1 4984 users 2517 Apr  1  2014 masterha_manager
-rwxr-xr-x 1 4984 users 2165 Apr  1  2014 masterha_master_monitor
-rwxr-xr-x 1 4984 users 2373 Apr  1  2014 masterha_master_switch
-rwxr-xr-x 1 4984 users 5171 Apr  1  2014 masterha_secondary_check
-rwxr-xr-x 1 4984 users 1739 Apr  1  2014 masterha_stop

MHA node客户端工具

[root@db01 ~]# tar xf mha4mysql-node-0.56.tar.gz
[root@db01 ~]# cd mha4mysql-node-0.56/bin/
​
[root@db01 bin]# ll
total 44
-rwxr-xr-x 1 4984 users 16367 Apr  1  2014 apply_diff_relay_logs
-rwxr-xr-x 1 4984 users  4807 Apr  1  2014 filter_mysqlbinlog
-rwxr-xr-x 1 4984 users  8261 Apr  1  2014 purge_relay_logs
-rwxr-xr-x 1 4984 users  7525 Apr  1  2014 save_binary_logs

MHA的优点总结

1）Masterfailover and slave promotion can be done very quickly  自动故障转移快 10 - 30 秒

2）Mastercrash does not result in data inconsistency  主库崩溃不存在数据一致性问题

3）Noneed to modify current MySQL settings (MHA works with regular MySQL)  不需要对当前mysql环境做重大修改

4）Noneed to increase lots of servers  不需要添加额外的服务器(仅一台manager就可管理上百个replication)

5）Noperformance penalty  性能优秀，可工作在半同步复制和异步复制，当监控mysql状态时，仅需要每隔N秒向master发送ping包(默认3秒)，所以对性能无影响。你可以理解为MHA的性能和简单的主从复制框架性能一样。select ping

6）Works with any storage engine  只要replication支持的存储引擎，MHA都支持，不会局限于innodb

基于GTID的主从复制

什么是GTID？

GTID是一个唯一标识符，主库的UUID+ 事务提交号，TID组成

UUID MySQL实例的ID

[root@db01 data]# cat auto.cnf 
[auto]
server-uuid=6b898418-23ec-11ea-9271-000c29e98743</pre>

TID：事务提交号

GTID

6b898418-23ec-11ea-9271-000c29e98743:4

GTID新特性

*   支持多线程复制（主库：dump线程，从库：IO线程和 每一个库都开启一个SQL线程）

*   无需再找binlog的名字和位置点，（show master status；file/position）

    *   change master to

    *   master_host='10.0.0.51',

    *   master_user='rep',

    *   master_password='123',

    *   master_auto_position=1;

    #1.grant replication slave on \*.\* to rep@'%' identified by '123';
    #2.在从库上执行以上change语句
    #3.start slave；

*   基于Row复制只保存改变的列,大大节省Disk Space/Network resources和Memory usage.

*   支持把Master 和Slave的相关信息记录在Table中，原来是记录在文件里,记录在表里,增强可用性

*   支持延时复制

GTID实践

mysql> show variables like '%gtid%';
+---------------------------------+-----------+
| Variable_name                   | Value     |
+---------------------------------+-----------+
| binlog_gtid_simple_recovery     | OFF       |
| enforce_gtid_consistency        | OFF       |## 开启
| gtid_executed                   |           |
| gtid_mode                       | OFF       |## 开启
| gtid_next                       | AUTOMATIC |
| gtid_owned                      |           |
| gtid_purged                     |           |
| simplified_binlog_gtid_recovery | OFF       |
+---------------------------------+-----------+

[root@db01 data]# vim /etc/my.cnf
​
[mysqld]
gtid_mode=ON
enforce_gtid_consistency
log-bin=mysql-bin
binlog_format=row
log-slave-updates
server_id=1

1.级联复制
2.双主+keepalived
3.gtid
​
[root@db01 data]# /etc/init.d/mysqld restart

创建主从复制用户

mysql> grant replication slave on *.* to rep@'%' identified by '123';

从库执行change master

change master to
master_host='10.0.0.51',
master_user='rep',
master_password='123',
master_auto_position=1;

开启主从复制

mysql> start slave;

检查主从复制状态

mysql> show slave status\G
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
​
​
mysql> show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000004 |      191 |              |                  | 6b898418-23ec-11ea-9271-000c29e98743:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
​
​
[root@db01 data]# tail -100 /application/mysql/data/db01.err

报错原因：如果想要开启gtid-mode参数，必须依赖 log-bin 和 log-slave-updates

解决办法：

[root@db01 data]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
log-slave-updates

报错原因：必须要开启GTID

解决办法：

[root@db03 scripts]# vim /etc/my.cnf
[mysqld]
server_id=3
gtid_mode=ON
enforce_gtid_consistency
log-bin=mysql-bin
binlog_format=row
log-slave-updates

基于GTID的MHA

传统

1.主库要开启binlog，从库不用开启binlog

2.主库要有中从复制用户，从库不需要创建主从复制用户

3.主库server_id=5,从库只要不等于5即可，从库之间可以相同

---

基于MHA的主从复制

1.主库要开启binlog，从库也要开启binlog

2.主库要创建主从复制用户，从库也必须创建主从复制用户

3.主库server_id=5，从库只要不等于5即可，从库之间必须不相同

从库随时会被提升为新的主库

1.关闭所有数据库自动清除relay log 的功能

• 主库要关闭

• 从库也要关闭

• 临时+永久关闭

2.从库要设置为只读 开启read-only功能

• 主库不开启

• 从库要开启

• 临时开启

临时关闭自动清除relay log功能（所有库）

set global relay_log_purge = 0;

永久关闭自动清除relay log功能（所有库）

[root@db01 data]# vim /etc/my.cnf
relay_log_purge = 0

临时开启只读功能（所有从库）

set global read_only=1;

部署MHA

[root@db01 ~]# ll
-rw-r--r-- 1 root root 36326 Dec 16 08:35 mha4mysql-node-0.56-0.el6.noarch.rpm
​
[root@db02 ~]# ll
-rw-r--r--  1 root root     87119 Dec 16 08:35 mha4mysql-manager-0.56-0.el6.noarch.rpm
-rw-r--r--  1 root root     36326 Dec 16 08:35 mha4mysql-node-0.56-0.el6.noarch.rpm
​
[root@db03 ~]# ll
-rw-r--r-- 1 root root 36326 Dec 16 08:35 mha4mysql-node-0.56-0.el6.noarch.rpm
​
[root@db04 ~]# ll
-rw-r--r-- 1 root root 36326 Dec 16 08:35 mha4mysql-node-0.56-0.el6.noarch.rpm
​
## 安装包下载地址
https://github.com/yoshinorim/mha4mysql-manager/wiki/Downloads
​
# 安装node
[root@db01 ~]# yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm
[root@db02 ~]# yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm
[root@db03 ~]# yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm
[root@db04 ~]# yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm
​
#安装manager
[root@db02 ~]# yum localinstall -y mha4mysql-manager-0.56-0.el6.noarch.rpm 
​
#创建MHA管理用户（所有库都要创建）
mysql> grant all on *.* to mha@'%' identified by 'mha';
​
#做命令的软链接
[root@db01 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db01 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql
​
[root@db02 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db02 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql
​
[root@db03 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db03 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql
​
[root@db04 ~]# ln -s /application/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
[root@db04 ~]# ln -s /application/mysql/bin/mysql /usr/bin/mysql
​
#创建密钥对
[root@db01 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db01 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.51
[root@db01 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.52
[root@db01 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.53
[root@db01 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.54
​
----
​
[root@db02 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db02 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.51
[root@db02 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.52
[root@db02 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.53
[root@db02 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.54
​
----
​
[root@db03 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db03 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.51
[root@db03 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.52
[root@db03 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.53
[root@db03 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.54
​
----
​
[root@db04 ~]# ssh-keygen -t dsa -P '' -f ~/.ssh/id_dsa >/dev/null 2>&1
[root@db04 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.51
[root@db04 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.52
[root@db04 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.53
[root@db04 ~]# ssh-copy-id -i ~/.ssh/id_dsa.pub 10.0.0.54
​
​
# 写MHA配置文件
[root@db02 ~]# mkdir /etc/mha
[root@db02 ~]# vim /etc/mha/app1.cnf
[server default]
manager_log=/etc/mha/app1/manager.log
manager_workdir=/etc/mha/app1
master_binlog_dir=/application/mysql/data
user=mha
password=mha
ping_interval=2
repl_password=123
repl_user=rep
ssh_user=root
ssh_port=22
​
[server1]
hostname=10.0.0.51
port=3306
​
​
[server2]
#candidate_master=1
#check_repl_delay=0
hostname=10.0.0.52
port=3306
​
​
[server3]
hostname=10.0.0.53
port=3306
​
[server4]
hostname=10.0.0.54
port=3306
​
## 启动前测试
[root@db02 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf
Mon Dec 16 12:12:11 2019 - [info] All SSH connection tests passed successfully.
​
[root@db02 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf
MySQL Replication Health is OK.
​
#启动MHA
nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /etc/mha/app1/manager.log 2>&1 &
​
#检测MHA启动状态
[root@db02 ~]# masterha_check_status --conf=/etc/mha/app1.cnf
app1 (pid:121442) is running(0:PING_OK), master:10.0.0.51
​
#停止MHA
[root@db02 ~]# masterha_stop --conf=/etc/mha/app1.cnf</pre>

报错原因：将IP解析成主机名了

解决方法：

#在配置文件中，添加一个跳过反向解析的参数
skip_name_resolve