【叶楠老师专题课】【企业必用的数据库集群高可用架构】

MHA介绍：

MHA数据库集群的高可用技术，MHA可以实现故障的监控和故障的自动切换，切换时间是0~30之间，并且在故障切换中，MHA能最大程度上保持数据的一致性，从而达到真正意义上的高可用

环境准备：准备6台机器，其中五台作为数据库服务器，一台作为MHA管理节点

配置一主多从结构

10主库 开启半同步复制 （只要有一台数据库把数据同步成功，就把结果返回给客户端）

20从库（备用主库）开启半同步复制模式

30从库（备用主库）开启半同步复制模式

40从库 只做从库不需要开启半同步

50从库 只做从库不需要开启半同步

主库10配置

[root@host10 ~]# mysql -uroot -p123456

安装半同步插件

主库插件

mysql> install plugin rpl_semi_sync_master soname "semisync_master.so";

从库插件

mysql> install plugin rpl_semi_sync_slave soname "semisync_slave.so";

查看插件

mysql> select pllect plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";

不自动删除本机中继日志文件

mysql> set global relay_log_purge=off;

添加从库同步用户

mysql> grant replication slave on *.* to repluser@"%" identified by "123456";

查看同步用户

mysql> show grants for repluser@"%";

mysql> exit

修改配置文件，启用半同步

[root@host10 ~]# vim /etc/my.cnf

[mysqld]

plugin-load=rpl_semi_sync_master=semisync_master.so

rpl_semi_sync_master_enabled=1

plugin_load=rpl_semi_sync_slave=semisync_slave.so

rpl_semi_sync_slave_enabled=1

server_id=10

log-bin

binlog_format="mixed"

validate_password_policy=0

validate_password_length=6

[root@host10 ~]# systemctl restart mysqld

##################################################################################

配置20数据库服务器

mysql -uroot -p123456

安装插件

mysql> install plugin rpl_semi_sync_master soname "semisync_master.so";

mysql> install plugin rpl_semi_sync_slave soname "semisync_slave.so";

查看插件

mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";

不自动删除本机中继日志文件

mysql> set global relay_log_purge=off;

exit

修改配置文件

[root@host20 ~]# vim /etc/my.cnf

[mysqld]

plugin-load=rpl_semi_sync_master=semisync_master.so

rpl_semi_sync_master_enabled=1

plugin_load=rpl_semi_sync_slave=semisync_slave.so

rpl_semi_sync_slave_enabled=1

server_id=20

log-bin

binlog_format="mixed"

validate_password_policy=0

validate_password_length=6

[root@host20 ~]# systemctl restart mysqld

指定主库信息

[root@host20 ~]# mysql -uroot -p123456

指定主库信息

mysql> change master to

-> master_host="192.168.0.10",

-> master_user="repluser",

-> master_password="123456",

-> master_log_file="host10-bin.00000x",

-> master_log_pos=xxx;

mysql> start slave;

mysql> mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.10

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: host10-bin.000003

Read_Master_Log_Pos: 154

Relay_Log_File: host20-relay-bin.000002

Relay_Log_Pos: 321

Relay_Master_Log_File: host10-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

######################################################################################

配置30数据库服务器

mysql -uroot -p123456

安装插件

mysql> install plugin rpl_semi_sync_master soname "semisync_master.so";

mysql> install plugin rpl_semi_sync_slave soname "semisync_slave.so";

查看插件

mysql> select plugin_name,plugin_status from information_schema.plugins where plugin_name like "%semi%";

不自动删除本机中继日志文件

mysql> set global relay_log_purge=off;

mysql> exit

修改配置文件

[root@host30 ~]# vim /etc/my.cnf

[mysqld]

plugin-load=rpl_semi_sync_master=semisync_master.so

rpl_semi_sync_master_enabled=1

plugin_load=rpl_semi_sync_slave=semisync_slave.so

rpl_semi_sync_slave_enabled=1

