MySQL8.0.33 主主复制高可用部署

标签：33 MySQL keepalived mysqlmaster01 主主 MySQL8.0 mysql ## root

一、服务器规划

1.1 服务器详情

主机名	IP	配置	备注
MySQLMasterVIP	10.0.0.10	虚拟IP跳转	MySQL连接地址
MySQLMaster01	10.0.0.11	2H4G
MySQLMaster02	10.0.0.12	2H4G

1.2 修改主机名

## 修改各主机的主机名
如：10.0.0.11服务器
[root@localhost ~]# hostnamectl set-hostname mysqlmaster01

退出终端重启进入

1.3 关闭防火墙

## 关闭防火墙
[root@mysqlmaster01 ~]# systemctl  disable --now firewalld

## 关闭selinux
[root@mysqlmaster01 ~]# setenforce 0
[root@mysqlmaster01 ~]# sed -i 's|SELINUX=enforcing|SELINUX=disabled|g' /etc/selinux/config

二、MySQL Cluster环境部署

2.1 MySQL部署

2.1.1 性能调优

MySQL相关服务器全部执行

[root@mysqlmaster01 ~]# cat >> /etc/profile <<EOF
if [ $USER = "mysql" ]; then
    ulimit -u 16384 -n 65536
fi
EOF
[root@mysqlmaster01 ~]# source /etc/profile

2.1.2 下载MySQL官方包

MySQL相关服务器全部执行

## 下载MySQL
[root@mysqlmaster01 ~]# yum install -y libaio gcc gcc-c++ automake pcre pcre-devel zlib zlib-devel openssl openssl-devel wget
[root@mysqlmaster01 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

2.1.3 解压并移动安装包

MySQL相关服务器全部执行

# 解压 tar -zxvf 待解压目录
[root@mysqlmaster01 ~]# tar xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz

# 移动 mv 移动目录 移动后的目录
[root@mysqlmaster01 ~]# mv mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql

2.1.4 创建数据目录

MySQL相关服务器全部执行

# 进入MySQL目录
[root@mysqlmaster01 ~]# cd /usr/local/mysql

# 创建data文件夹(也可以根据那个磁盘大而定--df -Th查看)
[root@mysqlmaster01 mysql]# mkdir data

2.1.5 创建mysql用户权限

MySQL相关服务器全部执行

# 创建msyql组
[root@mysqlmaster01 mysql]# groupadd mysql

# 创建mysql用户并添加到mysql组中,-r表示mysql是系统级用户，-g是所属组为mysql
[root@mysqlmaster01 mysql]# useradd -r -g mysql mysql

# 修改mysql目录的组以及权限
[root@mysqlmaster01 mysql]# chown mysql:mysql -R ./

2.1.6 修改MySQL配置文件

2.1.6.1 主一

10.0.0.11服务器执行

[root@mysqlmaster01 mysql]# vi /etc/my.cnf

[mysqld]
## 用于指定 MySQL 服务器绑定的网络接口地址，127.0.0.1代表只能本地访问
bind-address = 0.0.0.0
port = 3306
user = mysql

innodb_strict_mode=0
# mysql解压目录
basedir = /usr/local/mysql

# 数据存放目录
datadir = /usr/local/mysql/data

# 跳过密码验证
# skip-grant-tables

# socker文件存放目录
socket = /tmp/mysql.sock

# 错误日志
log-error = /usr/local/mysql/data/mysql.error

# mysql进程号
pid-file = /usr/local/mysql/data/mysql.pid

# 日志记录慢查询SQL
slow_query_log = true
slow_query_log_file = /usr/local/mysql/data/mysql_slow_query.log
long_query_time = 1

# 记录没有使用index的查询记录
log-queries-not-using-indexes

# 配置mysqlbinlog
server-id = 1		## 注意不能重复
log-bin = mysql-bin
binlog_format = MIXED
gtid-mode = on
enforce-gtid-consistency = true
master-info-repository = TABLE
relay-log-info-repository = TABLE
default_authentication_plugin = mysql_native_password

# 并行复制工作线程数
slave-parallel-workers = 4

# 设置binlog过期时间，过期自动删除 binlog_expire_logs_seconds单位是秒
binlog_expire_logs_seconds = 259200
max_binlog_size = 50M
log_bin_trust_function_creators = 1

# 设置sql_mode行为规则
sql_mode = 'NO_ENGINE_SUBSTITUTION'

2.1.6.2 主二

10.0.0.12服务器执行

[mysqld]
## 用于指定 MySQL 服务器绑定的网络接口地址，127.0.0.1代表只能本地访问
bind-address = 0.0.0.0
port = 3306
user = mysql

innodb_strict_mode=0
# mysql解压目录
basedir = /usr/local/mysql

# 数据存放目录
datadir = /usr/local/mysql/data

# 跳过密码验证
# skip-grant-tables

# socker文件存放目录
socket = /tmp/mysql.sock

# 错误日志
log-error = /usr/local/mysql/data/mysql.error

# mysql进程号
pid-file = /usr/local/mysql/data/mysql.pid

# 日志记录慢查询SQL
slow_query_log = true
slow_query_log_file = /usr/local/mysql/data/mysql_slow_query.log
long_query_time = 1

# 记录没有使用index的查询记录
log-queries-not-using-indexes

# 配置mysqlbinlog
server-id = 2		## 注意不能重复
log-bin = mysql-bin
binlog_format = MIXED
gtid-mode = on
enforce-gtid-consistency = true
master-info-repository = TABLE
relay-log-info-repository = TABLE
default_authentication_plugin = mysql_native_password

# 并行复制工作线程数
slave-parallel-workers = 4

# 设置binlog过期时间，过期自动删除 binlog_expire_logs_seconds单位是秒
binlog_expire_logs_seconds = 259200
max_binlog_size = 50M
log_bin_trust_function_creators = 1

# 设置sql_mode行为规则
sql_mode = 'NO_ENGINE_SUBSTITUTION'

2.1.7 初始化MySQL

MySQL相关服务器全部执行

# 初始化  --defaults-file为配置文件所在 ,--basedir为解压路径,--datadir为数据保存路径 --user表示MySQL所属用户
[root@mysqlmaster01 mysql]# ./bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --user=mysql --initialize

## 如果执行完初始化没有报错，复制MySQL启动脚本到/etc/init.d/目录便于使用service mysql start启动
[root@mysqlmaster01 mysql]# cp support-files/mysql.server /etc/init.d/mysql

2.1.8 查看密码

MySQL相关服务器全部执行

## 初始化MySQL是会生产一个临时密码，可以通过查看在my.cnf中配置的log-error的配置文件知晓。
[root@mysqlmaster01 mysql]# cat data/mysql.error | grep "root@"
root@mysqlmaster01: oK)CZs*bg9zw   ##通常最后一行

