mysql主从
mysql主从
目录
一、主从简介
1.主从的作用
- 实时灾备，用于故障切换
- 读写分离，提供查询服务
- 备份，避免影响业务
2.主从形式
- 一主一从
- 主主复制
- 一主多从---扩展系统读取的性能，因为读是在从库读取的
- 多主一从---5.7开始支持
- 联级复制
二、主从复制原理
主从复制步骤：
- 主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程
- 从库生成两个线程，一个I/O线程，一个SQL线程
- I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中
- SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的
三、主从复制配置
主从复制配置步骤：
- 确保从数据库与主数据库里的数据一样
- 在主数据库里创建一个同步账号授权给从数据库使用
- 配置主数据库（修改配置文件）
- 配置从数据库（修改配置文件）
需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
环境说明：
| 数据库角色 | IP | 应用与系统版本 | 有无数据 | 主机名 |
|---|---|---|---|---|
| 主数据库 | 192.168.118.130 | centos8/mysql-5.7 | 有数据 | 130 |
| 从数据库 | 192.168.118.136 | centos8/mysql-5.7 | 无数据 | 136 |
1.安装mysql
给两台服务器安装mysql
安装过程参考:
https://www.cnblogs.com/Clannaddada/p/16517869.html
复制到网页打开，内有安装mysql的教程
2.mysql主从配置
确保从数据库与主数据库里的数据一样
为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中
//先查看主库有哪些库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| 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@130 ~]# mysqldump -uroot -p'654321' --all-databases > /opt/all-20220802.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@130 ~]# scp /opt/all-20220802.sql root@192.168.118.136:/opt/
The authenticity of host '192.168.118.137 (192.168.118.137)' can't be established.
ECDSA key fingerprint is SHA256:+RwKWt1ErJuzJKcNeLslD46lbAtIO3wmNQk9BhIrtxs.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.118.137' (ECDSA) to the list of known hosts.
root@192.168.118.137's password:
all-20220801.sql 100% 857KB 134.5MB/s 00:00
//解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye
//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@136 ~]# mysql -uroot -p123456 < /opt/all-20220802.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@136 ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
在主数据库里创建一个同步账号授权给从数据库使用
mysql> create user 'repl'@'192.168.118.136' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.118.136';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
配置主数据库
配置主数据库my.cnf文件
[root@130 ~]# vim /etc/my.cnf
[root@130 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql-bin //启用binlog日志
server-id=10 //数据库服务器唯一标识符，此处需要小于从库
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[root@130 ~]# systemctl restart mysqld.service //重启mysql
//查看主库的状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
配置从数据库
[root@136 ~]# vim /etc/my.cnf
[root@136 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
relay-log=mysql-relay-bin //此处为中继日志
server-id=20
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[root@136 ~]# systemctl restart mysqld.service
配置并启动主从复制
#从库配置
mysql> change master to
-> master_host='192.168.118.130', //主库IP
-> master_user='repl',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> 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.118.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //此处必须为yes
Slave_SQL_Running: Yes //此处必须为yes
Replicate_Do_DB:
Replicate_Ignore_DB:
测试验证
在主数据库的runtime库的dxy表中插入数据
mysql> use runtime;
Database changed
mysql> create table dxy (id int not null,name varchar(10),age tinyint(3));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into dxy (id,name,age) values (1,'dlp',23),(2,'zxy',25),(3,'nby',28),(4,'dxy',26);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from dxy;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | dlp | 23 |
| 2 | zxy | 25 |
| 3 | nby | 28 |
| 4 | dxy | 26 |
+----+------+------+
4 rows in set (0.00 sec)
在从数据库中查看数据是否同步
mysql> use runtime;
Database changed
mysql> select * from dxy;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | dlp | 23 |
| 2 | zxy | 25 |
| 3 | nby | 28 |
| 4 | dxy | 26 |
+----+------+------+
4 rows in set (0.00 sec)
四、GTID主从
1.GTID概念
GTID即全局事务ID (global transaction identifier), 其保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID。GTID最初由google实现，官方MySQL在5.6才加入该功能。mysql主从结构在一主一从情况下对于GTID来说就没有优势了，而对于2台主以上的结构优势异常明显，可以在数据不丢失的情况下切换新主。使用GTID需要注意: 在构建主从复制之前，在一台将成为主的实例上进行一些操作（如数据清理等），通过GTID复制，这些在主从成立之前的操作也会被复制到从服务器上，引起复制失败。也就是说通过GTID复制都是从最先开始的事务日志开始，即使这些操作在复制之前执行。比如在server1上执行一些drop、delete的清理操作，接着在server2上执行change的操作，会使得server2也进行server1的清理操作。
GTID实际上是由UUID+TID (即transactionId)组成的。其中UUID(即server_uuid) 产生于auto.conf文件(cat /data/mysql/data/auto.cnf)，是一个MySQL实例的唯一标识。TID代表了该实例上已经提交的事务数量，并且随着事务提交单调递增，所以GTID能够保证每个MySQL实例事务的执行（不会重复执行同一个事务，并且会补全没有执行的事务）。GTID在一组复制中，全局唯一。
下面是一个GTID的具体形式 :
mysql> show master status;
+-----------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------+----------+--------------+------------------+-------------------------------------------+
| on.000003 | 187 | | | 7286f791-125d-11e9-9a9c-0050568843f8:1-362|
+-----------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
GTID:7286f791-125d-11e9-9a9c-0050568843f8:1-362
UUID:7286f791-125d-11e9-9a9c-0050568843f8
transactionId:1-362
在整个复制架构中GTID 是不变化的,即使在多个连环主从中也不会变。
例如：ServerA --->ServerB ---->ServerC
GTID从在ServerA ,ServerB,ServerC 中都是一样的。
了解了GTID的格式，通过UUID可以知道这个事务在哪个实例上提交的。通过GTID可以极方便的进行复制结构上的故障转移，新主设置，这就很好地解决了下面这个图所展现出来的问题。
如图, Server1(Master)崩溃，根据从上show slave status获得Master_log_File/Read_Master_Log_Pos的值，Server2(Slave)已经跟上了主，Server3(Slave)没有跟上主。这时要是把Server2提升为主，Server3变成Server2的从。这时在Server3上执行change的时候需要做一些计算。
这个问题在5.6的GTID出现后，就显得非常的简单。由于同一事务的GTID在所有节点上的值一致，那么根据Server3当前停止点的GTID就能定位到Server2上的GTID。甚至由于MASTER_AUTO_POSITION功能的出现，我们都不需要知道GTID的具体值，直接使用CHANGE MASTER TO MASTER_HOST='xxx', MASTER_AUTO_POSITION命令就可以直接完成failover的工作。
====== GTID和Binlog的关系 ======
- GTID在binlog中的结构
- GTID event 结构
- Previous_gtid_log_event
Previous_gtid_log_event 在每个binlog 头部都会有每次binlog rotate的时候存储在binlog头部Previous-GTIDs在binlog中只会存储在这台机器上执行过的所有binlog，不包括手动设置gtid_purged值。换句话说，如果你手动set global gtid_purged=xx； 那么xx是不会记录在Previous_gtid_log_event中的。 - GTID和Binlog之间的关系是怎么对应的呢? 如何才能找到GTID=? 对应的binlog文件呢？
假设有4个binlog: bin.001,bin.002,bin.003,bin.004
bin.001 : Previous-GTIDs=empty; binlog_event有: 1-40
bin.002 : Previous-GTIDs=1-40; binlog_event有: 41-80
bin.003 : Previous-GTIDs=1-80; binlog_event有: 81-120
bin.004 : Previous-GTIDs=1-120; binlog_event有: 121-160
假设现在我们要找GTID=$A，那么MySQL的扫描顺序为: - 从最后一个binlog开始扫描（即: bin.004）
- bin.004的Previous-GTIDs=1-120，如果$A=140 > Previous-GTIDs,那么肯定在bin.004中
- bin.004的Previous-GTIDs=1-120，如果$A=88 包含在Previous-GTIDs中,那么继续对比上一个binlog文件 bin.003,然后再循环前面2个步骤，直到找到为止.
- GTID相关参数
| 参数 | comment |
|---|---|
| gtid_executed | 执行过的所有GTID |
| gtid_purged | 丢弃掉的GTID |
| gtid_mode | GTID模式 |
| gtid_next | session级别的变量，下一个gtid |
| gtid_owned | 正在运行的GTID |
| enforce_gtid_consistency | 保证GTID安全的参数 |
====== 开启GTID的必备条件 ======
gtid_mode=on (必选)
enforce-gtid-consistency=1 （必选）
log_bin=mysql-bin （可选） #高可用切换，最好开启该功能
log-slave-updates=1 （可选） #高可用切换，最好打开该功能
2.GTID工作原理
从服务器连接到主服务器之后，把自己执行过的GTID (Executed_Gtid_Set: 即已经执行的事务编码) 、获取到的GTID (Retrieved_Gtid_Set: 即从库已经接收到主库的事务编号) 发给主服务器，主服务器把从服务器缺少的GTID及对应的transactions发过去补全即可。当主服务器挂掉的时候，找出同步最成功的那台从服务器，直接把它提升为主即可。如果硬要指定某一台不是最新的从服务器提升为主， 先change到同步最成功的那台从服务器， 等把GTID全部补全了，就可以把它提升为主了。
GTID是MySQL 5.6的新特性，可简化MySQL的主从切换以及Failover。GTID用于在binlog中唯一标识一个事务。当事务提交时，MySQL Server在写binlog的时候，会先写一个特殊的Binlog Event，类型为GTID_Event，指定下一个事务的GTID，然后再写事务的Binlog。主从同步时GTID_Event和事务的Binlog都会传递到从库，从库在执行的时候也是用同样的GTID写binlog，这样主从同步以后，就可通过GTID确定从库同步到的位置了。也就是说，无论是级联情况，还是一主多从情况，都可以通过GTID自动找点儿，而无需像之前那样通过File_name和File_position找点儿了。
简而言之，GTID的工作流程为：
- master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
- slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
- sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
- 如果有记录，说明该GTID的事务已经执行，slave会忽略。
- 如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
- 在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。
3.GTID主从配置
环境说明：
| 数据库角色 | IP | 应用与系统版本 |
|---|---|---|
| 主数据库 | 192.168.118.130 | centos8/redhat8 mysql-5.7 |
| 从数据库 | 192.168.118.136 | centos8/redhat8 mysql-5.7 |
主库从库数据统一
主库操作
[root@130 ~]# mysql -uroot -p654321 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
[root@130 ~]# mysqldump -uroot -p'654321' --all-databases > /opt/all-20220802.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@130 ~]# cd /opt/
[root@130 opt]# ls
all-20220802.sql data
[root@130 opt]# scp /opt/all-20220802.sql root@192.168.118.136:/opt/
The authenticity of host '192.168.118.136 (192.168.118.136)' can't be established.
ECDSA key fingerprint is SHA256:9C0X08PgcKZz+ejKcT6eOkg9eRMsoOQ2JiHYGqk7KZs.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.118.136' (ECDSA) to the list of known hosts.
root@192.168.118.136's password:
all-20220802.sql 100% 857KB 202.8MB/s 00:00
[root@130 opt]#
从库操作
[root@136 ~]# mysql -uroot -p123456 < /opt/all-20220802.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@136 ~]# mysql -uroot -p123456 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
配置主库
[root@130 ~]# vim /etc/my.cnf
[root@130 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
log-bin=mysql_bin //添加
server-id=10 //添加
gtid_mode=on //添加
enforce-gtid-consistency=true //添加
log-slave-updates=on //添加
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[root@130 ~]# systemctl restart mysqld.service
从库配置
[root@136 ~]# vim /etc/my.cnf
[root@136 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
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 //添加
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[root@136 ~]# systemctl restart mysqld.service
主库授权复制用户
mysql> grant replication slave on *.* to 'repl'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
从库设置要同步的主库信息，并开启同步。
mysql> change master to
-> master_host='192.168.118.130',
-> master_user='repl',
-> master_password='123456',
-> master_port=3306,
-> 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.118.130
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 437
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 650
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
4.测试验证
//查看主库
[root@130 ~]# mysql -uroot -p654321 -e'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
//查看从库
[root@136 ~]# mysql -uroot -p654321 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
//主库创建一个dxy数据库
mysql> create database dxy;
Query OK, 1 row affected (0.00 sec)
//查看是否同步
//查看主库
[root@130 ~]# mysql -uroot -p654321 -e'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| dxy |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
//查看从库
[root@136 ~]# mysql -uroot -p654321 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| dxy |
| mysql |
| performance_schema |
| runtime |
| sys |
+--------------------+
