目录
1.MySQL主从介绍
现代企业中,单点部署的数据库没有太好的容灾处理,如果服务器宕机容易产生数据的损坏与丢失;其次,单点部署的数据库服务器越来越满足不了服务的需求
MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份,实现数据库的拓展。多个数据备份不仅可以加强数据的安全性,通过实现读写分离还能进一步提升数据库的负载性能
2.MySQL主从复制原理
2.1 为什么要做主从复制
MySQL数据库的主从复制是一种常见的数据库复制技术,它有以下几个主要的优点和用途:
1. 提高数据库的可用性:通过主从复制,可以将读操作分摊到多个从数据库上,从而提高数据库的读取性能和可用性。当主数据库出现故障或者维护时,可以切换到从数据库继续提供服务,从而减少系统的停机时间。
2. 提高数据库的性能:通过主从复制,可以将写操作集中在主数据库上,而读操作可以分散到多个从数据库上,从而提高数据库的整体性能。主数据库负责处理写操作,从数据库负责处理读操作,减少了对主数据库的读写竞争,提高了系统的并发能力。
3. 实现数据备份和灾难恢复:通过主从复制,可以将主数据库的数据实时复制到从数据库中,从而实现数据的备份和灾难恢复。当主数据库发生故障或者数据损坏时,可以快速切换到从数据库进行数据恢复,保证数据的安全性和可靠性。
4. 实现数据分析和报表生成:通过主从复制,可以将主数据库的数据复制到从数据库中,然后在从数据库上进行数据分析和报表生成。这样可以避免对主数据库的读操作影响到写操作,提高了系统的性能和稳定性。
2.2 MySQL主从复制的流程
- 主库db的更新事件(update、insert、delete)被写到binlog
- 主库创建一个binlog dump thread,把binlog的内容发送到从库
- 从库启动并发起连接,连接到主库
- 从库启动之后,创建一个I/O线程,读取主库传过来的binlog内容并写入到relay log
- 从库启动之后,创建一个SQL线程,从relay log里面读取内容,从Exec_Master_Log_Pos位置开始执行读取到的更新事件,将更新内容写入到slave的db
2.3 主从形式
- 一主一从
- 主主复制
- 一主多从---扩展系统读取的性能,因为读是在从库读取的
- 多主一从---5.7开始支持
- 联级复制
3.主从复制配置
3.1 准备工作
1.首先准备2台实验机,我的配置如下
master centos8 192.168.176.131
slave centos8 192.168.187.133
2.分别在2台主机上安装mariadb
[root@master ~]# yum -y install mariadb*
[root@slave ~]# yum -y install mariadb*
3.安装完成后启动服务,关闭防火墙和selinux,不需要密码直接登录,然后设置密码
(slave主机上操作一致)
[root@master ~]# systemctl start mariadb
[root@master ~]# 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 0.0.0.0:3306 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
[root@master ~]# systemctl disable --now firewalld.service
[root@master ~]# setenforce 0
[root@master ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.28-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> set password = password('2664218545Z');
Query OK, 0 rows affected (0.000 sec)
3.2 查看数据库内容
# 查看这时2台主机的库一致
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.001 sec)
3.3 在主数据库里创建一个同步账号授权给从数据库使用
# 授权repl用户在192.168.187.133主机上访问所有数据库
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'192.168.187.133' identified by '123456789';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
3.4 配置主数据库
1.配置文件,在后面加上2行
[root@master ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
log-bin=mysql_bin //启用binlog日志
server-id=10 //数据库服务器唯一标识符,主库的server-id值一般比从库的小
2.重启mariadb服务
[root@master ~]# systemctl restart mariadb
[root@master ~]# 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 0.0.0.0:3306 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
3.查看主库的状态
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql_bin.000002 | 328 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)
3.5 配置从数据库
1.# 配置文件,加上如下2行
[root@slave ~]# vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/run/mariadb/mariadb.pid
server-id=20 //设置从库的唯一标识符,从库的server-id值一般大于主库的该值
relay-log=mysql-relay-bin //启用中继日志relay-log
2.# 配置并开启主从复制
MariaDB [(none)]> change master to
-> master_host='192.168.187.131', # 配置上前面授权的用户信息
-> master_user='repl',
-> master_password='123456789',
-> master_log_file=' mysql_bin.000002',
-> master_log_pos=328;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> start slave; # 启动从库
Query OK, 0 rows affected (0.000 sec)
3.# 查看从服务器状态
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.187.131
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000002
Read_Master_Log_Pos: 328
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 555
Relay_Master_Log_File: mysql_bin.000002
Slave_IO_Running: Yes # 这里必须为yes
Slave_SQL_Running: Yes # 这里必须为yes
3.6 验证测试
1.# 在主数据库中创建一个库,在其中创建一个表,并为其插入数据
# 然后在从数据库这边查看是否同步
MariaDB [(none)]> create database zmq;
Query OK, 1 row affected (0.000 sec)
MariaDB [(none)]> use zmq;
Database changed
MariaDB [zmq]> create table student(id int primary key auto_increment,name varchar(16) not null)charset utf8;
Query OK, 0 rows affected (0.003 sec)
MariaDB [zmq]> insert into student(name) value('龙星佑');
Query OK, 1 row affected (0.001 sec)
MariaDB [zmq]> select * from student;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 龙星佑 |
+----+-----------+
1 row in set (0.000 sec)
2.# 在从数据库这边查看是否同步
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| zmq |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> use zmq;
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
MariaDB [zmq]> show tables;
+---------------+
| Tables_in_zmq |
+---------------+
| student |
+---------------+
1 row in set (0.000 sec)
MariaDB [zmq]> select * from student;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 龙星佑 |
+----+-----------+
1 row in set (0.000 sec)
#### 同步成功
4.GTID主从
4.1 GTID概念介绍
GTID(Global Transaction Identifier)是MySQL 5.6版本及更高版本中引入的一种全局事务标识符。它用于唯一标识每个事务,并且在整个复制拓扑中保持全局唯一性。
GTID由两个部分组成:源ID(Source ID)和事务ID(Transaction ID)。源ID标识MySQL实例,事务ID标识特定事务。
使用GTID可以简化MySQL复制拓扑的管理和配置。在传统的基于二进制日志的复制中,需要手动配置每个从属实例的二进制日志文件和位置。而使用GTID,只需要在主服务器上启用GTID,并在从属服务器上配置正确的主服务器ID,就可以自动追踪和复制所有的事务。
GTID还可以解决复制中的一些常见问题,例如主服务器和从属服务器之间的数据不一致、从属服务器的延迟问题等。当从属服务器与主服务器断开连接后重新连接,GTID可以确保从属服务器能够自动从断开连接的位置继续复制,而不会错过任何事务。
总之,GTID是MySQL中用于全局事务标识和复制管理的一种机制,它提供了更简单、更可靠的复制配置和管理方式。下面是一个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
4.2 GTID工作原理
GTID的工作原理如下:
主服务器生成GTID:当主服务器开始一个新的事务时,它会生成一个全局唯一的GTID,并将其分配给该事务。
GTID传播到从属服务器:主服务器将生成的GTID包含在二进制日志中,并将其传播到所有的从属服务器。从属服务器在接收到主服务器的GTID后,将其记录在自己的GTID执行状态表中。
从属服务器复制事务:从属服务器会根据主服务器传播的GTID信息,自动复制主服务器上的事务。它会根据自己的GTID执行状态表中的记录,判断是否已经复制过该事务,以避免重复复制。
GTID同步:当从属服务器完成一个事务的复制后,它会将已经复制的GTID信息反馈给主服务器。主服务器会根据从属服务器的反馈,更新自己的GTID执行状态表,以确保所有的从属服务器都已经复制了该事务。
断开连接和重连:如果从属服务器与主服务器断开连接后重新连接,它会通过比较自己的GTID执行状态表和主服务器的GTID执行状态表,确定断开连接的位置,并从该位置继续复制事务,以避免数据丢失。
通过GTID,主服务器和从属服务器之间的事务复制变得更加简单和可靠。它提供了全局唯一的事务标识,避免了复制中的数据不一致和丢失问题,同时也简化了复制拓扑的管理和配置。
4.3 GTID主从配置
# 准备工作:
这里准备了2台实验虚拟机,配置如下:
master centos8 192.168.187.131 # 作为主库机
slave redhat8 192.168.187.133 # 作为从库机
1.# 分别在2台主机上安装mariadb 并启动服务
[root@master ~]# yum -y install mariadb*
[root@master ~]# systemctl start mariadb
root@master ~]# 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 0.0.0.0:3306 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
# 3306端口已启用
2.配置主库并重启mariadb服务(配置文件是按照mysql5.7以后的版本来写的,以前版本的参数会有变化)
[root@slave ~]# vim /etc/my.cnf.d/mariadb-server.cnf
在[mysqld]的后面加上这几行
log-bin=mysql_bin
server-id=10
gtid_strict_mode=on
log-slave-updates=on
[root@master ~]# systemctl restart mariadbf
[root@master ~]# 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 0.0.0.0:3306 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
3.配置从库并重启mariadb服务(配置文件是按照mysql5.7以后的版本来写的,以前版本的参数会有变化)
[root@slave ~]# vim /etc/my.cnf.d/mariadb-server.cnf
在[mysqld]后面加上这几行
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
[root@slave ~]# systemctl restart mariadb
[root@slave ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 80 0.0.0.0:3306 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
4.主库授权复制用户
MariaDB [(none)]> grant replication slave on *.* to 'repl'@'%' identified by 'repl123!';
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
5.从库设置要同步的主库信息,并开启同步
MariaDB [(none)]> change master to master_host='192.168.187.131',
-> master_port=3306,
-> master_user='repl',
-> master_password='repl123!',
-> master_use_gtid=slave_pos;
Query OK, 0 rows affected (0.003 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
6.查看状态,出现双yes即为成功
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.187.131
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql_bin.000001
Read_Master_Log_Pos: 639
Relay_Log_File: myrelay.000002
Relay_Log_Pos: 938
Relay_Master_Log_File: mysql_bin.000001
Slave_IO_Running: Yes # 这里必须为yes
Slave_SQL_Running: Yes # 这里必须为yes
7.测试实验
# 在主库中创建库和表,看从库中是否同步
MariaDB [(none)]> create database zmq;
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> use zmq;
Database changed
MariaDB [zmq]> create table student(id int primary key auto_increment,name varchar(16));
Query OK, 0 rows affected (0.004 sec)
MariaDB [zmq]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| zmq |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [zmq]> show tables;
+---------------+
| Tables_in_zmq |
+---------------+
| student |
+---------------+
1 row in set (0.000 sec)
# 在从库中查看
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| zmq |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> use zmq;
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
MariaDB [zmq]> show tables;
+---------------+
| Tables_in_zmq |
+---------------+
| student |
+---------------+
1 row in set (0.000 sec)
# 实验完成