小阿轩yx-案例：MySQL主从复制与读写分离

小阿轩yx-案例：MySQL主从复制与读写分离

案例分析

概述

实际生产环境中

• 如果对数据库读和写都在同一个数据库服务器中操作，无论在安全性、高可用性照旧高并发等各个方面都完全不能满足实际需求
  
• 一般都是通过主从复制（Master-Slave）同步数据
  
• 再通过读写分离来提升数据库并发负载能力举行部署与实施
  

案例前置知识点

MySQL 主从复制原理

• MySQL 主从复制和 MySQL 读写分离两者有细密接洽
  
• 首先部署主从复制，才能在此基础上举行数据的读写分离
  

MySQL 支持的复制类型

基于语句复制

• 在主服务器上实行的 SQL 语句，在从服务器上实行同样的语句
  

MySQL 默认采用基于语句的复制，服从比力高
基于行的复制

• 把改变的内容复制已往，而不是把下令在从服务器上实行一遍
  

混淆类型的复制

• 默认采用基于语句的复制，一旦发现基于语句无法精准复制时，就会采用基于行的复制
  

复制的工作过程

• 每个事物更新数据完成之前，Master 将这些改变记录进二进制日志。写入二进制日志完成后，Master 通知存储引擎提交事务
  
• Slave 将 Master 的 Binary log 复制到其中继日志（Relay log）
  
• SQL slave thread（SQL 从线程）处理该过程的最后一步
  

复制过程有一个很重要的限定，即复制在Slave上时串行化的，也就是说Master上的并行更新操作不能在 Slave 上并行操作
MySQL 读写分离原理

• 简单说，读写分离就是只在主服务器上写，只在从服务器上读。
  
• 让主数据库处理事务性查询，而数据库处理 select 查询。
  
• 数据库复制被用来把主数据库上事务性查询导致的变更同步到集群中的从数据库。
  

现在较为常见的 MySQL 读写分离分为两种

基于程序代码内部实现

在代码中根据 select、insert 举行路由分类，这类方法也是现在生产环境应用最广泛的
优点

• 性能较好
  
• 在程序代码中实现
  
• 不需要增长额外的设备作为硬件开支
  

缺点

• 需要开发人员来实现
  
• 运维人员无从动手
  

基于中心代理层实现

• 一般位于客户端和服务器之间，代理服务器接到后端请求后通过判断转发到后端数据库
  

两个代表性程序
MySQL-Proxy

• 为 MySQL 开源项目
  
• 通过自带的 lua 脚本举行 SQL 判断
  

（注：MySQL 官方不发起将 MySQL-Proxy用到生产环境）
Amoeba

• 由陈思儒用java语言举行开发
  
• 作者曾就职于阿里巴巴担任首席工程师（现已去职）
  
• 阿里巴巴将其用于生产环境
  

缺点

• 不支持事务
  
• 不支持存储过程
  

案例

搭建 MySQL 主从复制

需求
通过 Amoeba 实现 MySQL 数据库请求的读写分离
关闭全部服务器的 ffirewalld

1. [root@localhost ~]# setenforce 0
2. [root@localhost ~]# systemctl stop firewalld

复制代码

建立时间同步环境

主节点搭建时间同步服务器
安装NTP

1. [root@localhost ~]# yum -y install ntp

复制代码

从服务器选择时间与主机同步

设置 NTP

1. [root@localhost ~]# vim /etc/ntp.conf
2. //添加如下两行
3. server 127.127.1.0
4. fudge 127.127.1.0 stratum 8

复制代码

重启服务

1. [root@localhost ~]# systemctl restart mysqld
2. [root@localhost ~]# systemctl enable ntpd

复制代码

登录 MySQL 程序，给从服务器授权 

1. [root@localhost ~]# mysql -uroot -ppwd123
2. mysql> grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123456' ;
3. mysql> flush privileges;
4. mysql> show master status;
5. +-------------------+----------+--------------+------------------+
6. | File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
7. +-------------------+----------+--------------+------------------+
8. | master-bin.000001 |      337 |              |                  |
9. +-------------------+----------+--------------+------------------+
10. 1 row in set (0.01 sec)

