大存储、大负载.doc

软工学院第一章 mysql主从分离一、教学标准1、了解mysql的优化，为什么要实现主从分离2、熟悉如何实现mysql主从分离3、熟练掌握mysql主从分离的配置4、熟练应用mysql主从分离下的php的开发二、mysql主从分离 1、为什么要实现主从分离Mysql作为目前世界上使用最广泛的免费数据库，相信所有从事系统运维和web开发的工程师都一定接触过。但在实际的生产环境中，由单台Mysql作为独立的数据库是完全不能满足实际需求的，无论是在安全性，高可用性以及高并发等各个方面。因此，一般来说都是通过 主从复制（Master-Slave）的方式来同步数据，再通过读写分离（MySQL-Proxy）来提升数据库的并发负载能力 这样的方案来进行部署与实施的。如下图所示：2、mysql主从分离配置步骤MySQL主从复制场景描述：主数据库服务器：30，MySQL已经安装，并且无应用数据。从数据库服务器：31，MySQL已经安装，并且无应用数据。2.1 主服务器上进行的操作启动mysql服务/data0/mysql/3306/mysql start通过命令行登录管理MySQL服务器/usr/local/webserver/mysql/bin/mysql -u root -p -S /tmp/mysql.sock授权给从数据库服务器31mysql GRANT REPLICATION SLAVE ON *.* to rep131 identified by password;查询主数据库状态Mysql show master status;+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000005 | 261 | | |+-+-+-+-+记录下 FILE 及 Position 的值，在后面进行从服务器操作的时候需要用到。2.2 配置从服务器修改从服务器的配置文件/data0/mysql/3306/f将 server-id = 1修改为 server-id = 10，并确保这个ID没有被别的MySQL服务所使用。启动mysql服务/data0/mysql/3306/mysql start通过命令行登录管理MySQL服务器/usr/local/webserver/mysql/bin/mysql -u root -p -S /tmp/mysql.sock执行同步SQL语句mysql change master tomaster_host=26,master_user=root,master_password=12345678, master_log_file=mysql-bin.000005,master_log_pos=261;正确执行后启动Slave同步进程mysql start slave;主从同步检查mysql show slave statusG;=* 1. row *Slave_IO_State:Master_Host: 30Master_User: rep1Master_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000005Read_Master_Log_Pos: 415Relay_Log_File: localhost-relay-bin.000008Relay_Log_Pos: 561Relay_Master_Log_File: mysql-bin.000005Slave_IO_Running: YESSlave_SQL_Running: YESReplicate_Do_DB:省略若干Master_Server_Id: 11 row in set (0.01 sec)=其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：(1)主数据库进行锁表操作，不让数据再进行写入动作mysql FLUSH TABLES WITH READ LOCK;(2)查看主数据库状态mysql show master status;+-+-+-+-+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-+-+-+-+| mysql-bin.000005 | 261 | | |+-+-+-+-+(3)记录下 FILE 及 Position 的值。将主服务器的数据文件（整个/opt/mysql/data目录）复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。(4)取消主数据库锁定mysql UNLOCK TABLES;2.3 验证主从复制效果主服务器上的操作在主服务器上创建数据库first_dbmysql create database first_db;Query Ok, 1 row affected (0.01 sec)在主服务器上创建表first_tbmysql create table first_tb(id int(3),name char(10);Query Ok, 1 row affected (0.00 sec)在主服务器上的表first_tb中插入记录mysql insert into first_tb values (001,myself);Query Ok, 1 row affected (0.00 sec)在从服务器上查看mysql show databases;=+-+| Database |+-+| information_schema | first_db | mysql | performance_schema | test |+-+5 rows in set (0.01 sec)=数据库first_db已经自动生成mysql use first_db;Database chagedmysql show tables;=+-+| Tables_in_first_db |+-+| first_tb |+-+1 row in set (0.02 sec)=数据库表first_tb也已经自动创建mysql select * from first_tb;=+-+-+| id | name |+-+-+| 1 | myself |+-+-+1 rows in set (0.00 sec)=记录也已经存在由此，整个MySQL主从复制的过程就完成了，接下来，我们进行MySQL读写分离的安装与配置。3、MySQL读写分离场景描述：数据库Master主服务器：30数据库Slave从服务器：31MySQL-Proxy调度服务器：32以下操作，均是在32即MySQL-Proxy调度服务器 上进行的。3.1 MySQL的安装与配置具体的安装过程与上文相同。3.2 检查系统所需软件包通过 rpm -qa | grep name 的方式验证以下软件包是否已全部安装。gcc* gcc-c+* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*libevent* glib*若缺少相关的软件包，可通过yum -y install方式在线安装，或直接从系统安装光盘中找到并通过rpm -ivh方式安装。3.3 编译安装luaMySQL-Proxy的读写分离主要是通过rw-splitting.lua脚本实现的，因此需要安装lua。lua可通过以下方式获得从/download.html下载源码包从搜索相关的rpm包/pub/fedora/epel/5/i386//pub/fedora/epel/5/x86_64/lua-5.1.4-4.el5.x86_64.rpm这里我们建议采用源码包进行安装cd /opt/installwget /ftp/lua-5.1.4.tar.gztar zvfx lua-5.1.4.tar.gzcd lua-5.1.4vi src/Makefile在 CFLAGS= -O2 -Wall $(MYCFLAGS) 这一行记录里加上-fPIC，更改为 CFLAGS= -O2 -Wall -fPIC $(MYCFLAGS) 来避免编译过程中出现错误。make linuxmake installcp etc/lua.pc /usr/lib/pkgconfig/export PKG_CONFIG_PATH=$PKG_CONFIG_PATH:/usr/lib/pkgconfig3.4 安装配置MySQL-ProxyMySQL-Proxy可通过以下网址获得：.tw/Downloads/MySQL-Proxy/推荐采用已经编译好的二进制版本，因为采用源码包进行编译时，最新版的MySQL-Proxy对automake，glib以及libevent的版本都有很高的要求，而这些软件包都是系统的基础套件，不建议强行进行更新。并且这些已经编译好的二进制版本在解压后都在统一的目录内，因此建议选择以下版本：32位RHEL5平台：.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz64位RHEL5平台：.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-64bit.tar.gz测试平台为RHEL5 32位，因此选择32位的软件包wget .tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gztar xzvf mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gzmv mysql-proxy-0.8.1-linux-rhel5-x86-32bit /opt/mysql-proxy创建mysql-proxy服务管理脚本mkdir /opt/mysql-proxy/init.d/vim mysql-proxy#!/bin/sh# mysql-proxy This script starts and stops the mysql-proxy daemon# chkconfig: - 78 30# processname: mysql-proxy# description: mysql-proxy is a proxy daemon to mysql# Source function library. /etc/rc.d/init.d/functions#PROXY_PATH=/usr/local/binPROXY_PATH=/opt/mysql-proxy/binprog=mysql-proxy# Source networking configuration. /etc/sysconfig/network# Check that networking is up. $NETWORKING = no & exit 0# Set default mysql-proxy configuration.#PROXY_OPTIONS=-daemonPROXY_OPTIONS=-admin-username=root -admin-password=password -proxy-read-only-backend-addresses=31:3306 -proxy-backend-addresses=30:3306 -admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua -proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.luaPROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid# Source mysql-proxy configuration.if -f /etc/sysconfig/mysql-proxy ; then . /etc/sysconfig/mysql-proxyfiPATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH# By default its all goodRETVAL=0# See how we were called.case $1 in start) # Start daemon. echo -n $Starting $prog: $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 RETVAL=$? echo if $RETVAL = 0 ; then touch /var/lock/subsys/mysql-proxy fi ; stop) # Stop daemons. echo -n $Stopping $prog: killproc $prog RETVAL=$? echo if $RETVAL = 0 ; then rm -f /var/lock/subsys/mysql-proxy rm -f $PROXY_PID fi ; restart) $0 stop sleep 3 $0 start ; condrestart) -e /var/lock/subsys/mysql-proxy & $0 restart ; status) status mysql-proxy RETVAL=$? ; *) echo Usage: $0 start|stop|restart|status|condrestart RETVAL=1 ;esacexit $RETVAL脚本参数详解：=PROXY_PATH=/opt/mysql-proxy/bin /定义mysql-proxy服务二进制文件路径PROXY_OPTIONS=-admin-username=root /定义内部管理服务器账号-admin-password=password /定义内部管理服务器密码-proxy-read-only-backend-addresses=31:3306 /定义后端只读从服务器地址-proxy-backend-addresses=30: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-proxy PID文件路径$NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS -daemon /定义以守护进程模式启动-keepalive /使进程在异常关闭后能够自动恢复-pid-file=$PROXY_PID /定义mysql-proxy PID文件路径-user=mysql /以mysql用户身份启动服务-log-level=warning /定义log日志级别，由高到低分别有(error|warning|info|message|debug)-log-file=/opt/mysql-proxy/log/mysql-proxy.log /定义log日志文件路径=cp mysql-proxy /opt/mysql-proxy/init.d/chmod +x /opt/mysql-proxy/init.d/mysql-proxymkdir /opt/mysql-proxy/runmkdir /opt/mysql-proxy/logmkdir /opt/mysql-proxy/scripts配置并使用rw-splitting.lua读写分离脚本最新的脚本我们可以从最新的mysql-proxy源码包中获取cd /opt/installwget .tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1.tar.gztar xzvf mysql-proxy-0.8.1.tar.gzcd mysql-proxy-0.8.1cp lib/rw-splitting.lua /opt/mysql-proxy/scripts修改读写分离脚本rw-splitting.lua修改默认连接，进行快速测试，不修改的话要达到连接数为4时才启用读写分离vim /opt/mysql-proxy/scripts/rw-splitting.lua=- connection poolif not proxy.global.config.rwsplit thenproxy.global.config.rwsplit = min_idle_connections = 1, /默认为4max_idle_connections = 1, /默认为8is_debug = falseend=修改完成后，启动mysql-proxy/opt/mysql-proxy/init.d/mysql-proxy start3.5 测试读写分离效果创建用于读写分离的数据库连接用户登陆主数据库服务器30，通过命令行登录管理MySQL服务器/opt/mysql/bin/mysql -uroot -pnew-passwordmysql GRANT ALL ON *.* TO proxy132 IDENTIFIED BY password;由于我们配置了主从复制功能，因此从数据库服务器31上已经同步了此操作。为了清晰的看到读写分离的效果，需要暂时关闭MySQL主从复制功能登陆从数据库服务器31，通过命令行登录管理MySQL服务器/opt/mysql/bin/mysql -uroot -pnew-password关闭Slave同步进程mysql stop slave;Query OK, 0 rows affected (0.00 sec)连接MySQL-Proxy/opt/mysql/bin/mysql -uproxy1 -ppassword -P4040 -h32登陆成功后，在first_db数据的first_tb表中插入两条记录mysql use first_db;Database changedmysql insert into first_tb values (007,first);Query Ok, 1 row affected (0.00 sec)mysql insert into first_tb values (110,second);Query Ok, 1 row affected (0.00 sec)查询记录mysql select * from first_tb;=+-+-+| id | name |+-+-+| 1 | myself |+-+-+1 rows in set (0.00 sec)=通过读操作并没有看到新记录mysql quit退出MySQL-Proxy下面，分别登陆到主从数据库服务器，对比记录信息首先，检查主数据库服务器mysql select * from first_tb;=+-+-+| id | name |+-+-+| 1 | myself |+-+-+| 007 | first |+-+-+| 110 | second |+-+-+3 rows in set (0.00 sec)=两条新记录都已经存在然后，检查从数据库服务器mysql select * from first_tb;=+-+-+| id | name |+-+-+| 1 | myself |+-+-+1 rows in set (0.00 sec)=没有新记录存在由此验证，我们已经实现了MySQL读写分离，目前所有的写操作都全部在Master主服务器上，用来避免数据的不同步；另外，所有的读操作都分摊给了其它各个Slave从服务器上，用来分担数据库压力。经验分享：1.当MySQL主从复制在 show slave statusG 时出现Slave_IO_Running或Slave_SQL_Running 的值不为YES时，需要首先通过 stop slave 来停止从服务器，然后再执行一次本文 2.1与2.2 章节中的步骤即可恢复，但如果想尽可能的同步更多的数据，可以在Slave上将master_log_pos节点的值在之前同步失效的值的基础上增大一些，然后反复测试，直到同步OK。因为MySQL主从复制的原理其实就是从服务器读取主服务器的binlog，然后根据binlog的记录来更新数据库。2.MySQL-Proxy的rw-splitting.lua脚本在网上有很多版本，但是最准确无误的版本仍然是源码包中所附带的lib/rw-splitting.lua脚本，如果有lua脚本编程基础的话，可以在这个脚本的基础上再进行优化；3.MySQL-Proxy实际上非常不稳定，在高并发或有错误连接的情况下，进程很容易自动关闭，因此打开-keepalive参数让进程自动恢复是个比较好的办法，但还是不能从根本上解决问题，因此通常最稳妥的做法是在每个从服务器上安装一个MySQL-Proxy供自身使用，虽然比较低效但却能保证稳定性；4.一主多从的架构并不是最好的架构，通常比较优的做法是通过程序代码和中间件等方面，来规划，比如设置对表数据的自增id值差异增长等方式来实现两个或多个主服务器，但一定要注意保证好这些主服务器数据的完整性，否则效果会比多个一主多从的架构还要差；5.MySQL-Cluster 的稳定性也不是太好；6.Amoeba for MySQL 是一款优秀的中间件软件，同样可以实现读写分离，负载均衡等功能，并且稳定性要大大超过MySQL-Proxy，建议大家用来替代MySQL-Proxy，甚至MySQL-Cluster三、教学中要注意的关键点在教学过程中一定要告诉学生们在开发过程中在服务器上没有安装MySQL-Proxy这个软件或功能相同的软件的情况下如何通过php程序实现mysql的读写分离四、作业同学们下去后自己按照步骤配置一下，实现mysql的读写分离第二章 mysql数据分区一、教学标准1、了解mysql数据分区方法2、熟悉如何实现mysql数据分区3、熟练掌握mysql数据的切分方法二、mysql数据分区1、为什么要切分数据通过 MySQL 复制功能所实现的扩展总是会受到数据库大小的限制，一旦数据库过于庞大，尤其是当写入过于频繁，很难由一台主机支撑的时候，我们还是会面临到扩展瓶颈。这时候，我们就必须找其他技术手段来解决这个瓶颈，那我们介绍一下mysql的数据切分技术。2、何谓数据切分mysql数据库中的数据是以文件的形势存在磁盘上的，默认放在/mysql/data下面（可以通过f中的datadir来查看），一张表主要对应着三个文件，一个是frm存放表结构的，一个是myd存放表数据的，一个是myi存表索引的。如果一张表的数据量太大的话，那么myd,myi就会变的很大，查找数据就会变的很慢，这个时候我们可以利用mysql的分区功能，在物理上将这一张表对应的三个文件，分割成许多个小块，这样呢，我们查找一条数据时，就不用全部查找了，只要知道这条数据在哪一块，然后在那一块找就行了。如果表的数据太大，可能一个磁盘放不下，这个时候，我们可以把数据分配到不同的磁盘里面去。分区的二种方式1，横向分区什么是横向分区呢？就是横着来分区了，举例来说明一下，假如有100W条数据，分成十份，前10W条数据放到第一个分区，第二个10W条数据放到第二个分区，依此类推。也就是把表分成了十份。取出一条数据的时候，这条数据包含了表结构中的所有字段，也就是说横向分区，并没有改变表的结构。2，纵向分区什么是纵向分区呢？就是竖来分区了，举例来说明，在设计用户表的时候，开始的时候没有考虑好，而把个人的所有信息都放到了一张表里面去，这样这个表里面就会有比较大的字段，如个人简介，而这些简介呢，也许不会有好多人去看，所以等到有人要看的时候，在去查找，分表的时候，可以把这样的大字段，分开来。感觉数据库的分区好像是切苹果，到底是横着切呢，还是竖着切，根据个人喜好了，mysql提供的分区属于第一种，横向分区，并且细分成很多种方式。下面将举例说明一下。3、mysql数据如何分区 要实现mysql分区，首先要查看当前安装的mysql版本是否支持mysql分区功能，mysql5.1及以上的版本才支持支持分区功能首先需要查看，当前数据库是否支持分区mysqlSHOW VARIABLES LIKE %partition%;+-+-+| Variable_name| Value |+-+-+| have_partitioning | YES|+-+-+1 row in set (0.03 sec)如果看到上面的have_partitioning|YES的话，表明当前数据库支持分区功能。Mysql5.1版本现在支持4种分区类型：RANGE分区：基于属于一个给定连续区间的列值，把多行分配给分区；LIST分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择；HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式；KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。3.1 分区实例操作介绍Range分区方法创建range分区mysqlCREATETABLEIFNOTEXISTSuser(idint(11)NOTNULLAUTO_INCREMENTCOMMENT用户ID,namevarchar(50)NOTNULLDEFAULTCOMMENT名称,sexint(1)NOTNULLDEFAULT0COMMENT0为男，1为女,PRIMARYKEY(id)ENGINE=MyISAMDEFAULTCHARSET=utf8AUTO_INCREMENT=1PARTITIONBYRANGE(id)(PARTITIONp0VALUESLESSTHAN(3),PARTITIONp1VALUESLESSTHAN(6),PARTITIONp2VALUESLESSTHAN(9),PARTITIONp3VALUESLESSTHAN(12),PARTITIONp4VALUESLESSTHANMAXVALUE);/插入一些数据mysqlINSERTINTOtest.user(name,sex)VALUES(tank,0)-,(zhang,1),(ying,1),(张,1),(映,0),(test1,1),(tank2,1)-,(tank1,1),(test2,1),(test3,1),(test4,1),(test5,1),(tank3,1)-,(tank4,1),(tank5,1),(tank6,1),(tank7,1),(tank8,1),(tank9,1)-,(tank10,1),(tank11,1),(tank12,1),(tank13,1),(tank21,1),(tank42,1);插入完后可以到存放数据库表文件的地方看一下，如果不知道数据库文件在哪里放着可以到f里面查看一下，datadir后面就是，进入test目录下执行下面的命令可以看到user表的数据文件和索引文件都被分成了四个文件，数据结构文件没变rootBlackGhosttest#ls|grepuser|xargsdu-sh4.0Kuser#P#p0.MYD4.0Kuser#P#p0.MYI4.0Kuser#P#p1.MYD4.0Kuser#P#p1.MYI4.0Kuser#P#p2.MYD4.0Kuser#P#p2.MYI4.0Kuser#P#p3.MYD4.0Kuser#P#p3.MYI4.0Kuser#P#p4.MYD4.0Kuser#P#p4.MYI12Kuser.frm4.0Kuser.par/取出数据mysqlselectcount(id)ascountfromuser;+-+|count|+-+|25|+-+/删除第四个分区mysqlaltertableuserdroppartitionp4;执行这条命令后可以去test目录下看看有何变化，可看到里面p4的文件没有了，第四个分区里面有14条数据，剩下的3个分区只有11条数据，但是统计出来的文件大小都是4.0K，从这儿我们可以看出分区的最小区块是4K，下面咱们在查询一下mysqlselectcount(id)ascountfromuser;+-+|count|+-+|11|+-+Mysql也可以对现在已经存在的表进行分区操作，下面咱们来试验一下：mysqlaltertableaapartitionbyRANGE(id)-(PARTITIONp1VALUESlessthan(1),-PARTITIONp2VALUESlessthan(5),-PARTITIONp3VALUESlessthanMAXVALUE);假设当前表中总共有15条mysqlselectcount(*)fromaa;+-+|count(*)|+-+|15|+-+下面试验删除一个分区的操作：mysqlaltertableaadroppartitionp2;执行完之后再查询一下：/只有11条了，说明对现有的表分区成功了mysqlselectcount(*)fromaa;+-+|count(*)|+-+|11|+-+List分区方法LIST分区中每个分区的定义和选择是基于某列的值从属于一个值列表集中的一个值，而RANGE分区是从属于一个连续区间值的集合。下面咱们开始试验/这种方式失败 mysqlCREATETABLEIFNOTEXISTSlist_part( -idint(11)NOTNULLAUTO_INCREMENTCOMMENT用户ID, -province_idint(2)NOTNULLDEFAULT0COMMENT省, -namevarchar(50)NOTNULLDEFAULTCOMMENT名称, -sexint(1)NOTNULLDEFAULT0COMMENT0为男，1为女, -PRIMARYKEY(id) -)ENGINE=INNODBDEFAULTCHARSET=utf8AUTO_INCREMENT=1 -PARTITIONBYLIST(province_id)( -PARTITIONp0VALUESIN(1,2,3,4,5,6,7,8), -PARTITIONp1VALUESIN(9,10,11,12,16,21), -PARTITIONp2VALUESIN(13,14,15,19), -PARTITIONp3VALUESIN(17,18,20,22,23,24) -); ERROR1503(HY000):APRIMARYKEYmustincludeallcolumnsinthetablespartitioningfunction /这种方式成功 mysqlCREATETABLEIFNOTEXISTSlist_part( -idint(11)NOTNULLCOMMENT用户ID, -province_idint(2)NOTNULLDEFAULT0COMMENT省, -namevarchar(50)NOTNULLDEFAULTCOMMENT名称, -sexint(1)NOTNULLDEFAULT0COMMENT0为男，1为女 -)ENGINE=INNODBDEFAULTCHARSET=utf8 -PARTITIONBYLIST(province_id)( -PARTITIONp0VALUESIN(1,2,3,4,5,6,7,8), -PARTITIONp1VALUESIN(9,10,11,12,16,21), -PARTITIONp2VALUESIN(13,14,15,19), -PARTITIONp3VALUESIN(17,18,20,22,23,24) -); QueryOK,0rowsaffected(0.33sec)/这种方式失败 mysql CREATE TABLE IF NOT EXISTS list_part ( - id int(11) NOT NULL AUTO_INCREMENT COMMENT 用户ID, - province_id int(2) NOT NULL DEFAULT 0 COMMENT 省, - nam