MySQL集群架构-MHA

MySQL 主从复制搭建

1.安装MySQL

MySQL CentOS 7 安装教程_weixin_44615693的博客-CSDN博客

2.搭建Master

2.1 修改my.cnf 文件

cd /etc

vi my.cnf

2.2 在my.cnf 中添加如下配置

log_bin=mysql_bin #开启bin_log
server-id=1 #设置服务id
sync-binlog=1 # 每次执行写入性操作就与磁盘同步，可不加
binlog-ignore-db=information_schema # 设置不同步的库，可不加
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
binlog-do-db=mysql # 设置同步的库，可不加

2.3 重启MySQL服务

systemctl restart mysqld

2.4 登录MySQL

mysql -uroot -proot

2.5 给从库授权

grant replication slave on *.* to 'root'@'%' identified by 'root';

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

2.6 刷新权限

flush privileges;

2.7 查看master 状态

show master status;

3.搭建从库Slave

3.1 修改my.cnf 文件

cd /etc

vi my.cnf

3.2 在my.cnf 添加如下配置

server-id=2 # 指定serverid
relay_log=mysql-relay-bin # 中继日志
read_only=1 # 设置只读

3.3 重启MySQL

systemctl restart mysqld 

3.4 登录MySQL

mysql -uroot -proot

3.5 查看当前数据库状态

show slave status;

3.6 同步初始化设置

change master to master_host='1.13.24.56',master_port=3306,master_user='root',master_password='root',master_log_file='mysql_bin.000001',master_log_pos=853; 

3.7 启动

start slave;

4.半同步复制

4.1.1 登录master MySQL，查看是否支持动态插件

select @@have_dynamic_loading;

4.1.2 查看当前MySQL已下载的插件

show plugins;

4.1.3 安装 rpl_semi_sync_master 插件

install plugin rpl_semi_sync_master soname 'semisync_master.so';

4.1.4 查看semi 插件信息

show variables like '%semi%';

4.1.5 在/etc/my.cnf 中添加配置

rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

4.1.6 重启MySQL查看配置是否生效

4.2 Slave数据库配置

4.2.1 安装rpl_semi_sync_slave 插件

install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

4.2.2 修改/etc/my.cnf 

rpl_semi_sync_slave_enabled=1

4.2.3 重启MySQL

systemctl restart mysqld

4.2.4 查看是否生效

show variables like '%semi_sync_slave%';

5. MHA 

5.1 安装MHA mannager 、node

Release mha4mysql-manager-0.58 · yoshinorim/mha4mysql-manager · GitHub

https://github.com/yoshinorim/mha4mysql-node/releases/tag/v0.58

node 安装步骤

yum install -y perl-DBD-MySQL
# 在Centos中使用iputils软件包来安装arping
yum install -y iputils 
yum install -y perl-ExtUtils-CBuilder 
yum install -y perl-ExtUtils-MakeMaker
yum install -y perl-CPAN 
yum install -y perl-Config-Tiny
yum install -y perl-Log-Dispatch
yum install -y perl-Parallel-ForkManager

tar -zxvf ./mha4mysql-node-0.58.tar.gz
cd ./mha4mysql-node-0.58
perl Makefile.PL 
make 
make install

mannager 安装步骤

tar -zxvf ./mha4mysql-manager-0.58.tar.gz
cd ./mha4mysql-manager-0.58
perl Makefile.PL 
make 
make install 

注意：MHA服务器上需要同时安装mannager 、node，node需要先安装

5.2 集群中所有服务器配置ssh免登陆认证

5.2.1 生成密钥

ssh-keygen

5.2.2 使用ssh-copy-id将秘钥拷贝到各个服务器上

master

ssh-copy-id -i /root/.ssh/id_rsa.pub -p 22 root@1.13.180.72  //slave1
ssh-copy-id -i /root/.ssh/id_rsa.pub -p 22 root@1.13.180.86 //slave2
ssh-copy-id -i /root/.ssh/id_rsa.pub -p 22 root@1.13.180.109  //mha manager

Slave1

