分享 | 交流
让学习成为一种习惯

MySQL 主从复制实践

MySQL 主从复制是一个通过自动将主库数据复制到从数据库的过程,使得用户可以轻松维护数据的多个副本。多副本不仅可以增强数据的安全性,通过实现读写分离还能提升数据库的负载能力。本文试图详尽地描述主从复制的过程。

本文使用的主机如下:

MySQL 主从复制实践

安装MySQL

这里简单提一下CentOS安装MySQL的过程,原因有二:

  1. CentOS7 发行版中的源默认为MariaDB
  2. MySQL 官方的安装文档有些晦涩,这部分内容方便笔者后续查看

如果读者对MySQL的安装非常了解,请跳过该部分内容 🙂

## 从官网 https://dev.mysql.com/downloads/repo/yum/ 下载相应系统对应MySQL版本的Yum源
## 这里可能让人疑惑的是没有显示标明CentOS应该下载哪个,Red Hat Enterprise Linux 的即可
## 另外一个可能疑惑的地方是只有57版本的repo packages, 其实它包含了该发行版可用的所有
## MySQL版本,只不过默认启用的版本为5.7,可使用`yum repolist all | grep mysql` 查看
[root@master ~]# wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

## 使用如下命令安装源
[root@master ~]# sudo rpm -Uvh mysql57-community-release-el7-11.noarch.rpm
## 或
[root@master ~]# sudo yum localinstall mysql57-community-release-el7-11.noarch.rpm

## 弃用5.7版本,启用5.6版本
[root@master ~]# sudo yum-config-manager --disable mysql57-community
[root@master ~]# sudo yum-config-manager --enable mysql56-community

## 查看启用的MySQL源
[root@master ~]# yum repolist enabled | grep "mysql.*-community.*"
!mysql-connectors-community/x86_64 MySQL Connectors Community                 42
!mysql-tools-community/x86_64      MySQL Tools Community                      55
!mysql56-community/x86_64          MySQL 5.6 Community Server                361

## 设置好源之后使用如下命令来安装MySQL
[root@master ~]# sudo yum install mysql-community-server
## 查看版本
[root@master ~]# mysqld -V
mysqld  Ver 5.6.38 for Linux on x86_64 (MySQL Community Server (GPL))

## 启动并设置开机启动
[root@master ~]# systemctl start mysqld.service
[root@master ~]# sudo systemctl enable mysqld.service

## Securing the MySQL Installation
## 在安装5.6版本时需要进行该操作,根据提示设置root密码,删除匿名用户等
## 5.7版本需要不同的操作,详见https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/
[root@master ~]# mysql_secure_installation

以上是 MySQL 5.6 的安装过程,安装完成后往往还需要修改配置以获取较优的性能:

数据库配置

/etc/my.cnf

## [client] option group is read by all client programs provided in MySQL distributions (but not by mysqld)
[client]
default-character-set = utf8

## [mysql] option group apply specifically to mysql client program
[mysql]
# 更改默认字符集以免引发乱码
default-character-set = utf8

## [mysqld] option group apply specifically to mysqld server program
[mysqld]
# Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM)
innodb_buffer_pool_size = 6G

# 客户端最大并发连接数量,default: 151
max_connections = 1000

# 在检查客户端连接时,不要解析主机名,只使用IP地址
# 该选项要求grant表中的所有主机值必须是IP地址或localhost
skip-name-resolve

# 在Windows或OS X系统中,文件系统不区分大小写
# 设置为1,表文件全部以小写命名
lower_case_table_names = 1

# Server允许发送和接收的最大消息包大小,default: 4MB
# 使用大的BLOB列或长字符串,需要增加该值,它应该和你要使用的最大BLOB一样大
max_allowed_packet = 20M

# 设置字符集为 utf8
character-set-server = utf8
# 每个客户端连接数据库之后首先执行的一条命令,也是为了查询到乱码
init_connect = 'SET NAMES utf8'
# 可以使用 `show collation;` 来查看每个字符集可用的排序规则
# `show variables like "%character%";show variables like "%collation%";` 来查看当前设置的字符集及排序规则
# ci => case insensitive
collation-server = utf8_unicode_ci

## omit other default options and option group
...

