主从复制与读写分离

MySQL主从复制原理

MySQL的复制类型

基于语句的复制
基于行的复制
混合类型的复制

MySQL主从复制的工作过程

基本的复制过程

1.Master将用户对数据库更新的操作以二进制格式保存到 Binary Log日志文件中；
2.Slave上面的IO进程连接上Masetr，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；
3.Master接收来自Slave的IO进程的请求后， 通过负责复制的IO进程跟据请求信息读取制定日志日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，好包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；
4.Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-log文件中，以便下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”；
5.Slave的Sql进程检测到relay-log中增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

主从复制

其中ip为20.0.0.10为主服务器，20.0.0.11为从1服务器，20.0.0.14为从2服务器

主：20.0.0.10
[root@server1 ~]# yum install -y ntp //验证ntp是否已安装
[root@server1 ~]# vi /etc/ntp.conf  
修改以下配置
#restrict 192.168.1.0 mask 255.255.255.0 nomodify notrap→restrict 20.0.0.0 mask 255.255.255.0 nomodify notrap
21-24行 4dd并添加
server 127.127.1.0
fudge 127.127.1.0 stratum 8 //时间层级
[root@server1 ~]# systemctl restart ntpd //服务重启
[root@server1 ~]# systemctl status ntpd //查看服务状态，是否为running
 Active: active (running) since 一 2020-10-26 15:31:18 CST; 51s ago
 
从1:20.0.0.11
[root@server1 ~]# yum install -y ntp //验证ntp是否安装
[root@server1 ~]# ntpdate 20.0.0.10 //时间调整
 3 Nov 10:24:14 ntpdate[3678]: step time server 20.0.0.10 offset -71939.633770 sec
 
从2:20.0.0.14
[root@server1 ~]# yum install -y ntp //验证ntp是否安装
[root@server1 ~]# ntpdate 20.0.0.10 //时间调整
 3 Nov 10:25:50 ntpdate[74467]: adjust time server 20.0.0.10 offset 0.000819 sec

主：从1：从2，分别查看时间，是否基本同步：
[root@server1 ~]# date
2020年 11月 03日 星期二 10:26:28 CST
[root@server1 ~]# date
2020年 11月 03日 星期二 10:26:32 CST
[root@server2 ~]# date
2020年 11月 03日 星期二 10:26:38 CST

主：20.0.0.10
[root@server1 ~]# vi /etc/my.cnf
server-id = 1 //下添加两行内容
log_bin=master_bin //开启二进制日志，以log_bin开头
log_slave_updates=true//开启日志更新，给从服务器
[root@server1 ~]# systemctl restart mysqld //重启mysqld服务
[root@server1 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '123456';//授权主从复制的权限
mysql> show grants for 'myslave'@'20.0.0.%'; //查看授权

mysql> flush privileges;//刷新权限

从1：20.0.0.11
[root@server1 ~]# crontab -e //为了保持时间同步一直持续，做一个任务计划
*/30 * * * * /usr/sbin/update 20.0.0.10

从2: 20.0.0.14
[root@server2 ~]# crontab -e
no crontab for root - using an empty one
crontab: installing new crontab
*/30 * * * * /usr/sbin/update 20.0.0.10

从1: 20.0.0.11 
[root@server1 ~]# vi /etc/my.cnf //加日志信息
server-id=1 改为server-id = 11在下面添加两行
relay_log=relay_log_bin
relay_log_index=slave-relay-bin.index
[root@server1 ~]# systemctl stop firewalld
[root@server1 ~]# setenforce 0
[root@server1 ~]# systemctl restart mysqld
[root@server1 ~]# vi /etc/selinux/config 
SELINUX=disabled

从2: 20.0.0.14
[root@server1 ~]# vi /etc/my.cnf //加日志信息
server-id=1 改为server-id = 12在下面添加两行
relay_log=relay_log_bin
relay_log_index=slave-relay-bin.index
[root@server1 ~]# systemctl stop firewalld
[root@server1 ~]# setenforce 0
[root@server1 ~]# systemctl restart mysqld
[root@server1 ~]# vi /etc/selinux/config 
SELINUX=disabled

主：20.0.0.10
mysql> exit
[root@server1 ~]# systemctl stop firewalld
[root@server1 ~]# setenforce 0
[root@server1 ~]# systemctl disable firewalld
[root@server1 ~]# vi /etc/selinux/config 
SELINUX=disabled
[root@server1 ~]# mysql -uroot -p123456
mysql> show master status;//获取日志信息和位置信息

从1: 20.0.0.11
[root@server2 ~]# mysql -uroot -p123456  若无法登录，则mysql -uroot -p  回车
mysql> change master to master_host='20.0.0.10',master_user='myslave',master_password='123456',master_log_file='master_bin.000001',master_log_pos=599;
mysql> start slave; //开启从状态
mysql> show slave status; //查看IO SQL状态是否打开  

从2: 20.0.0.14
[root@server2 ~]# mysql -uroot -p123456 若无法登录，则mysql -uroot -p  回车
mysql> change master to master_host='20.0.0.10',master_user='myslave',master_password='123456',master_log_file='master_bin.000001',master_log_pos=599;
mysql> start slave; //开启从状态
mysql> show slave status; //查看IO SQL状态是否打开  

从1: 20.0.0.11
mysql> exit
Bye
[root@server1 ~]# cd /usr/local/mysql/data
[root@server1 data]# ll //查看是否有4条日志生成

主服务器做验证：20.0.0.10
mysql> create database abc; //添加一条数据库
Query OK, 1 row affected (0.01 sec)

mysql> show databases; //查看数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| abc                |
| bbs                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.01 sec)

从1 从2 效果图如下：

MySQL读写分离原理

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

读写分离方案

 基于程序代码内部实现
 基于中间代理层实现
  MySQL-Proxy 
  Amoeba

读写分离原理图

实验2：主从分离

建立在主从复制之上

要求：

ip为20.0.0.10为主服务器
ip为20.0.0.11为从1服务器
ip为20.0.0.14为从2服务器
ip为20.0.0.12为安装Amoeba
ip为20.0.0.13为Amoeba客户机

主：
 mysql> grant all privileges on *.* to test@'20.0.0.%' identified by '123.com';
//在数据库中为amoeba授权
mysql> show grants for test@'20.0.0.%'; //查看授权信息
+--------------------------------------------------+
| Grants for test@20.0.0.%                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'20.0.0.%' |
+--------------------------------------------------+
mysql> flush privileges;//刷新授权信息

从1：
 mysql> grant all privileges on *.* to test@'20.0.0.%' identified by '123.com';
//在数据库中为amoeba授权
mysql> show grants for test@'20.0.0.%'; //查看授权信息
+--------------------------------------------------+
| Grants for test@20.0.0.%                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'20.0.0.%' |
+--------------------------------------------------+
mysql> flush privileges;//刷新授权信息

从2：
 mysql> grant all privileges on *.* to test@'20.0.0.%' identified by '123.com';
//在数据库中为amoeba授权
mysql> show grants for test@'20.0.0.%'; //查看授权信息
+--------------------------------------------------+
| Grants for test@20.0.0.%                         |
+--------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'test'@'20.0.0.%' |
+--------------------------------------------------+
mysql> flush privileges;//刷新授权信息

Amoeba：

通过xftp将以上两位文件发送到ip为20.0.0.12的/root目录下
[root@server1 ~]# hostname amoeba //修改主机名
[root@server1 ~]# bash 
[root@amoeba ~]# ll
-rw-r--r--. 1 root root   5761714 11月  3 17:38 amoeba-mysql-3.0.5-RC-distribution.zip
-rw-r--r--. 1 root root 181367942 11月  3 17:38 jdk-8u91-linux-x64.tar.gz

在amoeba服务器安装JDK
[root@amoeba ~]# tar zxvf jdk-8u91-linux-x64.tar.gz 
[root@amoeba ~]# mv 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 AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$JAVA_HOME/bin:$AMOEBA_HOME/bin
export CLASSPATH=./:/local/java/lib:/usr/local/java/jre/lib
[root@amoeba ~]# source /etc/profile //使其生效

