【转载】MySQL复制的概述、安装、故障、技巧、工具

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介:

概述

首先主服务器把数据变化记录到主日志,然后从服务器通过I/O线程读取主服务器上的主日志,并且把它写入到从服务器的中继日志中,接着SQL线程读取中继日志,并且在从服务器上重放,从而实现MySQL复制。具体如下图所示:

MySQL复制

MySQL复制

整个过程反映到从服务器上,对应三套日志信息,可在从服务器上用如下命令查看:

mysql> SHOW SLAVE STATUS;

 

  • Master_Log_File & Read_Master_Log_Pos:下一个传输的主日志信息。
  • Relay_Master_Log_File & Exec_Master_Log_Pos:下一个执行的主日志信息。
  • Relay_Log_File & Relay_Log_Pos:下一个执行的中继日志信息。

理解这些日志信息的含义对于解决故障至关重要,后文会详细阐述。

安装

先在主服务器上创建复制账号:

mysql> GRANT REPLICATION SLAVE ON *.*
       TO '<SLAVE_USER>'@'<SLAVE_HOST>'
       IDENTIFIED BY '<SLAVE_PASSWORD>';

注:出于安全性和灵活性的考虑,不要把root等具有SUPER权限用户作为复制账号。

然后设置主服务器配置文件(缺省:/etc/my.cnf):

[mysqld]

server_id = 100
log_bin = mysql-bin
log_bin_index = mysql-bin.index
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
innodb_support_xa = 1

注:一定要保证主从服务器各自的server_id唯一,避免冲突。

注:如果没有指定log_bin的话,缺省会使用主机名作为名字,如此一来一旦主机名发生改变,就会出问题,所以推荐指定log_bin(从服务器的relay_log存在一样的问题)。

注:sync_binloginnodb_flush_log_at_trx_commitinnodb_support_xa三个选项都是出于安全目的设置的,不是复制的必须选项,但如果没设置的话,一旦主服务器宕机,数据可能来不及写入磁盘,从而导致从服务器在复制过程中出现类似下面的错误:

  • Client requested master to start replication from impossible position

因为日志数据已经丢失了,所以此类问题基本上不能处理,只能重新安装同步从服务器。

接下来设置从服务器配置文件(缺省:/etc/my.cnf):

[mysqld]

server_id = 200
log_bin = mysql-bin
log_bin_index = mysql-bin.index
relay_log = mysql-relay-bin
relay_log_index = mysql-relay-bin.index
read_only = 1
skip_slave_start = 1
log_slave_updates = 1

注:如果用户有SUPER权限,则read_only无效。

注:有了skip_slave_start,除非使用START SLAVE命令,否则从服务器不会开始复制。

注:设置log_slave_updates,让从服务器记录日志,有助于在必要时把从切换成主。

下面最重要的步骤是如何克隆一份主服务器的数据:

如果数据库使用的是MyISAM表类型的话,可按如下方式操作:

shell> mysqldump --all-databases --master-data=1 > data.sql

注:master-data选项缺省会打开lock-all-tables,并写入CHANGE MASTER TO语句。

如果数据库使用的是InnoDB表类型的话,则应该使用single-transcation

shell> mysqldump --all-databases --single-transaction --master-data=1 > data.sql

有了数据文件,传输到从服务器上并导入:

shell> mysql < data.sql

当然,整个过程也可以利用管道符一次性操作:

shell> mysqldump --host=<MASTER_HOST> ... | mysql --host=<SLAVE_HOST> ...

因为我们开始设置了master-data=1,所以系统会自动设置参数:MASTER_LOG_FILE和MASTER_LOG_POS,我们还需要设置剩下的参数:

mysql> CHANGE MASTER TO
       MASTER_HOST='<MASTER_HOST>',
       MASTER_USER='<SLAVE_USER>',
       MASTER_PASSWORD='<SLAVE_PASSWORD>';

如果数据量很大的话,mysqldump会非常慢,此时直接拷贝数据文件能节省不少时间:

在拷贝之前要先锁定数据,然后再获得相关的日志信息(File & Position):

mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

接下来拷贝数据文件时,如果是MyISAM表类型的话,直接拷贝即可;如果是InnoDB表类型的话,一定要先停止MySQL服务再拷贝,否则拷贝文件可能无法使用。把拷贝的数据文件直接复制到从服务器的数据目录。

最后还需要再指定一下日志信息:

