MySQL主从复制与读写分离

MySQL主从复制与读写分离

主从复制原理

MySQL的复制类型

基于语句的复制

基于行的复制

混合类型复制

MySQL主从复制的工作过程

为什么要复制？

保证数据完整性

谁复制谁？

salve角色复制master角色的数据

数据放在哪？

二进制日志文件中mysql-bin.00000x—》纪录完整sql，slave复制二进制日志到本地节点，保存为中继日志文件方式，最后基于这个中继日志，进行恢复操作，将执行的sql同步执行在自己的数据库内部，最终达到msater数据一致性

主从复制简化：

主从复制核心部分就是两个日志三个线程（高版本的mysql以及异步复制、半同步复制、全同步复制三种模式）

主从复制原理：

两个日志：二进制日志 中继日志

三个线程：master dump线程和salved的i/o线程、SQL线程 三个线程如何工作以达成一致

主要原理：master将数据保存在二进制日志中，i/o向dump发出同步请求，dump将数据发送给i/o线程，i/o写入本地的中继日志，SQL线程读取本地中继日志数据，同步到自己数据库中，完成同步。

mysql主从复制延迟

master服务器高并发，形成大量事务

网络延迟

主从硬件设备导致cpu主频、内存io、硬盘io

本来就不是同步复制、而是异步复制

从库优化Mysql参数。比如增大innodb_buffer_pool_size，让更多操作在Mysql内存中完成，减少磁盘操作。从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了ilo方面性。从库使用SSD磁盘

网络优化，避免跨机房实现同步

mysql有几种同步方式

异步复制

异步master完成后返回给客户端

主库将更新写入Binlog日志文件后，不需要等待数据更新是否已经复制到从库中，就可以继续处理更多的请求。Master将事件写入binlog，但并不知道Slave是否或何时已经接收且已处理。在异步复制的机制的情况下，如果Master宕机，事务在Master上已提交，但很可能这些事务没有传到任何的Slave上。假设有Master->Salve故障转移的机制，此时Slave也可能会丢失事务。MySQL复制默认是异步复制，异步复制提供了最佳性能。

同步复制

主库将更新写入Binlog日志文件后，需要等待数据更新已经复制到从库中，并且已经在从库执行成功，然后才能返回继续处理其它的请求。同步复制提供了最佳安全性，保证数据安全，数据不会丢失，但对性能有一定的影响。

半同步复制

主库提交更新写入二进制日志文件后，等待数据更新写入了从服务器中继日志中，然后才能再继续处理其它请求。该功能确保至少有1个从库接收完主库传递过来的binlog内容已经写入到自己的relay log里面了，才会通知主库上面的等待线程，该操作完毕。

MySQL 5.5版本之后引入了半同步复制功能，主从服务器必须安装半同步复制插件，才能开启该复制功能。如果等待超时，超过rpl_semi_sync_master_timeout参数设置时间(默认值为10000，表示10秒)，则关闭半同步复制，并自动转换为异步复制模式。当master dump线程发送完一个事务的所有事件之后，如果在rpl_semi_sync_master_timeout内，收到了从库的响应，则主从又重新恢复为增强半同步复制。ACK(Acknowledge character)即是确认字符。

增强半同步复制

增强半同步是在MySQL 5.7引入，其实半同步可以看成是一个过渡功能，因为默认的配置就是增强半同步，所以，大家一般说的半同步复制其实就是增强的半同步复制，也就是无损复制。

增强半同步和半同步不同的是，等待ACK时间不同rpl_semi_sync_master_wait_point = AFTER_SYNC(默认)半同步的问题是因为等待ACK的点是Commit之后，此时Master已经完成数据变更，用户已经可以看到最新数据,当Binlog还未同步到Slave时，发生主从切换，那么此时从库是没有这个最新数据的，用户看到的是老数据。

增强半同步将等待ACK的点放在提交Commit之前，此时数据还未被提交，外界看不到数据变更，此时如果发送主从切换，新库依然还是老数据，不存在数据不一致的问题。

mysql应用场景

mysql数据库主要性能是读和写，一般场景来说读请求更多

根据主从复制可以演变成读写分离，因为读写分离基于主从复制，使用读写分离从而解决高并发问题

主数据库处理事务性查询，从数据库处理select查询

数据库复制用于将事务性查询的变更同步到集群中的从数据库

