Mysql 数据库主从复制

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
日志服务 SLS,月写入数据量 50GB 1个月
简介: 在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。

查询两台虚拟机的IP

主虚拟机IP

从虚拟机IP服务

修改对应的配置文件

查询对应配置文件的命令

find / -name my.cnf

编辑对应的配置文件

主 my.cnf (部分配置)

[mysqld]
########basic settings########
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
port = 3306
socket  = /home/mysql/mysql8/mysql.sock
datadir = /home/mysql/mysql8
default_authentication_plugin=mysql_native_password
binlog_do_db = stms
  1. server-id = 1
    • server-id是一个必需的配置项,用于在MySQL复制环境中唯一标识每个服务器实例。在主从复制设置中,每个服务器必须有一个唯一的server-id。值1通常用于主服务器。
  2. log_bin = /var/log/mysql/mysql-bin.log

    • log_bin指定二进制日志文件的位置和名称。二进制日志记录了所有对数据库执行的更改操作,这对于复制和恢复操作至关重要。/var/log/mysql/mysql-bin.log是日志文件的路径和名称,可以根据实际需要进行调整。

      从 my.cnf 配置

      server_id = 2
      gtid_mode=ON
      enforce_gtid_consistency=ON
      
      在MySQL的配置文件中,[mysqld]部分包含了MySQL服务器实例的配置指令。以下是对您提供的配置指令的解释:
  3. server-id = 2

    • server-id是一个必需的配置项,用于在MySQL复制环境中唯一标识每个服务器实例。在主从复制设置中,每个服务器必须有一个唯一的server-id。值2通常用于从服务器。
  4. relay_log = /var/log/mysql/mysql-relay-bin.log
    • relay_log指定中继日志文件的位置和名称。中继日志用于存储从主服务器接收到的二进制日志事件,然后由从服务器的SQL线程读取并执行这些事件。/var/log/mysql/mysql-relay-bin.log是中继日志文件的路径和名称,可以根据实际需要进行调整。
  5. read_only = 1
    • read_only是一个可选的配置项,当设置为1时,它将使MySQL服务器进入只读模式。在这种模式下,除了具有SUPER权限的用户外,所有客户端的写操作(如INSERT, UPDATE, DELETE)都将被拒绝。这通常用于从服务器,以防止数据被意外修改,确保从服务器的数据与主服务器保持一致。

      重启MYSQL服务

      service mysqld stop
      service mysqld start
      

      主从均重启

      主服务器操作

  • 登录到MySQL控制台:

    mysql -u root -p
    
  • 创建复制用户并授权:

    CREATE USER 'replica_user'@'%' IDENTIFIED BY 'password';
    GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
    ALTER USER 'replica_user'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
    FLUSH PRIVILEGES;
    
  • 记录当前的二进制日志文件名和位置:

    SHOW MASTER STATUS;
    

    进行数据库锁表

    FLUSH TABLES WITH READ LOCK;
    

    进行全量备份

    mysqldump -u aisino_app -p --databases stms  --master-data > backup.sql
    

    将备份文件传输到从服务器:

    scp backup.sql user@slave_ip:/path/to/backup.sql
    

    scp backup.sql user@slave_ip:/path/to/backup.sql 是一条命令,用于在Linux或Unix系统中通过SSH(Secure Shell)安全地复制文件。这条命令的具体组成部分如下:

  • scp: 是Secure Copy的缩写,是一个命令行工具,用于在本地系统和远程系统之间或者两个远程系统之间安全地复制文件。

  • backup.sql: 这是要复制的源文件,位于当前执行scp命令的本地系统上。
  • user@slave_ip: user是远程服务器(从服务器)上的用户名,slave_ip是远程服务器的IP地址。这个部分指定了文件要复制到的目标服务器。
  • :/path/to/backup.sql: 这是文件在远程服务器上的目标路径和文件名。:表示紧随其后的路径是远程服务器上的路径。

