MySQL主主复制

最新推荐文章于 2025-03-22 14:18:04 发布

原创最新推荐文章于 2025-03-22 14:18:04 发布 · 1.5k 阅读 ·

大模型引用 1 次

本内容遵循CC 4.0 BY-SA版权协议

Josen Winter

关注

标签

#mysql #数据库 #服务器

分类数据库

本文详细介绍了MySQL的主主复制和级联复制，包括如何配置、级联复制的效果以及主键自增方案的实践。在主主复制中，通过调整`auto_increment_increment`和`auto_increment_offset`实现两个主库并发写入，同时讨论了其优缺点和实际应用中的考虑因素。文章还提供了完整的配置和操作步骤，帮助读者理解并实现MySQL的双向复制架构。

MySQL主主复制

超哥上一节是讲了MySQL的一主一从复制

尽量多的做虚拟机快照

一主多从也就是，多准备几个slave，同样操作即可

复制结构还有

级联复制
双向主从（主主）
多主复制（环状）

MySQL级联复制

也称之为MSS复制，master、slave1、slave2

级联复制（cascade）：是指从主场地复制过来的又从该场地再次复制到其他场地，即A场地把数据复制到B场地，B场地又把这些数据或其中部分数据再复制到其他场地。

级联复制可以平衡当前各种数据需求对网络通信的压力。

级联复制通常与主/从复制（master/slave）联合使用。

master、slave1 都需要开启binlog

Slave

MySQL复制核心是通过传输binlog日志实现复制

级联复制是对主从复制的扩展

级联复制架构有一个中间从库B的角色

从库开启binlog场景

在做主从复制实验时候，超过说的是slave不开启binlog

这里slave开启binlog是应对如下场景

当前slave还要作为其他slave的主库，例如
- 级联复制
- 双主、互为主从
当slave也作为全备的服务器，也就需要binlog开启，进行增量数据恢复

如何配置

上一节超哥讲的主从复制，是如下形式

有三台机器

master1    10.211.55.12    A        主
slave1    10.211.55.9        B        从

slave2 10.211.55.8        C

如何实现级联？

只需要在机器B上，开启binlog，机器B，修改my.cnf

[mysqld]
log_bin = /data/3306/logs/mysql-bin
log_slave_updates    #<==必须要有这个参数，否则不会记录Binlog日志。
#<==相当于find /path -type f -name " mysql-bin.000*" -mtime +7 |xargs rm -f
expire_logs_days = 7

这里只需要在机器B上开启binlog日志，然后让机器C和B进行同步，就实现了级联复制，操作和前面一样，这里超哥就不说了，大家当做扩展练习

最终完成的效果应该是

数据写入A机器，mysql-master

立即同步到B、以及C两个从机器

级联复制效果

级联复制是主从复制的扩展，常用在下面的效果

主从复制、A、B、C三个数据库
- B库作为级联中间库，减轻A库的压力
级联复制、主从复制主要用于性能不高的业务场景
- 数据备份
- 企业内网数据

MySQL主主复制

MySQL主主复制是级联复制的特殊形式，上一节是A > B > C 的单向复制

而主主复制是特殊架构，互为master，都可以写入数据。

主主复制，也就是互相复制，其实就是配置参数，多了一些。

背景

既然是主主模式、也就是都可以写入数据，部分架构设计会考虑主主模式，提高并发写入能力，但是高并发场景下，master1、master2都要并发写入，压力并不会小，具体还需要实际场景验证。

如果要切实增加大并发写入数据，主主复制架构并不是好选择，有如下参考

分库设计
- 例如有一个单实例数据库，运行多个库，如web业务库、博客业务库、CRM业务库
- 可以将每一个产品库都做成一套数据库集群，提高并发写入的压力
- 这样只需要运维同学调整运维架构，无须变动业务代码
分表设计
- 这需要开发人员，针对业务进行对数据字段进行分表设计，运维人员配合处理
数据库集群拆分
- 当单表数据写入达到指定行数后，自动化扩容、拆表数据。

