加入收藏 | 设为首页 | 会员中心 | 我要投稿 应用网_丽江站长网 （http://www.0888zz.com/）- 科技、建站、数据工具、云上网络、机器学习!
当前位置： 首页 > 站长学院 > MySql教程 > 正文

Mariadb多主一从的创建

发布时间：2022-06-27 17:48:07 所属栏目：MySql教程 来源：互联网
导读：目前MySQL依然只支持一个Slave从一个Master复制数据，虽然也可以做到一主多备(M-S)，双主复制(M-M)等架构，但是局限性依然很大。由于项目的要求，需要各个主库的表整合到一个地方进行统计和分析，要是每次连不同的实例操作，是一件非常耗体力的操作。所以
      目前MySQL依然只支持一个Slave从一个Master复制数据，虽然也可以做到一主多备(M->S)，双主复制(M<->M)等架构，但是局限性依然很大。由于项目的要求，需要各个主库的表整合到一个地方进行统计和分析，要是每次连不同的实例操作，是一件非常耗体力的操作。所以继续一种类似多主一从的实例。
 
        安装：
 
sudo apt-get install software-properties-common
 
sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
 
sudo add-apt-repository 'deb http://mirrors.hustunique.com/mariadb/repo/10.0/ubuntu trusty main'
 
sudo apt-get update
 
sudo apt-get install mariadb-server
 
       环境搭建：
 
Master 1：200.51(MySQL)
 
Master 2：200.52(MySQL)
 
Slave   ：200.73(MariaDB) 修改好server-id
 
         确认好Master的POS：
 
M1:
 
rep@192.168.200.51 : (none) 10:26:11>show master status;
 
+--------------------+----------+--------------+------------------+
 
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 
+--------------------+----------+--------------+------------------+
 
| mysql-bin51.000013 |      107 |              | test             |
 
+--------------------+----------+--------------+------------------+
 
1 row in set (0.01 sec)
 
M2：
 
rep@192.168.200.52 : r2 10:26:23>show master status;                                                                                   +---------------------+----------+--------------+------------------+
 
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 
+---------------------+----------+--------------+------------------+
 
| mysql-bin_52.000106 |      107 |              | test             |
 
+---------------------+----------+--------------+------------------+
 
1 row in set (0.00 sec)
 
Slave 操作：
 
MariaDB [(none)]> change master 'r1' to master_host='192.168.200.51',master_user='rep',master_password='rep123456',master_log_file='mysql-bin51.000013',master_log_pos=107;
 
Query OK, 0 rows affected (0.23 sec)
 
MariaDB [(none)]> change master 'r2' to master_host='192.168.200.52',master_user='rep',master_password='rep123456',master_log_file='mysql-bin_52.000106',master_log_pos=107;
 
Query OK, 0 rows affected (0.25 sec)
 
MariaDB的change方法和MySQL有点不一样，多了一个 ['connection_name'] ，这个就是多主一从的关键。为每个主设置一个通道标识，这样就可以支持多主复制了。
 
如何保存复制的信息？单主复制会把复制信息保存在master.info中，在多主复制中的保存也类似，只是在最后加上通道标识名称。如：
 
-rw-rw---- 1 mysql mysql  113 11月 17 10:30 master-r1.info
 
-rw-rw---- 1 mysql mysql  114 11月 17 10:31 master-r2.info
 
-rw-rw---- 1 mysql mysql  248 11月 17 10:30 mysqld-relay-bin-r1.000001
 
-rw-rw---- 1 mysql mysql   29 11月 17 10:30 mysqld-relay-bin-r1.index
 
-rw-rw---- 1 mysql mysql  248 11月 17 10:31 mysqld-relay-bin-r2.000001
 
-rw-rw---- 1 mysql mysql   29 11月 17 10:31 mysqld-relay-bin-r2.index
 
-rw-rw---- 1 mysql mysql   54 11月 17 10:30 relay-log-r1.info
 
-rw-rw---- 1 mysql mysql   55 11月 17 10:31 relay-log-r2.info
 
查看同步：
 
