文章目录
mysql1 在其中一台机器
mkdir -p /home/mysql
chmod 777 -R /home/mysql
cd ~
mkdir mysql
cd mysql
wget "https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.36-1.el7.x86_64.rpm-bundle.tar"
tar -xvf mysql-8.0.36-1.el7.x86_64.rpm-bundle.tar
#依次: common,libs,client,server
rpm -ivh mysql-community-common-8.0.36-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-libs-8.0.36-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-client-8.0.36-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-server-8.0.36-1.el7.x86_64.rpm --nodeps --force
# libaio包的作用是为了支持同步I/O
yum -y install libaio
- mysql1 /etc/my.cnf
[mysqld]
# 设置服务节点ID,不能重复
server-id=1
# 开启二进制同步
log-bin=mysql-bin
# 需要开启二进制日志的数据库,可设置白名单[binlog-do-db]和黑名单[binlog-ignore-db]中一种,多个可重复设置
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 需要开启同步的数据库,可设置白名单[replicate-do-db]和黑名单[replicate-ignore-db]中一种,多个可重复设置
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
#指定重做日志文件名,默认取主机名,修改主机名后可能导致问题
relay-log=mysql-relay-bin
# 跳过所有复制的错误
slave-skip-errors=all
# 自增长字段增量值
auto-increment-increment=2
# 自增长字段初始值为1,保证不同节点的自增值不会重复
auto-increment-offset=1
#bin-log文件的有效时间,过期后会被清理
expire_logs_days=3
max_allowed_packet = 32M
lower_case_table_names = 1
basedir = /home/mysql
datadir = /home/mysql/data
port = 3306
bind-address = 0.0.0.0
max_connections = 500
max_connect_errors = 500
external-locking = FALSE
sort_buffer_size = 1M
slow_query_log=true
slow_query_log_file=/home/mysql/slow-query.log
long_query_time=3
max_binlog_size = 2G
binlog_format = row
binlog_row_image = full
lower-case-table-names=1
log-error=/home/mysql/mysql_error.log
skip-grant-tables=1
# 修改完/etc/my.cnf后 执行,因为客户磁盘是在/home目录,如果是/home/mysql否则是/var/lib/mysql
mysqld --user=mysql --initialize-insecure;
chown mysql:mysql /home/mysql -R;
- 启动&关闭mysql服务
systemctl start mysqld;
systemctl status mysqld;
# 关闭
systemctl stop mysqld;
- 修改密码
mysql -u root
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
- 将
skip-grant-tables=1
从my.cnf删除
vi /etc/my.cnf
- 重启mysql
systemctl restart mysqld
- 配置开机启动启动
systemctl enable mysqld.service
- 登录
mysql -u root -p
123456
- 创建库和用户
CREATE DATABASE IF NOT EXISTS ipaas DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE DATABASE IF NOT EXISTS nacos DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE DATABASE IF NOT EXISTS xxl_job DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE DATABASE IF NOT EXISTS escdbs DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE USER 'app_user'@'%' IDENTIFIED BY '123456';
GRANT ALL ON *.* TO 'app_user'@'%';
FLUSH PRIVILEGES;
mysql2 在另一台机器
mkdir -p /home/mysql
chmod 777 -R /home/mysql
cd ~
mkdir mysql
cd mysql
wget "https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.36-1.el7.x86_64.rpm-bundle.tar"
tar -xvf mysql-8.0.36-1.el7.x86_64.rpm-bundle.tar
#依次: common,libs,client,server
rpm -ivh mysql-community-common-8.0.36-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-libs-8.0.36-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-client-8.0.36-1.el7.x86_64.rpm --nodeps --force
rpm -ivh mysql-community-server-8.0.36-1.el7.x86_64.rpm --nodeps --force
# libaio包的作用是为了支持同步I/O
yum -y install libaio
- mysql2 /etc/my.cnf
[mysqld]
# 设置服务节点ID,不能重复
server-id=2
# 开启二进制同步
log-bin=mysql-bin
# 需要开启二进制日志的数据库,可设置白名单[binlog-do-db]和黑名单[binlog-ignore-db]中一种,多个可重复设置
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
# 需要开启同步的数据库,可设置白名单[replicate-do-db]和黑名单[replicate-ignore-db]中一种,多个可重复设置
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
#指定重做日志文件名,默认取主机名,修改主机名后可能导致问题
relay-log=mysql-relay-bin
# 跳过所有复制的错误
slave-skip-errors=all
# 自增长字段增量值
auto-increment-increment=2
# 自增长字段初始值为1,保证不同节点的自增值不会重复
auto-increment-offset=2
#bin-log文件的有效时间,过期后会被清理
expire_logs_days=3
max_allowed_packet = 32M
lower_case_table_names = 1
basedir = /home/mysql
datadir = /home/mysql/data
port = 3306
bind-address = 0.0.0.0
max_connections = 500
max_connect_errors = 500
external-locking = FALSE
sort_buffer_size = 1M
slow_query_log=true
slow_query_log_file=/home/mysql/slow-query.log
long_query_time=3
max_binlog_size = 2G
binlog_format = row
binlog_row_image = full
log-error=/home/mysql/mysql_error.log
skip-grant-tables=1
# 修改完/etc/my.cnf后 执行,因为客户磁盘是在/home目录,如果是/home/mysql否则是/var/lib/mysql
mysqld --user=mysql --initialize-insecure;
chown mysql:mysql /home/mysql -R;
- 启动&关闭mysql服务
systemctl start mysqld;
systemctl status mysqld;
# 关闭
#systemctl stop mysqld;
- 修改密码
# 登录MySQL
mysql -u root
# 刷新
flush privileges;
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
- 将
skip-grant-tables=1
从my.cnf删除
vi /etc/my.cnf
- 重启mysql
systemctl restart mysqld
- 配置开机启动启动
systemctl enable mysqld.service
- 登录
mysql -u root -p
123456
- 创建库和用户
CREATE DATABASE IF NOT EXISTS ipaas DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE DATABASE IF NOT EXISTS nacos DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE DATABASE IF NOT EXISTS xxl_job DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
CREATE USER 'app_user'@'%' IDENTIFIED BY '123456';
GRANT ALL ON *.* TO 'app_user'@'%';
FLUSH PRIVILEGES;
主从配置
1. 互为主从配置
1.1. 注意事项
- 主从复制只能复制开启主从复制之后的记录,开启前要保证各节点数据一致,数据手动同步可参考 MySQL数据库手动同步复制(拷贝datadir目录文件)。
- 两个MySQL服务节点分别用
mysql-01
和mysql-02
表示。
1.2. 创建复制用户
- 在
mysql-01
和mysql-02
两个节点的MySQL命令行界面执行以下SQL语句,创建replication 用户用于其他节点连接当前节点,读取当前节点的log-bin。
mysql -uroot -p
密码:123456
mysql > CREATE USER 'replication'@'%' IDENTIFIED BY 'replication';
mysql > GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
mysql > flush privileges;
1.3. 建立复制关系
mysql-0.mysql.mysql
的由来 {pod-name}.{service-name}.{namespace}
-
mysql2-0.mysql2-svc.default : 172.16.8.106
-
mysql-0.mysql-svc.default : 172.16.8.104
-
在
mysql-01
节点的MySQL命令行界面执行以下SQL语句。
-- 先锁表,避免同步时有事务提交
flush tables with read lock;
--查看当前节点的服务状态
show master status;
--在[mysql-01]节点上change master时,master信息使用[mysql-02]节点的status信息,反之亦然。
--master_log_file取另一个节点status的File字段值
--master_log_pos取另一个节点status的Position字段值
--master_host取另一个节点的IP
change master to
master_host='10.241.244.225',
master_user='replication',
master_password='replication',
master_log_file='mysql-bin.000003',
master_log_pos=2327,
GET_MASTER_PUBLIC_KEY=1;
--开始同步
start slave;
--查看同步状态,Slave_IO_Running 和 Slave_SQL_Running 都为Yes表示配置成功
show slave status\G;
--解除锁定
unlock tables;
- 在
mysql-02
节点的MySQL命令行界面执行以下SQL语句。
复制
-- 先锁表,避免同步时有事务提交
flush tables with read lock;
--查看当前节点的服务状态
show master status;
--在[mysql-01]节点上change master时,master信息使用[mysql-02]节点的status信息,反之亦然。
--master_log_file取另一个节点status的File字段值
--master_log_pos取另一个节点status的Position字段值
--master_host取另一个节点的IP
change master to
master_host='10.241.244.224',
master_user='replication',
master_password='replication',
master_log_file='mysql-bin.000003',
master_log_pos=2330,
GET_MASTER_PUBLIC_KEY=1;
--开始同步
start slave;
--查看同步状态,Slave_IO_Running 和 Slave_SQL_Running 都为Yes表示配置成功
show slave status\G;
--解除锁定
unlock tables;
keepalived配置
两台mysql机器都要安装keepalived
如果客户没有提供虚拟ip,可以使用tunl0网卡的网段,比如tunl0是100.108.11.192,则VIP可以是100.xxx.xxx.xxx任何一个,注意不能冲突
机器1
安装keepalived
yum install -y keepalived;
#设置开机启动
systemctl enable keepalived
cp /usr/sbin/keepalived /etc/init.d/keepalived
chmod +x /etc/init.d/keepalived
check_haproxy
vi /etc/keepalived/check_haproxy.sh
当mysql挂掉,则返回1,keepalived权重降低,主节点被抢占,让另外一台机器接手
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
exit 1
else
exit 0
fi
确保这个脚本有执行权限,并且正确地配置了监听端口。如果监控的服务端口不可用,脚本将返回非零值,这将导致Keepalived认为本地服务器失效,并开始VIP转移的过程。
chmod +x /etc/keepalived/check_haproxy.sh
keepalived.conf
注意:10.241.244.224 10.241.244.225 为mysql服务器机器ip
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
# 指定router_id
router_id ha01
script_user root
}
vrrp_script check_haproxy {
# 定义脚本
script "/etc/keepalived/check_haproxy.sh"
# 脚本执行间隔,每2s检测一次
interval 2
}
vrrp_instance VI_1 {
# 在ha1、ha2都为 BACKUP
state BACKUP
# 设置为不抢占,m1挂了,m2接管VIP,m1重启不会自动抢回VIP
nopreempt
# 具体根据网卡来
interface ens192
garp_master_delay 10
smtp_alert
# 指定虚拟路由器ID, ha1和ha2此值必须相同
virtual_router_id 66
# 在ha2上为80, 代表着这台机器的权限比ha2高
priority 100
unicast_src_ip 10.241.244.224
unicast_peer {
#对端ip
10.241.244.225
}
advert_int 1
authentication {
auth_type PASS
# 指定验证密码, ha1和ha2此值必须相同
auth_pass 123456
}
virtual_ipaddress {
# 指定VIP, ha1和ha2此值必须相同,这里可以使用
10.241.244.237 dev ens192 label ens192:1
}
track_script {
# 调用上面定义的脚本
check_haproxy
}
}
启动keepalived
systemctl restart keepalived;
查看keepalived状态
systemctl status keepalived;
机器2
安装keepalived
yum install -y keepalived;
#设置开机启动
systemctl enable keepalived
cp /usr/sbin/keepalived /etc/init.d/keepalived
chmod +x /etc/init.d/keepalived
check_haproxy
vi /etc/keepalived/check_haproxy.sh
当mysql挂掉,则停掉本机的keepalived,让另外一台机器接手
#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
exit 1
else
exit 0
fi
确保这个脚本有执行权限,并且正确地配置了监听端口。如果监控的服务端口不可用,脚本将返回非零值,这将导致Keepalived认为本地服务器失效,并开始VIP转移的过程。
chmod +x /etc/keepalived/check_haproxy.sh
keepalived.conf
echo ‘’ > /etc/keepalived/keepalived.conf
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
# 指定router_id
router_id ha02
script_user root
}
vrrp_script check_haproxy {
# 定义脚本
script "/etc/keepalived/check_haproxy.sh"
# 脚本执行间隔,每2s检测一次
interval 2
}
vrrp_instance VI_1 {
# 在ha1、ha2都为 BACKUP
state BACKUP
# 设置为不抢占,m1挂了,m2接管VIP,m1重启不会自动抢回VIP
nopreempt
# 具体根据网卡来
interface ens192
garp_master_delay 10
smtp_alert
# 指定虚拟路由器ID, ha1和ha2此值必须相同
virtual_router_id 66
# 在ha2上为80
priority 80
# 本机ip
unicast_src_ip 10.241.244.225
unicast_peer {
#对端ip
10.241.244.224
}
advert_int 1
authentication {
auth_type PASS
# 指定验证密码, ha1和ha2此值必须相同
auth_pass 123456
}
virtual_ipaddress {
# 指定VIP, ha1和ha2此值必须相同,这里可以使用
10.241.244.237 dev ens192 label ens192:1
}
track_script {
# 调用上面定义的脚本
check_haproxy
}
}
启动keepalived
systemctl restart keepalived;
查看keepalived状态
systemctl status keepalived;
测试IP漂移
机器1
systemctl stop mysqld;
systemctl status keepalived.service
说明机器1的mysql挂了之后,keepalived也停掉了。但是容器中依然能够telnet通
重新启动
systemctl start mysqld;
systemctl start keepalived;
systemctl status keepalived.service
# 采用tcpdump抓包定位问题
tcpdump -i ens192 vrrp -n
问题:两台机器上面都有VIP的情况
排查:
1.检查防火墙,发现已经是关闭状态。
2. keepalived.conf配置问题。
3.可能是上联交换机禁用了arp的广播限制,造成keepalive无法通过广播通信,两台服务器抢占vip,出现同时都有vip的情况。
tcpdump -i eth0 vrrp -n 检查发现 14和15都在对224.0.0.18发送消息。但是在正常情况下,备节点如果收到主节点的心跳消息时,优先级高于自己,就不会主动对外发送消息。
解决方法,将多播调整为单播然后重启服务:
[root@test-15]# vim /etc/keepalived.conf
priority 50
unicast_src_ip 172.19.1.15 #本机ip
unicast_peer {
172.19.1.14 #对端ip
}
[root@test-14]# vim /etc/keepalived.conf
priority 100
unicast_src_ip 172.19.1.14 #本机ip
unicast_peer {
172.19.1.15 #对端ip
}
配置完成后恢复正常,查看: tcpdump -i eth0 vrrp -n