如何在两台Centos机器上部署主从复制的mysql

发表于 更新于 分类于 Waline：

为了保证数据的可靠，我们一般会对数据醉一次主从复制，最近我也进行了一点实践，踩了几个坑，在这里总结下。

环境准备

我自身有两台centos的云服务器，一台已经装过mysql了，不过是5.7版本，一台是我刚装的mysql，是8.0版本。

主从备份的流程

将5.7的数据库升级

因为我是打算用5.7所在的服务器作为从库的服务器，而mysql的主从备份有要求，从库的版本不能低于主库的，所以第一步就是升级

注意事项

首先，我们要大概了解下MySQL5.7和8.0有哪些不同，参考官方文档和其他网友文章，概括总结出MySQL8.0以下几点新特性：

  • 默认字符集由latin1变为utf8mb4。
  • MyISAM系统表全部换成InnoDB表。
  • JSON特性增强。
  • 支持不可见索引，支持直方图。
  • sql_mode参数默认值变化。
  • 默认密码策略变更。
  • 新增角色管理。
  • 支持窗口函数，支持Hash join。

根据版本变化及官方升级教程，列举出以下几点注意事项：

  • 注意字符集设置。为了避免新旧对象字符集不一致的情况，可以在配置文件将字符集和校验规则设置为旧版本的字符集和比较规则。
  • 密码认证插件变更。为了避免连接问题，可以仍采用5.7的mysql_native_password认证插件。
  • sql_mode支持问题。8.0版本sql_mode不支持NO_AUTO_CREATE_USER，要避免配置的sql_mode中带有NO_AUTO_CREATE_USER。
  • 是否需要手动升级系统表。在MySQL 8.0.16版本之前，需要手动的执行mysql_upgrade来完成该步骤的升级，在MySQL 8.0.16版本及之后是由mysqld来完成该步骤的升级。

升级流程

官网下载对应版本的tar包，可通过wget下载或者本地下载后上传。

下载地址：
https://downloads.mysql.com/archives/community/
选择mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
注意，如果你下载速度很慢，可以考虑使用淘宝的镜像地址：https://mirrors.aliyun.com/mysql

行以下步骤解压tar包：

  1. 安装包上传至原安装包目录下 我的是/usr/local/
    cd /usr/local/

  2. 解压安装包
    xz -d mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz
    tar -xvf mysql-8.0.19-linux-glibc2.12-x86_64.tar

  3. 文件夹重命名为mysql8
    mv mysql-8.0.19-linux-glibc2.12-x86_64 mysql8

  4. 更改文件夹所属
    chown -R mysql.mysql /usr/local/mysql8/

  5. 更改配置文件my.cnf

因5.7版本与8.0版本参数有所不同，为了能顺利升级，我们需要更改部分配置参数。主要注意sql_mode、basedir、密码认证插件及字符集设置，其他参数最好还是按照原5.7的来，不需要做调整。下面展示下更改后的配置文件：

最后几个for8.0的参数要格外注意

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
[mysqld]
user = mysql        
datadir = /data/mysql/data  
port = 3306               

socket = /data/mysql/tmp/mysql.sock
pid-file  = /data/mysql/tmp/mysqld.pid
tmpdir = /data/mysql/tmp    
skip_name_resolve = 1
max_connections = 2000
group_concat_max_len = 1024000
lower_case_table_names = 1
log_timestamps=SYSTEM
max_allowed_packet = 32M
binlog_cache_size = 4M
sort_buffer_size = 2M
read_buffer_size = 4M
join_buffer_size = 4M
tmp_table_size = 96M
max_heap_table_size = 96M
max_length_for_sort_data = 8096
default_time_zone = '+8:00'

#logs
server-id = 1003306
log-error = /data/mysql/logs/error.log
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 3
log-bin = /data/mysql/logs/binlog
binlog_format = row
log_bin_trust_function_creators = 1
gtid_mode = ON
enforce_gtid_consistency = ON

#for8.0
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
character-set-server = utf8
collation_server = utf8_general_ci
basedir = /usr/local/mysql8
skip_ssl
default_authentication_plugin=mysql_native_password
  1. 执行升级动作

所有前置工作准备好后就可以开始正式升级了，不过升级前还是建议先全库备份下。万事俱备后，按照如下指示进行正式升级。

进入原5.7 mysql命令行 正确关闭数据库

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
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.23-log |
+------------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1     |
+----------------------+-------+
1 row in set (0.00 sec)
# 确保数据都刷到硬盘上，更改成0
mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)

mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

