mysql主从分离和负载均衡

╰半橙微兮° 2023-09-25 09:56 151阅读 0赞

## mysql读写分离实现要多少台主机 ##

Mysql作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维的工程师都一定接触过。但在实际的生产环境中，由单台Mysql作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。

因此，一般来说都是通过主从复制(Master-Slave)的方式来同步数据，再通过读写分离(MySQL-Proxy)来提升数据库的并发负载能力这样的方案来进行部署与实施的。

如下图所示：

下面是我在实际工作过程中所整理的笔记，在此分享出来，以供大家参考。

一、MySQL的安装与配置

值得一提的是，我的安装过程都是源码包编译安装的，并且所有的配置与数据等都统一规划到了/opt/mysql目录中，因此在一台服务器上安装完成以后，可以将整个mysql目录打包，然后传到其它服务器上解包，便可立即使用。

二、MySQL主从复制

场景描述：

主数据库服务器：192.168.10.130，MySQL已经安装，并且无应用数据。

从数据库服务器：192.168.10.131，MySQL已经安装，并且无应用数据。

2.1主服务器上进行的操作

启动mysql服务

/opt/mysql/init.d/mysqlstart

通过命令行登录管理MySQL服务器

/opt/mysql/bin/mysql-uroot-p'new-password'

授权给从数据库服务器192.168.10.131

mysql>GRANTREPLICATIONSLAVEON\*.\*to'rep1'@'192.168.10.131'identifiedby‘password’;

查询主数据库状态

Mysql>showmasterstatus;

\+------------------+----------+--------------+------------------+

\+------------------+----------+--------------+------------------+

|mysql-bin.000005|261|||

\+------------------+----------+--------------+------------------+

记录下FILE及Position的值，在后面进行从服务器操作的时候需要用到。

2.2配置从服务器

修改从服务器的配置文件/opt/mysql/etc/my.cnf

将server-id=1修改为server-id=10，并确保这个ID没有被别的MySQL服务所使用。

启动mysql服务

/opt/mysql/init.d/mysqlstart

通过命令行登录管理MySQL服务器

/opt/mysql/bin/mysql-uroot-p'new-password'

执行同步SQL语句

mysql>changemasterto

master\_host=’192.168.10.130’,

master\_user=’rep1’,

master\_password=’password’,

master\_log\_file=’mysql-bin.000005’,

master\_log\_pos=261;

正确执行后启动Slave同步进程

mysql>startslave;

主从同步检查

mysql>showslavestatus\\G

==============================================

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

Slave\_IO\_State:

Master\_Host:192.168.10.130

Master\_User:rep1

Master\_Port:3306

Connect\_Retry:60

Master\_Log\_File:mysql-bin.000005

Read\_Master\_Log\_Pos:415

Relay\_Log\_File:localhost-relay-bin.000008

Relay\_Log\_Pos:561

Relay\_Master\_Log\_File:mysql-bin.000005

Slave\_IO\_Running:YES

Slave\_SQL\_Running:YES

Replicate\_Do\_DB:

……………省略若干……………

Master\_Server\_Id:1

1rowinset(0.01sec)

==============================================

其中Slave\_IO\_Running与Slave\_SQL\_Running的值都必须为YES，才表明状态正常。

如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：

(1)主数据库进行锁表操作，不让数据再进行写入动作

mysql>FLUSHTABLESWITHREADLOCK;

(2)查看主数据库状态

mysql>showmasterstatus;

(3)记录下FILE及Position的值。

将主服务器的数据文件(整个/opt/mysql/data目录)复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。

(4)取消主数据库锁定

mysql>UNLOCKTABLES;

2.3验证主从复制效果

主服务器上的操作

在主服务器上创建数据库first\_db

mysql>createdatabasefirst\_db;

QueryOk,1rowaffected(0.01sec)

在主服务器上创建表first\_tb

mysql>createtablefirst\_tb(idint(3),namechar(10));

QueryOk,1rowaffected(0.00sec)

在主服务器上的表first\_tb中插入记录

mysql>insertintofirst\_tbvalues(001,’myself’);

QueryOk,1rowaffected(0.00sec)

在从服务器上查看

mysql>showdatabases;

=============================

\+--------------------+

|Database|

\+--------------------+

|information\_schema|

|first\_db|

|mysql|

|performance\_schema|

|test|

\+--------------------+

5rowsinset(0.01sec)

=============================

数据库first\_db已经自动生成

mysql>usefirst\_db

Databasechaged

mysql>showtables;

=============================

\+--------------------+

|Tables\_in\_first\_db|

\+--------------------+

|first\_tb|

