環(huán)境：
centos7
MariaDB10.2
一、關(guān)閉防火墻
查看防火墻狀態(tài)：systemctl status firewalld
停止防火墻：systemctl stop firewalld
設(shè)置開機不啟用防火墻：systemctl disable firewalld
二、主數(shù)據(jù)庫配置
MariaDB10.2安裝后其配置文件為/etc/my.cnf，內(nèi)容如下：
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
通過!includedir /etc/my.cnf.d這一句可以看出，其具體的配置文件在目錄/etc/my.cnf.d下。該目錄下的文件如下：
[root@localhost etc]# clear
[root@localhost etc]# cd /etc/my.cnf.d/
[root@localhost my.cnf.d]# ll
total 12
-rw-r--r--. 1 root root 763 Jul 27 05:26 enable_encryption.preset
-rw-r--r--. 1 root root 232 Jul 27 05:26 mysql-clients.cnf
-rw-r--r--. 1 root root 1080 Jul 27 05:26 server.cnf
[root@localhost my.cnf.d]#
我們需要修改server.cnf文件。主要開啟bin-log和指定server-id，添加的內(nèi)容如下：
# this is only for the mysqld standalone daemon
[mysqld]
# 忽略表名大小寫
lower_case_table_names=1
# 開啟bin-log
log-bin=mysql-bin
# 指定serverId
server-id=1
三、主服務(wù)器上創(chuàng)建mysql用戶，以便從服務(wù)器使用
在mysql的命令行下執(zhí)行以下命令：
語法格式：
GRANT REPLICATION SLAVE ON .{所有權(quán)限} TO 'slave'@'%'{用戶名為slave，%為任意地址} identified by 'slave';
[root@localhost my.cnf.d]# clear
[root@localhost my.cnf.d]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.26-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
四、重啟主服務(wù)器，然后查看主服務(wù)器的狀態(tài)
重啟mariadb后，在mysql命令行下輸入：SHOW MASTER STATUS查看主服務(wù)器的狀態(tài)
[root@localhost my.cnf.d]# systemctl restart mariadb
[root@localhost my.cnf.d]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.2.26-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 328 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [(none)]>
五、從節(jié)點mariadb配置
同主務(wù)器配置一樣，只是server-id值要與主服務(wù)器的不同。
[mysqld]
# 忽略表名大小寫
lower_case_table_names=1
# 開啟bin-log
log-bin=mysql-bin
# 指定serverId
server-id=2
六、從節(jié)點slave配置
在從節(jié)點的mysql命令行執(zhí)行命令：
CHANGE MASTER TO MASTER_HOST='192.168.100.70', MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='slave',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=328;
如下：
[root@localhost my.cnf.d]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.26-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.100.70', MASTER_PORT=3306,MASTER_USER='slave',MASTER_PASSWORD='slave',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=328;
Query OK, 0 rows affected (0.04 sec)
MariaDB [(none)]>
語法：
CHANGE MASTER TO MASTER_HOST='主節(jié)點的IP地址', MASTER_PORT=主節(jié)端口號 MASTER_USER='主節(jié)點授權(quán)的用戶', MASTER_PASSWORD='主節(jié)點授權(quán)的用戶的密碼',MASTER_LOG_FILE='mysql-bin.000007',MASTER_LOG_POS=2197;
PS:注意語法逗號前后不要用空格
MASTER_LOG_FILE及MASTER_LOG_POS為主節(jié)點的bin-log文件名及當前同步的位置，在主節(jié)點可通過SHOW MASTER STATUS查看。
七、查看從節(jié)點狀態(tài)
重啟從節(jié)點的mariadb,然后在mysql命令行下輸入：show slave status\G;來查看從節(jié)點的狀態(tài)。
[root@localhost my.cnf.d]# systemctl restart mariadb
[root@localhost my.cnf.d]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.26-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.70
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 328
Relay_Log_File: localhost-relay-bin.000003
Relay_Log_Pos: 555
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:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 328
Relay_Log_Space: 868
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:
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
1 row in set (0.00 sec)
MariaDB [(none)]>
只要Slave_IO_Running: Yes與 Slave_SQL_Running: Yes這兩項為yes就表示成功。
八、測試
在主數(shù)據(jù)庫下建庫、表、添加數(shù)據(jù)都會自動同步到從庫
九、注意事項：
- 不可以直接從庫寫入數(shù)據(jù)，否則會導(dǎo)致主從數(shù)據(jù)不一致。
- 在配置的過程中應(yīng)關(guān)閉主庫的寫，否則主庫bin-log的id會隨時變動。
