centos7 mariadb主從復(fù)制配置搭建詳解步驟

更新時(shí)間：2017年02月08日 08:59:58 作者：姚一號(hào)

本篇文章主要介紹了centos7 mariadb主從復(fù)制配置搭建詳解步驟，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下。

花了小一天的時(shí)間，終于實(shí)現(xiàn)了centos7 mariadb主從復(fù)制配置搭建，下面記錄一下過(guò)程

環(huán)境:

虛擬機(jī):vm8; centos7 版本：7.2.1511; mariadb 版本：centos7.2內(nèi)置的

主庫(kù)服務(wù)器： 10.69.5.200，CentOS 7，MariaDB 10已安裝，有數(shù)據(jù)。

從庫(kù)服務(wù)器1： 10.69.5.201，CentOS 7，MariaDB 10已安裝，無(wú)應(yīng)用數(shù)據(jù)。

主服務(wù)器配置

以下操作在主服務(wù)器192.168.71.151的/etc/my.cnf上進(jìn)行。

1.修改配置文件，命令：vim /etc/my.cnf，輸入下列代碼：

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

`# 新添加的部分
# 配置主從時(shí)需要添加以下信息 start
innodb_file_per_table=NO
log-bin=/var/lib/mysql/master-bin #log-bin沒(méi)指定存儲(chǔ)目錄，則是默認(rèn)datadir指向的目錄
binlog_format=mixed
server-id=200 
#每個(gè)服務(wù)器都需要添加server_id配置，各個(gè)服務(wù)器的server_id需要保證唯一性，實(shí)踐中通常設(shè)置為服務(wù)器IP地址的最后一位
#配置主從時(shí)需要添加以下信息 end 
`
# 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

[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

最后，:wq!保存退出

2.重啟mariadb服務(wù)，輸入命令

[root@localhost ~]# systemctl restart mariadb.service

3.登錄mariadb

[root@localhost ~]# mysql -u root -padmin

注：-p后是密碼，中間沒(méi)有空格

4.創(chuàng)建帳號(hào)并賦予replication的權(quán)限

從庫(kù),從主庫(kù)復(fù)制數(shù)據(jù)時(shí)需要使用這個(gè)帳號(hào)進(jìn)行

MariaDB [(none)]> GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.69.5.%' IDENTIFIED BY 'admin';
Query OK, 0 rows affected (0.00 sec)

5.備份數(shù)據(jù)庫(kù)數(shù)據(jù)，用于導(dǎo)入到從數(shù)據(jù)庫(kù)中

加鎖

實(shí)際工作中，備份的時(shí)候是不讓往庫(kù)中寫數(shù)據(jù)的，所以數(shù)據(jù)庫(kù)要加鎖，只能讀

MariaDB [(none)]> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

記錄主庫(kù)log文件及其當(dāng)前位置

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |   694 |       |         |
+------------------+----------+--------------+------------------+

記住File和Position的部分，從服務(wù)器會(huì)用到

備份數(shù)據(jù)，輸入命令：

[root@localhost ~]# mysqldump -uroot -p --all-databases > /root/db.sql

解鎖主庫(kù)

數(shù)據(jù)備份完成后，就可以釋放主庫(kù)上的鎖：

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

從服務(wù)器配置

以下在從服務(wù)器上的操作

1.導(dǎo)入主庫(kù)的數(shù)據(jù)

[root@localhost ~]# mysql -uroot -p < db.sql

2.從服務(wù)器/etc/my.cnf配置,設(shè)置relay-log

my.cnf文件中添加一行relay_log=relay-bin

如果不設(shè)置，默認(rèn)是按主機(jī)名 + “-relay-bin”生成relay log。

[root@localhost ~]# cat /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

`#配置主從時(shí)需要添加以下信息 start
innodb_file_per_table=NO
server-id=201 #一般與服務(wù)器ip的最后數(shù)字一致
relay-log=/var/lib/mysql/relay-bin
#配置主從時(shí)需要添加以下信息 end 
`
# 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

[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

3.重啟服務(wù)

[root@localhost ~]# systemctl restart mariadb.service

4.登錄mariadb

[root@localhost ~]# mysql -u root -padmin

5.設(shè)置主從復(fù)制

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS= 694;
Query OK, 0 rows affected (0.02 sec)

這個(gè)命令完成以下幾個(gè)任務(wù)：

a.設(shè)置當(dāng)前服務(wù)器為主服務(wù)器(10.69.5.200)的從庫(kù)

b.提供當(dāng)前數(shù)據(jù)庫(kù)(從庫(kù))從主庫(kù)復(fù)制數(shù)據(jù)時(shí)所需的用戶名和密碼，即上面的GRANT REPLICATION SLAVE ON *.* TO 'root'@'10.69.5.%' IDENTIFIED BY 'admin';設(shè)置的

c.指定從庫(kù)開(kāi)始復(fù)制主庫(kù)時(shí)需要使用的日志文件和文件位置，即上面主庫(kù)執(zhí)行SHOW MASTER STATUS;顯示結(jié)果中的File和Position

6.開(kāi)啟主從復(fù)制

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

7.查看從庫(kù)狀態(tài)

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 10.69.5.200
         Master_User: root
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: master-bin.000001
     Read_Master_Log_Pos: 694
        Relay_Log_File: relay-bin.000003
        Relay_Log_Pos: 530
    Relay_Master_Log_File: master-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: 694
       Relay_Log_Space: 818
       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: 200
1 row in set (0.00 sec)

注意：結(jié)果中Slave_IO_Running和Slave_SQL_Running必須為Yes，如果不是，需要根據(jù)提示的錯(cuò)誤修改。

驗(yàn)證

主服務(wù)器：

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)

查看從服務(wù)器數(shù)據(jù)是否變化：

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)

