mysql主从
目录
1. 主从简介
在现代企业中不管什么数据库都存在这二个问题：
- 用一台数据库存放数据，此数据库服务器宕机了导致数据丢失怎么办？
- 业务量大了，数据多了，访问的人多了，一台数据库无法保证服务质量了怎么办？
1.1 主从作用
- 实时灾备，用于故障切换
- 读写分离，提供查询服务
- 备份，避免影响业务
1.2 主从形式
- 一主一从
- 主主复制
- 一主多从---扩展系统读取的性能，因为读是在从库读取的
- 多主一从---5.7开始支持
- 联级复制
2. 主从复制原理
主从复制步骤：
- 主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程
- 从库生成两个线程，一个I/O线程，一个SQL线程
- I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中
- SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的
3. 主从复制配置
主从复制配置步骤：
- 确保从数据库与主数据库里的数据一样
- 在主数据库里创建一个同步账号授权给从数据库使用
- 配置主数据库（修改配置文件）
- 配置从数据库（修改配置文件）
需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
3.1 mysql安装
//配置mysql的yum源
[root@z1 ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
[root@z1 ~]# rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
//禁用mysql
[root@z1 ~]# yum module disable mysql
//安装mysql5.7
[root@z1 ~]# yum -y install mysql-community-server mysql-community-client mysql-community-common mysql-community-devel --nogpgcheck
//启动mysql并设置开机自动启动
systemctl enable --now mysqld
systemctl status mysqld
//查看3306端口已经监听起来
[root@z1 ~]# ss -antl
LISTEN 0 80 *:3306 *:*
3.2 mysql主从配置
3.2.1 确保从数据库与主数据库里的数据一样
为确保从数据库与主数据库里的数据一样，先全备主数据库并还原到从数据库中
//先查看主库有哪些库
[root@z1 ~]# 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 |
| sys |
| teacher |
+--------------------+
//再查看从库有哪些库
[root@z2 ~]# 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 |
| sys |
+--------------------+
//全备主库
//全备主库时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
//此锁表的终端必须在备份完成以后才能退出
//备份主库并将备份文件传送到从库
[root@z1 ~]# mysqldump -uroot -p123456 --all-databases > /opt/all-2022080120.58.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@z1 ~]# ls /opt/
all-2022080120.58.sql
[root@z1 ~]# scp /opt/all-2022080120.58.sql root@192.168.26.132:/opt/
oot@192.168.26.132's password:
all-2022080120.58.sql 100% 859KB 54.9MB/s 00:00
//解除主库的锁表状态，直接退出交互式界面即可
mysql> quit
Bye
//在从库上恢复主库的备份并查看从库有哪些库，确保与主库一致
[root@z2 ~]# mysql -uroot -p123456 < /opt/all-2022080120.58.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@z2 ~]# 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 |
| sys |
| teacher |
+--------------------+
3.2.2 在主数据库里创建一个同步账号授权给从数据库使用
mysql> create user 'repl'@192.168.26.132 identified by 'repl123!';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'repl'@192.168.26.132;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
3.2.3 配置主数据库
[root@z1 ~]# vi /etc/my.cnf
//在[mysqld]这段的后面加上如下内容
log-bin=mysql-bin
server-id=10
//重启mysql服务
[root@z1 ~]# systemctl restart mysqld
[root@z1 ~]# 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 *:80 *:*
LISTEN 0 128 [::]:22 [::]:*
//查看主库的状态
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)
3.2.4 配置从数据库
[root@z2 ~]# vi /etc/my.cnf
//添加如下内容
server-id=20
relay-log=mysql-relay-bin
//重启从库的mysql服务
[root@z2 ~]# systemctl restart mysqld
[root@z2 ~]# 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.26.138',
-> master_user='repl',
-> master_password='repl123!',
-> 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.26.138
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: mysql-relay-bin.000006
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.2.5 测试验证
在主服务器上创建z数据库
mysql> create database z;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| teacher |
| z |
+--------------------+
6 rows in set (0.00 sec)
在从服务器中查看数据是否同步：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| teacher |
| z |
+--------------------+
6 rows in set (0.00 sec)
4. GTID主从
4.1 GTID概念介绍
MySQL 5.6 的新特性之一，全局事务标识符（GTID）是创建的唯一标识符，并与在源（主）服务器上提交的每个事务相关联。此标识符不但是唯一的，而且在给定复制设置中的所有服务器上都是唯一的。所有交易和所有GTID之间都有一对一的映射关系 。它由服务器ID以及事务ID组合而成。这个全局事务ID不仅仅在原始服务器上唯一，在所有存在主从关系 的mysql服务器上也是唯一的。正是因为这样一个特性使得mysql的主从复制变得更加简单，以及数据库一致性更可靠。一个GTID在一个服务器上只执行一次，避免重复执行导致数据混乱或者主从不一致。
4.2 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、在解析过程中会判断是否有主键，如果有就用二级索引，如果没有就用全部扫描。
4.3 GTID主从配置
主库配置。vi /etc/my.cnf，添加以下配置，重启mysql
[root@z1 ~]# vi /etc/my.cnf
log-bin=mysql_bin
server-id=10
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on
[root@z1 ~]# systemctl restart mysqld
从库配置。vi /etc/my.cnf, 添加以下配置，重启mysql
[root@z1k ~]# vi /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
[root@z1k ~]# systemctl restart mysqld
关闭防火墙
[root@z1 ~]# systemctl stop firewalld
[root@z1 ~]# systemctl disable firewalld
[root@z1 ~]# vi /etc/selinux/config
SELINUX=disabled
[root@z1 ~]# setenforce 0
主库授权复制用户
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=1;
mysql> grant replication slave on *.* to 'repl'@'%' identified by 'repl123!';
从库设置要同步的主库信息，并开启同步
mysql> change master to master_host='192.168.26.138',
-> 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.02 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.26.138
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
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· Docker 太简单，K8s 太复杂？w7panel 让容器管理更轻松！