MySQL主从

已于 2023-11-13 18:05:38 修改
阅读量190 收藏
点赞数
文章标签： mysql 数据库
于 2023-09-07 15:35:53 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_63150485/article/details/132736820
版权

目录

1.MySQL主从介绍

2.MySQL主从复制原理

2.1 为什么要做主从复制

 2.2 MySQL主从复制的流程

2.3 主从形式

3.主从复制配置

3.1 准备工作

3.2 查看数据库内容

3.3 在主数据库里创建一个同步账号授权给从数据库使用

3.4 配置主数据库

3.5 配置从数据库

3.6 验证测试

4.GTID主从

4.1 GTID概念介绍

4.2 GTID工作原理 

4.3 GTID主从配置

1.MySQL主从介绍

现代企业中，单点部署的数据库没有太好的容灾处理，如果服务器宕机容易产生数据的损坏与丢失；其次，单点部署的数据库服务器越来越满足不了服务的需求

MySQL数据库自身提供的主从复制功能可以方便的实现数据的多处自动备份，实现数据库的拓展。多个数据备份不仅可以加强数据的安全性，通过实现读写分离还能进一步提升数据库的负载性能

2.MySQL主从复制原理

2.1 为什么要做主从复制

MySQL数据库的主从复制是一种常见的数据库复制技术，它有以下几个主要的优点和用途：

1. 提高数据库的可用性：通过主从复制，可以将读操作分摊到多个从数据库上，从而提高数据库的读取性能和可用性。当主数据库出现故障或者维护时，可以切换到从数据库继续提供服务，从而减少系统的停机时间。

2. 提高数据库的性能：通过主从复制，可以将写操作集中在主数据库上，而读操作可以分散到多个从数据库上，从而提高数据库的整体性能。主数据库负责处理写操作，从数据库负责处理读操作，减少了对主数据库的读写竞争，提高了系统的并发能力。

3. 实现数据备份和灾难恢复：通过主从复制，可以将主数据库的数据实时复制到从数据库中，从而实现数据的备份和灾难恢复。当主数据库发生故障或者数据损坏时，可以快速切换到从数据库进行数据恢复，保证数据的安全性和可靠性。

4. 实现数据分析和报表生成：通过主从复制，可以将主数据库的数据复制到从数据库中，然后在从数据库上进行数据分析和报表生成。这样可以避免对主数据库的读操作影响到写操作，提高了系统的性能和稳定性。

 2.2 MySQL主从复制的流程

  

  1. 主库db的更新事件(update、insert、delete)被写到binlog
  2. 主库创建一个binlog dump thread，把binlog的内容发送到从库
  3. 从库启动并发起连接，连接到主库
  4. 从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
  5. 从库启动之后，创建一个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的工作原理如下：

  1. 主服务器生成GTID：当主服务器开始一个新的事务时，它会生成一个全局唯一的GTID，并将其分配给该事务。

  2. GTID传播到从属服务器：主服务器将生成的GTID包含在二进制日志中，并将其传播到所有的从属服务器。从属服务器在接收到主服务器的GTID后，将其记录在自己的GTID执行状态表中。

  3. 从属服务器复制事务：从属服务器会根据主服务器传播的GTID信息，自动复制主服务器上的事务。它会根据自己的GTID执行状态表中的记录，判断是否已经复制过该事务，以避免重复复制。

  4. GTID同步：当从属服务器完成一个事务的复制后，它会将已经复制的GTID信息反馈给主服务器。主服务器会根据从属服务器的反馈，更新自己的GTID执行状态表，以确保所有的从属服务器都已经复制了该事务。

  5. 断开连接和重连：如果从属服务器与主服务器断开连接后重新连接，它会通过比较自己的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)

# 实验完成

