时间:2021-07-01 10:21:17 帮助过:20人阅读
[mysqld]
datadir=/data/mysql
port=3306
socket=/tmp/mysql.sock
pid-file=/data/mysql/mysqld.pid
log_error=error.log
user=mysql
skip-name-resolve
log-bin=mysql-bin
log-bin-index=mysql-bin.index
server-id=62
character_set_server=utf8
log-slave-updates=1
relay-log=mysql-relay-bin
relay-log-index=mysql-relay-bin.index
binlog_format=mixed
auto-increment-increment=2
auto-increment-offset=2
gtid_mode=on
enforce_gtid_consistency=1
skip_slave_start=1
[mysql]
prompt=(\u@\h) [\d]>\_
[client]
user=root
password=
创建复制帐号:
(root@c6-node2) [(none)]> GRANT REPLICATION SLAVE ON *.* TO ‘mysync‘@‘192.168.197.61‘ IDENTIFIED BY ‘redhat‘;
Query OK, 0 rows affected (0.00 sec)
注意:由于这里配置的是双主模型所以两个数据库的数据一定要保持一致!如果要是不一致请以一个最新说句为主,注意连接的先后顺序!
node1配置:
(root@C6-node1) [(none)]> CHANGE MASTER TO MASTER_HOST=‘192.168.197.62‘,MASTER_USER=‘mysync‘,MASTER_PASSWORD=‘redhat‘,MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
(root@C6-node1) [(none)]> START SLAVE;
Query OK, 0 rows affected (0.01 sec)
node2配置:
(root@C6-node2) [(none)]> CHANGE MASTER TO MASTER_HOST=‘192.168.197.61‘,MASTER_USER=‘mysync‘,MASTER_PASSWORD=‘redhat‘,MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
(root@C6-node2) [(none)]> START SLAVE;
Query OK, 0 rows affected (0.03 sec)
安装keepalived这里是用的是yum install -y keepalived进行安装。keepalived的配置文件如下:
global_defs {
router_id KEEPALIVED_MYSQL
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 61
priority 140
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.197.88
}
}
virtual_server 192.168.197.61 3306 {
delay_loop 2
protocol TCP
real_server 192.168.197.61 3306 {
notify_down "/etc/keepalived/check_mysql.sh"
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
check_mysql 检测脚本如下:
#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
CHECK_COUNT=5
counter=1
while true
do
mysql -h $MYSQL_HOST -u $MYSQL_USER -e "show status;" >/dev/null 2>&1
i=$?
ps aux | grep mysqld | grep -v grep > /dev/null 2>&1
j=$?
if [ $i -eq 0 ] && [ $j -eq 0 ]
then
exit 0
else
if [ $i -eq 1 ] && [ $j -eq 0 ]
then
exit 0
else
if [ $counter -gt $CHECK_COUNT ]
then
break
fi
let counter++
continue
fi
fi
done
/etc/init.d/keepalived stop
exit 1
说明:
1.check_mysql.sh 脚本的需要赋予执行权限。
2.check_mysql脚本没有使用密码登录获取mysql的状态,这里建议创建一个普通用户来检测mysql 的状态。
3.我这边没有使用keepalived中轮询算法的配置,在网上文章说明需要安装ipvsadm。
global_defs {
router_id KEEPALIVED_MYSQL
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 61
priority 130
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.197.88
}
}
virtual_server 192.168.197.62 3306 {
delay_loop 2
protocol TCP
real_server 192.168.197.62 3306 {
notify_down "/etc/keepalived/check_mysql.sh"
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
check_mysql.sh脚本和node1的相同。
1.启动时先保证MySQL双主模式配置是好的,即Slave_IO_Running: Yes
和Slave_SQL_Running: Yes
,这两个要是Yes状态。
2.keepalived启动时先启动priority 低的节点,然后在启动priority高的节点,避免出现抢占VIP。
3.注意,我在mysql双主模式中设置了skip_slave_start,即启动mysql不会自动执行start slave;
而是需要手动执行!
# hostname -I
192.168.197.61 192.168.197.88
# ip addr show eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:9c:7a:30 brd ff:ff:ff:ff:ff:ff
inet 192.168.197.61/24 brd 192.168.197.255 scope global eth0
inet 192.168.197.88/32 scope global eth0
inet6 fe80::20c:29ff:fe9c:7a30/64 scope link
valid_lft forever preferred_lft forever
# mysql -u root -h 192.168.197.88 -p
Enter password:
(root@192.168.197.88) [(none)]> show variables like ‘server_id‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 61 |
+---------------+-------+
1 row in set (0.00 sec)
(root@192.168.197.88) [(none)]> select * from test.DOTA;
+----+--------------+------------+
| Id | Name | Short_name |
+----+--------------+------------+
| 2 | 敌法师 | DF |
| 4 | 隐形刺客 | SA |
| 5 | 沙王 | SK |
| 7 | 骷髅王 | SNK |
| 8 | 混沌骑士 | CK |
| 9 | 冰魂 | AA |
| 10 | 斧王 | FV |
| 11 | 司夜刺客 | NA |
| 12 | 全能骑士 | OK |
+----+--------------+------------+
9 rows in set (0.00 sec)
停止node1上的mysql并测试:
# hostname -I
192.168.197.62 192.168.197.88
# ip addr show eth0
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:41:57:51 brd ff:ff:ff:ff:ff:ff
inet 192.168.197.62/24 brd 192.168.197.255 scope global eth0
inet 192.168.197.88/32 scope global eth0
inet6 fe80::20c:29ff:fe41:5751/64 scope link
valid_lft forever preferred_lft forever
# mysql -uroot -h192.168.197.88 -p
Enter password:
(root@192.168.197.88) [(none)]> show variables like ‘server_id‘;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 62 |
+---------------+-------+
1 row in set (0.00 sec)
(root@192.168.197.88) [(none)]> select * from test.DOTA;
+----+--------------+------------+
| Id | Name | Short_name |
+----+--------------+------------+
| 2 | 敌法师 | DF |
| 4 | 隐形刺客 | SA |
| 5 | 沙王 | SK |
| 7 | 骷髅王 | SNK |
| 8 | 混沌骑士 | CK |
| 9 | 冰魂 | AA |
| 10 | 斧王 | FV |
| 11 | 司夜刺客 | NA |
| 12 | 全能骑士 | OK |
+----+--------------+------------+
9 rows in set (0.00 sec)
说明:
1.以上配置的不足之处是node1在重新启动mysql和 keepalived时会抢占VIP,这个原因个人认为是我的检测脚本把keepalived 给关闭了而在重新启动时由于priority的关系还是会抢占。
2.网上参考的是主上配置nopreempt参数,但是测试的效果还是不行。如有懂得的还请指教。
MySQL keepalived 双主.md
标签:file contain running weight replicat inter timeout ber 停止