主从复制实验

1.master服务器配置

1.安装ntp、修改配置文件

[root@nfs local]# yum install ntp -y
[root@nfs local]# vim /etc/ntp.conf
[root@nfs local]# yum -y install ntpdate ntp
[root@nfs local]# ntpdate ntp.aliyun.com
23 Jul 16:31:31 ntpdate[19124]: adjust time server 203.107.6.88 offset -0.003582 sec
[root@nfs local]# vim /etc/ntp.conf
restrict 127.0.0.1
restrict ::1
fudge 127.127.1.0 stratum 10   添加
server 127.127.1.0  添加

2.开启服务并关闭防火墙和安全机制

[root@nfs local]# systemctl start ntpd
[root@nfs local]# systemctl stop firewalld.service 
[root@nfs local]# setenforce 0

1.slave服务器配置

1.安装ntp服务

[root@localhost local]# yum install ntp ntpdate -y

2.开启服务并关闭防火墙和安全机制

[root@localhost local]# systemctl start ntpd
[root@localhost local]# systemctl stop firewalld.service 
[root@localhost local]# setenforce 0

3.时间同步到master服务器

[root@localhost local]# ntpdate 192.168.116.10
23 Jul 16:58:29 ntpdate[63138]: the NTP socket is in use, exiting

4.两台slave服务器配置相同，设置计划任务

[root@localhost local]# crontab -e
*/10 * * * * /usr/sbin/ntpdate 192.168.116.10

3.配置主从同步

1.配置master服务器配置文件

[root@nfs local]# vim /etc/my.cnf
log_bin=master-bin
log_slave-updates=true
server-id = 1
[root@nfs local]# systemctl restart mysqld.service 
重启服务
进入数据库，给从服务器做一个授权
mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.116.%' IDENTIFIED BY 'abc23';
Query OK, 0 rows affected, 1 warning (0.00 sec)
刷新授权表
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
查看master服务器状态
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      604 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

2.配置slave服务器

[root@localhost local]# vim /etc/my.cnf
max_allowed_packet=16M
log-bin=master-bin
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
server-id = 2
退出重启两台从服务器
[root@localhost local]# systemctl restart mysqld
进入数据库开启slave服务器功能

mysql> change master to master_host='192.168.116.10',master_user='myslave',master_password='abc23',master_log_file='master-bin.000001',master_log_pos=604;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
开启slave功能
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
查看slave服务器状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.116.10
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 604
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 321
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

4.测试主从复制

1.在主服务器创建一个数据库

mysql> create database kkk;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| kkk                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

2.在从服务器上查看数据库列表

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bdqn               |
| kkk                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

读写分离试验

Amoeba服务器配置

虚拟机服务环境
Master服务器:192.168.116.10
slave1服务器:192.168.116.11
Slave2服务器:192.168.116.13
Amoeba服务器:192.168.116.14 jdk1.6、Amoeba
客户端服务器:192.168.116.12

[root@web1 opt]# ls
jdk-6u14-linux-x64.bin  nginx-1.12.2  nginx-1.12.2.tar.gz  rh
[root@web1 opt]# cp jdk-6u14-linux-x64.bin /usr/local/
[root@web1 opt]# cd /usr/local/
[root@web1 local]# chmod +x jdk-6u14-linux-x64
[root@web1 local]# chmod +x jdk-6u14-linux-x64.bin 

./jdk-6u14-linux-x64.bin
Please enter "yes" or "no".
Do you agree to the above license terms? [yes or no]

Please enter "yes" or "no".
Do you agree to the above license terms? [yes or no]
yes
开始解压
Press Enter to continue.....
enter
[root@web1 local]# java -version
openjdk version "1.8.0_131"
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed mode)
修改配置文件
[root@web1 local]# mv jdk1.6.0_14/ /usr/local/jdk1.6

[root@web1 local]# vim /etc/profile
 76 unset -f pathmunge
 77 export JAVA_HOME=/usr/local/jdk1.6
 78 export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
 79 export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
 80 export AMOEBA_HOME=/usr/local/amoeba
 81 export PATH=$PATH:$AMOEBA_HOME/bin
刷新服务
[root@web1 local]# source /etc/profile
查看版本
[root@web1 local]# java -version
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

安装 Amoeba软件