2.1.9 设置systemd启动文件

MySQL相关服务器全部执行

[root@mysqlmaster01 mysql]# vim /usr/lib/systemd/system/mysql.service

[Unit]
Description=MySQL Server
Documentation=man:mysqld()
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
 
[Install]
WantedBy=multi-user.target
 
[Service]
User=mysql
Group=mysql
PIDFile=/usr/local/mysql/data/mysqld.pid
 
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=
 
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables
#ExecStartPre=/usr/bin/mysqld_pre_systemd
 
# Start main service
ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/usr/local/mysql/data/mysqld.pid
#注意这里要加上 --daemonize
# Use this to switch malloc implementation
#EnvironmentFile=-/etc/sysconfig/mysql
 
# Sets open_files_limit
LimitNOFILE = 
Restart=on-failure
RestartPreventExitStatus=
PrivateTmp=false

2.1.10 启动服务

MySQL相关服务器全部执行

## 开机自启动
[root@mysqlmaster01 mysql]# systemctl enable --now mysql.service

2.1.11 设置环境变量

MySQL相关服务器全部执行

[root@mysqlmaster01 mysql]# vim /etc/profile
export PATH=$PATH:/usr/local/mysql/bin		## 最后一行添加
[root@mysqlmaster01 mysql]# source /etc/profile

2.1.12 修改密码

MySQL相关服务器全部执行

## 登入MySQL
[root@mysqlmaster01 mysql]# mysql -uroot -p
Enter password: 	## mysql.error获取的临时密码: 2yhm?>?s7=iJ

## 修改密码
mysql> alter user 'root'@'localhost' identified by '123456';
mysql> set password for root@localhost = '123456';

## 设置MySQL远程登入
mysql> use mysql;
msyql> update user set user.Host='%' where user.User='root';
mysql> flush privileges;
mysql> exit

## 把mysql用户登录密码加密规则还原成mysql_native_password
[root@mysqlmaster01 mysql]# mysql -uroot -p123456
mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
mysql> FLUSH PRIVILEGES;

2.1.13 主主复制

2.1.13.1 主一

10.0.0.11服务器执行

## 创建授权用户
[root@mysqlmaster01 local]# mysql -uroot -p123456
mysql> CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
mysql> FLUSH PRIVILEGES;
mysql> show master status;  //查看bin文件
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000002 |     2684 |              |                  | 7fa5f52b-8d0b-11ee-8aef-000c29aad94b:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+

2.1.13.2 主二

10.0.0.12服务器执行

