centos7 mariadb主從復制配置搭建詳解步驟

2024-09-01 13:50:32

字體：大中小

來源：轉載

供稿：網友

花了小一天的時間，終于實現了centos7 mariadb主從復制配置搭建，下面記錄一下過程

環境:

虛擬機:vm8; centos7 版本：7.2.1511; mariadb 版本：centos7.2內置的

主庫服務器： 10.69.5.200，CentOS 7，MariaDB 10已安裝，有數據。

從庫服務器1： 10.69.5.201，CentOS 7，MariaDB 10已安裝，無應用數據。

主服務器配置

以下操作在主服務器192.168.71.151的/etc/my.cnf上進行。

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

[root@localhost ~]# cat /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock`# 新添加的部分# 配置主從時需要添加以下信息 startinnodb_file_per_table=NOlog-bin=/var/lib/mysql/master-bin #log-bin沒指定存儲目錄，則是默認datadir指向的目錄binlog_format=mixedserver-id=200 #每個服務器都需要添加server_id配置，各個服務器的server_id需要保證唯一性，實踐中通常設置為服務器IP地址的最后一位#配置主從時需要添加以下信息 end `# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-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.logpid-file=/var/run/mariadb/mariadb.pid## include all files from the config directory#!includedir /etc/my.cnf.d

最后，:wq!保存退出

2.重啟mariadb服務，輸入命令

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

3.登錄mariadb

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

注：-p后是密碼，中間沒有空格

4.創建帳號并賦予replication的權限

從庫,從主庫復制數據時需要使用這個帳號進行

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

5.備份數據庫數據，用于導入到從數據庫中

加鎖

實際工作中，備份的時候是不讓往庫中寫數據的，所以數據庫要加鎖，只能讀

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

記錄主庫log文件及其當前位置

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

記住File和Position的部分，從服務器會用到

備份數據，輸入命令：

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

解鎖主庫

數據備份完成后，就可以釋放主庫上的鎖：

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

從服務器配置

以下在從服務器上的操作

1.導入主庫的數據

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

2.從服務器/etc/my.cnf配置,設置relay-log

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

如果不設置，默認是按主機名 + “-relay-bin”生成relay log。

[root@localhost ~]# cat /etc/my.cnf[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0`#配置主從時需要添加以下信息 startinnodb_file_per_table=NOserver-id=201 #一般與服務器ip的最后數字一致relay-log=/var/lib/mysql/relay-bin#配置主從時需要添加以下信息 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.logpid-file=/var/run/mariadb/mariadb.pid## include all files from the config directory#!includedir /etc/my.cnf.d

3.重啟服務

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

4.登錄mariadb

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

5.設置主從復制

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)

這個命令完成以下幾個任務：

a.設置當前服務器為主服務器(10.69.5.200)的從庫

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

c.指定從庫開始復制主庫時需要使用的日志文件和文件位置，即上面主庫執行SHOW MASTER STATUS;顯示結果中的File和Position

6.開啟主從復制

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

7.查看從庫狀態

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: 0Master_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: 2001 row in set (0.00 sec)

注意：結果中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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedMariaDB [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: NoSlave_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.log160915 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.*'

運行命令：rm -rf master.info,rm -rf *relay*

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

進入mariadb:

[root@localhost mysql]# mysql -u root -padminMariaDB [(none)]> flush logs;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> reset slave;Query OK, 0 rows affected (0.00 sec)

重新設置主從復制關系：

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)

查看從庫狀態：

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: 01 row in set (0.00 sec)

發現問題2.Slave_IO_Running: Connecting

問題2.Slave_IO_Running: Connecting

查看錯誤日志

[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: 4160915 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

這時運行telnet命令

[root@localhost ~]# telnet 10.69.5.200 3306

-bash: telnet: 未找到命令

安裝telnet

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

安裝成功后重啟telnet服務

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

-bash: telnet: 未找到命令

還是不行

這回我reboot重啟虛擬機，運行命令

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

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

運行成功了

接著

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

telnet終于安裝成功了

從最新版本的centos7系統開始，默認的是 Mariadb而不是mysql！

使用系統自帶的repos安裝很簡單：