MySQL主从复制与读写分离

一：MySQL主从复制

1.1：概述

MySQL 的主从复制和 MySQL 的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。

• 基于语句的复制。在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。
• 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
• 混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。全局事务标识符 GTID（Global Transaction Identifier,GTID）
• 基于SQL语句的方式是最直接的方式，也是目前默认的复制方式，后来的三种是MySQL 5以后才出现的复制方式。

MySQL主从复制的工作流程

1、在每个事务更新数据完成之前，Master 将这些改变记录进二进制日志。写入二进制日志完成后，Master 通知存储引擎提交事务。

2、Slave 将 Master 的 Binary log 复制到其中继日志（Relay log）。首先，Slave 开始一个工作线程——I/O 线程，I/O 线程在 Master 上打开一个普通的连接，然后开始 Binlog dump process。Binlog dump process 从 Master 的二进制日志中读取事件，如果已经跟上 Master，它会睡眠并等待 Master 产生新的事件。I/O 线程将这些事件写入中继日志。

3、SQLslave thread（SQL 从线程）处理该过程的最后一步。SQL 线程从中继日志读取事件，并重放其中的事件而更新 Slave 数据，使其与 Master 中的数据保持一致。只要该线程与 I/O 线程保持一致，中继日志通常会位于 OS 的缓存中，所以中继日志的开销很小。 复制过程有一个很重要的限制，即复制在 Slave 上是串行化的，也就是说 Master 上的并行更新操作不能在 Slave 上并行操作。

1.2：配置MySQL主从复制

MySQL主服务器设置

[root@localhost ~]# vi /etc/my.cnf

在[mysqld]模块中修改或添加：

server-id=11           ##修改

log-bin=master-bin      ##修改

log-slave-updates=true    ##添加    （可不用添加）

binlog-format = MIXED

binlog-ignore-db=test      #不记录指定的数据库的二进制日志  
replicate-ignore-db=test    #设置不需要同步的库

binlog_cache_size = 1M    #日志缓存的大小
expire_logs_days=3       #自动过期清理日志的天数

以上参数在[mysqld]模块中设置。更改完成后重启MySQL

1：基于语句的复制。 在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。配置：

binlog_format = STATEMENT

2：基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从 MySQL 5.0开始支持，配置：binlog_format = ROW

3：混合类型的复制。默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制,配置：binlog_format = MIXED

[root@localhost ~]# mysql -u root -p

mysql> grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by '123456' ;

mysql> flush privileges;

mysql> show master status;

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| master-bin.000001 |      337 |              |                  |

+-------------------+----------+--------------+------------------+

1 row in set (0.01 sec)

从服务器设置

[root@localhost ~]# vi /etc/my.cnf

在[mysqld]模块中修改或添加：

server-id       = 22            ##修改，值不能和其他mysql服务器重复

relay-log=relay-log-bin            ##添加（可不指定）

relay-log-index=slave-relay-bin.index    ##添加（可不指定）

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

[root@localhost ~]# mysql -u root -p

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;

 mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.105
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 154
               Relay_Log_File: relay-log-bin.000005
                Relay_Log_Pos: 369
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes         
            Slave_SQL_Running: Yes                //IO线程和SQL线程都为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
  ......
1 row in set (0.00 sec)

 参数说明：

• master_host：主数据库的IP地址；
• master_port：主数据库的运行端口；
• master_user：在主数据库创建的用于同步数据的用户账号；
• master_password：在主数据库创建的用于同步数据的用户密码；
• master_log_file：指定从数据库要复制数据的日志文件，通过查看主数据的状态，获取File参数；
• master_log_pos：指定从数据库从哪个位置开始复制数据，通过查看主数据的状态，获取Position参数；
• master_connect_retry：连接失败重试的时间间隔，单位为秒。

 Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的Relay Log文件(relay-log-bin.xxxxxx)的最末端，并将读取到的Master端的master-bin的文件名和位置记录到master- info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个master-bin的哪个位置开始往后的日志内容，请发给我“

测试主从同步

在主服务器创建一个数据库看看是否可以同步到从服务器上

二：MySQL读写分离

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

Amoeba （变形虫）项目开源框架千2008 年发布一款Amoeba for MySQL 软件。这个软件致力千MySQ L 的分布式数据库前端代理层， 它主要为应用层访问MySQ L 的时候充当SQ L 路由功能， 并具有负载均衡、高可用性、SQL 过滤、读写分离、可路由到相关的目标数据库、可并发请求多台数据库。通过Amoeba 能够完成多数据源的高可用、负载均衡、数据切片的功能

1. 在主机Amoeba 上安装Java 环境

因为Amoeba 基千是jdk1.5 开发的， 所以官方推荐使用jdk1.5 或1.6 版本， 高版本不建议使用。

[root@localhost ~]# chmod +x jdk-6u14-linux-x64.bin
[root@localhost ~]# ./jdk-6u14-linux-x64.bin
[root@localhost ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@localhost ~]# vi /etc/profile
添加到最末尾：
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$JAVA_HOME/bin
export AMOEBA_HOME=/usr/local/amoeba/
export PATH=$PATH:$AMOEBA_HOME/bin
 
[root@localhost local]# source /etc/profile
[root@localhost local]# java -version            ##查询版本，确定java安装成功

2.安装并配置Amoeba 软件

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

3 . 配置Amoeba 读写分离， 两个Slave 读负载均衡

( 1 ) Master、Slave 1 、Slave2 中开放权限给Amoeba 访问

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

（2）在amoeba上配置amoeba.xml文件

此处设置的是mysql客户端连接amoeba时用的账号和密码

[root@localhost amoeba]# systemctl stop firewalld
 
[root@localhost ~]# cd /usr/local/amoeba/conf
[root@localhost conf]# vi amoeba.xml
......
<property name="user">amoeba</property>                 ##30行
 
             <property name="password">123456</property>               ##32行
......
 
 <property name="defaultPool">master</property>             ##115行
 
               
                <property name="writePool">master</property>             ##118行
                <property name="readPool">slaves</property>    ##119行此处的注释去掉
......

(3) 编辑dbServers.xml 配置文件。

[root@localhost amoeba)# vim conf/dbServers.xml
//省略部分内容
 
<!-- mysql user -->
<property name="user" >test＜／property>
<property name=" password">123.com</property>
</factoryConfig>
//省略部分内容

 ( 4 ) 配置无误后， 可以启动Amoeba 软件， 其默认端口为tcp 8066 

[root@localhost amoeba]# netstat -anpt | grep java
tcp6       0      0 :::8066                 :::*                    LISTEN      1754/java           
tcp6       0      0 127.0.0.1:65241         :::*                    LISTEN      1754/java           
tcp6       0      0 192.168.10.103:38612    192.168.10.105:3306     ESTABLISHED 1754/java           
tcp6       0      0 192.168.10.103:51318    192.168.10.106:3306     ESTABLISHED 1754/java           
tcp6       0      0 192.168.10.103:39548    192.168.10.100:3306     ESTABLISHED 1754/java   

（5）测试

由此验证， 已经实现了MySQL 读写分离。目前所有的写操作都全部在Master 主服务器上， 用来避免数据的不同步； 所有的读操作都分摊给了S lave 从服务器， 用来分担数据库压力。