## 连接主库
[root@mysqlmaster02 local]# mysql -uroot -p123456
mysql> stop slave;
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST ='10.0.0.11',SOURCE_USER='slave',SOURCE_PASSWORD ='123456',SOURCE_LOG_FILE ='mysql-bin.000002',SOURCE_LOG_POS =2684;
mysql> start slave;

参数：
SOURCE_HOST ='10.0.0.11'				## 主库IP地址
SOURCE_USER='slave'						## 主库创建用于主从复制的用户
SOURCE_PASSWORD ='123456'				## 主库创建用于主从复制的密码
SOURCE_LOG_FILE ='mysql-bin.000002'		## 主库binlog日志文件名
SOURCE_LOG_POS =2684					## 主库binlog日志文件位置

## 检查是否成功
mysql> show slave status \G
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.11
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 2684
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes			## 两个Yes代表成功
            Slave_SQL_Running: Yes

2.1.13.3 主二

10.0.0.12服务器执行

## 创建授权用户
[root@mysqlmaster02 local]# mysql -uroot -p123456
mysql> CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
mysql> FLUSH PRIVILEGES;
mysql> show master status;  //查看bin文件
+------------------+----------+--------------+------------------+-------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000002 |     2684 |              |                  | 7fa5f52b-8d0b-11ee-8aef-000c29aad94b:1-10 |
+------------------+----------+--------------+------------------+-------------------------------------------+

2.1.13.4 主一

10.0.0.11服务器执行

## 连接主库
[root@mysqlmaster01 local]# mysql -uroot -p123456
mysql> stop slave;
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST ='10.0.0.12',SOURCE_USER='slave',SOURCE_PASSWORD ='123456',SOURCE_LOG_FILE ='mysql-bin.000002',SOURCE_LOG_POS =2684;
mysql> start slave;

参数：
SOURCE_HOST ='10.0.0.12'				## 主库IP地址
SOURCE_USER='slave'						## 主库创建用于主从复制的用户
SOURCE_PASSWORD ='123456'				## 主库创建用于主从复制的密码
SOURCE_LOG_FILE ='mysql-bin.000002'		## 主库binlog日志文件名
SOURCE_LOG_POS =2684					## 主库binlog日志文件位置

## 检查是否成功
mysql> show slave status \G
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.12
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 2684
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 326
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes			## 两个Yes代表成功
            Slave_SQL_Running: Yes

2.1.14 开放相关端口

MySQL相关服务器全部执行

[root@mysqlmaster01 mysql]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
[root@mysqlmaster01 mysql]# firewall-cmd --reload

2.2 keepalived部署

MySQL相关服务器全部执行

2.2.1下载Keepalived官方包

MySQL相关服务器全部执行

[root@mysqlmaster01 ~]# wget https://www.keepalived.org/software/keepalived-2.2.8.tar.gz

2.2.2 解压安装包

MySQL相关服务器全部执行

[root@mysqlmaster01 ~]# tar -zxvf keepalived-2.2.8.tar.gz

2.2.3 编译安装

MySQL相关服务器全部执行

[root@mysqlmaster01 ~]# cd keepalived-2.2.8
[root@mysqlmaster01 keepalived-2.2.8]# ./configure --prefix=/usr/local/keepalived
[root@mysqlmaster01 keepalived-2.2.8]# make -j4 && make install

2.2.4 启动脚本

MySQL相关服务器全部执行

[root@mysqlmaster01 keepalived-2.2.8]# cp keepalived/etc/init.d/keepalived /etc/init.d/ 
[root@mysqlmaster01 keepalived-2.2.8]# chmod 755 /etc/init.d/keepalived

2.2.5 做软连接

MySQL相关服务器全部执行

[root@mysqlmaster01 keepalived-2.2.8]# ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/

2.2.6 修改配置文件

MySQL相关服务器全部执行

2.2.6.1 主节点

10.0.0.11服务器执行

[root@mysqlmaster01 keepalived-2.2.8]# vim /usr/local/keepalived/etc/keepalived/keepalived.conf

global_defs { #全局配置
   router_id mysqlmaster01 #路由id号，不能重复
}

vrrp_script mysqlmaster {
        script "/usr/local/keepalived/scripts/mysql.sh"	#检查当前mysqlmaster是否为master
        interval 3			#每3秒检查一次
        weight 2			#当本机为master检测结果返回1，将优先级加2
        fall 2   			##检测失败多少次就才认为是真的失败
        rise 1    			##检测成功1为就认为是成功的
    }
    
