mysql 数据库合并命令_mysql多源复制及合并数据库

背景:机器1:10.1.6.99

机器2:10.1.6.100

机器3:10.1.6.101

1、分别在三台主机上安装mysql数据库

1)配置yum源

[root@master lucky-front]# cat /etc/yum.repos.d/mysql-community.repo

[mysql-connectors-community]

name=MySQL Connectors Community

baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$basearch/

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]

name=MySQL Tools Community

baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/7/$basearch/

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.5

[mysql55-community]

name=MySQL 5.5 Community Server

baseurl=http://repo.mysql.com/yum/mysql-5.5-community/el/7/$basearch/

enabled=0

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.6

[mysql56-community]

name=MySQL 5.6 Community Server

baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/7/$basearch/

enabled=0

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql57-community]

name=MySQL 5.7 Community Server

baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/

enabled=1

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql80-community]

name=MySQL 8.0 Community Server

baseurl=http://repo.mysql.com/yum/mysql-8.0-community/el/7/$basearch/

enabled=0

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-preview]

name=MySQL Tools Preview

baseurl=http://repo.mysql.com/yum/mysql-tools-preview/el/7/$basearch/

enabled=0

gpgcheck=1

gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

2)安装mysql社区版

yum install -y mysql-community-server.x86_64

3)查看mysql临时密码

[root@master lucky-front]# grep password /var/log/mysqld.log

2020-12-30T08:10:38.641764Z 1 [Note] A temporary password is generated for root@localhost: cVjf.CqQ0Yud

4)使用临时密码登陆mysql并修改密码

alter user user() identified by "123456";

2、从别的机器上导入测试数据库mp_basic_service到机器1，导入mp_contract到机器3，分别配置机器1，机器3的my.cnf配置文件，增加以下

server-id=2

log-bin=river

3、导出机器1和机器3的数据库并导入机器2

机器1:mysqldump -uroot -p'Hczd365!!' --databases mp_contract >/tmp/mp_contract.sql

机器3:mysqldump -uroot -p'Hczd365!!' --databases mp_basic_service >/tmp/mp_basic_service.sql

机器1:scp /tmp/mp_basic_service.sql 10.1.6.100:/tmp/

机器3:scp /tmp/mp_contract.sql 10.1.6.100:/tmp/

机器2mysql>source /tmp/mp_basic_service.sql;

机器2mysql>source /tmp/mp_contract.sql;

4、配置机器2的my.cnf

pid-file=/var/run/mysqld/mysqld.pid

master-info-repository=TABLE

relay-log-info-repository=TABLE

# replicate-rewrite-db 多库同步到单库，库名重写，其他的replicate-*会在replicate-rewrite-db评估后执行，多个映射的话，配置文件中包含多行即可，这个设计好傻，为啥不逗号或者分号分隔呢。如果同时有多个replicate*过滤器，先评估数据库级别的、然后表级别的；先评估do，后评估ignore(也就是在白名单或者不在黑名单的模式)。比如，主库多个分库合并到从库一个库

replicate-do-db="mp_basic_service"

replicate-do-db="mp_contract"

replicate_do_db="test"

replicate-do-table="mp_basic_service.%"

replicate-do-table="mp_contract.%"

replicate-rewrite-db="mp_contract->test"

replicate-rewrite-db="mp_basic_service->test"

replicate-wild-do-table="test.%"

replicate-ignore-db=information_schema

replicate-ignore-db=mysql

replicate-ignore-db=performance_schema

replicate-ignore-db=sys

sync_relay_log=1

relay_log_recovery=1

slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=8 #具体值多少合适需要性能测试得到，一般cpu数量即可

server-id = 3

#replicate-ignore-db #如果只需要不同步某些库

slave-skip-errors=ddl_exist_errors + 1022 #建议不要同步ddl(1007,1008,1050,1051,1054,1060,1061,1068,1094,1146,1022)

log_slave_updates=ON(GTID模式必须开始log_slave_updates，对性能有一定影响，Mysql 5.7之后从节点可以不开启binlog)

skip-slave-start=false #默认false，也就是server重启的时候会自动启动slave，不建议修改

plugin-load=rpl_semi_sync_slave=semisync_slave.so

rpl_semi_sync_slave_enabled=0

relay-log = /var/lib/mysql/mysql-relay-bin

relay-log-index = /var/lib/mysql/mysql-relay-bin.index

log-error = /var/lib/mysql/mysql.err

master-info-file = /var/lib/mysql/mysql-master.info

relay-log-info-file = /var/lib/mysql/mysql-relay-log.info

5、机器1查看并记住二进制日志位置

mysql>show master status;

mysql> show master status;

+--------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+--------------+----------+--------------+------------------+-------------------+

| river.000002 | 73070 | | | |

+--------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

6、机器3查看并记住二进制日志位置

mysql> show master status;

+--------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+--------------+----------+--------------+------------------+-------------------+

| river.000001 | 5898 | | | |

+--------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

7、分别在三台机器创建复制用户，并赋予权限,在机器1和3要将SLAVE将成MASTER

create user repl ;

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

8、在机器2使用change master命令

CHANGE MASTER TO

MASTER_HOST='10.1.6.99',

MASTER_USER='repl',

MASTER_PASSWORD='Hczd365!!',

MASTER_PORT=3306,

MASTER_LOG_FILE='river.000002',

MASTER_LOG_POS=73070

FOR CHANNEL 'source_1';

CHANGE MASTER TO

MASTER_HOST='10.1.6.101',

MASTER_USER='repl',

MASTER_PASSWORD='Hczd365!!',

MASTER_PORT=3306,

MASTER_LOG_FILE='river.000001',

MASTER_LOG_POS=5898

FOR CHANNEL 'source_2';

9、启动slave

start slave for channel 'source_1';

start slave for channel 'source_2';

10、查看slave状态

show slave status for channel 'source_1'\G;

show slave status for channel 'source_2'\G;

11、看到两个状态均为yes说明配置成功

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

12、需要注意的是要提前在机器2创建test库，并且使用mavicat等工具将机器1和机器3的两个数据库的表导入到机器2的test库

当然使用mysqldump导出来，再用sed -i替换里面的数据库，再导入test库也可以，我这边是怕替换错了，把里面的表也给改了，直接用navicat比较简单

在测试机器1和机器3上的2个库都同步到test2时就可以删除机器2上的mp_contract和mp_basic_service这两个导入的不再用于同步的库(现在同步的库是test)