迎安
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
mysql主从
h1228322088的博客
05-11 3万+
主从介绍 什么是mysql主从 所谓mysql主从就是建立两个完全一样的数据库，其中一个为主要使用的数据库，另一个为次要的数据库，一般在企业中，存放比较重要的数据的数据库服务器需要配置主从，这样可以防止因数据库服务器宕机导致数据丢失，还能保证业务量太多、数据太多和访问人数太多时服务的质量（服务器响应速度），还能提供故障切换、读写分离、和备份等等功能。 主从形式 查看各个服务器中的mysql实例中数据是否一致 主： [root@localhost ~]# mysql -uroot -phanao. mysql
MySql主从
m0_37685866的博客
03-25 827
mysql主从同步原理，同步策略，延迟原因，延迟优化方案等
mysql主从搭建
qq_37940440的博客
01-17 881
mysql 主从配置
《深入理解MySQL主从原理32讲》推荐篇
01-19
作者：高鹏（网名八怪），《深入理解MySQL主从原理32讲》系列文的作者。2008年开始至今一直从事Oracle/MySQL相关工作，现任易极付高级DBA，Oracle Ocm。有丰富的数据库性能调优，故障诊断经验，并且对MySQL源码有...
docker-compose关于mysql主从复制一键搭建脚本
09-18
关于博客: https://blog.csdn.net/qq_42413011/article/details/126914080 的搭建脚本
Mysql主从同步备份策略分享
09-11
MySQL 主从同步备份策略分享 MySQL 主从同步备份策略是指在 master 服务器和 slave 服务器之间实现数据同步的备份策略。该策略可以确保数据的一致性和可用性，提高系统的可靠性和稳定性。 在本文中，我们将介绍 ...
SpringBoot第 12 讲：SpringBoot+MySQL主从复制、读写分离
10-10
在本讲中，我们将深入探讨如何使用SpringBoot与MySQL实现主从复制以及读写分离的架构设计。这一技术方案在大型分布式系统中尤为常见，它能够有效地提高数据库系统的可用性和性能。 首先，让我们理解主从复制的核心...
ubuntu 16.04配置MySQL主从同步的配置方法
09-09
在Ubuntu 16.04上配置MySQL主从同步是一项关键的任务，对于实现高可用性和数据备份至关重要。以下是一个详细的步骤指南： **准备工作** 1. **主从数据库版本一致**：为了确保兼容性和数据一致性，建议主从数据库...
mysql-day02
最新发布
m0_64370841的博客
08-29 672
【代码】mysql-day02。
MySQL——多表操作（四）子查询（5）带比较运算符的子查询
PAP
08-28 553
MySQL——子查询（5）带比较运算符的子查询
MySQL空间函数ST_Distance_Sphere()的使用
程序人生
08-29 427
MySQL提供了一系列的空间函数，其中ST_Distance_Sphere()函数可以计算两个经纬度坐标之间的距离，返回单位为米的浮点数。这个函数使用的是球形大地模型，因此对于较短的距离，计算结果的精度比较高。注意，在使用ST_Distance_Sphere()函数时，参数必须是经度和纬度的点对象。函数来创建表示经纬度坐标的点对象，然后将这两个点作为参数传递给。这个函数会计算这两个点之间的距离，并将结果作为。在上面的SQL中，我们将经度和纬度的值作为参数传递给。函数，然后将返回的点对象作为参数传递给。
es相关概念、索引操作（相当于mysql中的数据库操作）
小丁的博客
08-26 397
【代码】es相关概念、索引操作（相当于mysql中的数据库操作）
【MySQL数据库管理问答题】第5章 监控 MySQL
Songyaxuan075118的博客
08-26 1148
MySQL服务器的日志文件类型及用途 默认启用的两种日志 错误日志 二进制日志 常规查询日志和慢速查询日志 Performance Schema的作用 Performance Schema顶级检测组件 sys模式的作用 audit_log_filter_linux_install.sql脚本 配置基于规则的MySQL审计功能 MySQL Enterprise Monitor提供的管理功能 SHOW PROCESSLIST命令输出的列名及作用 中继日志 审计日志 查询日志 慢查询日志
MySQL中的事务
2301_80389611的博客
08-25 1320
Session A和Session B各开启了一个事务，Session B中的事务先将studentno列为1的记录的name 列更新 为'张三'，然后Session A中的事务再去查询这条studentno为1的记录，如果读到列name的值为' 张三'，而 Session B中的事务稍后进行了回滚，那么Session A中的事务相当于读到了一个不存在的数据， 这种现象 就称之为 脏读。事务的执行是互不干扰的，一个事务不可能看到其他事务运行时中间某一时刻的数据。基于锁的状态分类：意向共享锁、意向排它锁。
MySQL做题笔记
qq_44736340的博客
08-25 578
sql语法
前后端分离项目实战-通用管理系统搭建（前端Vue3+ElementPlus，后端Springboot+Mysql+Redis）第七篇：菜单和路由动态绑定
分享式获得也是一种学习的态度
08-26 840
每个人都有惰性，但不断学习是好好生活的根本，共勉！绿竹入幽径，青萝拂行衣。——《下终南山过斛斯山人宿置酒》
Clickhouse和MySQL的区别以及适用业务场景
小橙子的笔记屋
08-26 634
clickhouse和MySQL的区别
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值