nginxphpmysql分别技巧_MySQL读写分离原来如斯简单

问题

  如上图我们的运用程序和MySQL数据库直连，数据库的配置信息是写在我们的运用配置文件中（脑筋里要闪现出项目中的数据库配置），数据库只有一个实例来供应做事，之后我们的项目要发布到网络上供应做事，如果网站推广做的好就会有越来越多的用户利用，访问量就会很大，App可以通过nginx负载均衡等，我们的数据库还是单机在卖命？压力就会很大，一旦数据库宕机就GG了逆风翻盘已不存在，客户心里就骂骂咧咧的：垃圾软件卸载，所有的数据都存储在一个数据库中或者一个表中，数据越来越多怎么办？单表数据超过几百万条，几千万条上亿条怎么掩护？等等总结几点如下：

  通过上边问题相信大家可以看出实质，我们可以支配多台MySQL搭建集群，配置读和写操作不同的数据库，数据进行拆分，存储在多张表中，运用程序与数据库降落耦合，添加中间件，总结几点如下

上边我们说到数据库会涌现什么问题以及办理的思想，便是利用数据库中间件（MyCat）来做读写分离，分表分库等，下边我们就说说如何详细利用！

  随着业务的发展，数据库里的表越来越大，一两年往后，2千万、乃至上亿记录的表就会涌现了（一样平常可以大略认为表比较繁芜的时候，MySQL几百万上千万的时候，Oracle几千万的时候，就会涌现繁芜查询或变更有性能问题），这时候可能会导致繁芜的查询慢，插入和修正数据慢，修正表的DDL实行太慢导致无法修正列类型或者加索引或者加字段等等。
怎么办呢？这时候我们就可以读写分离，分表分库  如果我们的业务发展到了须要降落单库单表的压力、或者读写分离，而研发团队又不大，自己对这一块的技能积累不敷以自己开拓一些中间层代码去搞定问题，就须要考虑引入数据中间件了。

海内利用较多的是Mycat，免费，大略，功能强大，社区完善，后两者虽是官方供应的但是拿到海内就有点水土不服，毕竟海内网站的吞吐量是非常大的。
现在也有选择利用 sharding-sphere 的

  下载上传Linux解压安装在/usr/local目录下即可，版本为1.6，下图便是mycat目录构造，险些所有软件都是这些目录就不一个一个说了！

mycat紧张配置文件schema.xml：定义逻辑库，表、分片节点等内容rule.xml：定义分片规则server.xml：定义用户以及系统干系变量，如端口等一主一从读写分离

  单个数据库宕机会导致数据获取不到，数据库一样平常都是多个，而且我们项目中一样平常都是读得多写得少，比如我们利用淘宝看商品要比购买的点击次数多得多，我们为了提升项目并发量，数据库会实现读写分离，就好比火车站的入口和出口分开一样。
  我们先来个一主一从配置，主库卖力写入数据，从库卖力读取数据，从库和主库须要数据同步，可以利用MySQL(5.5版本之后)自带的主从复制实现，先搭建MySQL主从复制，再配置一主一从实现读写分离，一个主机卖力处理所有的写要求，一台从机卖力所有的读要求，架构图如下

Mysql主从复制事理

  主数据库会将所有的写操作写入到二进制文件（binlog）中，从数据库读取binlog日志再将读取到的binlog写入到中继日志（Relaylog）中，再通过SQL线程实行语句，实现主从复制数据同步，与Redis一样主数据库只能有一台，从数据库可以有多台，不同之处在于Redis是全量复制，而MySQL是从确认主从关系后开始复制，之前的数据是不同步的，读取二进制文件和写入中继日志都会发生IO以是会存在延时

主数据库配置(编辑/etc/my.cnf文件)

# Server IDserver-id =1# 启动二进制日志log-bin = mysql-bin# Mysql有一些系统库不须要复制，可以配置多个binlog-ignore-db = mysql binlog-ignore-db = sysbinlog-ignore-db = information_schemabinlog-ignore-db = performance_schema# 设置须要复制的数据库，这里设置了上边不复制项就可以不设置binlog-do-db = mycatdb# 设置logbin日志格式binlog_format = STATEMENT从数据库配置

