MySQL主从复制与读写分离的理论+实操（配有详细图释）

最新推荐文章于 2024-11-05 11:19:17 发布

TaKe___Easy

最新推荐文章于 2024-11-05 11:19:17 发布

阅读量702

点赞数

文章标签： mysql linux 数据库

本文链接：https://blog.csdn.net/TaKe___Easy/article/details/116127556

版权

主从复制与读写分离

一、为什么需要主从复制与读写分离？
二、主从复制原理
- 2.1 MySQL支持的复制类型
- 2.2 MySQL主从复制工作过程
三、读写分离原理
- 3.1 MySQL读写分离工作过程
四、搭建主从复制与读写分离
五、总结

一、为什么需要主从复制与读写分离？

因为日常企业应用中，成熟的业务通常数据量都比较大，单台MySQL无论是在安全性、高可用性以及高并发方面都无法满足实际的需求。所以我们需要配置多台主从数据库服务器先通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力

二、主从复制原理

MySQL的主从复制和MySQL的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离

2.1 MySQL支持的复制类型

基于语句的复制
- 在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率较高
基于行的复制
- 把改变的内容复制过去，而不是把命令在从服务器上执行一遍
混合类型的复制
- 当发现基于语句无法精确复制时，就会采用基于行的复制

2.2 MySQL主从复制工作过程

在这里插入图片描述

1.在每个事务更新数据完成之前，Master在二进制日志（Binary log）里记录这些改变。写入二进制日志完成后，Master通知存储引擎提交事务
2.Slave将Master的Binary log复制到其中继日志。首先，Slave开始一个工作线程——I/O线程，I/O线程在Master上打开一个普通的连接，然后开始Binlog dump process。Binlog dump process从Master的二进制日志中读取事件，如果已经跟上Master，它会睡眠并等待Master产生新的事件。I/O线程将这些事件写入中继日志
SQL salve thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新Slave的数据，使其与Master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的成本开销很小
复制过程中有一个很重要的限制：复制在Slave上是串行化的，也就是说Master上的并行更新操作不能在Slave上并行操作

三、读写分离原理

只在主服务器上写，只在从服务器上读。基于原理则是让主数据库处理事务性查询，从数据库处理select查询。数据库复制用于将事务性查询导致的变更同步到集群中的从数据库

3.1 MySQL读写分离工作过程

在这里插入图片描述

读写分离是基于程序代码内部+中间代理层（MySQL-Proxy、Amoeba）实现的
代理一般位于客户端和服务器之间，代理服务器接到客户端请求通过判断后转发到后端数据库
MySQL-Proxy：是MySQL开源项目，通过其自带的lua脚本进行SQL判断，虽然是MySQL官方产品，但是MySQL官方并不建议将MySQL-Proxy用到生成环境中
Amoeba：基于Jave语言由陈思儒进行开发，不支持事务和存储过程
并不是所有应用都适合在程序代码中实现读写分离，比如一些大型复杂的Jave应用，如果在程序代码中实现读写分离会对代码改成较大，所以像这种应用一般会考虑使用代理层来实现

四、搭建主从复制与读写分离

4.1 实验环境

mySQL服务器也作为时间同步服务器
mysql三台
- masrer 192.168.131.9
- slave1 192.168.131.12
- slave2 192.168.131.13
amoeba一台
- 192.168.131.14
客户端一台
- 192.168.131.15

4.2 时间同步（主服务器）

[root@localhost ~]# yum -y install ntp
[root@localhost ~]# vim /etc/ntp.conf 
 25 server 127.127.131.0                                【127代表本地，所以这里两个127代表本地的192.168】
 26 fudge  127.127.131.0 stratum 8                      【允许131网段的主机与本机同步，时间环开为8个】

