MySQL ·

MySQL5.6 安装与主从配置项目实战

什么是MySQL主从复制

简单来说,就是保证主SQL(Master)和从SQL(Slave)的数据是一致性的,向Master插入数据后,Slave会自动从Master把修改的数据同步过来(有一定的延迟),通过这种方式来保证数据的一致性,就是主从复制。

MySQL主从能解决什么问题

一、高可用

因为数据都是相同的,所以当Master挂掉后,可以指定一台Slave充当Master继续保证服务运行,因为数据是一致性的(如果当插入Master就挂掉,可能不一致,因为同步也需要时间),当然这种配置不是简单的把一台Slave充当Master,毕竟还要考虑后续的Salve同步Master,当然本文并不是将高可用的配置,所以这里就不多讲了。

二、负载均衡

因为读写分离也算是负载均衡的一种,所以就不单独写了,因为一般都是有多台Slave的,所以可以将读操作指定到Slave服务器上(需要代码控制),然后再用负载均衡来选择那台Slave来提供服务,同时也可以吧一些大量计算的查询指定到某台Slave,这样就不会影响Master的写入以及其他查询.

三、数据备份

一般我们都会做数据备份,可能是写定时任务,一些特殊行业可能还需要手动备份,有些行业要求备份和原数据不能在同一个地方,所以主从就能很好的解决这个问题,不仅备份及时,而且还可以多地备份,保证数据的安全

四、业务模块化

可以一个业务模块读取一个Slave,再针对不同的业务场景进行数据库的索引创建和根据业务选择MySQL存储引擎

五、高扩展(硬件扩展)

主从复制支持2种扩展方式

1、scale-up

向上扩展或者纵向扩展,主要是提供比现在服务器更好性能的服务器,比如增加CPU和内存以及磁盘阵列等,因为有多台服务器,所以可扩展性比单台更大

2、scale-out

向外扩展或者横向扩展,是指增加服务器数量的扩展,这样主要能分散各个服务器的压力

主从复制的缺点

一、成本增加

无可厚非的是搭建主从肯定会增加成本,毕竟一台服务器和两台服务器的成本完全不同,另外由于主从必须要开启二进制日志,所以也会造成额外的性能消耗

二、数据延迟

Slave从Master复制过来肯定是会有一定的数据延迟的,所以当刚插入就出现查询的情况,可能查询不出来,当然如果是插入者自己查询,那么可以直接从Master中查询出来,当然这个也是需要用代码来控制的

三、写入更慢

主从复制主要是针对读远大于写或者对数据备份实时性要求较高的系统中,因为Master在写中需要更多操作,而且只有一台写入的Master(因为我目前只会配置一台写入Master,最多就是有从Master的Slave,用来在Master挂掉后替换成Master,平时不对外进行服务),所以写入的压力并不能被分散,当然如果直接怎么解决这个问题的话,欢迎留言指教

复制方式

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

本文只涉及基于日志binlog的主从配置

复制原理

1、Master将数据改变记录到二进制日志(binary log)中,也就是配置文件log-bin指定的文件,这些记录叫做二进制日志事件(binary log events)

2、Slave通过I/O线程读取Master中的binary log events并写入到它的中继日志(relay log)

3、Slave重做中继日志中的事件,把中继日志中的事件信息一条一条的在本地执行一次,完成数据在本地的存储,从而实现将改变反映到它自己的数据(数据重放)

设置MySQL主从配置的优点
1、解决web应用系统,数据库出现的性能瓶颈,采用数据库集群的方式来实现查询负载;一个系统中数据库的查询操作比更新操作要多得多,通过多台查询服务器将数据库的查询分担到不同的查询服务器上从而提高查询效率。

2、MySQL数据库支持数据库的主从复制功能,使用主数据库进行数据的插入、删除与更新操作,而从数据库则专门用来进行数据查询操作,这样可以将更新操作和查询操作分担到不同的数据库上,从而提高了查询效率。

要求

1、主从服务器操作系统版本和位数一致

2、Master和Slave数据库的版本要一致

3、Master和Slave数据库中的数据要一致

4、Master开启二进制日志,Master和Slave的server_id在局域网内必须唯一

具体配置

硬件需求

台或两台以上安装了相同版本的MySQL

System OS    Server Name      主从服务器        IP Software Version
Centos 6.5    work67 主服务器-Master 192.168.1.67 MySQL 5.6.27
Centos 6.5    work68 主服务器-Slave 192.168.1.68 MySQL 5.6.27


配置 Master 主库

一、数据库安装方法点这里 配置详细参数说明点这里

二、配置 my.cnf

不同的系统my.cnf路径不同,所以我们只讲解牵扯修改的地方。添加配置

配置完成后重启mysql

关于复制过滤:
复制过滤可以让你只复制服务器中的一部分数据,有两种复制过滤:
1、在Master上过滤二进制日志中的事件
2、在Slave上过滤中继日志中的事件。
复制类型:
1、基于语句的复制
在Master上执行的SQL语句,在Slave上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高。一旦发现没法精确复制时,会自动选着基于行的复制
2、基于行的复制
把改变的内容复制到Slave,而不是把命令在Slave上执行一遍。从MySQL5.0开始支持
3、混合类型的复制
默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制

三、创建数据库-- 分别 在 主从服务器 上操作

四、创建数据同步用户-- 在 主服务器 Master 上操作

五、授权 slave 用户拥有 dbtest 数据库的所有权限

五、刷新数据库

这里主要是要授予用户REPLICATION SLAVE权限和REPLICATION CLIENT权限

配置 Slave 从库

一、数据库安装方法点这里     配置详细参数说明点这里

二、配置 my.cnf

如果Slave为其它Slave的Master时,必须设置bin_log.配置完成后重启mysql.

完成Master和Slave链接

一、初始化数据
保证Master和Slave除不同步的数据库,其他库的数据一致
二、查询Master状态
在Master中执行

记录下返回结果的File列和Position列的值

三、Slave中设置Master信息

在Slave中执行

四、查看主从同步状态

在Slave中执行命令

可看到 Slave IO State 为空, Slave IO RunningSlave SQL RunningNo,表明 Slave 还没有开始复制过程。相反Slave IO RunningSlave SQL RunningYes 表明已经开始工作了.

五、开启主从同步

在Slave中执行命令

查询查看主从同步状态,会发现 Slave IO RunningSlave SQL RunningYes 了,表明开启成功。

六、若开启同步出现类似如下报错

在Slave中执行命令

【经验】:以后用冷备份恢复实例后,在启动slave前,先进行reset slave清空下以前的旧信息
查询查看主从同步状态,会发现 Slave IO RunningSlave SQL RunningYes 了,表明开启成功。

主从同步出现一下错误:

Slave_IO_Running: Connecting
Slave_SQL_Running: Yes

解决方法:

导致 lave_IO_Running 为 connecting 的原因主要有以下 3 个方面:

1、网络不通

2、密码不对

3、pos 点不对

评论已关闭