在 Centos 7 中部署 MySQL 主从集群

通过 YUM 安装 MySQL

在主从服务器中均需要执行

下载 rpm 包

wget http://repo.mysql.com/mysql80-community-release-el7-1.noarch.rpm
rpm -ivh mysql80-community-release-el7-1.noarch.rpm

卸载 mariadb

rpm -qa|grep mariadb
rpm -e mariadb-libs-5.5.65-1.el7.x86_64 --nodeps

安装 MySQL

yum -y install mysql-server

如果出现 mysql-community-client-8.0.28-1.el7.x86_64.rpm 的公钥尚未安装，在安装之前执行 rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

启动 MySQL

systemctl restart mysqld
systemctl enable mysqld

获取 MySQL 密码并修改和配置远程访问

# 获取密码
grep "A temporary password is generated for root@localhost" /var/log/mysqld.log

# 登录数据库
mysql -u root -p

# 修改密码
alter user 'root'@'localhost' identified by 'Root123456!';

# 开启远程访问
update mysql.user set host = '%' where user = 'root';
alter user 'root'@'%' identified by 'Root123456!' password expire never;
alter user 'root'@'%' identified with mysql_native_password by 'Root123456!';
flush privileges;

主从数据库配置文件

修改主和从节点配置文件

主数据库配置文件

$ vim /etc/my.cnf

character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci
# 默认存储引擎
default-storage-engine=INNODB
# # Compatible with versions before 8.0
default_authentication_plugin=mysql_native_password
skip-host-cache
skip-name-resolve
# 主从配置
log-bin=binlog
server-id=1
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=on

[client]
#设置客户端编码
default-character-set=utf8mb4
[mysql]
# 设置mysql客户端默认编码
default-character-set=utf8mb4

$ vim /etc/my.cnf

# 修改为utf8编码
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci
# 默认存储引擎
default-storage-engine=INNODB
# Compatible with versions before 8.0
default_authentication_plugin=mysql_native_password
skip-host-cache
skip-name-resolve
# 主从配置
server-id=2
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=on

[client]
#设置客户端编码
default-character-set=utf8mb4
[mysql]
# 设置mysql客户端默认编码
default-character-set=utf8mb4

重启主和从数据库

systemctl restart mysqld

配置主从复制

在主节点创建slave账号

CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave';
GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
flush privileges;

获取主节点当前 binlog 和 position

SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000003 |      868 |              |                  | 1b009ef8-a67f-11ea-8c9a-0242ac110002:1-8 |
+---------------+----------+--------------+------------------+------------------------------------------+

配置从节点连接主节点

mysql>  CHANGE MASTER TO
mysql>  MASTER_HOST='192.168.0.10',
mysql>  MASTER_USER='slave',
mysql>  MASTER_PASSWORD='slave',
mysql>  MASTER_PORT=3306,
mysql>  MASTER_LOG_FILE='binlog.000003',
mysql>  MASTER_LOG_POS=868;

# 启动从节点
mysql> start slave;

# 再查看主从同步状态
mysql> show slave status;

这里只要看到两个参数 Slave_IO_Running 和 Slave_SQL_Running 都为 true 且 Error 字段都为空则主从集群正常工作。

建库验证

在主数据库创建 Rainbond 部署所需的数据库，查看从服务器是否同步更新了数据

在主库创建库

mysql>  create database console;
mysql>  create database region;

在从库查看

mysql>  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| console            |
| mysql              |
| performance_schema |
| region             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

数据同步成功，则主从复制部署完成