[root@web1 local]# mkdir /usr/local/amoeba
[root@web1 amoeba]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@web1 amoeba]# chmod -R 755 /usr/local/amoeba/
查看服务是否安装成功
[root@web1 amoeba]# /usr/local/amoeba/bin/amoeba
amoeba start|stop
在主从服务器上配置权限
mysql> grant all on *.* to test@'192.168.116.%' identified by 'abc123';
Query OK, 0 rows affected, 1 warning (0.02 sec)
修改配置文件，首先做一个备份
[root@web1 amoeba]# cd /usr/local/amoeba/conf/
[root@web1 conf]# cp amoeba.xml amoeba.xml.bak
[root@web1 conf]# vim amoeba.xml
 30          <property name="user">amoeba</property>
 32          <property name="password">abc123</property>
117                 
118                 <property name="writePool">master</property>
119                 <property name="readPool">slaves</property>

[root@web1 conf]# cp dbServers.xml dbServers.xml.bak
[root@web1 conf]# vim dbServers.xml
 22    <!-- mysql schema 
 23    <property name="schema">test</property>
 24    -->
 25    <!-- mysql user -->
 26    <property name="user">test</property>
29     <property name="password">abc123</property>
 45         <dbServer name="master"  parent="abstractServer">
 46                 <factoryConfig>
 47                         <!-- mysql ip -->
 48                         <property name="ipAddress">192.168.116.10</property>
 49                 </factoryConfig>
 50         </dbServer>
 51 
 52         <dbServer name="slave1"  parent="abstractServer">
 53                 <factoryConfig>
 54                         <!-- mysql ip -->
 55                         <property name="ipAddress">192.168.116.11</property>
 56                 </factoryConfig>
 57         </dbServer>
 58 
 59         <dbServer name="slave2"  parent="abstractServer">
 60                 <factoryConfig>
 61                         <!-- mysql ip --> 62                         <property name="ipAddress">192.168.116.13</property>
 63                 </factoryConfig>
 64         </dbServer>
 65         <dbServer name="slaves" virtual="true">
 66                 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
 67                         <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-    ->
 68                         <property name="loadbalance">1</property>
 69 
 70                         <!-- Separated by commas,such as: server1,server2,server1 -->
 71                         <property name="poolNames">slave1,slave2</property>
启动服务
/usr/local/amoeba/bin/amoeba start&
[root@web1 ~]# netstat -natlp|grep java
tcp6       0      0 :::8066                 :::*                    LISTEN      24146/java          
tcp6       0      0 127.0.0.1:41289         :::*                    LISTEN      24146/java          
tcp6       0      0 192.168.116.14:43548    192.168.116.13:3306     ESTABLISHED 24146/java          
tcp6       0      0 192.168.116.14:59234    192.168.116.10:3306     ESTABLISHED 24146/java  

2.测试读写分离

[root@web2 ~]# yum install -y mariadb-server mariadb
[root@web2 ~]# systemctl start mariadb.service
[root@web2 ~]# mysql -u amoeba -pabc123 -h 192.168.116.14 -P8066
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1043744321
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| kkk                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)
在主服务器上面创建表格
mysql> use kkk
Database changed
mysql> create table test (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.02 sec)
在两台从服务器上
先停止同步
mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)
分别在刚才的表格上写入数据
从服务器1
mysql> insert into test values('1','zhangsan','this_is_slave1');
Query OK, 1 row affected (0.01 sec)
从服务器2
mysql> insert into test values('2','lisi','this_is_slave2');
Query OK, 1 row affected (0.02 sec)
在主服务器上
mysql> insert into test values('3','wangwu','this_is_master');
Query OK, 1 row affected (0.03 sec)
在客户端上
MySQL [kkk]> select * from test;
+------+----------+----------------+
| id   | name     | address        |
+------+----------+----------------+
|    1 | zhangsan | this_is_slave1 |
+------+----------+----------------+
1 row in set (0.00 sec)
然后在客户端上添加数据
MySQL [kkk]> insert into test values('5','qianqi','this_is_client');
Query OK, 1 row affected (0.03 sec)
在主服务器上查询
mysql> select * from test;
+------+--------+----------------+
| id   | name   | address        |
+------+--------+----------------+
|    3 | wangwu | this_is_master |
|    5 | qianqi | this_is_client |
+------+--------+----------------+
2 rows in set (0.00 sec)