MySQL主从复制和读写分离

数据库为什么要主从复制、读写分离？

• 在企业应用中，成熟的业务通常数据量都比较大
• 单台MySQL在数据存储、安全性和高并发方面都无法满足实际的需求
• 需要配置多台主从数据服务器以实现主从复制，增加数据可靠性，读写分离，减少数据库压力和存储引擎带来的表锁定和行锁定问题
  

一、主从复制原理

• 基于语句的复制（默认）
  在主服务器上执行的语句，从服务器执行同样的语句
• 基于行的复制
  把改变的内容复制到从服务器
• 混合类型的复制
  一旦发现基于语句无法精确复制时，就会采用基于行的复制

主从间的工作过程
主服务器上开启二进制日志文件，并允许其他数据库同步
从服务器上开启中继日志
主服务器的二进制日志数据以字节流的形式传入I/O线程
I/O线程写入中继日志
从服务器通过SQL线程从中继日志读取SQL语句与主服务器同步

二、读写分离原理

• 只在主服务器上写，只在从服务器上读
• 主数据库处理事务性查询，从数据库处理SELECT查询
• 数据库复制用于将事务性查询的变更同步到集群中的从数据库

三、Amoeba实现主从复制、读写分离

3.1原理：

• 共涉及三个账号
  1.主从复制账号myslave，允许从服务器访问主服务器
  2.mysql允许amoeba直接访问的账号 test
  3.允许客户端访问amoeba服务器的账号 amoeba
• 通过Amoeba实现读写分离
• 虚拟模块MySQL-Proxy做读取从服务器时的轮询

3.2实例

此处在amoeba上装数据库作为客户端

3.2.1.时间同步

[root@master ~]# ntpdate ntp1.aliyun.com
21 Oct 18:29:46 ntpdate[44242]: step time server 120.25.115.20 offset 1.449094 sec

3.2.2.三台数据库安装mysql5.7，amoeba服务器作为客户端使用时可安装mysql5.7或者简易数据库做测试，此处四台都装了mysql5.7,安装方法见

mysql5.7编译安装

3.3.3.主服务器20.0.0.21上

vim /etc/my.cnf
#找到server-id部分，三台主从数据库的id必须不同
server-id = 11
log-bin = master-bin               //主服务器日志文件
log-slave-updates=true				//允许从服务器更新
[root@master ~]# systemctl restart mysqld.service    //配置文件修改后必须重启

然后进数据库

[root@master ~]# mysql -uroot -p
Enter password: 
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by 'abc123'';
Query OK, 0 rows affected, 1 warning (0.01 sec)
//给从服务器授权，允许20.0.0.网段的服务器使用myslave访问所有库的所有表
mysql> flush privileges;  //策略刷新
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;  //查看主服务器状态,日志用于从服务器同步，position是当前定位
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      599 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (
此时主服务器配置完成，等待从服务器的同步

3.3.4.从服务器20.0.0.22与20.0.0.23

首先修改配置文件
先配置20.0.0.22,20.0.0.23只在id处不同，为33

vim /etc/my.cnf
server-id = 22
relay-log = relay-log-bin 	//从主服务器上同步日志文件记录到本地
relay-log-index = slave-relay-bin.index   //建立索引文件，定义relay-log的位置和名称

[root@slave1 ~]# systemctl restart mysqld

进入数据库

mysql>  change master to master_host='20.0.0.21',master_user='myslave',master_password='abc123',master_log_file='master-bin.000001',master_log_pos=599;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
指明从哪里找什么文件的什么位置进行复制
mysql> start slave;      //
Query OK, 0 rows affected (0.00 sec)

名称	解释
master_host	主数据库IP
master_user	主数据库授权用户名
master_password	主数据库授权的密码
master_log_file	需要同步的二进制日志文件名
master_log_pos	断点位置

查看从服务器状态
I/O线程与SQL线程都为Yes，主从复制完成

3.3.5验证主从复制效果

在主服务器上创建数据库school

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

mysql> create database school;
Query OK, 1 row affected (0.00 sec)

从服务器上查看，能查看到

3.3.6配置读写分离

在amoeba服务器上装,用到这两个包
amoeba-mysql-binary-2.2.0.tar.gz
jdk-6u14-linux-x64.bin

先装jdk

[root@amoeba ~]# chmod +x jdk-6u14-linux-x64.bin  //加执行权限
[root@amoeba ~]# ./jdk-6u14-linux-x64.bin    //二进制文件 可以直接执行
接下来大部分都是描述内容，略过
直到出现下面内容
Please enter "yes" or "no".
Do you agree to the above license terms? [yes or no]
yes       //输入yes 开始安装
按enter

到这就ok了

mv jdk1.6.0_14/ /usr/local/jdk1.6          //移动到指定路径，能够识别
配置环境变量
vim /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:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

source /etc/profile           //生效

mkdir /usr/local/amoeba       //创建amoeba工作目录
[root@amoeba ~]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
//解压到指定目录
chmod -R 755 /usr/local/amoeba 给执行权限
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop
 这样说明amoeba安装成功

在三台数据库上都给amoeba授权

grant all on *.* to test@'20.0.0.%' identified by '123456';  
允许test账户以123456为密码访问数据库的所有库的所有表
flush privileges;
刷新权限

回到amoeba服务器上,修改配置文件

[root@amoeba ~]# cd /usr/local/amoeba/
[root@amoeba amoeba]# cd conf

需要修改这两个配置文件

vim amoeba.xml

[root@amoeba conf]# vim dbServers.xml

配置完成后启动

放在后台启动
[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start &
[1] 33159
[root@amoeba conf]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2020-10-22 08:55:54,930 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
2020-10-22 08:55:55,096 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2020-10-22 08:55:55,100 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:50559.

查看后台进程
[root@amoeba conf]# jobs
[1]+  Running                 /usr/local/amoeba/bin/amoeba start &

[root@amoeba conf]# netstat -anpt | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      33159/java          

通过amoeba登录数据库 -u 用户名 -p密码 -h 访问地址 此处测试是从本地登录 所以是127.0.0.1 -P amoeba端口号
[root@amoeba conf]# mysql -u amoeba -p123456 -h 127.0.0.1 -P8066

测试
在master上创建空表

mysql> use school;
Database changed
mysql> create table info (id int(4),name varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> desc info;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.02 sec)

在slave1插入zhangsan

mysql> insert into info values  (1,'zhangsan');
Query OK, 1 row affected (0.04 sec)

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

在slave2插入lisi

mysql> insert into info values (2,'lisi');
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+------+------+
| id   | name |
+------+------+
|    2 | lisi |
+------+------+
1 row in set (0.00 sec)

在master上查询

mysql> select * from info;
Empty set (0.00 sec)
从数据库上数据变更不会复制，主机上什么也查不到

在客户机上查询

mysql> select * from info;
+------+------+
| id   | name |
+------+------+
|    2 | lisi |
+------+------+
1 row in set (0.00 sec)

mysql> select * from info;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
+------+----------+
1 row in set (0.01 sec)
可以从从数据中轮询查看

实验成功