MariaDB 主从复制

一、环境准备

1）CentOS Linux release 7.4.1708 (Core)

2）YUM安装 Mariadb

3）两台虚拟机，master：192.168.231.132  slave：192.168.231.135

二、安装部署

1）Master 主机配置：

首先，登录到主服务器：

[root@master ~]# rpm -qa |grep mariadb
mariadb-server-5.5.56-2.el7.x86_64
mariadb-5.5.56-2.el7.x86_64
mariadb-devel-5.5.56-2.el7.x86_64
mariadb-libs-5.5.56-2.el7.x86_64
[root@master ~]# mysql -uroot -p
Enter password: 
---------------------------------------
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| release_system     |
+--------------------+
4 rows in set (0.00 sec)   #此时看到主服务器里有一个release_system库，现在退出mariadb 

[root@master ~]# vim /etc/my.cnf　　　　　　#增加以下红色字段　　　　
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
log-bin=mysql-bin     　　　　#将mysql二进制日志取名为mysql-bin
binlog_format=mixed　　　　　 #二进制日志的格式，有三种：statement/row/mixed,具体分别不多做解释，这里使用mixed
server-id=1　　　　　　　　　　 #为服务器设置一个独一无二的id便于区分

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

重启 mariadb 服务:
[root@master ~]# systemctl restart mariadb

进到 mariadb 界面：
MariaDB [(none)]> GRANT replication slave ON *.* TO 'biezz'@'%' IDENTIFIED BY 'biezz';
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status;　　　　　　　　　　#查看主服务器BIN日志的信息
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      462 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

 2）slave 主机配置

登录到从服务器：
[root@slave ~]# rpm -qa |grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
此时，我们发现从服务器上面没有按照 mariadb，下面我们YUM安装过一遍

[root@slave ~]# yum install -y mariadb-server mariadb
************************************************************** 下载安装的过程，我们省略
[root@slave ~]# rpm -qa |grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
mariadb-5.5.56-2.el7.x86_64
mariadb-server-5.5.56-2.el7.x86_64
[root@slave ~]# systemctl start mariadb
[root@slave ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@slave ~]# mysql_secure_installation
设置root密码，一路回车即可，然后进入 mariadb 界面：

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST = "192.168.231.132",
    ->  MASTER_USER = "biezz",
    ->  MASTER_PASSWORD = "biezz",
    -> MASTER_LOG_FILE = "mysql-bin.000001",
    -> MASTER_LOG_POS = 462;
Query OK, 0 rows affected (0.02 sec)

[root@slave ~]# vim /etc/my.cnf　　　　#同上，这一步可以一进来从服务器的时候就配置，我忘记了。
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
log-bin=mysql-bin
binlog_format=mixed
server-id=2

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d


此时我们，启动看看（正常情况下，这里应该启动成功的）：
MariaDB [(none)]> start slave;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO
MariaDB [(none)]> 
MariaDB [(none)]> SHOW VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 0     |
+---------------+-------+
1 row in set (0.00 sec)
我明明在配置文件里配置的server_id =2 ....
MariaDB [(none)]> SET GLOBAL server_id=2;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;　　　　　　　　　　　　#nice,nice,nice
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show slave status\G;
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

 3）测试

[root@master ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database form;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| form               |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

MariaDB [(none)]> Ctrl-C -- exit!
Aborted
[root@slave ~]# 

此时，我们看到主服务器上面创建了一个form数据库，在从服务器上面已经查到了。