#查看所有通道
MariaDB [(none)]> show all slaves statusG;
 
*************************** 1. row ***************************
 
              Connection_name: r1
 
              Slave_SQL_State:
 
               Slave_IO_State:
 
                  Master_Host: 192.168.200.51
 
                  Master_User: rep
 
                  Master_Port: 3306
 
                Connect_Retry: 60
 
              Master_Log_File: mysql-bin51.000013
 
          Read_Master_Log_Pos: 107
 
               Relay_Log_File: mysqld-relay-bin-r1.000001
 
                Relay_Log_Pos: 4
 
        Relay_Master_Log_File: mysql-bin51.000013
 
             Slave_IO_Running: No
 
            Slave_SQL_Running: No
 
              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: 107
 
              Relay_Log_Space: 248
 
              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: NULL
 
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: 0
 
               Master_SSL_Crl:
 
           Master_SSL_Crlpath:
 
                   Using_Gtid: No
 
                  Gtid_IO_Pos:
 
         Retried_transactions: 0
 
           Max_relay_log_size: 104857600
 
         Executed_log_entries: 0
 
    Slave_received_heartbeats: 0
 
       Slave_heartbeat_period: 1800.000
 
               Gtid_Slave_Pos:
 
*************************** 2. row ***************************
 
              Connection_name: r2
 
              Slave_SQL_State:
 
               Slave_IO_State:
 
                  Master_Host: 192.168.200.52
 
                  Master_User: rep
 
                  Master_Port: 3306
 
                Connect_Retry: 60
 
              Master_Log_File: mysql-bin_52.000106
 
          Read_Master_Log_Pos: 107
 
               Relay_Log_File: mysqld-relay-bin-r2.000001
 
                Relay_Log_Pos: 4
 
        Relay_Master_Log_File: mysql-bin_52.000106
 
             Slave_IO_Running: No
 
            Slave_SQL_Running: No
 
              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: 107
 
              Relay_Log_Space: 248
 
              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: NULL
 
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: 0
 
               Master_SSL_Crl:
 
           Master_SSL_Crlpath:
 
                   Using_Gtid: No
 
                  Gtid_IO_Pos:
 
         Retried_transactions: 0
 
           Max_relay_log_size: 104857600
 
         Executed_log_entries: 0
 
    Slave_received_heartbeats: 0
 
       Slave_heartbeat_period: 1800.000
 
               Gtid_Slave_Pos:
 
2 rows in set (0.00 sec)
 
ERROR: No query specified
 
 
#查看单个通道
 
MariaDB [(none)]> show slave 'r1' statusG;
 
*************************** 1. row ***************************
 
               Slave_IO_State:
 
                  Master_Host: 192.168.200.51
 
                  Master_User: rep
 
                  Master_Port: 3306
 
                Connect_Retry: 60
 
              Master_Log_File: mysql-bin51.000013
 
          Read_Master_Log_Pos: 107
 
               Relay_Log_File: mysqld-relay-bin-r1.000001
 
                Relay_Log_Pos: 4
 
        Relay_Master_Log_File: mysql-bin51.000013
 
             Slave_IO_Running: No
 
            Slave_SQL_Running: No
 
              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: 107
 
              Relay_Log_Space: 248
 
              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: NULL
 
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: 0
 
               Master_SSL_Crl:
 
           Master_SSL_Crlpath:
 
                   Using_Gtid: No
 
                  Gtid_IO_Pos:
 
1 row in set (0.00 sec)
 
ERROR: No query specified
 
MariaDB [(none)]> show slave 'r2' statusG;
 
*************************** 1. row ***************************
 
               Slave_IO_State:
 
                  Master_Host: 192.168.200.52
 
                  Master_User: rep
 
                  Master_Port: 3306
 
                Connect_Retry: 60
 
              Master_Log_File: mysql-bin_52.000106
 
          Read_Master_Log_Pos: 107
 
               Relay_Log_File: mysqld-relay-bin-r2.000001
 
                Relay_Log_Pos: 4
 
        Relay_Master_Log_File: mysql-bin_52.000106
 
             Slave_IO_Running: No
 
            Slave_SQL_Running: No
 
              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: 107
 
              Relay_Log_Space: 248
 
              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: NULL
 
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: 0
 
               Master_SSL_Crl:
 
           Master_SSL_Crlpath:
 
                   Using_Gtid: No
 
                  Gtid_IO_Pos:
 
