MariaDB多源复制数据汇总

MariaDB多源复制数据汇总,如下图所示,在某些场景中,有A和b两个节点数据库,从数据分别读取ab两个节点的数据到一台slave数据库中

主A和主B:

[root@master local]# tar xf mariadb-10.0.10-linux-x86_64.tar.gz 
[root@master local]# ln -sv mariadb-10.0.10-linux-x86_64 mysql
`mysql' -> `mariadb-10.0.10-linux-x86_64'
[root@master local]# cd mysql/
[root@master mysql]# scripts/mysql_install_db --user=mysql --datadir=/mydata/data/
[root@master mysql]# cp support-files/my-large.cnf /etc/mysql/my.cnf

修改server-id,二进制log-bin开启,

datadir = /mydata/data
basedir = /usr/local/mysql
innodb_file_per_table = on
skip_name_resolve = on
log-bin=/mydata/binlogs/master-1
binlog_format=mixed
server-id       = 1

在主A和主B上分别创建复制账号:

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'user'@'192.168.%.%' IDENTIFIED BY 'pass';

从服务器:
注释掉log-bin
添加relay-log

#log-bin=mysql-bin
server-id       = 10
relay-log = relay-bin

在从服务器上分别如下:
从服务器需要开启中继日志,修改server_id号,从服务器仍然不能写入,只能读取

CHANGE MASTER 'm1' TO MASTER_HOST='192.168.0.100',MASTER_USER='user',MASTER_PASSWORD='pass';
CHANGE MASTER 'm2' TO MASTER_HOST='192.168.0.101',MASTER_USER='user',MASTER_PASSWORD='pass';

如下:

MariaDB [(none)]> CHANGE MASTER 'm1' TO MASTER_HOST='192.168.0.100',MASTER_USER='user',MASTER_PASSWORD='pass';
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> CHANGE MASTER 'm2' TO MASTER_HOST='192.168.0.101',MASTER_USER='user',MASTER_PASSWORD='pass';
Query OK, 0 rows affected (0.03 sec)
MariaDB [(none)]> start all slaves;

当OK后,使用一下命令查看!show all slaves status\G

由于是第一次使用空数据,可能需要手动在从服务器上指定,如下所示!
关闭slaves后,在master1和2上分别查看后并且重新change

MariaDB [(none)]> stop all slaves;

master-1

MariaDB [(none)]> show master status;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| master-1.000003 |      325 |              |                  |
+-----------------+----------+--------------+------------------+
MariaDB [(none)]> CHANGE MASTER 'm1' TO MASTER_HOST='192.168.0.100',MASTER_USER='user',MASTER_PASSWORD='pass',MASTER_LOG_FILE='master-1.000003',MASTER_LOG_POS=325;

master-2

MariaDB [(none)]> show master status;
+-----------------+----------+--------------+------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+
| master-2.000001 |      518 |              |                  |
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> CHANGE MASTER 'm2' TO MASTER_HOST='192.168.0.101',MASTER_USER='user',MASTER_PASSWORD='pass',MASTER_LOG_FILE='master-2.000001',MASTER_LOG_POS=518;

开启slaves;

MariaDB [(none)]> start all slaves;

查看

MariaDB [(none)]> show all slaves status\G
*************************** 1. row ***************************
              Connection_name: m1
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.100
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-1.000003
          Read_Master_Log_Pos: 532
               Relay_Log_File: relay-bin-m1.000003
                Relay_Log_Pos: 534
        Relay_Master_Log_File: master-1.000003
             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: 532
              Relay_Log_Space: 1321
              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: 1
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
         Retried_transactions: 0
           Max_relay_log_size: 1073741824
         Executed_log_entries: 12
    Slave_received_heartbeats: 0
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos: 0-1-2141
*************************** 2. row ***************************
              Connection_name: m2
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.101
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-2.000001
          Read_Master_Log_Pos: 518
               Relay_Log_File: relay-bin-m2.000003
                Relay_Log_Pos: 534
        Relay_Master_Log_File: master-2.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: 518
              Relay_Log_Space: 1114
              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: 3
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
                   Using_Gtid: No
                  Gtid_IO_Pos: 
         Retried_transactions: 0
           Max_relay_log_size: 1073741824
         Executed_log_entries: 10
    Slave_received_heartbeats: 0
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos: 0-1-2141
2 rows in set (0.00 sec)

MariaDB [(none)]> 

如果数据不一致,则可以reset slave 刷新试试

MariaDB [(none)]> reset slave 'm1';
Query OK, 0 rows affected (0.00 sec)

 Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> START ALL SLAVES;
Query OK, 0 rows affected, 2 warnings (0.11 sec)

MariaDB [(none)]> 

多源复制,每个源应该使用不同的数据,多源复制目前不支持同步复制;

0 分享

您可以选择一种方式赞助本站

支付宝扫码赞助

支付宝扫码赞助

日期: 2016-02-22分类: Mariadb

标签: mariadb

发表评论