GTID主从配置（一从一从、一主多从、多主一从）

最新推荐文章于 2022-08-02 11:31:53 发布

1314444

最新推荐文章于 2022-08-02 11:31:53 发布

阅读量498

点赞数

分类专栏： MYSQL 文章标签： mysql

1314444

本文链接：https://blog.csdn.net/qq_48289488/article/details/120015336

版权

MYSQL 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

GTID主从搭建

什么是GTID

GTID(Global Transaction ID)是对于一个已提交事务的编号，并且是一个全局唯一的编号。
GTID实际上是由UUID+TID组成的。
其中UUID是一个MySQL实例的唯一标识，保存在mysql数据目录下的auto.cnf文件里。
TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增。

GTID与传统主从的区别:

传统的主从是基于二进制日志文件的复制,必须配置唯一的服务器id,并且服务器的id主必须大于从,否则主设备将拒绝来自从设备的任何连接
传统的主从复制从端，binlog是不用开启的，但是在GTID中从端的binlog是必须开启的，目的是记录执行过的GTID
GTID不用像传统复制那样保证binlog的坐标准确，因为根本不需要binlog以及坐标,并且不在使用binlog+pos开启复制.而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制.

GTID的工作原理：

在这里插入图片描述

master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
slave端的i/o线程将变更的binlog，写入到本地的relay log中。
sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
如果有记录，说明该GTID的事务已经执行，slave会忽略。
如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。

一主一从

需求

搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

数据库角色	IP地址	应用与系统版本
主：master	192.168.129.133	Redhat 8/ MySQL 5.7
从：slave	192.168.129.135	Redhat 7/ MySQL 5.7

安装MySQL

master：