1 row in set (0.00 sec)
 
ERROR: No query specified
 
开启同步：
 
#开启单个通道
MariaDB [(none)]> start slave 'r1';
 
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> start slave 'r2';
 
Query OK, 0 rows affected (0.00 sec)
 
#关闭多个通道
 
MariaDB [(none)]> start all slaves;
 
Query OK, 0 rows affected, 2 warnings (0.01 sec)
 
MariaDB [(none)]> show warnings;
 
+-------+------+--------------------+
 
| Level | Code | Message            |
 
+-------+------+--------------------+
 
| Note  | 1937 | SLAVE 'r2' started |
 
| Note  | 1937 | SLAVE 'r1' started |
 
+-------+------+--------------------+
 
2 rows in set (0.00 sec)
 
通过 show all slaves status 命令可知是否同步成功。
 
关闭同步：
 
#关闭单个通道
MariaDB [(none)]> stop slave 'r1';
 
Query OK, 0 rows affected (0.14 sec)
 
MariaDB [(none)]> stop slave 'r2';
 
Query OK, 0 rows affected (0.03 sec)
 
#关闭所有通道
 
MariaDB [(none)]> stop all slaves;
 
Query OK, 0 rows affected, 2 warnings (0.08 sec)
 
MariaDB [(none)]> show warnings;
 
+-------+------+--------------------+
 
| Level | Code | Message            |
 
+-------+------+--------------------+
 
| Note  | 1938 | SLAVE 'r2' stopped |
 
| Note  | 1938 | SLAVE 'r1' stopped |
 
+-------+------+--------------------+
 
2 rows in set (0.00 sec)
 
多源复制在原先复制的基础上多了几个变量，现在来说明下：
 
MariaDB [(none)]> show all slaves statusG;
 
*************************** 1. row ***************************
 
              Connection_name: r1                #master的连接名，通道名，第一个参数。
 
              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.200.51
 
                  Master_User: rep
 
                  Master_Port: 3306
 
                Connect_Retry: 60
 
              Master_Log_File: mysql-bin51.000013
 
          Read_Master_Log_Pos: 107
 
               Relay_Log_File: mysqld-relay-bin-r1.000005
 
                Relay_Log_Pos: 396
 
        Relay_Master_Log_File: mysql-bin51.000013
 
             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: 107
 
              Relay_Log_Space: 845
 
              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: 104857600  #relay log的最大值. 如果是0的话，那么在启动的时候就会被设置成max_binlog_size 的大小
 
         Executed_log_entries: 17         #slave已经指向了多少个日志条目
 
    Slave_received_heartbeats: 0          #我们从master收到了多少个心跳包
 
       Slave_heartbeat_period: 1800.000   #多久从master请求一个心跳包 (以秒计算)
 
               Gtid_Slave_Pos:
 
 测试复制
 
Master 1:
 
rep@192.168.200.51 : (none) 01:52:34>show databases;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| information_schema |
 
| mha_test           |
 
| mysql              |
 
| performance_schema |
 
| xtra_test          |
 
+--------------------+
 
5 rows in set (0.00 sec)
 
rep@192.168.200.51 : (none) 01:52:37>create database r1 default charset utf8;
 
Query OK, 1 row affected (0.01 sec)
 
rep@192.168.200.51 : (none) 01:53:36>use r1;
 
Database changed
 
rep@192.168.200.51 : r1 01:53:44>create table r1(id int not null auto_increment primary key,name varchar(30))default charset utf8;
 
Query OK, 0 rows affected (1.35 sec)
 
rep@192.168.200.51 : r1 01:54:09>insert into r1(name) values('a'),('b'),('c');
 
