MariaDB 10.3.8 主从同步

运行环境：CentOS7.4+MariaDB10.3.8 单主机多实例

单主机多实例演示主从同步：
实例node4作为主库，node5为从库。

规划：

实例名  角色   端口  server-id  数据目录
node4  master 4000  40        /data/mysql/node4
node5  slave  5000  50        /data/mysql/node5

node4实例的配置：

# cat /data/mysql/node4.cnf 
[mysqld]
user                            = mysql
datadir                         = /data/mysql/node4
port                            = 4000
server-id                       = 40
pid-file                        = /tmp/mysql_node4.pid
socket                          = /tmp/mysql_node4.sock
default-storage-engine         =InnoDB
character-set-client-handshake = FALSE
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
init_connect                   ='SET NAMES utf8mb4'
performance-schema-instrument='memory/%=COUNTED'
interactive_timeout            = 172800
wait_timeout                   = 172800
lower_case_table_names        = 1
sql-mode                       =ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sysdate-is-now                 = 1
#setting for Memory
log-error                      = node4_error.log
slow-query-log                 = 1
slow-query-log-file            = node4_slow.log
expire-logs-days               =1
relay_log_recovery             = 1
log-bin                       = node4_bin.log
relay-log                     = node4_relay.log
#setting memory:PGA
read_buffer_size              = 512K
read_rnd_buffer_size          = 256K
sort_buffer_size              = 2M
thread_stack                  = 256K
join_buffer_size              = 2M
binlog_cache_size             = 2M
max-connections                = 4000
innodb_log_buffer_size         =32M
innodb-buffer-pool-size        = 1G
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
query-cache-type               = 0
query-cache-size               = 0
event_scheduler    =on
userstat           =on
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 10
bulk_insert_buffer_size       = 64M
tmp-table-size                 = 8M

node5实例的配置：

# cat /data/mysql/node5.cnf  
[mysqld]
user                            = mysql
datadir                         = /data/mysql/node5
port                            = 5000
server-id                       = 50
pid-file                        = /tmp/mysql_node5.pid
socket                          = /tmp/mysql_node5.sock
default-storage-engine         =InnoDB
character-set-client-handshake = FALSE
character-set-server           = utf8mb4
collation-server               = utf8mb4_unicode_ci
init_connect                   ='SET NAMES utf8mb4'
performance-schema-instrument='memory/%=COUNTED'
interactive_timeout            = 172800
wait_timeout                   = 172800
lower_case_table_names        = 1
sql-mode                       =ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sysdate-is-now                 = 1
#setting for Memory
log-error                      = node5_error.log
slow-query-log                 = 1
slow-query-log-file            = node5_slow.log
expire-logs-days               =1
relay_log_recovery             = 1
log-bin                       = node5_bin.log
relay-log                     = node5_relay.log
#setting memory:PGA
read_buffer_size              = 512K
read_rnd_buffer_size          = 256K
sort_buffer_size              = 2M
thread_stack                  = 256K
join_buffer_size              = 2M
binlog_cache_size             = 2M
max-connections                = 4000
innodb_log_buffer_size         =32M
innodb-buffer-pool-size        = 1G
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 256M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
query-cache-type               = 0
query-cache-size               = 0
event_scheduler    =on
userstat           =on
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 10
bulk_insert_buffer_size       = 64M
tmp-table-size                 = 8M

启动2个实例node4和node5：

--初始化：
#/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/node4 --defaults-file=/data/mysql/node4.cnf

#/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/node5 --defaults-file=/data/mysql/node5.cnf

--启动：
# /usr/local/mysql/bin/mysqld_safe   --defaults-file=/data/mysql/node4.cnf &
# /usr/local/mysql/bin/mysqld_safe   --defaults-file=/data/mysql/node5.cnf &

登录2个实例创建复制的专用账号和权限：

alter user root@'localhost' identified by 'oracle';
grant all privileges on *.* to root@'%' identified by 'oracle' with grant option;
grant REPLICATION SLAVE, REPLICATION CLIENT on *.* to repl@'%' identified by 'repl';
grant REPLICATION SLAVE, REPLICATION CLIENT on *.* to repl@'localhost' identified by 'repl';
grant REPLICATION SLAVE, REPLICATION CLIENT on *.* to repl@'node4' identified by 'repl';
flush privileges;
注释：node4是主机名

查看master库的binlog位置信息：

MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| node4_bin.000001 |      328 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.000 sec)

在从库设置：

获取帮助信息：
MariaDB [(none)]> help change master to

--实际操作：
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='172.16.1.92', MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_PORT=4000,MASTER_LOG_FILE='node4_bin.000001', MASTER_LOG_POS=328;
Query OK, 0 rows affected (0.182 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 172.16.1.92
                   Master_User: repl
                   Master_Port: 4000
                 Connect_Retry: 60
               Master_Log_File: node4_bin.000001
           Read_Master_Log_Pos: 990
                Relay_Log_File: node5_relay.000002
                 Relay_Log_Pos: 1217
         Relay_Master_Log_File: node4_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: 
   Replicate_Wild_Ignore_Table: 
                    Last_Errno: 0
                    Last_Error: 
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 990
               Relay_Log_Space: 1522
               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: 40
                Master_SSL_Crl: 
            Master_SSL_Crlpath: 
                    Using_Gtid: No
                   Gtid_IO_Pos: 
       Replicate_Do_Domain_Ids: 
   Replicate_Ignore_Domain_Ids: 
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 4
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

重点关注：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

 Seconds_Behind_Master: 0

--验证：主库创建表 验证从库是否同步数据和表定义：

主库操作：
MariaDB [(none)]> create database wuhan;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> create table wuhan.city(cityid int not null auto_increment primary key,cityname varchar(20));
Query OK, 0 rows affected (0.019 sec)

MariaDB [(none)]> insert into wuhan.city(cityname)values('wuhan!');
Query OK, 1 row affected (0.002 sec)

MariaDB [(none)]> select * from wuhan.city;
+--------+----------+
| cityid | cityname |
+--------+----------+
|      1 | wuhan!   |
+--------+----------+
1 row in set (0.000 sec)

--从库验证：
MariaDB [(none)]> select * from wuhan.city;
+--------+----------+
| cityid | cityname |
+--------+----------+
|      1 | wuhan!   |
+--------+----------+
1 row in set (0.001 sec)