mysql+数据库主从原理_MySQL主从复制+读写分离原理及配置实例

最新推荐文章于 2023-03-30 06:30:00 发布

头像是狐狸吗

最新推荐文章于 2023-03-30 06:30:00 发布

阅读量245

点赞数

文章标签： mysql+数据库主从原理

本文链接：https://blog.csdn.net/weixin_30802399/article/details/113598766

版权

一、MySQL主从复制原理：

MySQL的主从复制和MySQL的读写分离两者不分家，基于主从复制的架构才可实现数据的读写分离。

1、MySQL支持的复制类型：

(1)基于语句的复制。顾名思义就是在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用这种方式的复制，效率比较高。

(2)基于行的复制。把改变的内容复制过去，而不是把命令再从主服务器上执行一遍。

(3)混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

以上三种复制类型，不需要人为干预，MySQL数据库会自动控制。

2、复制的工作过程，如下图所示：

(1)在每个事物更新数据完成之前，master在二进制日志记录这些改变。写入二进制日志完成后，master通知存储引擎提交事务。

(2)slave将master的Binary log复制到其中继日志。首先slave开始一个工作线程——I/O线程，I/O线程在master上打开一个普通的连接，然后开始Binlog dump process(Binlog转储过程)，Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

(3)SQL slave thread(SQL从线程)处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O现场曾保持一致，中继日志通常存在系统的缓存中，所以中继日志的开销很小。

复制过程有一个很重要的限制，就是在slave上的复制是串行化的，master上时并行化的。说白了就是，有可能一些数据更新是多条SQL语句同时在master上进行的，但slave进行复制时，只能一条一条的执行SQL语句进行数据同步。

二、MySQL读写分离原理：

简单来说，就如下图一样，就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理数据写入、更新操作，而从数据库处理select查询操作。

较为常见的MySQL读写分离分为两种：

1、基于程序代码内部实现：在代码中根据select、insert语句进行路由分类，这类方法目前在生产环境中应用最广泛。优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支；缺点是需要开发人员来实现，我们运维无从下手。

2、基于中间代理实现：代理位于客户端和MySQL服务器之间，代理服务器接到客户端的请求后通过判断后转发到后端数据库。中间代理有两个代表性程序：MySQL-Proxy和amoeba(变形虫)。

二者区别如下：

MySQL-Proxy是MySQL开源项目，通过自带的lua脚本进行SQL判断，虽然是MySQL官方产品，但是MySQL官方并不建议将其应用到生产环境。

amoeba使用Java语言进行开发，阿里巴巴将其用于生产环境，它不支持事务和存储过程。

虽然通过程序代码实现MySQL读写分离是一个很好的选择，但并不是所有的应用都适合在程序代码中实现读写分离，一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码的改动就比较大。所以，大型复杂的应用一般都会考虑使用代理层来实现。

三、搭建MySQL主从复制及读写分离

环境如下：

1、部署前准备：

(1)我这里所有服务器均使用centos 7。

(2)下载部署MySQL所需软件包：下载链接，提取码：2z5k

(3)准备系统映像，自行配置本地yum仓库。下载搭建读写分离所需软件：下载amoeba及JDK链接，提取码：ulz3 。

(4)应用客户端为了测试效果，必须可以使用mysql命令，所以也需要安装mysql，为了方便，安装系统映像自带的mysql即可。

(5)自行配置网络环境，确保网络互通，防火墙放行相关端口的流量，我这里为了方便，直接关闭了防火墙。

2、部署MySQL数据库(在主/从节点共三台服务器上都需要部署MySQL数据库，将以下部署过程分别在三台服务器上执行一遍)

(1)为了避免发生程序冲突、端口冲突等问题，可以先执行以下命令，进行删除系统自带的mysql程序：

[root@localhost ~]# yum -y erase mysql

(2)挂载centos系统盘，安装ncurses-devel包：

[root@localhost ~]# mount /dev/cdrom /media

[root@localhost ~]# cd /media/Packages/

