MySQL主从和GTID主从
1.MySQL主从和GTID主从相关介绍
在现代企业中，数据显得尤为重要，可以说是一个公司的命脉，能决定公司的存亡，而存储数据的数据库选择又五花八门，但无论是何种数据库，均存在着一种隐患。
用一台数据库存放数据，若此数据库服务器宕机了导致数据丢失怎么办？业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量了怎么办？
因而主从复制完美的解决了这些隐患，它能够实时灾备，用于故障切换；读写分离，提供查询服务；备份，避免影响业务。
主从又分为GTID主从和传统主从（MySQL主从）。
两者的区别
- 传统主从
普通主从复制主要是基于二进制日志文件位置的复制，因此主必须启动二进制日志记录并建立唯一的服务器ID，复制组中的每个服务器都必须配置唯一的服务器ID。如果您省略server-id（或者明确地将其设置为其默认值0），则主设备将拒绝来自从设备的任何连接。 - GTID主从
MySQL 5.6 的新特性之一，全局事务标识符（GTID）是创建的唯一标识符，并与在源（主）服务器上提交的每个事务相关联。此标识符不但是唯一的，而且在给定复制设置中的所有服务器上都是唯一的。所有交易和所有GTID之间都有一对一的映射关系 。它由服务器ID以及事务ID组合而成。这个全局事务ID不仅仅在原始服务器上唯一，在所有存在主从关系 的mysql服务器上也是唯一的。正是因为这样一个特性使得mysql的主从复制变得更加简单，以及数据库一致性更可靠。一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。
2.主从形式
- 一主一从
- 主主复制
- 一主多从—扩展系统读取的性能，因为读是在从库读取的
- 多主一从—5.7开始支持
- 联级复制
3.MySQL主从复制原理
MySQL主从复制步骤：
-
主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程
-
从库生成两个线程，一个I/O线程，一个SQL线程
I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中
SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的
4.MySQL主从复制配置
主从复制配置步骤：
1.确保从数据库与主数据库里的数据一样
2.在主数据库里创建一个同步账号授权给从数据库使用
3.配置主数据库（修改配置文件）
4.配置从数据库（修改配置文件）
5.MySQL主从复制应用实例
需求：（一主多从）
搭建三台MySQL服务器，一台作为主服务器，两台作为从服务器，主服务器进行写操作，从服务器进行读操作
环境说明：
| 数据库角色 | IP | 应用与系统版本 | 有无数据 |
|---|---|---|---|
| 主数据库 | 192.168.249.141 | centos8/redhat7 mysql-5.7 | 有 |
| 从数据库 | 192.168.249.142 | centos8/redhat7 mysql-5.7 | 无 |
| 从数据库 | 192.168.249.143 | centos8/redhat7 mysql-5.7 | 无 |
//关闭所有环境的防火墙和selinux
systemctl stop firewalld
setenforce 0
//或者直接设置永久关闭
systemctl disable firewalld
vim /etc/selinux/config
SELINUX=disabled
5.1.确保从数据库与主数据库里的数据一样
为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中
//查看主库有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
5 rows in set (0.00 sec)
//查看从库1有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.07 sec)
//查看从库2有哪些数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.07 sec)
//全量备份主数据库
//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致，这个操作很重要
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
//此锁表的终端必须在备份完成以后才能退出
//备份主库并将备份文件传送到从库
[root@localhost ~]# mysqldump -uroot -plixirong123! --all-databases > /opt/all-66.sql;
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls /opt
all-66.sql data
[root@localhost ~]# scp /opt/all-66.sql root@192.168.249.142:/opt/
root@192.168.249.142's password:
all-66.sql
[root@localhost ~]# scp /opt/all-66.sql root@192.168.249.143:/opt/
The authenticity of host '192.168.249.143 (192.168.249.143)' can't be established.
ECDSA key fingerprint is SHA256:8Hq+7uQceGjFiz7+iKM5876lzbdYzBH/KsNgKuD9+aQ.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.249.143' (ECDSA) to the list of known hosts.
root@192.168.249.143's password:
all-66.sql 100% 853KB 10.1MB/s 00:00
//解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye
//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@localhost ~]# mysql -uroot -plixirong123! < /opt/all-66.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -plixirong123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
//另外一台同上，一模一样，这里不在展示
5.2在主数据库里创建一个同步账号授权给从数据库使用
mysql> create user 'repl'@192.168.249.142 identified by 'repl123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.249.142';
Query OK, 0 rows affected (0.00 sec)
mysql> create user 'repl'@192.168.249.143 identified by 'repl123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.249.143';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges; //刷新权限
Query OK, 0 rows affected (0.00 sec)
5.3配置主数据库
//在[mysqld]这段的后面加上如下内容
[root@localhost ~]# vim /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 //数据库服务器唯一标识符，主库的server-id值必须比从库的小
//重启mysql服务
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# 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 [::]:*
//查看主库的状态
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)
5.4配置从数据库
[root@localhost ~]# vim /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 //设置从库的唯一标识符，从库的server-id值必须大于主库的该值
relay-log = mysql_relay_bin /启用中继日志relay-log
//重启服务
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# 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@localhost ~]# vim /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 = 30
relay-log = mysql_relay_bin
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# 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 [::]:*
//在从数据库配置并启动主从复制
mysql> change master to
-> master_host='192.168.249.141',
-> master_user='repl',
-> master_password='repl123',
-> master_log_file='mysql_bin.000002',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
//另外一台一模一样
mysql> change master to
-> master_host='192.168.249.141',
-> master_user='repl',
-> master_password='repl123',
-> master_log_file='mysql_bin.000002',
-> master_log_pos=154;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
//查看从服务器状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.249.141
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: mysql_relay_bin.000001
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql_bin.000002
Slave_IO_Running: Yes //此处必须是yes
Slave_SQL_Running: Yes //此处必须是yes
Replicate_Do_DB:
Replicate_Ignore_DB:
5.5测试验证
//在主数据库插入数据
mysql> create database lixirong;
Query OK, 1 row affected (0.04 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lixirong |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
6 rows in set (0.00 sec
//在从数据库查看数据是否同步
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lixirong |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
6 rows in set (0.00 sec
//另外一个
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| lixirong |
| mysql |
| performance_schema |
| school |
| sys |
+--------------------+
6 rows in set (0.00 sec
6.GTID主从复制原理
1.当一个事务在主库端执行并提交时，产生GTID，并记录到bin-log日志中。
2.从库通过I/O线程来请求主库，bin-log传输到从库，并存储到从库的relay-log，然后读取这个GTID的值设置gtid_next变量，也就是告诉从库，下一个要执行的GTID值。
3.sql线程从relay-log日志中获取GTID，然后对比从库端的bin-log日志是否有该GTID。
4.如果有记录，说明该GTID事务已经执行，从库会直接忽略。
5.如果没有记录，从库就会执行该GTID事务，并记录该GTID到自身的bin-log日志。在读取执行事务前会先检查其它session是否持有该GTID，确保不被重复执行。
6.在解析过程中会判断是否有主键，如果有就用二级索引，如果没有就用全部扫描。
7.GTID主从复制应用实例
环境说明：
| 数据库角色 | IP | 应用与系统版本 | 有无数据 |
|---|---|---|---|
| 主数据库 | 192.168.249.141 | centos8/redhat7 mysql-5.7 | 无 |
| 从数据库 | 192.168.249.145 | centos8/redhat7 mysql-5.7 | 无 |
//关闭所有环境的防火墙和selinux
systemctl stop firewalld
setenforce 0
//或者直接设置永久关闭
systemctl disable firewalld
vim /etc/selinux/config
SELINUX=disabled
7.1确保数据一致性
//主数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
//从数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
//如果主从数据库的数据不一样，首先需要全量备份主数据库的数据，然后恢复到从数据库，和上面传统主从一样，可以往上看
7.2在主数据创建一个同步账号授权给从数据库使用
//创建
mysql> create user 'lxr'@'192.168.249.145' identified by 'lxr123';
Query OK, 0 rows affected (0.00 sec)
//授权
mysql> grant replication slave on *.* to 'lxr'@'192.168.249.145';
Query OK, 0 rows affected (0.00 sec)
//刷新
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
7.3配置主数据库
[root@localhost ~]# vim /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 = 1 //数据库服务器唯一标识符，主服务器的id必须比从服务器小
gtid-mode = on //开启gtid模式
enforce-gtid-consistency = on //强制gtid一致性，用于保证启动gtid后事务的安全
log-bin = mysql_bin //开启bin-log日志
log-slave-updates = 1 //mysql5.7以前使用gtid模式，必须要加这个
binlog-format = row //日志格式‘行’
skip-slave-start = 1
//重启mysql服务
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# 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 [::]:*
7.4配置从数据库
[root@localhost ~]# vim /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 = 2 //必须保证比主服务器大
gtid-mode = on
enforce-gtid-consistency = on
log-bin = mysql_slave_bin //这里同样也为log-bin
log-slave-updates = 1
binlog-format = row
skip-slave-start = 1
//重启服务
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# 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 [::]:*
//在从数据库配置并启动主从复制
mysql> change master to
-> master_host='192.168.249.141',
-> master_user='lxr',
-> master_password='lxr123',
-> master_port=3306,
-> master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.02 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.249.141
Master_User: lxr
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000002
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql_bin.000002
Slave_IO_Running: Yes //此处必须是yes
Slave_SQL_Running: Yes //此处必须是yes
Replicate_Do_DB:
Replicate_Ignore_DB:
7.5测试验证
//主库插入数据
mysql> create database haha;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| haha |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql>
//从库查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| haha |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
792
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