# Server IDserver-id =2# 启用中继日志relay-log = mysql-relay重启Mysql查看状态，并且关闭防火墙

# 重启systemctl restart mysqld # 查看状态systemctl status mysqld# 关闭防火墙systemctl stop firewalld# 开机禁用systemctl disable firewalld# 查看防火墙状态systemctl status firewalld

# 创建用户名为slave，密码为123456grant replication slave on . to 'slave'@'%' identified by '123456';# 刷新flush privileges;# 查看master状态show master status;

记录下File和Position的值，到这里就不要操作主Mysql，防止做事器状态值变革，由于mysql主从复制是从主数据库当前状态开始复制

change master to master_host='主IP',master_user='上方我们创建的用户',master_password='上方密码',master_log_file='File',master_log_pos=Position;# 例如change master to master_host='192.168.11.201',master_user='slave',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=582;

如果之前环境搭建过主从复制会报错，运行stop slave;和restart master;

# 启动从做事start slave;# 查看从做事器状态show slave status\G;哎呦？出错了？

强调：Slave_IO_Running和Slave_SQL_Running都要为YES才行！
！
！

不慌

  程序员基本素养看日志，问题提示主从利用了相同的server UUID，这个UUID是在auto.cnf文件中，也便是你安装mysql时在my.cnf文件中配置的datadir路径下，输出之后创造确实是一样的

[root@stt201 data]# cat auto.cnf [auto]server-uuid=dbbcb5a4-7331-11ea-b078-000c29382f46[root@stt202 data]# cat auto.cnf [auto]server-uuid=dbbcb5a4-7331-11ea-b078-000c29382f46

盘它

  停滞从库的mysqld做事，删除auto.cnf文件，再启动数据库做事即可：

systemctl stop mysqld #停滞做事rm -rf auto.cnf #删除文件systemctl start mysqld #启动做事systemctl status mysqld #查看状态

一套创建库创建表插入数据连招安排