MySQL主主复制

该架构部署，有两种方案

方案：主键自增

例如这里是一张表，包含了自主键，自增

自增

CREATE TABLE `web_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payment_type` smallint(6) NOT NULL,
  `payment_number` varchar(128) DEFAULT NULL,
  `order_number` varchar(128) NOT NULL,
  `actual_amount` double NOT NULL,
  `status` smallint(6) NOT NULL,
  `date` datetime(6) NOT NULL,
  `pay_time` datetime(6) DEFAULT NULL,
  `cancel_time` datetime(6) DEFAULT NULL,
  `account_id` int(11) NOT NULL,
  `order_type` smallint(6) DEFAULT 0,
  `memo` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_number` (`order_number`),
  KEY `web_order_account_id_c8203036_fk_web_account_id` (`account_id`),
  CONSTRAINT `web_order_account_id_c8203036_fk_web_account_id` FOREIGN KEY (`account_id`) REFERENCES `web_account` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52127 DEFAULT CHARSET=utf8

我们能看出来，这里的主键ID是递增1的，每次加一

这是通过auto_increment参数，每次添加一条记录，id号就加一

有关主主复制，master1和master2都是可以写入数据的，并发写入

master1库，每张表的主键都为奇数、1、3、5、7。。
master2表，每张表的主键都为偶数，例如2、4、6、8。。。

知道怎么配置数据表，配置文件如下

my.cnf

m1的my.cnf

auto_increment_increment=2 # m1设置，间隔为2，奇数是1、3、5
auto_increment_offset=1 # id初始位置是1

m2的my.cnf

auto_increment_increment=1 # m2设置，间隔为1，id是2、4、6
auto_increment_offset=2 # m2的id初始位置是2

问题点

该方式

优点：网站代码无须做更改，快速实现双主架构
缺点：id号在表中不是连续的

自增ID-主主复制实践

环境准备

主主复制，准备好2台完全一样的mysql环境即可

m1 10.211.55.12   3306
m2 10.211.55.9        3306

m1

# 环境准备
[root@mysql-server56 opt]# mkdir -p /mm_data/3306/
[root@mysql-server56 3306]# touch /mm_data/3306/my.cnf

my.cnf m2待会也用这个配置文件，m1和m2这里一定得不同！！

这是m2的配置

server_id = 2

auto_increment_increment = 2

auto_increment_offset = 2

# 这是m1的配置
[root@mysql-server56 3306]# cat my.cnf
[client]
port=3306
socket=/mm_data/3306/mysql.sock
default-character-set=utf8

[mysqld]
user=mysql
port=3306
socket=/mm_data/3306/mysql.sock
basedir=/application/mysql-5.6.40-linux-glibc2.12-x86_64/
datadir=/mm_data/3306/data
log-bin=/mm_data/3306/mysql-bin
server-id=1
expire_logs_days=7
auto_increment_increment=2 # 自增id的步长，为2，依次是1、3、5
auto_increment_offset=1    # 自增id起点
slave-skip-errors=1032,1062,1007,1008,1146,1049
log_slave_updates
character-set-server=utf8


[mysqld_safe]
log-error=/mm_data/3306/mysql_3306_error.log
pid-file=/mm_data/3306/mysqld_3306.pid


# 授权
[root@mysql-server56 3306]# chown -R mysql.mysql /mm_data/


# 初始化
/application/mysql-5.6.40-linux-glibc2.12-x86_64/scripts/mysql_install_db --defaults-file=/mm_data/3306/my.cnf --basedir=/application/mysql-5.6.40-linux-glibc2.12-x86_64/ --datadir=/mm_data/3306/data/ --user=mysql

# 启动脚本
#!/bin/bash
port=3306
mysql_user="root"
Cmdpath="/application/mysql-5.6.40-linux-glibc2.12-x86_64/bin"
mysql_sock="/mm_data/${port}/mysql.sock"
mysqld_pid_file_path=/mm_data/${port}/mysqld_${port}.pid

start(){
if [ ! -e "$mysql_sock" ];then
    printf "Starting MySQL...\n"
    /bin/sh ${Cmdpath}/mysqld_safe --defaults-file=/mm_data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &
    sleep 3
else
    printf "MySQL is running...\n"
    exit 1
fi
}


stop(){
    if [ ! -e "$mysql_sock" ];then
        printf "MySQL is stopped...\n"
        exit 1
    else
        printf "Stoping MySQL...\n"
        mysqld_pid=`cat "$mysqld_pid_file_path"`
    if (kill -0 $mysqld_pid 2>/dev/null)
        then
        kill $mysqld_pid
        sleep 2
        fi
    fi
}



restart(){
    printf "Restarting MySQL...\n"
    stop
    sleep 2
    start
}



case "$1" in
start)
    start
;;
stop)
    stop
;;
restart)
    restart
;;
*)
    printf "Usage: /data/${port}/mysql{start|stop|restart}\n"
esac



# 授权
[root@mysql-server56 3306]# chmod +x mysql_3306

# 创建文件
[root@mysql-server56 3306]# touch /mm_data/3306/mysql_3306_error.log


# 启动
[root@mysql-server56 3306]#  /mm_data/3306/mysql_3306 start
Starting MySQL...
[root@mysql-server56 3306]# netstat -tunlp|grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      16162/mysqld
[root@mysql-server56 3306]#

至此，m1主库的binlog和表记录id的自增配置好了

也正确启动了

m2也遵循一样的操作

创建复制用户

mysql> grant replication slave on *.* to 'repl'@'10.211.55.%' identified by 'repl888';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql>

导出m1的数据

--lock-all-tables，-x
    所有数据库中的所有表加锁。在整体转储过程中通过全局读锁定来实现。该选项自动关闭--single-transaction和--lock-tables。

--master-data[=value]
    该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，并且必须启用二进制日志。
    如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。
    如果选项值等于2，CHANGE MASTER语句被写成SQL注释。如果value被省略，这是默认动作。

备份语句

[root@mysql-server56 3306]# mysqldump  -S /mm_data/3306/mysql.sock -A -B -x --master-data=1 |gzip > /mm_data/mm_all_db_$(date +%F).sql.gz
Warning: Using a password on the command line interface can be insecure.
[root@mysql-server56 3306]#

[root@mysql-server56 ~]# cd /mm_data/
[root@mysql-server56 mm_data]# ls
3306  mm_all_db_2021-04-26.sql.gz
[root@mysql-server56 mm_data]#

# 该数据文件，内容如下，做了一个change_master
[root@mysql-server56 mm_data]# zcat mm_all_db_2021-04-26.sql.gz |grep -i '^change'
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=417;

发给m2机器

[root@mysql-server56 mm_data]# scp -rp /mm_data/mm_all_db_2021-04-26.sql.gz root@10.211.55.9:/mm_data/
root@10.211.55.9's password:
mm_all_db_2021-04-26.sql.gz                                                                                                                                                                100%  177KB  32.9MB/s   00:00

[root@mysql-server56 mm_data]#

m2

修改m2配置文件

[root@chaoge_slave1 mm_data]# cat 3306/my.cnf
[client]
port=3306
socket=/mm_data/3306/mysql.sock


[mysqld]
user=mysql
port=3306
socket=/mm_data/3306/mysql.sock
basedir=/application/mysql-5.6.40-linux-glibc2.12-x86_64/
datadir=/mm_data/3306/data
log-bin=/mm_data/3306/mysql-bin
server-id=2
expire_logs_days=7
auto_increment_increment=2
auto_increment_offset
slave-skip-errors=1032,1062,1007,1008,1146,1049
log_slave_updates


[mysqld_safe]
log-error=/mm_data/3306/mysql_3306_error.log
pid-file=/mm_data/3306/mysqld_3306.pid
[root@chaoge_slave1 mm_data]#

重启

[root@chaoge_slave1 mm_data]# netstat -tunlp|grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      27758/mysqld
[root@chaoge_slave1 mm_data]#

m2导入数据

m1导出的数据是全库数据，也包含了mysql.user表中的数据，也包含了m1上创建的复制用户。

进行数据导入

[root@chaoge_slave1 mm_data]# zcat /mm_data/mm_all_db_2021-04-26.sql.gz |mysql -S /mm_data/3306/mysql.sock

在m2中写入m1的binlog信息

mysql> grant replication slave on . to ‘repl’@‘10.211.55.%’ identified by ‘repl888’;

这里不需要指定binlog文件，以及POS值了

因为在m1数据备份里，已经包含了该信息，通过–master-data=1参数的作用

mysql> change master to
    -> master_host='10.211.55.12',
    -> master_port=3306,
    -> master_user='repl',
    -> master_password='repl888';
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql>
mysql> start slave;
mysql> show slave status\G

确认如下两行信息

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

小结

此时m1是master m2是slave，单向主从复制搭建好了

反过来m2>m1

此时需要配置m2是mater，m1是slave

记录m2的binlog信息

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |  2163445 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

m1操作

这里就是，反过来步骤，在搞一边

m1指定m2的复制信息

mysql> change master to
    -> master_host='10.211.55.9',
    -> master_port=3306,
    -> master_user='repl',
    -> master_password='repl888',
    -> master_log_file='mysql-bin.000003',
    -> master_log_pos=2163445;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

确保如下

             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

如果看不到2个yes，则一定会有报错日志提示

# 例如
  Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'repl@10.211.55.9:3306' - retry-time: 60  retries: 2

可能的问题

注意iptables规则清空
selinux关闭
配置步骤是否有误

测试主主模式

在m1上创建数据库、表、插入数据

mysql> create database chaoge_kings;
Query OK, 1 row affected (0.00 sec)

mysql> show create database chaoge_kings;
+--------------+-----------------------------------------------------------------------+
| Database     | Create Database                                                       |
+--------------+-----------------------------------------------------------------------+
| chaoge_kings | CREATE DATABASE `chaoge_kings` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+--------------+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> use chaoge_kings;
Database changed

create table tanks(
id int not null auto_increment comment '主键id',
name varchar(20) not null comment '英雄姓名',
primary key (id));


mysql> insert into tanks(name) values('盾山'),('凯'),('猪八戒'),('东皇太一');
Query OK, 2 rows affected, 2 warnings (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> select * from tanks;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 盾山         |
|  3 | 凯           |
|  5 | 猪八戒       |
|  7 | 东皇太一     |
+----+--------------+
4 rows in set (0.00 sec)

# 每次自增，步长为2，下一次id就是9了
mysql> show create table tanks;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tanks | CREATE TABLE `tanks` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(20) NOT NULL COMMENT '英雄姓名',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8       |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

登录m2查看数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| chaoge_kings       |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use chaoge_kings;
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
mysql> select * from tanks;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 盾山         |
|  3 | 凯           |
|  5 | 猪八戒       |
|  7 | 东皇太一     |
+----+--------------+
4 rows in set (0.00 sec)

# 插入数据
mysql>  insert into tanks(name) values('关羽'),('白起'),('钟无艳'),('刘备');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

# 查看数据，m2数据库是从最大偶数6开始插入、每次递增2，因此插入的数据是，8、10、12、14
mysql> select * from tanks;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 盾山         |
|  3 | 凯           |
|  5 | 猪八戒       |
|  7 | 东皇太一     |
|  8 | 关羽         |
| 10 | 白起         |
| 12 | 钟无艳       |
| 14 | 刘备         |
+----+--------------+
8 rows in set (0.00 sec)