mysql主备 + vip的高可用架构

适用场景

初始化mysql集群主备，双库均为空库

mysql基本配置

# 准备目录mkdir -p /data/mysql/conf.dmkdir -p /data/mysql/data# 准备配置文件/data/mysql/conf.d/my.cnf# 主库：[mysqld]## 设置server_id，一般设置为IP，同一局域网内注意要唯一server_id=100## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）binlog-ignore-db=mysql## 开启二进制日志功能log-bin=edu-mysql-bin## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存binlog_cache_size=1M## 主从复制的格式（mixed,statement,row，默认格式是statement）binlog_format=mixed## 二进制日志自动删除/过期的秒数。binlog_expire_logs_seconds=604800## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致replica_skip_errors=1062max_connections=50000# 备库：[mysqld]server_id=101## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）binlog-ignore-db=mysql## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用log-bin=edu-mysql-slave1-bin# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存binlog_cache_size=1M## 主从复制的格式（mixed,statement,row，默认格式是statement）binlog_format=mixed## 二进制日志自动删除/过期的秒数。binlog_expire_logs_seconds=604800## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致replica_skip_errors=1062## relay_log配置中继日志relay_log=edu-mysql-relay-bin## log_slave_updates表示slave将复制事件写进自己的二进制日志log_replica_updates=1## 防止改变数据(除了特殊的线程)read_only=1super_read_only=1max_connections=50000

docker启动mysql

docker run --privileged -u 0 -d --name mysql --network host --restart always-v /data/mysql/conf.d:/etc/mysql/conf.d -v /data/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=xxxx -e TZ=Asia/Shanghai mysql:8.0.28

mysql主备配置

主库配置：

CREATE USER 'slave'@'%' IDENTIFIED BY 'lulu2022';GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%'; flush privileges;

主库上获取master状态，部分信息用于下面初始化从库：

show master status;| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set ||------------------------|----------|--------------|------------------|-------------------|| 'edu-mysql-bin.000004' | '704'| '' | 'mysql'| ''|

从库配置：这里master_log_file和master_log_pos就是从上面读的：

设置主库信息：change master to master_host='master-ip', master_user='slave', master_password='lulu2022', master_port=3306, master_log_file='edu-mysql-bin.000004', master_log_pos=704, master_connect_retry=30, GET_MASTER_PUBLIC_KEY=1 ;启动主备：start slave;

验证从库是否正常：show slave status \G;

*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.131.141Master_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: master-a-bin.000002Read_Master_Log_Pos: 1545Relay_Log_File: linux001-relay-bin.000010Relay_Log_Pos: 320Relay_Master_Log_File: master-a-bin.000002Slave_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: 1545Relay_Log_Space: 696Until_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: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: bc1e2413-b8d5-11ec-a822-000c299e074dMaster_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)ERROR:No query specified1、看slave是否正常进行：Slave_IO_Running: YesSlave_SQL_Running: YesSeconds_Behind_Master: 02、看salve内容是否正确Master_Log_File: master-a-bin.000002Read_Master_Log_Pos: 1545Relay_Log_File: linux001-relay-bin.000010

keepalived vip配置

check脚本： /etc/keepalived/check.sh，master和node都一样

#!/bin/shif [ $(ps -C mysqld --no-header | wc -l) -eq 0 ]; then echo mysql stoped exit 1else exit 0fi

主库keepalived配置：

! Configuration File for keepalivedglobal_defs { router_id LVS_DEVEL script_user root enable_script_security}vrrp_script chk_mysql {script "/etc/keepalived/check.sh"weight -10interval 2fall 3rise 2}vrrp_instance VI_1 {state MASTERinterface eth0virtual_router_id 111priority 101advert_int 1mcast_src_ip main-node-ipauthentication {auth_type PASSauth_pass 1111}virtual_ipaddress {mysql-sluster-vip}track_script {chk_mysql}}

备库keepalived配置：

! Configuration File for keepalivedglobal_defs { router_id LVS_DEVEL script_user root enable_script_security}vrrp_script chk_mysql {script "/etc/keepalived/check.sh"weight -10interval 2fall 3rise 2}vrrp_instance VI_1 {state BACKUPinterface eth0virtual_router_id 111priority 100advert_int 1mcast_src_ip slave-node-ipauthentication {auth_type PASSauth_pass 1111}track_script {chk_mysql}virtual_ipaddress {mysql-sluster-vip}}

实现效果

  • 1、主从均正常时，访问vip可以直接到达主库进行处理

  • 2、当主库异常时，此时vip飘到备库节点，依旧可以访问，但只读不可写

  • 3、当主库恢复时，此时vip飘回主库，读写均恢复