【小菜鸟之---MySQL基础-主从复制】

一只慵懒的猪

已于 2024-04-21 23:24:05 修改

阅读量812

点赞数 14

分类专栏： MySQL 运维文章标签： mysql adb 数据库运维

于 2024-04-17 15:41:39 首次发布

本文链接：https://blog.csdn.net/FWT975959/article/details/137875465

版权

运维同时被 2 个专栏收录

27 篇文章 0 订阅

订阅专栏

MySQL

5 篇文章 0 订阅

订阅专栏

文章目录

【简介】

MySQL 主从复制（Master-Slave Replication）是一种数据复制技术，用于在多个数据库服务器之间的数据同步。在主从复制架构中，一个服务器被设置为主服务器（Master），充当数据源，其他服务器被设置为从服务器（Slave），用来复制主服务器的数据。

1.主从复制优点

主从复制的主要优点有以下几个：

高可用性：
通过将主数据库的数据复制到一个或多个从数据库，可以在主数据库故障时快速切换到从数据库，以实现系统的高可用性和容错能力，从而保证系统的持续可用性。
提高整体性能和吞吐量：
通过将读请求分散到多个从服务器上进行处理，从而减轻了主服务器的负载压力，提高数据库系统的整体性能和吞吐量。主服务器主要负责写操作，而从服务器主要负责读操作，从而分担了主服务器的压力。
数据备份和恢复：
通过主从同步，可以将主服务器上的数据异步复制到从服务器上，从而实现数据备份和灾难恢复的需求。在应对意外数据丢失、灾难恢复或误操作时，可以使用从服务器作为数据的备份源来进行数据恢复。

2.如何实现主从复制？

① 配置并重启主服务器

在主服务器的配置文件（my.cnf）中添加以下参数：

vi /etc/my.cnf

[mysqld] server-id = 1 # 设置服务器 ID，每个主服务器和从服务器都必须有唯一的 ID 
log_bin = /var/log/mysql/mysql-bin.log # 开启二进制日志，记录数据修改操作

log-bin=master-a-bin #日志文件名称
binlog-format= MIXED #二进制日志格式，有row，statement，mixed三种类型，主从要一致
server-id=1 #要求各个服务器的这个id必须不一样
#设置需要复制的数据库
binlog-do-db= test #需要复制的主数据库名字
#设置不要复制的数据库
binlog-ignore-db=mysql

以上配置完成之后，重启 MySQL 服务器，因为重启了 MySQL 服务才能让配置生效。

② 创建用于主从复制的用户

登录到主服务器上，执行以下命令：

CREATE USER 'zjc'@'%' IDENTIFIED BY '9529';    #替换为实际的用户名和密码
GRANT REPLICATION SLAVE ON *.* TO 'zjc'@'%';   #赋权

③ 查看主服务器状态

在 MySQL 主服务器中，执行以下命令，记录下 File 和 Position 的值，后续用于配置从服务器：

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;
±-----------------±---------±-------------±-----------------±------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±------------------+
| mysql-bin.000004 | 368 | test | | |
±-----------------±---------±-------------±-----------------±------------------+
1 row in set (0.00 sec)

④ 配置并重启从服务器

在从服务器的配置文件（my.cnf）中添加以下参数：

[mysqld] server-id = 2 # 设置服务器 ID，每个主服务器和从服务器都必须有唯一的 ID

重启从服务器，让以上配置生效。

systemctl restart mysqld

⑤ 在从服务器上设置主服务器信息

登录到从服务器的 MySQL 中，执行以下命令（将 MASTER_HOST、MASTER_USER、MASTER_PASSWORD、MASTER_LOG_FILE 和 MASTER_LOG_POS 替换为对应的值）：

CHANGE MASTER TO MASTER_HOST='192.168.58.129', MASTER_USER='zjc',
MASTER_PASSWORD='9529', MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=157;


change master to master_host='192.168.157.130',master_user='zjc',master_password='9529',master_log_file='mysql-bin.000004',master_log_pos=120;

⑥ 启动从服务器的复制进程

执行以下命令启动从服务器的复制进程：

START SLAVE;

STOP SLAVE;#停止进程

⑦ 检查从服务器的复制状态

执行以下命令，确保 Slave_IO_Running 和 Slave_SQL_Running 的值都为 “YES”：

SHOW SLAVE STATUS \G;

在这里插入图片描述

实践

问题1：io进程显示connection状态

问题分析：
1.网络不通（可以ping通）
2.账户密码错误（没问题）
3.防火墙（打开端口了）
4.mysql配置文件问题
5.连接服务器时语法
6.主服务器mysql权限

问题2：主库插入数据时候，从库sql进程读取错误

Replica_IO_Running: Yes
          Replica_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1146
                   Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at source log mysql-bin.000005, end_log_pos 1693. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

问题解决：

根据上提示，打开详细信息
select * from performance_schema.replication_applier_status_by_worker\G；

在这里插入图片描述

分析出问题在于创建表时候没有开启主从复制，所以主库建数据库和表时，从库数据库没有创建相关表，然后导致插入数据读取时导致sql复制失败

3.主从复制原理

MySQL 数据库的主从复制主要是基于 Binary Log（二进制文件，简称 bin log）实现的，它的实现流程如下：

它的主要执行流程如下：

主数据库接收到一个写操作（如 INSERT、UPDATE、DELETE）时，会将这个操作记录到二进制日志（Binary Log）中，将数据修改的操作按顺序记录下来。

从数据库 IO 线程会自动连接主服务，从二进制中读取同步数据，记录到中继日志（Relay Log）中。

从数据库的 SQL 线程会定期从中继日志中获取同步数据，写入到从数据库中。