server_id=30

log-bin

binlog_format="mixed"

validate_password_policy=0

validate_password_length=6

[root@host30 ~]# mysql -uroot -p123456

mysql> change master to

-> master_host="192.168.0.10",

-> master_user="repluser",

-> master_password="123456",

-> master_log_file="host10-bin.00000x",

-> master_log_pos=xxx;

mysql> start slave;

mysql> mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.10

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: host10-bin.000003

Read_Master_Log_Pos: 154

Relay_Log_File: host30-relay-bin.000002

Relay_Log_Pos: 321

Relay_Master_Log_File: host10-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

######################################################################################

配置40数据库服务器

[root@host40 ~]# vim /etc/my.cnf

[mysqld]

server_id=40

validate_password_policy=0

validate_password_length=6

重启服务

[root@host40 ~]# systemctl restart mysqld

[root@host40 ~]# mysql -uroot -p123456

mysql> change master to

-> master_host="192.168.0.10",

-> master_user="repluser",

-> master_password="123456",

-> master_log_file="host10-bin.00000x",

-> master_log_pos=xxx;

mysql> start slave;

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.10

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: host10-bin.000003

Read_Master_Log_Pos: 154

Relay_Log_File: host40-relay-bin.000002

Relay_Log_Pos: 321

Relay_Master_Log_File: host10-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

###############################################################

配置50数据库服务器

[root@host50 ~]# vim /etc/my.cnf

[mysqld]

server_id=50

validate_password_policy=0

validate_password_length=6

[root@host50 ~]# mysql -uroot -p123456

mysql> change master to

-> master_host="192.168.0.10",

-> master_user="repluser",

-> master_password="123456",

-> master_log_file="host10-bin.00000x",

-> master_log_pos=xxx;

mysql> start slave;

mysql> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.0.10

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: host10-bin.000003

Read_Master_Log_Pos: 154

Relay_Log_File: host50-relay-bin.000002

Relay_Log_Pos: 321

Relay_Master_Log_File: host10-bin.000003

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

#########################################################################

配置数据库10~50免密登录

数据库10配置

[root@host10 ~]# ssh-keygen

Generating public/private rsa key pair.