/推荐下载MySQL5.7.34的版本
[root@master ~]# cd /usr/src
[root@master ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz 
...

//解压软件至/usr/local目录下，目录的位置可以是其他的地方
[root@master ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@master ~]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.34-linux-glibc2.12-x86_64  share

//创建用户和组
[root@master ~]# useradd -r -M -s /sbin/nologin mysql

//创建软连接
[root@master ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql
[root@master local]# ll -d /usr/local/mysql*
lrwxrwxrwx. 1 root root  46 8月  30 14:44 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 8月  30 14:41 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

//修改修改目录/usr/local/mysql的属主属组
[root@master ~]# chown -R mysql.mysql /usr/local/mysql*
[root@master ~]# ll -d /usr/local/mysql*
lrwxrwxrwx. 1 mysql mysql  46 8月  30 14:44 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 8月  30 14:41 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

//添加环境变量
[root@master ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@master ~]# source /etc/profile.d/mysql.sh
[root@master ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

//建立数据存放目录
[root@master ~]# mkdir -p /opt/data/
[root@master ~]# chown -R mysql.mysql /opt/data/
[root@master ~]# ll /opt/data
总用量 0

//初始化数据库（不要密码）
[root@master ~]# mysqld --initialize-insecure --datadir=/opt/data/ --user=mysql
2021-08-31T06:33:24.260349Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-31T06:33:24.493032Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-31T06:33:24.524102Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-31T06:33:24.529250Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 57d51369-0a25-11ec-8346-000c2931864c.
2021-08-31T06:33:24.530528Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-31T06:33:25.255441Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-31T06:33:25.287528Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

//安装依赖包
[root@master ~]# yum -y install ncurses-compat-libs
...

//生成配置文件
[root@master data]# vim /etc/my.cnf			#如果这个文件存在，请先备份再修改
[root@master data]# cat /etc/my.cnf 
[mysqld]
port = 3306
datadir = /opt/data/
basedir = /usr/local/mysql
socket = /tmp/mysql.sock
pid-file = /opt/data/mysql.pid
skip-name-resolve	#跳过名称解析

//配置服务启动脚本
[root@master ~]# cp -a /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
[root@master ~]# vim /etc/init.d/mysqld 		#修改文件中的这两个地方
basedir=/usr/local/mysql
datadir=/opt/data

//启动mysql服务
[root@master ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@master ~]# ss -antl|grep 3306
LISTEN    0         80                       *:3306                   *:* 

//设置新密码
[root@master ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> set password = password('redhat123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye

slave：

//在master将mysql-5.7包传输到slave上
[root@master ~]# scp /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz  192.168.129.135:/usr/src/
The authenticity of host '192.168.129.135 (192.168.129.135)' can't be established.
ECDSA key fingerprint is SHA256:rp+LMeFjAIapu3y+MH6bQa8QZpl/XRlUd1PammVI9E0.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.129.135' (ECDSA) to the list of known hosts.
root@192.168.129.135's password: 
mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz               100%  635MB 102.6MB/s   00:06 

//解压软件至/usr/local目录下，目录的位置可以是其他的地方
[root@slave ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@slave ~]# ls /usr/local/
apr       bin  games  include  lib64    mysql-5.7.34-linux-glibc2.12-x86_64  share
apr-util  etc  httpd  lib      libexec  sbin                                 src

//创建用户和组
[root@slave ~]# useradd -r -M -s /sbin/nologin mysql

//创建软连接
[root@slave ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql
[root@slave ~]# ll -d /usr/local/mysql*
lrwxrwxrwx 1 root root  46 8月  30 14:55 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x 9 root root 129 8月  30 14:52 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

//修改修改目录/usr/local/mysql的属主属组
[root@slave ~]# chown -R mysql.mysql /usr/local/mysql*
[root@slave ~]# ll -d /usr/local/mysql*
lrwxrwxrwx 1 mysql mysql  46 8月  30 14:55 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x 9 mysql mysql 129 8月  30 14:52 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

//添加环境变量
[root@slave ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@slave ~]# source /etc/profile.d/mysql.sh
[root@slave ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

//建立数据存放目录
[root@slave ~]# mkdir -p /opt/data/
[root@slave ~]# chown -R mysql.mysql /opt/data/
[root@slave ~]# ll /opt/data
总用量 0

//初始化数据库（不要密码）
[root@master ~]# mysqld --initialize-insecure --datadir=/opt/data/ --user=mysql
2021-08-31T06:33:24.260349Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-31T06:33:24.493032Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-31T06:33:24.524102Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-31T06:33:24.529250Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 57d51369-0a25-11ec-8346-000c2931864c.
2021-08-31T06:33:24.530528Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-31T06:33:25.255441Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-31T06:33:25.287528Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

//安装依赖包
[root@master ~]# yum -y install ncurses-compat-libs
...

//生成配置文件
[root@slave ~]# vim /etc/my.cnf
[root@slave ~]# cat /etc/my.cnf
[mysqld]
port = 3306
datadir = /opt/data/
basedir = /usr/local/mysql
socket = /tmp/mysql.sock
pid-file = /opt/data/mysql.pid
skip-name-resolve

//配置服务启动脚本
[root@slave ~]# cp -a /usr/local/mysql/support-files/mysql.server/etc/init.d/mysqld
[root@slave ~]# vim /etc/init.d/mysqld 
basedir=/usr/local/mysql
datadir=/opt/data

//启动mysql服务
[root@slave ~]# service mysqld start
 Starting MySQL. SUCCESS!
[root@slave ~]# ss -antl|grep 3306
LISTEN    0         80                       *:3306                   *:*       

//设置新密码
[root@master ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.34 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> set password = password('redhat123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye

关闭防火墙和SElinux

master：

[root@master ~]# systemctl disable --now firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@master ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@master ~]# setenforce 0

slave：

[root@slave ~]# systemctl disable --now firewalld.service 
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@slave ~]# sed -i s/SELINUX=enforing/SELINUX=disabled/g /etc/selinux/config 
[root@slave ~]# setenforce 0

配置主库文件

//在主数据库里创建一个同步账号授权给从数据库使用
mysql> grant replication slave on *.* to 'hhr'@'192.168.129.135' identified by 'hhr123!';		#无需提前创建用户，执行授权的时候会自动创建用户
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

[root@master ~]# vim /etc/my.cnf
server-id = 10                  #主库的server_id必须小于从库
gtid-mode = on                  #开启gtid
enforce-gtid-consistency = on   #强制gtid一致性
log-bin= mysql_bin              #开启二进制
binlog-format = row             #默认为mixed混合模式，更改成row复制，为了数据一致性
log-slave-updates = 1           #从库binlog才会记录主库同步的操作日志
skip-slave-start = 1            #跳过slave复制线程

//重启MySQL服务
[root@master ~]# service mysqld restart
Shutting down MySQL.... SUCCESS! 
Starting MySQL. SUCCESS!

//检查gtid模式状态
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

配置从库文件

[root@slave ~]# vim /etc/my.cnf 
server-id = 20                  #从库的server_id必须大于主库
gtid-mode = on                  #开启gtid
enforce-gtid-consistency = on   #强制gtid一致性
log-bin = mysql_bin             #开启二进制
binlog-format = row             #默认为mixed混合模式，更改成row复制，为了数据一致性
log-slave-updates = 1           #从库binlog才会记录主库同步的操作日志
skip-slave-start = 1            #跳过slave复制线程

//重启MySQL服务
[root@slave ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//检查gtid模式状态
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

//配置并启动主从复制
mysql> change master to
    -> master_host='192.168.129.133',
    -> master_user='hhr',
    -> master_password='hhr123!',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

//查看从服务器状态
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.129.133
                  Master_User: hhr
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 574
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 57d51369-0a25-11ec-8346-000c2931864c
             Master_Info_File: /opt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

测试

//主服务器创建数据库
mysql> create database hhr;
Query OK, 1 row affected (0.00 sec)

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

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

mysql> insert student (name,age) values('tom',20),('jerry',20),('wnagermazi',15),('zhangsan',19),('lisi',25);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
+----+------------+------+
5 rows in set (0.01 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000003 |      870 |              |                  | 57d51369-0a25-11ec-8346-000c2931864c:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

//查看从库中的数据是否同步成功
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hhr                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> select * from hhr.student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
+----+------------+------+
5 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000001 |      862 |              |                  | 57d51369-0a25-11ec-8346-000c2931864c:1-3 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

停止

//从库停止服务
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

//主库
mysql> insert student (name,age) values('qq',20),('ww',20),('ee',15),('rr',19),('lisi',25); 
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
|  6 | qq         |   20 |
|  7 | ww         |   20 |
|  8 | ee         |   15 |
|  9 | rr         |   19 |
| 10 | lisi       |   25 |
+----+------------+------+
10 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000003 |     1172 |              |                  | 57d51369-0a25-11ec-8346-000c2931864c:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

//从库开启服务
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from hhr.student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
|  6 | qq         |   20 |
|  7 | ww         |   20 |
|  8 | ee         |   15 |
|  9 | rr         |   19 |
| 10 | lisi       |   25 |
+----+------------+------+
10 rows in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000001 |     1156 |              |                  | 57d51369-0a25-11ec-8346-000c2931864c:1-4 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

一主多从

需求

搭建三台MySQL服务器，一台作为主服务器，一台作为从服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

数据库角色	IP地址	应用与系统版本
主：master	192.168.129.133	Redhat 8/ MySQL 5.7
从：slave	192.168.129.135	Redhat 7/ MySQL 5.7
从：slave2	192.168.129.250	Redhat 7/ MySQL 5.7

安装MySQL

master

略

slave

//在slave将mysql-5.7包传输到slave2上
[root@slave ~]# scp /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz  192.168.129.137:/usr/src/
The authenticity of host '192.168.129.137 (192.168.129.137)' can't be established.
ECDSA key fingerprint is SHA256:u29tZ8NW9v2uYAf2bAK7FOZwaxtjsxa0NVjgDTcaNTg.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.129.137' (ECDSA) to the list of known hosts.
root@192.168.129.137's password: 
mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz               100%  635MB 134.5MB/s   00:04 

[root@slave2 ~]# tar xf /usr/src/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@slave2 ~]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.34-linux-glibc2.12-x86_64  share

[root@slave2 ~]# ln -s /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64 /usr/local/mysql
[root@slave2 ~]# ll -d /usr/local/mysql*
lrwxrwxrwx. 1 root root  46 Aug 31 17:14 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 Aug 31 17:14 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

[root@slave2 ~]# chown -R mysql.mysql /usr/local/mysql*
[root@slave2 ~]# ll -d /usr/local/mysql*
lrwxrwxrwx. 1 mysql mysql  46 Aug 31 17:14 /usr/local/mysql -> /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 Aug 31 17:14 /usr/local/mysql-5.7.34-linux-glibc2.12-x86_64

root@slave2 ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@slave2 ~]# source /etc/profile.d/mysql.sh
[root@slave2 ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin

[root@slave2 ~]# mkdir -p /opt/data/
[root@slave2 ~]#  chown -R mysql.mysql /opt/data/
[root@slave2 ~]# ll /opt/data/
total 0

[root@slave2 ~]# mysqld --initialize --datadir=/opt/data/ --user=mysql
2021-08-31T09:15:31.711025Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-08-31T09:15:31.866033Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-08-31T09:15:31.889223Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-08-31T09:15:31.945429Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: fdd2fc8c-0a3b-11ec-82ba-000c29209bda.
2021-08-31T09:15:31.947661Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-08-31T09:15:32.598937Z 0 [Warning] CA certificate ca.pem is self signed.
2021-08-31T09:15:32.652206Z 1 [Note] A temporary password is generated for root@localhost: Qq73AjyWw2%/
[root@slave2 ~]# echo 'Qq73AjyWw2%/' > pass

//安装依赖包
[root@slave2 ~]# yum -y install ncurses-compat-libs

//位置文件
[root@slave2 ~]# vim /etc/my.cnf 
[mysqld]
port = 3306
datadir = /opt/data/
basedir = /usr/local/mysql
socket = /tmp/mysql.sock
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

//配置服务启动脚本
[root@slave2 ~]# cp -a /usr/local/mysql/support-files/mysql.server  /etc/init.d/mysqld
[root@slave2 ~]# vim /etc/init.d/mysqld 
basedir=/usr/local/mysql
datadir=/opt/data

//启动mysql服务
[root@slave2 ~]# service mysqld start
Starting MySQL. SUCCESS!
[root@slave2 ~]# ss -antl|grep 3306
LISTEN    0         80                       *:3306                   *:*   

//设置新密码
[root@slave2 ~]# mysql -uroot -p'Qq73AjyWw2%/'
mysql> set password = password('redhat123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

关闭防火墙和selinux

master、slave

略

配置主库文件

略

配置从库文件

[root@slave2 ~]# vim /etc/my.cnf
server-id = 21                  #从库的server_id必须大于主库
gtid-mode = on                  #开启gtid
enforce-gtid-consistency = on   #强制gtid一致性
log-bin = mysql_bin             #开启二进制
binlog-format = row             #默认为mixed混合模式，更改成row复制，为了数据一致性
log-slave-updates = 1           #从库binlog才会记录主库同步的操作日志
skip-slave-start = 1            #跳过slave复制线程

//重启MySQL服务
[root@slave2 ~]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

//检查gtid模式状态
mysql> show variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.01 sec)

//配置并启动主从复制
mysql> change master to
    -> master_host='192.168.129.133',
    -> master_user='hhr',
    -> master_password='hhr123!',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

//查看从服务器状态
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.129.133
                  Master_User: hhr
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 1618
               Relay_Log_File: slave2-relay-bin.000002
                Relay_Log_Pos: 1831
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1618
              Relay_Log_Space: 2039
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 57d51369-0a25-11ec-8346-000c2931864c
             Master_Info_File: /opt/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 57d51369-0a25-11ec-8346-000c2931864c:1-6
            Executed_Gtid_Set: 57d51369-0a25-11ec-8346-000c2931864c:1-6
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

测试

//从库查看
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000001 |     1602 |              |                  | 57d51369-0a25-11ec-8346-000c2931864c:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

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

mysql> use hhr;
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> select * from student;
+----+------------+------+
| id | name       | age  |
+----+------------+------+
|  1 | tom        |   20 |
|  2 | jerry      |   20 |
|  3 | wnagermazi |   15 |
|  4 | zhangsan   |   19 |
|  5 | lisi       |   25 |
|  6 | qq         |   20 |
|  7 | ww         |   20 |
|  8 | ee         |   15 |
|  9 | rr         |   19 |
| 10 | lisi       |   25 |
+----+------------+------+
10 rows in set (0.00 sec)

多主一从

需求

搭建三台MySQL服务器，两台作为主服务器，一台作为从服务器，从服务器进行读操作

数据库角色	IP地址	应用与系统版本
主：master	192.168.129.133	Redhat 8/ MySQL 5.7
主：slave	192.168.129.135	Redhat 7/ MySQL 5.7
从：slave2	192.168.129.250	Redhat 7/ MySQL 5.7

主库设置

//主库1设置：
mysql> grant replication slave on *.* to 'hhr'@'192.168.129.250'identified by 'hhr123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


//主库2设置：
mysql> grant replication slave on *.* to 'hhr'@'192.168.129.250'identified by 'hhr123!';
Query OK, 0 rows affected, 1 warning (0.00 sec)

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

mysql>  show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql_bin.000003 |      194 |              |                  | 51dda494-0a42-11ec-9fb3-000c299f4129:1-6 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

从库设置：

mysql> show  variables like '%gtid%';
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| binlog_gtid_simple_recovery      | ON        |
| enforce_gtid_consistency         | ON        |
| gtid_executed_compression_period | 1000      |
| gtid_mode                        | ON        |
| gtid_next                        | AUTOMATIC |
| gtid_owned                       |           |
| gtid_purged                      |           |
| session_track_gtids              | OFF       |
+----------------------------------+-----------+
8 rows in set (0.01 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)

在从库上配置主库信息

//下面两条命令不执行，后续会报错
mysql> set global master_info_repository='table';
Query OK, 0 rows affected (0.00 sec)

mysql> set global relay_log_info_repository='table';
Query OK, 0 rows affected (0.00 sec)

mysql> change master to
    -> master_host='192.168.129.133',
    -> master_user='hhr',
    -> master_password='hhr123!', 
    -> master_log_file='mysql_bin.000008', 
    -> master_log_pos=194
    -> for channel 'master-1';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

//配置主库2
mysql> change master to
    -> master_host='192.168.129.135',
    -> master_user='hhr',
    -> master_password='hhr123!', 
    -> master_log_file='mysql_bin.000003', 
    -> master_log_pos=194
    -> for channel 'master-1';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

查看从库状态

mysql>  show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.129.133
                  Master_User: hhr
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave2-relay-bin.000004
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql_bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 788
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 10
                  Master_UUID: 57d51369-0a25-11ec-8346-000c2931864c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 51dda494-0a42-11ec-9fb3-000c299f4129:1-5
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: master-1
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.129.135
                  Master_User: hhr
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000003
          Read_Master_Log_Pos: 194
               Relay_Log_File: slave2-relay-bin-master@002d1.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 194
              Relay_Log_Space: 541
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 11
                  Master_UUID: 51dda494-0a42-11ec-9fb3-000c299f4129
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 51dda494-0a42-11ec-9fb3-000c299f4129:1-5
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: master-2
           Master_TLS_Version: 
2 row in set (0.00 sec)

效果演示

//主库1插入数据：
mysql> create database hhr;
Query OK, 1 row affected (0.00 sec)

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


//主库2插入数据
mysql> create database hang;
Query OK, 1 row affected (0.00 sec)

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

查看从库变化

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| hang               |
| hhr                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)