Query OK, 3 rows affected (0.01 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
rep@192.168.200.51 : r1 01:54:56>select * from r1;
 
+----+------+
 
| id | name |
 
+----+------+
 
|  1 | a    |
 
|  2 | b    |
 
|  3 | c    |
 
+----+------+
 
3 rows in set (0.00 sec)
 
Master 2:
 
rep@192.168.200.52 : (none) 01:52:13>create database r2 default charset utf8;
 
Query OK, 1 row affected (0.01 sec)
 
rep@192.168.200.52 : (none) 01:54:27>use r2
 
Database changed
 
rep@192.168.200.52 : r2 01:54:30>create table r2(id int not null auto_increment primary key,name varchar(30))default charset utf8;
 
Query OK, 0 rows affected (0.23 sec)
 
rep@192.168.200.52 : r2 01:54:32>insert into r2(name) values('A'),('B'),('C');
 
Query OK, 3 rows affected (0.28 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
rep@192.168.200.52 : r2 01:55:18>select * from r2;
 
+----+------+
 
| id | name |
 
+----+------+
 
|  1 | A    |
 
|  2 | B    |
 
|  3 | C    |
 
+----+------+
 
3 rows in set (0.01 sec)
 
Slave:
 
MariaDB [(none)]> show databases;
 
+--------------------+
 
| Database           |
 
+--------------------+
 
| information_schema |
 
| mysql              |
 
| performance_schema |
 
| r1                 |
 
| r2                 |
 
+--------------------+
 
5 rows in set (0.00 sec)
 
MariaDB [(none)]> use r1;
 
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
 
MariaDB [r1]> select * from r1;
 
+----+------+
 
| id | name |
 
+----+------+
 
|  1 | a    |
 
|  2 | b    |
 
|  3 | c    |
 
+----+------+
 
3 rows in set (0.00 sec)
 
MariaDB [r1]> use r2;
 
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
 
MariaDB [r2]> select * from r2;
 
+----+------+
 
| id | name |
 
+----+------+
 
|  1 | A    |
 
|  2 | B    |
 
|  3 | C    |
 
+----+------+
 
3 rows in set (0.00 sec)
 
同步成功，那如何错误跳过呢（default_master_connection）？
 
在Master 1上创建r2数据库，因为Slave上存在，所以会报错：
 
Master 1：
 
rep@192.168.200.51 : r1 01:55:52>create database r2 default charset utf8;
 
Query OK, 1 row affected (0.01 sec)
 
rep@192.168.200.51 : r1 01:59:51>insert into r1(name) values('d'),('e'),('f');
 
Query OK, 3 rows affected (0.01 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
rep@192.168.200.51 : r1 02:04:22>select * from r1;
 
+----+------+
 
| id | name |
 
+----+------+
 
|  1 | a    |
 
|  2 | b    |
 
|  3 | c    |
 
|  4 | d    |
 
|  5 | e    |
 
|  6 | f    |
 
+----+------+
 
6 rows in set (0.00 sec)
 
Slave ：
 
MariaDB [r2]> show slave 'r1' statusG;
 
*************************** 1. row ***************************
 
               Slave_IO_State: Waiting for master to send event
 
                  Master_Host: 192.168.200.51
 
                  Master_User: rep
 
                  Master_Port: 3306
 
                Connect_Retry: 60
 
              Master_Log_File: mysql-bin51.000013
 
          Read_Master_Log_Pos: 767
 
               Relay_Log_File: mysqld-relay-bin-r1.000005
 
                Relay_Log_Pos: 956
 
        Relay_Master_Log_File: mysql-bin51.000013
 
             Slave_IO_Running: Yes
 
            Slave_SQL_Running: No
 
              Replicate_Do_DB:
 
          Replicate_Ignore_DB:
 
           Replicate_Do_Table:
 
       Replicate_Ignore_Table:
 
      Replicate_Wild_Do_Table:
 
  Replicate_Wild_Ignore_Table:
 
                   Last_Errno: 1007
 
                   Last_Error: Error 'Can't create database 'r2'; database exists' on query. Default database: 'r2'. Query: 'create database r2 default charset utf8'
 
                 Skip_Counter: 0
 
          Exec_Master_Log_Pos: 667
 
              Relay_Log_Space: 1505
 
              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: NULL
 
Master_SSL_Verify_Server_Cert: No
 
                Last_IO_Errno: 0
 
                Last_IO_Error:
 
               Last_SQL_Errno: 1007
 
               Last_SQL_Error: Error 'Can't create database 'r2'; database exists' on query. Default database: 'r2'. Query: 'create database r2 default charset utf8'
 
  Replicate_Ignore_Server_Ids:
 
             Master_Server_Id: 1
 
               Master_SSL_Crl:
 
           Master_SSL_Crlpath:
 
                   Using_Gtid: No
 
                  Gtid_IO_Pos:
 
1 row in set (0.00 sec)
 
MariaDB [r1]> select * from r1;
 
+----+------+
 
| id | name |
 
+----+------+
 
|  1 | a    |
 
|  2 | b    |
 
|  3 | c    |
 
+----+------+
 
3 rows in set (0.00 sec)
 
r1的同步失败了，那r2可以继续同步吗？
 
Master 2:
 
rep@192.168.200.52 : r2 01:55:59>insert into r2(name) values('D'),('E'),('F');
 
Query OK, 3 rows affected (0.01 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
rep@192.168.200.52 : r2 02:02:19>select * from r2;
 
+----+------+
 
| id | name |
 
+----+------+
 
|  1 | A    |
 
|  2 | B    |
 
|  3 | C    |
 
|  4 | D    |
 
|  5 | E    |
 
|  6 | F    |
 
+----+------+
 
6 rows in set (0.01 sec)
 
Slave：
 
MariaDB [r2]> select * from r2;
 
+----+------+
 
| id | name |
 
+----+------+
 
|  1 | A    |
 
|  2 | B    |
 
|  3 | C    |
 
|  4 | D    |
 
|  5 | E    |
 
|  6 | F    |
 
+----+------+
 
6 rows in set (0.00 sec)
 
上面可以得出：r1同步失败之后，不影响r2的同步。想要r1同步正常，则需要忽略即跳过该错误。如：
 
MariaDB [r1]> stop slave 'r1';
 
Query OK, 0 rows affected (0.12 sec)
 
MariaDB [r1]> set @@default_master_connection='r1';  #这里是重点：指定一个通道，然后用单通道的sql_slave_skip_counter。
 
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [r1]> select @@default_master_connection;
 
+-----------------------------+
 
| @@default_master_connection |
 
+-----------------------------+
 
| r1                          |
 
+-----------------------------+
 
1 row in set (0.00 sec)
 
MariaDB [r1]> SET GLOBAL sql_slave_skip_counter =1;
 
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [r1]> start slave 'r1';
 
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [r1]> show slave 'r1' statusG;
 
*************************** 1. row ***************************
 
               Slave_IO_State: Waiting for master to send event
 
                  Master_Host: 192.168.200.51
 
                  Master_User: rep
 
                  Master_Port: 3306
 
                Connect_Retry: 60
 
              Master_Log_File: mysql-bin51.000013
 
          Read_Master_Log_Pos: 993
 
               Relay_Log_File: mysqld-relay-bin-r1.000006
 
                Relay_Log_Pos: 396
 
        Relay_Master_Log_File: mysql-bin51.000013
 
             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: 993
 
              Relay_Log_Space: 1731
 
              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:
 
1 row in set (0.00 sec)
 
MariaDB [r1]> select * from r1;
 
+----+------+
 
| id | name |
 
+----+------+
 
|  1 | a    |
 
|  2 | b    |
 
|  3 | c    |
 
|  4 | d    |
 
|  5 | e    |
 
|  6 | f    |
 
+----+------+
 
6 rows in set (0.00 sec)
 
看到跳过/忽略错误之后，r1的复制就正常了。
 
从上面的测试上说明，在用多主一从的复制时，需要保证各个主的Master Schema 要唯一，不能有重复。
 
最后再看看如何初始化：
 
MariaDB [r1]> show all slaves statusG;
 
*************************** 1. row ***************************
 
              Connection_name: r1
 
              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.200.51
 
                  Master_User: rep
 
                  Master_Port: 3306
 
                Connect_Retry: 60
 
              Master_Log_File: mysql-bin51.000013
 
          Read_Master_Log_Pos: 1376
 
               Relay_Log_File: mysqld-relay-bin-r1.000006
 
                Relay_Log_Pos: 779
 
        Relay_Master_Log_File: mysql-bin51.000013
 
             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: 1376
 
              Relay_Log_Space: 2114
 
              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: 104857600
 
         Executed_log_entries: 39
 
    Slave_received_heartbeats: 4
 
       Slave_heartbeat_period: 1800.000
 
               Gtid_Slave_Pos:
 
*************************** 2. row ***************************
 
              Connection_name: r2
 
              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.200.52
 
                  Master_User: rep
 
                  Master_Port: 3306
 
                Connect_Retry: 60
 
              Master_Log_File: mysql-bin_52.000106
 
          Read_Master_Log_Pos: 893
 
               Relay_Log_File: mysqld-relay-bin-r2.000005
 
                Relay_Log_Pos: 1183
 
        Relay_Master_Log_File: mysql-bin_52.000106
 
             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: 893
 
              Relay_Log_Space: 1633
 
              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: 2
 
               Master_SSL_Crl:
 
           Master_SSL_Crlpath:
 
                   Using_Gtid: No
 
                  Gtid_IO_Pos:
 
         Retried_transactions: 0
 
           Max_relay_log_size: 104857600
 
         Executed_log_entries: 28
 
    Slave_received_heartbeats: 4
 
       Slave_heartbeat_period: 1800.000
 
               Gtid_Slave_Pos:
 
2 rows in set (0.00 sec)
 
#单个通道初始化
 
MariaDB [r1]> reset slave 'r1' all;
 
ERROR 1198 (HY000): This operation cannot be performed as you have a running slave 'r1'; run STOP SLAVE 'r1' first
 
MariaDB [r1]> stop slave 'r1';
 
Query OK, 0 rows affected (0.03 sec)
 
MariaDB [r1]> reset slave 'r1' all;
 
Query OK, 0 rows affected (0.04 sec)
 
MariaDB [r1]> stop slave 'r2';
 
Query OK, 0 rows affected (0.02 sec)
 
MariaDB [r1]> reset slave 'r2' all;
 
Query OK, 0 rows affected (0.02 sec)
 
MariaDB [r1]> show all slaves statusG;
 
Empty set (0.00 sec)
 
#所有通道初始化
 
MariaDB [r1]> stop all slaves;
 
Query OK, 0 rows affected, 2 warnings (0.05 sec)
 
MariaDB [r1]> show warnings;
 
+-------+------+--------------------+
 
| Level | Code | Message            |
 
+-------+------+--------------------+
 
| Note  | 1938 | SLAVE 'r2' stopped |
 
| Note  | 1938 | SLAVE 'r1' stopped |
 
+-------+------+--------------------+
 
2 rows in set (0.00 sec)
 
MariaDB [r1]> reset slave all;   #执行时候发现只能让r1初始化，不能初始化r2。所以初始化还是要单通道执行。
 
总结：
 
      经过上面的测试，实现了多个主实例的数据同步到一个从实例，这个就可以把集中做分析的数据表同步到一起进行分析处理，大大减少了数据的中间处理时间和安全，这里还有一点特别注意的是，在同步数据库的时候可以用过滤选项（Replicate_Do_Table、Replicate_Ignore_Table、Replicate_Wild_Do_Table、Replicate_Wild_Ignore_Table），看着需要同步自己需要的表，不需要把没必要的也同步过来。

（编辑：应用网_丽江站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
      站长推荐
      热点阅读

        【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

        建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的优质浏览效果

        Copygight © 2013-2023 http://www.0888zz.com/ All Rights Reserved. 应用网_丽江站长网

        ICP备案：浙ICP备12044117号 浙公网安备 33038102330468号