[root@localhost Packages]# rpm -ivh ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm

(3)由于mysql 5.x系列版本需要cmake编译安装，所以继续安装cmake包：

[root@localhost media]# tar zxf cmake-2.8.6.tar.gz -C /tmp #解包

[root@localhost media]# cd /tmp/cmake-2.8.6/

[root@localhost cmake-2.8.6]# ./configure && gmake && gmake install #配置，编译安装

(4)创建专门用来运行mysql服务的用户，此用户不需要直接登录到系统：

[root@localhost cmake-2.8.6]# groupadd mysql

[root@localhost cmake-2.8.6]# useradd -M -s /sbin/nologin mysql -g mysql

(5)将下载的mysql源码包解压，并进行配置，编译及安装(千万要注意大小写，不要打错配置项，就算错误也可以继续后续的安装，但是，最后服务是无法启动的，千万不要打错字母，千万不要打错字母，千万不要打错字母，重要的事情说三遍)：

[root@localhost media]#tar zxf mysql-5.6.36.tar.gz -C /tmp #解压至/tmp目录

[root@localhost cmake-2.8.6]# cd /tmp/mysql-5.6.36 #切换至展开后的源码目录

[root@localhost mysql-5.6.36]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql

-DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8

-DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all

[root@localhost mysql-5.6.36]# make && make install #编译并安装

(6)对数据库目录进行权限设置：

[root@localhost mysql-5.6.36]# chown -R mysql:mysql /usr/local/mysql

(7)建立配置文件：

centos 7系统下默认支持MariaDB数据库，因此系统默认的/etc/my.cnf配置文件中是MariaDB的配置文件，而在mysql的源码目录中提供了mysql数据库默认的样本配置文件，在启动mysql数据库之前，需要先将原有的my.cnf文件替换为mysql提供的配置文件内容。

[root@localhost mysql-5.6.36]# rm -rf /etc/my.cnf #删除原有配置文件

[root@localhost mysql-5.6.36]# cp support-files/my-default.cnf /etc/my.cnf

#复制源码包中的配置文件到/etc/下

(8)初始化数据库(若数据库初始化时配置错误的话，将mysql安装目录中的data目录删掉后重新初始化即可)：

[root@localhost mysql-5.6.36]# /usr/local/mysql/scripts/mysql_install_db --user=mysql

--basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

(9)设置环境变量(为了方便在任何目录下使用mysql命令)：

[root@localhost mysql-5.6.36]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile

[root@localhost mysql-5.6.36]# . /etc/profile #立即生效

(10)当对/usr/local/mysql/bin/mysqld.sh 赋予执行权限后，继续以下操作：

[root@localhost mysql-5.6.36]# cp support-files/mysql.server /usr/local/mysql/bin/mysqld.sh

#复制到安装目录中

[root@localhost mysql-5.6.36]# chmod +x /usr/local/mysql/bin/mysqld.sh

#赋予执行权限

[root@localhost mysql-5.6.36]# cp /usr/local/mysql/bin/mysqld.sh /etc/init.d/mysqld

[root@localhost mysql-5.6.36]# vim /etc/init.d/mysqld

修改后，保存退出。继续执行如下命令：

[root@localhost mysql-5.6.36]# chkconfig --add mysqld #添加为系统服务

11、启动服务并查看mysql服务器状态：

[root@localhost mysql-5.6.36]# systemctl start mysqld #启动服务

[root@localhost mysql-5.6.36]# systemctl status mysqld #查看服务状态是否正常

3、开始搭建MySQL主从复制：

(1)建立时间同步环境：

①在MySQL主服务器上搭建时间同步服务器(关于ntp时间同步，安装系统时，一般都安装了该服务，可以在执行下面的yum操作之前，主服务器使用rpm -qa | grep ntp进行查询，从服务器使用rpm -qa | grep ntpdate查询，若已安装，则可省略下面的yum安装步骤)。

[root@master ~]# yum -y install ntp #安装ntp

#若服务器可连接Internet，直接yum安装即可；