\+--------------------+

1rowinset(0.02sec)

=============================

数据库表first\_tb也已经自动创建

mysql>select\*fromfirst\_tb;

=============================

\+------+------+

|id|name|

\+------+------+

|1|myself|

\+------+------+

1rowsinset(0.00sec)

=============================

记录也已经存在

由此，整个MySQL主从复制的过程就完成了，接下来，我们进行MySQL读写分离的安装与配置。

三、MySQL读写分离

场景描述：

数据库Master主服务器：192.168.10.130

数据库Slave从服务器：192.168.10.131

MySQL-Proxy调度服务器：192.168.10.132

以下操作，均是在192.168.10.132即MySQL-Proxy调度服务器上进行的。

3.1MySQL的安装与配置

具体的安装过程与上文相同。

3.2检查系统所需软件包

通过rpm-qa|grepname的方式验证以下软件包是否已全部安装。

gcc\*gcc-c++\*autoconf\*automake\*zlib\*libxml\*ncurses-devel\*libmcrypt\*libtool\*flex\*pkgconfig\*

libevent\*glib\*

若缺少相关的软件包，可通过yum-yinstall方式在线安装，或直接从系统安装光盘中找到并通过rpm-ivh方式安装。

3.3编译安装lua

MySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua。

lua可通过以下方式获得

下载源码包

从rpm.pbone.net搜索相关的rpm包

download.fedora.redhat.com/pub/fedora/epel/5/i386/lua-5.1.4-4.el5.i386.rpm

download.fedora.redhat.com/pub/fedora/epel/5/x86\_64/lua-5.1.4-4.el5.x86\_64.rpm

这里我们建议采用源码包进行安装

cd/opt/install

wgethz

tarzvfxlua-5.1.4.tar.gz

cdlua-5.1.4

visrc/Makefile

在CFLAGS=-O2-Wall$(MYCFLAGS)这一行记录里加上-fPIC，更改为CFLAGS=-O2-Wall-fPIC$(MYCFLAGS)来避免编译过程中出现错误。

makelinux

makeinstall

cpetc/lua.pc/usr/lib/pkgconfig/

exportPKG\_CONFIG\_PATH=$PKG\_CONFIG\_PATH:/usr/lib/pkgconfig

3.4安装配置MySQL-Proxy

MySQL-Proxy可通过以下网址获得：

推荐采用已经编译好的二进制版本，因为采用源码包进行编译时，最新版的MySQL-Proxy对automake，glib以及libevent的版本都有很高的要求，而这些软件包都是系统的基础套件，不建议强行进行更新。

并且这些已经编译好的二进制版本在解压后都在统一的目录内，因此建议选择以下版本：

测试平台为RHEL532位，因此选择32位的软件包

tarxzvfmysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz

mvmysql-proxy-0.8.1-linux-rhel5-x86-32bit/opt/mysql-proxy

创建mysql-proxy服务管理脚本

mkdir/opt/mysql-proxy/init.d/

vimmysql-proxy

01\#!/bin/sh

03\#mysql-proxyThisscriptstartsandstopsthemysql-proxydaemon

05\#chkconfig:-7830

06\#processname:mysql-proxy

07\#description:mysql-proxyisaproxydaemontomysql

09\#Sourcefunctionlibrary.

10./etc/rc.d/init.d/functions

12\#PROXY\_PATH=/usr/local/bin

13PROXY\_PATH=/opt/mysql-proxy/bin

15prog="mysql-proxy"

17\#Sourcenetworkingconfiguration.

18./etc/sysconfig/network

20\#Checkthatnetworkingisup.

21\[$\{NETWORKING\}="no"\]&&exit0

23\#Setdefaultmysql-proxyconfiguration.

24\#PROXY\_OPTIONS="--daemon"

25PROXY\_OPTIONS="--admin-username=root--admin-password=password--proxy-read-only-backend-addresses=192.168.10.131:3306--proxy-backend-addresses=192.168.10.130:3306--admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua"

26PROXY\_PID=/opt/mysql-proxy/run/mysql-proxy.pid

28\#Sourcemysql-proxyconfiguration.

29if\[-f/etc/sysconfig/mysql-proxy\];then

30./etc/sysconfig/mysql-proxy

33PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY\_PATH

35\#Bydefaultit'sallgood

36RETVAL=0

38\#Seehowwewerecalled.

39case"$1"in

40start)

41\#Startdaemon.

42echo-n$"Starting$prog:"

43$NICELEVEL$PROXY\_PATH/mysql-proxy$PROXY\_OPTIONS--daemon--pid-file=$PROXY\_PID--user=mysql--log-level=warning--log-file=/opt/mysql-proxy/log/mysql-proxy.log

