Mysql5.7配置主从同步

发表于 更新于 分类于
本文字数： 7k 阅读时长 ≈ 6 分钟

什么是主从复制

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

主从复制的优点

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

img

安装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
4
5
6
7
8
9
10
11
CREATE USER 'repl'@'%' IDENTIFIED BY 'repl'; #创建repl账号并设置密码为repl
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; #赋予repl账号复制权限
FLUSH PRIVILEGES; #刷新权限
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
show master status;
[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
8
9
10
11
12
13
14
15
16
17
18
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;
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
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
63
show slave status\G
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
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的日志报错