mysql主从配置
mysql8.0安装参考 https://www.jianshu.com/p/b3e7b27d0b00
主库配置修改并重启
vi /etc/my.cnf
server-id=1
log-bin=mysql-bin
relay-log=mysql-relay-bin
配置主库
# 配置slave同步账号
mysql> CREATE USER 'slave_repl'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave_repl'@'%';
mysql> flush privileges;
# 持有读锁，避免数据写入
mysql> flush tables with read lock;
# 查看master状态
mysql> show master status
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 2655 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
导出主库数据
# --source-data=1 导出master bin-log文件名和偏移量
mysqldump -uroot -p --source-data=1 --all-databases > all.sql
从库配置修改并重启
server-id=2
log-bin=mysql-bin
relay-log=mysql-relay-bin
配置从库
# 修改master信息， 这里可以不用配置 MASTER_LOG_FILE, MASTER_LOG_POS 因为导出的主库sql包含了该配置
mysql> CHANGE MASTER TO MASTER_HOST='10.20.10.1', MASTER_USER='slave_repl', MASTER_PASSWORD='123456', GET_MASTER_PUBLIC_KEY=1;
从库导入主库数据
mysql -uroot -p < all.sql
启动从库slave
# 启动slave
mysql> start slave;
# 查看slave状态是否正常
# Slave_IO_Running 和 Slave_SQL_Running 状态必须是 Yes，否则根据Error信息修复
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.20.10.1
Master_User: slave_repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 2655
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
....
反向主从配置
查看从库master status
mysql> show master status
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 2522189 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
配置主库master信息
# 配置10.1主库信息, 注意修改MASTER_HOST，MASTER_LOG_FILE，MASTER_LOG_POS为从库master status
mysql> CHANGE MASTER TO MASTER_HOST='10.20.10.2', MASTER_USER='slave_repl', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2522189, GET_MASTER_PUBLIC_KEY=1;
# 启动slave
mysql> start slave;
# 查看slave状态
# Slave_IO_Running 和 Slave_SQL_Running 状态必须是 Yes
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 10.20.10.2
Master_User: slave_repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 2522189
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
释放主库读锁
mysql> unlock tables;
半同步复制
主库从库安装插件
mysql> install plugin rpl_semi_sync_source soname 'semisync_source.so';
mysql> install plugin rpl_semi_sync_replica soname 'semisync_replica.so';
开启半同步复制
因为是双主，所以2台数据库设置操作是一样的
1.直接设置参数（临时)
mysql> set global rpl_semi_sync_source_enabled=1;
mysql> set global rpl_semi_sync_source_timeout=5000;
mysql> set global rpl_semi_sync_replica_enabled=1;
2.修改配置文件（永久）
vi /etc/my.cnf
[mysqld]
rpl_semi_sync_source_enabled=1
rpl_semi_sync_source_timeout=5000
rpl_semi_sync_replica_enabled=1
3.重启IO线程
mysql> stop replica io_thread;
mysql> stop replica io_thread;
4.查看半同步状态
Rpl_semi_sync_replica_status=on
Rpl_semi_sync_source_clients=1
Rpl_semi_sync_source_status=on
mysql> show global status like 'rpl%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_replica_status | ON |
| Rpl_semi_sync_source_clients | 1 |
| Rpl_semi_sync_source_net_avg_wait_time | 0 |
| Rpl_semi_sync_source_net_wait_time | 0 |
| Rpl_semi_sync_source_net_waits | 0 |
| Rpl_semi_sync_source_no_times | 0 |
| Rpl_semi_sync_source_no_tx | 0 |
| Rpl_semi_sync_source_status | ON |
| Rpl_semi_sync_source_timefunc_failures | 0 |
| Rpl_semi_sync_source_tx_avg_wait_time | 0 |
| Rpl_semi_sync_source_tx_wait_time | 0 |
| Rpl_semi_sync_source_tx_waits | 0 |
| Rpl_semi_sync_source_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_source_wait_sessions | 0 |
| Rpl_semi_sync_source_yes_tx | 0 |
+--------------------------------------------+-------
keepalived配置
主备安装keepalived
yum install -y keepalived
主配置keepalived
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
vrrp_script check_mysql_alive {
script "/etc/keepalived/check_mysql.sh"
interval 3
weight -20
}
global_defs {
router_id LVS_MYSQL # 运行keepalived机器的一个标识
}
vrrp_instance VI_1 {
interface eth0 # 设置实例绑定的网卡
state MASTER # 指定哪个为master，哪个为backup
virtual_router_id 123 # VPID标记，主备必须一样
priority 180 # 优先级，高优先级竞选为master
vrrp_unicast_bind 10.20.10.1 # 主ip
vrrp_unicast_peer 10.20.10.2 # 备ip
authentication {
auth_type PASS
auth_pass shenhao
}
virtual_ipaddress {
10.20.10.222 # 设置VIP，必须是同一网段虚拟IP
}
track_script {
check_mysql_alive # 存活检查
}
}
备keepalived配置
! Configuration File for keepalived
vrrp_script check_mysql_alive {
script "/etc/keepalived/check_mysql.sh"
interval 3
weight -20
}
global_defs {
router_id LVS_MYSQL # 运行keepalived机器的一个标识
}
vrrp_instance VI_1 {
interface eth0 # 设置实例绑定的网卡
state BACKUP # 指定哪个为master，哪个为backup
virtual_router_id 123 # VPID标记，主备必须一样
priority 170 # 优先级，高优先级竞选为master
vrrp_unicast_bind 10.20.10.1 # 备ip
vrrp_unicast_peer 10.20.10.2 # 主ip
authentication {
auth_type PASS
auth_pass shenhao
}
virtual_ipaddress {
10.20.10.222 # 设置VIP，必须是同一网段虚拟IP
}
track_script {
check_mysql_alive # 存活检查
}
}
存活检查脚本
脚本非0退出，权重降级weight -20，实现主从切换
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
exit 1
fi
重启keepalived
校验
检查主节点VIP是否配置成功
ip -a # 绑定网卡出现 10.20.10.222 地址
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast
...
inet 10.20.10.222/32 scope global eth0
valid_lft forever preferred_lft forever
从应用节点查看是否能ping通，或者用arp命令查看VIP地址是否广播
arp
Address HWtype HWaddress Flags Mask Iface
10.20.10.222 ether 0c:fa:41:1e:3b:0c C eth0
10.20.10.1 ether 0c:fa:41:1e:3b:0c C eth0
10.20.10.2 ether 0c:fa:41:1e:5b:11 C eth0
通过mysql client链接VIP，访问正常
网友评论