服务器之家

服务器之家 > 正文

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

时间：2021-12-27 16:22     来源/作者：姚一号

花了小一天的时间，终于实现了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
  • systemctl start mariadb ==> 启动mariadb
  • systemctl enable mariadb ==> 开机自启动
  • mysql_secure_installation ==> 设置 root密码等相关
  • mysql -u root -p 123456 ==> 测试登录！

结束！

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持服务器之家。

原文链接：http://blog.csdn.net/mingliangniwo/article/details/54606894?locationNum=8&fps=1

标签：
CentOS MariaDB 主从复制 

相关文章

热门资讯

yue是什么意思 网络流行语yue了是什么梗
yue是什么意思 网络流行语yue了是什么梗 2020-10-11
背刺什么意思 网络词语背刺是什么梗
背刺什么意思 网络词语背刺是什么梗 2020-05-22
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全
2020微信伤感网名听哭了 让对方看到心疼的伤感网名大全 2019-12-26
蜘蛛侠3英雄无归3正片免费播放 蜘蛛侠3在线观看免费高清完整
蜘蛛侠3英雄无归3正片免费播放 蜘蛛侠3在线观看免费高清完整 2021-08-24
2021年耽改剧名单 2021要播出的59部耽改剧列表
2021年耽改剧名单 2021要播出的59部耽改剧列表 2021-03-05
返回顶部