注:所有可配置的选项都可以通过相应的命令查看,如 mysqld –verbose –help、mysql –verbose –help。或查看手册 Server Option and Variable Reference、mysql Options。

Master/Slave Setup

回到正题。在讨论设置主从复制的细节之前,我们先简单了解一下 MySQL 是如何复制数据的,直观上,复制包括三个过程:

  1. 主节点将数据的变动记录到 binary log (这些记录被称作 binary log events)
  2. 从节点通过网络将主节点的 binary log events 复制到从节点的 relay log
  3. 从节点重放(replay) relay log 中的事件,将这些变动应用到从节点的数据上

下图显示了这一过程:

MySQL 主从复制实践

更细节的部分可翻阅《High Performance MySQL, 3rd Edition》第10章进行查看。

配置主节点

master /etc/my.cnf

server-id = 1
log-bin = mysql-bin
# 当InnoDB存储引擎需要处理事务,为了尽可能满足持久性和一致性,应该设置如下两项
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

需要在主节点上设置一个可供从节点连接的账号,并赋予相应的权限:

mysql> GRANT REPLICATION SLAVE ON *.* TO slave@'10.0.63.%' IDENTIFIED BY 'p4ssword';

然后把主库的数据使用 mysqldump 保存到一个文件中:

## -A => --all-databases
## --skip-lock-tables => --opt 是 --add-drop-table --add-locks --create-options 
##                       --disable-keys --extended-insert --lock-tables --quick
##                       --set-charset 选项的组合,默认是生效的,当使用 InnoDB
##                       时,--single-transation 是一个比 --lock-tables 更好的选
##                       项,因此使用 --skip-lock-tables 来禁掉 --lock-tables
## --single-transaction => 通过将导出操作封装在一个事务内来使得导出的数据是一个
##                         一致性快照, 依赖 InnoDB 的 MVCC 机制。
## --flush-logs => 导出之前先刷新服务器日志文件
## --hex-blog => 使用十六进制表示法导出二进制(如:'abc' 导出为 0x616263)
## --master-data=2 => 将 binlog 的坐标作为注释记录到导出文件中,用于后续操作
## 以上参数详见 https://dev.mysql.com/doc/refman/5.6/en/mysqldump.html
[root@master ~]# mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs 
--hex-blob --master-data=2 -A  > all-databases.sql

上面的 –single-transaction 和 –master-data=2 选项组合在导出数据前做了如下几件事:

  1. FLUSH TABLES WITH READ LOCK;
  2. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  3. START TRANSACTION;
  4. SHOW MASTER STATUS;
  5. UNLOCK TABLES;

什么意思呢?就是说这条命令在运行的时候既保证保证了导出的数据是 binary log 坐标(MASTER_LOG_FILE, MASTER_LOG_POS)位置的数据库快照,又不影响后续写命令的执行。

由于上述命令将 binlog 坐标作为注释记录在了 all-databases.sql 文件中,因此可以使用如下命令获取:

[root@master ~]# head all-databases.sql -n80 | grep "MASTER_LOG_POS"
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=120;

然后将 all-databases.sql 传输到从节点上(如果数据文件较大,可用 gzip 压缩后再传输)。

需要注意的一点,FLUSH TABLES WITH READ LOCK; 命令在获取全局读锁之前,必须等待所有的查询结束,如果有长时间的查询操作,将会使得该操作的过程非常漫长,并导致整个数据库处于只读状态甚至连读操作都会阻塞(见参考8~13)。因此,mysqldump 操作应该选择在数据库负载最小的时刻进行。

配置从节点

slave /etc/my.cnf

server-id=2
# 在从节点开启 log_bin 和 log-slave-update 可用于配置级联复制架构
log_bin = mysql-bin
log-slave-update = 1
relay-log = mysql-relay-bin
# the server permits no client updates except from users who have the SUPER privilege
read-only = 1

重新启动 Slave Server 并将 all-databases.sql 的语句在 Slave Server 执行。

[root@slave ~]# systemctl restart mysqld.service
[root@slave ~]# mysql -uroot -p < all-databases.sql

执行完上述命令之后,从节点的数据库就跟 binlog 坐标点的数据一模一样了。接下来就是进入从节点 MySQL 的控制台,告诉它应该从主节点的什么位置进行接下来的同步了:

