rpm_mysql8.0主从部署手册

关闭防火墙

systemctl status firewalld.service  #检查防火墙状态
systemctl stop firewalld.service  #关闭防火墙
systemctl disable firewalld.service  #禁止开机启动防火墙

下载rpm包

使用以下任意指令查看系统版本

cat /etc/os-release
uname -a
cat /proc/version

![](bdf78187-2e56-4820-9956-10b298a6d0a0_files/2024-02-02-09-15-22-image.png)

- 在官网下载对应rpm包（cenos7可跳过直接使用 rpm_mysql8.0.tgz ）

MySQL :: Download MySQL Community Server (Archived Versions)

- 选择对应版本依次下载下面的rpm包
![](bdf78187-2e56-4820-9956-10b298a6d0a0_files/2024-02-02-09-15-46-image.png)
![](bdf78187-2e56-4820-9956-10b298a6d0a0_files/248549b2-8213-492f-83e3-388254399b1d.jpg)

按照以下顺序进行安装

tar xvf rpm_mysql8.0.tgz
cd rpm_mysql8.0
rpm -ivh mysql-community-common-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-icu-data-files-8.0.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-8.0.31-1.el7.x86_64.rpm

安装 mysql-community-libs-8.0.31-1.el7.x86_64.rpm 过程中可能会遇到以下异常：

rpm -ivh mysql-community-libs-8.0.31-1.el7.x86_64.rpm 
警告：mysql-community-libs-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
错误：依赖检测失败：
        mariadb-libs 被 mysql-community-libs-8.0.31-1.el7.x86_64 取代

解决方法：

rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
rpm -e mariadb-libs-5.5.68-1.el7.x86_64
错误：依赖检测失败：
        libmysqlclient.so.18()(64bit) 被 (已安裝) postfix-2:2.10.1-9.el7.x86_64 需要
        libmysqlclient.so.18(libmysqlclient_18)(64bit) 被 (已安裝) postfix-2:2.10.1-9.el7.x86_64 需要
rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64

安装 mysql-community-server-8.0.31-1.el7.x86_64.rpm 过程中可能会遇到缺少依赖异常：

解决方法：

# 安装perl依赖
rpm -ivh --force perl/*.rpm

# 安装net-tools依赖
rpm -ivh net-tools-2.0-0.25.20131004git.el7.x86_64.rpm

# 安装libaio
rpm -ivh libaio-0.3.109-13.el7.x86_64.rpm

# 再次安装server
rpm -ivh mysql-community-server-8.0.31-1.el7.x86_64.rpm

附依赖下载地址：
RPM resource net-tools

修改my.cnf配置

vi /etc/my.cnf
主节点

server-id=1
skip_ssl=1
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default_authentication_plugin=mysql_native_password
max_connections=5000
binlog_expire_logs_seconds=172800
port=3306
log-bin=mysql-bin
expire_logs_days=30

sql_mode='STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'

从节点

server-id=2
read_only = 1
master_info_repository=TABLE
relay_log_info_repository=TABLE
skip_ssl=1
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
default_authentication_plugin=mysql_native_password
max_connections=5000
binlog_expire_logs_seconds=172800
port=3306

若使用非标准3306端口则需要关闭SELINUX防火墙

vi /etc/selinux/config

SELINUX=disabled

#立即关闭
setenforce 0
# 查看状态，显示Disabled即可
getenforce

数据库初始化

#以下两种初始化方式选择一个就好
#不生成root账号密码
mysqld --initialize-insecure --user=mysql
#自动生成root账号密码
mysqld --initialize --user=mysql
#如果自动生成了root账号密码，那么可以mysql日志中查看初始密码
cat /var/log/mysqld.log

2023-02-03T06:55:54.523261Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.31) initializing of server in progress as process 41643 2023-02-03T06:55:54.552161Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2023-02-03T06:55:55.517362Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2023-02-03T06:55:57.053474Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: er:ExOzaI1b.

注意： er:ExOzaI1b. 就是随机生成的初始密码，注意保存

• 若需要重新初始化请按照以下步骤处理：

# 停止mysql服务
systemctl stop mysqld
# 删除/var/lib/mysql/*
rm -rf /var/lib/mysql/*
# 初始化mysql
mysqld --initialize --user=mysql
# 查看随机root密码
cat /var/log/mysqld.log

启动mysql服务

# 启动mysql
systemctl start mysqld
# 查看mysql状态
systemctl status mysqld

● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since 五 2023-02-03 14:56:27 CST; 5s ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 42677 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 42714 (mysqld) Status: "Server is operational" CGroup: /system.slice/mysqld.service └─42714 /usr/sbin/mysqld

2月 03 14:56:25 localhost.localdomain systemd[1]: Starting MySQL Server... 2月 03 14:56:27 localhost.localdomain systemd[1]: Started MySQL Server.

mysql服务常用指令

# 启动mysql服务
systemctl start mysqld
# 查看mysql服务状态
systemctl status mysqld
# 停止mysql服务
systemctl stop mysqld
# 重启mysql服务
systemctl restart mysqld

登录mysql修改root用户密码

登录mysql数据库

mysql -uroot -p
# 输入之前初始化的随机密码
Enter password:

修改root密码

ALTER USER 'root'@'localhost' IDENTIFIED BY 'ksf@123!';
quit;

验证修改后的root密码

mysql -uroot -p
# 输入修改后的root密码
Enter password:

主节点执行

创建新用户backup并授权同步数据

create user 'backup'@'%' IDENTIFIED BY 'backup@123!';
grant file on *.* to 'backup'@'%';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'backup'@'%';

重启之后从新连接进入数据库查看log_bin是否成功开启:

show variables like '%log_bin%';

然后查询主服务器的状态(后面配置从服务器需要主服务器的日志名称,和position号)

show master status;

记录mysql-bin.000001 | 157

在从服务器上设置主服务器,记得在从服务器的host中添加node1 主服务器ip

change master to master_host='172.17.92.66',master_port=3306,master_user='backup',master_password='kingstar@2023!',master_log_file='mysql-bin.000001',master_log_pos=157;

设置完成后查看从服务器状态

show slave status\G
stop slave;
start slave;

关闭同步，重新设置change，启动同步

初始化建库

-- 创建数据库
CREATE DATABASE db_taurus;
...

数据库新建用户并赋权

CREATE USER ksf@'%' IDENTIFIED BY 'ksf@123!';
GRANT ALL ON *.* TO 'ksf'@'%' WITH GRANT OPTION;
flush privileges;

来自为知笔记(Wiz)