利用 MySQL 复制聚合多个库到同一个数据库

喜欢ヅ旅行 2023-07-01 05:27 9阅读 0赞

由于微服务分库导致无法跨库（忽略 `db_name.table_name` 方式）统计查询，因此利用 MySQL 主从复制的方式，将主服务器上的多个数据库同步到从服务器上的单个数据库上，从库设置只读，仅用于统计查询。

假设主服务器上有以下数据库：

*  db\_a:
    
     *  table\_a1
     *  table\_a2
 *  db\_b:
    
     *  table\_b1
 *  db\_c
    
     *  table\_c1
     *  table\_c2

下面是简要步骤。

首先，全量备份主库数据，记下 `MASTER_LOG_FILE='mysql-bin.000025', MASTER_LOG_POS=313352823` 的值。

然后，还原备份到从库。

> 主从基本配置参考：[MySQL 主从配置以及 Docker 方式演练][MySQL _ Docker]

从库上还原完成后，假设要将 Master 上的所有表同步到 Slave 的 db\_full 数据库中。继续下面的配置：

[mysqld]
    # 其他配置
    # ...
    
    # 只读 https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_read_only
    read-only=1
    
    # 复制配置
    # 将数据库名变成另一个，通过这种方式把不同的库合并到一起
    replicate-rewrite-db=db_a->db_full
    replicate-rewrite-db=db_b->db_full
    replicate-rewrite-db=db_c->db_full
    
    # 如果想排除表，一定用改名后的数据库名
    # 例如下面
    replicate-ignore-table=db_full.table_c2
    
    # 排除以下数据库
    replicate-ignore-db=information_schema
    replicate-ignore-db=mysql
    replicate-ignore-db=performance_schema
    replicate-ignore-db=sys

> 上述配置是在从库配置，binlog 文件是全的，只是 sql 线程执行时应用上面的策略。  
> 在主库上可以通过 [binlog-do-db=db\_name][binlog-do-db_db_name] 和 [binlog-ignore-db=db\_name][binlog-ignore-db_db_name] 配置同步数据库的信息。

在开启从库同步前，先在 Slave 数据库上创建 `db_full` 数据库，然后手动将其他几个还原库的表复制到 `db_full` 库中，复制完后还原的表 `db_a\b\c` 都可以删除。

在从库上通过下面 SQL 设置同步：

-- 注意修改参数值
    CHANGE MASTER TO
             MASTER_HOST='IP',
             MASTER_USER='slave',
             MASTER_PASSWORD='密码',
    		 MASTER_LOG_FILE='mysql-bin.000025', MASTER_LOG_POS=313352823;

然后通过 `start slave;` 开启同步。

正常情况下，同步过来的数据都会在 `db_full` 数据库中。通过这种简单方式就实现了聚合数据库的操作。

如果要将**多个服务器**上的数据同步到**一个服务器**的表中，可以参考 [MySQL 多源复制][MySQL]。

> **注意！**
> 
> 在 `db_full` 执行 sql 优化时，如果需要对索引进行增删改时，要在 Master 服务器进行操作，避免在从库（只读）进行修改操作。
> 
> 如果从库同步出错，可以参考: [How to Repair MySQL Replication][]

[MySQL _ Docker]: https://blog.csdn.net/isea533/article/details/99448724
[binlog-do-db_db_name]: https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#option_mysqld_binlog-do-db
[binlog-ignore-db_db_name]: https://dev.mysql.com/doc/refman/5.7/en/replication-options-binary-log.html#option_mysqld_binlog-ignore-db
[MySQL]: https://dev.mysql.com/doc/refman/5.7/en/replication-multi-source.html
[How to Repair MySQL Replication]: https://www.howtoforge.com/how-to-repair-mysql-replication