MySQL主从复制搭建
注:MySQL安装
yum下载地址:
MySQL :: Download MySQL Yum Repositoryhttps://dev.mysql.com/downloads/repo/yum/
rpm -ivh mysql80-xxxx-xxxx-xxxx.rpm
cd /etc/yum.repoos.d #查看有无mysql的repo文件
yum install mysql-server* #直接安装MySQL
初始化一下,直接开始步入正题
#### 一、部署mysql主从复制(一主两从)
环境:#由于资源有限,mha-manager跟mysql的master节点放一起
master:192.168.1.133
node1:192.168.1.134
node2:192.168.1.135
服务器节点 | 部署内容 | 主机名 | IP地址 |
mha-manager管理节点 | mha-manager | master | 192.168.1.133 |
mysql-master | mysql+mha-node | master | 192.168.1.133 |
mysql-node1 | mysql+mha-node | slave1 | 192.168.1.134 |
mysql-node2 | mysql+mha-node | slave2 | 192.168.1.135 |
mysql版本:5.7.43
##### 1、准备工作
注:mysql安装略过
###### 1.1、三个节点时间同步
自己配置
###### 1.2、三节点互相实现免密ssh通信(mha准备)
ssh-keygen #生成密钥文件,密钥默认在root目录下./ssh
ssh-copy-id “ip地址” #mha部署有详细免密过程,可先不操作
##### 2、主节点配置
vim /etc/my.cnf
server-id 1 #节点的server-id
log-binmaster-bin #开启主节点
binglog_format MIXED #开启二进制日志
log-slave-updatestrue #允许slave从master复制数据时可以写入到自己的二进制日志
systemctl restart mysqld #重启
mysql -u root -p
GRANT REPLICATION SLAVE ON . TO 'myslave'@'192.168.1.%' IDENTIFIED BY 'Admin@123'; #添加授权用户
GRANT ALL PRIVILEGES ON . TO 'myslave'@'192.168.1.%' IDENTIFIED BY 'Admin@123' WITH GRANT OPTION; #给授权用户添加权限
FLUSH PRIVILEGES; #刷新
mysql> show master status; #查看master状态,从节点配置需要用
##### 3、从节点配置
###### 3.1、vim /etc/my.cnf
server-id 2 #server-id
relay-logrelay-log-bin #开启中继日志
relay-log-indexslave-relay-bin.index #自定义二进制文件
relaylogrecovery 1 #
###### 3.2、配置slave
stop slave; #关闭slave
reset slave; #重置slave
#change master to masterhost='192.168.1.133',masteruser='myslave',masterpassword='Admin@123',masterlogfile='master-bin.000008',masterlog_pos=154;
#change master to masterhost='master主机IP',masteruser='授权用户',masterpassword='授权用户密码',masterlogfile='“1.1中file文件名”',masterlog_pos="1.1中position数值";
#上步骤为指定master,开启主从复制
show slave status; #查看slave状态,(master主机、二进制文件、开始数值等信息)
以下内容:
Master_Host: 192.168.1.133
Master_User: myslave
Master_Port: 3306
MasterLogFile: master-bin.000008
ReadMasterLog_Pos: 154
SlaveIORunning: Yes
SlaveSQLRunning: Yes
##### 4、测试
master创建测试库,查看node1、node2节点是否有同步
MHA搭建
#### 二、部署MHA (manager+node)
方案:三台机器做主备,主节点部署manager,监控mysql用户和复制用户共用一个
mha版本:0.58
##### 1、master节点部署mha的manager
###### 1.1、安装部署依赖包
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Net-SSH2 perl-Proc-Daemon perl-IO-Socket-SSL perl-Module-Install.noarch libconfig
###### 1.2、部署manager
1.2.1、解压缩编译安装
解压
tar -zxvf mha4mysql-manager-0.58.tar.gz
切换到安装目录
cd mha4mysql-manager-0.58
Perl编译
perl Makefile.PL
安装
make && make install
1.2.2、配置文件处理
mkdir /etc/mha
cp /opt/mha4mysql-manager-0.58/samples/conf/app1.cnf /etc/mha/
vim /etc/mha/app1.cnf
[server default]
manager_log=/var/log/masterha/app1/manager.log #manager的日志目录(需手动创建)
manager_workdir=/var/log/masterha/app1 #manager的工作目录
master_binlog_dir=/var/lib/mysql #存放mysql的binlog文件的目录
master_ip_failover_script=/usr/local/bin/master_ip_failover #故障自动切换所用的脚本**重要**
master_ip_online_change_script=/usr/local/bin/master_ip_online_change #手动切换时的脚本
password=Admin@123 #监控用户密码
ping_interval=2 #ping检测的时间(2:每两秒)
repl_password=Admin@123 #复制用户的密码
repl_user=myslave #复制mysql的用户
secondary_check_script=/usr/local/bin/masterha_secondary_check -s 192.168.1.134 -s 192.168.1.135 #指定要检查的从服务器的ip
shutdown_script="" #照抄
ssh_user=root #ssh连接用户
user=myslave #监控mysql状态的用户:最低需要status权限,一般和复制用户共用一个
[server1] #节点
hostname192.168.1.133 #ip
port3306 #端口号
[server2] #node1节点
candidate_master1
check_repl_delay0
hostname192.168.1.134 #ip
port3306 #端口号
[server3] #node2节点
hostname192.168.1.135 #ip
port3306 #端口号
1.2.3、修改脚本文件
将安装目录中的脚本复制到/usr/local/bin/目录
cp /opt/mha4mysql-manager-0.58/samples/scripts/masteripfailover /usr/local/bin/
cp /opt/mha4mysql-manager-0.58/samples/scripts/masteriponline_change /usr/local/bin/
vim masteripfailover
#注:0.58版本的masteripfailover脚本中并没有创建虚拟ip这部分功能,需要修改或手动添加
my $vip '192.168.1.111'; #虚拟ip地址
my $brdc '192.168.1.255'; #广播地址
my $ifdev 'ens33'; #网卡
my $key '1';
my $ssh_start_vip "/sbin/ifconfig $ifdev:$key $vip";
my $ssh_stop_vip "/sbin/ifconfig $ifdev:$key down";
my $exit_code 0;
1.2.4、免密 #自己ip的也要做
#在manager节点上配置到所有数据库节点的无密码认证
ssh-keygen
ssh-copy-id 192.168.1.133
ssh-copy-id 192.168.1.134
ssh-copy-id 192.168.1.135
##### 2、部署node节点(node1为例)
###### 2.1、解压缩编译安装
tar -zxvf mha4mysql-node-0.58.tar.gz
cd mha4mysql-manager-0.58
perl Makefile.PL
make && make install
###### 2.2、免密
#在node1节点上配置到所有数据库节点的无密码认证
ssh-keygen
ssh-copy-id 192.168.1.134
ssh-copy-id 192.168.1.133
ssh-copy-id 192.168.1.135
##### 3、进入mha的manager节点
masterhacheckssh --conf /etc/mha/app1.cnf #测试主从节点ssh连通性
vim /etc/mha/app1.cnf #故障切换主节点后,会将原master的配置删除,恢复需要重新写入
[server1]
hostname192.168.1.133
port3306
nohup masterhamanager --conf /etc/mha/app1.cnf --removedeadmasterconf --ignorelastfailover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 & #启动manager
##### 4、查看日志、脚本测试
masterhacheckstatus --conf/etc/mha/app1.cnf #查看当前主节点
[root@master mha]# masterhacheckstatus --conf/etc/mha/app1.cnf
app1 (pid:123425) is running(0:PING_OK), master:192.168.1.133
查看日志
故障恢复
## mysql恢复流程
#### 1、分别进入master、node1、node2 (全部开启)
##### 1.1、原master操作
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | BinlogDoDB | BinlogIgnoreDB | ExecutedGtidSet |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000008 | 154 | | | |
+-------------------+----------+--------------+------------------+-------------------+
##### 1.2、node1、node2操作
stop slave; #关闭slave
change master to masterhost='192.168.1.133',masteruser='myslave',masterpassword='Admin@123',masterlogfile='master-bin.000008',masterlog_pos=154;
#change master to masterhost='原master主机IP',masteruser='授权用户',masterpassword='授权用户密码',masterlogfile='“1.1中file文件名”',masterlog_pos="1.1中position数值";
#上步骤为指定master,开启主从复制,故障后从节点会变为master,这步为恢复步骤
start slave;
show slave status; #查看slave状态,(master主机、二进制文件、开始数值等信息)
以下内容:
Master_Host: 192.168.1.133
Master_User: myslave
Master_Port: 3306
MasterLogFile: master-bin.000008
ReadMasterLog_Pos: 154
SlaveIORunning: Yes
SlaveSQLRunning: Yes
#### 2、进入mha的manager节点
masterhacheckssh -conf=/etc/mha/app1.cnf #测试主从节点ssh连通性
vim /etc/mha/app1.cnf #故障切换主节点后,会将原master的配置删除,恢复需要重新写入
[server1]
hostname=192.168.1.133
port=3306
masterhacheckrepl -conf=/etc/mha/app1.cnf #测试mysql主从复制连通性(此过程时间较长)
nohup masterhamanager --conf=/etc/mha/app1.cnf --removedeadmasterconf --ignorelastfailover < /dev/null > /var/log/masterha/app1/manager.log 2>&1 & #启动manager
#### 3、恢复成功、查看日志、脚本测试
masterhacheckstatus --conf=/etc/mha/app1.cnf #查看当前主节点
[root@master mha]# masterhacheckstatus --conf=/etc/mha/app1.cnf
app1 (pid:123425) is running(0:PING_OK), master:192.168.1.133
查看日志
Checking the Status of the script.. OK
Wed Oct 18 13:14:05 2023 - [info] OK.
Wed Oct 18 13:14:05 2023 - [warning] shutdown_script is not defined.
Wed Oct 18 13:14:05 2023 - [info] Set master ping interval 2 seconds.
Wed Oct 18 13:14:05 2023 - [info] Set secondary check script: /usr/local/bin/masterhasecondarycheck -s 192.168.1.134 -s 192.168.1.135
Wed Oct 18 13:14:05 2023 - [info] Starting ping health check on 192.168.1.133(192.168.1.133:3306)..
Wed Oct 18 13:14:05 2023 - [info] Ping(SELECT) succeeded, waiting until MySQL doesn't respond..