mysql> CHANGE MASTER TO MASTER_HOST='10.0.63.202',MASTER_USER='slave',MASTER_PASSWORD='p4ssword',MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.03 sec)

查看 Slave 状态命令:

mysql> SHOW SLAVE STATUS \G

以上为 MySQL 主从复制的过程,其中比较关键的是如何获取 mysqldump 运行时的数据库快照和 binlog 的坐标,即充分理解几个参数的含义。

在写本文之前,笔者对主从复制存有一些疑问,经过两天的调研整理,基本能把之前的疑问做一简单回答,如下:

  1. 是否需要选择需要备份的库?
    对于现在我接触到的应用,基本上是多个微服务各自对应一个数据库(database),但却同时存在于一个 MySQL Server 上。备份的时候使用 –all-databases 选项可将所有数据库(–all-databases 选项不会备份 performance_schema 和 information_schema)导出到文件,并后续同步到 slave 节点。在配置文件中没有配置 binlog-do-db,会将所有数据库的变动写入 binary log,包括创建database的命令。
    如果是一个微服务对应一个 MySQL Server 的场景,既然只有一个库了,备份整个库也无所谓啊:)

  2. 如何处理存储过程、函数及触发器?
    这个问题之前一直困扰着我,通过调研,发现它们存储在 mysql database 中,mysqldump –all-databases 会将 mysql 数据库导出,使得从节点与主节点拥有一样的 mysql 数据库,而任何新创建的存储过程、函数及触发器都会写到 binary log 中,进而同步到从节点的 mysql 数据库。因此,只需要操作主节点的 MySQL Server,而不需对从节点进行任何操作。

  3. 如果需要变动表结构需要如何处理?
    如果没有配置 binlog-do-db,那么任何数据库的改动都会写入 binary log,因此,也不用关心从节点。

  4. 如果新增数据库需要如何处理?
    同问题3。

  5. 如果需要将一主一丛扩展为一主二从应该如何操作?
    选则在主节点负载最小的时刻再进行一次上面的操作即可。

结语

本文仅介绍了一种 MySQL 的主从复制过程,还有很多其它方法(如利用文件系统的snapshot或Percona XtraBackup 工具)可能有更好的性能,在今后的实践中会进行尝试。

另外,在查阅资料的过程中在 MySQL 官网上看到了 InnoDB Cluster 和 MySQL NDB Cluster 相关的内容。前者通过将一组 MySQL Server 配置为一个集群,在默认额单主节点模式下,集群具有一个读写主节点和多个只读副节点,客户端程序通过连接 MySQL Router,Router 会选择一个合适的 Server 来提供服务;后者通过 NDB 存储引擎提供存储能力,SQL 层(mysqld)负责存储层之上的所有事情,如连接管理,query 优化及响应,Cache 管理等等。这些笔者还没有进行深入了解,这里列出作为后续调研的方向。

– 20180119 更新 –

当应用连接到主库进行测试的时候,出现了如下错误:

SQL Error: 1418, SQLState: HY000
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary 
logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

原因是因为 SQL 语句中含有存储程序(stored precedures and functions/triggers/events),如:

SELECT distinct a.permission_value FROM auth_permission a INNER JOIN auth_role_permission b 
    ON a.id=b.permission_id INNER JOIN auth_user_role c ON b.role_id=c.role_id 
    WHERE a.deleted=0 and c.user_id=? AND FIND_IN_SET(a.id, getPermissionChildList(?))

如果该语句被路由到 Slave 节点且 getPermissionChildList 含有更改数据的操作,会造成主从库不一致,存在安全隐患,所以 MySQL 默认禁止这种操作。如果明确知道存储程序不会造成主从库不一致,则可以以通过以下两种方式放宽这一限制:

  1. 在 MySQL 控制台执行 SET GLOBAL log_bin_trust_function_creators = 1;
  2. 在配置文件中添加 log_bin_trust_function_creators = 1; 并重新启动

 

未经允许不得转载:留时刻运维网 » MySQL 主从复制实践

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

留时刻 - Linux系统教程,运维经验分享

加入我们给我留言