存档

‘MySQL’ 分类的存档

MySQL 分支 MariaDB

2010年5月7日 16hot 没有评论

MariaDB 是一个采用 Maria 存储引擎的 MySQL 分支版本，是由原来 MySQL 的作者 Michael Widenius 创办的公司所开发的免费开源的数据库服务器。

与 MySQL 相比较，MariaDB 更强的地方在于：

Maria 存储引擎
PBXT 存储引擎
XtraDB 存储引擎
FederatedX 存储引擎
更快的复制查询处理
线程池
更少的警告和bug
运行速度更快
更多的 Extensions (More index parts, new startup options etc)
更好的功能测试
数据表消除
慢查询日志的扩展统计
支持对 Unicode 的排序

官方网站： http://askmonty.org/wiki/MariaDB

分类: MySQL 标签: MariaDB, MySQL

解决wiki内容出现换行n字符的问题

2009年5月1日 16hot 没有评论

原来在使用mysql4版本时，mediawiki总是会在提交内容后，浏览时出现n换行字符。

一些该换行的地方没有换行，以至荒废wiki.isyi.com半年多了。

今天趁放假在家，将mediawiki安装在mysql5上，解决了这个问题。以后逐步使用上wiki来记录文档。

分类: BSD/linux, MySQL, PHP 标签:

MYSQL 注射精华

创建时间：2008-06-25
文章属性：原创
文章提交：tsenable (tsenable_at_gmail.com)

MYSQL 注射精华

前言
鄙人今天心血来潮突然想写篇文章,鄙人从来没写过文章,如果有错误的地方请多多指教.本文需要有基础的SQL语句知识才可以更好的理解.建议想学习的人多去了解一下SQL语句和编程语言,知己知彼才能百战百胜.
我不希翼得到读者您的好评,尽管我尽力了;只希望本文能解决您学习过程的障碍,希望您早日掌握有关MYSQL注入方面的知识.

1.MYSQL 注射的产生.
漏洞产生原因 : 程序执行中未对敏感字符进行过滤,使得攻击者传入恶意字符串与结构化数据查询语句合并,并且执行恶意代码.

咱们先创造一个没有过滤的程序. 因为我机器上没有PHP,所以我就是用 JAVA了,我会详细注释.

代码
数据库:

create database if not exists `test`;

USE `test`;

/*数据表 `account` 的表结构*/

DROP TABLE IF EXISTS `account`;