-- 创建、添加数据都是写，你说在哪弄？-- 创建库CREATE DATABASE mycatdb;-- 创建表CREATE TABLE IF NOT EXISTS mycatdb.tb_mycat(id INT NOT NULL, name VARCHAR(20) NOT NULL, PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8;-- 插入数据INSERT INTO mycatdb.tb_mycat(id,name) values(1,"添添"),(2,"小添"),(3,"晓添");-- 主库和从库查询数据若都可以正常显示，解释木得问题SELECT id,name FROM mycatdb.tb_mycat;

Mycat配置读写分离

分为以下两个步骤：

修正server.xml文件修正schema.xml文件修正server.xml文件

修正schema.xml文件

  删除<schema>标签间表信息，<datanode>标签只留一个，<dataHost>标签只留一个，<writeHost>，<readHost>只留一对

强调：schema中的name属性值是我们mycat记录的逻辑库的名字，区分大小写，dataNode标签的database属性值对应的是真实的物理数据库名字，大家一定要记住并区分开，我们通过操作Mycat的逻辑库，Mycat帮我们去操作真正的物理数据库，啊呦欧克~~~

dataHost标签中的balance属性配置读写分离类型，默认为0，实际生产过程中设置为1（主双从）或者3（单主单从）

0：不开启读写分离，所有读操作都发送到当前可用的writeHost上

1：全部的readHost与stand by writeHost参与SELECT 语句的负载均衡

2：所有的读操作随机在writeHost和readHost上分发

3：所有读要求随机分发到readHost上实行，writeHost不包袱读压力

启动Mycat吧

条件

安装JDK，并且配置环境变量，Mycat基于Java编写，须要JVM在mysql中创建mycat用户密码为123456(和我们server.xml文件中同等)mysql启动远程访问

-- 表示任意ip后可通过root用户密码123456访问GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;-- 刷新flush privileges;

sql都准备好，便是懂你~~~

启动办法

掌握台启动：到mycat/bin目录下运行 ./mycat console；推举利用，可以第一韶光看到日志方便定位问题后台启动：到mycat/bin目录下运行 ./mycat start；

如果看到图上红框内容解释mycat启动成功

Mycat上岸

登录后台管理窗口

用于管理掩护Mycat

mysql -umycat -p123456 -P 9066 -h 192.168.11.201

上岸数据窗口

用于通过Mycat查询数据，我们开拓是利用8066端口搞事情

mysql -umycat -p123456 -P 8066 -h 192.168.11.201

查看数据

-- 上岸mycatmysql -umycat -p123456 -P 8066 -h 192.168.11.201-- 查看数据库，看下图，查出来的是我们的逻辑库，区分大小写哦show databases;-- 利用逻辑库use MYCATDB;-- 查看库下所有表show tables;-- 查看数据SELECT FROM tb_mycat;

验证读写分离

-- 进入主mysql，添加一条数据，@@hostname为当前主机的名字-- 主数据库和从数据库的主机名不同，以是数据也不一样INSERT INTO mycatdb.tb_mycat(id,name) values (4,@@hostname);-- 我们上岸mycat查看数据，如果是从数据库的数据那么就证明成功SELECT FROM MYCATDB.tb_mycat;

呀呀呀！
咱们到这里就实现MySQL的主从复制，利用Mycat实现Mysql单主单从的读写分离，心里还真有点小激动呢！
到这里大家随着做出来，喝杯茶，下方留个言啥的岂不美哉！

双主双从读写分离

先容

  上边我们先容了一主一从实现读写分离，但是如果主库或者从库宕机就无法正常供应写和读的做事。
我们利用双主双从，master1和master2为主理事器，都有对应的slave，当Master1宕机时Master2卖力写要求，M1和M2互为备机，提高MySQL可用性，架构图如下

集群方案

编号

角色

IP地址

主机名

1

Master1

192.168.11.201

stt201

2

Slave1

192.168.11.202

stt202

3

Master2

192.168.11.203

stt203

4

Slave2

192.168.11.204

stt204

双主双从配置

Master1配置my.cnf文件

# Server IDserver-id =1# 启动二进制日志log-bin = mysql-bin# Mysql有一些系统库不须要复制，可以配置多个binlog-ignore-db = mysqlbinlog-ignore-db = sysbinlog-ignore-db = information_schemabinlog-ignore-db = performance_schema# 设置须要复制的数据库，这里设置了上边不复制项就可以不设置binlog-do-db = mycatdb# 设置logbin日志格式binlog_format = STATEMENT#步进值auto_imcrement。
一样平常有n台主MySQL就填nauto_increment_increment=2 #起始值。
一样平常填第n台主MySQL。
此时为第一台主MySQLauto_increment_offset=1# 在作为从数据库时，有写入操作也要更新二进制文件log-slave-updates = 1

Master2配置

# Server IDserver-id =3# 启动二进制日志log-bin = mysql-bin# Mysql有一些系统库不须要复制，可以配置多个binlog-ignore-db = mysqlbinlog-ignore-db = sysbinlog-ignore-db = information_schemabinlog-ignore-db = performance_schema# 设置须要复制的数据库，这里设置了上边不复制项就可以不设置binlog-do-db = mycatdb# 设置binlog日志格式binlog_format = STATEMENT#步进值auto_imcrement。
一样平常有n台主MySQL就填nauto_increment_increment=2 #起始值。
一样平常填第n台主MySQL。
此时为第2台主MySQLauto_increment_offset=2log-slave-updates = 1

Slave1配置

# Server IDserver-id =2# 启用中继日志relay-log = mysql-relay

Slave2配置

# Server IDserver-id =4# 启用中继日志relay-log = mysql-relay

启动

条件

先删除mycatdb库(为什么呢？)

drop database mycatdb;

主从都重启mysql做事

systemctl restart mysqldsystemctl status mysqld

在两台主理事器上创建并授权slave用户，我们上边创建过的

-- 查看是否有用户存在mysql> use mysql;mysql> select user from user;

取消主从关系，在slave上运行

mysql> stop slave;mysql> restart master;

配置主从和主主互备并且验证同步

-- 主从和主主互备与我们一主一从那个命令一样，不同的是我们须要在主节点运行一遍下方命令实现主主互备以是我们先查看Master的信息change master to master_host='主IP',master_user='上方我们创建的用户',master_password='上方密码',master_log_file='File',master_log_pos=Position;-- 启动从做事start slave;-- 查看状态,IO和SQL是否都为YESshow slave status\G;-- 在主理事器做写入操作，参考一主一从

贴出过程大家可供参考

到这我们就实现了Mysql双主双从配置，并且没有问题的跑了起来，真是不随意马虎啊，在这里我也是出了很多问题，踩了很多坑，大家按照步骤来弄，该当没有问题，涌现问题下方留言评论或者私信我都可以哦！
接下来我们就一口气利用Mycat实现双主双从的读写分离吧

Mycat配置读写分离

修正schema.xml文件

<?xml version="1.0"?><!DOCTYPE mycat:schema SYSTEM "schema.dtd"><mycat:schema xmlns:mycat="http://io.mycat/"><!--dataNode配置数据库地址，对应下方dataNode标签name属性值--><schema name="MYCATDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema><!--数据节点name：自定义唯一dataHost：数据主机database：物理数据库名，我这里有数据库名为mycatdb--><dataNode name="dn1" dataHost="host1" database="mycatdb" /><!--数据主机name：和datanode的dataHost同等balance：配置读写分离类型，单主单从配置3，双主双从配置1--><dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"><!--心跳机制，检测数据库是否可用--><heartbeat>select user()</heartbeat><!-- 写主机目前只配置一个url：配置数据库的ip:端口--><writeHost host="hostM1" url="192.168.11.201:3306" user="root" password="123456"><!--配置读主机--><readHost host="hostS1" url="192.168.11.202:3306" user="root" password="123456" /></writeHost><writeHost host="hostM2" url="192.168.11.203:3306" user="root" password="123456"><!--配置读主机--><readHost host="hostS2" url="192.168.11.204:3306" user="root" password="123456" /></writeHost></dataHost></mycat:schema>

1、配置两个writeHost标签，修正host，url，user，password四个属性为自己的

2、dataHost标签中writeType属性，默认值为0

0：表示所有写操作发送到第一个writeHost中，第一个挂了切换到第二个还生存的:

1：所有的写操作随机发送给配置的writeHost，1.5之后废弃不推举

3、dataHost标签中switchType属性，默认值为1

1：故障自动切换

2：故障不切换

3：基于Mysql主从同步状态决定是否切换

验证读写分离

  配置完mycat之后启动和登录mycat，下图前四个窗口是mysql窗口，在第一个窗口添加数据，以hostname为区分，数据成功添加并同步成功，在mycat窗口查询大家看到输出的数据时stt202和stt204这两个主机正是从做事器！

验证抗风险性

  我们仿照一下mysql宕机，停滞stt201做事器上mysql做事，通过mycat插入数据，通过select语句查询数据创造只有stt203和stt204上有数据，stt202是stt201的从做事器以是没有同步数据，但是stt203和stt204两台主机仍旧可以为我们供应正常的数据做事

故障规复

  这是运维大佬正在召唤师峡谷大展异彩，溘然接到报警书数据库崩了，没有什么问题是重启办理不明晰，我们现在重启一下stt201上的mysql做事，在利用mycat插入数据，创造四台做事器上数据同等，包括之前stt201宕机时插入的id为6的数据也在，是由于我们有主主互备，并且stt202是stt201的从做事。

总结

mysql单机会涌现单点故障问题，数据库宕机将造成数据无法读写，而且如果网站并发高，一个数据库实例无法支撑弘大的业务流量，我们项目中一样平常是读比写多，以是我们可以利用mysql的读写分离mysql读写分离这里演示了两种一主一从和双主双从，如果大家后边的n主n从可以参考配置修正即可，读写分离须要担保主库和从库数据同等这个可以依赖mysql5.5之后自带的主从复制实现一主一从模式存在的问题是如果主库或者从库宕机也无法实现正常的做事，以是我们在生产中会利用多主多从办法都供应备用机提高mysql的可用性mysql的主从复制可以单独实现，但是如果要实现读写分离则须要利用数据库中间件，我们这里利用的是MycatMycat我们须要安装和配置，以及一些主要配置参数大家要心里有数

这篇文章我们先说一下基于Mycat实现Mysql读写分离，下一篇安排MySQL分表分库

收藏即是学会，动动小手支持一下吧！

标签：MySQL数据库