# 退出至终端 用mysql8.0.19客户端直接启动
[root@centos ~]# /usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql & 
[1] 23333
[root@centos ~]# 2020-05-20T07:07:02.337626Z mysqld_safe Logging to '/data/mysql/logs/error.log'.
2020-05-20T07:07:02.366244Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data

# 可观察下错误日志看是否报错 然后重新登录测试
[root@centos ~]# mysql -uroot -p123456 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)
  1. 修改环境变量
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
因basedir由/usr/local/mysql变成了/usr/local/mysql8，故相关环境变量推荐修改下。可按照以下步骤来操作验证：

# 修改mysql服务启动项配置
vi /etc/init.d/mysql
# 修改basedir目录
basedir=/usr/local/mysql8

# 修改PATH变量
vi /etc/profile 
# 将PATH中的/usr/local/mysql/bin改为/usr/local/mysql8/bin 

# 生效验证
[root@centos ~]# source /etc/profile
[root@centos ~]# which mysql
/usr/local/mysql8/bin/mysql
[root@centos ~]# mysql -V
mysql  Ver 8.0.19 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

Tips

如果没法用systemctl来控制mysql怎么办

操作流程：

1.进入/etc/systemd/system

cd /etc/systemd/system

2.创建mysql.service

vi mysql.service

3.mysql.service中添加配置信息

1
2
3
4
5
6
7
8
9
10
11
12
13
[Unit]
Description=MySQL Server
After=network.target

[Service]
ExecStart=/usr/local/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --datadir=/usr/local/mysql/data
ExecStop=/usr/local/mysql8/bin/mysqladmin --defaults-file=/etc/my.cnf shutdown
User=mysql
Group=mysql
Restart=always

[Install]
WantedBy=multi-user.target

注意，这里的路径要改成你自己的实际路径

设置主从备份

分别在两个数据库上创建一个可以远程访问的账号

认主流程

主服务数据库

  1. 登陆到主服务器的mysql，并执行如下语句创建用户，授予权限：
1
2
3
sql复制代码CREATE USER 'slave'@'%' IDENTIFIED BY '123456';

GRANT ALL PRIVILEGES ON *.* TO slave@"%" IDENTIFIED BY "123456";

修改my.cnf配置文件

1
2
log-bin=mysql-bin
server-id=1

说明：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
log-bin:开启二进制日志，该日志是在事务提交时写日志文件的。默认大小是1G，后面加001,002这样的后缀顺加。

server-id，唯一标识主机，mysql主从每个mysql实例配置都不一样就行。这个值默认是0，如果是0，主服务器拒绝任何从服务器的连接。

其他配置（不是必须配置的）：

1、binlog-do-db=db_001（主数据库配置）       #指定mysql的binlog日志记录哪个db，配置需要同步的数据库，可以配置多个，如果没有此配置项则同步全部。

2、binlog-ignore-db=mysql（主数据库配置）    #配置不同步的数据库，可以配置多个。

3、binlog_format = mixed     #配置binlog的格式

4、read-only = 0                   #配置是否只读  0代表不只读，1代表只读

5、auto-increament-increment = 10    #用于设定双主情况下自增列的ID冲突使用的，主要用来设置自增步长

6、auto-increment-offset = 1               #表示这台服务器的序号，从1开始，不超过auto-increament-increment

重启数据库

从服务器设置：

1
2
3
server-id=2
replicate-do-db=test
skip-slave-start=true

重启数据库

配置从服务器认主

获取binlog的信息
我们先到主（master）服务器上获取binlog的信息，在mysql的命令界面输入：

1
show master status;

这里展示的就是我们当前主服务器使用的binlog的文件名，其中position是文件中偏移量，我们之后配置slave需要用到这些信息，这个文件在每次服务器状态变化后都不同
进入最关键的一步了，在进入从服务器mysql操作界面后，输入如下指令：

1
2
3
4
5
6
7
8
stop slave;          //先停步slave服务线程，这个是很重要的，如果不这样做会造成以下操作不成功，第一次设计主从的话忽略。

change master to 
master_host='192.0.0.131',
master_user='slave',
master_password='123456',
master_log_file='mysql-bin.000002', 
master_log_pos=1472;

这里user和password就是我们第一步在主服务器上创建的用户名和密码，然后MASTER_LOG_FILE 就是我们上一步查看到的master在使用的binlog文件（这个文件在每次主服务器状态变化后都不同），MASTER_LOG_POS   就是binlog的偏移量，用于同步扫描使用。master_log_file对应File, master_log_pos对应Position。Mysql 5.x以上版本已经不支持在配置文件中指定主服务器相关选项。
执行完抛警告的话没关系，没有异常就可以。
开启从服务器slave线程

1
2
3
4
start slave;

# 查看（Slave）状态
show slave status\G;