mysql主从

mysql主从

传统主从 与 GTID主从 的原理及区别

传统主从

普通主从复制主要是基于二进制日志文件位置的复制，因此主必须启动二进制日志记录并建立唯一的服务器ID，复制组中的每个服务器都必须配置唯一的服务器ID。如果您省略server-id（或者明确地将其设置为其默认值0），则主设备将拒绝来自从设备的任何连接。

GTID 主从

基本概念

MySQL 5.6 的新特性之一，全局事务标识符（GTID）是创建的唯一标识符，并与在源（主）服务器上提交的每个事务相关联。此标识符不但是唯一的，而且在给定复制设置中的所有服务器上都是唯一的。所有交易和所有GTID之间都有一对一的映射关系 。它由服务器ID以及事务ID组合而成。这个全局事务ID不仅仅在原始服务器上唯一，在所有存在主从关系 的mysql服务器上也是唯一的。正是因为这样一个特性使得mysql的主从复制变得更加简单，以及数据库一致性更可靠。一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。

一个GTID被表示为一对坐标，用冒号（:）分隔，如下所示：GTID = source_id:transaction_id，source_id标识的源服务器。通常情况下，服务器 server_uuid用于这个目的。这transaction_id是一个序列号，由在此服务器上提交事务的顺序决定 .

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

在传统的主从复制slave端，binlog是不用开启的，但是在GTID中slave端的binlog是必须开启的，目的是记录执行过的GTID（强制）。GTID用来代替classic的复制方法，不在使用binlog+pos开启复制。而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制。

mysql的主从复制是十分经典的一个应用，但是主从之间总会有数据一致性（data consistency ）的问题，一般情况从库会落后主库几个小时，而且在传统一主多从(mysql5.6之前)的模型中当master down掉后，我们不只是需要将一个slave提成master就可以，还要将其他slave的同步目的地从以前的master改成现在master，而且bin-log的序号和偏移量也要去查看，这是十分不方便和耗时的，但mysql5.6引入gtid之后解决了这个问题。

GTID的工作原理

1、当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。
2、binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即 告诉Slave，下一个要执行的GTID值。
3、sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。
4、如果有记录，说明该GTID的事务已经执行，slave会忽略。
5、如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，
在读取执行事务前会先检查其他session持有该GTID，确保不被重复执行。
6、在解析过程中会判断是否有主键，如果有就用二级索引，如果没有就用全部扫描。

传统主从 与 GTID主从配置

传统主从配置

确保从数据库与主数据库里的数据一样
为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中

//先查看主库有哪些库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chenlang           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
//再查看从库有哪些库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
//全备主库
//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
//此锁表的终端必须在备份完成以后才能退出

//备份主库并将备份文件传送到从库
[root@cl ~]# mysqldump -uroot -pchenlang123! --all-databases > /opt/all-20220802.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@cl ~]# ls /opt/
11  aa  all-20220802.sql  htop
[root@cl ~]# scp /opt/all-20220802.sql root@192.168.58.135:/opt/
The authenticity of host '192.168.58.135 (192.168.58.135)' can't be established.
ECDSA key fingerprint is SHA256:CjeIARsm2XYz/9jH91JiCMfAndt/adprYHDXHcOVY8g.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.58.135' (ECDSA) to the list of known hosts.
root@192.168.58.135's password: 
all-20220802.sql                100%  860KB  86.8MB/s   00:00
//解除主库的锁表状态，直接退出交互式界面即可
mysql> exit
Bye
//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@cl ~]# mysql -uroot -pchenlang123! < /opt/all-20220802.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chenlang           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

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

mysql> create user 'repl'@'192.168.58.135' identified by 'repl123!';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'repl'@'192.168.58.135';
Query OK, 0 rows affected (0.00 sec)

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

配置主数据库

[root@cl ~]# vim /etc/my.cnf
log-bin=mysql-bin   //启用binlog日志
server-id=10     //数据库服务器唯一标识符，主库的server-id值必须比从库的小
vim /etc/selinux/config
SELINUX=disabled
[root@cl ~]# systemctl restart mysqld
[root@cl ~]# 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      128               [::]:22             [::]:*            
LISTEN 0      80                   *:3306              *:* 
//查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000008 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从数据库

[root@cl ~]# vim /etc/my.cnf
server-id=20     //设置从库的唯一标识符，从库的server-id值必须大于主库的该值
relay-log=mysql-relay-bin       //启用中继日志relay-log
[root@cl ~]# systemctl restart mysqld
[root@cl ~]# 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      128             [::]:22           [::]:*            
LISTEN 0      80                 *:3306            *:*
mysql> change master to
    -> master_host='192.168.58.130',
    -> master_user='repl',
    -> master_password='repl123!',
    -> master_log_file='mysql-bin.000010',
    -> master_log_pos=154;
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.58.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000010
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000013
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql_bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试验证
在主服务器的student库的bj2表中插入数据

mysql> create table text (id int not null,age tinyint);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into text (id,age) values (1,12),(2,24);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from text;
+----+------+
| id | age  |
+----+------+
|  1 |   12 |
|  2 |   24 |
+----+------+
2 rows in set (0.00 sec)

在从数据库中查看数据是否同步

mysql> use chenlang;
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 text;
+----+------+
| id | age  |
+----+------+
|  1 |   12 |
|  2 |   24 |
+----+------+
2 rows in set (0.00 sec)

GTID主从配置

主库配置。vi /etc/my.cnf，添加以下配置，重启mysql

[root@cl ~]# vim /etc/my.cnf
log-bin=mysql_bin
server-id=10
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on

从库配置。vi /etc/my.cnf, 添加以下配置，重启mysql

[root@ll ~]# vim /etc/my.cnf
server-id=20
relay-log=myrelay
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on
read_only=on
master-info-repository=TABLE
relay-log-info-repository=TABLE

主库授权复制用户

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

mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> change master to master_host='192.168.58.130',
    -> master_port=3306,master_user='repl',master_password='repl123!',
    -> 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.58.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql_bin.000010
          Read_Master_Log_Pos: 154
               Relay_Log_File: ll-relay-bin.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: mysql_bin.000010
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes