MySQL5.6主从复制(读写分离)方案

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

一、前言:为什么MySQL要做主从复制(读写分离)?

通俗来讲,如果对数据库的读和写都在同一个数据库服务器中操作,业务系统性能会降低。

为了提升业务系统性能,优化用户体验,可以通过做主从复制(读写分离)来减轻主数据库的负载。

而且如果主数据库宕机,可快速将业务系统切换到从数据库上,可避免数据丢失。

二、MySQL主从复制(读写分离)和集群的区别:

我对MySQL也是刚开始研究,不是很专业。我的理解是:

1、主从复制(读写分离):一般需要两台及以上数据库服务器即可(一台用于写入数据,一台用于同步主的数据并用于数据查询操作)。

局限性:

(1)配置好主从复制之后,同一张表,只能对一个服务器写操作。如果在从上执行了写操作,而之后主也操作了这张表,或导致主从不同步;据说可以配置成主主方式,但我还没有研究到。

(2)主数据库服务器宕机,需要手动将业务系统切换到从数据库服务器。无法做到高可用性(除非再通过部署keepalive做成高可用方案)。

2、集群是由N台数据库服务器组成,数据的写入和查询是随机到任意一台数据库服务器的,其他数据库服务器会自动同步数据库的操作。

任何一台数据库宕机,不会对整个集群造成大的影响。

局限性:我经过测试才知道目前mysql集群版本(MySQL Cluster)只能对NDB存储引擎的数据进行集群同步,如果是INNODB或其他的MySQL存储引擎是不行的。这个也导致了我放弃了在业务系统中应用这种方案。

三、回归正题,接下来开始MySQL5.6.12的主从复制教程:

1、MySQL5.6开始主从复制有两种方式:基于日志(binlog);基于GTID(全局事务标示符)。

需要注意的是:GTID方式不支持临时表!所以如果你的业务系统要用到临时表的话就不要考虑这种方式了,至少目前最新版本MySQL5.6.12的GTID复制还是不支持临时表的。

所以此篇教程主要是告诉大家如何通过日志(binlog)方式做主从复制!

2、MySQL官方提供的MySQL Replication教程:

http://dev.mysql.com/doc/refman/5.6/en/replication.html

这个官方教程强烈建议大家阅读(需要一定的英语阅读能力哦!不行就google翻译后再阅读吧~)。

3、准备工作:

(1)配置MySQL主从复制(读写分离)之前,需要在主从两台服务器先安装好MySQL5.6。

(2)目前最新的MySQL5.6 GA版本是MySQL5.6.12(点此下载MySQL5.6.12源码包)。

个人推荐Linux(RedHat/CentOS 6.4)源码编译安装,具体可以看本站这篇教程:RedHat/CentOS源码编译安装MySQL5.6.12

(3)注意:

(a)如果你需要用于生产环境,安教程安装MySQL时不要急着做mysql启动操作。建议把mysql初始化生成的/usr/local/mysql/mysql.cnf删除,然后把你优化好的mysql配置文件my.cnf放到/etc下。

(b)建议主备两台服务器在同一局域网,主备两台数据库网络需要互通。

(4)我的环境:

主数据库IP:192.168.100.2

从数据库IP:192.168.100.3

4、修改主数据库的的配置文件:

1     [mysqld] 
2     server-id=1 
3     log-bin=mysqlmaster-bin.log 
4     sync_binlog=1 
5     #注意:下面这个参数需要修改为服务器内存的70%左右 
6     innodb_buffer_pool_size = 512M 
7     innodb_flush_log_at_trx_commit=1 
8     sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO 
9     lower_case_table_names=1 
10     log_bin_trust_function_creators=1

修改之后要重启mysql:

# /etc/init.d/mysql restart

附一个我已优化过的主数据库配置文件:点此下载

5、修改从数据库的的配置文件(server-id配置为大于1的数字即可):

1     [mysqld] 
2     server-id=2 
3     log-bin=mysqlslave-bin.log 
4     sync_binlog=1 
5     #注意:下面这个参数需要修改为服务器内存的70%左右 
6     innodb_buffer_pool_size = 512M 
7     innodb_flush_log_at_trx_commit=1 
8     sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO 
9     lower_case_table_names=1 
10     log_bin_trust_function_creators=1

修改之后要重启mysql:

 # /etc/init.d/mysql restart

附一个我已优化过的从数据库配置文件:点此下载

6、SSH登录到主数据库:

(1)在主数据库上创建用于主从复制的账户(192.168.100.3换成你的从数据库IP):

1     # mysql -uroot -p 
2     mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.100.3' IDENTIFIED BY 'repl';

(2)主数据库锁表(禁止再插入数据以获取主数据库的的二进制日志坐标):

mysql> FLUSH TABLES WITH READ LOCK;

(3)然后克隆一个SSH会话窗口,在这个窗口打开MySQL命令行:

1     # mysql -uroot -p 
2     mysql> SHOW MASTER STATUS; 
3     +------------------------+----------+--------------+------------------+-------------------+ 
4     | File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | 
5     +------------------------+----------+--------------+------------------+-------------------+ 
6     | mysqlmaster-bin.000001 |      332 |              |                  |                   | 
7     +------------------------+----------+--------------+------------------+-------------------+ 
8     1 row in set (0.00 sec) 
9     mysql> exit;