整条命令的含义是:将本地文件backup.sql通过SSH安全地复制到远程服务器slave_ip上,使用用户user进行认证,并将文件放置在远程服务器上的/path/to/backup.sql路径下。
在执行此命令时,系统会提示输入远程用户user的密码(除非设置了无密码SSH登录),以完成文件的复制过程。

从服务器操作

从数据库恢复数据

mysql -u root -p < /path/to/backup.sql

配置从库复制

查看 server_id

SHOW VARIABLES LIKE 'server_id';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 0 |
+---------------+-------+
使用获取的主库二进制日志文件名和位置配置从库:

CHANGE REPLICATION SOURCE TO
  SOURCE_USER='replica_user',
  SOURCE_PASSWORD='123456',
  SOURCE_HOST='192.168.44.142',
  SOURCE_PORT=3306,
  SOURCE_LOG_FILE='taxdb-bin.000045',
  SOURCE_LOG_POS=245;

请确保在执行此命令之前,你已经创建了用于复制的用户 replica_user,并且该用户在主服务器(192.168.44.142)上具有适当的权限。
执行完上述命令后,你可以使用以下命令启动复制:

START REPLICA;

通过执行 SHOW REPLICA STATUS\G; 来检查复制状态,确保 Replica_IO_Running 和 Replica_SQL_Running 都是 Yes。

mysql> SHOW REPLICA STATUS\G;
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: 192.168.44.142
                  Source_User: replica_user
                  Source_Port: 3306
                Connect_Retry: 60
              Source_Log_File: taxdb-bin.000045
          Read_Source_Log_Pos: 245
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 326
        Relay_Source_Log_File: taxdb-bin.000045
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Source_Log_Pos: 245
              Relay_Log_Space: 540
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File: 
           Source_SSL_CA_Path: 
              Source_SSL_Cert: 
            Source_SSL_Cipher: 
               Source_SSL_Key: 
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Source_Server_Id: 1
                  Source_UUID: 200dbdd5-dc52-11ee-8954-000c29f27890
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 86400
                  Source_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Source_SSL_Crl: 
           Source_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 200dbdd5-dc52-11ee-8954-000c29f27890:1-28913
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Source_TLS_Version: 
       Source_public_key_path: 
        Get_Source_public_key: 0
            Network_Namespace:

解除主数据只读

UNLOCK TABLES;
FLUSH PRIVILEGES;

税金3.0更新用户查看效果

从库

税金新增用户后,从库自动同步

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
11天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
9天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。
|
1天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
14 3
|
2天前
|
SQL 关系型数据库 MySQL
《揭秘MySQL主从复制:数据同步的幕后故事》
MySQL主从复制依赖binlog,从库通过I/O线程拉取主库的binlog并存入relay log,SQL线程再将relay log中的事件应用到数据。复制过程是异步的,确保从库能独立管理同步。三种复制方式:默认异步(可能丢失数据)、全同步(低性能)和半同步(折中)。半同步要求至少一个从库确认收到binlog事件。主从延迟指的是从库相对于主库的数据滞后。
|
21小时前
|
DataWorks 关系型数据库 MySQL
DataWorks操作报错合集之从OceanBase(OB)数据库调度数据到MySQL数据库时遇到连接报错,该怎么办?
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
|
8天前
|
关系型数据库 数据库 RDS
利用DTS将自建mysql5.7版本数据库迁移至对应rds报错
利用DTS将自建mysql5.7版本数据库迁移至对应rds报错
30 0
|
14天前
|
XML Java 关系型数据库
Action:Consider the following: If you want an embedde ,springBoot配置数据库,补全springBoot的xml和mysql配置信息就好了
Action:Consider the following: If you want an embedde ,springBoot配置数据库,补全springBoot的xml和mysql配置信息就好了
|
14天前
|
关系型数据库 MySQL 数据库
|
14天前
|
关系型数据库 MySQL Shell
|
14天前
|
存储 关系型数据库 MySQL