mySQL的多源复制

mySQL的多源复制

因领导要求，需要从两台不同业务的数据库中，同步数据到一台数据库，并修改他们的索引
由于不确定索引修改后，数据是否还能同步成功，此文档仅为事前方案
mysql版本：5.7
主库1：master1
主库2：master2
新增从库：slave

主库已经建好，暂时无需改动
从库配置文件的配置

[mysqld]
master-info-repository=TABLE
relay-log-info-repository=TABLE
#如为单表，则把“%”替换为表名
replicate_wild_do_table=库1.%
#如为多表需要写多行
replicate_wild_do_table=库2.%
slave_parallel_workers = 4
read-only

启动从库：

systemctl start mysqld
systemctl enable mysqld

主库创建权限用户

grant replication slave on 库名.* to  'test' @ '%'  identified by  '123123' ；
grant replication slave on 库名.* to  'test' @ '%'  identified by  '123123' ；

在从库备份主库的数据

mysqldump -uroot -pDigitalgd\@database00700_service_root -h173.16.70.244 -P3306 --master-data=2 --single-transaction -c hsa_cfc_local_partition_prd > work1.sql
 
mysqldump -uroot -pDigitalgd\@database00700_service_root -h173.16.70.27 -P3306 --master-data=2 --single-transaction -c hsa_cfc_jcsj_local_partition > work2.sql

拷贝的数据导入从库

mysql -uroot -p '******' -e  "create database 库名  属性;use hsa_cfc_local_partition_prd;source work1.sql;
 
mysql -uroot -p '******' -e  "create database 库名  属性;use hsa_cfc_jcsj_local_partition;source work2.sql;

更改从库的表索引

ALTER TABLE table_name
DROP PRIMARY KEY,
ADD PRIMARY KEY clume_name USING BTREE;

查看主库当前pos值和filename

show master status;

创建主从同步

CHANGE MASTER TO MASTER_HOST='master1', MASTER_USER='rpl', MASTER_PORT='3306' MASTER_PASSWORD='' \ MASTER_LOG_FILE='filename', MASTER_LOG_POS=pos值 FOR CHANNEL 'master-1';
 
CHANGE MASTER TO MASTER_HOST='master1', MASTER_USER='rpl', MASTER_PORT='3306' MASTER_PASSWORD='' \ MASTER_LOG_FILE='filename', MASTER_LOG_POS=pos值 FOR CHANNEL 'master-2';

启动主从复制（以下命令，网上找到的，亲测无效，不知道是格式问题还是啥）

启动所有复制源
START SLAVE thread_types;
启动单个复制源
START SLAVE thread_types FOR CHANNEL master-1;
停止复制源
STOP SLAVE thread_types;
STOP SLAVE thread_types FOR CHANNEL channel;
重置复制源
RESET SLAVE;
RESET SLAVE FOR CHANNEL channel;

使用老命令启动主从

start slave for channel 'master-1';
start slave;
show slave status\G
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.112.23
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 1471
               Relay_Log_File: localhost-relay-bin-master1.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: test1.%,test2.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1471
              Relay_Log_Space: 539
              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: 12
                  Master_UUID: 714bf301-421b-11eb-b584-000c29350664
             Master_Info_File: mysql.slave_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: master1
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.112.24
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 1830
               Relay_Log_File: localhost-relay-bin-master2.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: test1.%,test2.%
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1830
              Relay_Log_Space: 539
              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: 11
                  Master_UUID: 00cf8f80-421b-11eb-a3ca-000c2975180e
             Master_Info_File: mysql.slave_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: master2
           Master_TLS_Version: 
2 rows in set (0.00 sec)

查看多源状态

mysql> use performance_schema;
mysql> SELECT * FROM replication_connection_status\G;