MySQL8安装部署并配置主从复制集群（超详细）

2 基础环境准备

在 master 和 slave 都做！

2.1 修改服务器名

# master节点
hostnamectl set-hostname mysql-master

# slave节点执行
hostnamectl set-hostname mysql-slave

2.2 设置防火墙和SELinux

# 关闭防火墙
systemctl disable --now firewalld

# 关闭SELinux
sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config

# 临时关闭SELinux
setenforce 0

2.2 创建用户和目录

# 创建 mysql 用户
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

# 创建安装、数据、日志、临时目录
mkdir -p /data/mysql/{data,logs,tmp}

# 授权目录
chown -R mysql:mysql /data/mysql

3 安装 MySQL

3.1 下载 MySQL8.0

cd /data
wget https://cdn.mysql.com/archives/mysql-8.0/mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz

3.2 解压并移动到安装目录

# 解压
tar -xvf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz

# 移动部署目录
mv mysql-8.0.32-linux-glibc2.12-x86_64/* /data/mysql

# 设置权限
chown -R mysql:mysql /data/mysql

3.3 配置环境变量

# 将环境变量追加到/etc/profile尾部
echo 'export PATH=/data/mysql/bin:$PATH' >> /etc/profile

# 立即生效
source /etc/profile

3.4 安装依赖包

yum install -y libaio numactl ncurses-compat-libs libaio-devel

4 配置 MySQL 配置文件

新增配置文件

Master 节点配置（mysql-master）

cat >> /data/mysql/my.cnf 
[client]
port=3306
socket=/data/mysql/tmp/mysql.sock

[mysqld]
user=mysql
basedir=/data/mysql
datadir=/data/mysql/data
tmpdir = /data/mysql/tmp
port=3306
socket = /data/mysql/tmp/mysql.sock
pid-file  = /data/mysql/tmp/mysqld.pid
log-error = /data/mysql/logs/error.log

server-id=101
log-bin=mysql-bin
expire_logs_days = 30
binlog_format=ROW
relay-log=/data/mysql/logs/relay-bin
relay-log-index=/data/mysql/logs/relay-bin.index

max_connections=2000
character-set-server=utf8mb4
default-authentication-plugin=mysql_native_password

# 可以缓存索引和行数据,物理内存80%
innodb_buffer_pool_size = 2G
# 内存优化
innodb_buffer_pool_instances=32
innodb_log_file_size = 1G
innodb_log_buffer_size = 256M
innodb_page_cleaners = 32
innodb_thread_concurrency = 128
innodb_sort_buffer_size = 256M
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_stats_persistent_sample_pages = 28
innodb_online_alter_log_max_size = 2G
innodb_open_files = 8192

log-slave-updates=true

EOF

Slave 节点配置

cat > /data/mysql/my.cnf 
[client]
port=3306
socket=/data/mysql/tmp/mysql.sock

[mysqld]
user=mysql
basedir=/data/mysql
datadir=/data/mysql/data
tmpdir = /data/mysql/tmp
port=3306
socket = /data/mysql/tmp/mysql.sock
pid-file  = /data/mysql/tmp/mysqld.pid
log-error = /data/mysql/logs/error.log


server-id=102
log-bin=mysql-bin
expire_logs_days = 30
binlog_format=ROW
relay-log=/data/mysql/logs/relay-bin
relay-log-index=/data/mysql/logs/relay-bin.index

max_connections=2000
character-set-server=utf8mb4
default-authentication-plugin=mysql_native_password

#可以缓存索引和行数据,物理内存80%
innodb_buffer_pool_size = 2G
#内存缓冲池实例数量
innodb_buffer_pool_instances=32
innodb_log_file_size = 1G
innodb_log_buffer_size = 256M
innodb_page_cleaners = 32
innodb_thread_concurrency = 128
innodb_sort_buffer_size = 256M
innodb_write_io_threads = 16
innodb_read_io_threads = 16
innodb_stats_persistent_sample_pages = 28
innodb_online_alter_log_max_size = 2G
innodb_open_files = 8192

EOF

再修改一下权限

chown -R mysql.mysql /data/mysql

5  初始化 MySQL 数据库

mysqld --initialize --user=mysql  --basedir=/data/mysql --datadir=/data/mysql/data 

注意：初始化后会输出 root 初始密码，记下来！

6 配置 systemd 管理服务

在 master 和 slave 都做！路径改成你设置的：

cat >> /etc/systemd/system/mysqld.service 
[Unit]
Description=MySQL 8.0 Service
After=network.target

[Service]
User=mysql
Group=mysql
Type=notify
PIDFile=/data/mysql/tmp/mysqld.pid
ExecStart=/data/mysql/bin/mysqld --defaults-file=/data/mysql/my.cnf
ExecStop=/data/mysql/bin/mysqladmin --defaults-file=/data/mysql/my.cnf shutdown
LimitNOFILE=65535
Restart=on-failure
RestartPreventExitStatus=1
PrivateTmp=true

[Install]
WantedBy=multi-user.target

EOF

启动mysql

systemctl daemon-reload
systemctl start mysqld
systemctl enable mysqld

7 修改 root 密码

两个节点都执行

# 链接sock文件
ln -s /data/mysql/tmp/mysql.sock /tmp/mysql.sock

# 登录修改密码，根据之前初始化的密码进行登录
mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

8 创建主从复制账户（在 master 节点）

CREATE USER 'repl'@'10.0.0.%' IDENTIFIED BY 'repl123';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.0.0.%';
FLUSH PRIVILEGES;

查看master状态并记录下 File 和 Position 的值：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

9 配置主从复制（在 slave 节点）

# 停止复制进程
stop slave;

# 配置主服务器的连接信息，
CHANGE MASTER TO
MASTER_HOST='10.0.0.41',
MASTER_USER='repl',
MASTER_PASSWORD='repl123',
MASTER_LOG_FILE = 'mysql-bin.000004',
MASTER_LOG_POS = 157;

# MASTER_LOG_FILE是上面FIle的值，MASTER_LOG_POS为上面Position的值

# 启动复制进程
START SLAVE;

10  验证主从同步

# 在slave节点查看
SHOW SLAVE STATUSG

重点检查：

• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes
• Seconds_Behind_Master: 0

全部 Yes，延迟为 0，说明主从集群已经配置完成，并已实现数据同步！

11  主从复制基本验证

11.1 Master 节点创建数据库和表

CREATE DATABASE testdb;
USE testdb;
CREATE TABLE user(id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO user VALUES(1, 'Tom');

11.2 Slave 节点查询是否同步

SELECT * FROM testdb.user;

至此，主从复制集群部署并验证成功！