MySQL
1.主从作用
- 实时灾备，用于故障切换
- 读写分离，提供查询服务
- 备份，避免影响业务
主从形式
- 一主一从
- 主主复制
- 一主多从—扩展系统读取的性能，因为读是在从库读取的
- 多主一从—5.7开始支持
- 联级复制
2.主从复制原理
MySQL主从复制是一种数据复制技术，它允许将一个MySQL数据库服务器（主服务器）上的数据复制到一个或多个其他MySQL服务器（从服务器）。主从复制的原理如下：
- 主服务器将更新操作记录在二进制日志（binary log）中。这包括插入、更新和删除操作。
- 从服务器连接到主服务器，并请求复制主服务器上的数据。从服务器发送一个复制请求，包括它希望复制的日志文件名和位置。
- 主服务器接收到复制请求后，将请求的二进制日志文件发送给从服务器。
- 从服务器接收到二进制日志文件后，将其应用到自己的数据库中。从服务器会记录已经复制的日志位置，以便下次请求时可以继续复制。
- 主服务器继续记录更新操作到二进制日志中，并将新的日志文件发送给从服务器。
- 从服务器不断地从主服务器请求和应用新的二进制日志文件，实现数据的持续复制。
通过主从复制，可以实现数据的备份、负载均衡和故障恢复等功能。当主服务器发生故障时，可以快速切换到从服务器，保证系统的可用性。同时，可以通过多个从服务器实现读写分离，提高系统的性能。
3.主从复制配置
主从复制配置步骤：
- 确保从数据库与主数据库里的数据一样
- 在主数据库里创建一个同步账号授权给从数据库使用
- 配置主数据库（修改配置文件）
- 配置从数据库（修改配置文件）
需求：
搭建两台MySQL服务器，一台作为主服务器，一台作为从服务器，主服务器进行写操作，从服务器进行读操作
MySQL安装
两台主机上安装mysql5.7版本,我们这里跳过安装步骤，详情请参考MySQL基础
mysql主从配置
确保从数据与主数据相同
//先查看主库有哪些库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| edu |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
6 rows in set (0.00 sec)
//再看看从库有哪些库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
将主库进行一个全量备份
全量备份时需要另开一个终端，给数据库加上读锁，避免在备份期间有其他人在写入导致数据不一致
锁表操作必须在从库同步完成之后才能退出
[root@localhost ~]# mysqldump -uroot -p --all-databases > /root/all-20230907.sql
Enter password:
[root@localhost ~]# ls
all-20230907.sql anaconda-ks.cfg
[root@localhost ~]# scp /root/all-20230907.sql root@192.168.37.120:/root/
The authenticity of host '192.168.37.120 (192.168.37.120)' can't be established.
ECDSA key fingerprint is SHA256:p4ZvKSb+74uFq5V2Oxj6Kpa9D8sc3x7S1HVBvkBDtu8.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.37.120' (ECDSA) to the list of known hosts.
root@192.168.37.120's password:
all-20230907.sql 100% 859KB 73.0MB/s 00:00
从库进行数据恢复操作，确保数据与主库相同
[root@localhost ~]# mysql -uroot -p < all-20230907.sql
Enter password:
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.39 MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| edu |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
6 rows in set (0.00 sec)
在主数据库里创建一个同步账号授权给从数据库使用
mysql> create user 'repl'@'192.168.37.120' identified by 'a123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to 'repl'@'192.168.37.120';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
配置主数据库
[root@localhost ~]# vi /etc/my.cnf
//添加以下内容
log-bin=mysql-bin
server-id=10
//重启mysql服务
[root@localhost ~]# systemctl restart mysql
[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)
配置从数据库
[root@localhost ~]# vi /etc/my.cnf
//添加以下内容
server-id=20
relay-log=mysql-relay-bin
//重启服务
[root@localhost ~]# systemctl restart mysql
[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 128 [::]:22 [::]:*
LISTEN 0 80 *:3306 *:*
//配置并启动主从复制
mysql> change master to
-> master_host='192.168.37.100',
-> master_user='repl',
-> master_password='a123456',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=931;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| edu |
| mysql |
| performance_schema |
| student |
| sys |
+--------------------+
6 rows in set (0.00 sec)
//查看从服务器状态
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 931
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes //必须为YES
Slave_SQL_Running: Yes //必须为YES
验证测试
在主服务器创建newkl库
mysql> create database newkl;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| edu |
| mysql |
| newkl |
| performance_schema |
| student |
| sys |
+--------------------+
7 rows in set (0.00 sec)
查看从库是否更新
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| edu |
| mysql |
| newkl |
| performance_schema |
| student |
| sys |
+--------------------+
7 rows in set (0.00 sec)
4.GTID主从
GTID(全局事务标识) 是MySQL复制中的一种标识方式，用于确保主从复制的数据一致性和可靠性。
在传统的基于二进制日志的主从复制中，主库将其写入的每个事务记录为二进制日志事件，并将这些事件传输给从库进行重放。从库通过解析二进制日志来重建主库上的事务。然而，这种方式存在一些问题，比如在主库上执行DDL语句时，从库可能无法正确重放DDL操作。
GTID解决了这个问题。它是一个全局唯一的标识符，用于标识主库上的每个事务。每个事务都有一个唯一的GTID，不受具体的数据库服务器或实例的影响。当主库执行一个事务时，它会生成一个新的GTID，并将该GTID包含在传输给从库的二进制日志事件中。从库根据接收到的GTID来确定是否需要执行相应的事务。
GTID的主要优点是：
- 简化主从切换：在基于GTID的复制中，从库只需要记录已经应用的GTID，而不需要记录已经执行的二进制日志位置。这样，在主从切换时，从库可以快速确定需要同步的位置。
- 更可靠的复制：由于GTID是全局唯一的，可以确保主从复制的数据一致性。即使在主库上执行DDL语句，从库也可以正确地重放。
使用GTID需要满足以下条件：
- 主库和从库都必须启用GTID模式。
- 所有的写操作都必须在主库上执行，并且从库只能作为只读副本。
GTID主从复制提供了更可靠和方便的复制机制，可以简化主从切换和故障恢复过程，并提高复制的可靠性。
工作原理
GTID（全局事务标识）是MySQL复制中的一种标识方式，用于确保主从复制的数据一致性和可靠性。GTID的工作原理如下：
- 主库生成GTID：当主库上执行一个事务时，它会生成一个新的GTID，并将该GTID包含在传输给从库的二进制日志事件中。GTID由两个部分组成：源ID和事务ID。源ID是标识主库的唯一标识符，事务ID是主库上生成的每个事务的唯一标识符。
- 从库接收GTID：从库通过复制线程从主库获取二进制日志事件，并解析这些事件。当从库接收到一个包含GTID的事件时，它会记录已经应用的GTID，并将该GTID存储在从库的GTID集合中。
- 从库执行事务：从库根据GTID集合来确定是否需要执行相应的事务。如果一个GTID在从库的GTID集合中不存在，那么从库就会执行相应的事务。这样，从库可以确保与主库的数据一致性。
- GTID集合同步：当从库执行一个事务时，它会将已经应用的GTID添加到自己的GTID集合中。从库定期将自己的GTID集合信息发送给主库，主库会将从库的GTID集合信息记录在二进制日志中。这样，主库可以了解从库的复制进度，并在主从切换时提供准确的位置信息。
简而言之，GTID的工作流程为：
- master更新数据时，会在事务前产生GTID，一同记录到binlog日志中。
- slave端的i/o 线程将变更的binlog，写入到本地的relay log中。
- sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。
- 如果有记录，说明该GTID的事务已经执行，slave会忽略。
- 如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。
- 在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。
GTID主从配置
注意，此处配置只适合mysql5.6-5.7版本，不适用于mariadb的新版本
mysql版本
主库配置。vi /etc/my.cnf，添加以下配置，重启mysql。
log-bin=mysql_bin
server-id=10
gtid_mode=on
enforce-gtid-consistency=true
log-slave-updates=on
从库配置。vi /etc/my.cnf, 添加以下配置，重启mysql。
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
maridb版本:
主库配置
log-bin=mysql_bin
server-id=10
gtid_strict_mode=on
log-slave-updates=on
从库配置
server-id=20
relay-log=myrelay
gtid_strict_mode=on
log-slave-updates=on
read_only=on
master-info-repository=TABLE
relay-log-info-repository=TABLE
进行数据同步
以下演示的是mysql5.6-5.7版本的
主库授权复制用户
grant replication slave on *.* to 'repl'@'%' identified by 'a123456';
从库设置要同步的主库信息，并开启同步。
change master to master_host='主库IP', \
master_port=3306,master_user='repl',master_password='a123456', \
master_auto_position=1;
start slave;
show slave status\G
mariadb版本
主库授权复制用户
grant replication slave on *.* to 'repl'@'%' identified by 'a123456';
从库设置要同步的主库信息，并开启同步。
change master to master_host='主库IP', \
master_port=3306,master_user='repl',master_password='a123456', \
master_use_gitd=slave_pos;
start slave;
show slave status\G
显示两个YES就代表成功了
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 1307
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 1520
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
扫一扫
3328
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
