- 主节点：10\.142\.18\.190
- 从节点：10\.142\.18\.191
# 两台节点都执行下面的命令（190,191）
卸载旧版本的数据库（系统可能自带，执行一下）
```
yum remove -y mariadb-libs mysql mysql-server
```
创建mysql用户
```
groupadd mysql
useradd -r -g mysql -s /bin/false mysql
```
解压安装包
```
mkdir -p /usr/local/mysql
tar -xvf mysql-8.0.43-linux-glibc2.17-x86_64.tar.xz -C /usr/local/mysql --strip-components=1
```
设置权限和符号链接
```
chown -R mysql:mysql /usr/local/mysql
mkdir -p /var/lib/mysql
chown mysql:mysql /var/lib/mysql
mkdir /etc/mysql
touch /etc/mysql/my.cnf
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
ln -s /usr/local/mysql/bin/mysqld /usr/bin/mysqld
```
创建配置文件my\.cnf（/etc/mysql/my\.cnf） 这里注意：主库id是1，从库id改为2
```
[mysqld]
server_id = 1
basedir = /usr/local/mysql
datadir = /var/lib/mysql
socket = /tmp/mysql.sock
port = 3306
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
ssl_ca = /var/lib/mysql/ca.pem
ssl_cert = /var/lib/mysql/server-cert.pem
ssl_key = /var/lib/mysql/server-key.pem
require_secure_transport = ON
log_bin = mysql-bin
binlog_format = ROW
bind-address = 0.0.0.0
skip-networking = false
gtid_mode = ON
enforce_gtid_consistency = ON
innodb_buffer_pool_size = 1G
max_connections = 200
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[client]
socket = /tmp/mysql.sock
```
初始化MySQL数据库
```
cd /usr/local/mysql
touch /var/log/mysqld.log
chown mysql:mysql /var/log/mysqld.log
chmod 660 /var/log/mysqld.log
chown -R mysql:mysql /var/lib/mysql
chmod 750 /var/lib/mysql
bin/mysqld --initialize --ssl --user=mysql --basedir=/usr/local/mysql --datadir=/var/lib/mysql
# 记录生成的临时root密码
grep 'temporary password' /var/log/mysqld.log
#o#V;/zdN-9EK
```
创建服务文件 /etc/systemd/system/mysqld\.service
```
[Unit]
Description=MySQL Server
After=network.target
[Service]
User=mysql
Group=mysql
Type=forking
ExecStart=/usr/local/mysql/bin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
ExecStop=/usr/local/mysql/bin/mysqladmin shutdown
Restart=on-failure
RestartSec=10
PrivateTmp=true
# 目录设置
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755
[Install]
WantedBy=multi-user.target
```
生成ssl证书
```
# 进入 MySQL 数据目录
cd /var/lib/mysql
# 生成 CA 私钥
sudo openssl genrsa -out ca-key.pem 2048
# 生成 CA 证书（有效期10年）
sudo openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca.pem \
-subj "/C=CN/ST=Beijing/L=Beijing/O=YourCompany/CN=MySQL CA"
# 生成服务器私钥
sudo openssl genrsa -out server-key.pem 2048
# 创建证书签名请求 (CSR)
sudo openssl req -new -key server-key.pem -out server-req.pem \
-subj "/C=CN/ST=Beijing/L=Beijing/O=YourCompany/CN=MySQL Server"
# 签署服务器证书
sudo openssl x509 -req -in server-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
# 生成客户端私钥和证书（可选，用于客户端验证）
sudo openssl genrsa -out client-key.pem 2048
sudo openssl req -new -key client-key.pem -out client-req.pem \
-subj "/C=CN/ST=Beijing/L=Beijing/O=YourCompany/CN=MySQL Client"
sudo openssl x509 -req -in client-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 02 -out client-cert.pem
sudo chown -R mysql:mysql /var/lib/mysql/*.pem
sudo chmod 600 /var/lib/mysql/*.pem
sudo chmod 644 /var/lib/mysql/ca.pem
sudo rm /var/lib/mysql/server-req.pem /var/lib/mysql/client-req.pem
```
启动MySQL服务 启动时如果报错别着急，等几秒，会刷新成running的
```
systemctl daemon-reload
mkdir -p /var/run/mysqld
systemctl start mysqld
systemctl enable mysqld
```
配置mysql
```
# 使用临时密码登录
mysql -u root -p -h 127.0.0.1
# 在MySQL提示符下执行：
-- 修改root密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'Nari@1234';
-- 基础安全配置
DELETE FROM mysql.user WHERE User='';
DROP DATABASE IF EXISTS test;
FLUSH PRIVILEGES;
```
配置环境变量
```
# 添加MySQL到PATH
echo 'export PATH=$PATH:/usr/local/mysql/bin' | sudo tee /etc/profile.d/mysql.sh
# 立即生效
source /etc/profile.d/mysql.sh
```
验证安装
```
# 检查MySQL版本
mysql --version
# 应输出：mysql Ver 8.0.43 for Linux on x86_64 (MySQL Community Server - GPL)
# 检查服务状态
systemctl status mysqld
# 连接测试
mysql -u root -p -h 127.0.0.1 -e "SHOW DATABASES;"
```
定期备份
```
# 使用mysqldump备份
mkdir /backups
mysqldump -u root -p -h 127.0.0.1 --all-databases > /backups/mysql-full-$(date +%F).sql
```
# 配置主备
主节点： 创建/修改用户权限
```
-- 使用 root 登录 MySQL
mysql -u root -p -h 127.0.0.1
-- 创建允许所有 IP 访问的用户（推荐创建专用用户）
CREATE USER 'master'@'%' IDENTIFIED BY 'master@1234';
-- 授予所有权限（根据需求调整权限范围）
GRANT ALL PRIVILEGES ON *.* TO 'master'@'%' WITH GRANT OPTION;
-- 刷新权限
FLUSH PRIVILEGES;
-- 退出
EXIT;
```
配置防火墙 放行3306
```
firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reload
# 验证端口是否开放
firewall-cmd --list-ports
```
主节点
```
scp /var/lib/mysql/ca.pem root@slave-ip:/var/lib/mysql/
```
从节点
```
chown mysql:mysql /var/lib/mysql/ca.pem
```
```
mysql -u root -p -h 127.0.0.1
```
```
-- 停止现有复制
STOP REPLICA;
-- 重置复制配置
RESET REPLICA ALL;
-- 配置GTID复制
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.45.1',
SOURCE_USER='master',
SOURCE_PASSWORD='master@1234',
SOURCE_SSL=1,
SOURCE_SSL_CA='/var/lib/mysql/ca.pem',
SOURCE_AUTO_POSITION=1; -- 如果使用 GTID
-- 启动复制
START REPLICA;
-- 查看状态
SHOW SLAVE STATUS\G
```
检查关键字段
```
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Retrieved_Gtid_Set: [应有值]
Executed_Gtid_Set: [应有值]
Last_IO_Error:
Last_SQL_Error:
```
测试 在主节点执行：
```
CREATE DATABASE replication_test;
```
在从节点执行
```
show databases;
```
---
来自 Daymica