[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

[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 
[root@amoeba ~]# vi /etc/profile //修改成以下内容
export JAVA_HOME=/usr/local/java
export JRE_HOME=/usr/local/java/jre
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$JAVA_HOME/bin:$AMOEBA_HOME/bin
export CLASSPATH=./:/usr/local/java/lib:/usr/local/java/jre/lib
[root@amoeba ~]# source /etc/profile
[root@amoeba ~]# chmod -R 755 /usr/local/amoeba/ //进行授权
[root@amoeba ~]#  vi /usr/local/amoeba/jvm.properties 修改以下内容
JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"//下面添加2行
JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"
#JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m"
JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"
[root@amoeba ~]# cd /usr/local/amoeba/bin/
[root@amoeba  bin]# launcher
[rootamoeba  bin]# netstat -anpt | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      1829/java           
##Amoeba开启，主机会强制关机，需要自行开机

制作amoeba管理脚本

[root@amoeba ~]# cd /usr/local/amoeba/
[root@amoeba amoeba]# cd bin/
[root@amoeba bin]# ll //查看是否有以下两个文件生成
-rwxr-xr-x. 1 root root 2114 7月   5 2013 launcher //启动amoeba脚本
-rwxr-xr-x. 1 root root 1615 7月   5 2013 shutdown //关闭amoeba脚本
[root@amoeba bin]# cd
[root@amoeba ~]# vi /etc/profile
export PATH=$PATH:$JAVA_HOME/bin:$AMOEBA_HOME/bin:$AMOEBA_HOME/jre/bin
[root@amoeba ~]# vi /etc/init.d/amoeba //添加脚本
#!/bin/bash
#chkconfig: 35 60 70
# ameoba 启动文件
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 ~]# chkconfig --add amoeba
[root@amoeba ~]# chmod +x /etc/init.d/amoeba 
[root@amoeba ~]# systemctl start amoeba
Failed to start amoeba.service: Unit not found.
[root@amoeba ~]# chkconfig --add amoeba
[root@amoeba ~]# chkconfig --list
[root@amoeba ~]# systemctl start amoeba
[root@amoeba ~]# cd /usr/local/amoeba/bin/
[root@amoeba bin]# ./launcher
[root@amoeba bin]# netstat -anpt | grep 8066 //服务已启动
tcp6       0      0 :::8066                 :::*                    LISTEN      12159/java 
[root@amoeba bin]# cd /usr/local/amoeba/
[root@amoeba amoeba]# cd conf/
[root@amoeba conf]# ll
-rwxr-xr-x. 1 root root 3107 7月   5 2013 amoeba.xml
-rwxr-xr-x. 1 root root 2366 7月   5 2013 dbServers.xml

配置文件修改：

[root@amoeba conf]# vi amoeba.xml
<property name="user">root</property>→<property name="user">amoeba</property>
 <property name="password"></property>→<property name="password">123456</property>
 <property name="defaultPool">multiPool</property>→<property name="defaultPool">master</property>
<property name="writePool">server1</property>→ <property name="writePool">master</property>
  <property name="readPool">server1</property>→<property name="readPool">slaves</property>

配置文件修改：
[root@amoeba conf]# vi dbServers.xml
修改为

修改为

修改为

复制上图的内容，修改为

[root@amoeba conf]# cd -
/usr/local/amoeba
[root@amoeba amoeba]# cd bin/
[root@amoeba bin]# shutdown 
[root@server1 ~]# launcher

关闭时，启动不了，需要手动开机
[root@server1 ~]# systemctl stop firewalld
[root@server1 ~]# setenforce 0 
[root@server1 ~]# systemctl status amoeba.service //开启amoeba

客户机做验证：
[root@localhost ~]# hostname client //更改主机名
[root@localhost ~]# bash
[root@localhost ~]# yum -y install mariadb*
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# systemctl status mariadb
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
   Active: active (running) since 三 2020-11-04 22:10:22 CST; 21s ago
[root@localhost ~]# netstat -anpt | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      52149/mysqld    
[root@localhost ~]# mysql //可以登录数据库
客户机登录amoeba验证（注意），第二个P要大写（哭了）
如果登录不上，看看amoeba的8066号端口是否打开

主：20.0.0.10
mysql> create database test; //创建一个test的数据库

Amoeba客户机：
[root@client ~]# mysql -uamoeba -p123456 -h20.0.0.12 -P8066
MySQL [(none)]> use test;
MySQL [test]> create table abc(name varchar(64) not null, id int(3) primary key not null auto_increment, salary int(10) not null);//创建表

主：20.0.0.10
mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| abc            |
+----------------+
mysql> desc abc;//查看到amoeba客户机创建表及三个字段
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| name   | varchar(64) | NO   |     | NULL    |                |
| id     | int(3)      | NO   | PRI | NULL    | auto_increment |
| salary | int(10)     | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+

从1  从2：验证是否同步成功
mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| abc            |
+----------------+
mysql> desc abc;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| name   | varchar(64) | NO   |     | NULL    |                |
| id     | int(3)      | NO   | PRI | NULL    | auto_increment |
| salary | int(10)     | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

关闭同步，做读写分离
从1，从2
mysql> stop slave;
Amoeba验证机：
MySQL [test]> insert into abc(name,salary) values('zhangsan',10000);

主：
mysql> select * from abc;
+----------+----+--------+
| name     | id | salary |
+----------+----+--------+
| zhangsan |  1 |  10000 |
+----------+----+--------+
从1，从2
mysql>  select * from abc; .//输入无响应，说明关闭同步
Empty set (0.00 sec)

从1
mysql> insert into abc(name,salary) values('lisi',15000);
mysql>  select * from abc;//只能看见自己，说明在本地缓存
+------+----+--------+
| name | id | salary |
+------+----+--------+
| lisi |  1 |  15000 |

从2
mysql> insert into abc(name,salary) values('wangwu',16000);
mysql>  select * from abc;                              
+--------+----+--------+
| name   | id | salary |
+--------+----+--------+
| wangwu |  1 |  16000 |
+--------+----+--------+

客户端：
MySQL [test]> select * from abc; //验证，能看见从1，从2上的数据，看不见自己创建的数据，实现读写分离
+--------+----+--------+
| name   | id | salary |
+--------+----+--------+
| wangwu |  1 |  16000 |
+--------+----+--------+
1 row in set (0.01 sec)

MySQL [test]> select * from abc;
+------+----+--------+
| name | id | salary |
+------+----+--------+
| lisi |  1 |  15000 |
+------+----+--------+

开启主从同步之后，主服务器上写入的数据同步到从服务器，客户端也能读取主服务器数据了，但从服务器上的数据不会到主服务器上