mysql传统主从及GTID主从原理及配置

咖啡调调。

已于 2022-10-24 12:58:01 修改

阅读量539

点赞数

分类专栏： MySQL 文章标签： mysql 数据库服务器 1024程序员节

于 2022-08-02 10:38:02 首次发布

本文链接：https://blog.csdn.net/qq_65998623/article/details/126117405

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

MySQL主从

文章目录

MySQL主从
@[toc]
1. 传统主从
1.1 主从原理

2. GTID主从
2.1 GTID原理

3. 传统主从配置
4. GTID主从配置

1. 传统主从

主从作用
- 实时灾备，用于故障切换
- 读写分离，提供查询服务
- 备份，避免影响业务
主从形式
- 一主一丛
- 主主复制
- 一主多从（扩展系统读取的性能，以为读是在从库中读取的）
- 多主一从（5.7版本开始支持）
- 联级复制

1.1 主从原理

主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程
从库生成两个线程，一个I/O线程，一个SQL线程
- I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志）文件中
- SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的

2. GTID主从

GTID实际上是由UUID+TID (即transactionId)组成的。其中UUID(即server_uuid) 产生于auto.conf文件(cat /data/mysql/data/auto.cnf)，是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。GTID在一组复制中，全局唯一。

2.1 GTID原理

从服务器连接到主服务器之后，把自己执行过的GTID、获取到的GTID发给主服务器，主服务器把从服务器缺少的GTID及对应的transactions发过去补全即可。

当主服务器挂掉的时候，找出同步最成功的那台从服务器，直接把它提升为主即可。如果硬要指定某一台不是最新的从服务器提升为主，先change到同步最成功的那台从服务器，等把GTID全部补全了，就可以把它提升为主了。

GTID的工作流程：

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

优势：

更简单的实现‎故障转移‎，不用像传统主从那样需要找log_file和log_pos
更简单的搭建主从复制
比传统的复制更加安全
GTID是连续的没有空洞的，保证数据的一致性，零丢失

3. 传统主从配置

需求:
搭建两台mysql服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作

环境说明：

数据库角色	IP	应用与系统版本	有无数据
主数据库	192.168.183.131	centos8/redhat8 mysql-5.7	有数据
从数据库	192.168.183.133	centos8/redhat8 mysql-5.7	无数据

配置：

在两台初始化环境虚拟机做以下相同配置

[root@master ~]# dnf -y install mariadb-server
Last metadata expiration check: 0:09:52 ago on Mon 01 Aug 2022 09:28:03 PM CST.
Dependencies resolved.
······
[root@master ~]# systemctl disable --now firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
[root@master ~]# vim /etc/selinux/config 
SELINUX=disabled
[root@master ~]# setenforce 0
[root@master ~]# systemctl start mariadb
[root@master ~]# ss -antl
State   Recv-Q  Send-Q    Local Address:Port     Peer Address:Port  Process  
LISTEN  0       128             0.0.0.0:22            0.0.0.0:*              
LISTEN  0       80                    *:3306                *:*              
LISTEN  0       128                [::]:22               [::]:*              
[root@master ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

MariaDB [(none)]> set password = password('123!com');
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> quit
Bye

注意：
需要确保主从数据库的数据是一致的，因为此实验都为初始化环境，所以无需做备份

在主数据库里创建一个同步账号授权给从数据库使用

MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.183.133' identified by '123!com';
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> quit;
Bye
[root@master ~]#

配置主数据库

[root@master ~]# cd /etc/my.cnf.d/
[root@master my.cnf.d]# ls
auth_gssapi.cnf  enable_encryption.preset  mysql-clients.cnf
client.cnf       mariadb-server.cnf
[root@master my.cnf.d]# vi mariadb-server.cnf 
	 ······
	 21 log-bin = mysql-bin	//启用binlog日志
     22 server-id = 10	//数据库服务器唯一标识符，主库的server-id至必须比从库的大
······

//重启服务
[root@master my.cnf.d]# systemctl restart mariadb
[root@master my.cnf.d]# cd
[root@master ~]# mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

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

//查看主库的状态
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]>

配置从数据库

[root@slave ~]# vi /etc/my.cnf.d/mariadb-server.cnf	 
	 21 server-id = 20	//设置从库的唯一标识符，从库的server-id至必须大于主库的该值
     22 relay-log = myrelay	//启用中继日志relay-log，名称可自定义，一般常用myrelay
     
//重启服务
[root@slave ~]# systemctl restart mariadb

//配置并启动主从复制
ariaDB [(none)]> change master to master_host='192.168.183.131', master_user='repl', master_password='123!com', master_log_file='mysql_bin.000001', master_log_pos=328;
Query OK, 0 rows affected (0.003 sec)

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)

//以列的形式查看从服务器的状态
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.183.131
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 328
                Relay_Log_File: myrelay.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: Yes	//此处必须为yes
             Slave_SQL_Running: Yes	//此处必须为yes
               Replicate_Do_DB: 
           Replicate_Ignore_DB: 
            Replicate_Do_Table:

测试验证

//在主库创建一个数据库george
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)

MariaDB [(none)]> create database george;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| george             |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

MariaDB [(none)]> 


//在从库上查看也同样存在
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| george             |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)


//在主库插入数据
MariaDB [(none)]> use george;
Database changed
MariaDB [george]> create table student (id int not null,name varchar(50));
Query OK, 0 rows affected (0.003 sec)

MariaDB [george]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.001 sec)

MariaDB [george]> insert student values(1,'tom'),(2,'jerry');
Query OK, 2 rows affected (0.001 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [george]> 


//在从库进行查看
MariaDB [(none)]> show tables from george;
+------------------+
| Tables_in_george |
+------------------+
| student          |
+------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> desc george.student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.001 sec)

MariaDB [(none)]> select * from george.student;
+----+-------+
| id | name  |
+----+-------+
|  1 | tom   |
|  2 | jerry |
+----+-------+
2 rows in set (0.000 sec)

MariaDB [(none)]>

主从同步功能实现。

4. GTID主从配置

环境说明：
提前关闭防火墙及selinux

数据库角色	IP	应用与系统版本
主数据库	192.168.183.131	centos8/redhat8 mysql-5.7
从数据库	192.168.183.133	centos8/redhat8 mysql-5.7

mysql安装方式：二进制安装

主库配置

//添加配置文件
[root@master ~]# vim /etc/my.cnf
 10 log-bin=mysql_bin
 11 server-id=10
 12 gtid_mode=on
 13 enforce-gtid-consistency=true
 14 log-slave-updates=on

[root@master ~]# service mysqld restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL.Logging to '/opt/data/master.err'.
. SUCCESS!

从库配置

[root@slave ~]# vim /etc/my.cnf

 10 server-id=20
 11 relay-log=myrelay
 12 gtid_mode=on
 13 enforce-gtid-consistency=true
 14 log-slave-updates=on
 15 read_only=on
 16 master-info-repository=TABLE
 17 relay-log-info-repository=TABLE

[root@slave ~]# service mysqld restart
 ERROR! MySQL server PID file could not be found!
Starting MySQL.Logging to '/opt/data/slave.err'.
. SUCCESS!

主库授权复制用户

mysql> grant replication slave on *.* to 'repl'@'%' identified by '123com';
Query OK, 0 rows affected, 1 warning (0.01 sec)

从库设置要同步的主库信息，并开启同步

mysql> change master to master_host='192.168.183,131',
    -> master_port=3306,master_user='repl',master_password='123com',
    -> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.183,131
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: myrelay.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB:

验证测试

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

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


//在从库查看，同步到从库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| george_1           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)