MySQL数据库的主主同步配置

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

一、架构方案思路

1.两台mysql都可读写,互为主备。本文的实验中:默认只使用一台(DCGH-DB1)负责数据的写入,另一台(DCGH-DB2)备用。

2.DCGH-DB1是DCGH-DB2的主库,DCGH-DB2又是DCGH-DB1的主库,它们互为主从。

3.不足之处:DCGH-DB2可能会一直处于空闲状态(后期经过改进后,可以用它当从库,负责部分查询)。

二、基础环境

操作系统:CentOS Linux release 7.4.1708 (Core) 64Bit
MySQL版本:MySQL Community Server version: 5.7.21 64Bit
主机名及IP:DCGH-DB1(10.1.1.31),DCGH-DB2(10.1.1.32)
两台主机的操作系统,数据库版本完全一致(克隆的)。实际环境中,需要确保操作系统版本完全一致、MySQL版本完全一致、数据完全一致,可能会涉及到数据库的备份与还原。

三、环境初始化

1.在DCGH-DB2上安装MySQL服务器,进行初始化。

[root@DCGH-DB2 ~]# curl -C - -O https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
[root@DCGH-DB2 ~]# rpm -ivh mysql57-community-release-el7-11.noarch.rpm
[root@DCGH-DB2 ~]# yum -y install mysql-community-server
[root@DCGH-DB2 ~]# systemctl start mysqld
[root@DCGH-DB2 ~]# systemctl enable mysqld
[root@DCGH-DB2 ~]# firewall-cmd --permanent --add-port=3306/tcp
[root@DCGH-DB2 ~]# firewall-cmd --reload
[root@DCGH-DB2 ~]# grep -i password /var/log/mysqld.log 
2018-04-11T10:11:40.732848Z 1 [Note] A temporary password is generated for root@localhost: p>-/fQDca3ag
[root@DCGH-DB2 ~]# mysql -u root -p
Enter password: 
mysql> set password=password("DCGH-test-db2");
mysql> exit

如果遭遇如下错误:ERROR 1819 (HY000): Your password does not satisfy the current policy requirements,密码不满足当前策略要求,那么请执行以下命令:

set global validate_password_policy=0;  
set global validate_password_mixed_case_count=0;  
set global validate_password_number_count=3;  
set global validate_password_special_char_count=0;  
set global validate_password_length=3;  

2.关机克隆DCGH-DB2,修改配置,使之满足DCGH-DB1要求,只需修改主机名及IP即可,登录数据库,修改密码。

[root@DCGH-DB2 ~]# hostnamectl set-hostname DCGH-DB1 --static
[root@DCGH-DB2 ~]# nmtui
[root@DCGH-DB2 ~]# systemctl restart network
[root@DCGH-DB1 ~]# mysql -u root -p
Enter password: 
mysql> set password=password("DCGH-test-db1");
mysql> exit

至此,实验环境准备完毕。

四、DCGH-DB1配置

1.修改/etc/my.cnf,在[mysqld]下加入如下内容:

server-id=31
auto-increment-increment=2
auto-increment-offset=1
log-bin=mysql-bin
binlog_format=mixed
relay-log=relay-bin
log-slave-updates

2.重启服务,创建同步用户copy,并授予相应权限,锁表,查出Position备用。

[root@DCGH-DB1 ~]# mysql -u root -A -p
Enter password: 
mysql> grant replication slave,replication client on *.* to 'copy'@10.1.1.32 identified by 'DCGH-test-db2';
mysql> flush privileges;
mysql> flush tables with read lock;
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      627 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

五、DCGH-DB2配置

1.修改/etc/my.cnf,在[mysqld]下加入如下内容:

server-id=32
auto-increment-increment=2
auto-increment-offset=1
log-bin=mysql-bin
binlog_format=mixed
relay-log=relay-bin
log-slave-updates

2.重启服务,创建同步用户copy,并授予相应权限并在DCGH-DB2上去同步DCGH-DB1上的数据。

[root@DCGH-DB2 ~]# mysql -u root -A -p
Enter password: 
mysql> grant replication slave,replication client on *.* to 'copy'@10.1.1.31 identified by 'DCGH-test-db1';
mysql> CHANGE MASTER TO MASTER_HOST='10.1.1.31',MASTER_USER='copy',MASTER_PASSWORD='DCGH-test-db2',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=627;
mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 10.1.1.31
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 627
               Relay_Log_File: relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No
            Slave_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_Master_Log_Pos: 627
              Relay_Log_Space: 154
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1593
                Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 31
                  Master_UUID: 
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 180411 19:27:07
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 

致命报错来袭:Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different。原来是UUID重复了,这是因为咱们的MySQL服务器是克隆出来的,好办,改server-uuid(随便改个数字或字母跟之前uuid不一致即可)!

[root@DCGH-DB2 ~]# vi /var/lib/mysql/auto.cnf
server-uuid=ba5f1c18-3d70-11e8-891f-000c2986a1f0
[root@DCGH-DB2 ~]# systemctl restart mysqld

重复以上步骤:

mysql> CHANGE MASTER TO MASTER_HOST='10.1.1.31',MASTER_USER='copy',MASTER_PASSWORD='DCGH-test-db2',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=627;
mysql> start slave;
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.1.1.31
                  Master_User: copy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 627
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_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_Master_Log_Pos: 627
              Relay_Log_Space: 521
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 31
                  Master_UUID: ba5f1c18-3d70-11e8-891f-000c2986a1f9
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 

