一、背景
cdh，第二套cdh的元数据部署， 使用5.7.27版本mysql。
二、架构
1主1从，异步复制。
资源
# 资源
172.25.116.11
172.25.116.12
VIP： 172.25.116.230
三、master部署(done)
# ###
mkdir -p /data/disk1/mysql/data
cd /usr/local
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.27-linux-glibc2.12-x86_64 mysql
groupadd mysql
useradd -r -g mysql mysql
mkdir -p /data/disk1/mysql/
cd /data/disk1/mysql/
chown -R mysql:mysql /data/disk1/mysql/
./bin/mysqld --initialize --user=mysql --datadir=/data/disk1/mysql/data
# generated for root@localhost: hqNAaUy(c9&i
# ./bin/mysqld_safe --datadir=/data/disk1/mysql/data &
# 1. 创建root
mysql -h172.25.116.12 -uroot -p'123'
# 重置local root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123';
create user 'root'@'%' identified by '123';
grant all on *.* to root with grant option;
FLUSH PRIVILEGES;
EXIT;
# 创建 % root的密码
create user 'root'@'%' identified by '123';
grant all on *.* to root with grant option;
flush privileges;
show grants for 'root'@'%';
#
SHOW VARIABLES LIKE '%read_only';
# 2. my.cnf 配置文件
# ###
cat > /etc/my.cnf << EOF
[mysqld]
super_read_only = ON
report-host=172.25.116.11
user = mysql
port = 3306
# 主从复制配置
server_id=3
relay-log=mysql-relay-bin
log-slave-updates=ON
gtid-mode=ON
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
slave_preserve_commit_order=on
log_bin=/data/disk1/mysql/mysql-bin
binlog-format = row
expire_logs_days=7
max_binlog_size = 500M
datadir=/data/disk1/mysql/data/
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
# pid-file=/var/run/mysqld/mysqld.pid
character_set_server=utf8
EOF
# ###
# 3. systemd配置
cat > /etc/systemd/system/mysqld.service << EOF
[Unit]
Description=MySQL 5.7 Database Server
After=network.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF
4. 启动mysql
systemctl daemon-reload && systemctl start mysqld
# 配置环境变量
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile
四、slave部署(done)
cd /usr/local
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
tar -zxvf mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
ln -s mysql-5.7.27-linux-glibc2.12-x86_64 mysql
groupadd mysql
useradd -r -g mysql mysql
mkdir -p /data/disk1/mysql/
touch /var/log/mysqld.log
mkdir -p /var/lib/mysql/
cd /data/disk1/mysql/
chown -R mysql:mysql /data/disk1/mysql/
chown -R mysql:mysql /var/lib/mysql/
chown -R mysql:mysql /var/log/mysqld.log
./bin/mysqld --initialize --user=mysql --datadir=/data/disk1/mysql/data
# generated for root@localhost : *vqdaUAN9szq
# ./bin/mysqld_safe --datadir=/data/disk1/mysql/data &
# 重置local root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY '123';
create user 'root'@'%' identified by '123';
grant all on *.* to root with grant option;
FLUSH PRIVILEGES;
EXIT;
# 创建 % root的密码
create user 'root'@'%' identified by '123';
grant all on *.* to root with grant option;
flush privileges;
show grants for 'root'@'%';
#
SHOW VARIABLES LIKE '%read_only';
# ###
cat > /etc/my.cnf << EOF
[mysqld]
super_read_only = ON
report-host=172.25.116.12
user = mysql
port = 3306
# 主从复制配置
server_id=4
relay-log=mysql-relay-bin
log-slave-updates=ON
gtid-mode=ON
enforce-gtid-consistency=ON
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
slave_preserve_commit_order=on
log_bin=/data/disk1/mysql/mysql-bin
binlog-format = row
expire_logs_days=7
max_binlog_size = 500M
datadir=/data/disk1/mysql/data/
socket=/var/lib/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
# pid-file=/var/run/mysqld/mysqld.pid
character_set_server=utf8
EOF
# ###
# 3. systemd配置
cat > /etc/systemd/system/mysqld.service << EOF
[Unit]
Description=MySQL 5.7 Database Server
After=network.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
Restart=on-failure
[Install]
WantedBy=multi-user.target
EOF
4. 启动mysql
systemctl daemon-reload && systemctl start mysqld
# 配置环境变量
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile
五、主从配置(done)
# ###
1. master 上备份与复制账号
# 全备数据
mysqldump -h172.25.116.11 -uroot -p'123' --single-transaction --all-databases --flush-logs > ./all_backup_01.sql
#
# 创建复制账号
CREATE USER 'replica'@'%' identified WITH mysql_native_password by 'Asdf@1234';
# 复制账号权限配置
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replica'@'%';
# 刷新
flush privileges;
# ###
2. slave上配置复制信息
set global read_only=0;
#
source xxxx;
# 配置主从信息
CHANGE MASTER TO MASTER_HOST='172.25.116.11' ,MASTER_USER='replica' ,MASTER_PASSWORD='Asdf@1234',master_auto_position=1;
# 启动slave，并启动复制
start slave;
六、keepalived安装与配置(done)
# ###
1. # 172.25.116.11 master 上
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
vrrp_script chk_haproxy {
script "/etc/keepalived/check_master_vip.sh"
interval 2
}
#router_id需要根据具体业务命名，以便于不同套作区分，同套keepalived的2台此参数名相同
global_defs {
router_id ks_pqsql
}
vrrp_instance VI_1 {
state BACKUP
#网口名称需要确认是否为bond0，如果不是需要修改为对应网口信息
interface bond0
#virtual_router_id 取值范围为0~255，同vip网段需要确认不能和其他套keepalived共用一个值，同套keepalived的2台此参数名相同
virtual_router_id 245
#priority取值自定义，区别2台keepalived的权重，一台值偏小
priority 100
advert_int 1
nopreempt
#auth_pass需要一套环境配置为相同信息
authentication {
auth_type PASS
auth_pass Yumc1234
}
track_script {
chk_haproxy
}
#172.xx.xx.xx/xx需要修改为真实vip的网段ip信息，网口名称需要确认是否为ens192，如果不是需要修改为对应网口信息
virtual_ipaddress {
172.25.116.230/32 dev bond0
}
#172.25.89.xx改为实际vip地址，172.25.88.1改为实际网关地址
notify_master "/usr/sbin/arping -I ens192 -c 2 -s 172.25.116.230 172.25.116.1"
}
# ###
# ###
2. # 172.25.116.12 slave 上
vi /etc/keepalived/keepalived.conf
! Configuration File for keepalived
vrrp_script chk_haproxy {
script "/etc/keepalived/check_master_vip.sh"
interval 2
}
#router_id需要根据具体业务命名，以便于不同套作区分，同套keepalived的2台此参数名相同
global_defs {
router_id ks_pqsql
}
vrrp_instance VI_1 {
state BACKUP
#网口名称需要确认是否为bond0，如果不是需要修改为对应网口信息
interface bond0
#virtual_router_id 取值范围为0~255，同vip网段需要确认不能和其他套keepalived共用一个值，同套keepalived的2台此参数名相同
virtual_router_id 245
#priority取值自定义，区别2台keepalived的权重，一台值偏小
priority 80
advert_int 1
nopreempt
#auth_pass需要一套环境配置为相同信息
authentication {
auth_type PASS
auth_pass Yumc1234
}
track_script {
chk_haproxy
}
#172.xx.xx.xx/xx需要修改为真实vip的网段ip信息，网口名称需要确认是否为ens192，如果不是需要修改为对应网口信息
virtual_ipaddress {
172.25.116.230/32 dev bond0
}
#172.25.89.xx改为实际vip地址，172.25.88.1改为实际网关地址
notify_master "/usr/sbin/arping -I ens192 -c 2 -s 172.25.116.230 172.25.116.1"
}
七、master检测与自动Failover
vi check_and_failover.sh
#
#!/bin/bash
# keepalived 调用该脚本的时候，mysql命令找不到，需要直接使用mysql的绝对路径。
# MySQL连接信息，VIP的连接信息
MYSQL_HOST="172.25.116.230"
MYSQL_USER="root"
MYSQL_PASSWORD="123"
time=$(date +"%Y-%m-%d-%H:%M:%S")
# 执行MySQL查询
output=$(/usr/local/mysql/bin/mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW GLOBAL VARIABLES LIKE '%read_only';" 2>/dev/null)
# 提取read_only和super_read_only的值
read_only=$(echo "$output" | awk '/read_only/ {print $2}')
super_read_only=$(echo "$output" | awk '/super_read_only/ {print $2}')
echo "super_read_only=$super_read_only , $time" >> /tmp/check_and_failover.log
# 判断VIP主机是否为从库
if [[ "$super_read_only" == "ON" ]]; then
echo "这是从库$super_read_only"
# ssh -o "StrictHostKeyChecking=no" 172.25.116.230 "systemctl restart keepalived"
output_02=$(/usr/local/mysql/bin/mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "show slave status\G" 2>/dev/null)
Slave_IO_Running=$(echo "$output_02" | awk '/Slave_IO_Running/ {print $2}')
Slave_SQL_Running=$(echo "$output_02" | awk '/Slave_SQL_Running:/ {print $2}')
Master_Host=$(echo "$output_02" | awk '/Master_Host/ {print $2}')
echo "Slave_IO_Running, $Slave_IO_Running" >> /tmp/check_and_failover.log
echo "Slave_SQL_Running, $Slave_SQL_Running" >> /tmp/check_and_failover.log
if [[ "$Slave_IO_Running" == "Connecting" || "$Slave_SQL_Running" == "Connecting" ]]; then
echo "master已经不存在， VIP所在机器是唯一的mysql， 需要提备升主...."
# 提备升主
/usr/local/mysql/bin/mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "stop slave;"
/usr/local/mysql/bin/mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "reset slave all;"
/usr/local/mysql/bin/mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SET GLOBAL read_only = OFF;"
echo "$MYSQL_HOST has been promoted to master."
elif [[ "$Slave_IO_Running" == "Yes" && "$Slave_SQL_Running" == "Yes" ]]; then
echo "从库正常， 且master 状态也正常, 只是VIP在slave上，需要重启VIP所在的keepalived" >> /tmp/check_and_failover.log
echo "主库地址：$Master_Host"
# 重启vip所在的机器的keepalived服务， 让VIP切换到master机器上
ssh -o "StrictHostKeyChecking=no" $MYSQL_HOST "systemctl restart keepalived"
else
echo "it is nothing , $time" >> /tmp/check_and_failover.log
fi
else
echo "这是master，跳过检查"
fi
host01="172.25.116.11"
host02="172.25.116.12"
out01=$(/usr/local/mysql/bin/mysql -h$host01 -u$MYSQL_USER -p$MYSQL_PASSWORD -e "show slave status\G" 2>/dev/null)
out02=$(/usr/local/mysql/bin/mysql -h$host02 -u$MYSQL_USER -p$MYSQL_PASSWORD -e "show slave status\G" 2>/dev/null)
super_read_only01=$(echo "$out01" | awk '/super_read_only/ {print $2}')
super_read_only02=$(echo "$out02" | awk '/super_read_only/ {print $2}')
Slave_IO_Running01=$(echo "$out01" | awk '/Slave_IO_Running/ {print $2}')
Slave_SQL_Running01=$(echo "$out01" | awk '/Slave_SQL_Running/ {print $2}')
Slave_IO_Running02=$(echo "$out02" | awk '/Slave_IO_Running/ {print $2}')
Slave_SQL_Running02=$(echo "$out02" | awk '/Slave_SQL_Running/ {print $2}')
if [[ "$super_read_only01" == "ON" && "$super_read_only02" == "ON" ]]; then
# 如果host01的slave状态正常，就清理host02的slave状态
if [[ "$Slave_IO_Running01" == "YES" && "$Slave_SQL_Running01" == "YES" ]]; then
/usr/local/mysql/bin/mysql -h$host02 -u$MYSQL_USER -p$MYSQL_PASSWORD -e "stop slave;"
/usr/local/mysql/bin/mysql -h$host02 -u$MYSQL_USER -p$MYSQL_PASSWORD -e "reset slave all;"
/usr/local/mysql/bin/mysql -h$host02 -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SET GLOBAL read_only = OFF;"
# 如果host02的slave状态正常，就清理host01的slave状态
elif [[ "$Slave_IO_Running02" == "YES" && "$Slave_SQL_Running02" == "YES" ]]; then
/usr/local/mysql/bin/mysql -h$host01 -u$MYSQL_USER -p$MYSQL_PASSWORD -e "stop slave;"
/usr/local/mysql/bin/mysql -h$host01 -u$MYSQL_USER -p$MYSQL_PASSWORD -e "reset slave all;"
/usr/local/mysql/bin/mysql -h$host01 -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SET GLOBAL read_only = OFF;"
else
echo "从库正常， 且master 状态也正常, 不需要做任何操作"
fi
else
echo "主从正常，跳过检查"
fi
判断VIP所在mysql是否master
vi /etc/keepalived/check_master_vip.sh
#
#!/bin/bash
# MySQL连接信息，VIP的连接信息
MYSQL_HOST="172.25.116.230"
MYSQL_USER="root"
MYSQL_PASSWORD="123"
# 执行MySQL查询
output=$(mysql -h$MYSQL_HOST -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW GLOBAL VARIABLES LIKE '%read_only';" 2>/dev/null)
# 提取read_only和super_read_only的值
read_only=$(echo "$output" | awk '/read_only/ {print $2}')
super_read_only=$(echo "$output" | awk '/super_read_only/ {print $2}')
# 判断是否为从库
if [[ "$super_read_only" == "ON" ]]; then
echo "这是从库"
ssh -o "StrictHostKeyChecking=no" 172.25.116.230 "systemctl restart keepalived"
else
echo "这不是从库"
fi
