centos7 mariadb主从复制配置搭建详解步骤

注意：结果中Slave_IO_Running和Slave_SQL_Running必须为Yes，如果不是，需要根据提示的错误修改。

验证

主服务器：

MariaDB [(none)]> show databases;
+--------------------+
| Database      |
+--------------------+
| information_schema |
| mysql       |
| mytest       |
| performance_schema |
| test        |
+--------------------+
5 rows in set (0.04 sec)

MariaDB [(none)]> use mytest;
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 [mytest]> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | t  |
| 2 | t2  |
| 3 | t3  |
+----+------+
3 rows in set (0.00 sec)

MariaDB [mytest]> insert into user(name) values('t4');
Query OK, 1 row affected (0.01 sec)

MariaDB [mytest]> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | t  |
| 2 | t2  |
| 3 | t3  |
| 4 | t4  |
+----+------+
4 rows in set (0.00 sec)

查看从服务器数据是否变化：

MariaDB [(none)]> use mytest;
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 [mytest]> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | t  |
| 2 | t2  |
+----+------+
2 rows in set (0.00 sec)

MariaDB [mytest]> select * from user;
+----+------+
| id | name |
+----+------+
| 1 | t  |
| 2 | t2  |
| 4 | t4  |
+----+------+
3 rows in set (0.00 sec)

可以看到，从服务器更新了数据

搭建过程中遇到的问题及解决方法

问题1：从服务器设置主从复制出现错误:

MariaDB [mytest]> start slave;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log

发现 

Slave_IO_Running: No
Slave_SQL_Running: No

进一步发现我输入的是：CHANGE MASTER TO MASTER_HOST='192.168.71.151',MASTER_USER='slave_user', MASTER_PASSWORD='bigs3cret', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 469;

重新输入：MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS= 469;
报错：ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log

于是看错误日志：/var/log/mariadb/mariadb.log

错误日志的位置在/etc/my.cnf中配置：log-error=/

[root@localhost ~]# cat /var/log/mariadb/mariadb.log
160915 12:52:02 [ERROR] Failed to open the relay log './mariadb-relay-bin.000001' (relay_log_pos 4)
160915 12:52:02 [ERROR] Could not find target log during relay log initialization

通过查找答案： 删除/var/lib/mysql/路径下the ‘master.info' ‘mysqld-relay-bin.*' ‘relay-log.info' ‘relay-log-index.*'