文章目录
一、MySQL使用主从复制与读写分离的原因
- 1.在企业应用中,成熟的业务通常数据量都比较大
- 2.单台MySQL在安全性、高可用性和高并发方面都无法满足实际的需求
- 3.配置多台主从数据库服务器以实现读写分离
二、MySQL主从复制原理
2.1 MySQL的复制类型
基于语句的复制(默认)
- 在主服务器上执行的语句,从服务器执行同样的语句
基于行的复制
- 把改变的内容复制到从服务器
混合类型的复制
- 一旦发现基于语句无法精确复制时,就会采用基于行的复制
2.2 MySQL主从复制的工作过程
2.2.1 复制的详细基本过程
-
1.Master将用户对数据库更新的操作以二进制格式保存到 Binary Log 日志文件中;
-
2.Slave上面的IO进程连接上Master,并请求从指定日志文件的指定位置(或者从最开始的日志)之后的日志内容;
-
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端真实执行时候的那些
可执行的内容,并在自身执行。
注意:复制过程有个很重要的限制,即复制在Slave上是串行化的,也就是说Master上的并行更新操作不能在Slave上并行操作
三、MySQL读写分离原理
- 只在主服务器上写,只在从服务器上读
- 主数据库处理事务性查询,从数据库处理SELECT查询
- 数据库复制用于将事务性查询的变更同步到集群中的从数据库
3.1 读写分离方案
- 基于程序代码内部实现
- 基于中间代理层实现:代理一般位于客户端与服务器之间,代理服务器接到客户端请求后通过判断转发到后端数据库,有两个代表性程序 “MySQL-Proxy Amoeba”
3.2 读写分离过程
四、部署
4.1 原理
主从数据库之间先时间同步,后主从复制,amoeba配置读写分离,授权。最后客户机登录验证
4.2 环境
关闭防火墙(所有设备)
[root@master ~]# systemctl stop firewalld
[root@master ~]# setenforce 0
[root@master ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config
[root@master ~]# grep -v "#" /etc/selinux/config
SELINUX=disabled
SELINUXTYPE=targeted
4.3 部署流程
4.3.1 设置主从数据库时间同步
1.先确保机器可以正常上网
在主数据库上
2.设置时间同步
[root@master ~]# yum -y install ntpdate ntp #安装ntp软件
[root@master ~]# ntpdate ntp.aliyun.com #时间同步
30 Dec 03:28:45 ntpdate[30648]: step time server 203.107.6.88 offset 0.844560 sec
[root@master ~]# date #查看当前日期
2020年 12月 30日 星期三 03:28:50 -03
[root@master ~]# vi /etc/ntp.conf #编辑配置文件
添加修改:
restrict default nomodify #定义默认访问规则,nomodify禁止远程主机修改本地服务器配置
restrict 192.168.1.0 mask 255.255.255.0 nomodify notrap
#从192.168.1.1-192.168.1.254的主机都可以使用我们的ntp服务来时间同步
fudge 127.127.1.0 stratum 10
#设置本机的时间层级为10级,0级表示时间层级为0级,是向其他服务器提供时间同步源的意思,不要设置为0级
server 127.127.1.0 #设置本机为时间同步源
3.查看ntpd端口
[root@master ~]# systemctl restart ntpd 重启服务
[root@master ~]# netstat -anptu | grep ntpd 查看端口状态
4.部署任务计划
[root@master ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate ntp.aliyun.com
每隔30分钟进行一次同步对象为阿里云时钟网站的时间同步
[root@master ~]# date
2020年 12月 30日 星期三 03:35:09 -03
在slave1、slave2从数据库上
[root@slave1 ~]# yum -y install ntpdate 安装ntp
[root@slaver1 ~]# ntpdate 192.168.1.10 #设置同步源
30 Dec 14:36:09 ntpdate[80677]: step time server 192.168.1.10 offset 0.844885 sec
[root@slave1 ~]# date 查看当前日期
2020年 12月 30日 星期三 14:36:14 CST
[root@slave1 ~]# crontab -e 部署任务计划
*/3 * * * * /usr/sbin/ntpdate 192.168.1.10
每隔3分钟进行一次同步对象为20.0.0.10(主数据库)的时间同步
[root@slave1 ~]# date 查看当前日期
2020年 12月 30日 星期三 14:36:42 CST
以上主从时间同步设置完成
4.3.2 主从复制
master主库上
1.配置数据库文件
[root@master ~]# vi /etc/my.cnf
server-id = 1 #服务器id,每个服务器不能相同
log_bin=master-bin #主服务器日志文件
log_slave_updates=true #允许中继日志读取主服务器的二进制日志
2.重启服务器,查看生成的二进制日志文件
[root@master ~]# systemctl restart mysqld.service
[root@master ~]# ls /usr/local/mysql/data/
3.数据库授权
[root@master ~]# mysql -u root -pfa123
#为所有服务器所有库授权
mysql> grant replication slave on *.* to 'myslave'@'192.168.1.%' identified by 'fa123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
#刷新,更新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'myslave'@'192.168.1.%';
+-----------------------------------------------------------+
| Grants for myslave@192.168.1.% |
+-----------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.1.%' |
+-----------------------------------------------------------+
1 row in set (0.00 sec)
#查看主的状态,记下position的值(同步的位置)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 602 | | | |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
在slaver1、slaver2从库上
[root@slaver1 ~]# vi /etc/my.cnf #修改从服务器主配置文件参数
server-id = 2 #三台数据库id不同
relay_log=relay-log-bin #从主服务器上同步日志文件记录到本地中继日志
relay_log_index=slave-relay-bin.index #定义中继日志的索引
[root@slaver1 ~]# systemctl restart mysqld.service
[root@slaver1 ~]# mysql -uroot -pfa123
#添加要同步数据的master服务器的账号、二进制日志文件、开始同步的位置(position)
mysql>change master to master_host='192.168.1.10',master_user='myslave',master_password='sword='fa123',master_log_file='master-bin.000001',master_log_pos=602;
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 #查看从服务器状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: myslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 602
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.3.3 读写分离
安装jdk环境(需要java环境)
[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.d/java.sh #环境文件最后添加
export JAVA_HOME=/usr/local/java #设置java根目录
export PATH=$PATH:$JAVA_HOME/bin #在PATH环境变量中添加JAVA根目录下的bin子目录
[root@amoeba ~]# source /etc/profile.d/java.sh
[root@amoeba ~]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/java/bin
[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
wget https://nchc.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/3.x/amoeba-mysql-3.0.5-RC-distribution.zip
部署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 ~]# vim /usr/local/amoeba/jvm.properties
32 #JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize =16m -XX:MaxPermSize=96m"
33 JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"
在三台MySQL主从库上分别给amoeba授权
mysql> grant all on *.* to 'test'@'192.168.1.%' identified by 'fa123';
Query OK, 0 rows affected, 1 warning (0.00 sec)
在amoeba上
修改amoeba主配置文件
[root@amoeba ~]# vi /usr/local/amoeba/conf/amoeba.xml
28 <property name="user">amoeba</property> #修改root为amoeba
29
30 <property name="password">123456</property> #添加数据库登陆密码
83 <property name="defaultPool">master</property> #修改multiPool为master
84
85 <!-- #注释,需删除
86 <property name="writePool">master</property> #主库写权限
87 <property name="readPool">slaves</property> #从库读权限
88 --> #注释,需删除
修改dbServers.xml 文件
[root@server1 ~]# vi /usr/local/amoeba/conf/dbServers.xml
22 <!-- mysql schema --> #注释行
23 <property name="schema">mysql</property> #MySQL5.7版本没有默认的test库,所以修改为mysql(5.7默认的数据库)
24
25 <!-- mysql user --> #注释行
26 <property name="user">test</property>
27 <!-- mysql password --> #注释行
28 <property name="password">fa123</property>
配置三台MySQL服务器主机名和地址
43 <dbServer name="master" parent="abstractServer"> #修改server1为master
44 <factoryConfig>
45 <!-- mysql ip -->
46 <property name="ipAddress">192.168.1.10</property> #修改为主库master的IP地址
47 </factoryConfig>
48 </dbServer>
49
50 <dbServer name="slaver1" parent="abstractServer"> #修改为slaver1
51 <factoryConfig>
52 <!-- mysql ip -->
53 <property name="ipAddress">192.168.1.11</property> #修改为从库1的IP
54 </factoryConfig>
</dbServer>
#添加下面slaver2模块
<dbServer name="slaver2" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.12</property>
</factoryConfig>
</dbServer>
设置地址池
57 <dbServer name="slaves" virtual="true">
58 <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
59 <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
60 <property name="loadbalance">1</property>
61
62 <!-- Separated by commas,such as: server1,server2,server1 -->
63 <property name="poolNames">slaver1,slaver2</property> #修改为slaver1,slaver2
64 </poolConfig>
65 </dbServer>
查看端口
[root@amoeba ~]# /usr/local/amoeba/bin/launcher #启动amoeba
2020-12-09 17:08:04 [INFO] Project Name=Amoeba-MySQL, PID=25373 , starting...
^C
#强行结束
[root@amoeba ~]# netstat -anpt | grep 8066
tcp6 0 0 :::8066 :::* LISTEN 25373/java
4.4.4 效果验证
客户机
[root@client ~]# yum -y install mariadb*
[root@client ~]# mysql -uamoeba -pfa123 -h 192.168.1.13 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 832429565
Server version: 5.1.45-mysql-amoeba-proxy-3.0.4-BETA Source distribution
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
主库
mysql> create database abc;
Query OK, 1 row affected (0.00 sec)
mysql> use abc;
Database changed
mysql> create table a(id int(5),name varchar(32),age int(3));
Query OK, 0 rows affected (0.01 sec)
从库1上
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
#可以看到主库中新键的库
mysql> use abc;
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_abc |
+---------------+
| a |
+---------------+
1 row in set (0.00 sec)
#可以看到主库中新键的表
从库2上
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| abc |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
#可以看到主库中新键的库
mysql> use abc;
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_abc |
+---------------+
| a |
+---------------+
1 row in set (0.00 sec)
#可以看到主库中新键的表
从库1关闭服务
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
主库添加数据
mysql> insert into a values(1,'fa',22);
Query OK, 1 row affected (0.00 sec)
mysql> select * from a;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | fa | 22 |
+------+------+------+
1 row in set (0.00 sec)
从库1查看数据为空
mysql> select * from a;
Empty set (0.00 sec)
从库2正常复制数据
mysql> select * from a;
Empty set (0.00 sec)
mysql> select * from a;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | fa | 22 |
+------+------+------+
1 rows in set (0.00 sec)
从库1开启服务后,自动同步数据
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | fa | 22 |
+------+------+------+
1 rows in set (0.00 sec)
关闭两台从库服务
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
从库中写入数据
#从库1
mysql> insert into a values(2,'wangwu',23);
Query OK, 1 row affected (0.00 sec)
mysql> select * from a;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | fa | 22 |
| 2 | wangwu | 23 |
+------+--------+------+
2 rows in set (0.00 sec)
#从库2
mysql> insert into a values(3,'chenqi',21);
Query OK, 1 row affected (0.00 sec)
mysql> select * from a;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | fa | 22 |
| 3 | chenqi | 21 |
+------+--------+------+
2 rows in set (0.00 sec)
客户端查看数据库
MySQL [(none)]> use abc;
Database changed
MySQL [abc]> select * from a;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | fa | 22 |
| 2 | wangwu | 23 |
+------+--------+------+
2 rows in set (0.01 sec)
MySQL [abc]> select * from a;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | fa | 22 |
| 3 | chenqi | 21 |
+------+--------+------+
2 rows in set (0.00 sec)
在客户端上写入数据
MySQL [abc]> insert into aaa values(5,'zhaosi',22);
Query OK, 1 row affected (0.00 sec)
只有主库会记录,客户机写入的数据
mysql> select * from a;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | fa | 22 |
| 4 | zhaosi | 19 |
+------+--------+------+
2 rows in set (0.00 sec)
从库开启同步
从库1
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | fa | 22 |
| 3 | chenqi | 21 |
| 4 | zhaosi | 19 |
+------+--------+------+
3 rows in set (0.00 sec)
从库2
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from a;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | fa | 22 |
| 2 | wangwu | 23 |
| 4 | zhaosi | 19 |
+------+--------+------+
3 rows in set (0.00 sec)
在开启同步后,主服务器上的数据会同步到各从服务器上中,但从服务器上的自己增加的数据不会同步,只会本地保存
客户端访问依然保持轮询
MySQL [abc]> select * from a;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | fa | 22 |
| 3 | chenqi | 21 |
| 4 | zhaosi | 19 |
+------+--------+------+
3 rows in set (0.00 sec)
MySQL [abc]> select * from a;
+------+--------+------+
| id | name | age |
+------+--------+------+
| 1 | fa | 22 |
| 2 | wangwu | 23 |
| 4 | zhaosi | 19 |
+------+--------+------+
3 rows in set (0.01 sec)