44RETVAL=$?

45echo

46if\[$RETVAL=0\];then

47touch/var/lock/subsys/mysql-proxy

50stop)

51\#Stopdaemons.

52echo-n$"Stopping$prog:"

53killproc$prog

54RETVAL=$?

55echo

56if\[$RETVAL=0\];then

57rm-f/var/lock/subsys/mysql-proxy

58rm-f$PROXY\_PID

61restart)

62$0stop

63sleep3

64$0start

66condrestart)

67\[-e/var/lock/subsys/mysql-proxy\]&&$0restart

69status)

70statusmysql-proxy

71RETVAL=$?

74echo"Usage:$0\{start|stop|restart|status|condrestart\}"

75RETVAL=1

77esac

79exit$RETVAL

脚本参数详解：

==============================================

PROXY\_PATH=/opt/mysql-proxy/bin//定义mysql-proxy服务二进制文件路径

PROXY\_OPTIONS="--admin-username=root\\//定义内部管理服务器账号

\--admin-password=password\\//定义内部管理服务器密码

\--proxy-read-only-backend-addresses=192.168.10.131:3306\\//定义后端只读从服务器地址

\--proxy-backend-addresses=192.168.10.130:3306\\//定义后端主服务器地址

\--admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua\\//定义lua管理脚本路径

\--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua"\\//定义lua读写分离脚本路径

PROXY\_PID=/opt/mysql-proxy/run/mysql-proxy.pid//定义mysql-proxyPID文件路径

$NICELEVEL$PROXY\_PATH/mysql-proxy$PROXY\_OPTIONS\\

\--daemon\\//定义以守护进程模式启动

\--keepalive\\//使进程在异常关闭后能够自动恢复

\--pid-file=$PROXY\_PID\\//定义mysql-proxyPID文件路径

\--user=mysql\\//以mysql用户身份启动服务

\--log-level=warning\\//定义log日志级别，由高到低分别有(error|warning|info|message|debug)

\--log-file=/opt/mysql-proxy/log/mysql-proxy.log//定义log日志文件路径

==============================================

cpmysql-proxy/opt/mysql-proxy/init.d/

chmod+x/opt/mysql-proxy/init.d/mysql-proxy

mkdir/opt/mysql-proxy/run

mkdir/opt/mysql-proxy/log

mkdir/opt/mysql-proxy/scripts

配置并使用rw-splitting.lua读写分离脚本

最新的脚本我们可以从最新的mysql-proxy源码包中获取

cd/opt/install

tarxzvfmysql-proxy-0.8.1.tar.gz

cdmysql-proxy-0.8.1

cplib/rw-splitting.lua/opt/mysql-proxy/scripts

![mysql主从分离和负载均衡\_mysql主从和读写分离实现][mysql_mysql]

## 目前有什么优秀的实现mysql读写分离的方案 ##

1.高可用且性能稳定可靠；

这个属于架构上的，架构上要做高可用，和中间件没关系

2.能够很好的解决脑裂问题；

架构上可以解决

3.能够解决主从数据库之间的数据同步时的延迟导致数据访问不一致的问题。

这个中间件没办法解决，要不你买san，要不你使用DRBD，但是DRBD效率比较差，还有就是确认数据复制到从库后，主库事务再提交，网易开源的一个mysql分支有这个功能

目前个人认为比较好的是dbware，atlas没用过，但是我看是基于MySQL-Proxy，这玩意配置有的复杂

本回答由提问者推荐

## mysql数据库的读写分离怎么做啊？有谁能提供文档吗？不胜感激 ##

把查询 和 更新写在不同的方法里面，调用不同的数据库，业务端来出来就行了没看懂什么意思？

![mysql主从分离和负载均衡\_mysql主从和读写分离实现\_02][mysql_mysql_02]

## mysql 读写分离 有几种实现方法 ##

利用mysql proxy来实现的。 MySQL Proxy最强大的一项功能是实现“读写分离(Read/Write Splitting)”。基本的原理是让主数据库处理事务性查询，而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。mysql-proxy是官方提供的mysql中间件产品可以实现负载平衡，读写分离，failover等，但其不支持大数据量的分库分表且性能较差。

其他mysql开源中间件产品有：atlas，cobar，tddl。你可以查阅一下相关信息和各自的优缺点。

[mysql_mysql]: https://img2.baidu.com/it/u=2098770938,369177214&fm=26&fmt=auto
[mysql_mysql_02]: https://img1.baidu.com/it/u=930347077,3179999092&fm=15&fmt=auto