复制代码

设置从服务器

1. [root@localhost ~]# vim /etc/my.cnf
3. 在[mysqld]模块中修改或添加：
4. ##修改，值不能和其他mysql服务器重复
5. server-id = 22
6. ##添加（可不指定）
7. relay-log=relay-log-bin
8. ##添加（可不指定）
9. relay-log-index=slave-relay-bin.index

复制代码

--relay-log=name    中继日志的文件的名字
 --relay-log-index=name      MySQL slave 在启动时需要检查relay log index 文件中的relay log信息，此处定义该索引文件的名字
重启服务

1. [root@localhost ~]# systemctl restart mysqld

复制代码

登录MySQL，设置同步

1. [root@localhost ~]# mysql -uroot -ppwd123
2. mysql> change master to master_host='192.168.10.101',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=337;
3. Query OK,0 rows affected,2 warnings (0.05 sec)

复制代码

启动同步

1. mysql> start slave;

复制代码

注：如果背面加了分号，显示的最后一行会提示ERROR: No query specified，当然，这没有任何影响

查看 Slave 状态，确保以下两个值为 YES

1. ##注意后面不要加分号
2. mysql> show slave status\G
3. *********1.row*********
4. //省略部分内容
5. ...
6. Slave_IO_Running: Yes
7. Slave_SQL_Running: Yes
8. //省略部分内容
9. ...
10. 1 row in set (0.00 sec)

复制代码

验证主从复制
在主从服务器上分别查询数据库

1. [root@localhost ~]# mysql -uroot -ppwd123
2. mysql> show databases;
3. +--------------------+
4. | Database           |
5. +--------------------+
6. | information_schema |
7. | mysql              |
8. | performance_schema |
9. | test               |
10. +--------------------+
11. 4 rows in set (0.00 sec)

复制代码

在主服务器上创建数据库

1. mysql> create database test;
2. Query OK, 1 row affected (0.00 sec)
4. mysql> show databases;
5. +--------------------+
6. | Database           |
7. +--------------------+
8. | information_schema |
9. | db_test            |
10. | mysql              |
11. | performance_schema |
12. | test               |
13. +--------------------+
14. 5 rows in set (0.00 sec)

复制代码

在从服务器上再次查询数据库，显示数据库相同，则主从复制乐成

1. mysql> show databases;
2. +--------------------+
3. | Database           |
4. +--------------------+
5. | information_schema |
6. | db_test            |
7. | mysql              |
8. | performance_schema |
9. | test               |
10. +--------------------+
11. 5 rows in set (0.00 sec)

复制代码

扩展

主主复制

• 将一个 slave1服务器作为另一台 slave2的master
  

在slave1 上修改my.cnf

1. ## 在[mysqld]模块添加
2. server-id=11
3. log-bin=master-bin
4. log-slave-updates=true

复制代码

重启mysql

1. [root@localhost ~]# systemctl restart mysqld

复制代码

在slave1上实行以下下令创建一个授权用户，用于在slave2上链接slave1

1. mysql> grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123456' ;
2. mysql> flush privileges;
3. mysql> show master status;

复制代码

搭建 Mysql 读写分离

Amoeba(变形虫)

• 开源框架项目
  
• 于 2008 年发布一款 Amoeba for MySQL 软件。
  
• 这个软件致力于 MySQL的分布式数据库前端代理层
  
• 主要为应用层访问 MySQL 的时间充当SQL路由功能
  

上风

• 具有负载均衡
  
• 高可用性
  
• SQL过滤
  
• 读写分离
  
• 可路由到相关的目标数据库
  
• 可并发请求多台数据库
  

通过 Amoeba 能够完成多数据源以下功能

• 高可用
  
• 负载均衡
  
• 数据切片
  

