Mysql 高可用解决方案

1.环境说明

操作系统：centos7.7
主服务器：node2(192.168.1.102)
从服务器：node3(192.168.1.103)
keepalived中虚拟ip(VIP):192.168.1.100

2.准备事项

主库和从库数据库的版本一致
把主库的数据同步给从库一份

3.主库配置

3.1 编辑MySQL配置文件,开启二进制日志

Mysql：

[root@node2 /]# vim /etc/my.cnf

Mariadb：

[root@node2 /]# vim /etc/my.cnf.d/server.cnf

在下面添加如下配置

binlog-do-db=testdb
binlog-ignore-db=mysql
#启用二进制日志
log-bin=mysql-bin
#服务器唯一ID，与从数据库不重复即可
server-id=2

说明：binlog-do-db 需要同步的数据库 binlog-ignore-db 忽略同步的数据库

如果需要同步两个库的话,需要在添加一行

binlog-do-db=testdb2

3.2 重启mysql服务

Mysql：

[root@node2 /]# systemctl restart mysqld

Mariadb：

[root@node2 /]# systemctl restart mariadb

3.3 建立帐户并授权slave

[root@node2 /]# mysql -u root -proot

查看mysql的binlog是否开启

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.00 sec)

赋予用户FILE 权限，FILE 权限允许用户执行文件操作，如导入和导出数据。

mysql> GRANT FILE ON *.* TO 'root'@'%' IDENTIFIED BY 'root';

REPLICATION SLAVE 权限允许用户作为复制从服务器连接到主服务器并读取二进制日志，REPLICATION CLIENT 权限允许用户监控和管理复制过程

mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'root'@'%' identified by 'root';

刷新权限

mysql> FLUSH PRIVILEGES;

3.4 查询master的状态

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      863 | testdb       | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

注意：File 与Position参数需要记录下来,从数据库配置需要使用

4.从数据库配置

4.1 添加mysql配置

[root@node3 mysql]# vim /etc/my.cnf

在下面添加如下配置

server-id=3

说明：从数据库只需要配置server-id 即可，从库的server-id和主库的server-id必须不一致

4.2 重启mysql服务

[root@node3 mysql]# systemctl restart mysqld

4.3 配置从库

[root@node3 mysql]# mysql -u root -proot

mysql>change master to master_host='192.168.1.102',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=863;

说明：
master_port: mysql服务器端口号
master_user: 执行同步操作的数据库账户
master_password: 同步账号的密码
master_log_pos: 863 就是上面3.4中主数据库中 show master status 中的position对应的值
master_log_file: mysql-bin.000001 就是上面3.4中主数据库中 show master status中的file对应的值

4.4 启动从库复制功能

mysql> start slave;

4.5 检查从库复制功能状态

mysql> show slave status \G;
*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.1.102Master_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 863Relay_Log_File: node3-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 863Relay_Log_Space: 527Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 2Master_UUID: 393a9867-cc77-11ed-b111-000c29991e40Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 
1 row in set (0.00 sec)

注意：Slave_IO_Running和Slave_SQL_Running这两个服务必须保证正常

至此，Mysql主从复制的配置已完成，可进行测试

5.高可用配置

引入了keepalived，keepalived通过VIP虚拟IP的漂移实现高可用，在相同集群内发送组播包，master主通过VRRP协议发送组播包，告诉从主的状态。
当我们通过keepalived虚拟出一个ip服务192.168.1.100，应用服务只需要将mysql配置信息更改为192.168.1.100的配置信息，192.168.1.100实际代理的实体服务器为192.168.1.102或192.168.1.103。
当192.168.1.100实际代理192.168.1.102时，192.168.1.102服务出现故障后，keepalived通过自身机制，自动将192.168.1.103作为实际代理主服务器，不需要人工干预去修改mysql配置信息，从而实现了高可用

5.1 keepalived部署安装（主从操作一样）

[root@node2 software]$ wget -q http://www.keepalived.org/software/keepalived-1.2.13.tar.gz
[root@node2 software]$ tar xf keepalived-1.2.13.tar.gz
[root@node2 software]$ yum install -y gcc openssl-devel popt-devel
[root@node2 software]$ cd keepalived-1.2.13
[root@node2 keepalived-1.2.13]# ./configure && make && make install
[root@node2 keepalived-1.2.13]# cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
[root@node2 keepalived-1.2.13]# cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/
[root@node2 keepalived-1.2.13]# mkdir /etc/keepalived
[root@node2 keepalived-1.2.13]# cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/
[root@node2 keepalived-1.2.13]# cp /usr/local/sbin/keepalived /usr/sbin/
[root@node2 keepalived-1.2.13]# chkconfig --add keepalived
[root@node2 keepalived-1.2.13]# chkconfig --level 345 keepalived on

5.2 keepalived.conf配置

[root@node2 keepalived-1.2.13]# vim /etc/keepalived/keepalived.conf

5.2.1 主库配置

