1. 环境规划
主机名 | IP | 节点信息 | 数据版本 | 系统版本 |
db1 | 172.19.0.131 | mysql主节点 | 5.7.32 | CentOS7.9 |
db2 | 172.19.0.130 | mysql从节点 | 5.7.32 | CentOS7.9 |
db3 | 172.19.0.132 | mysql从节点 | 5.7.32 | CentOS7.9 |
MHA | 172.19.0.133 | MHA manager节点 | 0.58 | CentOS7.9 |
利用四台主机模拟3台mysql主从1台MHA。
2. 部署mysql主从复制(一主两从)
2.1###db1、db2、db3三台均安装mysql
(1)卸载mariadb
//查看是否安装mariadb
//卸载mariadb
(2)四台主机均关闭防火墙,修改所有节点主机名
[root@db1 ~]# systemctl stop firewalld
[root@db1 ~]# systemctl disable firewalld
[root@db1 ~]# setenforce 0
[root@db1 ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
[root@db1 opt]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
172.19.0.131 db1
172.19.0.130 db2
172.19.0.132 db3
172.19.0.133 mha
(3)下载及解压
#cd /opt //软件包都放在这里方便管理
#wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
# tar zxf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
(4)安装和配置
安装依赖
# yum install ncurses-devel autoconf -y
# groupadd mysql
# useradd -g mysql -s /sbin/nologin -d /usr/local/mysql -MN mysql
# mkdir /data/mysql/data -p
# chown -R mysql:mysql /data/mysql
# mv mysql-5.7.32-linux-glibc2.12-x86_64 /usr/local/
# ln -s /usr/local/mysql-5.7.32-linux-glibc2.12-x86_64 /usr/local/mysql
# chown -R mysql.mysql /usr/local/mysql
# cd /usr/local/mysql
//备份配置文件
# cp /etc/my.cnf /etc/my.cnf.bak
# >/etc/my.cnf
///编辑配置文件
# vi /etc/my.cnf
[client]
port = 3306
socket = /tmp/mysql.sock
[mysql]
no-auto-rehash
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/data
port = 3306
socket = /tmp/mysql.sock
log-error = error.log
slow_query_log_file = slow.log
character-set-server = utf8
open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000
lower_case_table_names =1
server_id=1
gtid_mode=on
enforce_gtid_consistency=on
#binlog
log_bin=master-binlog
log-slave-updates=1
binlog_format=row
#relay log
skip-slave-start=1
relay_log_purge=0 #关闭定期清除。中继日志的清除是由SQL线程定期清除,若使用MHA则不让定期清除,因为mha做数据补齐时可能会用到中继日志。
另外两台从服务器的 server_id分别设为server_id=2、server_id=3
(5)初始化mysql
# /usr/local/mysql/bin/mysqld --initialize ###初始化mysql
# grep 'temporary password' /data/mysql/data/error.log // 查看mysql密码
(6)复制启动脚本文件
#cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
(7)添加环境变量
#echo "export PATH=/usr/local/mysql/bin:$PATH">>/etc/profile
#source /etc/profile
(8)启动mysql
#service mysql start
(9)查看你mysql进程
#ps -ef | grep mysql
(10)登录mysql
# grep 'temporary password' /data/mysql/data/error.log // 查看mysql密码
#mysql -uroot -p
(11)修改mysql密码
mysql> alter user user() identified by 'test'; ///将密码改成test
Query OK, 0 rows affected (0.00 sec)
(12)登录mysql
# mysql -uroot -ptest
(13)主服务器上创建复制账户
[root@db1 tmp]# mysql -uroot -ptest
mysql> grant replication slave on *.* to 'repl'@'172.19.0.%' identified by '123456';
(14)在主服务器上备份全库
[root@db1 ~]# mysqldump -uroot -ptest --set-gtid-purged=OFF --master-data=2 --single-transaction -A > /opt/mysqlbak_`date +%Y%m%d`.sql
导入mysql主备份的mysql到从服务器上
/ 进入主服务器/opt文件夹查看备份的文件
[root@db1 opt]# scp /opt/mysqlbak_20210623.sql root@192.168.137.101:/opt/
[root@db1 opt]# scp /opt/mysqlbak_20210623.sql root@192.168.137.102:/opt/
[root@db2 ~]# mysql -uroot -ptest
mysql> source /opt/mysqlbak_20210603.sql;
[root@db3 ~]# mysql -uroot -ptest
mysql> source /opt/mysqlbak_20210603.sql;
配置从服务器上的连接
[root@db2 ~]# mysql -uroot -ptest
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.137.100',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='123456',
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION=1,
-> MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
启动slave,如果Slave_IO_Running与Slave_SQL_Running都是yes就代表从服务器配置成功。
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3. 授权MHA管理账户及配置节点间免密登录
mysql> grant all privileges on *.* to mha@'192.168.137.%' identified by '123456';
mysql> flush privileges;
配置节点间免密登录
[root@db1 opt]# ssh-keygen #一直回车
[root@db1 opt]# cd /root/.ssh/
[root@db1 .ssh]# mv id_rsa.pub authorized_keys
[root@db1 .ssh]# scp -r /root/.ssh 192.168.137.101:/root
[root@db1 .ssh]# scp -r /root/.ssh 192.168.137.102:/root
[root@db1 .ssh]# scp -r /root/.ssh 192.168.137.150:/root
登录各节点验证 //各节点均能切换
db1:
ssh 192.168.137.101
ssh 192.168.137.102
ssh 192.168.137.150
db2:
ssh 192.168.137.100
ssh 192.168.137.102
ssh 192.168.137.150
db3:
ssh 192.168.137.100
ssh 192.168.137.101
ssh 192.168.137.150
mha:
ssh 192.168.137.100
ssh 192.168.137.101
ssh 192.168.137.102
在mysql服务节点(db1,db2,db3)安装node: /MHA端也需要安装
在安装前先安装依赖软件
#yum install -y perl-DBD-MySQL
#yum install -y wget
#yum install -y perl*
下载安装
[root@db1 ~]# wget https://github.com/yoshinorim/mha4mysql-node/releases/download/v0.58/mha4mysql-node-0.58-0.el7.centos.noarch.rpm
[root@db1 ~]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
Preparing... ################################# [100%]
Updating / installing...
1:mha4mysql-node-0.58-0.el7.centos ################################# [100%]
创建命令软链接(mha调用mysql命令默认在/usr/bin下面,不做此步mha会报错) ///三台服务器都要创建
#ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
#ln -s /usr/local/mysql/bin/mysqlbinlog /usr/local/bin/mysqlbinlog
4. 在管理节点(mha)上安装manage
安装必须软件
[root@mha ~]# yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
安装mha4mysql-node,mha4mysql-manager依赖于mha2mysql-node
[root@mha ~]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
安装mha4mysql-manager
[root@mha ~] wget https://github.com/yoshinorim/mha4mysql-manager/releases/download/v0.58/mha4mysql-manager-0.58-0.el7.centos.noarch.rpm
[root@mha ~]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm //与上相同
5. 配置MHA
全局配置文件
[root@mha ~]# mkdir -p /mesterha/manager
[root@mha ~]# cat /etc/masterha_default.cnf
manager_workdir=/masterha/manager
remote_workdir=/tmp
对主从复制的配置文件app1
[root@mha ~]# mkdir -p /masterha/app1
[root@mha ~]# cat /masterha/app1.cnf
[server default]
manager_log=/masterha/app1/manager.log
master_binlog_dir=/data/mysql/data
user=mha
password=123456
repl_user=repl
repl_password=123456
ssh_user=root
ping_interval=2
[server1]
hostname=192.168.137.100
port=3306
[server2]
hostname=192.168.137.101
port=3306
[server3]
hostname=192.168.137.102
port=3306
在mha上测试ssh连通性
[root@mha ~]# masterha_check_ssh --conf=/masterha/app1.cnf
在mha上检查主从复制状态:
[root@mha ~]# masterha_check_repl --conf=/masterha/app1.cnf
6. 启动MHA
[root@mha ~]# nohup masterha_manager --conf=/masterha/app1.cnf > /masterha/app1/manager.log 2>&1 &
[root@mha ~]# tail -f /masterha/app1/manager.log
7. MHA自动故障转移
指定自动转移节点,在app1.cnf添加如下参数(标红),当server1宕机,mha会根据candidate_master=1这个参数选择server2作为主节点,然后将server3挂到server2上进行重新架构,新主从。
[root@mha masterha]# cat /masterha/app1.cnf
[server default]
manager_log=/masterha/app1/manager.log
master_binlog_dir=/data/mysql/data
user=mha
password=123456
repl_user=repl
repl_password=123456
ssh_user=root
ping_interval=2
[server1]
hostname=172.19.0.131
port=3306
[server2]
hostname=172.19.0.130
port=3306
candidate_master=1
[server3]
hostname=172.19.0.132
port=3306
查看mha是否是启动状态
[root@mha masterha]# masterha_check_status --conf=/masterha/app1.cnf
app1 (pid:55156) is running(0:PING_OK), master:172.19.0.131
停止mha,重新启动mha,使新参数生效
[root@mha masterha]# masterha_stop --global_conf=/etc/masterha_default.cnf --conf=/masterha/app1.cnf
[root@mha ~]# nohup masterha_manager --conf=/masterha/app1.cnf > /masterha/app1/manager.log 2>&1 &
查看server2、server3是否正常
mysql> show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
模拟db1关机,查看mha是否有故障转移到db2上,同时查看mha上的manager.log。发现主已经转移到db2上了。
[root@mha masterha]# tail -f /masterha/app1/manager.log
在db2上查看
mysql> show slave status\G;
Empty set (0.00 sec)
ERROR:
No query specified
在db3上查看,Master_Host已变为192.168.137.101,说明主已转移成功
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.137.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 10
Master_Log_File: master-binlog.000004
Read_Master_Log_Pos: 234
Relay_Log_File: db3-relay-bin.000005
Relay_Log_Pos: 415
Relay_Master_Log_File: master-binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes