Mysql5.7配置主从同步

什么是主从复制

简单来说,是使用两个或两个以上相同的数据库,将一个数据库当做主数据库,而另一个数据库当做从数据库。在主数据库中进行相应操作时,从数据库记录下所有主数据库的操作,使其二者一模一样。

主从复制的优点

1.如果主服务器出现问题,可以快速切换到从服务器提供服务;

2.可以在从服务器上执行查询操作,降低主服务器的访问压力;

3.可以在从服务器上执行备份,以避免备份期间影响主服务器的服务

实验环境

数据库主服务器 10.150.1.36

数据库从服务器 10.150.1.37

数据库版本:5.7.30

操作系统:CentOS Linux release 7.8.2003

关闭selinux

修改/etc/selinux/config 文件

将SELINUX=enforcing改为SELINUX=disabled,重启生效

1
2
3
4
5
6
7
8
9
10
11
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
# targeted - Targeted processes are protected,
# minimum - Modification of targeted policy. Only selected processes are protected.
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
关闭防火墙
1
systemctl stop firewalld
卸载系统自带Mariadb
1
2
3
4
[root@localhost ~]rpm -qa | grep mariadb #查看centos 默认安装的mariadb
mariadb-libs-5.5.65-1.el7.x86_64
[root@localhost ~] rpm -e --nodeps mariadb-libs-5.5.65-1.el7.x86_64 # 强制卸载mariadb
[root@localhost ~] rm -rf /etc/my.cnf #删除mariadb默认的配置文件
下载mysql

下载mysql二进制压缩包并上传到服务器

进入https://dev.mysql.com/downloads/mysql/5.7.html

安装MySql
安装mysql
1
2
3
4
5
6
#进入mysql上传目录
cd /usr/local/src
#解压文件
tar zxvf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
#拷贝至安装路径
cp -R mysql-5.7.30-linux-glibc2.12-x86_64 /usr/local/mysql
添加mysql用户和用户组
1
2
groupadd mysql
useradd mysql -g mysql -M -s /sbin/nologin
给mysql用户授权mysql 文件夹
1
chown -R mysql:mysql /usr/local/mysql/
拷贝启动文件
1
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/
数据库初始化
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
#创建数据库文件夹
mkdir -p /data/mysql/data
mkdir -p /data/mysql/logs
mkdir -p /data/mysql/binlog
chown mysql:mysql -R /data/mysql/
#创建主库my.cnf
vim /etc/my.cf

[mysqld]
bind-address=0.0.0.0 #绑定的IP
port=3306 #mysql占用的端口
basedir=/usr/local/mysql #mysql安装目录
datadir=/data/mysql/data #mysql数据目录
socket=/tmp/mysql.sock #socket位置
log-error=/data/mysql/logs/mysql.err #error日志路径
pid-file=mysql.pid #进程id文件
#character config
character_set_server=utf8mb4 #编码格式
symbolic-links=0 #关闭符号链接
explicit_defaults_for_timestamp=true
lower_case_table_names=1 #不区分大小写
log_bin =/data/mysql/binlog/mysql-bin #logbin文件路径
binlog_format=row #binlog
max_connections =1024 #mysql最大连接数
server-id=1 #数据库服务器ID,配置主从或者集群时 server id必须唯一
sync_binlog=1 #二进制日志文件刷新到磁盘上
expire_logs_days = 30 #binlog日志 超过30天的自动清理
slow_query_log = 1 #启用慢查询日志
long_query_time = 2 #慢查询时间
slow_query_log_file=/data/mysql/logs/slow.log #慢查询日志路径

#使用-initialize会生成随机密码,使用-initialize-insecure生成空密码,这里选择生成空密码。
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql
添加至环境变量
1
2
3
4
5
6
#使其永久生效,把安装目录添加至环境变量
vim /etc/profile
#文件最后一行添加进去
export PATH=$PATH:/usr/local/mysql/bin
#马上生效
source /etc/profile

5.从数据库安装

安装过程略,与主服务器安装一样,但server-id必须唯一

主从配置
分别启动主从数据库服务器
1
/etc/ini.d/mysql.server start
主服务器创建主从同步账号
1
2
3
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl'; #创建repl账号并设置密码为repl
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; #赋予repl账号复制权限
FLUSH PRIVILEGES; #刷新权限
1
2
3
4
5
6
7
8
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
查看主库position和File
1
show master status;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[root@localhost src] mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1162 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql>

5.4从库配置主库信息并开启同步

1
2
3
4
5
6
7
CHANGE MASTER TO
MASTER_HOST='10.150.1.36',
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1162;
1
2
3
4
5
6
7
8
9
10
11
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.150.1.36',
-> MASTER_USER='repl',
-> MASTER_PASSWORD='repl',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=1162;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
主从同步状态查看

登录从服务器

1
show slave status\G
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.150.1.36
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1162
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
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: 1162
Relay_Log_Space: 531
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: 1
Master_UUID: d784f10a-bd86-11ea-a579-00505693b4d1
Master_Info_File: /data/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

mysql>

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
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)

mysql>

从库

1
2
3
4
5
6
7
8
9
10
11
12
13
14

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)

mysql>

主从配置常见错误及解决思路

Slave_IO_Running: Connecting

网络不通

密码不对

pos不正确

ID相同

防火墙未关闭或者端口未开放

Slave_SQL_Running:No

为No说明数据库不统一,或者无法复制数据库,手动重新配置主从即可,具体查看Last_IO_Error_Timestamp:和Last_SQL_Error_Timestamp的日志报错

-------------本文结束 感谢您的阅读-------------

本文标题:Mysql5.7配置主从同步

文章作者:柒小浪

发布时间:2020年07月03日 - 21:05:26

最后更新:2020年07月04日 - 14:21:07

原始链接:sswolf.cn/post/2020-07-03-MySql-master-slave/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。