#若无法连接，自行配置本地yum仓库，系统盘有相关软件包。

[root@master ~]# vim /etc/ntp.conf #添加下面两行配置，从而设置时区。

server 127.127.1.0 #添加到任意位置即可

fudge 127.127.1.0 stratum 8

#添加后，保存退出即可。

[root@master ~]# systemctl restart ntpd #重启服务

[root@master ~]# systemctl enable ntpd #设置开机自启动

②在slave1和slave2分别执行以下操作，从而配置时间同步。(由于两台从节点有很多相同的配置，为了方便，我会将两台从节点一样的配置，只写一遍，自行在两台服务器上分别配置即可)。

[root@slave1 ~]# yum -y install ntpdate #安装ntpdate。

[root@slave1 ~]# ntpdate 192.168.1.1

(2)配置MySQL master主服务器：

[root@master ~]# vim /etc/my.cnf #编辑MySQL主配置文件，修改或增加下面配置

log_bin = master-bin #修改

log-slave-updates = true #增加

server_id = 11 #修改

[root@master ~]# systemctl restart mysqld

[root@master ~]# mysql -u root -p #登录MySQL

Enter password: #输入密码

mysql> grant replication slave on *.* to 'myslave'@'192.168.1.%' identified by '123456';

#授权一个账户，以便从服务器连接使用

mysql> flush privileges;

mysql> show master status;

+-------------------+----------+--------------+------------------+-------------------+

+-------------------+----------+--------------+------------------+-------------------+

| master-bin.000001 | 410 | | | |

+-------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

#其中file列显示日志名，Position列显示偏移量，这两个值在后面配置从服务器时使用。

#这两个值还需根据自己查询出来的实际来变，你们可能和我这里查询出的不一样。

(3)分别配置slave1和slave2从服务器(两台slave服务器都执行一遍以下的操作)：

[root@slave1 ~]# vim /etc/my.cnf #更改或增加以下内容

server_id = 22 #修改，该id号不可和其他主/从服务器重复，另一从服务器我配置成“33”了

relay-log=relay-log-bin #增加

relay-log-index=slave-relay-bin.index #增加

#编辑好上面三号，保存退出即可。

[root@slave1 ~]# systemctl restart mysqld #重启MySQL

[root@slave1 ~]# mysql -u root -p #登录到数据库

Enter password: #输入密码

mysql> change master to master_host='192.168.1.1',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=410;

#指定主服务器及日志文件位置，并指定使用哪个用户连接主服务器。

Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave; #启动同步

Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G #查看slave状态，确保下面两个值为“yes”。

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.1.1

Master_User: myslave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 410

Relay_Log_File: relay-log-bin.000002

Relay_Log_Pos: 284

Relay_Master_Log_File: master-bin.000001

Slave_IO_Running: Yes #这个值要为“yes”。

Slave_SQL_Running: Yes #这个值也要为“yes”。

Replicate_Do_DB:

Replicate_Ignore_DB:

........................... #省略部分内容

至此，主从复制已经完成了，现在测试下主从复制的效果：

(1)登录MySQL主服务器创建一个库：

mysql>create database db_test; #创建db_test库

Query OK, 1 row affected (0.00 sec)

(2)在主、从服务器上分别查看数据库，显示的数据库相同，则主从复制没有问题。

mysql> show databases; #查看所有库

+--------------------+

| Database |

+--------------------+

| information_schema |

| db_test |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.00 sec)

4、开始搭建MySQL读写分离：

(1)接着上面配置好主从复制的环境，现在需要配置amoeba服务器(将下载的相关软件上传到amoeba服务器)：

[root@localhost ~]# mount amoeba.iso /mnt #挂载下载的.iso文件

mount: /dev/loop0 写保护，将以只读方式挂载

[root@localhost ~]# cd /mnt

[root@localhost mnt]# cp * /usr/src/ #将文件内的所有安装包复制到指定目录

[root@localhost mnt]# cd /usr/src/

#接下来的操作是在配置java环境，由于amoeba是基于jdk1.5开发的，