Slave_IO_Running: Yes,Slave_SQL_Running: Yes,Seconds_Behind_Master: 0,表明目前已经同步了。

六、验证

1.回到DCGH-DB1,解锁表创建数据库DCGHDB。

mysql> unlock tables;
mysql> create database DCGHDB;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| DCGHDB             |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

我在DCGHDB2使用show databases命令,结果一致。
2.在DCGH-DB1创建表并插入数据,看是否同步。

mysql> use DCGHDB;
Database changed
mysql> create table test(username varchar(30),password varchar(30));
mysql> insert into test(username,password) values('IVAN DU','DCGH-test');
mysql> select * from test;
+----------+-----------+
| username | password  |
+----------+-----------+
| IVAN DU  | DCGH-test |
+----------+-----------+

在DCGH-DB2查表test的数据是否一致。

mysql> use DCGHDB;
Database changed
mysql> select * from test;
+----------+-----------+
| username | password  |
+----------+-----------+
| IVAN DU  | DCGH-test |
+----------+-----------+

3.在DCGH-DB1更新表test。

mysql> update test set username='DCGH' where username='IVAN DU';
mysql> select * from test;
+----------+-----------+
| username | password  |
+----------+-----------+
| DCGH     | DCGH-test |
+----------+-----------+

在DCGH-DB2进行查看,结果一致。

mysql> select * from test;
+----------+-----------+
| username | password  |
+----------+-----------+
| DCGH     | DCGH-test |
+----------+-----------+

4.经验证,删除表数据、表结构、库都能够顺利同步。不展示,相关验证命令如下:

mysql> delete from test;
mysql> drop table test;
mysql> drop database DCGHDB;
mysql> show tables;

七、拓展知识

配置文件部分选项解读及拓展。

# 服务器的ID,必须唯一,一般设置自己的IP
server-id=31
# 复制过滤:不需要备份的数据库(MySQL库一般不同步)
binlog-ignore-db=mysql
# 开启二进制日志功能,名字可以随便取,最好有含义(比如项目名)
log-bin=DCGH
# 为每个 session 分配的内存,在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式(mixed,statement,row,默认格式是 statement)
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为 0,表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误,避免 slave 端复制中断。 
## 如:1062 错误是指一些主键重复,1032 错误是因为主从数据库数据不一致
slave_skip_errors=1062
# 作为从服务器时的中继日志
relay_log=edu-mysql-relay-bin
# log_slave_updates 表示 slave 将复制事件写进自己的二进制日志
log_slave_updates=1
# 主键自增规则,避免主从同步ID重复的问题
auto_increment_increment=2  # 自增因子(每次加2)
auto_increment_offset=1     # 自增偏移(从1开始),单数

八、参考资料

https://dev.mysql.com/doc/refman/5.6/en/replication-howto-newservers.html
https://dev.mysql.com/doc/refman/5.7/en/stored-programs-logging.html
https://dev.mysql.com/doc/refman/5.7/en/replication.html

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
7天前
|
SQL 关系型数据库 MySQL
MySQL Workbench的安装与配置
MySQL Workbench的安装与配置
|
1天前
|
分布式计算 大数据 关系型数据库
MaxCompute产品使用问题之如何查看数据离线同步每天从MySQL抽取的数据量
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
1天前
|
分布式计算 大数据 关系型数据库
MaxCompute产品使用问题之如何实现MySQL的实时增量同步
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
4天前
|
canal 关系型数据库 MySQL
蓝易云 - 详解canal同步MySQL增量数据到ES
以上就是使用Canal同步MySQL增量数据到Elasticsearch的基本步骤。在实际操作中,可能还需要根据具体的业务需求和环境进行一些额外的配置和优化。
21 2
|
7天前
|
关系型数据库 MySQL 数据库
MySQL8.0.36 安装配置教程(保姆级,包含图文讲解,环境变量的配置)适合小白
MySQL8.0.36 安装配置教程(保姆级,包含图文讲解,环境变量的配置)适合小白
|
6天前
|
负载均衡 关系型数据库 MySQL
MySQL 主主复制与主从复制对比
MySQL的主主复制和主从复制是两种常见的数据库复制配置方式,各有优缺点和适用场景。以下是对这两种复制方式的详细对比: ### 主从复制 (Master-Slave Replication) **特点:** 1. **单向复制**:数据从主服务器复制到一个或多个从服务器。从服务器只能从主服务器接收数据更新,不能向主服务器发送数据。 2. **读写分离**:主服务器处理写操作(INSERT、UPDATE、DELETE),从服务器处理读操作(SELECT),可以分担读负载,提高系统的整体性能。 3. **数据一致性**:数据在主服务器上是最新的,从服务器上可能会有一定的延迟。 **优点:**
|
7天前
|
消息中间件 关系型数据库 MySQL
实时计算 Flink版操作报错合集之同步MySQL数据并EP(复杂事件处理)时,编译报错,如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
14小时前
|
缓存 Java 数据库
springboot数据库及缓存常用依赖及配置
springboot数据库及缓存常用依赖及配置
28 9
|
1天前
|
分布式计算 大数据 关系型数据库
MaxCompute产品使用问题之mysql读取从mc里的每10分钟计算好的结果数据表,如何同步数据过去
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
5天前
|
安全 BI 数据库
数据库大作业——基于qt开发的图书管理系统 (一)环境的配置与项目需求的分析
数据库大作业——基于qt开发的图书管理系统 (一)环境的配置与项目需求的分析

热门文章

最新文章