526互联

MySQL - 分布式部署&主从复制&读写分离

发布时间 2024-01-09 00:24:09作者: HOUHUILIN

 

 

 

Step1：基础环境准备

虚拟机环境：VMware workstation

服务器信息：

主机名 IP 操作系统
node01 192.168.1.131 CentOS Linux release 7.9.2009 (Core)
node02 192.168.1.132 CentOS Linux release 7.9.2009 (Core)
node03 192.168.1.133 CentOS Linux release 7.9.2009 (Core)
node04 192.168.1.134 CentOS Linux release 7.9.2009 (Core)

防火墙：已关闭，并禁止开机自启

systemctl stop firewalld && systemctl disable firewalld

SELinux：已禁用

sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/selinux/config

 

Step2：MySQL主从库角色分配

主机名 IP 角色
node01 192.168.1.131 不进行操作
node02 192.168.1.132 主库
node03 192.168.1.133 从库
node04 192.168.1.134 从库

 

 

Step3：安装MySQL

1、卸载CentOS 7.9默认安装的MariaDB

[root@node02 ~]# rpm -qa | grep mysql
[root@node02 ~]#
[root@node02 ~]#
[root@node02 ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
[root@node02 ~]#
[root@node02 ~]# rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64
[root@node02 ~]# rpm -qa | grep mariadb

2、官网下载mysql安装包，上传到node02节点（/root/software）并解压

# 在root目录下创建一个中转站的目录0_0（根据个人习惯）
[root@node02 0_0]# pwd
/root/0_0
[root@node02 0_0]# ll
总用量 823256
-rw-r--r-- 1 root root 843008000 1月  10 00:00 mysql-8.0.28-1.el7.x86_64.rpm-bundle.tar
[root@node02 0_0]#
[root@node02 0_0]#
[root@node02 0_0]#
[root@node02 0_0]# tar -xvf mysql-8.0.28-1.el7.x86_64.rpm-bundle.tar
mysql-community-client-8.0.28-1.el7.x86_64.rpm
mysql-community-client-plugins-8.0.28-1.el7.x86_64.rpm
mysql-community-common-8.0.28-1.el7.x86_64.rpm
mysql-community-devel-8.0.28-1.el7.x86_64.rpm
mysql-community-embedded-compat-8.0.28-1.el7.x86_64.rpm
mysql-community-icu-data-files-8.0.28-1.el7.x86_64.rpm
mysql-community-libs-8.0.28-1.el7.x86_64.rpm
mysql-community-libs-compat-8.0.28-1.el7.x86_64.rpm
mysql-community-server-8.0.28-1.el7.x86_64.rpm
mysql-community-test-8.0.28-1.el7.x86_64.rpm
[root@node02 0_0]#
[root@node02 0_0]#
[root@node02 0_0]# ll -l
总用量 1646504
-rw-r--r-- 1 root root  843008000 1月  10 00:00 mysql-8.0.28-1.el7.x86_64.rpm-bundle.tar
-rw-r--r-- 1 7155 31415  55199948 12月 18 2021 mysql-community-client-8.0.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   5933684 12月 18 2021 mysql-community-client-plugins-8.0.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415    645388 12月 18 2021 mysql-community-common-8.0.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   7763684 12月 18 2021 mysql-community-devel-8.0.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415  23637584 12月 18 2021 mysql-community-embedded-compat-8.0.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   2215928 12月 18 2021 mysql-community-icu-data-files-8.0.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   4935572 12月 18 2021 mysql-community-libs-8.0.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415   1265072 12月 18 2021 mysql-community-libs-compat-8.0.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 473116268 12月 18 2021 mysql-community-server-8.0.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 268279684 12月 18 2021 mysql-community-test-8.0.28-1.el7.x86_64.rpm

3、按照以下顺序安装

[root@node02 0_0]# rpm -ivh mysql-community-common-8.0.28-1.el7.x86_64.rpm
警告：mysql-community-common-8.0.28-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-common-8.0.28-1.e################################# [100%]
[root@node02 0_0]# rpm -ivh mysql-community-client-plugins-8.0.28-1.el7.x86_64.rpm
警告：mysql-community-client-plugins-8.0.28-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-client-plugins-8.################################# [100%]
[root@node02 0_0]#
[root@node02 0_0]# rpm -ivh mysql-community-libs-8.0.28-1.el7.x86_64.rpm
警告：mysql-community-libs-8.0.28-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-libs-8.0.28-1.el7################################# [100%]
[root@node02 0_0]# rpm -ivh mysql-community-client-8.0.28-1.el7.x86_64.rpm
警告：mysql-community-client-8.0.28-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-client-8.0.28-1.e################################# [100%]
[root@node02 0_0]#
[root@node02 0_0]# rpm -ivh mysql-community-icu-data-files-8.0.28-1.el7.x86_64.rpm
警告：mysql-community-icu-data-files-8.0.28-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-icu-data-files-8.################################# [100%]
[root@node02 0_0]# rpm -ivh mysql-community-server-8.0.28-1.el7.x86_64.rpm
警告：mysql-community-server-8.0.28-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-server-8.0.28-1.e################################# [100%]
[root@node02 0_0]#

4、根据需要修改安装目录权限，将安装目录的拥有者改为mysql，如果系统没有，需要添加mysql用户及分组

只是用mysql运行程序，禁止mysql登录，增加安全性

[root@node02 0_0]# id mysql
id: mysql: no such user
[root@node02 0_0]# groupadd mysql
[root@node02 0_0]# useradd mysql -s /usr/sbin/nologin -g mysql
[root@node02 0_0]# id mysql
uid=1000(mysql) gid=1000(mysql) 组=1000(mysql)
[root@node02 0_0]# chown -R mysql:mysql /var/lib/mysql

4、初始化数据库

[root@node02 0_0]# mysqld --initialize --console
[root@node02 0_0]#

5、启动mysql服务，并查看初始化密码

[root@node02 0_0]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead)
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
[root@node02 0_0]#
[root@node02 0_0]#
[root@node02 0_0]#
[root@node02 0_0]#
[root@node02 0_0]# systemctl start mysqld
[root@node02 0_0]#
[root@node02 0_0]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 三 2024-01-10 00:08:09 CST; 3s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3204 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3228 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─3228 /usr/sbin/mysqld

1月 10 00:08:08 node02 systemd[1]: Starting MySQL Server...
1月 10 00:08:09 node02 systemd[1]: Started MySQL Server.
[root@node02 0_0]#
[root@node02 0_0]# cat /var/log/mysqld.log | grep localhost
2024-01-09T16:04:24.123912Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: #s?deDDo(1sW
[root@node02 0_0]# mysql -uroot -p#s?deDDo(1sW
-bash: 未预期的符号 `(' 附近有语法错误
[root@node02 0_0]# mysql -uroot -p'#s?deDDo(1sW'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.28

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

6、修改root密码

mysql> alter user 'root'@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
[root@node02 0_0]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.28 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 

Step4：主从复制

前提条件：在node03、node04也进行mysql的安装

未完待续......