Docker----mariadb多源复制

一、mariadb简介

二、配置主从复制（一主一从）

1、配置文件

master

[mariadb]
log-bin
server_id=1
log-basename=master

slaver

[mariadb]
log-bin
server_id=2
log-basename=slaver

2、docker启动

master

docker run -d -p 3316:3306 --name master \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /home/cyf/docker/db/mariadb/master/config/:/etc/mysql \
mariadb:10.2

slaver

docker run -d -p 3316:3306 --name slaver \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /home/cyf/docker/db/mariadb/slaver/config/:/etc/mysql \
mariadb:10.2

3、创建复制账号并授权

CREATE USER 'repl'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

4、配置主从

查看主库master信息

MariaDB [(none)]> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| master-bin.000001 |      329 |              |                  |
+-------------------+----------+--------------+------------------+

登录从库配置主从

MariaDB [(none)]> CHANGE MASTER TO \
MASTER_HOST='192.168.10.24', \
MASTER_USER='repl', \
MASTER_PASSWORD='123456', \
MASTER_PORT=3316, \
MASTER_LOG_FILE='master-bin.000001', \
MASTER_LOG_POS=329, \
MASTER_CONNECT_RETRY=10;

开启slave复制，并查看从库状态，Slave_IO_Running和Slave_SQL_Running均为Yes即可。

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

MariaDB [(none)]> show slave  status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.24
                  Master_User: root
                  Master_Port: 3316
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 329
               Relay_Log_File: slaver-relay-bin.000002
                Relay_Log_Pos: 556
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
.....

三、配置多源复制（两主一从）

docker启动三个mariadb，注意配置文件server_id不能一样

# master1
docker run -d -p 3316:3306 --name master1 -e MYSQL_ROOT_PASSWORD=123456 -v /home/cyf/docker/db/mariadb/master1/config/:/etc/mysql mariadb:10.2

# master2
docker run -d -p 3326:3306 --name master2 -e MYSQL_ROOT_PASSWORD=123456 -v /home/cyf/docker/db/mariadb/master2/config/:/etc/mysql mariadb:10.2

# slaver
docker run -d -p 3336:3306 --name slaver -e MYSQL_ROOT_PASSWORD=123456 -v /home/cyf/docker/db/mariadb/slaver/config/:/etc/mysql mariadb:10.2

授权主从

MariaDB [(none)]> CHANGE MASTER 'master1' TO MASTER_HOST='192.168.10.24', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_PORT=3316, MASTER_LOG_FILE='master1-bin.000001', MASTER_LOG_POS=637, MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.06 sec)

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

MariaDB [(none)]> show slave 'master1' status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.24
                  Master_User: repl
                  Master_Port: 3316
                Connect_Retry: 10
              Master_Log_File: master1-bin.000001
          Read_Master_Log_Pos: 637
               Relay_Log_File: slaver-relay-bin-master1.000002
                Relay_Log_Pos: 557
        Relay_Master_Log_File: master1-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
.....

MariaDB [(none)]> CHANGE MASTER 'master2' TO MASTER_HOST='192.168.10.24', MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_PORT=3326, MASTER_LOG_FILE='master2-bin.000001', MASTER_LOG_POS=637, MASTER_CONNECT_RETRY=10;
Query OK, 0 rows affected (0.06 sec)

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

MariaDB [(none)]> show slave 'master2' status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.24
                  Master_User: repl
                  Master_Port: 3326
                Connect_Retry: 10
              Master_Log_File: master2-bin.000001
          Read_Master_Log_Pos: 637
               Relay_Log_File: slaver-relay-bin-master2.000002
                Relay_Log_Pos: 557
        Relay_Master_Log_File: master2-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
 .....

测试主从

# master1
MariaDB [(none)]> create database test1;
Query OK, 1 row affected (0.00 sec)

# slaver
MariaDB [(none)]>  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test1              |
+--------------------+
4 rows in set (0.00 sec)

# master2
MariaDB [(none)]> create database test2;
Query OK, 1 row affected (0.00 sec)

# slaver
MariaDB [(none)]>  show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test1              |
| test2              |
+--------------------+
5 rows in set (0.00 sec)

设置默认mater

MariaDB [(none)]> show status like 'Slave_running';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slave_running | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> set @@default_master_connection='master1';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show status like 'Slave_running';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slave_running | ON    |
+---------------+-------+
1 row in set (0.00 sec)
MariaDB [(none)]> show variables like 'default_master_connection';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| default_master_connection | master1 |
+---------------------------+---------+
1 row in set (0.00 sec)