可以看到，從服務(wù)器更新了數(shù)據(jù)

搭建過(guò)程中遇到的問(wèn)題及解決方法

問(wèn)題1：從服務(wù)器設(shè)置主從復(fù)制出現(xiàn)錯(cuò)誤:

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

發(fā)現(xiàn)

Slave_IO_Running: No
Slave_SQL_Running: No

進(jìn)一步發(fā)現(xiàn)我輸入的是：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;
報(bào)錯(cuò)：ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MariaDB error log

于是看錯(cuò)誤日志：/var/log/mariadb/mariadb.log

錯(cuò)誤日志的位置在/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

通過(guò)查找答案：刪除/var/lib/mysql/路徑下the ‘master.info' ‘mysqld-relay-bin.*' ‘relay-log.info' ‘relay-log-index.*'

運(yùn)行命令：rm -rf master.info,rm -rf *relay*

重啟服務(wù)：[root@localhost mysql]# systemctl restart mariadb.service

進(jìn)入mariadb:

[root@localhost mysql]# mysql -u root -padmin

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

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

重新設(shè)置主從復(fù)制關(guān)系：

MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='10.69.5.200',MASTER_USER='root', MASTER_PASSWORD='admin', MASTER_LOG_FILE='master-bin.000001', MASTER_LOG_POS= 694;
Query OK, 0 rows affected (0.02 sec)

這次成功了。

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

查看從庫(kù)狀態(tài)：

MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
        Slave_IO_State: Connecting to master
         Master_Host: 10.69.5.200
         Master_User: root
         Master_Port: 3306
        Connect_Retry: 60
       Master_Log_File: master-bin.000001
     Read_Master_Log_Pos: 694
        Relay_Log_File: relay-bin.000001
        Relay_Log_Pos: 4
    Relay_Master_Log_File: master-bin.000001
      Slave_IO_Running: Connecting
      Slave_SQL_Running: Yes
  ···
  ···
  ···
 Replicate_Ignore_Server_Ids: 
       Master_Server_Id: 0
1 row in set (0.00 sec)

發(fā)現(xiàn)問(wèn)題2.Slave_IO_Running: Connecting

問(wèn)題2.Slave_IO_Running: Connecting

查看錯(cuò)誤日志

[root@localhost ~]# cat /var/log/mariadb/mariadb.log
···
160915 13:17:56 [Note] Slave SQL thread initialized, starting replication in log 'master-bin.000001' at position 694, relay log '/var/lib/mysql/relay-bin.000001' position: 4
160915 13:17:56 [ERROR] Slave I/O: error connecting to master 'root@10.69.5.200:3306' - retry-time: 60 retries: 86400 message: Can't connect to MySQL server on '10.69.5.200' (113), Error_code: 2003

這時(shí)運(yùn)行telnet命令

[root@localhost ~]# telnet 10.69.5.200 3306

-bash: telnet: 未找到命令

安裝telnet

[root@localhost ~]# yum -y install telnet-server.x86_64

安裝成功后重啟telnet服務(wù)

[root@localhost ~]# systemctl start telnet.socket
[root@localhost ~]# systemctl enable telnet.socket
[root@localhost ~]# telnet 10.69.5.200 3306

-bash: telnet: 未找到命令

還是不行

這回我reboot重啟虛擬機(jī)，運(yùn)行命令

注意：這回不是"yum -y install telnet-server.x86_64"了，這回沒(méi)有telnet-server了

[root@localhost ~]# yum install telnet.x86_64

運(yùn)行成功了

接著

[root@localhost ~]# systemctl enable telnet.socket
[root@localhost ~]# systemctl start telnet.socket
[root@localhost ~]# firewall-cmd --add-service=telnet --permanent 
success
[root@localhost ~]# telnet
telnet>

telnet終于安裝成功了

從最新版本的centos7系統(tǒng)開(kāi)始，默認(rèn)的是 Mariadb而不是mysql！

使用系統(tǒng)自帶的repos安裝很簡(jiǎn)單：