CREATE TABLE `account` (
  `accountId` bigint(20) NOT NULL auto_increment,
  `accountName` varchar(32) default NULL,
  `accountPass` varchar(32) default NULL,
  PRIMARY KEY  (`accountId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*数据表 `account` 的数据*/

insert into `account` values
(1,'account1','account1');

/*数据表 `admin` 的表结构*/

DROP TABLE IF EXISTS `admin`;

CREATE TABLE `admin` (
  `adminId` bigint(20) NOT NULL auto_increment,
  `adminName` varchar(32) default NULL,
  `adminPass` varchar(32) default NULL,
  PRIMARY KEY  (`adminId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*数据表 `admin` 的数据*/

insert into `admin` values
(1,'admin','admin');

:
程序:
<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="utf-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<body>
<%
//连接MYSQL的字符串.
//jdbc:mysql://localhost:3306/test
//驱动:数据库://地址:端口/数据库名称
String mysqlConnection = "jdbc:mysql://localhost:3306/test";

//加载驱动 com.mysql.jdbc.Driver 是JAVA与MYSQL 连接用的JDBC驱动
Class.forName("com.mysql.jdbc.Driver").newInstance();

//建立MYSQL链接 root是用户名 cx0321 是密码
Connection connection = DriverManager.getConnection(mysqlConnection, "root", "cx0321");

//建立一个查询对象
Statement statment = connection.createStatement();

//建立一个查询返回集合. 就是说查询完以后返回的数据全部都在这个里面.
ResultSet resultSet = null;

//从account里面读取数据.
resultSet = statment.executeQuery("select * from account where accountId = '"+ request.getParameter("id") +"'");

//循环,直到resultSet结束
while(resultSet.next())
{
//从resultSet读取出值输出到页面.
    out.print(resultSet.getInt(1)+"|");//取出第一列的值,因为是数字类型的所以是getInt();
    out.print(resultSet.getString(2)+"|");//取出第二列的值,因为是字符串类型的所以是getString();
    out.print(resultSet.getString(3)+"|");
    out.print("<br />");//页面输出换行
}
%>
  </body>
</html>

2.漏洞的利用

(图1)
这个就是数据库里的记录了.以后黄色为关键语句,红色为输入的部分.
大家注意看resultSet = statment.executeQuery("select * from account where accountId = '"+ request.getParameter("id") +"'");
这里的request.getParameter("id") 是获取GET传参的id 参数,也就是mysqlInject.jsp?id=1 这里的id. 这样这个SQL语句就变成了select * from account where accountId = '1' 了.如果加以变换呢?

2.1漏洞的检测
我们把id 写成mysqlInject.jsp?id=1' 那么SQL 语句就变成select * from account where accountId = '1'' 了,这样的话SQL语句就会报错,因为SQL语句的值是需要2个包含符号,比如’和”如果只是数字可以什么都不写.如果不报错的话就说明程序替换,过滤或者其他方法来防护了.

那么我们可以继续来测验, mysqlInject.jsp?id=1' and ''=' 那么SQL语句就变成了select * from account where accountId = '1' and '' = '' ,应该返回正常.

有些人说我的为什么返回不正常呢? 有2种原因,第一是程序把恶意字符过滤了;第二是程序的语句和我写的不一样select * from account where accountId = 1' and ''='. 这个问题在下边会谈到.

2.2 Union查询猜此次查询列的数量
这里有的人会说猜此次查询列的数量有什么用?如果只是检测当然没有,但是你想进一步的利用那么就有大的用处了,文章后边会讲到的,耐心.

如果懂SQL的人应该知道UNION查询吧?UNION查询就是联合查询,执行第二条查询语句将返回值和本次查询合并.

大家想想,如果要和本次查询值合并需要一个什么条件呢？需要联合查询的列数和此次查询的列数相等.如果不想等的话就会无法合并,那么就会报错.通过这一特点聪明的你应该会想出这么才列数了吧?

那么我们要的就是使得UNION查询出来的列数与本次查询出来的列数相等.也就是说不报错就会相等.
先从第一列开始猜,那么要把这个语句union select 1构造在地址程序的语句当中.
那么语句就是mysqlInject.jsp?id=1' and union select 1 and ''=' 这样的.
有些人问为什么后边(绿色的部分)要加上and ''=' 呢? 也许大家记了吧,我们的SQL语句是需要两个包含符号的,语句select * from account where accountId = '1' 我们输入的是在1那个位置,所以要去除后边的',否则语句会报错的.
在本程序里也就是' 如果你要想消除' 有很多办法,为了让大家明白所以我现在使用and ''='.
先说一说有几种办法消除这个'
1. 使用 and '' = ' 虽然不够方便,但是在复杂SQL语句里不会报错的.
2. 使用注释 # 或者 /**/, 这样可以把后面的东西全部注释掉,但是有一个大问题,就是在执行复杂SQL语句的时候有可能会报错.
有些人测试,咦?为什么我加了#还是会报错呢?因为本次是使用GET传参,在地址栏传参.大家想想,当初下载带#名称的数据库是什么样子呢?哦,对了,#是地址栏的结束符,就是说#包括#以后的字符全部不传入.所以#在GET模式下注入注入不起作用.

那么有些工具写的在构造注射的时候为什么是mysqlInject.jsp?id=1'/**/and/**/union/**/select/**/1 /**/and/**/''/**/= /**/'/* 呢? 因为在程序里边有函数可以把传入参数里面的空格去除,如果去除了空格,将会是程序产生了错误的语句,那么就会一直报错了.所以有些工具就是用/**/这种东西来取代空格了.

那 /**/ 又是什么呢? /**/ 是一种注释,叫做文档注释,就是从/* 开始直到*/ 结束,中间任何代码都会成为注释,所以是程序员在写大量注释时候所使用的一种注释.

那最后的/* 是什么呢? 那个是用来解决 SQL语句包含符号没有成双成对的.

我们开始测试.
mysqlInject.jsp?id=1 '/**/union/**/select/**/1/*
select * from account where accountId = '1 '/**/union/**/select/**/1/*'.

注意到最低下那句话了吗?
javax.servlet.ServletException: The used SELECT statements have a different number of columns
大概意思是”这个使用的查询列数不同”,由此得出此次查询不是查询了一个表.

以此类推, select 1 select 1,2 select 1,2,3 知道正确位置,那么你现在说写的列数也就是本次查询的列数了.

大家看到地下返回 1|2|3| ,这个值是从咱们的UNION查询里合并出来的. 试试把UNION SELECT 1,2,3 换成 UNION SELECT 4,5,6 看看.地下是不是编程了 4|5|6| 了?

有人说你都是骗人的我怎么换,我都换到789了也没有出来,还是现实原来的数据,你骗人;我没有骗人,我也不会骗人;那为什么出不来?
有些程序写的时候只是把数据返回集合的第一行输出,但是UNION查询以后是把数据合并到此次查询以后,那么他只输出了此次查询的数据,其实UNION查询的数据也有,但是他没有输出.那怎么办呢?聪明的人一定会想到. 啊,原来如此,只要让此次查询不输出就可以了.哈哈哈,我聪明了,可是怎么让此次查询不输出呢? 先告诉大家一个简单的方法,看看SQL语句,我们是做过限制条件的. Where accountid = ? ,那么也就是说让这个accoundId 限制到一个没有的id 上那么不就会没有了? 心动不如行动,试试.
mysqlInject.jsp?id=1000'/**/union/**/select/**/4,5,6/*
select * from account where accountId =1000'/**/union/**/select/**/4,5,6/*

哈哈,果然没有了!!! 注意绿色的部分,指定查询一个没有的id ,那么他理所当然的就会蒸发了.
2.3 低几率另类猜此次查询列的数量
此方法虽然几率低一点,但是会大大减少工作量的.次方法只适用于 select * 的简易SQL语句.
这个方法是用的是 mysql 里的 order 排序. 排序是按照顺序排下来.我们来写一条SQL语句. Select * from account where accountId = '1' order by accountId 那么这个SQL语句也就是根据 accountId 升序排序. 那么我们不知道他有什么怎么办,而且这怎么猜? 这里是关键问题. MYSQL支持列编号排序Select * from account where accountId = '1' order by 1 这样也就是按照第一列排序.
哎呀,你又在骗我们,排序怎么猜列的数量? 那么我按照一个不存在的列排序呢? 比如第四列? 你一般身上有3个口袋,一个最多10元钱,一天吃一顿,一顿3斤米,一斤米一元,但是你今天吃了4斤米,需要40元,你却只有3个口袋,你就没有40元,你就要挨打了.
也就是说一共有3个列,order by 3 ,按照第3列排序,正常,order by 4,按照第4列排序,没有第4列,出错.那么也就说明他有4列.
这种方法是根据人的经验判断的.我一般使用这个方法都会成功,就是不成功也相差不多.

2.4 使用UNION猜其他表,查询其他表
使用此方法可以查询到其他表里的内容.比如查询管理员的密码等.但是有个前题,必须道要才表的表名和列名. 那怎么才能知道呢? 猜!!! 因为MYSQL 和SQLSERVER 的系统函数不一样,SQLSERVER 里有 SP_HELPDB 而MYSQL 里没有,所以只能猜了.
好,开始构造语句. 我们要猜看看有没有admin表.
mysqlInject.jsp?id=1'/**/union/**/select/**/4,5,6/**/from/**/admin/*
SQL : select * from account where accountId = '1'/**/union/**/select/**/4,5,6/**/from/**/admin/*'

如果正常的有admin表的话,那么返回是正常的,如果没有的话会报错的.

大家看到了吧? 有admin 这个表,为了让大家更好的理解,我们在猜一个其他不存在的表.

mysqlInject.jsp?id=1'/**/union/**/select/**/4,5,6/**/from/**/helloword/*
SQL : select * from account where accountId = '1'/**/union/**/select/**/4,5,6/**/from/**/ helloword/*'

看到了吧?没有 helloworld 这个表.所以报错了.
又问,为什么还是会写4,5,6呢? 啊哈,因为我们不知道他的列名,如果写了 * 他将会全部列出来,如果和此次查询的列不相等,那么就会报错了.所以要写一个相等的.
现在表名出来了,怎么才列名呢?哎呀,大家太聪明了,直接把4,5,6其中一个替换成列名不久行了? 那么构造出.
mysqlInject.jsp?id=1'/**/union/**/select/**/adminId,5,6/**/from/**/admin/*
SQL : select * from account where accountId = '1'/**/union/**/select/**/adminid,5,6/**/from/**/admin/*'

看见了吗? 1|5|6 的一就是 adminid.如果正常那么就是存在了. 大家可以把列名猜出来,然后带入UNION查询中,这样就查出来管理员帐号或者密码了.现在我要把列名一次全部带入.

mysqlInject.jsp?id=1'/**/union/**/select/**/adminId,adminName,adminPass/**/from/**/admin/*
SQL : select * from account where accountId = '1'/**/union/**/select/**/adminid,adminName,adminPass/**/from/**/admin/*'

    哈哈,出来了, 1|admin|admin| 就是 adminid|adminName|adminPass|
    也可以在union 查询上限制条件,比如你知道有admin这个用户那么就构造 union select adminId,adminName,adminPass from admin where adminName = ‘admin’,看个人的发挥了.
2.5    使用MYSQL 系统函数.
2.5.1.1.1    使用 load_file() 函数显示文件.
  Load_file 顾名思义.就是加载文件,可不是运行啊,是显示内容,但是必须对文件拥有读取权限.我们先来构造一个显示 c:boot.ini 文件的语句.
mysqlInject.jsp?id=1'/**/union/**/select/**/1,load_file(0x633A5C626F6F742E696E69),3/*
    SQL : select * from account where accountId = '1'/**/union/**/select/**/1, load_file(0x633A5C626F6F742E696E69),3/*'

  看到了吗? C:boot.ini 文件的内容. 又问,为什么load_file() 里面是乱码呢? 那不是乱码,那个是C:boot.ini 16进制编码. 因为本函数无法处理直接写的路径,只能能使用16进制或者是 Ascii 编码.所以要将路径转换成 16进制或者是Ascii 编码才可以执行.
  又问,为什么load_file 是在第二列的位置上,不是在第一列或者第三列的位置上呢?因为啊,第一列不行,其他的都可以,第一列是一个 INT类型,一个数字类型,难道你会把你女朋友送进男厕所吗? 呵呵.玩笑.如果是在 linux 下可以使用 / 来列目录 ,但是必须有列目录的权限.
  通过load_file 可以列目录,读文件,但是遇到文件格式编码的时候也许会遇到乱码的问题. 这个问题可以这么解决. 使用 subString 函数, subString(字符串,开始,返回).
  假设我们要返回第三个字符,那么就是mysqlInject.jsp?id=1'/**/union/**/select/** /1,substring(load_file(0x633A5C626F6F742E696E69),3,1) ,3/* 这样我们就返回了第三个字符,用于解决乱码是非常好的办法.
  我近期会做一个这样个工具,将会公布在我的个人主页上.
2.5.1.1.2    使用outfile 写WEBSHELL.
  mysql 有一个功能,就是把查询的结果输出.就是outfile.先来构造一个简单的语句.
  select ‘hello word’ into outfile ‘c:a.txt’ 这里是讲 ‘hello word’ 输出到 c:a.txt
那么在网站也来构造一下.
mysqlInject.jsp?id=1'/**/union/**/select/**/1,'hello',3/**/into/**/outfile/**/'c:hello.txt'/*
    SQL : select * from account where accountId = '1'/**/union/**/select/**/1, 'hello',3/**/into/**/outfile/**/’c:hello.txt’/*'
   成功插入.但是为什么会报错呢?哦,那是因为你把数据写到文件中,返回集合什么都没有了,当然会报错了.如果你把hello 换成一句话或者其他的,如果写入到网站目录下,那是多么恐怖啊…

2.漏洞的防护和总结
通过过滤特殊关键字来防护.代码网站很多,我这里就不写了.
针对JAVA有一种防护措施,就是使用PreparedStatement 对象进行查询,这里也不多说了.

本文只是一个概括的讲述,如果应用到实战当中需要结合经验.

如果有什么错误的地方可以和我说. msn:tsenable@msn.com 我的主页tsenable.spaces.live.com 感谢大家.

分类: MySQL, 转载标签:

SQLite线程安全

2009年1月31日 16hot 没有评论

(7) 多个应用程序或者同一个应用程序的多个例程能同时存取同一个数据库文件吗？

多进程可以同时打开同一个数据库，也可以同时 SELECT 。但只有一个进程可以立即改数据库。

SQLite 使用读/写锁定来控制数据库访问。(Win95/98/ME 操作系统缺乏读/写锁定支持，在低于 2.7.0 的版本中，这意味着在 windows 下在同一时间内只能有一个进程读数据库。在版本 2.7.0 中这个问题通过在 windows 接口代码中执行一个用户间隔几率读写锁定策略解决了。) 但如果数据库文件在一个 NFS 文件系统中，控制并发读书的锁定机制可以会出错。因为 NFS 的fcntl() 文件锁定有时会出问题。如果有多进程可能并发读数据库则因当避免把数据库文件放在 NFS 文件系统中。根据微软的文档，如果不运行 Share.exe 后台程序则 FAT 文件系统中的锁定可能不工作。对 Windows 非常有经验的人告诉我网络文件的锁定有许多问题并且不可靠。如果是这样，在2个或以上 Windows 系统中共享一个 SQLite 数据库文件会导致不可预知的问题。

我们知道没有其他的嵌入式 SQL数据库引擎比SQLite支持更多的并发性。 SQLite允许多进程同时打开和读取数据库。任何一个进程需要写入时，整个数据库将在这一过程中被锁定。但这一般仅耗时几毫秒。其他进程只需等待然后继续其他事务。其他嵌入式SQL数据库引擎往往只允许单进程访问数据库。

但是，client/server 型的数据库引擎 (如 PostgreSQL, MySQL, 以及 Oracle) 通常支持更高的并发度，并支持多进程同时写入同一个数据库。由于总有一个控制良好的服务器协调数据库的访问，这才保证了以上特性的实现。如果你的应用需要很高的并发度，你应该考虑使用client/server数据库。事实上，经验告诉我们大多数应用所需要的并发度比他们的设计者们想象的要少得多。

当 SQLite 尝试操作一个被另一个进程锁定的文件时，缺省的行为是返回 SQLITE_BUSY。你可以用 C代码更改这一行为。使用 sqlite3_busy_handler() 或sqlite3_busy_timeout() API函数。

如果两个或更多进程同时打开同一个数据库，其中一个进程创建了新的表或索引，则其它进程可能不能立即看见新的表。其它进程可能需要关闭并重新连结数据库。

——————————————————————————–

(8) SQLite是线程安全的吗?

有时候是的。为了线程安全，SQLite 必须在编译时把 THREADSAFE 预处理宏设为1。在缺省的发行的已编译版本中 Windows 版的是线程安全的，而 Linux 版的不是。如果要求线程安全，Linux 版的要重新编译。

“线程安全”是指二个或三个线程可以同时调用独立的不同的sqlite3_open() 返回的"sqlite3"结构。而不是在多线程中同时使用同一个 sqlite3 结构指针。

一个sqlite3结构只能在调用 sqlite3_open创建它的那个进程中使用。你不能在一个线程中打开一个数据库然后把指针传递给另一个线程使用。这是因为大多数多线程系统的限制（或 Bugs？）例如RedHat9上。在这些有问题的系统上，一个线程创建的fcntl()锁不能由另一个线程删除或修改。由于SQLite依赖fcntl()锁来进行并发控制，当在线程间传递数据库连接时会出现严重的问题。

也许在Linux下有办法解决fcntl()锁的问题，但那十分复杂并且对于正确性的测试将是极度困难的。因此，SQLite目前不允许在线程间共享句柄。

在UNIX下，你不能通过一个 fork() 系统调用把一个打开的 SQLite 数据库放入子过程中，否则会出错。

分类: MySQL, 转载标签:

MySQL优化

2008年8月27日 16hot 没有评论

thread_concurrency
数量设置为CPU核心数量的两倍.
thread_cache_size
按照内存大小来设置, 1G=8, 2G=16, 3G=32, >3G=64
wait_timeout
超时时间,如果连接数比较大,可以减少此参数的值,我使用的是10
max_connections
最大连接数,mysql实际允许连接数的值是max_connections+1,按照系统库不同而有不同性能.一般是500~1000,MySQL AB提供的linux静态库可以达到4000.
query_cache_size
查询缓冲,默认是0,所以必须打开以提高mysql性能,其本身需要40K来保存结构数据.所以不能设置的太小,初期可以设置成32M,然后根据实际运行情况另行调整
query_cache_type
指定查询缓冲的类型,0是关闭,1是缓冲除了使用SELECT SQL_NO_CACHE语句指明了不需要缓冲的数据意外的所有查询,2是只缓冲SELECT SQL_CACHE指定的查询.一般设置为1.
query_cache_limit
允许进入查询缓冲区的最小数据大小,默认值是1MB,可以修改的小一点以满足更多查询的需求.但是如果设置的过于小,则会导致很多新的小查询的结果将原有的查询结果交换出去.增加系统的颠簸.

相关命令
查询mysql服务器相关状态数据
>SHOW STATUS;

查询mysql服务器相关配置选项
>SHOW VARIABLES;

整理查询缓冲区里的碎片
>flush query cache;

删除查询缓冲区里的所有内容
>reset query cache;

设置mysql参数
>SET GLOBAL;

查询mysql当前执行的sql语句
>show processlist;

变量含义
Qcache_queries_in_cache
在缓存中已注册的查询数目
Qcache_inserts
被加入到缓存中的查询数目
Qcache_hits
缓存采样数数目
Qcache_lowmem_prunes
因为缺少内存而被从缓存中删除的查询数目
Qcache_not_cached
没有被缓存的查询数目 (不能被缓存的，或由于 QUERY_CACHE_TYPE)
Qcache_free_memory
查询缓存的空闲内存总数
Qcache_free_blocks
查询缓存中的空闲内存块的数目
Qcache_total_blocks
查询缓存中的块的总数目

MySQL查询优化
>SHOW STATUS LIKE ‘Qcache%’;
查询出Cache状态
如果Qcache_lowmem_prunes非常大,说明因为内存不足而被交换出cache的数据很多.如果增加内存.可以保证较小的交换次数以及较高的命中率
例如现在我们查询的结果如下

| Qcache_free_blocks | 1234 |
| Qcache_free_memory | 25957504 |
| Qcache_hits | 55771119 |
| Qcache_inserts | 7441153 |
| Qcache_lowmem_prunes | 28332 |
| Qcache_not_cached | 1233788 |
| Qcache_queries_in_cache | 4810 |
| Qcache_total_blocks | 11038 |

设置为64M cache内存后
>set global query_cache_size=67108864;

| Qcache_free_blocks | 1 |
| Qcache_free_memory | 66623616 |
| Qcache_hits | 55788258 |
| Qcache_inserts | 7445445 |
| Qcache_lowmem_prunes | 28332 |
| Qcache_not_cached | 1234057 |
| Qcache_queries_in_cache | 183 |
| Qcache_total_blocks | 392 |

自由内存块看起来变小了
是因为现在自由内存块.是一个整块.而以前的内存块都是分散的小块
而因为重建了cache区
Qcache_queries_in_cache变量变小了.因为此操作重新建立了cache内存区.所有数据重新缓存
在运行一两天后我们再看此数据.如果变大了.说明增大cache内存区域是有效的.如果和以前数据差不多
说明增加的内存并没有实际起到多大的作用.

有人会觉得如果我将cache内存设置的非常大
然后将cache_limit设置成0
那么所有查询都会被缓存了
理论上是这样.但是一台数据库服务器的查询非常多.
如果连查询单条数据都要缓存.
那么内存再大也会不够的.到时候老的内容就会被交换出去
当cache内存使用满的时候,就会不停的有新查询进来将老查询替换出去.
这样导致两个结果.一个是内存颠簸.效率反而下降.
第二个是cache内存的小碎块增多,内存利用率降低
如果是只有内容很少的小库,并且查询率不高.是可以使用这种方法提高响应速度
但是如果是实际生产环境,数据量会比较大.还是需要按照最佳比例来配置.
而不同的应用不同的数据量会有不同的搭配,这点大家不要看网上的优化配置随便的填写
还是要时时的查看mysql的状态进行调整.即便是这个月调整好的优化参数
到了下个月业务不同,数据量增加,也会需要调整的.

分类: MySQL 标签: MySQL

用mysqlslap对MySQL进行压力测试（转）

2008年6月14日 16hot 没有评论

MySQL5.1地的确提供了好多有力的工具来帮助我们DBA进行数据库管理。
现在看一下这个压力测试工具mysqlslap.
关于他的选项手册上以及–help介绍的很详细。
我解释一下一些常用的选项。
这里要注意的几个选项：
–concurrency代表并发数量，多个可以用逗号隔开，当然你也可以用自己的分隔符隔开，这个时候要用到–delimiter开关。
–engines代表要测试的引擎，可以有多个，用分隔符隔开。
–iterations代表要运行这些测试多少次。
–auto-generate-sql 代表用系统自己生成的SQL脚本来测试。
–auto-generate-sql-load-type 代表要测试的是读还是写还是两者混合的（read,write,update,mixed）
–number-of-queries 代表总共要运行多少次查询。每个客户运行的查询数量可以用查询总数/并发数来计算。比如倒数第二个结果2=200/100。
–debug-info 代表要额外输出CPU以及内存的相关信息。
–number-int-cols 代表示例表中的INTEGER类型的属性有几个。
–number-char-cols 意思同上。
–create-schema 代表自己定义的模式（在MySQL中也就是库）。
–query 代表自己的SQL脚本。
–only-print 如果只想打印看看SQL语句是什么，可以用这个选项。

现在来看一些我测试的例子。

1、用自带的SQL脚本来测试。
MySQL版本为5.1.23
[root@localhost ~]# mysqlslap --defaults-file=/usr/local/mysql-maria/my.cnf --concurrency=50,100,200 --iterations=1 --number-int-cols=4 --number-char-cols=35 --auto-generate-sql --auto-generate-sql-add-autoincrement --auto-generate-sql-load-type=mixed --engine=myisam,innodb --number-of-queries=200 --debug-info -uroot -p1 -S/tmp/mysql_3310.sock

Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.063 seconds
Minimum number of seconds to run all queries: 0.063 seconds
Maximum number of seconds to run all queries: 0.063 seconds
Number of clients running queries: 50
Average number of queries per client: 4

Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.070 seconds
Minimum number of seconds to run all queries: 0.070 seconds
Maximum number of seconds to run all queries: 0.070 seconds
Number of clients running queries: 100
Average number of queries per client: 2

Benchmark
Running for engine myisam
Average number of seconds to run all queries: 0.092 seconds
Minimum number of seconds to run all queries: 0.092 seconds
Maximum number of seconds to run all queries: 0.092 seconds
Number of clients running queries: 200
Average number of queries per client: 1

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.115 seconds
Minimum number of seconds to run all queries: 0.115 seconds
Maximum number of seconds to run all queries: 0.115 seconds
Number of clients running queries: 50
Average number of queries per client: 4

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.134 seconds
Minimum number of seconds to run all queries: 0.134 seconds
Maximum number of seconds to run all queries: 0.134 seconds
Number of clients running queries: 100
Average number of queries per client: 2

Benchmark
Running for engine innodb
Average number of seconds to run all queries: 0.192 seconds
Minimum number of seconds to run all queries: 0.192 seconds
Maximum number of seconds to run all queries: 0.192 seconds
Number of clients running queries: 200
Average number of queries per client: 1

User time 0.06, System time 0.15
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 5803, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 8173, Involuntary context switches 528

我来解释一下结果的含义。
拿每个引擎最后一个Benchmark示例。
对于INNODB引擎，200个客户端同时运行这些SQL语句平均要花0.192秒。相应的MYISAM为0.092秒。

2、用我们自己定义的SQL 脚本来测试。
这些数据在另外一个MySQL实例上。版本为5.0.45
先看一下这两个表的相关数据。
1）、总记录数。
mysql> select table_rows as rows from information_schema.tables where table_schema='t_girl' and table_name='article'; +--------+ | rows | +--------+ | 296693 | +--------+ 1 row in set (0.01 sec)

mysql> select table_rows as rows from information_schema.tables where table_schema=‘t_girl’ and table_name=‘category’;
+------+
| rows |
+------+
| 113 |
+------+
1 row in set (0.00 sec)

2）、总列数。
mysql> select count(*) as column_total from information_schema.columns where table_schema = 't_girl' and table_name = 'article'; +--------------+ | column_total | +--------------+ | 32 | +--------------+ 1 row in set (0.01 sec)

mysql> select count(*) as column_total from information_schema.columns where table_schema = ‘t_girl’ and table_name = ‘category’;
+--------------+
| column_total |
+--------------+
| 9 |
+--------------+
1 row in set (0.01 sec)

3）、调用的存储过程
DELIMITER $$

DROP PROCEDURE IF EXISTS `t_girl`.`sp_get_article`$$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_get_article`(IN f_category_id int,
IN f_page_size int, IN f_page_no int
)
BEGIN
set @stmt = ‘select a.* from article as a inner join ‘;
set @stmt = concat(@stmt,‘(select a.aid from article as a ‘);
if f_category_id != 0 then
set @stmt = concat(@stmt,‘ inner join (select cid from category where cid = ‘,f_category_id,‘ or parent_id = ‘,f_category_id,‘) as b on a.category_id = b.cid’);
end if;
if f_page_size >0 && f_page_no > 0 then
set @stmt = concat(@stmt,‘ limit ‘,(f_page_no-1)*f_page_size,‘,’,f_page_size);
end if;

set @stmt = concat(@stmt,‘) as b on (a.aid = b.aid)’);
prepare s1 from @stmt;
execute s1;
deallocate prepare s1;
set @stmt = NULL;
END$$

DELIMITER ;
4）、我们用mysqlslap来测试
以下得这个例子代表用mysqlslap来测试并发数为25,50,100的调用存储过程，并且总共调用5000次。
[root@localhost ~]# mysqlslap --defaults-file=/usr/local/mysql-maria/my.cnf --concurrency=25,50,100 --iterations=1 --query='call t_girl.sp_get_article(2,10,1);' --number-of-queries=5000 --debug-info -uroot -p -S/tmp/mysql50.sock
Enter password:
Benchmark
Average number of seconds to run all queries: 3.507 seconds
Minimum number of seconds to run all queries: 3.507 seconds
Maximum number of seconds to run all queries: 3.507 seconds
Number of clients running queries: 25
Average number of queries per client: 200
平均每个并发运行200个查询用了3.507秒。
Benchmark
Average number of seconds to run all queries: 3.742 seconds
Minimum number of seconds to run all queries: 3.742 seconds
Maximum number of seconds to run all queries: 3.742 seconds
Number of clients running queries: 50
Average number of queries per client: 100

Benchmark
Average number of seconds to run all queries: 3.697 seconds
Minimum number of seconds to run all queries: 3.697 seconds
Maximum number of seconds to run all queries: 3.697 seconds
Number of clients running queries: 100
Average number of queries per client: 50

User time 0.87, System time 0.33
Maximum resident set size 0, Integral resident set size 0
Non-physical pagefaults 1877, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 27218, Involuntary context switches 3100

[root@localhost ~]# mysqlslap --defaults-file=/usr/local/mysql-maria/my.cnf --concurrency=25,50,100 --iterations=1 --create-schema='t_girl' --query='call sp_get_article(2,10,1);' --number-of-queries=5000 --debug-info -uroot -p -S/tmp/mysql50.sock

小总结一下。
mysqlslap对于模拟多个用户同时对MySQL发起“进攻”提供了方便。同时详细的提供了“高负荷攻击MySQL”的详细数据报告。
而且如果你想对于多个引擎的性能。这个工具再好不过了。

分类: MySQL 标签: MySQL

为你的MySQL数据库加铸23道安全门

2008年3月21日 16hot 没有评论

转自cu:http://linux.chinaunix.net/docs/2007-04-02/4101.shtml

使用MySQL，安全问题不能不注意。以下是MySQL提示的23个注意事项：

1.如果客户端和服务器端的连接需要跨越并通过不可信任的网络，那么就需要使用SSH隧道来加密该连接的通信。

2.用set password语句来修改用户的密码，三个步骤，先“mysql -u root”登陆数据库系统，然后“mysql> update mysql.user set password=password(‘newpwd’)”，最后执行“flush privileges”就可以了。

3.需要提防的攻击有，防偷听、篡改、回放、拒绝服务等，不涉及可用性和容错方面。对所有的连接、查询、其他操作使用基于ACL即访问控制列表的安全措施来完成。也有一些对SSL连接的支持。

4.除了root用户外的其他任何用户不允许访问mysql主数据库中的user表;

加密后存放在user表中的加密后的用户密码一旦泄露，其他人可以随意用该用户名/密码相应的数据库;

5.用grant和revoke语句来进行用户访问控制的工作;

6.不使用明文密码，而是使用md5()和sha1()等单向的哈系函数来设置密码;

7.不选用字典中的字来做密码;

8.采用防火墙来去掉50%的外部危险，让数据库系统躲在防火墙后面工作，或放置在DMZ区域中;

9.从因特网上用nmap来扫描3306端口，也可用telnet server_host 3306的方法测试，不能允许从非信任网络中访问数据库服务器的3306号TCP端口，因此需要在防火墙或路由器上做设定;

10.为了防止被恶意传入非法参数，例如where ID=234，别人却输入where ID=234 OR 1=1导致全部显示，所以在web的表单中使用”或”"来用字符串，在动态URL中加入%22代表双引号、%23代表井号、%27代表单引号;传递未检查过的值给mysql数据库是非常危险的;

11.在传递数据给mysql时检查一下大小;

12.应用程序需要连接到数据库应该使用一般的用户帐号，只开放少数必要的权限给该用户;

13.在各编程接口(C C++ PHP Perl Java JDBC等)中使用特定‘逃脱字符’函数;

在因特网上使用mysql数据库时一定少用传输明文的数据，而用SSL和SSH的加密方式数据来传输;

14.学会使用tcpdump和strings工具来查看传输数据的安全性，例如tcpdump -l -i eth0 -w -src or dst port 3306 strings。以普通用户来启动mysql数据库服务;

15.不使用到表的联结符号，选用的参数 –skip-symbolic-links;

16.确信在mysql目录中只有启动数据库服务的用户才可以对文件有读和写的权限;

17.不许将process或super权限付给非管理用户，该mysqladmin processlist可以列举出当前执行的查询文本;super权限可用于切断客户端连接、改变服务器运行参数状态、控制拷贝复制数据库的服务器;

18.file权限不付给管理员以外的用户，防止出现load data ‘/etc/passwd’到表中再用select 显示出来的问题;

19.如果不相信DNS服务公司的服务，可以在主机名称允许表中只设置IP数字地址;

20.使用max_user_connections变量来使mysqld服务进程，对一个指定帐户限定连接数;

21.grant语句也支持资源控制选项;

22.启动mysqld服务进程的安全选项开关，–local-infile=0或1 若是0则客户端程序就无法使用local load data了，赋权的一个例子grant insert(user) on mysql.user to ‘user_name’@'host_name’;若使用–skip-grant-tables系统将对任何用户的访问不做任何访问控制，但可以用 mysqladmin flush-privileges或mysqladmin reload来开启访问控制;默认情况是show databases语句对所有用户开放，可以用–skip-show-databases来关闭掉。

23.碰到Error 1045(28000) Access Denied for user ‘root’@'localhost’ (Using password:NO)错误时，你需要重新设置密码，具体方法是:先用–skip-grant-tables参数启动mysqld，然后执行 mysql -u root mysql,mysql>update user set password=password(‘newpassword’) where user=’root’;mysql>Flush privileges;，最后重新启动mysql就可以了。

分类: MySQL 标签: MySQL

将SELECT结果导到文件的操作

2007年4月12日 16hot 没有评论

1.使用MySQL命令

SELECT columns FROM table_name WHERE whatever='something' INTO OUTFILE "/tmp/outfile.txt";

2.命令行方式

2.1.管道方式

echo "SELECT columns FROM table_name WHERE whatever='something'" | /path/to/mysql -uUSERNAME -pPASSWORD DATABASENAME > /tmp/outfile.txt;

2.2.使用SQL文件

/path/to/mysql -uUSERNAME -pPASSWORD DATABASENAME < /tmp/sql.sql > /tmp/outfile.txt;

分类: MySQL 标签: MySQL

MySQL memory used

2005年10月3日 16hot 没有评论

MySQL memory used = key_buffer + max_connections * (join_buffer + record_buffer + sort_buffer + thread_stack + tmp_table_size)

分类: MySQL 标签:

16hot 的博客

存档

MySQL 分支 MariaDB

解决wiki内容出现换行n字符的问题