【转载】mysql优化之主从复制与读写分离部署详细教程

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 读写分离与主从复制是提升mysql性能的重要及必要手段,大中型管理系统或网站必用之。一、什么是读写分离与主从复制先看图(图1 图片来源于网络) 如上图所示,当web server1/2/3要写入数据时,则向mysql db Master(主服务器)发出写入请求(即写入到master),如果要进入读操作时,则只向从服务器 mysql DB Slave1或2或3发出读取请求。

读写分离与主从复制是提升mysql性能的重要及必要手段,大中型管理系统或网站必用之。
一、什么是读写分离与主从复制
先看图

img_444efaaa8d66d4e9478f8f44931586c1.jpe

(图1 图片来源于网络)

  如上图所示,当web server1/2/3要写入数据时,则向mysql db Master(主服务器)发出写入请求(即写入到master),如果要进入读操作时,则只向从服务器 mysql DB Slave1或2或3发出读取请求。如此将原本读写在同一台服务器的工作量分摊到了一台负责写入,N台负责读取(大部分的网站都是读取请求远大于写入请求),从而从一定程度时实现了负载均衡(如果有N台从服务器,则由主从代理系统自动分配某个具体请求从哪一个从服务器读取)。
  读写分离是靠主从复制来实现的。即当一个数据写入到主服务器后,主服务器会将写入信息写入到binlog(二进制日志)里,同时同步(或异步或半同步)到从服务器里。从服务器根据主服务器传来的binlog,生成relay-log(中继日志),然后mysql服务器再用relay-log的信息将数据写入到数据库。
  如此做的优点除了实现负责均衡之外,还为我们保留了两份实时热备的数据binlog和relay-log。当服务器发生灾难时,我们可以用他们将数据恢复到任何一个时间点。

二、准备
1、在linux上安装好两台mysql服务器(mysql至少5.5以上),且两台服务器最好在一个机房(如此通过内网进行主从复制,速度远远大于通过外网。如果web服务器也在同一机房,则也可内网访问,还不用向ISP买带宽)。
假定主的IP为10.121.0.110,从的IP为10.121.0.220.

2、关闭两台服务器的防火墙(稍后再配置):

service iptables stop (这是centos6.5关闭方法,其他系统请问度娘)

3、如果你没有创建数据库,则进行如下操作
(1)在主服务器上登录mysql

mysql -uroot -p你的密码

(2)创建数据库
mysql> create database test1;
mysql> create database test2;
(3)如果已经有历史数据,则将历史数据备份成sql文件上传到主服务器,运行如下命令
mysql> source /disk/test1.sql;('/disk/test1.sql '换成你自己的路径即可)

三、主服务器配置
mysql已经提供了完美的读写分离与主从复制支持,我们只需要作如下设置即可。
1、配置my.cnf

vi /etc/my.cnf

(1)去掉log-bin=mysql-bin前的“#”号。这一行的意思是允许mysql使用binlog,同时为主从复制打开了大门。这是关键中的关键中的关键。
(2)如果有多个数据库,则添加如下行:
binlog-do-db=数据库名1
binlog-do-db=数据库名2
binlog-do-db=数据库名3
binlog-ignore-db=mysql(忽略mysql自身用的数据库)
(3)查看server-id是后的数字并记住。slave(从服务器)的server-id不能与该值重复。
(4)查看expire-log-day。binlog过期时间,默认为10天。你可以根据你硬盘空间大小及每天产生的数据量修改。如果你有冷备,则一般设成2-3天即可。

2、操作数据库
(1)在linux命令行下登录mysql

mysql -uroot -p(你的root密码)

(2)创建并授权一个帐号
mysql> GRANT REPLICATION SLAVE ON . TO 'slave_account'@'10.121.0.220' identified by '123456'
这句的意思是,允许从服务器'10.121.0.220 '使用'slave-account'及'123456'这个帐号密码对对主服务器的所有数据库(.)进行主从复制('REPLICATION SLAVE').
(3)重启mysql使上述配置生效

etc/init.d/mysql restart 或

service mysql restart

(4)查看主服务器binlog状态
mysql> show master stauts;


img_3fb95ba3f5dd7a20e54bcab67c1d1011.png

(图2)
记下File及Position下的值。以备在配置从服务器时使用。
注:File:当前binlog的文件名,每重启一次mysql,就会生成一个新binlog文件
Position:当前binlog的指针位置

三、从服务器配置
1、配置mysql.cnf

vi /etc/my.cnf

(1)修改server-id=2(该值不能与主服务器的server-id同。如果有多个从服务器,则该值顺延)
(2)添加如下两行:
relay-log-index=slave-relay-bin.index (中继日志的索引文件)
relay-log=slave-relay-bin (中继日志的文件前缀)
(3)重启mysql使上述配置生效

/etc/init.d/mysql restart

(4)登录mysql

mysql -uroot -p你的密码

(5)停止主从复制服务
mysql> stop slave
(6)主从关联配置
mysql> change master to
master_host='10.121.0.110', #主服务器IP
master_user='slave_account ', #主服务器访问从服务器的用户,即上述第三条第2小条第2子条所述帐号
master_password='123456', #主服务器访问从的密码,即上述第三条第2小条第2子条所述密码
master_log_file='mysql-bin.000008', #主服务器起始的binlog文件名,即图2的file
master_log_pos=107; #主服务器binlog起始位置,即图2的postion
上述每行都特别重要,错一个字符都不行
(7)启动从服务
mysql> start slave
(8) 查看从服务的状态,判断从服务是否生效
mysql> show slave status\G; (G参数为纵向显示结果)
会显示如下结果
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.110
Master_User: slave_account
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.00008
Read_Master_Log_Pos: 107
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.00008
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: 264
Relay_Log_Space: 409
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