#所以官方推荐使用jdk1.5或1.6版本，高版本不建议使用

[root@localhost src]# chmod +x jdk-6u14-linux-x64.bin #添加执行权限

[root@localhost src]# ./jdk-6u14-linux-x64.bin #执行该文件，执行后，按空格进行翻页

....................#省略部分内容，该内容像是Windows安装软件时的协议条款，不管它

Do you agree to the above license terms? [yes or no]

yes #输入“yes”

Press Enter to continue..... #按回车键

Done. #完成

[root@localhost src]# mv jdk1.6.0_14/ /usr/local/jdk1.6

[root@localhost src]# 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

#写完保存退出即可。

[root@localhost src]# source /etc/profile #更新环境变量

[root@localhost src]# java -version #查询Java版本，显示如下说明Java安装成功。

java version "1.6.0_14"

Java(TM) SE Runtime Environment (build 1.6.0_14-b08)

Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

[root@localhost src]# mkdir /usr/local/amoeba

[root@localhost src]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/

#解包

[root@localhost src]# chmod -R 755 /usr/local/amoeba/ #调整amoeba目录权限

[root@localhost src]# /usr/local/amoeba/bin/amoeba #执行

amoeba start|stop #显示此内容说明amoeba安装成功

(2)现在配置amoeba读写分离，两个slave读负载均衡。

①master、slave1、slave2数据库中分别执行以下语句，以便开放权限给amoeba访问。

#三台数据库服务器都执行该语句。

mysql>grant all on *.* to test@'192.168.1.%' identified by '123.com';

Query OK, 0 rows affected (0.00 sec)

②编辑amoeba服务器的amoeba.xml配置文件(以下改动比较零散，仔细看下面的注释，根据注释提示进行操作)：

[root@localhost src]# cd /usr/local/amoeba/

[root@localhost amoeba]# vim conf/amoeba.xml

.......................

amoeba

123456

${amoeba.home}/conf/access_list.conf

.....................#省略部分内容

1500

master

slaves

③编辑dbServers.xml配置文件：

[root@localhost amoeba]# vim conf/dbServers.xml #编辑该配置文件

...............

test

123.com

...............

192.168.1.1

192.168.1.2

192.168.1.3

slave1,slave2

④配置无误后，可以启动amoeba软件，其默认监听端口为tcp 8066。

[root@localhost amoeba]# bin/amoeba start &

#启动服务，一定要加“&”符号，到后台执行，否则将一直占用前台命令行

[1] 5384

[root@localhost amoeba]# log4j:WARN log4j config load ............

...............#省略一部分提示信息，再按一下回车键即可。

[root@localhost init.d]# netstat -anpt | grep java #查询端口8066是否在监听

tcp6 0 0 127.0.0.1:49276 :::* LISTEN 5384/java

tcp6 0 0 :::8066 #OK，在监听 :::* LISTEN 5384/java

tcp6 0 0 192.168.1.4:41938 192.168.1.2:3306 ESTABLISHED 5384/java

tcp6 0 0 192.168.1.4:34712 192.168.1.3:3306 ESTABLISHED 5384/java

tcp6 0 0 192.168.1.4:34002 192.168.1.1:3306 ESTABLISHED 5384/java

5、现在已经主从复制+读写分离配置完毕了，开始在应用客户端连接amoeba服务器进行测试：

①应用客户端连接amoeba服务器：

[root@client ~]# yum -y install mysql

#安装系统映像中的MySQL即可，以便可以使用mysql命令

[root@client ~]# mysql -u amoeba -p 123456 -h 192.168.1.4 -P 8066

#连接amoeba服务器的8066端口

Enter password:

#输入在amoeba服务器的amoeba.xml文件配置中“amoeba”用户对应的密码

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 494299142

Server version: 5.1.45-mysql-amoeba-proxy-2.2.0

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [123456]> #连接成功

MySQL [123456]> use db_test; #切换至之前测试主从复制时创建的库

No connection. Trying to reconnect...