global_defs {router_id Nginx_HA_1
}vrrp_script check_run {script "/home/mysql/mysql_check.sh"interval 2
}vrrp_sync_group VG1 {group {VI_1}
}vrrp_instance VI_1 {state MASTERinterface ens32virtual_router_id 12priority 100advert_int 1nopreempt#本机ip
unicast_src_ip 192.168.1.102
unicast_peer {#对象IP  发送vrrp包给备服务器192.168.1.103
}authentication {auth_type PASSauth_pass nginxha}track_script {check_run}virtual_ipaddress {192.168.1.100}
}

5.2.2 从库配置

global_defs {router_id Nginx_HA_1
}vrrp_script check_run {script "/home/mysql/mysql_check.sh"interval 2
}vrrp_sync_group VG1 {group {VI_1}
}vrrp_instance VI_1 {state BACKUPinterface ens32virtual_router_id 12priority 50advert_int 1nopreempt#本机ip
unicast_src_ip 192.168.1.103
unicast_peer {#对象IP  发送vrrp包给备服务器192.168.1.102
}authentication {auth_type PASSauth_pass nginxha}track_script {check_run}virtual_ipaddress {192.168.1.100}
}

说明：
1.interface ens32 ->ens32是linux网卡名称
2.主服务 priority 参数必须大于从服务
3.script “/home/mysql/mysql_check.sh” mysql_check.sh是检测mysql是否正常的脚本如果不正常就关闭keepalived;

5.3 编写检查Mysql脚本(mysql_check.sh)

[root@node2 keepalived-1.2.13]# vim /home/mysql/mysql_check.sh

#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; thenservice keepalived stop
fi

主服务器和从服务器节点都要一份

#授权与格式转换
[root@node2 ~]# chmod -R 777  /home/mysql/mysql_check.sh    
[root@node2 ~]# sed -i "s/\r//" /home/mysql/mysql_check.sh

5.4 启动并测试

主从都启动

[root@node2 ~]# systemctl start keepalived

启动后可以进行状态查看

[root@node2 ~]# service keepalived status
● keepalived.service - SYSV: Start and stop KeepalivedLoaded: loaded (/etc/rc.d/init.d/keepalived; bad; vendor preset: disabled)Active: active (running) since 一 2024-02-26 22:18:06 CST; 14s agoDocs: man:systemd-sysv-generator(8)Process: 2901 ExecStart=/etc/rc.d/init.d/keepalived start (code=exited, status=0/SUCCESS)Main PID: 2908 (keepalived)CGroup: /system.slice/keepalived.service├─2908 keepalived -D├─2910 keepalived -D└─2911 keepalived -D

查看vip绑定到哪台机器上

[root@node2 /]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever
2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:99:1e:40 brd ff:ff:ff:ff:ff:ffinet 192.168.1.102/24 brd 192.168.1.255 scope global noprefixroute ens32valid_lft forever preferred_lft foreverinet 192.168.1.100/32 scope global ens32valid_lft forever preferred_lft foreverinet6 fe80::348f:8ac3:b2c:809d/64 scope link noprefixroute valid_lft forever preferred_lft forever

[root@node3 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00inet 127.0.0.1/8 scope host lovalid_lft forever preferred_lft foreverinet6 ::1/128 scope host valid_lft forever preferred_lft forever
2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000link/ether 00:0c:29:8e:59:08 brd ff:ff:ff:ff:ff:ffinet 192.168.1.103/24 brd 192.168.1.255 scope global noprefixroute ens32valid_lft forever preferred_lft foreverinet6 fe80::d26d:7d7a:6daa:f99b/64 scope link noprefixroute valid_lft forever preferred_lft forever

至此，MySQL高可用配置完成，现在访问虚拟IP(192.168.1.100)即可访问到MySQL主节点(192.168.1.102)

6.问题点

6.1 在配置mysql从服务器的时候如果出现以下问题

在这里插入图片描述
首先确定两台机器中my.cnf中server-id配置不一样

然后将其中一台机器auto.cnf文件备份
mv auto.cnf auto.cnf.bak
接着重启那台mysql,会自动创建auto.cnf

6.2 在启动keepalived的时候如果启动不起来

在这里插入图片描述
按照提示查看keepalived的状态

需要给缺失的动态库文件做软连接libssl.so.1.1(openssl组件)
ln -s /usr/local/lib64/libssl.so.1.1 /usr/lib64/libssl.so.1.1
ln -s /usr/local/lib64/libcrypto.so.1.1 /usr/lib64/libcrypto.so.1.1

6.3 在安装keepalived的时候需要安装openssl-devel这个包,安装的时候可能会出现如下错误

在这里插入图片描述
先按照上述提示执行

yum install -y openssl-devel --setopt=protected_multilib=false

然后再对keepalived进行编译安装
如果出现以下错误
在这里插入图片描述
需要把node2上的openssl-devel这个包以及所有的依赖全部下载下来

yumdownloader --resolve --destdir /root/chrome/  openssl-devel
Yumdownloader：只下载不安装
--resolve ：下载需要的依赖
--destdir ： 指定下载存放的目录
然后把所有的rpm包拷贝到另一台mysql所在节点
scp -r chrome/ root@node3:/app/module/