Enter file in which to save the key (/root/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /root/.ssh/id_rsa.

Your public key has been saved in /root/.ssh/id_rsa.pub.

The key fingerprint is:

SHA256:dIDjLbbkeeio53FnJco8Kz4HeMILNfz4atQ5MqR97jc root@host10

The key's randomart image is:

+---[RSA 2048]----+

| .. |

| o . |

| . . o. . |

| = =... |

| * * = =S . |

|o X Oo=..o |

| o X.=*.o |

| o BoE= |

| .oB+=.. |

将密钥拷贝给其他数据库主机

[root@host10 ~]# ssh-copy-id 192.168.0.20 【20~50】

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"

/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys

root@192.168.0.20's password:

Number of key(s) added: 1

Now try logging into the machine, with: "ssh '192.168.0.20'"

and check to make sure that only the key(s) you wanted were added.

数据库20配置

[root@host20 ~]# ssh-keygen

Generating public/private rsa key pair.

Enter file in which to save the key (/root/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /root/.ssh/id_rsa.

Your public key has been saved in /root/.ssh/id_rsa.pub.

The key fingerprint is:

SHA256:L+JXzsVEzdtyYCv/0/13q+aiTASD1q3F6jGhjUI9waU root@host20

The key's randomart image is:

+---[RSA 2048]----+

| .... o |

| ..= o . = |

| . E = + . . = |

| . . = B o + o|

| . o *S. o o o |

| . . +.. o . |

| ...=.. .o|

| . .+.o. . .*|

| .. o. +o..B|

+----[SHA256]-----+

将密钥拷贝给其他数据库主机

[root@host20 ~]# ssh-copy-id 192.168.0.10 【10~50】//不包括本机20

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"

The authenticity of host '192.168.0.10 (192.168.0.10)' can't be established.

ECDSA key fingerprint is SHA256:JteE8m/SscxjDXKqWv/kZ61JR8uQ5VCoUQrFqLI9ZiU.

ECDSA key fingerprint is MD5:fd:dc:1e:a5:fa:d9:43:64:06:e1:a7:c7:e8:61:fe:9d.

Are you sure you want to continue connecting (yes/no)? yes

/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys

root@192.168.0.10's password:

Number of key(s) added: 1

Now try logging into the machine, with: "ssh '192.168.0.10'"

and check to make sure that only the key(s) you wanted were added.

数据库30配置

[root@host30 ~]# ssh-keygen

Generating public/private rsa key pair.

Enter file in which to save the key (/root/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /root/.ssh/id_rsa.

Your public key has been saved in /root/.ssh/id_rsa.pub.

The key fingerprint is:

SHA256:BCHsXS4jc2TbbH/q2QB1JvKxw0TuPVaz4i5SDXmklmg root@host30

The key's randomart image is:

+---[RSA 2048]----+

| .. o. . |

| ..o..o . |

| . + *o.*=o o |

| + *.EB*B.. o |

| + =So*+= . |

| .o+oo |

| ..o. |

| . o= |

| oo.o |

+----[SHA256]-----+

将密钥拷贝给其他数据库主机

[root@host30 ~]# ssh-copy-id 192.168.0.10【10~50】//不包括本机30

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"

The authenticity of host '192.168.0.10 (192.168.0.10)' can't be established.

ECDSA key fingerprint is SHA256:JteE8m/SscxjDXKqWv/kZ61JR8uQ5VCoUQrFqLI9ZiU.

ECDSA key fingerprint is MD5:fd:dc:1e:a5:fa:d9:43:64:06:e1:a7:c7:e8:61:fe:9d.

Are you sure you want to continue connecting (yes/no)? yes

/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys

root@192.168.0.10's password:

Number of key(s) added: 1

Now try logging into the machine, with: "ssh '192.168.0.10'"

and check to make sure that only the key(s) you wanted were added.

数据库40配置

[root@host40 ~]# ssh-keygen

Generating public/private rsa key pair.

Enter file in which to save the key (/root/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /root/.ssh/id_rsa.

Your public key has been saved in /root/.ssh/id_rsa.pub.

The key fingerprint is:

SHA256:HXVcGCntTQjeA9DaY8IHzH+0/l0FV5ia08mpK2j6ahw root@host40

The key's randomart image is:

+---[RSA 2048]----+

| o.o++oO+|

| +oo+@ o|

| ..=.OoO |

| .+.X O.o|

| S .+ * .|

| E . . .|

| . . . . .o|

| o o . . o|

| .o=. . |

+----[SHA256]-----+

将密钥拷贝给其他数据库主机

[root@host40 ~]# ssh-copy-id 192.168.0.10【10~50】//不包括本机40

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"

The authenticity of host '192.168.0.10 (192.168.0.10)' can't be established.

ECDSA key fingerprint is SHA256:JteE8m/SscxjDXKqWv/kZ61JR8uQ5VCoUQrFqLI9ZiU.

ECDSA key fingerprint is MD5:fd:dc:1e:a5:fa:d9:43:64:06:e1:a7:c7:e8:61:fe:9d.

Are you sure you want to continue connecting (yes/no)? yes

/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys

root@192.168.0.10's password:

Number of key(s) added: 1

Now try logging into the machine, with: "ssh '192.168.0.10'"

and check to make sure that only the key(s) you wanted were added.

数据库50配置

[root@host50 ~]# ssh-keygen

Generating public/private rsa key pair.

Enter file in which to save the key (/root/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /root/.ssh/id_rsa.

Your public key has been saved in /root/.ssh/id_rsa.pub.

The key fingerprint is:

SHA256:gPFftDIJLZazlUIfa052MJf7WnqNrHqg9Vfx8+x+WuI root@host50

The key's randomart image is:

+---[RSA 2048]----+

| ..oo+.o. |

| +*o+O.. |

| ..o*X +. |

| .B =. . |

| S . o |

| o o ...|

| o o = +..+|

| . = =..o+|

| .o.+ E++|

+----[SHA256]-----+

将密钥拷贝给其他数据库主机

[root@host50 ~]# ssh-copy-id 192.168.0.10【10~40】//不包括本机50

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"

The authenticity of host '192.168.0.10 (192.168.0.10)' can't be established.

ECDSA key fingerprint is SHA256:JteE8m/SscxjDXKqWv/kZ61JR8uQ5VCoUQrFqLI9ZiU.

ECDSA key fingerprint is MD5:fd:dc:1e:a5:fa:d9:43:64:06:e1:a7:c7:e8:61:fe:9d.

Are you sure you want to continue connecting (yes/no)? yes

/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys

root@192.168.0.10's password:

Number of key(s) added: 1

Now try logging into the machine, with: "ssh '192.168.0.10'"

and check to make sure that only the key(s) you wanted were added.

配置管理主机100免密登录所有数据库服务器

[root@host100 ~]# ssh-keygen

Generating public/private rsa key pair.

Enter file in which to save the key (/root/.ssh/id_rsa):

Created directory '/root/.ssh'.

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /root/.ssh/id_rsa.

Your public key has been saved in /root/.ssh/id_rsa.pub.

The key fingerprint is:

SHA256:DtTiufp276HhOGEN3UXamTucGe5Wb/E+bvuzkIo55cI root@host100

The key's randomart image is:

+---[RSA 2048]----+

| .. |

| . o.o |

| o.....= |

| o.o. .o = |

| +oS B .. |

| o+. o o..o|

| ...+ + oo +|

| .oo.Eo+. .* |

| .oooo==. o+O|

+----[SHA256]-----+

将密钥拷贝给其他数据库主机

[root@host100 ~]# ssh-copy-id 192.168.0.10【10~50】//不包括本机100

/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: "/root/.ssh/id_rsa.pub"

The authenticity of host '192.168.0.10 (192.168.0.10)' can't be established.

ECDSA key fingerprint is SHA256:JteE8m/SscxjDXKqWv/kZ61JR8uQ5VCoUQrFqLI9ZiU.

ECDSA key fingerprint is MD5:fd:dc:1e:a5:fa:d9:43:64:06:e1:a7:c7:e8:61:fe:9d.

Are you sure you want to continue connecting (yes/no)? yes

/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed

/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys

root@192.168.0.10's password:

Number of key(s) added: 1

Now try logging into the machine, with: "ssh '192.168.0.10'"

and check to make sure that only the key(s) you wanted were added.

在每个主机进行测试免密登录是否成功

如果在进行密钥传输时报如下错误请使用ssh-keygen -R "你的远程服务器ip地址" 目的是清除你当前机器里关于你的远程服务器的缓存和公钥信息，注意是大写的字母“R”

ECDSA host key for 192.168.0.50 has changed and you have requested strict checking.

在20数据库授权同步用户

grant replication slave on *.* to repluser@"%" identified by "123456";

在30数据库授权同步用户

grant replication slave on *.* to repluser@"%" identified by "123456";

在10数据库启用不自动删除中继日志

mysql> set global relay_log_purge=off;

mysql> show variables like "relay_log_purge"; //查看变量状态

在20数据库启用不自动删除中继日志

mysql> set global relay_log_purge=off;

在30数据库启用不自动删除中继日志

mysql> set global relay_log_purge=off;

在40数据库启用不自动删除中继日志

mysql> set global relay_log_purge=off;

在50数据库启用不自动删除中继日志

mysql> set global relay_log_purge=off;

#################################################################################################

安装软件包

在所有主机上安装Perl依赖包【10~100】

[root@host10 ~]# cd mha-soft-student/

[root@host10 mha-soft-student]# yum -y install perl-*.rpm

在所有数据库服务器上安装mha-node包【10~50】

先安装需要的依赖包

[root@host10 mha-soft-student]# yum -y install perl-DBD-mysql perl-DBI

用rpm安装mha-node包

[root@host10 mha-soft-student]# rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm

在管理主机100上安装mha_node 和 mha-manager包

yum -y install perl-DBD-mysql perl-DBI

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

[root@host100 mha-soft-student]# yum install perl-ExtUtils-*

[root@host100 mha-soft-student]# yum install perl-CPAN-*

解压manager源码

[root@host100 mha-soft-student]# tar -xf mha4mysql-manager-0.56.tar.gz

进入源码目录

[root@host100 mha-soft-student]# cd mha4mysql-manager-0.56

用Makefile.PL检测环境

[root@host100 mha4mysql-manager-0.56]# perl Makefile.PL

...

*** Module::AutoInstall configuration finished. //配置完成提示

编译make

[root@host100 mha4mysql-manager-0.56]# make

安装make install

[root@host100 mha4mysql-manager-0.56]# make install

将命令链接到PATH变量路径下

[root@host100 mha4mysql-manager-0.56]# ln -s /root/mha-soft-student/mha4mysql-manager-0.56/bin/ /root/

创建MHA工作目录

[root@host100 mha4mysql-manager-0.56]# mkdir /etc/mha_manager

拷贝MHA模板文件

[root@host100 mha4mysql-manager-0.56]# cp samples/conf/app1.cnf /etc/mha_manager/

修改模板文件

[root@host100 mha4mysql-manager-0.56]# vim /etc/mha_manager/app1.cnf

[server default] #本机配置

manager_workdir=/etc/mha_manager #指定工作目录

manager_log=/etc/mha_manager/manager.log #指定日志文件位置

master_ip_failover_script=/etc/mha_manager/master_ip_failover #指定故障脚本位置

ssh_user=root #指定ssh用户

ssh_port=22 #指定端口

repl_user=repluser #指定主从同步用户

repl_password=123456 #指定密码

user=root #指定监控用户

password=123456 #指定密码

[server1]

hostname=192.168.0.10 #数据库IP地址

candidate_master=1 #竞选主库

[server2]

hostname=192.168.0.20 #数据库IP地址

candidate_master=1 #竞选主库

[server3]

hostname=192.168.0.30 #数据库IP地址

candidate_master=1 #竞选主库

[server4]

hostname=192.168.0.40 #数据库IP地址

no_master=1 #不竞选主库

[server5]

hostname=192.168.0.50 #数据库IP地址

no_master=1 #不竞选主库

拷贝故障切换脚本（续修改故障切换脚本）

[root@host100 samples]# cp scripts/master_ip_failover /etc/mha_manager/

检测配置（检测配置是需先将配置文件中故障切换脚本行先注释掉）

检测ssh配置

[root@host100 ~]# masterha_check_ssh --conf=/etc/mha_manager/app1.cnf

检测主从同步配置

[root@host100 ~]# masterha_check_repl --conf=/etc/mha_manager/app1.cnf

为主库绑定VIP地址

[root@host10 ~]# ifconfig ens32:1 192.168.0.110/24

启动服务（启动服务前把故障切换脚本行注释打开）

[root@host100 ~]# masterha_manager --conf=/etc/mha_manager/app1.cnf --remove_dead_master_conf --ignore_last_failover

解释：--remove_dead_master_conf 主库宕机后从配置文件中移除

解释：--ignore_last_failover 忽略健康文件

手动关闭主数据库服务测试

[root@host10 ~]# systemctl stop mysqld

查看VIP

[root@host10 ~]# ip a s ens32

查看其他备用主库是否拥有VIP

[root@host20 ~]# ip a s

inet 192.168.0.110/24 brd 192.168.0.255 scope global secondary ens32:1