在这个例子中,二进制日志文件是mysqlmaster-bin.000001,位置是332,记录下这两个值,稍后要用到。

(4)在主数据库上使用mysqldump命令创建一个数据快照:

#mysqldump -uroot -p -h127.0.0.1 -P3306 --all-databases  --triggers --routines --events >all.sql 
# 接下来会提示你输入mysql数据库的root密码,输入完成后,如果当前数据库不大,很快就能导出完成。

(5)解锁第(2)步主数据的锁表操作:

mysql> UNLOCK TABLES;

7、SSH登录到从数据库:

(1)通过FTP、SFTP或其他方式,将上一步备份的主数据库快照all.sql上传到从数据库某个路径,例如我放在了/home/yimiju/目录下;

(2)从导入主的快照:

# cd /home/yimiju 
# mysql -uroot -p -h127.0.0.1 -P3306 < all.sql 
# 接下来会提示你输入mysql数据库的root密码,输入完成后,如果当前数据库不大,很快就能导入完成。

(3)给从数据库设置复制的主数据库信息(注意修改MASTER_LOG_FILE和MASTER_LOG_POS的值):

# mysql -uroot -p 
mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.2',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mysqlmaster-bin.000001',MASTER_LOG_POS=332; 
# 然后启动从数据库的复制线程: 
mysql> START slave; 
# 接着查询数据库的slave状态: 
mysql>  SHOW slave STATUS \G 
# 如果下面两个参数都是Yes,则说明主从配置成功! 
Slave_IO_Running: Yes 
Slave_SQL_Running: Yes

(4)接下来你可以在主数据库上创建数据库、表、插入数据,然后看从数据库是否同步了这些操作

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
存储 SQL 关系型数据库
Mysql高可用架构方案
本文阐述了Mysql高可用架构方案,介绍了 主从模式,MHA模式,MMM模式,MGR模式 方案的实现方式,没有哪个方案是完美的,开发人员在选择何种方案应用到项目中也没有标准答案,合适的才是最好的。
124 3
Mysql高可用架构方案
|
2月前
|
SQL 关系型数据库 MySQL
mysql主从复制概述和配置
【10月更文挑战第22天】MySQL 主从复制是一种将主服务器的数据复制到一个或多个从服务器的技术,实现读写分离,提高系统性能和可用性。主服务器记录变更日志,从服务器通过 I/O 和 SQL 线程读取并应用这些变更。适用于读写分离、数据备份和恢复、数据分析等场景。配置步骤包括修改配置文件、创建复制用户、配置从服务器连接主服务器并启动复制进程。
|
2月前
|
监控 关系型数据库 MySQL
深入了解MySQL主从复制:构建高效稳定的数据同步架构
深入了解MySQL主从复制:构建高效稳定的数据同步架构
130 1
|
2月前
|
负载均衡 监控 关系型数据库
MySQL 官宣:支持读写分离了!!
【10月更文挑战第8天】MySQL的读写分离功能显著提升了数据库性能、可用性和可靠性。通过将读写操作分配至不同服务器,有效减轻单个服务器负载,提高响应速度与吞吐量,并增强系统稳定性。此外,它还支持便捷的扩展方式,可通过增加只读服务器提升读操作性能。实现读写分离的方法包括软件层面(如使用数据库中间件)和硬件层面(使用独立服务器)。使用时需注意数据一致性、负载均衡及监控管理等问题。
133 0
|
2月前
|
存储 关系型数据库 MySQL
MySQL主从复制原理和使用
本文介绍了MySQL主从复制的基本概念、原理及其实现方法,详细讲解了一主两从的架构设计,以及三种常见的复制模式(全同步、异步、半同步)的特点与适用场景。此外,文章还提供了Spring Boot环境下配置主从复制的具体代码示例,包括数据源配置、上下文切换、路由实现及切面编程等内容,帮助读者理解如何在实际项目中实现数据库的读写分离。
MySQL主从复制原理和使用
|
1月前
|
关系型数据库 MySQL
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
64 5
|
1月前
|
关系型数据库 MySQL
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
mysql 5.7.x版本查看某张表、库的大小 思路方案说明
36 1
|
2月前
|
SQL 关系型数据库 MySQL
Mysql中搭建主从复制原理和配置
主从复制在数据库管理中广泛应用,主要优点包括提高性能、实现高可用性、数据备份及灾难恢复。通过读写分离、从服务器接管、实时备份和地理分布等机制,有效增强系统的稳定性和数据安全性。主从复制涉及I/O线程和SQL线程,前者负责日志传输,后者负责日志应用,确保数据同步。配置过程中需开启二进制日志、设置唯一服务器ID,并创建复制用户,通过CHANGE MASTER TO命令配置从服务器连接主服务器,实现数据同步。实验部分展示了如何在两台CentOS 7服务器上配置MySQL 5.7主从复制,包括关闭防火墙、配置静态IP、设置域名解析、配置主从服务器、启动复制及验证同步效果。
Mysql中搭建主从复制原理和配置
|
3月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
587 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
3月前
|
存储 SQL 关系型数据库
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案
MySQL如何进行分库分表、数据迁移?从相关概念、使用场景、拆分方式、分表字段选择、数据一致性校验等角度阐述MySQL数据库的分库分表方案。
474 15
一篇文章搞懂MySQL的分库分表,从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案