vrrp_instance VI_mysqlmaster { #定义一个实例
    state MASTER #参数 master/backup 只是说明，具体根据优先级
    interface ens33 #虚IP地址放置的网卡位置 
    virtual_router_id 51 #同一个集群id一致
    priority 100 #优先级决定是主还是备    越大越优先
    advert_int 1 #主备通讯时间间隔
    authentication {
        auth_type PASS
        auth_pass 1111 #认证号，集群中要一致
    }
    virtual_ipaddress {
        10.0.0.10 #使用的虚拟ip，要和网段内ip不冲突
    }
    track_script {			##调用状态检查
        mysqlmaster
    }
}

2.2.6.1 从节点

10.0.0.12服务器执行

global_defs { #全局配置
   router_id mysqlmaster02 #路由id号，不能重复
}

vrrp_script mysqlmaster {
        script "/usr/local/keepalived/scripts/mysql.sh"	#检查当前mysqlmaster是否为master
        interval 3			#每3秒检查一次
        weight 2			#当本机为master检测结果返回1，将优先级加2
        fall 2   			##检测失败多少次就才认为是真的失败
        rise 1    			##检测成功1为就认为是成功的
    }
    
vrrp_instance VI_mysqlmaster { #定义一个实例
    state backup #参数 master/backup 只是说明，具体根据优先级
    interface ens33 #虚IP地址放置的网卡位置 
    virtual_router_id 51 #同一个集群id一致
    priority 99 #优先级决定是主还是备    越大越优先
    advert_int 1 #主备通讯时间间隔
    authentication {
        auth_type PASS
        auth_pass 1111 #认证号，集群中要一致
    }
    virtual_ipaddress {
        10.0.0.10 #使用的虚拟ip，要和网段内ip不冲突
    }
    track_script {			##调用状态检查
        mysqlmaster
    }
}

2.2.7 编写状态检查脚本

MySQL相关服务器全部执行

## 创建脚本目录
[root@mysqlmaster01 keepalived-2.2.8]# mkdir /usr/local/keepalived/scripts

[root@mysqlmaster01 keepalived-2.2.8]# vim /usr/local/keepalived/scripts/mysql.sh

#!/bin/bash
MySQL_PS=`ps -C mysqld --no-header | wc -l`
if [ ${MySQL_PS} -eq 0 ];then
        systemctl restart mysql.service
        sleep 3
        if [ ${MySQL_PS} -eq 0 ];then
                systemctl stop keepalived.service
        fi
else
        printf "Service：mysqld.service Running！！！\r\n"
fi

## 脚本授权
[root@mysqlmaster01 keepalived-2.2.8]# chmod +x /usr/local/keepalived/scripts/mysql.sh

2.2.8 设置systemd启动文件

MySQL相关服务器全部执行

[root@mysqlmaster01 keepalived-2.2.8]# vim /usr/lib/systemd/system/keepalived.service

[Unit]
Description=LVS and VRRP High Availability Monitor
After=network-online.target syslog.target
Wants=network-online.target
Documentation=man:keepalived(8)
Documentation=man:keepalived.conf(5)
Documentation=man:genhash(1)
Documentation=https://keepalived.org

[Service]
Type=forking
PIDFile=/run/keepalived.pid
KillMode=process
EnvironmentFile=-/usr/local/keepalived/etc/sysconfig/keepalived
ExecStart=/usr/local/keepalived/sbin/keepalived  $KEEPALIVED_OPTIONS
ExecReload=/bin/kill -HUP $MAINPID
PrivateTmp=true
TimeoutStopSec=5
KillMode=process
RestartSec=10
Restart=always

[Install]
WantedBy=multi-user.target

2.2.9 启动服务

MySQL相关服务器全部执行

[root@mysqlmaster01 keepalived-2.2.8]# systemctl enable --now keepalived.service

2.2.10 端口开放

MySQL相关服务器全部执行

[root@mysqlmaster01 keepalived-2.2.8]# firewall-cmd --zone=public --add-port=80/tcp --permanent
[root@mysqlmaster01 keepalived-2.2.8]# firewall-cmd --add-rich-rule='rule protocol value="vrrp" accept' --permanent
[root@mysqlmaster01 keepalived-2.2.8]# firewall-cmd --reload

2.2.11 检查VIP是否启动

查看VIP是否存在

[root@mysqlmaster01 keepalived-2.2.8]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:9f:39:98 brd ff:ff:ff:ff:ff:ff
    inet 10.0.0.11/24 brd 10.0.0.255 scope global noprefixroute ens33
       valid_lft forever preferred_lft forever
    inet 10.0.0.10/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::50f7:7bb7:e131:be15/64 scope link noprefixroute 
       valid_lft forever preferred_lft forever

标签：33,MySQL,keepalived,mysqlmaster01,主主,MySQL8.0,mysql,##,root
From： https://blog.csdn.net/weixin_45552105/article/details/140424320