MySQL主从复制与读写分离

案例

• 在企业应用中，成熟的业务通常数据量都比较大
• 单台MySQL在安全性、高可用性和高并发方面都无法满足实际的需求
• 配置多台主从数据库服务器以实现读写分离

案例前置知识点

MySQL主从复制原理

• MySQL的复制类型
  基于语句的复制
  基于行的复制
  混合类型的复制
• MySQL主从复制的工作过程
  

复制的基本过程如下:

1. Master将用户对数据库更新的操作以二进制格式保存到Binary Log日志文件中
2. Slave上面的IO进程连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;
IO进程：从数据库复制主数据库上二进制日志的进程

3.Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置;

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

5. Slave的Sql进程检测到relay-log中新增加了内容后,会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行.
Sql进程：将二进制日志内容翻译成SQL语句写进从数据库

MySQL读写分离原理

• 只在主服务器上写，只在从服务器上读
• 主数据库处理事务性查询，从数据库处理SELECT查询
• 数据库复制用于将事务性查询的变更同步到集群中的从数据库
• 读写分离方案
  基于程序代码内部实现
  基于中间代理层实现
  MySQL-Proxy
  Amoeba
  

1.I/0线程显示为NO:主库与从库网络不通、主库未授权给从库
若从库查看连接主库I/O线程状态为conneting,一直是这个状态，考虑双方的防火墙是否开启。
2.SQL线程显示为NO:从库日志和位置点与主不同步

项目配置MySQL主从复制

1 时间同步
在master服务器上

[root@master ~]# yum -y install ntp ntpdate
[root@master ~]# ntpdate ntp.aliyun.com
[root@master ~]# vi /etc/ntp.conf
8行 restrict default nomodify  
//restrict、default 定义默认访问规则, nomodify禁止远程主机修改本地服务器配置。

17行 restrict 20.0.0.0 mask 255.255.255.0 nomodify notrap //去掉#
//从20.0.0.1-20.0.0.254的主机都可以使用我们的NTP服务器来同步时间   

//21-24行删除
//加两行
fudge 127.127.1.0 stratum 10 #设置本机的时间层级为10级，0级代表时间层级是0级，向其他服务器提供时间同步源的意思，不要设置为0级
server 127.127.1.0#指定本机为时间同步源

[root@master ~]# systemctl restart ntpd
[root@master ~]# netstat -anptu | grep ntpd

[root@master ~]# crontab -e
*/30  *  *  *  *  /usr/ sbin/ntpdatentp.aliyun.com
[root@master ~]# date

在slave1 slave2上

[root@slave1 ~]# yum -y install ntpdate
[root@slave1 ~]# ntpdate 20.0.0.10
[root@slave1 ~]# crontab -e
*  *  *  *  *  /usr/ sbin/ntpdate 20.0.0.10
[root@localhost ~]# date

配置master服务器

[root@master ~]# vi /etc/my.cnf   //在[mysqld]下面配置

server_id = 1
//添加
log_bin=master_bin
log_slave_updates=true

[root@master ~]# systemctl restart mysqld

[root@master ~]# mysql -uroot -p123123

mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '123123';
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 |      859 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置slave1服务器

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

server-id = 2      #每个数据库的id必须不一样
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
[root@slave1 ~]# systemctl restart mysqld

[root@slave1 ~]# mysql -uroot -p123123

mysql> change master to master_host='20.0.0.10',master_user='myslave',master_password='123123',master_log_file='master_bin.000001',master_log_pos=859;
//注意master_log_pos=的值，要在主上使用show master status;查看一下，注意–和_----
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

配置slave2服务器

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

server-id = 3      #每个数据库的id必须不一样
relay-log = relay-log-bin
relay-log-index = slave-relay-bin.index
[root@slave1 ~]# systemctl restart mysqld

[root@slave1 ~]# mysql -uroot -p123123

mysql> change master to master_host='20.0.0.10',master_user='myslave',master_password='123123',master_log_file='master_bin.000001',master_log_pos=859;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

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

mysql> show slave status\G

在master上验证同步

[root@master ~]# mysql -uroot -p123123
mysql> create database text;
Query OK, 1 row affected (0.00 sec)

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

查看slave1 从服务器

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

查看slave2 从服务器

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

主从复制完成

配置读写分离

amoeba代理服务器配置

设置时间同步

[root@amoeba ~] ntpdate 20.0.0.10
29 Dec 13:56:02 ntpdate[10363]: adjust time server 20.0.0.10 offset 0.000311 sec

[root@amoeba ~]#crontab -e
*/3  *  *  *  *  /usr/sbin/ntpdate 20.0.0.10

安装JDK

导入jdk包并解包，拷贝到usr目录下

[root@amoeba ~]# tar zxvf jdk-8u91-linux-x64.tar.gz 
[root@amoeba ~]# cp -rv jdk1.8.0_91/ /usr/local/java

修改配置文件

[root@amoeba ~]# vi /etc/profile  //在最后添加以下四行

export JAVA_HOME=/usr/local/java
export JRE_HOME=/usr/local/java/jre
export PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin
export CLASSPATH=./:$JAVA_HOME/lib:$JRE_HOME/lib

查看是否配置正确

[root@amoeba ~]# source /etc/profile
[root@amoeba ~]# echo $CLASSPATH
./:/usr/local/java/lib:/usr/local/java/jre/lib
[root@amoeba ~]# 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)

、
安装amoeba
导入amoeba-mysql-3.0.5-RC-distribution.zip包
解包并赋予权限

[root@amoeba ~]# unzip amoeba-mysql-3.0.5-RC-distribution.zip

