MySQL主从复制与读写分离

一、MySQL主从复制概述

1.MySQL主从复制原理

MySQL的主从复制和读写分离紧密相连，首先部署主从复制，才能在此基础上进行读写分离。

2.MySQL支持的复制类型

基于语句的复制：在主服务器上执行的语句，在从服务器上执行同样语句。MySQL默认采用该语句，效率较高。

基于行的复制：把改变的内容复制过去，而不是把命令在服务器上执行一遍。

混合类型的复制：默认采用基于语句复制，发现语句无法复制时，就采用基于行的复制。

3.工作过程

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

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

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

二、MySQL 读写分离原理

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

三、实验分析

1.主从复制

主服务器

修改主配置文件
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=11
log-bin=master-bin
binlog-format=MIXED

备注：
binlog-format=MIXED   
混合类型的复制。默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。
binlog-format=ROW   
 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍，从 MySQL 5.0开始支持。  

binlog-format=STATEMENT   
 基于语句的复制。 在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。

[root@localhost ~]# systemctl restart mysqld

[root@localhost ~]# mysql -uroot -ppwd123
mysql> grant replication slave on *.* to myslave@'192.168.10.%' identified by 'pwd123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;        （更新权限）
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      603 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
 

从服务器1号
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
server-id=22    （在mysqld模块中添加server-id=22） 
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 -uroot -ppwd123
mysql> change master to master_host='192.168.10.101', master_user='myslave',                                   ->  master_password ='pwd123',
         -> master_log_file='master-bin.000001',master_log_pos=603;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

mysql> start slave;
mysql> show slave status\G           ##注意后面不要加分号

   *************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.101
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 603
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

从服务器2号  （操作同服务器1号）

[root@localhost ~]# vim /etc/my.cnf
server-id=33
[root@localhost ~]# systemctl restart mysqld

mysql> change master to master_host='192.168.10.101', master_user='myslave',master_password ='pwd123',master_log_file='master-bin.000001',master_log_pos=603;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.101
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 603
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

验证：
在主服务器上创建库，表。从服务器都会显示。

2.读写分离

<安装jdk> 
[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 jdk1.6]# java -version    查看版本，确定安装成功

<安装amoeba>
[root@localhost ~]#  mkdir  /usr/local/amoeba
[root@localhost ~]# ls
amoeba-mysql-binary-2.2.0.tar.gz  anaconda-ks.cfg  jdk-6u14-linux-x64.bin
[root@localhost ~]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz   -C  /usr/local/amoeba/

配置amoeba读写分离
（1）授权
在三个mysql服务器中开放权限给amoeba访问（只在master中即可，会复制到slave中）
mysql> grant all on *.* to test@'192.168.10.%' identified by 'pwd123';
Query OK, 0 rows affected, 1 warning (0.08 sec)

[root@localhost amoeba]# systemctl stop firewalld

（2）修改amoeba.xml
[root@localhost ~]# cd /usr/local/amoeba/conf
[root@localhost conf]# vi amoeba.xml
修改红色部分，此处设置的是mysql客户端连接amoeba时用的账号和密码

                                  <property name="user">amoeba</property>

                                        <property name="password">pwd123</property>

                          ..............
<property name="defaultPool">master</property>               
                <property name="writePool">master</property>
                <property name="readPool">slaves</property>
                
 

（3）编辑dbserver.xml  文件 （连接MySQL主从服务器）

[root@localhost amoeba]# vim conf/dbServers.xml 

<!-- mysql schema -->
                        <property name="schema">mysql</property>

                        <!-- mysql user -->
                        <property name="user">test</property>

                         mysql password
                        <property name="password">pwd123</property>
                                      ...................... .....................
 

<dbServer name="master"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.10.101</property>
                </factoryConfig>
        </dbServer>

        <dbServer name="slave1"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.10.102</property>
                </factoryConfig>
        </dbServer>

<dbServer name="slave2"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.10.103</property>
                </factoryConfig>
        </dbServer>

 <dbServer name="slaves" virtual="true">
                <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
                        <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
                        <property name="loadbalance">1</property>

                        <!-- Separated by commas,such as: server1,server2,server1 -->
                        <property name="poolNames">slave1,slave2</property>
                </poolConfig>
        </dbServer>

启动amoeba
[root@localhost amoeba]# bin/amoeba start &
备注：
当在前台运行某个作业时,终端被该作业占据;而在后台运行作业时,它不会占据终端。可以使用&命令把作业放到后台执行

[root@localhost amoeba]# netstat -anpt | grep java