ssh-copy-id -i /root/.ssh/id_rsa.pub -p 22 root@1.13.169.75  //master
ssh-copy-id -i /root/.ssh/id_rsa.pub -p 22 root@1.13.180.86 //slave2
ssh-copy-id -i /root/.ssh/id_rsa.pub -p 22 root@1.13.180.109  //mha manager

Slave2

ssh-copy-id -i /root/.ssh/id_rsa.pub -p 22 root@1.13.169.75  //master
ssh-copy-id -i /root/.ssh/id_rsa.pub -p 22 root@1.13.180.72 //slave1
ssh-copy-id -i /root/.ssh/id_rsa.pub -p 22 root@1.13.180.109  //mha manager

MHA

ssh-copy-id -i /root/.ssh/id_rsa.pub -p 22 root@1.13.169.75  //master
ssh-copy-id -i /root/.ssh/id_rsa.pub -p 22 root@1.13.180.72  //slave1
ssh-copy-id -i /root/.ssh/id_rsa.pub -p 22 root@1.13.180.86 //slave2

5.3 配置MHA

5.3.1 在集群所有服务器中都先建立一个mha工作目录

mkdir -p /etc/mysql_mha/

Mannage服务器添加配置文件

vim /etc/mysql_mha/mha.cnf 

[server default]
#mha用户 
user=mha
password=mharoot
#manager工作目录
manager_workdir=/etc/mysql_mha/
#manager的日志
manager_log=/etc/mysql_mha/manager.log
# Mysql在发生切换时二进制日志文件的保存位置
remote_workdir=/etc/mysql_mha

#master服务器保存二进制日志文件的位置，需要跟master服务器上的文件路径一致，不然会报错
master_binlog_dir=/var/lib/mysql
# 自动故障切换时master服务器IP地址切换脚本，如果没有则不写此项
# master_ip_online_change_script=/usr/local/mha4mysql-manager-0.58/samples/scripts/master_ip_online_change
# 监控主库时发送ping包的时间间隔，默认为3秒，如果连续3次没有回应将自动进行故障切换
ping_interval=1

# secondary_check_script= masterha_secondary_check -s remote_host1 -s remote_host2
# master_ip_failover_script= /script/masterha/master_ip_failover
# shutdown_script= /script/masterha/power_manager
# report_script= /script/masterha/send_report
# master_ip_online_change_script= /script/masterha/master_ip_online_change

#SSH登录用户
ssh_user=root
#复制用户
repl_user=root
repl_password=root

# 监控用户
user=root
# 监控用户的密码
password=root
         
[server1] 
hostname=1.13.169.86
candidate_master=1
[server2]
hostname=1.13.180.82
# 候选master，主从切换后升为master
candidate_master=1
# 默认情况下，如果一个slave的中转日志落后master二进制日志100M，那么MHA就不会选择该slave作为新的master。
# 但设置此项后MHA会忽略复制延迟，对候选master非常有用。
check_repl_delay=0
[server3]
hostname=1.13.180.109
candidate_master=1 
check_repl_delay=0

Master 服务器配置

# For advice on how to change settings please see

# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
log_bin=mysql_bin
server-id=1
sync-binlog=1
#开启gtid
##一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致
#gtid_mode=on
#enforce_gtid_consistency=1
# relay_log配置
relay_log=mysql-relay-bin
log_slave_updates=1
relay_log_purge=0
# 过滤掉不需要同步的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 半同步复制
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock


# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Slave服务器配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
log_bin=mysql_bin
server-id=2
sync-binlog=1
# 中继日志
relay_log=mysql-relay-bin
# 只读
read_only=1
log_slave_updates=1
relay_log_purge=0
# 过滤掉不需要同步的数据库
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=sys
# 开启半同步
plugin_load="rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1
rpl_semi_sync_master_timeout=1000
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M  
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock 

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

由于mha文件中用户名是mha ，所以要授权

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

flush privileges; 

检查ssh免密登陆

masterha_check_ssh --conf=/etc/mysql_mha/mha.cnf

检查主从复制环境

masterha_check_repl --conf=/etc/mysql_mha/mha.cnf

开启节点监控

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

查看管理节点状态

masterha_check_status --conf=/etc/mysql_mha/mha.cnf