国内海外服务器测评及优惠
Linux服务器运维救灾服务
Hi, 请登录     我要注册     找回密码
当前位置：linuxcto运维 服务器系统 centos 正文

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

2026-01-15 分类：centos 阅读(40) 评论(0)

花了小一天的时间，终于实现了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，输入下列代码：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
[root@localhost ~]
# cat /etc/my.cnf
[mysqld]
datadir=
/var/lib/mysql
socket=
/var/lib/mysql/mysql
.sock
 
`
# 新添加的部分
# 配置主从时需要添加以下信息 start
innodb_file_per_table=NO
log-bin=
/var/lib/mysql/master-bin
#log-bin没指定存储，则是默认datadir指向的
binlog_format=mixed
server-
id
=200
#每个服务器都需要添加server_id配置，各个服务器的server_id需要保证唯一性，实践中通常设置为服务器IP地址的最后一位
#配置主从时需要添加以下信息 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服务，输入命令

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

3.登录mariadb

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

注：-p后是密码，中间没有空格

4.创建帐号并赋予replication的权限

从库,从主库复制数据时需要使用这个帐号进行

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

5.备份数据库数据，用于导入到从数据库中

加锁

实际工作中，备份的时候是不让往库中写数据的，所以数据库要加锁，只能读

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

记录主库log文件及其当前位置

?
1
2
3
4
5
6
MariaDB [(none)]> SHOW MASTER STATUS;
+
------------------+----------+--------------+------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+
------------------+----------+--------------+------------------+
| mysql-bin.000001 |   694 |       |         |
+
------------------+----------+--------------+------------------+

记住File和Position的部分，从服务器会用到

备份数据，输入命令：

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

解锁 主库

数据备份完成后，就可以释放主库上的锁：

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

从服务器配置

以下在从服务器上的操作

1.导入主库的数据

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

2.从服务器/etc/my.cnf配置,设置relay-log

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

如果不设置，默认是按主机名 + “-relay-bin”生成relay log。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
[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
 
`
#配置主从时需要添加以下信息 start
innodb_file_per_table=NO
server-
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
.log
pid-
file
=
/var/run/mariadb/mariadb
.pid
 
#
# include all files from the config directory
#
!includedir
/etc/my
.cnf.d

3.重启服务

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

4.登录mariadb

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

5.设置主从复制

?
1
2
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.开启主从复制

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

7.查看从库状态

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
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)

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

验证

主服务器：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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)

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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：从服务器设置主从复制出现错误:

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

发现 

?
1
2
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=/

?
1
2
3
[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.*’

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

重启服务：[root@localhost mysql]# systemctl restart mariadb.service

进入mariadb:

?
1
2
3
4
5
6
7
[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)

重新设置主从复制关系：

?
1
2
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)

这次成功了。

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

查看从库状态：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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)

发现问题2.Slave_IO_Running: Connecting

问题2.Slave_IO_Running: Connecting

查看错误日志

?
1
2
3
4
[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

这时运行telnet命令

?
1
[root@localhost ~]
# telnet 10.69.5.200 3306

-bash: telnet: 未找到命令

安装telnet

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

安装成功后重启telnet服务

?
1
2
3
[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了

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

运行成功了

接着

?
1
2
3
4
5
6
[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系统开始，默认的是 Mariadb而不是mysql！

使用系统自带的repos安装很简单：

?
1
yum
install
mariadb mariadb-server

结束！

AD：【鸟朝天CDN】专业过移动屏蔽加速|免实名|T级高防|海外防DDOS
赞(0) 打赏
未经允许不得转载：linuxcto运维 » centos7 mariadb主从复制配置搭建详解步骤
分享到

相关推荐

置顶推荐

热门标签

Apache centos CentOS6.5 CentOS7 Docker Eth0 Firewall httpd ip IP地址 jdk LAMP Linux LNMP mysql NFS nginx php phpMyAdmin Python redis root ssh SVN tomcat vmware vmware安装centos VPS vsftpd YUM 云服务器 安装 开机启动 挂载 服务器 服务器搭建 端口 编译安装 网卡 网络配置 虚拟机 配置 防火墙 阿里云 静态IP

热门文章

觉得文章有用就打赏一下文章作者

非常感谢你的打赏，我们将继续提供更多优质内容，让我们一起创建更加美好的网络世界！

支付宝扫一扫

微信扫一扫