[root@amoeba ~]# mv amoeba-mysql-3.0.5-RC/ /usr/local/amoeba  //移动至用户目录，便于直接调取命令
[root@amoeba ~]# chmod -R 755 /usr/local/amoeba/  //赋予权限

修改amoeba配置文件

[root@amoeba ~]# vi /usr/local/amoeba/jvm.properties  
#JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"
JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"   //32行添加

制作amoeba管理脚本并赋予权限

[root@amoeba ~]# vi /etc/init.d/amoeba
#!/bin/bash
#chkconfig: 35 20 90
export JAVA_HOME=/usr/local/java
export PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin
NAME=Amoeba
AMOEBA_BIN=/usr/local/amoeba/bin/launcher
SHUTDOWN_BIN=/usr/local/amoeba/bin/shutdown
PIDFILE=/usr/local/amoeba/Amoeba-MySQL.pid
SCRIPTNAME=/etc/init.d/amoeba
case "$1" in
start)
echo -n "Starting $NAME..."
$AMOEBA_BIN
echo "done"
;;
stop)
echo -n "Stopping $NAME..."
$SHUTDOWN_BIN
echo "done"
;;
restart)
$SHUTDOWN_BIN
sleep 1
$AMOEBA_BIN
;;
*)
echo "Usage:$SCRIPTNAME {start|stop|restart}"
exit 1
;;
esac

[root@amoeba ~]# chmod +x /etc/init.d/amoeba 
[root@amoeba ~]# chkconfig --add amoeba
[root@server3 ~]# service amoeba start
Starting Amoeba...Error: JAVA_HOME environment variable is not set.
done
[root@amoeba ~]# netstat -anpt | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      2351/java       

amoeba配置文件修改用户名及密码，用于客户端连接

[root@amoeba ~]# cd /usr/local/amoeba/
[root@amoeba amoeba]# vi conf/amoeba.xml 
<property name="user">amoeba</property>
//28行修改
<property name="password">123456</property>
//30行修改

property name="defaultPool">master</property>
 //83行修改
//删除此前<--注释符
<property name="writePool">master</property>
<property name="readPool">slaves</property>
//删除此前-->注释符

[root@amoeba amoeba]# vi conf/dbServers.xml 
                        <!-- mysql user -->
                        <property name="user">test</property>  //26行修改

                        <property name="password">123.com</property>   //28行修改
        
        
        <dbServer name="master"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.10</property>46行修改
                </factoryConfig>
        </dbServer>

        <dbServer name="slave1"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.11</property>
                </factoryConfig>
        </dbServer>   //添加
       <dbServer name="slave2"  parent="abstractServer">
                      <factoryConfig>
                              <!-- mysql ip -->
                              <property name="ipAddress">20.0.0.12</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>

启动服务

[root@amoeba ~]# /usr/local/amoeba/bin/shutdown
kill -15 2351
[root@amoeba ~]# /usr/local/amoeba/bin/launcher

授权给amoeba

master

mysql> grant all privileges on *.* to 'test'@'20.0.0.%' identified by '123.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

slave1

mysql> grant all privileges on *.* to 'test'@'20.0.0.%' identified by '123.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

slave2

mysql> grant all privileges on *.* to 'test'@'20.0.0.%' identified by '123.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

客户端测试

安装mariadb数据库，并登录amoeba

root@client ~]# yum -y install mariadb*

[root@server6 ~]# mysql -uamoeba -p123456 -h20.0.0.14 -P8066
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 393647748
Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA 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)]> 

登录成功

验证读写分离

在客户机上创建表bbb

MySQL [(none)]> create table zhang (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.02 sec)

MySQL [(none)]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| bbb            |
+----------------+
1 row in set (0.00 sec)

在主从MySQL服务器上查看
master上查看

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| bbb            |
+----------------+
1 row in set (0.00 sec)

slave1上查看

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| bbb            |
+----------------+
1 row in set (0.00 sec)

关闭从服务器查看数据

slave1
关闭slave1从服务器

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

新建ttt表，添加数据，并在zhang表中添加数据

mysql> create table ttt (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into ttt values(5,'zhangsan','nanjing');
Query OK, 1 row affected (0.00 sec)

mysql> select * from ttt;
+------+----------+---------+
| id   | name     | address |
+------+----------+---------+
|    5 | zhangsan | nanjing |
+------+----------+---------+
1 row in set (0.00 sec)

mysql> insert into zhang values(5,'zhangsan','nanjing');
Query OK, 1 row affected (0.00 sec)

mysql> select * from zhang;
+------+----------+---------+
| id   | name     | address |
+------+----------+---------+
|    5 | zhangsan | nanjing |
+------+----------+---------+
1 row in set (0.00 sec)

在客户机上查看

MySQL [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| bbb            |
+----------------+
1 row in set (0.01 sec)

MySQL [test]> select * from zhang;
+------+----------+---------+
| id   | name     | address |
+------+----------+---------+
|    5 | zhangsan | nanjing |
+------+----------+---------+
1 row in set (0.01 sec)

MySQL [test]> select * from ttt;
+------+----------+---------+
| id   | name     | address |
+------+----------+---------+
|    5 | zhangsan | nanjing |
+------+----------+---------+
1 row in set (0.00 sec)

结论：得出结论，关闭从服务，客户机只能查看，无法写入从服务器数据

master
此时在主服务器插入表

mysql> create table ddd (id int(10),name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| ddd            |
| bbb            |
+----------------+
2 rows in set (0.00 sec)

在客户机上查看

MySQL [test]> show tables;
+----------------+
| Tables_in_test |
+----------------+
| ddd            |
| bbb            |
+----------------+
2 rows in set (0.01 sec)

结论：得出主服务器数据可写入客户机