mysql> CHANGE MASTER TO
       MASTER_HOST='<MASTER_HOST>',
       MASTER_USER='<SLAVE_USER>',
       MASTER_PASSWORD='<SLAVE_PASSWORD>',
       MASTER_LOG_FILE='<File>',
       MASTER_LOG_POS=<Position>;

注:不要在my.cnf配置文件里设置MASTER_USER和MASTER_PASSWORD,因为最终生效的是CHANGE MASTER TO生成的master.info文件里的信息。

在主服务器上直接拷贝数据文件虽然很快,但需要锁表或者停止服务,这会影响线上服务。如果先前已经有了从服务器,那么可以用旧的从服务器做母本来克隆新的从服务器:

先在旧的从服务器上查询日志信息:

mysql> SHOW SLAVE STATUS;

我们需要的是其中的Relay_Master_Log_File & Exec_Master_Log_Pos。

然后在旧的从服务器上按照前面的方法得到数据,并在新的从服务器上还原。

接着在新的从服务器上设置日志信息:

mysql> CHANGE MASTER TO
       MASTER_HOST='<MASTER_HOST>',
       MASTER_USER='<SLAVE_USER>',
       MASTER_PASSWORD='<SLAVE_PASSWORD>',
       MASTER_LOG_FILE='<Relay_Master_Log_File>',
       MASTER_LOG_POS=<Exec_Master_Log_Pos>;

不管用那个方法,最后记得在从服务器上启动复制,并检查工作是否正常:

mysql> START SLAVE;
mysql> SHOW SLAVE STATUS;

如果IO线程和SQL线程都显示Yes,就可以感谢上帝了:

  • Slave_IO_Running: Yes
  • Slave_SQL_Running: Yes

如果显示No,则说明某些配置步骤有问题,请重新对照一遍前面所说的步骤。

故障

问题:主从复制不止何故停止了,我该怎么办?

答案:复制错误多半是因为日志错误引起的,所以首先要搞清楚是主日志错误还是中继日志错误,从错误信息里一般就能判断,如果不能可以使用类似下面的mysqlbinlog命令:

shell> mysqlbinlog <MASTER_BINLOG_FILE> > /dev/null
shell> mysqlbinlog <RELAY_BINLOG_FILE> > /dev/null

如果没有错误,则不会有任何输出,反之如果有错误,则会显示出来。

如果是主日志错误,需要手动找到正确的日志信息,重新CHANGE MASTER TO即可:

mysql> CHANGE MASTER TO
       MASTER_LOG_FILE='<GOOD_LOG_FILE>',
       MASTER_LOG_POS=<GOOD_LOG_POS>;
mysql> START SLAVE;

如果是中继日志错误,只要在从服务器使用SHOW SLAVE STATUS结果中的日志信息重新CHANGE MASTER TO即可,系统会抛弃当前的中继日志,重新下载:

mysql> CHANGE MASTER TO
       MASTER_LOG_FILE='<Relay_Master_Log_File>',
       MASTER_LOG_POS=<Exec_Master_Log_Pos>;
mysql> START SLAVE;

至于为什么使用的是Relay_Master_Log_File & Exec_Master_Log_Pos,参见概述。

有时候由于BUG或者在从服务器执行了写操作可能会造成键重复错误,错误信息如下:

  • Error ‘Duplicate entry …’ for key … on query

此时最好手动确认并删除从服务器上的无效数据,然后从主服务器复制正确数据,如果错误仍然不能解决,可以在从服务器使用SET GLOBAL sql_slave_skip_counter,如下:

mysql> SET GLOBAL sql_slave_skip_counter = 1;
mysql> START SLAVE;

注:如果有多个错误,可能需要执行多次(提醒:主从服务器数据可能因此不一致,遇到这样的情况可以使用pt-table-checksumpt-table-sync检查并修复从服务器数据)。

问题:主服务器宕机了,如何把从服务器提升会主服务器?

答案:在一主多从的环境总,需选择数据最新的从服务器做新的主服务器。如下图所示:

提升从服务器为主服务器

提升从服务器为主服务器

在一主(Server1)两从(Server2,、Server3)环境中,Server1宕机后,等到Server2和Server3把宕机前同步到的日志都执行完,比较Master_Log_File和Read_Master_Log_Pos就可以判断出谁快谁慢,因为Server2从Server1同步的数据(1582)比Server3从Server1同步的数据(1493)新,所以应该提升Server2为新的主服务器,那么Server3在CHANGE MASTER TO到Server2的时候应该使用什么样的参数呢?1582-1493=89,而Server2的最后的二进制日志位置是8167,所以答案是8167-89=8078。

