http://www.slideshare.net/secret/IzjEa4qGWlMeLH
第二步，写一个包括参数，变量，变量赋值，条件判断，UPDATE语句，SELECT返回结果集的完整的一个存储过程，如下：
CREATE PROCEDURE plogin (
p_username char(15), p_password char(32), p_ip char(18), p_logintime datetime )
LABEL_PROC: BEGIN
DECLARE v_uid mediumint(8);
DECLARE v_realpassword char(32); DECLARE v_nickname varchar(30); DECLARE v_oltime smallint(6);
SELECT u.uid, u.password, f.nickname, u.oltime INTO v_uid, v_realpassword, v_nickname, v_oltime
FROM cdb_members u INNER JOIN cdb_memberfields f ON f.uid = u.uid WHERE u.username = p_username;
IF (v_uid IS NULL) THEN SELECT 2 AS ErrorCode; LEAVE LABEL_PROC; END IF;
IF (p_password <> v_realpassword) THEN SELECT 3 AS ErrorCode; LEAVE LABEL_PROC; END IF;
UPDATE ipsp_userexpands SET lastloginip = p_ip, lastlogintime = p_logintime WHERE uid = v_uid;
SELECT 0 AS ErrorCode, v_uid AS uid, v_nickname AS nickname, v_oltime AS oltime;
END LABEL_PROC //
首先要说的是给变量赋值的语法，MySQL中使用SELECT u.uid, u.password, f.nickname, u.oltimeINTO v_uid, v_realpassword, v_nickname, v_oltime FROM cdb_members u INNER JOINcdb_memberfields f ON f.uid = u.uid WHERE u.username = p_username;这种方式给变量赋值。
其次是条件判断的语法结构，如下所示：
IF ... THEN ...; ELSE
IF ... THEN ...; ELSEIF ...; ELSE ...; END IF; END IF;
最后说说LEAVE 语法的使用。当满足某种条件，不继续执行下面的SQL时，在MS SQL SERVER中使用RETURN语法，在MySQL中我没有找到对应的关键字，但是这里可以利用LEAVE语法来满足要求，在存储过程的BEGIN前定义一个标签，如：“LABEL_PROC:” 然后再需要用到RETURN中断执行的地方执行“LEAVE LABEL_PROC;”即可。
第三步，创建一个执行动态SQL的存储过程。
CREATE PROCEDURE ipsp_getresourcedir (
p_hashcode char(40) )
LABEL_PROC: BEGIN
DECLARE v_sql varchar(200);
SET v_sql = CONCAT('SELECT filedir FROM ipsp_resources WHERE hashcode =\\'', p_hashcode, '\\' LIMIT 0, 1');
SET @sql = v_sql;
PREPARE sl FROM @sql;
EXECUTE sl;
DEALLOCATE PREPARE sl;
END LABEL_PROC //
这里提一下 “\\”是转义字符，拼接成的SQL类
似 SELECT filedir FROM ipsp_resources WHEREhashcode ='xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' LIMIT 0, 1
另外@sql这个属于用户变量，具体用法请查询MySQL参考手册。
如果有在MS SQL SERVER上编写存储过程的经验的话，看完这些，我想基本的MySQL存储过程编程应该可以应付了吧！
想了解更多的内容可查询MySQL参考手册或者相关书籍！
Keywords:MySQL,Stored Procedure,存储过程,小强,hsqzzzl,http://hi.baidu.com/hsqzzzl
mysql 5.0存储过程学习总结
http://www.ccvita.com/100.html
mysql存储过程的创建，删除，调用及其他常用命令
mysql 5.0存储过程学习总结
一.创建存储过程 1.基本语法：
create procedure sp_name() begin ……… end
2.参数传递 二.调用存储过程
1.基本语法：call sp_name()
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递 三.删除存储过程 1.基本语法：
drop procedure sp_name//
2.注意事项
(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程 四.区块，条件，循环
1.区块定义，常用
begin …… end;
也可以给区块起别名，如：
lable:begin ……….. end lable;
可以用leave lable;跳出区块，执行区块以后的代码 2.条件语句
if 条件 then statement else
statement end if;
3.循环语句 (1).while循环
[label:] WHILE expression DO
statements
END WHILE [label] ;
(2).loop循环
[label:] LOOP
statements
END LOOP [label];
(3).repeat until循环
[label:] REPEAT
statements
UNTIL expression
END REPEAT [label] ;
五.其他常用命令
1.show procedure status
显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等 2.show create procedure sp_name 显示某一个存储过程的详细信息
mysql存储过程中要用到的运算符
mysql存储过程学习总结－操作符
算术运算符
+ 加 SET var1=2+2; 4 - 减 SET var2=3-2; 1 * 乘 SET var3=3*2; 6
/ 除 SET var4=10/3; 3.3333 DIV 整除 SET var5=10 DIV 3; 3 % 取模 SET var6=10%3 ; 1 比较运算符
> 大于 1>2 False < 小于 2<1 False
<= 小于等于 2<=2 True >= 大于等于 3>=2 True
BETWEEN 在两值之间 5 BETWEEN 1 AND 10 True
NOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 False IN 在集合中 5 IN (1,2,3,4) False
NOT IN 不在集合中 5 NOT IN (1,2,3,4) True = 等于 2=3 False
<>, != 不等于 2<>3 False
<=> 严格比较两个NULL值是否相等 NULL<=>NULL True LIKE 简单模式匹配 \"Guy Harrison\" LIKE \"Guy%\" True
REGEXP 正则式匹配 \"Guy Harrison\" REGEXP \"[Gg]reg\" False IS NULL 为空 0 IS NULL False
IS NOT NULL 不为空 0 IS NOT NULL True 逻辑运算符 与(AND)
AND TRUE FALSE NULL TRUE FALSE NULL TRUE FALSE FALSE NULL FALSE NULL NULL NULL NULL 或(OR)
OR TRUE FALSE NULL TRUE TRUE TRUE TRUE TRUE FALSE NULL FALSE TRUE NULL NULL NULL
异或(XOR)
XOR TRUE FALSE NULL FALSE TRUE NULL TRUE TRUE FALSE NULL FALSE NULL NULL NULL NULL 位运算符
| 位或 & 位与 << 左移位
>> 右移位
~ 位非(单目运算，按位取反)
mysq存储过程中常用的函数，字符串类型操作，数学类，日期时间类。
mysql存储过程基本函数
一.字符串类
CHARSET(str) //返回字串字符集 CONCAT (string2 [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0 LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符 LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1
mysql> select substring(’abcd’,0,2); +———————–+ | substring(’abcd’,0,2) | +———————–+ | |
+———————–+ 1 row in set (0.00 sec)
mysql> select substring(’abcd’,1,2); +———————–+ | substring(’abcd’,1,2) | +———————–+ | ab |
+———————–+ 1 row in set (0.02 sec)
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符 UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符 SPACE(count) //生成count个空格 二.数学类
ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制 CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换 FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数 HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(’DEF’)返回4142143 也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19 LEAST (number , number2 [,..]) //求最小值 MOD (numerator ,denominator ) //求余 POWER (number ,power ) //求指数 RAND([seed]) //随机数
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
注：返回类型并非均为整数，如： (1)默认变为整形值 mysql> select round(1.23); +————-+ | round(1.23) | +————-+ | 1 | +————-+
1 row in set (0.00 sec)
mysql> select round(1.56); +————-+ | round(1.56) | +————-+ | 2 | +————-+ 1 row in set (0.00 sec)
(2)可以设定小数位数，返回浮点型数据 mysql> select round(1.567,2); +—————-+ | round(1.567,2) | +—————-+ | 1.57 | +—————-+
1 row in set (0.00 sec)
SIGN (number2 ) //返回符号,正负或0 SQRT(number2) //开平方
三.日期时间类
ADDTIME (date2 ,time_interval ) //将time_interval加到date2 CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区 CURRENT_DATE ( ) //当前日期 CURRENT_TIME ( ) //当前时间
CURRENT_TIMESTAMP ( ) //当前时间戳 DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间 DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间 DATEDIFF (date1 ,date2 ) //两个日期差 DAY (date ) //返回日期的天 DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天 DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分 MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串 MAKETIME (hour ,minute ,second ) //生成时间串 MONTHNAME (date ) //英文月份名 NOW ( ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示 TIMEDIFF (datetime1 ,datetime2 ) //两个时间差 TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周 YEAR (datetime ) //年份 QUARTER(datetime) //季度
DAYOFMONTH(datetime) //月的第几天 HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期 MICROSECOND(datetime) //微秒 MONTH(datetime) //月 MINUTE(datetime) //分
附:可用在INTERVAL中的类型
DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND
得到当前月天数例子：
SELECT DAYOFMONTH(LAST_DAY(CURDATE())); 得到当前月第一天例子：
SELECT
DATE_FORMAT((LAST_DAY(CURDATE())-DAYOFMONTH(LAST_DAY(CURDATE()))+1), '%Y-%m-%d');
得到上个月的第一天例子：
SELECT DATE_SUB(DATE_SUB(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL EXTRACT(DAY FROM NOW())-1 DAY),INTERVAL 1 MONTH); 得到上个月的最后一天例子：
SELECT DATE_SUB(DATE_SUB(DATE_FORMAT(NOW(),'%y-%m-%d'),INTERVAL EXTRACT(DAY FROM NOW()) DAY),INTERVAL 0 MONTH) AS date; 查询上个季度的记录例子：
SELECT * FROM tb_result WHERE QUARTER(result_time)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)); 查询本季度的记录例子：
SELECT * FROM tb_result WHERE QUARTER(result_time)=QUARTER(NOW()); 查询本周的记录例子：
SELECT * FROM tb_result WHERE WEEKOFYEAR(result_time)=WEEKOFYEAR(NOW()); 查询上周的记录例子：
如果系统当前日期为星期日：
SELECT * FROM tb_result WHERE result_time>=DATE_ADD(NOW(),INTERVAL -(8+WEEKDAY(NOW())) DAY) AND result_time<=DATE_ADD(NOW(),INTERVAL -(1+WEEKDAY(NOW())) DAY);
如果系统当前日期为星期一~星期六：
SELECT * FROM tb_result WHERE result_time>=DATE_ADD(NOW(),INTERVAL -(7+WEEKDAY(NOW())) DAY) AND result_time<=DATE_ADD(NOW(),INTERVAL -(1+WEEKDAY(NOW())) DAY); 查询本月的记录例子：
SELECT * FROM tb_result WHERE MONTH(result_time)=MONTH(NOW()) AND YEAR(result_time)=YEAR(NOW()); 查询上个月的记录例子：
SELECT * FROM tb_result WHERE result_time<=LAST_DAY(DATE_ADD(NOW(),INTERVAL -1 MONTH)) AND result_time>=DATE_FORMAT(CONCAT(EXTRACT(YEAR_MONTH FROM DATE_ADD(NOW(),INTERVAL -1 MONTH)),'01'),'%Y-%m-%d'); 查询本年记录的例子：
SELECT * FROM tb_result WHERE YEAR(result_time)=YEAR(NOW()); 查询上一年记录的例子：
SELECT * FROM tb_result YEAR(result_time)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR));
WHERE
ySQL错误代码速查表汇总
(2010-12-28 09:28:59) 转载
标签： 分类： 数据库 mysql error code it
MYSQL ERROR CODE
1005：创建表失败 1005：创建表失败 1006：创建数据库失败
1007：数据库已存在，创建数据库失败 1008：数据库不存在，删除数据库失败
1009：不能删除数据库文件导致删除数据库失败 1010：不能删除数据目录导致删除数据库失败 1011：删除数据库文件失败
1012：不能读取系统表中的记录 1020：记录已被其他用户修改
1021：硬盘剩余空间不足，请加大硬盘可用空间 1022：关键字重复，更改记录失败 1023：关闭时发生错误
1024：读文件错误
1025：更改名字时发生错误 1026：写文件错误 1032：记录不存在
1036：数据表是只读的，不能对它进行修改
1037：系统内存不足，请重启数据库或重启服务器 1038：用于排序的内存不足，请增大排序缓冲区
1040：已到达数据库的最大连接数，请加大数据库可用连接数 1041：系统内存不足 1042：无效的主机名 1043：无效连接
1044：当前用户没有访问数据库的权限 1045：不能连接数据库，用户名或密码错误 1048：字段不能为空 1049：数据库不存在 1050：数据表已存在 1051：数据表不存在 10：字段不存在
1062：字段值重复，入库失败
10：处理控制请求时，服务出现意外情况。 1065：无效的SQL语句，SQL语句为空
1081：不能建立Socket连接
1114：数据表已满，不能容纳任何记录 1116：打开的数据表太多
1129：数据库出现异常，请重启数据库
1130：连接数据库失败，没有连接数据库的权限 1133：数据库用户不存在
1141：当前用户无权访问数据库 1142：当前用户无权访问数据表
1143：当前用户无权访问数据表中的字段 1146：数据表不存在
1147：未定义用户对数据表的访问权限 1149：SQL语句语法错误
1158：网络错误，出现读错误，请检查网络连接状况 1159：网络错误，读超时，请检查网络连接状况 1160：网络错误，出现写错误，请检查网络连接状况 1161：网络错误，写超时，请检查网络连接状况 1169：字段值重复，更新记录失败 1177：打开数据表失败 1180：提交事务失败 1181：回滚事务失败 1203：当前用户和数据库建立的连接已到达数据库的最大连接数，请增大可用的数据库连接数或重启数据库 1205：加锁超时
1211：当前用户没有创建用户的权限
1216：外键约束检查失败，更新子表记录失败
1217：外键约束检查失败，删除或修改主表记录失败
1226：当前用户使用的资源已超过所允许的资源，请重启数据库或重启服务器 1227：权限不足，您无权进行此操作 1235：MySQL版本过低，不具有本功能 1235：MySQL版本过低，不具有本功
http://dev.mysql.com/doc/refman/5.1/zh/stored-procedures.html#case-statement BOOK
http://book.51cto.com/art/201012/237760.htm SOFTWARE
http://www.apachefriends.org/zh_cn/xampp.html
LAMP 调优之：MySQL 服务器调优
时间:2011-09-15 09:04 来源:IBM 作者:Sean A. Walberg 点击:
748 次 【字号：大 中 小】
关于 MySQL 调优 有 3 种方法可以加快 MySQL 服务器的运行速度，效率从低到高依次为： 替换有问题的硬件。 对 MySQL 进程的设置进行调优。 对查询进行优化。 替换有问题的硬件通常是我们的第一考虑，主要原因是数据库会占用大量资源。不过这种解决方案也就仅
关于 MySQL 调优
有 3 种方法可以加快 MySQL 服务器的运行速度，效率从低到高依次为： 替换有问题的硬件。
对 MySQL 进程的设置进行调优。 对查询进行优化。
替换有问题的硬件通常是我们的第一考虑，主要原因是数据库会占用大量资源。不过这种解决方案也就仅限于此了。实际上，您通常可以让处理器(CPU)或磁盘速度加倍，也可以让内存增大 4 到 8 倍。
第二种方法是对 MySQL 服务器(也称为 mysqld)进行调优。对这个进程进行调优意味着适当地分配内存，并让 mysqld 了解将会承受何种类型的负载。加快磁盘运行速度不如减少所需的磁盘访问次数。类似地，确保 MySQL 进程正确操作就意味着它花费在服务查询上的时间要多于花费在处理后台任务(如处理临时磁盘表或打开和关闭文件)上的时间。对 mysqld 进行调优是本文的重点。
最好的方法是确保查询已经进行了优化。这意味着对表应用了适当的索引，查询是按照可以充分利用 MySQL 功能的方式来编写的。尽管本文并没有包含查询调优方面的内容(很多著作中已经针对这个主题进行了探讨)，不过它会配置 mysqld 来报告可能需要进行调优的查询。
虽然已经为这些任务指派了次序，但是仍然要注意硬件和 mysqld 的设置以利于适当地调优查询。机器速度慢也就罢了，我曾经见过速度很快的机器在运行设计良好的查询时由于负载过重而失败，因为 mysqld 被大量繁忙的工作所占用而不能服务查询。
记录慢速查询
在一个 SQL 服务器中，数据表都是保存在磁盘上的。索引为服务器提供了一种在表中查找特定数据行的方法，而不用搜索整个表。当必须要搜索整个表时，就称为表扫描。通常来说，您可能只希望获得表中数据的一个子集，因此全表扫描会浪费大量的磁盘 I/O，因此也就会浪费大量时间。当必须对数据进行连接时，这个问题就更加复杂了，因为必须要对连接两端的多行数据进行比较。
当然，表扫描并不总是会带来问题;有时读取整个表反而会比从中挑选出一部分数据更加有效(服务器进程中查询规划器用来作出这些决定)。如果索引的使用效率很低，或者根本就不能使用索引，则会减慢查询速度，而且随着服务器上的负载和表大小的增加，这个问题会变得更加显著。执行时间超过给定时间范围的查询就称为慢速查询。
您可以配置 mysqld 将这些慢速查询记录到适当命名的慢速查询日志中。管理员然后会查看这个日志来帮助他们确定应用程序中有哪些部分需要进一步调查。清单 1 给出了要启用慢速查询日志需要在 my.cnf 中所做的配置。
清单 1. 启用 MySQL 慢速查询日志
1. 2. 3. 4. 5. 6. 7. 8.
[mysqld]
; enable the slow query log, default 10 seconds log-slow-queries
; log queries taking longer than 5 seconds long_query_time = 5
; log queries that don't use indexes even if they take less t
han long_query_time
; MySQL 4.1 and newer only log-queries-not-using-indexes
这三个设置一起使用，可以记录执行时间超过 5 秒和没有使用索引的查询。请注意有关 log-queries-not-using-indexes 的警告：您必须使用 MySQL 4.1 或更高版本。慢速查询日志都保存在 MySQL 数据目录中，名为 hostname-slow.log。如果希望使用一个不同的名字或路径，可以在 my.cnf 中使用 log-slow-queries = /new/path/to/file 实现此目的。 阅读慢速查询日志最好是通过 mysqldumpslow 命令进行。指定日志文件的路径，就可以看到一个慢速查询的排序后的列表，并且还显示了它们在日志文件中出现的次数。一个非常有用的特性是 mysqldumpslow 在比较结果之前，会删除任何用户指定的数据，因此对同一个查询的不同调用被计为一次;这可以帮助找出需要工作量最多的查询。
对查询进行缓存
很多 LAMP 应用程序都严重依赖于数据库，但却会反复执行相同的查询。每次执行查询时，数据库都必须要执行相同的工作 —— 对查询进行分析，确定如何执行查询，从磁盘中加载信息，然后将结果返回给客户机。MySQL 有一个特性称为查询缓存，它将(后面会用到的)查询结果保存在内存中。在很多情况下，这会极大地提高性能。不过，问题是查询缓存在默认情况下是禁用的。
将 query_cache_size = 32M 添加到 /etc/my.conf 中可以启用 32MB 的查询缓存。 监视查询缓存
在启用查询缓存之后，重要的是要理解它是否得到了有效的使用。MySQL 有几个可以查看的变量，可以用来了解缓存中的情况。清单 2 给出了缓存的状态。
清单 2. 显示查询缓存的统计信息
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14.
mysql> SHOW STATUS LIKE 'qcache%'; +-------------------------+------------+ | Variable_name | Value | +-------------------------+------------+ | Qcache_free_blocks | 5216 | | Qcache_free_memory | 1406 | | Qcache_hits | 25816882 | | Qcache_inserts | 3602109 | | Qcache_lowmem_prunes | 281680433 | | Qcache_not_cached | 79740667 | | Qcache_queries_in_cache | 16927 | | Qcache_total_blocks | 47042 | +-------------------------+------------+ 8 rows in set (0.00 sec)
这些项的解释如表 1 所示。
表 1. MySQL 查询缓存变量
变量名 说明 Qcache_free_blocks 缓存中相邻内存块的个数。数目大说明可能有碎片。FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个空闲块。 Qcache_free_memory Qcache_hits Qcache_inserts 缓存中的空闲内存。 每次查询在缓存中命中时就增大。 每次插入一个查询时就增大。命中次数除以插入次数就是不中比率；用 1 减去这个值就是命中率。在上面这个例子中，大约有 87% 的查询都在缓存中命中。 Qcache_lowmem_prunes 缓存出现内存不足并且必须要进行清理以便为更多查询提供空间的次数。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，或者内存很少。（上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）。 Qcache_not_cached 不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句。 Qcache_queries_in_cache Qcache_total_blocks 当前缓存的查询（和响应）的数量。 缓存中块的数量。 通常，间隔几秒显示这些变量就可以看出区别，这可以帮助确定缓存是否正在有效地使用。运行 FLUSH STATUS 可以重置一些计数器，如果服务器已经运行了一段时间，这会非常有帮助。
使用非常大的查询缓存，期望可以缓存所有东西，这种想法非常诱人。由于 mysqld 必须要对缓存进行维护，例如当内存变得很低时执行剪除，因此服务器可能会在试图管理缓存时而陷入困境。作为一条规则，如果 FLUSH QUERY CACHE 占用了很长时间，那就说明缓存太大了。 强制
您可以在 mysqld 中强制一些来确保系统负载不会导致资源耗尽的情况出现。清单 3 给出了 my.cnf 中与资源有关的一些重要设置。
清单 3. MySQL 资源设置
1. 2.
set-variable=max_connections=500 set-variable=wait_timeout=10
3. max_connect_errors = 100
连接最大个数是在第一行中进行管理的。与 Apache 中的 MaxClients 类似，其想法是确保只建立服务允许数目的连接。要确定服务器上目前建立过的最大连接数，请执行 SHOW STATUS LIKE 'max_used_connections'。
第 2 行告诉 mysqld 终止所有空闲时间超过 10 秒的连接。在 LAMP 应用程序中，连接数据库的时间通常就是 Web 服务器处理请求所花费的时间。有时候，如果负载过重，连接会挂起，并且会占用连接表空间。如果有多个交互用户或使用了到数据库的持久连接，那么将这个值设低一点并不可取!
最后一行是一个安全的方法。如果一个主机在连接到服务器时有问题，并重试很多次后放弃，那么这个主机就会被锁定，直到 FLUSH HOSTS 之后才能运行。默认情况下，10 次失败就足以导致锁定了。将这个值修改为 100 会给服务器足够的时间来从问题中恢复。如果重试 100 次都无法建立连接，那么使用再高的值也不会有太多帮助，可能它根本就无法连接。
缓冲区和缓存
MySQL 支持超过 100 个的可调节设置;但是幸运的是，掌握少数几个就可以满足大部分需要。查找这些设置的正确值可以通过 SHOW STATUS 命令查看状态变量，从中可以确定 mysqld 的运作情况是否符合我们的预期。给缓冲区和缓存分配的内存不能超过系统中的现有内存，因此调优通常都需要进行一些妥协。
MySQL 可调节设置可以应用于整个 mysqld 进程，也可以应用于单个客户机会话。 服务器端的设置
每个表都可以表示为磁盘上的一个文件，必须先打开，后读取。为了加快从文件中读取数据的过程，mysqld 对这些打开文件进行了缓存，其最大数目由 /etc/mysqld.conf 中的 table_cache 指定。清单 4 给出了显示与打开表有关的活动的方式。
清单 4. 显示打开表的活动
1. 2. 3. 4. 5.
mysql> SHOW STATUS LIKE 'open%tables'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Open_tables | 5000 |
6. 7. 8.
| Opened_tables | 195 | +---------------+-------+ 2 rows in set (0.00 sec)
清单 4 说明目前有 5,000 个表是打开的，有 195 个表需要打开，因为现在缓存中已经没有可用文件描述符了(由于统计信息在前面已经清除了，因此可能会存在 5,000 个打开表中只有 195 个打开记录的情况)。如果 Opened_tables 随着重新运行 SHOW STATUS 命令快速增加，就说明缓存命中率不够。如果 Open_tables 比 table_cache 设置小很多，就说明该值太大了(不过有空间可以增长总不是什么坏事)。例如，使用 table_cache = 5000 可以调整表的缓存。
与表的缓存类似，对于线程来说也有一个缓存。 mysqld 在接收连接时会根据需要生成线程。在一个连接变化很快的繁忙服务器上，对线程进行缓存便于以后使用可以加快最初的连接。
清单 5 显示如何确定是否缓存了足够的线程。
清单 5. 显示线程使用统计信息
1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
mysql> SHOW STATUS LIKE 'threads%'; +-------------------+--------+ | Variable_name | Value | +-------------------+--------+ | Threads_cached | 27 | | Threads_connected | 15 | | Threads_created | 838610 | | Threads_running | 3 | +-------------------+--------+ 4 rows in set (0.00 sec)
此处重要的值是 Threads_created，每次 mysqld 需要创建一个新线程时，这个值都会增加。如果这个数字在连续执行 SHOW STATUS 命令时快速增加，就应该尝试增大线程缓存。例如，可以在 my.cnf 中使用 thread_cache = 40 来实现此目的。
关键字缓冲区保存了 MyISAM 表的索引块。理想情况下，对于这些块的请求应该来自于内存，而不是来自于磁盘。清单 6 显示了如何确定有多少块是从磁盘中读取的，以及有多少块是从内存中读取的。
清单 6. 确定关键字效率
1. 2. 3. 4. 5. 6. 7. 8.
mysql> show status like '%key_read%'; +-------------------+-----------+ | Variable_name | Value | +-------------------+-----------+ | Key_read_requests | 1635268 | | Key_reads | 98247 | +-------------------+-----------+ 2 rows in set (0.00 sec)
Key_reads 代表命中磁盘的请求个数， Key_read_requests 是总数。命中磁盘的读请求数除以读请求总数就是不中比率 —— 在本例中每 1,000 个请求，大约有 0.6 个没有命中内存。如果每 1,000 个请求中命中磁盘的数目超过 1 个，就应该考虑增大关键字缓冲区了。例如，key_buffer = 384M 会将缓冲区设置为 384MB。
临时表可以在更高级的查询中使用，其中数据在进一步进行处理(例如 GROUP BY 字句)之前，都必须先保存到临时表中;理想情况下，在内存中创建临时表。但是如果临时表变得太大，就需要写入磁盘中。清单 7 给出了与临时表创建有关的统计信息。
清单 7. 确定临时表的使用
1. 2. 3. 4. 5. 6. 7. 8. 9.
mysql> SHOW STATUS LIKE 'created_tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 30660 | | Created_tmp_files | 2 | | Created_tmp_tables | 32912 | +-------------------------+-------+ 3 rows in set (0.00 sec)
每次使用临时表都会增大 Created_tmp_tables;基于磁盘的表也会增大
Created_tmp_disk_tables。对于这个比率，并没有什么严格的规则，因为这依赖于所涉及的查询。长时间观察 Created_tmp_disk_tables 会显示所创建的磁盘表的比率，您可以确定设置的效率。 tmp_table_size 和 max_heap_table_size 都可以控制临时表的最大大小，因此请确保在 my.cnf 中对这两个值都进行了设置。 每个会话的设置
下面这些设置针对于每个会话。在设置这些数字时要十分谨慎，因为它们在乘以可能存在的连接数时候，这些选项表示大量的内存!您可以通过代码修改会话中的这些数字，或者在 my.cnf 中为所有会话修改这些设置。
当 MySQL 必须要进行排序时，就会在从磁盘上读取数据时分配一个排序缓冲区来存放这些数据行。如果要排序的数据太大，那么数据就必须保存到磁盘上的临时文件中，并再次进行排序。如果 sort_merge_passes 状态变量很大，这就指示了磁盘的活动情况。清单 8 给出了一些与排序相关的状态计数器信息。
清单 8. 显示排序统计信息
1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
mysql> SHOW STATUS LIKE \"sort%\"; +-------------------+---------+ | Variable_name | Value | +-------------------+---------+ | Sort_merge_passes | 1 | | Sort_range | 79192 | | Sort_rows | 2066532 | | Sort_scan | 44006 | +-------------------+---------+ 4 rows in set (0.00 sec)
如果 sort_merge_passes 很大，就表示需要注意 sort_buffer_size。例如， sort_buffer_size = 4M 将排序缓冲区设置为 4MB。
MySQL 也会分配一些内存来读取表。理想情况下，索引提供了足够多的信息，可以只读入所需要的行，但是有时候查询(设计不佳或数据本性使然)需要读取表中大量数据。要理解这种行为，需要知道运行了多少个 SELECT 语句，以及需要读取表中的下一行数据的次数(而不是通过索引直接访问)。实现这种功能的命令如清单 9 所示。
清单 9. 确定表扫描比率
1. 2. 3. 4. 5. 6.
mysql> SHOW STATUS LIKE \"com_select\"; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | Com_select | 318243 | +---------------+--------+
7. 8. 9. 10. 11. 12. 13. 14. 15.
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE \"handler_read_rnd_next\"; +-----------------------+-----------+ | Variable_name | Value | +-----------------------+-----------+ | Handler_read_rnd_next | 165959471 | +-----------------------+-----------+ 1 row in set (0.00 sec)
Handler_read_rnd_next / Com_select 得出了表扫描比率 —— 在本例中是 521:1。如果该值超过 4000，就应该查看read_buffer_size，例如 read_buffer_size = 4M。如果这个数字超过了 8M，就应该与开发人员讨论一下对这些查询进行调优了! 3 个必不可少的工具
尽管在了解具体设置时，SHOW STATUS 命令会非常有用，但是您还需要一些工具来解释 mysqld 所提供的大量数据。我发现有 3 个工具是必不可少的;在 参考资料 一节中您可以找到相应的链接。
大部分系统管理员都非常熟悉 top 命令，它为任务所消耗的 CPU 和内存提供了一个不断更新的视图。 mytop 对 top 进行了仿真;它为所有连接上的客户机以及它们正在运行的查询提供了一个视图。mytop 还提供了一个有关关键字缓冲区和查询缓存效率的实时数据和历史数据，以及有关正在运行的查询的统计信息。这是一个很有用的工具，可以查看系统中(比如 10 秒钟之内)的状况，您可以获得有关服务器健康信息的视图，并显示导致问题的任何连接。
mysqlard 是一个连接到 MySQL 服务器上的守护程序，负责每 5 分钟搜集一次数据，并将它们存储到后台的一个 Round Robin Database 中。有一个 Web 页面会显示这些数据，例如表缓存的使用情况、关键字效率、连接上的客户机以及临时表的使用情况。尽管 mytop 提供了服务器健康信息的快照，但是 mysqlard 则提供了长期的健康信息。作为奖励，mysqlard 使用自己搜集到的一些信息针对如何对服务器进行调优给出一些建议。 搜集 SHOW STATUS 信息的另外一个工具是 mysqlreport。其报告要远比 mysqlard 更加复杂，因为需要对服务器的每个方面都进行分析。这是对服务器进行调优的一个非常好的工具，因为它对状态变量进行适当计算来帮助确定需要修正哪些问题。 结束语
本文介绍了对 MySQL 进行调优的一些基础知识，并对这个针对 LAMP 组件进行调优的 3 部分系列文章进行了总结。调优很大程度上需要理解组件的工作原理，确定它们是否正常工作，进行一些调整，并重新评测。每个组件 —— Linux、Apache、PHP 或 MySQL —— 都有各种各样的需求。分别理解各个组件可以帮助减少可能会导致应用程序速度变慢的瓶颈。
博客分类：
Web Database
MySQL搜索引擎SQL ServerSQLLinux How to start / stop the MySQL service
http://bytes.com/topic/mysql/answers/704453-how-start-stop-mysql-service
1. net stop MySQL 2. net start MySQL
1. # /etc/init.d/mysqld start 2. # /etc/init.d/mysqld stop 3. # /etc/init.d/mysqld restart 1. # service mysqld start 2. # service mysqld stop 3. # service mysqld restart mysql 备份还原
http://renxiangzyq.iteye.com/blog/6667
mysql -u root -p databasename root 用户名(root管理员) dbcurr 备份的数据库名; > 备份符号 20090219.sql 备份的文件名 还原: mysql -u root -p dbcurr< d:\\bbb.sql mysql 还原命令 root 用户名(root管理员) dbcurr 备份的数据库名; < 还原符号 20090219.sql 还原的文件名 首先要确保你的mysql 中有 microerp这个库，还原命令是不会自动建库了。 全过程： mysql -uroot -pyoupwd create database microerp; use microerp; source d:/microerp-mysql .sql; --all-databases, -A： 备份所有数据库 --databases, -B： 用于备份多个数据库，如果没有该选项，mysql dump 把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysql dum把每个名字都当作为数据库名。 --force, -f：即使发现sql错误，仍然继续备份 --host=host_name, -h host_name：备份主机名，默认为localhost --no-data, -d：只导出表结构 --password[=password], -p[password]：密码 --port=port_num, -P port_num：制定TCP/IP连接时的端口号 --quick, -q：快速导出 --tables：覆盖 --databases or -B选项，后面所跟参数被视作表名 --user=user_name, -u user_name：用户名 --xml, -X：导出为xml文件 1.备份全部数据库的数据和结构 mysql dump -uroot -p123456 -A >F:\\all.sql 2.备份全部数据库的结构（加 -d 参数） mysql dump -uroot -p123456 -A -d>F:\\all_struct.sql 3.备份全部数据库的数据(加 -t 参数) mysql dump -uroot -p123456 -A -t>F:\\all_data.sql 4.备份单个数据库的数据和结构(,数据库名mydb) mysql dump -uroot -p123456 mydb>F:\\mydb.sql 5.备份单个数据库的结构 mysql dump -uroot -p123456 mydb -d>F:\\mydb.sql 6.备份单个数据库的数据 mysql dump -uroot -p123456 mydb -t>F:\\mydb.sql 7.备份多个表的数据和结构（数据，结构的单独备份方法与上同） mysql dump -uroot -p123456 mydb t1 t2 >f:\\multables.sql 8.一次备份多个数据库 mysql dump -uroot -p123456 --databases db1 db2 >f:\\muldbs.sql 还原部分分（1）mysql 命令行source方法 和 （2）系统命令行方法 1.还原全部数据库: (1) mysql 命令行：mysql >source f:\\all.sql (2) 系统命令行： mysql -uroot -p123456 (2) mysql -uroot -p123456 mydb mysql >source f:\\multables.sql (2) mysql -uroot -p123456 mydb (1) mysql 命令行：mysql >source f:\\muldbs.sql (2) 系统命令行： mysql -uroot -p123456 同时使用200多MB的sql文件。 例如： C:\\Program Files\\MySQL\\bin>mysql -u root -p myrosz < c:/myro.sql Enter password: **** 稍等一会，就可以导入数据表。 注意一点：数据库必须在本机存在，否则导入的时候将出错。 redhat mysql [root@localhost mysql]# find / -name mysql -print /var/lib/mysql /var/lib/mysql/mysql /usr/bin/mysql /usr/lib/mysql /usr/share/mysql 卸载redhat 5.2 自带mysql rpm -qa | grep mysql http://linux.chinaunix.net/bbs/thread-918196-1-1.html 卸载rpm mysql 时报错 error: Failed dependencies: libmysqlclient.so.15 is needed by (installed) dovecot-1.0-1.2.rc15.el5.i386 libmysqlclient.so.15(libmysqlclient_15) is needed by (installed) dovecot-1.0-1.2.rc15.el5.i386 出现了依赖性的问题。 dovecot是一个IMAP服务器，主要是用于收发邮件用的 rpm -e mysql --nodeps mysql存储字符串长度的问题 mysql5.0以上的版本： 1.一个汉字占多少长度与编码有关： UTF－8：一个汉字＝3个字节 GBK：一个汉字＝2个字节 2.varchar(n)表示n个字符，无论汉字和英文，Mysql都能存入n个字符，仅是实际字节长度有所区别 3.MySQL检查长度，可用SQL语言： select LENGTH(fieldname) from tablename 来查看 搜狐公司高级 DBA叶金荣推荐《MySQL性能调优与架构设计》 http://bvbook.iteye.com/blog/4026 MySql优化(2009-08-28 系统架构师大会) http://www.slideshare.net/secret/IzjEa4qGWlMeLH 架设java mysql主备及HA切换服务器 http://swachian.iteye.com/blog/222116 Linux下两个不同版本Mysql的安装 http://www.iteye.com/topic/203986 mysql集群整理版 http://www.iteye.com/topic/149130 http://www.iteye.com/topic/249875 mysql 存储引擎 http://samana.iteye.com/blog/368885 mysql表类型MyISAM和InnoDB比较 http://npsajax.iteye.com/blog/368737 MySQL engine/type类型InnoDB/MYISAM/MERGE/BDB/HEAP的区别 http://happyzhen.iteye.com/blog/353430 http://www.phpap.com/view.php?tid=50195&extra=&page=2 OLTP和OLAP 联机事务处理（OLTP）和联机分析处理（OLAP） http://feigme.iteye.com/blog/1491 Linux下两个不同版本Mysql的安装 http://www.iteye.com/topic/203986 问题的产生：在已有的Red Hat Enterprise Linux AS 3.0系统上已经运行了一套web程序，使用Mysql4, tomcat41, 现在又要求安装一套新程序，依旧使用该tomcat41, 但数据库变为mysql5。 注意事项： 新的程序需要注意字符集的问题， 1)具体数据库的权限和分组问题， 2)mysql5下的具体数据库从windows直接拷贝到Linux下不好使的问题， 3)以及mysql5需要设置密码的问题， 4)mysql5在linux下对数据库区分大小写的问题。 5) mysql在终端进入 mysql> 时的用户名和密码问题 此外，在具体安装mysql5的过程中，要注意将mysql5的安装位置（baseDir），数据库的具体存放位置（dataDir），端口号（改为3307）,进行修改，这样才能保证两个不同版本的数据库的同时运行。 mysql集群整理版 http://www.iteye.com/topic/149130 http://www.iteye.com/topic/249875 mysql 存储引擎 http://samana.iteye.com/blog/368885 www.php100.com Lamp Wamp MYSQL的共有7种存储引擎。每一种都为了不同的需要而选择最适合的引擎而,不是向oracle里头只有一种通用的存储引擎. 如何选择合适的MySQL存储引擎 http://database.51cto.com/art/200902/107871.htm mysql 5.0.67-community-nt 存储 引擎 共12个 MyISAM、MEMORY(HEAP)、InnoDB、BerkeleyDB(BDB)、BLACKHOLE、EXAMPLE、 ARCHIVE、CSV、ndbcluster、FEDERATED、MRG_MYISAM、ISAM mysql 5.0.67-community-nt 存储 引擎 适用列表 【 集群 】NDBCluster： 搜索方面 / Session数据 【 速度 】MyISAM： 字典索引 / Session数据 / bbs 【未知】Flat file： web统计数据 【 安全 】InnoDB： 金融事务 【未知】HEAP： 本地计算数据 MySQL支持数个存储引擎作为对不同表的类型的处理器。MySQL存储引擎包括处理事务安全表的引擎和处理非事务安全表的引擎： ◆ MyISAM管理非事务表。 它提供高速存储和检索，以及全文搜索能力。MyISAM在所有MySQL配置里被支持，它是默认的存储引擎，除非你配置MySQL默认使用另外一个引擎。 ◆ MEMORY存储引擎提供“内存中”表。 MERGE存储引擎允许集合将被处理同样的MyISAM表作为一个单独的表。就像MyISAM一样，MEMORY和MERGE存储引擎处理非事务表，这两个引擎也都被默认包含在MySQL中。 注释：MEMORY存储引擎正式地被确定为HEAP引擎。 ◆ InnoDB和BDB存储引擎提供事务安全表。 BDB被包含在为支持它的操作系统发布的MySQL-Max二进制分发版里。InnoDB也默认被包括在所 有MySQL 5.1二进制分发版里，你可以按照喜好通过配置MySQL来允许或禁止任一引擎。 ◆ EXAMPLE存储引擎是一个“存根”引擎，它不做什么。 你可以用这个引擎创建表，但没有数据被存储于其中或从其中检索。这个引擎的目的是服务，在 MySQL源代码中的一个例子，它演示说明如何开始编写新存储引擎。同样，它的主要兴趣是对开发者。 ◆ NDB Cluster是被MySQL Cluster用来实现分割到多台计算机上的表的存储引擎。 它在MySQL-Max 5.1二进制分发版里提供。这个存储引擎当前只被Linux, Solaris, 和Mac OS X 支持。在未来的MySQL分发版中，我们想要添加其它平台对这个引擎的支持，包括Windows。 ◆ ARCHIVE存储引擎被用来无索引地，非常小地覆盖存储的大量数据。 ◆ CSV存储引擎把数据以逗号分隔的格式存储在文本文件中。 ◆ BLACKHOLE存储引擎接受但不存储数据，并且检索总是返回一个空集。 ◆ FEDERATED存储引擎把数据存在远程数据库中。 在MySQL 5.1中，它只和MySQL一起工作，使用MySQL C Client API。在未来的分发版中，我们想要让它使用其它驱动器或客户端连接方法连接到另外的数据源。当你创建一个新表的时候， 你可以通过添加一个ENGINE 或TYPE 选项到CREATE TABLE语句来告诉MySQL你要创建什么类型的表： CREATE TABLE t (i INT) ENGINE = INNODB; CREATE TABLE t (i INT) TYPE = MEMORY; 虽然TYPE仍然在MySQL 5.1中被支持，现在ENGINE是首选的术语。 mysql 显示表所属存储引擎 mysql> SHOW TABLE STATUS LIKE 'user' \\G mysql 显示表存储引擎 mysql> show engines; Mysql 存储引擎对比图 http://robinwu.iteye.com/blog/314927 MySQL的存储引擎 http://samana.iteye.com/blog/368885 横向比较的是效率，纵向比较的数据可靠性 mysql表类型MyISAM和InnoDB比较 http://npsajax.iteye.com/blog/368737 MyISAM :这个是默认类型,它是基于传统的ISAM类型,ISAM是Indexed Sequential Access Method (有索引 的顺序访问方法) 的缩写,它是存储记录和文件的标准方法.与其他存储引擎比较,MyISAM具有检查和修复表格的大多数工具. MyISAM表格可以被压缩,而且它们支持全文搜索.它们不是事务安全的,而且也不支持外键。如果事物回滚将造成不完全回滚，不具有原子性。如果执行大量的SELECT ，MyISAM是更好的选择。 InnoDB :这种类型是事务安全的.它与BDB类型具有相同的特性,它们还支持外键.InnoDB表格速度很快.具有比BDB还丰富的特性,因此如果需要一个事务安全的存储引擎,建议使用它.如果你的数据执行大量的INSERT 或UPDATE ,出于性能方面的考虑，应该使用InnoDB表, 对于支持事物的InnoDB类型的标，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动 Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事物（即使autocommit打开也可以），将大大提高性能。 =============================================================== 1. 4.0以上mysqld都支持事务，包括非max版本。3.23的需要max版本mysqld才能支持事务。 2. 创建表时如果不指定type则默认为myisam，不支持事务。 可以用 show create table tablename 命令看表的类型。 2.1 对不支持事务的表做start/commit操作没有任何效果，在执行commit前已经提交，测试： 执行一个msyql： use test; drop table if exists tn; create table tn (a varchar(10)) type=myisam; drop table if exists ty; create table ty (a varchar(10)) type=innodb; begin; insert into tn values('a'); insert into ty values('a'); select * from tn; select * from ty; 都能看到一条记录 执行另一个mysql： use test; select * from tn; select * from ty; 只有tn能看到一条记录 然后在另一边 commit; 才都能看到记录。 3. 可以执行以下命令来切换非事务表到事务（数据不会丢失），innodb表比myisam表更安全： alter table tablename type=innodb; 3.1 innodb表不能用repair table命令和myisamchk -r table_name 但可以用check table，以及mysqlcheck [OPTIONS] database [tables] 4. 启动mysql数据库的命令行中添加了以下参数可以使新发布的mysql数据表都默认为使用事务（ 只影响到create语句。） --default-table-type=InnoDB 测试命令： use test; drop table if exists tn; create table tn (a varchar(10)); show create table tn; 5. 临时改变默认表类型可以用： set table_type=InnoDB; show variables like 'table_type'; 或： c:\\mysql\\bin\\mysqld-max-nt --standalone --default-table-type=InnoDB MySQL engine/type类型InnoDB/MYISAM/MERGE/BDB/HEAP的区别 http://happyzhen.iteye.com/blog/353430 http://www.phpap.com/view.php?tid=50195&extra=&page=2 看MySQL参考手册 发现CREATE TABLE 时有多种数据库存储引擎： TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM } 网上查了下据说MyISAM、InnoDB两种引擎常用 大至区别如下[不知是否准确]： 高级处理: MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。 执行速度: MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快。 移值性: MyISAM类型的二进制数据文件可以在不同操作系统中迁移。也就是可以直接从Windows系统拷贝到linux系统中使用。 －－－－－－－－－－－－－－－－－－－－－－－－－－－－－ 今天找到官方准确解释 · MyISAM：默认的MySQL插件式存储引擎，它是在Web、数据仓储和 其他应用环境下最常使用的存储引擎之一。注意，通过更改STORAGE_ENGINE配置变量，能够方便地更改MySQL服务器的默认存储引擎。 · InnoDB：用于事务处理应用程序，具有众多特性，包括ACID事务支持。 · BDB：可替代InnoDB的事务引擎，支持COMMIT、ROLLBACK和其他事务特性。 · Memory：将所有数据保存在RAM中，在需要快速查找引用和其他类似数据的环境下，可提供极快的访问。 · Merge：允许MySQL DBA或开发人员将一系列等同的MyISAM表以逻辑方式组合在一起，并作为1个对象引用它们。对于诸如数据仓储等VLDB环境十分适合。 · Archive：为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。 · Federated：能够将多个分离的MySQL服务器链接起来，从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。 · Cluster/NDB：MySQL的簇式数据库引擎，尤其适合于具有高性能查找要求的应用程序，这类查找需求还要求具有最高的正常工作时间和可用性。 · Other：其他存储引擎包括CSV（引用由逗号隔开的用作数据库表的文件），Blackhole（用于临时禁止对数据库的应用程序输入），以及Example引擎（可为快速创建定制的插件式存储引擎提供帮助）。 请记住，对于整个服务器或方案，你并不一定要使用相同的存储引擎，你可以为方案中的每个表使用不同的存储引擎，这点很重要。 OLTP和OLAP 联机事务处理（OLTP）和联机分析处理（OLAP） http://feigme.iteye.com/blog/1491 联机事务处理（OLTP）和联机分析处理（OLAP）的不同，主要通过以下五点区分开来。 用户和系统的面向性: OLTP是面向顾客的,用于事务和查询处理 OLAP是面向市场的,用于数据分析 数据内容: OLTP系统管理当前数据. OLAP系统管理大量历史数据,提供汇总和聚集机制. 数据库设计: OLTP采用实体-联系ER模型和面向应用的数据库设计. OLAP采用星型或雪花模型和面向主题的数据库设计. 视图: OLTP主要关注一个企业或部门内部的当前数据,不涉及历史数据或不同组织的数据 OLAP则相反. 访问模式: OLTP系统的访问主要由短的原子事务组成.这种系统需要并行和恢复机制. OLAP系统的访问大部分是只读操作 OLTP是传统的关系型数据库的主要应用，主要是基本的、日常的事务处理，例如银行交易。 OLAP是数据仓库系统的主要应用，支持复杂的分析操作，侧重决策支持，并且提供直观易懂的查询结果。 下表列出了OLTP与OLAP之间的比较: OLTP 用户 功能 DB 设计 数据 操作人员，低层管理人员 日常操作处理 面向应用 OLAP 决策人员，高级管理人员 分析决策 面向主题 最新的，细节的，二维的，分立的 历史的，聚集的，的，集成的 存取规模 读/写数条（甚至数百条）记录 读上百万（甚至上亿）条记录 操作频度 工作单位 用户数 DB 大小 非常频繁（以秒计） 严格的事务 数百个-数千万个 100MB-GB 比较稀松（以小时甚至以周计） 复杂的查询 数个-数百个 100GB-TB mysql 监控脚本 http://niyunjiu.iteye.com/blog/321092 #!/bin/bash # Shell script to restart MySQL server if it is killed or not working # due to ANY causes. # When script detects mysql is not running ( it basically sends ping request # to MySQL) it try to start using /etc/init.d/mysql script; and it sends an # email to user indicating the status. # This script must be run from Cron Job so that it can monitor mysql server. # For more info visit following url: # http://www.cyberciti.biz/nixcraft/vivek/blogger/2005 /08 /linux-mysql-server-monitoring.html # -------------------------------------------------------------------------- # Copyright ( C) 2005 nixCraft project # This script is part of nixCraft shell script collection ( NSSC) # Visit http://bash.cyberciti.biz/ for more information. # ------------------------------------------------------------------------- # mysql root/admin username MUSER= \"root\" # mysql admin/root password MPASS= \"SET-ROOT-PASSWORD\" # mysql server hostname MHOST= \"localhost\" #Shell script to start MySQL server i.e. path to MySQL daemon start/stop script. # Debain uses following script, need to setup this according to your UNIX/Linux/BSD OS. MSTART= \"/etc/init.d/mysql start\" # Email ID to send notification EMAILID= \"notification@somewhere-corp.com\" # path to mail program MAILCMD= \"$(which mail)\" # path mysqladmin MADMIN= \"$(which mysqladmin)\" #### DO NOT CHANGE anything BELOW #### MAILMESSAGE= \"/tmp/mysql.fail.$$\" # see if MySQL server is alive or not # 2 &1 could be better but i would like to keep it simple and easy to # understand stuff :) $MADMIN -h $MHOST -u $MUSER -p${ MPASS} ping 2 >/dev/null 1 >/dev/null if [ $? -ne 0 ] ; then echo \"\" >$MAILMESSAGE echo \"Error: MySQL Server is not running/responding ping request\" >>$MAILMESSAGE echo \"Hostname: $(hostname)\" >>$MAILMESSAGE echo \"Date & Time: $(date)\" >>$MAILMESSAGE # try to start mysql $MSTART >/dev/null # see if it is started or not o= $( ps cax | grep -c ' mysqld$' ) if [ $o -eq 1 ] ; then sMess= \"MySQL Server MySQL server successfully restarted\" else sMess= \"MySQL server FAILED to restart\" fi # Email status too echo \"Current Status: $sMess\" >>$MAILMESSAGE echo \"\" >>$MAILMESSAGE echo \"*** This email generated by $(basename $0) shell script ***\" >>$MAILMESSAGE echo \"*** Please don't reply this email, this is just notification email ***\" >>$MAILMESSAGE # send email $MAILCMD -s \"MySQL server\" $EMAILID < $MAILMESSAGE else # MySQL is running :) and do nothing : fi # remove file rm -f $MAILMESSAGE 一次历尽艰辛的mysql启动经历 http://bbs.chinaunix.net/archiver/?tid-1367834.html [code] [root@testbox-04 mysql]# cat /etc/issue CentOS release 5.2 (Final) Kernel \\r on an \\m [root@testbox-04 mysql]# uname -r 2.6.18-92.1.22.el5 [root@testbox-04 mysql]# rpm -qa | grep mysql mysql-5.0.45-7.el5 mysql-server-5.0.45-7.el5 mysql-5.0.45-7.el5 php-mysql-5.1.6-20.el5_2.1 mysql-devel-5.0.45-7.el5 mysql-devel-5.0.45-7.el5 mysql-test-5.0.45-7.el5 mysql-bench-5.0.45-7.el5 [root@testbox-04 mysql]# sestatus -bv | head -n1 SELinux status: enabled [root@testbox-04 mysql]# sestatus -bv | grep mysql allow_user_mysql_connect off mysqld_disable_trans on [/code] my.cnf配置： 为了节省空间，打扰次序排序的 [code] [root@testbox-04 mysql]# grep -v \"#\" /etc/my.cnf | sort | uniq back_log = 50 bind-address = 192.168.12.81 binlog_cache_size = 1M bulk_insert_buffer_size = M [client] default_table_type = INNODB ft_min_word_len = 4 innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 2G innodb_data_file_path = ibdata1:500M:autoextend innodb_data_home_dir = /var/lib/mysql/ibdata innodb_file_io_threads = 4 innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 120 innodb_log_buffer_size = 32M innodb_log_files_in_group = 3 innodb_log_file_size = 500M innodb_log_group_home_dir=/var/lib/mysql/iblogs innodb_max_dirty_pages_pct = 90 innodb_thread_concurrency = 16 interactive-timeout [isamchk] join_buffer_size = 8M key_buffer = 512M key_buffer_size = 32M log_long_format max_allowed_packet = 16M max_connect_errors = 10 max_connections = 100 max_heap_table_size = M [myisamchk] myisam_max_extra_sort_file_size = 10G myisam_max_sort_file_size = 10G myisam_recover myisam_repair_threads = 1 myisam_sort_buffer_size = 128M [mysql] [mysqld] [mysqld_safe] [mysqldump] [mysqlhotcopy] no-auto-rehash open-files-limit = 8192 port = 3306 query_cache_limit = 2M query_cache_size = M quick read_buffer = 8M read_buffer_size = 2M read_rnd_buffer_size = 16M server-id = 1 socket = /tmp/mysql.sock sort_buffer_size = 512M sort_buffer_size = 8M table_cache = 2048 thread_cache_size = 8 thread_concurrency = 8 thread_stack = 192K tmp_table_size = M transaction_isolation = REPEATABLE-READ write_buffer = 8M [/code] 错误日志1 [code] [root@testbox-04 ~]# tail -f /var/log/mysqld.log InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 090210 23:25:42 [ERROR] Default storage engine (InnoDB) is not available 090210 23:25:42 [ERROR] Aborting 090210 23:25:42 [Note] /usr/libexec/mysqld: Shutdown complete 090210 23:25:42 mysqld ended [/code] 解决错误1: my.cnf 里面打开 innodb_data_home_dir = /var/lib/mysql/ibdata 错误日志2: [code] 090210 23:26:59 mysqld started InnoDB: Error: auto-extending data file ./ibdata1 is of a different size InnoDB: 0 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 32000 pages, max 0 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 090210 23:27:00 [ERROR] Can't start server : Bind on unix socket: Permission denied 090210 23:27:00 [ERROR] Do you already have another mysqld server running on socket: /tmp/mysql.sock ? 090210 23:27:00 [ERROR] Aborting [/code] 解决错误日志2 sealert -a /var/log/audit/audit.log 查看到selinux 阻止 mysqld 读取mysql.sock 文件， Detailed Description: SELinux has denied mysqld access to potentially mislabeled file(s) (mysql.sock). This means that SELinux will not allow mysqld to use these files. It is common for users to edit files in their home directory or tmp directories and then move (mv) them to system directories. The problem is that the files end up with the wrong file context which confined applications are not allowed to access. Allowing Access: If you want mysqld to access this files, you need to relabel them using restorecon -v 'mysql.sock'. You might want to relabel the entire directory using restorecon -R -v ' 由于mysql无法启动，无法产生mysql.sock 所以，暂时取消对mysqld监控 setsebool mysqld_disable_trans 1 错误日志3 [code] 090210 23:29:42 mysqld started 090210 23:29:42 InnoDB: Operating system error number 2 in a file operation. InnoDB: The error means the system cannot find the path specified. InnoDB: If you are installing InnoDB, remember that you must create InnoDB: directories yourself, InnoDB does not create them. InnoDB: File name /var/lib/mysql/ibdata/ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 090210 23:29:42 mysqld ended [/code] 解决错误3： rm -rf /var/lib/mysql/ibdata/* rm -rf /var/lib/mysql/iblogs/* 错误日志4： [code] 090210 23:52:21 mysqld started 090210 23:52:22 InnoDB: Started; log sequence number 0 43656 090210 23:52:23 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist 090210 23:52:23 mysqld ended [/code] 解决方法4： [root@testbox-04 /]# /usr/bin/mysql_install_db Installing MySQL system tables... OK Filling help tables... OK 错误日志5: [code] 090211 00:00:34 mysqld started 090211 0:00:35 InnoDB: Started; log sequence number 0 43656 /usr/libexec/mysqld: File './mysql-bin.000008' not found (Errcode: 13) 090211 0:00:35 [ERROR] Failed to open log (file './mysql-bin.000008', errno 13) 090211 0:00:35 [ERROR] Could not open log file 090211 0:00:35 [ERROR] Can't init tc log 090211 0:00:35 [ERROR] Aborting [/code] 解决错误5： 感觉到是读取bin-log错误，为什么开启会读bin-log 而且，我的/var/lib/mysql下 mysql-bin.000008文件是存在的 先注释掉my.cnf log-bin 错误日志6: [code] 090211 00:23:19 mysqld started /usr/libexec/mysqld: File '/var/lib/mysql/testbox-04-slow.log' not found (Errcode: 13) 090211 0:23:19 [ERROR] Could not use /var/lib/mysql/testbox-04-slow.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it. 090211 0:23:20 InnoDB: Started; log sequence number 0 43656 [/code] 解决错误6： #log_slow_queries #long_query_time = 2 以上2行增加注释; 错误日志7： [code] 090211 00:02:51 mysqld started 090211 0:02:52 InnoDB: Started; log sequence number 0 43656 090211 0:02:52 [ERROR] /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 090211 0:02:52 [ERROR] /usr/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 090211 0:02:52 [ERROR] Fatal error: Can't open and lock privilege tables: Can't find file: './mysql/host.frm' (errno: 13) 090211 00:02:52 mysqld ended [/code] 解决错误方法6： /usr/bin/mysql_install_db --user=mysql 哈哈：起来了 [root@testbox-04 mysql]# service mysqld start Starting MySQL: [ OK ] [root@testbox-04 mysql]# 上面的前提是注释掉了selinux对mysql的mysql.sock监控 现在把监控加进去，在做如下测试。 还是起不来，看这个样子是selinux严格的权限禁止 原生目录/var/lib/mysql下的sock文件产生 那更改/etc/my.cnf [code] [root@testbox-04 mysql]# grep -v \"#\" /etc/my.cnf |grep -i sock socket = /var/lib/mysql/mysql.sock socket = /var/lib/mysql/mysql.sock 改到/var/lib/mysql下吧 重新加监控进去 [root@testbox-04 mysql]# setsebool mysqld_disable_trans 0 [root@testbox-04 mysql]# ls -lZ |grep sock srwxrwxrwx mysql mysql user_u:object_r:mysqld_var_run_t mysql.sock 090211 00:42:48 mysqld started 090211 0:42:49 InnoDB: Started; log sequence number 0 43656 090211 0:42:49 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.45' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution [/code] end MySQL DBA 管理 常用 命令(转) 2010-05-07 14:18:32| 分类： linux+mysql | 标签： |字号大中小 订阅 虽然自己不是DBA，但是作为一个程序员，多多少少，应该了解一些数据库方面的东西，并不能只关心程序，不考虑数据库，看到一篇文章，就先转过来，也许以后自己哪天会用到。 查看mysql的某个选项 show variables like ‗%VAR_NAME%‘; select @@VAR_NAME; 在Linux下管理MySQL数据库的时候总有一些很紧急的情况，发现数据库突然变得压力很大了，那么作为一个DBA，也许需要一些常用的手段或者说命令去分析问题出现在哪里，然后解决： 数据库突然产生压力时查看正在查询的SQL:(如果这里内容太多表示并发执行的SQL过多，或许数据库堵塞了，会越来越慢，正常情况下这里应该很少有东西的，也就是连接都在Sleep状态） /usr/local/mysql/bin/mysql -uroot -ppassword databaseName -e ―show full processlist‖ | grep -v Sleep 正在运行的SQL太多了，看不过来，那需要排序了，看持续执行时间最长的那些SQL： /usr/local/mysql/bin/mysql -uroot -ppassword databaseName -e ―show full processlist‖ | grep -v Sleep | sort -k6rn >sort.tmp 如果发现IOWait很高，请查看临时表的生成情况，特别是disk tmp table: /usr/local/mysql/bin/mysql -uroot -ppassword databaseName -e ―show global status like ‗%tmp%‘‖ 通过这样一些办法可以查看数据库都在忙什么，那些忙的SQL又具体在哪一个步骤上卡住了，是在创建磁盘临时文件、Sending Data、statistics？依照不同的原因来解决问题 ————————————————————— 关于Mysql Replication日常管理，重做，问题分析时常用的办法： 重做Slave，或者Master变化等等，需要将Slave与新的Master同步： change master to master_host=IP,master_user=‘replication userName‘,master _password=‘replication Passwrod‘,master_log_file=‘log-bin.000001′,master_log_pos=0; 导出数据成SQL文本，慎用，根据你的DB大小会锁表，导致堵塞其他访问： nohup /usr/local/mysql/bin/mysqldump –database DATABASEName -uUserName -pPassWord –lock-all-tables -F >DATA20070519.sql & -F后会刷新Master Log这样配合上面的Change Master可以让Slave进行同步 只导出数据库的结构（没有任何内容） /usr/local/mysql/bin/mysqldump -d DATABASEName -uUserName -pPassWord >DATA20070519.structure 只导出数据库的数据（没有创建表结构的语句等等） /usr/local/mysql/bin/mysqldump -t DATABASEName -uUserName -pPassWord >DATA20070519.data 同步的时候出现问题（或者其他问题）了，根据同步出现问题的位置（偏移量），查看Binlog的具体内容 /usr/local/mysql/bin/mysqlbinlog binlogFileName –start-position=偏移量 呵呵，我们碰到过Master执行的SQL到了Slave会报语法错误，够诡异吧！不过就是这样查到了原因：如果通过存储过程将bit的内容改为1就会出现这样的问题，后来将bit改为tinyint(1)就好了 授权给某一台Slave拥有复制的权限： grant replication slave on *.* to 用户名@IP identified by ‗密码‘; 查看Slave状态： Show slave status \\G 查看Master状态： Show master status; 重置Slave（慎用） reset slave; Slave出现问题了，先跳过这一条语句（请确认所要跳过的具体内容不会影响后面的同步，确认方法查看Binlog文件）： set global sql_slave_skip_counter=1; (记得先暂停Slave：stop slave; 然后重启Slave：start slave;) 因篇幅问题不能全部显示，请点此查看更多更全内容
Copyright © 2019- shangjiatang.cn 版权所有 湘ICP备2022005869号-4
违法及侵权请联系：TEL:199 1889 7713 E-MAIL:2724546146@qq.com
本站由北京市万商天勤律师事务所王兴未律师提供法律服务