【开启服务并关闭防火墙】
[root@localhost ~]# systemctl start ntpd
[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# setenforce 0

节点服务器slave1 192.168.131.12设置
节点服务器slave2 192.168.131.13设置

[root@localhost ~]# yum -y install ntp ntpdate.x86_64 
[root@localhost ~]# systemctl start ntpd
[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# setenforce 0 
[root@localhost ~]# /usr/sbin/ntpdate 192.168.131.9
[root@localhost ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.131.9
[root@localhost ~]# crontab -l
*/30 * * * * /usr/sbin/ntpdate 192.168.131.9

4.3 安装MySQL

三个服务器都需安装

[root@localhost ~]# cd /opt/
[root@localhost opt]# ls
rh
[root@localhost opt]# rz -E
rz waiting to receive.
[root@localhost opt]# tar zxvf mysql-5.6.26.tar.gz 
[root@localhost opt]# useradd -s /sbin/nologin mysql       【创建程序用户】
[root@localhost opt]# yum -y install \                     【安装依赖包】
> gcc \ 
> gcc-c++ \
> make \
> pcre-devel \
> expat-devel \
> perl \
> ncurses-devel \
> autoconf \
> cmake
[root@localhost opt]# cd mysql-5.6.26/
[root@localhost mysql-5.6.26]# cmake  \          【编译】
> -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
> -DDEFAULT_CHARSET=utf8 \
> -DDEFAULT_COLLATION=utf8_general_ci \
> -DEXTRA_CHARSETS=all \
> -DSYSCONFIDIR=/etc \
> -DMYSQL_DATADIR=/home/mysql/ \
> -DMYSQL_UNIX_ADDR=/home/mysql/mysql.sock
[root@localhost mysql-5.6.26]# make -j2 && make install
[root@localhost mysql-5.6.26]# chown -R mysql.mysql /usr/local/mysql/
[root@localhost mysql-5.6.26]# echo export PATH=$PATH:/usr/local/mysql/bin/ >>/etc/profile
[root@localhost mysql-5.6.26]# export PATH=$PATH:/usr/local/mysql/bin/
[root@localhost mysql-5.6.26]# source /etc/profile
[root@localhost mysql-5.6.26]# cd /opt/mysql-5.6.26
[root@localhost mysql-5.6.26]# cp support-files/my-default.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y 
[root@localhost mysql-5.6.26]# cp support-files/mysql.server /etc/init.d/mysqld
[root@localhost mysql-5.6.26]# chmod 755 /etc/init.d/mysqld
[root@localhost mysql-5.6.26]# chkconfig --add /etc/init.d/mysqld
[root@localhost mysql-5.6.26]# chkconfig mysqld --level 35 on

【修改数据文件目录和工作目录】
[root@localhost ~]# vim /etc/init.d/mysqld    
 46 basedir=/usr/local/mysql      
 47 datadir=/home/mysql


[root@localhost opt]# /usr/local/mysql/scripts/mysql_install_db \
> --user=mysql \
> --ldata=/var/lib/mysql \
> --basedir=/usr/local/mysql \
> --datadir=/home/mysql
[root@localhost opt]# service mysqld start 
Starting MySQL. SUCCESS!
[root@localhost opt]# mysqladmin -uroot -p password '5514'
[root@localhost opt]# mysql -uroot -p5514

4.4 配置主从同步

master服务器的mysqld下

[root@localhost opt]# vim /etc/my.cnf
 30 log_bin=master-bin
 31 log_slave=updates=true
 32 server_id=21

【重启服务】
[root@localhost opt]# service mysqld restart 
Shutting down MySQL. SUCCESS! 
Starting MySQL. SUCCESS!

修改从服务器配置文件

[root@localhost opt]# vim /etc/my.cnf
log-bin=mysql-bin                               【开启二进制日志文件】
server_id=22                                    【slave1为22，slave2为23】
relay-log=relay-log-bin                         【从服务器上同步日志文件记录到本地】  
relay-log-index=slave-relay-bin.index           【确定relay-log的位置和名称（index索引）】
[root@localhost opt]# service mysqld restart 
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

配置数据库规则（主服务器授权）

[root@localhost opt]# mysql -uroot -p5514
mysql> grant replication slave on *.* to 'myslave'@'192.168.131.%' identified by '111222';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
【grant：授权】
【replication：复制】
【给给从服务器提权，允许其复制所有数据库的所有表（使用myslave的身份进行）】
【允许192.168.131网段，使用的密码为111222（第一个账户）】


【查看master数据库状态】
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      412 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
【新搭建的MySQL数据库的节点就为412】


【设置完从服务器的指向后查看】
mysql> show processlist;
+----+---------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User    | Host                 | db   | Command     | Time | State                                                                 | Info             |
+----+---------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
|  2 | root    | localhost            | NULL | Query       |    0 | init                                                                  | show processlist |
|  3 | myslave | 192.168.131.12:33438 | NULL | Binlog Dump |   93 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
|  4 | myslave | 192.168.131.13:53300 | NULL | Binlog Dump |   93 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
+----+---------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+

配置数据库规则（从服务器指向）

[root@localhost opt]# mysql -uroot -p5514
mysql> change master to master_host='192.168.131.9',master_user='myslave',master_password='111222',master_log_file='master-bin.000001',master_log_pos=412;
mysql> start slave;

【查看下面两项为yes则同步成功（I/O，SQL，DUMP）】
mysql> show slave status\G;
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

4.5 验证主从复制

主服务器（master）

mysql> create database qz;
mysql> use qz;
mysql> create table qz1(id int(8),name varchar(10),age int(3));
mysql> insert into qz1 values(1,'qq',20),(2,'ww',40);
mysql> select * from qz1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | qq   |   20 |
|    2 | ww   |   40 |
+------+------+------+

从服务器（slave）

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
【当主服务器创建完相关信息后再查看】
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| qz                 |
| test               |
+--------------------+
mysql> use qz;
mysql> show tables;
+--------------+
| Tables_in_qz |
+--------------+
| qz1          |
+--------------+

mysql> select * from qz1;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | qq   |   20 |
|    2 | ww   |   40 |
+------+------+------+

4.5 搭建读写分离

[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# setenforce 0
[root@localhost ~]# systemctl disable firewalld.service 
[root@localhost ~]# cd /opt/
[root@localhost opt]# rz -E
rz waiting to receive.
[root@localhost opt]# ls
amoeba-mysql-binary-2.2.0.tar.gz  jdk-6u14-linux-x64.bin  rh
[root@localhost opt]# cp jdk-6u14-linux-x64.bin /usr/local/
[root@localhost opt]# cd /usr/local/
[root@localhost local]# ls
bin  etc  games  include  jdk-6u14-linux-x64.bin  lib  lib64  libexec  sbin  share  src
[root@localhost local]# ./jdk-6u14-linux-x64.bin          【可执行的二进制文件】
[root@localhost local]# chmod +x jdk-6u14-linux-x64.bin   【给该文件加可权限】 
[root@localhost local]# ./jdk-6u14-linux-x64.bin
[root@localhost local]# ls
bin  etc  games  include  jdk1.6.0_14  jdk-6u14-linux-x64.bin  lib  lib64  libexec  sbin  share  src
[root@localhost local]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@localhost local]# ls
bin  etc  games  include  jdk1.6  jdk-6u14-linux-x64.bin  lib  lib64  libexec  sbin  share  src
[root@localhost local]# export JAVA_HOME=/usr/local/jdk1.6
[root@localhost local]# export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
[root@localhost local]# export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
[root@localhost local]# export AMOEBA_HOME=/usr/local/amoeba
[root@localhost local]# export PATH=$PATH:$AMOEBA_HOME/bin
[root@localhost local]# source /etc/profile

4.6 配置amoeba

[root@localhost local]# mkdir /usr/local/amoeba
[root@localhost local]# cd /opt/
【解压amoeba压缩包】
[root@localhost opt]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@localhost opt]# chmod -R 755 /usr/local/amoeba/
【测试是否安装成功】
[root@localhost opt]# /usr/local/amoeba/bin/amoeba
amoeba start|stop

【在三台MySQL服务器添加授权开发给amoeba访问】
mysql>grant all on *.* to qz2@'192.168.131.%' identified by '222333';



[root@localhost opt]# cd /usr/local/amoeba/conf/
[root@localhost conf]# ls
access_list.conf  amoeba.dtd  amoeba.xml  dbserver.dtd  dbServers.xml  function.dtd  functionMap.xml  log4j.dtd  log4j.xml  rule.dtd  ruleFunctionMap.xml  rule.xml
[root@localhost conf]# vim amoeba.xml 
【修改30行的账户名（数据库访问amoeba服务器时使用的账号）】
 30                                         <property name="user">amoeba</property>
【修改32行的账户密码（数据库访问amoeba服务器时使用账号时的密码）】
 32                                         <property name="password">333444</property>

【修改115行的默认池为主服务器】
115                 <property name="defaultPool">master</property>

【将117-120的注释“ <!-- -->”取消，即把120号复制到117号，然后修改118行的读池为主服务器，119行写池为从服务器】
117                 <!-- -->
118                 <property name="writePool">master</property>
119                 <property name="readPool">slaves</property>
    

[root@localhost conf]# vim dbServers.xml
【将23行text修改为mysql】
 23                         <property name="schema">mysql</property>
【将26行修改成amoeba访问三台数据库的账号】
 26                         <property name="user">qz2</property>
【将28-30行的访问数据库密码修改成222333并取消注释】
 28                         <!--  mysql password -->
 29                         <property name="password">222333</property>
 30   
【将45行修改为数据库主服务器名】
 45         <dbServer name="master"  parent="abstractServer">
【将48行修改成master服务器的ip】
 48                         <property name="ipAddress">192.168.131.9</property>
【将52行修改成从服务器名】
 52         <dbServer name="slave1"  parent="abstractServer">
 53                 <factoryConfig>
 54                         <!-- mysql ip -->
【将55行修改成从服务器地址】
 55                         <property name="ipAddress">192.168.131.12</property>
 56                 </factoryConfig>
 57         </dbServer>
【复制52-57行并添加第二台从服务器名和ip地址】
 58 
 59 
 60         <dbServer name="slave2"  parent="abstractServer">
 61                 <factoryConfig>
 62                         <!-- mysql ip -->
 63                         <property name="ipAddress">192.168.131.13</property>
 64                 </factoryConfig>
 65         </dbServer>
【将66行的多个服务器池的名称修改为slaves】
 66         <dbServer name="slaves" virtual="true">
【在72行添加两个从服务器的服务器名（slave1,slave2）】
 72                         <property name="poolNames">slave1,slave2</property>

4.7 MySQL客户端测试

下载安装mariadb客户端并远程登录

[root@localhost ~]# setenforce 0
[root@localhost ~]# systemctl stop firewalld.service 
[root@localhost ~]# yum -y install mariadb
【远程登录amoeba服务器】
[root@localhost ~]# mysql -u amoeba -p333444 -h 192.168.131.14 -P8066

测试一：amoeba服务器是否关联后端MySQL

在这里插入图片描述

测试二：测试MySQL读写分离

在这里插入图片描述

测试三：读写分离架构，对于“读”的任务是怎样操作

在这里插入图片描述

五、总结

5.1 一定要关闭防火墙！！！

5.2 主从复制

主从复制中最重要的两个日志文件：Master（主）的Binary log（二进制日志）与Slave（从）Relay log（中继日志）
主从复制中最重要的三个线程：Master（主）的dump线程与Slave（从）的I/O线程、SQL线程

5.3 读写分离

读写分离原理：只在主服务器上写，只在从服务器上读。让主数据库处理事务性查询，从数据库处理select查询。
读写分离是基于程序代码内部+中间代理层（MySQL-Proxy、Amoeba）实现的
“流量”是被轮询读取
三个账号
- ① 主从同步（数据库相互授权）
- ②amoeba（客户端登录amoeba）
- ③amoeba （amoeba登录数据库集群）（该账号及密码也需在数据库集群里进行授权操作）