Connection id: 494299142

Current database: *** NONE ***

Database changed

MySQL [db_test]> create table test (id int(10),name varchar(10)); #新建一个表

Query OK, 0 rows affected (0.04 sec)

②分别在三台数据库服务器上查看是否有刚才创建的表：

mysql> use db_test; #切换至db_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; #查看所有表，可以看到刚才创建的表，OK。

+-------------------+

| Tables_in_db_test |

+-------------------+

| test |

+-------------------+

1 row in set (0.00 sec)

③关闭两台从服务器上的同步功能：

mysql>stop slave; #两台slave服务器上都需执行一下该命令

Query OK, 0 rows affected (0.00 sec)

④在主服务器上写入以下数据：

mysql>insert into test values('1','zhangsan');

Query OK, 1 row affected (0.00 sec)

⑤在从服务器上分别写入以下数据：

slave1：

mysql>insert into test values('2','lisi');

Query OK, 1 row affected (0.00 sec)

slave2：

mysql>insert into test values('3','wangwu');

Query OK, 1 row affected (0.01 sec)

⑥在应用客户端测试读操作：

MySQL [db_test]> select * from test; #第一次读test表

+------+------+

| id | name |

+------+------+

| 2 | lisi |

+------+------+

1 row in set (0.00 sec)

MySQL [db_test]> select * from test; #第二次读test表

+------+--------+

| id | name |

+------+--------+

| 3 | wangwu |

+------+--------+

1 row in set (0.01 sec)

MySQL [db_test]> select * from test; #第三次读test表

+------+------+

| id | name |

+------+------+

| 2 | lisi |

+------+------+

1 row in set (0.03 sec)

不管进行读多少次，都不会读到主服务器上的数据，说明读操作是在slave1/2上进行的。

⑦测试写操作：

MySQL [db_test]> insert into test values('666','lvjianzhao');

Query OK, 1 row affected (0.01 sec)

#在应用服务器写入一条数据

但是在应用客户端上继续查询该表，是查不到上面刚刚写入的数据的，说明写到了主服务器上，去主服务器查询就可以查到刚才写入的数据了，如下：

#在主服务器上再查询test表

mysql> mysql> select * from test;

+------+------------+

| id | name |

+------+------------+

| 1 | zhangsan |

| 666 | lvjianzhao |

+------+------------+

2 rows in set (0.00 sec)

主服务器能查到刚才在应用客户端写入的数据，但是两个从服务器是查不到的：

mysql> mysql> select * from test; #在slave1进行查询

+------+------+

| id | name |

+------+------+

| 2 | lisi |

+------+------+

1 row in set (0.00 sec)

mysql> mysql> select * from test; #在slave2进行查询

+------+--------+

| id | name |

+------+--------+

| 3 | wangwu |

+------+--------+

1 row in set (0.00 sec)

由此反复验证，已经实现了MySQL的读写分离，目前所有的写操作都会在master(主服务器)上，用来避免数据的不同步；所有的读操作都分摊给了slave(从服务器)，用来分担数据库压力，当然，在这个案例环境中还有一个问题：负责写入数据的服务器只有一台，那么这台服务器一旦宕掉了呢？不就是完蛋了么？关于这个问题，我将在以后的博文写出解决办法，暂时先从网上搜罗一篇关于MySQL高可用方案的文章，有需要的话可以参考一下：MySQL高可用方案

终于写完了这篇博文，希望可以给更多的人做一下参考吧！！！

头像是狐狸吗

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql+数据库主从原理_MySQL主从复制+读写分离原理及配置实例

一、MySQL主从复制原理：MySQL的主从复制和MySQL的读写分离两者不分家，基于主从复制的架构才可实现数据的读写分离。1、MySQL支持的复制类型：(1)基于语句的复制。顾名思义就是在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用这种方式的复制，效率比较高。(2)基于行的复制。把改变的内容复制过去，而不是把命令再从主服务器上执行一遍。(3)混合类型的复制。默认采用...
复制链接

扫一扫