如果Slave_IO_Running和Slave_SQL_Running都显示YES,则表示从与主的读写通讯正常、主动复制已经运行,则表明我们主从复制已经设置成功。如果出现故障,则看下节“故障排除”。
然后,你再在主服务器插入一条数据,看看从服务器相应数据库里有没有做相应更新。如果有,则大功告成。

**四、权限配置 **
虽然某些mysql版本不进行如下操作,可能也能正常运行,但我还是强列建议你设置。否则,必有安全之虑。
1、为master分配select、insert、update、delete权限
mysql>GRANT SELECT,INSERT,UPDATE,DELETE ON . TO '数据库的帐号'@'WEB服务器的IP' identified by '密码'。
注意,虽然master只负责写,但也必须有select权限。原因是使用事务时,需要从master查询,二是update/delete这些命令都需要使用select权限。
2、为slave分配select权限
mysql> GRANT SELECT ON . TO '数据库的帐号'@'WEB服务器的IP' identified by '密码';
这样设置也有一个弊端,就是当主宕机时,读写分离机制分主动将写操作转到从上来,这时,就会出现写不正常的情况。所以,slave要分配哪些权限,还看你自己选择。

五、防火墙配置
刚才我们为了调试方便,先关闭了防火墙,但这样做,非常很不安全。特别是数据库服务器,关系公司的身家性命,更不可小视。
主从复制的防火墙,还是比较好设置的,如果你只有一台web服务器,则只需要在主、从服务器的iptables里开放2个授权即可。先在主服务器执行如下命令:

iptables INPUT -s 10.121.0.220 -j ACCEPT (允许从服务器访问)

iptables INPUT -s 10.121.2.142 -j ACCEPT (允许web服务器访问)

service iptables save (保存上述规则)

service iptables restart (重启iptables)

在从中将第一条换成主的IP即可。
设置完后,再在从上用show slave status看看主从复制是否正常。

六、一些故障排除
设置过程中,可能会出现如下几个故障
(1) Slave_IO_Running: No
(2) Slave_IO_Running: Connect
(3)Last_IO_Error: error connecting to master 'slave-account@192.168.0.110:3306' - retry-time: 60 retries: 8640
其实上述故障,都是由IO不正常造成的,请从如下几个步骤着手检查。
a.配置完主或从的my.cnf后,是否重启了mysql服务。如果重启还不成功,则可以用reboot命令重启服务器后再试
b.change master那一步所填写的信息是否正确。
(4)Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
如上是说主、从使用了相同的server-id,进入my.cnf检查,改正之,然后重启mysql服务。

七、php设置
在php的数据库连接配置文件里作如下设置即可
//该行读写分离支持
'DB_DEPLOY_TYPE'=> 1,
'DB_RW_SEPARATE'=>true,
//主、从数据库的IP地址,前为主,后从
'DB_HOST' => '10.121.0.110,10.121,0.220',
// 数据库类型
'DB_TYPE' => 'mysql',
// 用户名,如果主和从的用户名相同,则 可省略一个
'DB_USER' => 'root root',
// 用户名,如果主和从密码相同,则可省略一个
'DB_PWD' => '123456 123456',
// 用户名,如果主和从数据库相同,则可省略一个
'DB_NAME' => 'test test'

来源:http://mp.weixin.qq.com/s/CVKSGYgmgqCW2j44pITl1A

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
16天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
990 7
Mysql 数据库主从复制
|
6天前
|
关系型数据库 MySQL Linux
Docker安装mysql详细教程, mysqld: Can‘t read dir of ‘/etc/mysql/conf.d/‘(报错已解决)
Docker安装mysql详细教程, mysqld: Can't read dir of '/etc/mysql/conf.d/' (Errcode: 2 - No such file or directory) 已解决
|
3天前
|
缓存 关系型数据库 MySQL
MySQL优化
【7月更文挑战第21天】MySQL优化
10 1
|
9天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
37 3
|
7天前
|
关系型数据库 MySQL 调度
MySQL高级功能与优化策略深度探索
MySQL高级功能与优化策略深度探索
|
12天前
|
关系型数据库 MySQL API
MySQL上亿数据查询优化:实践与技巧
MySQL亿级数据查询优化涉及索引设计、分区表、查询语句优化和数据库架构调整。例如,通过为常用查询列创建索引、使用EXPLAIN分析查询计划、避免全表扫描和SELECT *,以及采用垂直拆分、水平拆分和读写分离来提升性能。分区表能减少查询数据量,API接口测试可验证优化效果。
22 0
|
12天前
|
Oracle 关系型数据库 MySQL
【实操记录】MySQL二进制安装包部署
本文详细描述了采用二进制安装的各个步骤,具有较强的参考意义,基本可作为标准步骤实施
22 0
|
19天前
|
关系型数据库 MySQL Linux
Linux部署实战前言,MySQL在CentOS安装【单机软件】,MySQL的安装需要root权限,yum install mysql,systemctl enable mysqld开机自启的意思
Linux部署实战前言,MySQL在CentOS安装【单机软件】,MySQL的安装需要root权限,yum install mysql,systemctl enable mysqld开机自启的意思
|
20天前
|
SQL Oracle 关系型数据库
MySQL单表千万级数据查询优化大家怎么说(评论有亮点)
单表千万级数据是MySQL查询的一个坎,可能还不是天花板。“一个人走的慢,一群人走的快”,通过讨论可以发现MySQL千万数据的全貌大概是怎样的。
33 0
|
18天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中