现在 Amoeba 已在很多企业的生产线上利用
在主机amoeba上安装java环境

1. [root@localhost ~]# chmod +x jdk-6u14-linux-x64.bin
2. ## 根据提示按 Enter 键完成即可
3. [root@localhost ~]# ./jdk-6u14-linux-x64.bin
4. [root@localhost ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6

复制代码

1. ## 增加一下配置
2. [root@localhost ~]# vim /etc/profile
3. ## 添加到最末尾
4. export JAVA_HOME=/usr/local/jdk1.6
5. export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
6. export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$JAVA_HOME/bin
7. export AMOEBA_HOME=/usr/local/amoeba/
8. export PATH=$PATH:$AMOEBA_HOME/bin

复制代码

1. [root@localhost local]# source /etc/profile
2. ## 查询版本，确定java安装成功
3. [root@localhost local]# java -version
4. java version "1.6.0 14"
5. Java(TM) SE Runtime Environment (build 1.6.0 14-b08)
6. Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

复制代码

（Java 环境已设置乐成）
安装并设置 amoeba

1. [root@localhost local]# mkdir /usr/local/amoeba
2. [root@localhost ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
3. [root@localhost ~]# chmod -R 755 /usr/local/amoeba/
4. [root@localhost ~]# /usr/local/amoeba/bin/amoeba
5. ## 有此提示表示成功
6. amoeba start|stop

复制代码

设置 amoeba 读写分离，两个 Slave 读负载均衡
Master、Slave1、Slave2三个mysql服务器中开放权限给amoeba访问（只在master中即可，会复制到slave中）

1. mysql> grant all on *.* to test@'192.168.10.%' identified by '123.com';

复制代码

在amoeba上设置amoeba.xml文件

1. [root@localhost ~]# cd /usr/local/amoeba/conf
2. [root@localhost conf]# vim amoeba.xml
4. ## 修改带有注释的行部分，此处设置的是mysql客户端连接amoeba时用的账号和密码
5. <property name="authenticator">
6.         <bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
7.              ##30行
8.              <property name="user">amoeba</property>
9.              ##32行
10.              <property name="password">123456</property>
11.          </property>
13.         .......略......
15. <queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
16.                 <property name="LRUMapSize">1500</property>
17.                  ##115行
18.                 <property name="defaultPool">master</property>
19.                 ##118行
20.                 <property name="writePool">master</property>
21.                 ##119行此处的注释去掉
22.                 <property name="readPool">slaves</property>
23.                 <property name="needParse">true</property>
24.         </queryRouter>

复制代码

编辑 Server.xml 文件

1. [root@localhost conf]# vim dbServers.xml
2. 修改（注意去掉注释），slave2的复制一个slave1
3.      <!-- mysql user -->
4.                    ##26行
5.                   <property name="user">test</property>
6.                    ##29行，去掉注释符
7.                   <property name="password">123.com</property>
8.                 </factoryConfig>
10.                 ......略......
11.    
12.     ##45行
13.   <dbServer name="master"  parent="abstractServer">
14.                 <factoryConfig>
15.                         <!-- mysql ip -->
16.                         ##48行
17.                         <property name="ipAddress">192.168.1.101</property>            
18.                 </factoryConfig>
19.         </dbServer>
20.             ##52行
21.         <dbServer name="slave1"  parent="abstractServer">
22.                 <factoryConfig>
23.                         <!-- mysql ip -->
24.                         ##55行
25.                         <property name="ipAddress">192.168.1.102</property>
26.                 </factoryConfig>
27.         </dbServer>
29.         <dbServer name="slave2"  parent="abstractServer">
30.                 <factoryConfig>
31.                         <!-- mysql ip -->
32.                         <property name="ipAddress">192.168.1.103</property>
33.                 </factoryConfig>
34.         </dbServer>
35.         ##59行
36.         <dbServer name="slaves" virtual="true">
37.                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
38.                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
39.                         <property name="loadbalance">1</property>
40.                         <!-- Separated by commas,such as: server1,server2,server1 -->
41.                         ##65行
42.                         <property name="poolNames">slave1,slave2</property>         
43.                 </poolConfig>
44.         </dbServer>

复制代码

启动 amoeba 软件

1. [root@localhost ~]# cd /usr/local/amoeba/
2. [root@localhost amoeba]# bin/amoeba start&

复制代码

注：当在前台运行某个作业时,终端被该作业占据;而在背景运行作业时,它不会占据终端。可以利用&下令把作业放到背景实行
如果能看到 8066 和 3306端口，证明 amoeba 是正常开启

1. [root@localhost amoeba]# netstat -anpt | grep java
2. tcp6 0    0 127.0.0.1:51388    ...*    LISTEN    31083/java
3. tcp6 0    0 ：：：8066          ...*    LISTEN    31083/java
4. tcp6 0    0 192.168.8.100:58748    192.168.8.139:3306 ESTABLISHED 31083/java
5. tcp6 0    0 192.168.8.100:37810    192.168.8.134:3306 ESTABLISHED 31083/java
6. tcp6 0    0 192.168.8.100:56066    192.168.8.136:3306 ESTABLISHED 31083/iava

复制代码

测试
在 client 主机上

1. [root@localhost ~]# yum -y install mysql

复制代码

通过代理访问 MySQL 

1. [root@localhost ~]# mysql -u amoeba -p 123456 -h 192.168.10.104 -P 8066
2. ## 密码：123456
3. Enter password:
4. MySQL [(none)]>

复制代码

若在连接“192.168.1.110”时报如下错误

1. MySQL [(none)]>show databases;
2. ERROR 2006 (HY000): MySQL server has gone away
3. No connection. Trying to reconnect...
4. 1437053119Connection id:
5. Current database:*** NONE ***
6. ERROR 2013 (HY000): Lost connection to MySQL server during query
7. MySQL [(none)]>

复制代码

同时，在 Amoeba 的服务器上面有如下报错日志

1. amoeba Could not create a validated object, cause: ValidateObject failed

复制代码

是因为 dbServers.xm 中的用户，需要在主从机上分配权限。
同时注意该文件中

1. <!-- mysql schema --><property name="schema">test</property>

复制代码

test数据库肯定是要存在的。
在 Master、Slave1 和 Slave2 上面创建 test 数据库,就可以办理此标题。 
在 master 服务器上创建表

1. mysql> stop slave;
2. MySQL [test]> use auth
3. MySQL [auth]> create table users (id int(10),name char(20));
4. Query Ok, 0 rows affected (0.16 sec)

复制代码

分别在两台服务器上实行操作

1. mysql> stop slave;

复制代码

在主服务器上

1. mysql> insert into users values ('2','zhangsan');
2. Query OK,1 rows affected (0.06 sec)

复制代码

从服务器同步了表，手动插入其它内容

1. slave1:
2. mysql> use auth;
3. mysql>insert into users values ('2','zhangsan');
5. slave2:
6. mysql> use auth;
7. mysql> insert into users values ('3','zhangsan);

复制代码

在客户机上查询3次

1. mysql> use auth;
2. mysql> select * from users;

复制代码

对比三次的输出，验证读操作，发现没有在master写入的数据，而slave上写的能查到
在客户机上

1. mysql> use auth;
2. mysql>insert into users values ('4','zhangsan');
3. ##发现在client上查询不到自己写的数据
4. mysql> select * from users;

复制代码

在主服务器上

1. ##能查到在client上写入的数据，说明写操作在master上
2. mysql> select * from users;

复制代码

在从服务器上

1. ##发现没有数据，说明写入的操作是在master上
2. mysql> select * from users;

复制代码

由此验证，已经实现了 MySQL读写分离
现在全部的写操作都全部在 Master 主服务器上，用来避免数据的不同步;全部的读操作都分摊给了 Slave 从服务器，用来分担数据库压力。
   小阿轩yx-案例：MySQL主从复制与读写分离

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。