MySQL 5.7 主从集群搭建

IP	功能	Linux版本
192.168.56.136	Master	CentOS 7.9
192.168.56.140	Slave	CentOS 7.9

一、安装前的准备

1、卸载老版本

（1）查看是否安装mariadb（centos7默认安装）

命令： rpm -qa | grep mariadb

（2）卸载mariadb

命令：rpm -e mariadb-libs-5.5.68-1.el7.x86_64 --nodeps

2、安装依赖

命令：yum -y install perl perl-devel

二、安装MySQL（RPM包安装）

1、下载安装包

2、解压安装包

3、按如下顺序安装rpm包

rpm -ivh mysql-community-common-5.7.23-1.el7.x86_64.rpm 
rpm -ivh mysql-community-libs-5.7.23-1.el7.x86_64.rpm 
rpm -ivh mysql-community-client-5.7.23-1.el7.x86_64.rpm 
rpm -ivh mysql-community-server-5.7.23-1.el7.x86_64.rpm

4、启动服务

命令：systemctl restart mysqld

5、修改密码并授权

（1）通过日志查找到初始密码

命令：grep 'password' /var/log/mysql.log

（2）登录MySQL

mysql -u root -p（回车后输入刚查找到的密码）

（3）重新设置密码

set password=password('new_password')        # 不设置无法执行其它指令

（4）授权root用户

# 授予所有主机通过root用户和密码访问数据库所有表所有权限
grant all on *.* to root@'%' identified by 'new_password';

# 刷新权限
flush privileges;

三、配置主从

1、修改配置文件my.cnf

（1）主服务器配置

# 新增如下配置
server-id=1            # mysql实例唯一标识符
log-bin=master-log    # 二进制日志文件名和路径

（2）从服务器配置

# 新增如下配置
server-id=2

（3）重启服务

2、创建同步账号（主数据库执行）

# 1、登录mysql
mysql -u root -p

# 2、进入mysql数据库
use mysql

# 3、创建同步用户
create user master@'从数据库IP' identified with mysql_native_password by 'master_pass';

# 4、授权
grant replication slave on *.* to master@'从数据库IP' identified by 'master_pass';

# 5、刷新
flush privileges;

# 6、查看master状态
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| master-log.000001|     1    |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

3、开启同步（从服务器执行）

# 1、停止同步
stop slave;

# 2、配置从服务器连接主服务器的配置项
change master to master_host='主服务器IP',master_user='master',master_password='master_pass',master_log_file='bin-log文件名',master_log_pos=position;

# 3、开启同步
start slave;

# 4、刷新
flush privileges;

# 5、查看同步状态
show slave status\G;

四、检测

1、主服务器执行

# 创建数据库
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

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

mysql> use test;
Database changed
mysql> create table ceshi (id int auto_increment primary key, name varchar(50) not null, age int);
Query OK, 0 rows affected (0.03 sec)

2、从服务器查看

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

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| ceshi          |
+----------------+
1 row in set (0.00 sec)

mysql> desc ceshi;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(50) | NO   |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)