Linux CentOS 7下mysql5.7主从搭建--基于GTID

一、搭建环境

主(Master) ：192.168.1.173
从(Slave) ：192.168.1.174

[root@localhost 20]# cat /etc/redhat-release 
CentOS Linux release 7.7.1908 (Core)

MySQL版本：Server version: 5.7.24 MySQL Community Server (GPL)
学习环境下最好关闭防火墙：https://blog.csdn.net/qq_39680564/article/details/84940221

卸载自带的Mariadb

yum -y remove mariadb-libs

下载mysql-5.7.27

yum install -y wget
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar
tar -xvf mysql-5.7.27-1.el7.x86_64.rpm-bundle.tar

安装mysql-5.7.27

yum install -y libaio net-tools perl \ 
&& rpm -ivh mysql-community-common-5.7.27-1.el7.x86_64.rpm \
&& rpm -ivh mysql-community-libs-5.7.27-1.el7.x86_64.rpm \
&& rpm -ivh mysql-community-client-5.7.27-1.el7.x86_64.rpm \
&& rpm -ivh mysql-community-server-5.7.27-1.el7.x86_64.rpm \
&& rpm -ivh mysql-community-libs-compat-5.7.27-1.el7.x86_64.rpm

启动并设置开机自启

systemctl start mysqld && systemctl enable mysqld

获取初始密码

grep password /var/log/mysqld.log | sed 's/.*\(............\)$/\1/'

进入MySQL命令行，修改密码，设置访问权限

[root@localhost ~]# mysql -uroot -p 
Enter password: 

mysql> set password for root@localhost = password('123456Aa.');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'%' identified by '123456Aa.';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

二、修改配置文件

2.1 修改Master的my.cnf文件，在末尾增加如下内容

server_id = 1    #服务器id
gtid_mode = on    #开启gtid模式
enforce_gtid_consistency = on    #强制gtid一致性，开启后对特定的create table不被支持
log-bin = mysql-bin    #开启二进制日志
binlog_format = row    #默认为mixed混合模式，更改成row复制，为了数据一致性
log-slave-updates = 1    #从库binlog才会记录主库同步的操作日志
skip_slave_start=1    #跳过slave复制线程

2.2 修改Slave的my.cnf文件，在末尾增加如下内容

server_id = 2
log-bin = mysql-bin
binlog_format = row
log-slave-updates = 1
gtid_mode = on
enforce_gtid_consistency = on
skip_slave_start=1

2.3 分别重启两台MySQL

[root@localhost ~]# systemctl restart mysqld

三、修改数据库设置

3.1 Master设置

授权从库的复制权限

mysql> grant replication slave on *.* to 'root'@'192.168.1.174' identified by '123456Aa.';
Query OK, 0 rows affected, 1 warning (0.01 sec)

查看主库状态

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

3.2 Slave设置

设置链接主库的信息

mysql> change master to master_host='192.168.1.173',master_user='root',master_password='123456Aa.',master_log_file='mysql-bin.000001', master_log_pos=449;
Query OK, 0 rows affected, 2 warnings (0.04 sec)
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看是否开启

mysql> show slave status\G

应该能看到有这两项开启

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

四、验证

4.1 Master创建test库，并创建一个info表

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> create table info (name varchar(20),sex varchar(20));
Query OK, 0 rows affected (0.04 sec)

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

4.2 Slave查看是否有此库和info表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 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 |
+----------------+
| info           |
+----------------+
1 row in set (0.00 sec)