技巧

主从服务器中的表可以使用不同的表类型。比如主服务器可以使用InnoDB表类型,提供事务,行锁等高级特性,从服务器可以使用MyISAM表类型,内存消耗少,易备份等优点。还有一个例子,一台主服务器如果同时带很多个从服务器的话,势必会影响其性能,此时可以拿出一台服务器作为从服务器代理,使用BLACKHOLE表类型,只记录日志,不写数据,由它带多台从服务器,从而提升性能。

主从服务器中的表可以使用不同的键类型。比如主服务器用InnoDB,键用VARCHAR的话节省空间,从服务器使用MyISAM,键用CHAR提高速度,因为MyISAM有静态表一说。

主从服务器中的表可以使用不同的索引。主服务器主要用来应付写操作,所以除了主键和唯一索引等保证数据关系的索引一般都可以不加,从服务器用来应付读操作,所以可以针对查询特征设置索引,甚至不同的从服务器可以针对不同的查询设置不同的索引。

工具

有一些优秀的工具可以让你的得到事半功倍的效果,详细内容请参考各自文档:

补充:PerconaSkySQL都提供了MySQL在线配置工具,使用起来非常方便。

补充:Yoshinori释出了MySQL-MHA项目,有助于提高MySQL主从复制的可靠性。

说明:本文参考了下面列出的书籍中相关的内容:

希望我的总结能让大家少走一些弯路。


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
6天前
|
tengine 关系型数据库 MySQL
Tengine、Nginx安装MySQL数据库命令教程
本指南详细介绍了在Linux系统上安装与配置MySQL数据库的步骤。首先通过下载并安装MySQL社区版本,接着启动MySQL服务,使用`systemctl start mysqld.service`命令。若启动失败,可尝试使用`sudo /etc/init.d/mysqld start`。利用`systemctl status mysqld.service`检查MySQL的服务状态,确保其处于运行中。通过日志文件获取初始密码,使用该密码登录数据库,并按要求更改初始密码以增强安全性。随后创建一个名为`tengine`的数据库,最后验证数据库创建是否成功以及完成整个设置流程。
|
15天前
|
关系型数据库 MySQL Linux
Linux 安装 mysql 【使用 tar.gz | tar.xz安装包-离线安装】
在Linux系统中使用tar.xz压缩包安装MySQL数据库的详细步骤。包括下载MySQL压缩包,解压到指定目录,创建mysql用户和组,设置目录权限,初始化MySQL,配置my.cnf文件,启动服务,以及修改root用户密码。此外,还提供了如何设置Windows远程登录MySQL服务器的方法。
Linux 安装 mysql 【使用 tar.gz | tar.xz安装包-离线安装】
|
1天前
|
弹性计算 关系型数据库 MySQL
Docker安装MySQL
这篇文章详细介绍了如何使用Docker安装MySQL数据库服务,包括拉取镜像、配置数据卷以及启动容器的步骤。
11 0
Docker安装MySQL
|
12天前
|
数据采集 中间件 关系型数据库
Mac系统通过brew安装mysql5.7后,启动报错的解决办法
Mac系统通过brew安装mysql5.7后,启动报错的解决办法
33 2
|
15天前
|
关系型数据库 MySQL Linux
Linux 安装 mysql【使用yum源进行安装】
这篇文章介绍了在Linux系统中使用yum源安装MySQL数据库的步骤,包括配置yum源、安装MySQL服务、启动服务以及修改root用户的默认密码。
Linux 安装 mysql【使用yum源进行安装】
|
1天前
|
自然语言处理 关系型数据库 MySQL
MySQL数据库使用Match语法需要安装什么插件吗?
【10月更文挑战第1天】MySQL数据库使用Match语法需要安装什么插件吗?
8 0
|
2天前
|
Ubuntu 关系型数据库 MySQL
Ubuntu 安装mysql和简单操作
Ubuntu 安装mysql和简单操作
20 0
|
3天前
|
存储 关系型数据库 MySQL
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB区别,适用场景
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景比较
|
3天前
|
SQL 关系型数据库 MySQL

热门文章

最新文章