MySQL主从复制和读写分离

主从复制

MySQL主从复制的原理

MySQL主从复制的类型

（1）基于语句的复制

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

（2）基于行的复制

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

（3）混合类型的复制

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

MySQL主从复制的工作过程

主（master）数据库启动 bin 二进制日志，这样会有一个 Dump 线程，这个线程是把主（master）数据库的写入操作都会记录到这个 bin 的二进制文件中。

然后从（slave）数据库会启动一个 I/O 线程（监控主服务器的二进制日志的变化），这个线程主要是把主（master）数据库的 bin 二进制文件读取到本地，并写入到中继日志（Relay log）文件中。

最后从（slave）数据库其他 SQL 线程，把中继日志（Relay log）文件中的事件再执行一遍，更新从（slave）数据库的数据，保持主从数据一致，重新写入数据库。

测试案例

环境搭建

准备三台安装了MySQL的服务器

配置master主服务器

配置mysql master主服务器

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

server-id=11           //修改
log-bin=master-bin      //修改
log-slave-updates=true    //添加    （可不用添加）
binlog-format = MIXED
log-slave-updates=true     //Slave可以是其他 Slave 的 Master，从而扩散 Master 的更新
binlog-ignore-db=test      //不记录指定的数据库的二进制日志  
replicate-ignore-db=test    #设置不需要同步的库
binlog_cache_size = 1M    #日志缓存的大小
expire_logs_days=3       #自动过期清理日志的天数

登录mysql创建从服务器用户，并授权

[root@localhost ~]# systemctl restart mysqld
[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
server-id       = 22            //修改，值不能和其他mysql服务器重复
relay-log=relay-log-bin           //添加（可不指定）
relay-log-index=slave-relay-bin.index   //添加（可不指定）

[root@localhost ~]# systemctl restart mysqld

登录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> start slave;
mysql> show slave status\G           ##注意后面不要加分号

…………
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 //这两个的参数必须为yes  

验证主从复制 

在主从服务器上分别查询数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

在主服务器上创建数据库

mysql> create database db_test;

Query OK, 1 row affected (0.00 sec)



mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_test            |
| mysql              |
| performance_schema |
| test               |
+--------------------+

5 rows in set (0.00 sec)

在从服务器上再次查询数据库，可以看到从服务器上也有了db_test数据库了

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db_test            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

 读写分离

搭建MySQL读写分离

在主从复制的基础上再开两台主机

在主机amoeba上安装java环境

[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安装成功

安装并配置amoeba

[root@localhost ~]# mkdir /usr/local/amoeba
[root@localhost ~]# tar zxvf 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       //有此提示表示成功

配置amoeba读写分离

在三个mysql服务器中开放权限给amoeba访问（只在master中即可，会复制到slave中）

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

在amoeba上配置amoeba.xml文件

[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>    //19行此处的注释去掉

编辑dbServer.xml文件

[root@localhost conf]# vim dbServers.xml 


<property name="user">test</property>         //26行

                        
<property name="password">123.com</property>  //29行，去掉注释符

<dbServer name="master"  parent="abstractServer">        //第45行
                <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        //若没有后三行则尝试重启服务器，再次运行依旧没有的话再检查配置文件
tcp6       0      0 127.0.0.1:43820         :::*                    LISTEN      1447/java           
tcp6       0      0 :::8066                 :::*                    LISTEN      1447/java           
tcp6       0      0 192.168.10.104:39326    192.168.10.103:3306     ESTABLISHED 1447/java           
tcp6       0      0 192.168.10.104:49698    192.168.10.102:3306     ESTABLISHED 1447/java           
tcp6       0      0 192.168.10.104:42440    192.168.10.101:3306     ESTABLISHED 1447/java

测试

（1）在客户机上

[root@localhost yum.repos.d]# yum -y install mariadb
[root@localhost yum.repos.d]# mysql -u amoeba -p 123456 -h 192.168.10.104 -P 8066

（2）在master服务器上创建表

mysql> stop slave;
MySQL [test]> use auth
MySQL [auth]> create table users (id int(10),name char(20));

（3）在两个slave服务器上

mysql> stop slave;

（4）在master服务器上

mysql> insert into users values ('1','zhangsan');

（5）在slave1上

mysql> use auth;
mysql>insert into zang values ('2','zhangsan');

（6）在slave2上

mysql> use auth;
mysql>insert into zang values ('3','zhangsan);

（7）在client上查询三次

mysql> use auth;
mysql> select * from users;

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

（8）在client上

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

（9）在master上

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

（10）在slave上

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