Java伴侣 - Database

httpd.exe 应用程序错误，该内存不能为read

blurxx@yahoo.cn(Java伴侣) — Sun,13 Nov 2011 21:35:53 +0800

安装完后，打开普通php文件正常，而打开连接数据库的php文件出现 httpd.exe 应用程序错误，该内存不能为read。

可将C:\php5文件夹下的libmysql.dll 和 php5ts.dll拷到C:\WINDOWS\system32下，即可。

在Select语句中同时使用COUNT和DISTINCT

blurxx@yahoo.cn(Java伴侣) — Fri,09 Apr 2010 10:57:45 +0800

在Select语句中同时使用COUNT和DISTINCT

MySQL配置方案，如my-medium.ini、my-huge

blurxx@yahoo.cn(Java伴侣) — Wed,10 Mar 2010 14:11:48 +0800

MySql noinstall-5.1.xx-win32 配置（原创）
1、解压mysql-noinstall-5.1.xx-win32.zip 到你喜欢的目录,例如：d:\php\mysql

2、在根目录d:\php\mysql中有五个配置信息文件：
my-small.ini （内存 <= 64M）
my-medium.ini （内存 128M ）
my-large.ini （内存 512M）
my-huge.ini （内存 1G-2G）
my-innodb-heavy-4G.ini （内存 4GB）

假设我的内存是1G的，我选择了my-huge.ini，选择其中一个适合你自己机子的配置文件。

3、编辑my.ini，在[mysqld]的“port=3306”下面增加如下两句：

注意，在Windows中路径名中使用斜杠“/”而不是反斜杠“\”。如果使用反斜杠“\”，那么必须双写它们“\\”。

# 安装目录的路径 # 设置mysql数据库的数据的存放目录，必须是data，或者是\\xxx\data

basedir="E:/by8/MySQL/"

# 设置mysql数据库的数据的存放目录
datadir="F:/by8/MySQL/Data/"

# 设置mysql服务器的字符集
default-character-set = utf8

# 默认的存储引擎
default-storage-engine = MYISAM

移动“E:\by8\mysql\data”目录到“F:\by8\MySQL\Data”。

在[mysql]下面新增：
default-character-set = utf8

修改以下几句为：
max_allowed_packet = 32M (默认为1M)
sort_buffer_size = 4M (MySQL 5.0.15 my-medium.ini默认为512K)

max_allowed_packet 参数说明：
参数的作用是，用来控制其通信缓冲区的最大长度。

sort_buffer_size 参数说明：
查询排序时所能使用的缓冲区大小。
注意：该参数对应的分配内存是每连接独占！
如果有100个连接，那么实际分配的总共排序缓冲区大小为100 × 6 ＝ 600MB。
所以，对于内存在4GB左右的服务器推荐设置为6-8M。

4、安装服务：
"E:\by8\mysql\bin\mysqld" --install Mysql --defaults-file="E:\by8\Mysql\my.ini"

5、启动服务：
net start mysql

这样mysql就安装好了。你任务管理器当中可以看到mysqld-nt.exe这个进程。说明您的mysql已经安装好了。并成功的启动了。

6、为匿名用户设置密码：

%myd_dir%\bin\mysqladmin.exe -uroot -ptoor password "%my_newpwd%"

刚开始安装好的mysql登录密码是空密码：

C:\Documents and Settings\Administrator>e:
E:\>cd by8
E:\by8>cd mysql
E:\by8\Mysql>cd bin
E:\by8\Mysql\bin\>mysql -uroot -p
Enter password: //这里直接按回车（enter），不需要输入密码的。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)

为了安全，我们这里修改下刚安装的mysql数据库的root用户的密码。操作命令如下：

mysql> use mysql
Database changed
mysql> update user set password=password("123456") where user="root";
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> exit //退出

就这样我把root的密码修改为了123456。

还有中修改密码的方法是利用msyqladmin这个命令
确认可以登录我们退出mysql
格式：mysqladmin -u用户名 -p旧密码 password 新密码
>exit
>mysqladmin -uroot -password 123456
//我们这里把mysql root的原来的空密码修改成123456，因为原来root没有密码，所以-p 就密码这项就可以省略了。
为了让大家印象更深刻些，我们这里再把已经修改过的密码1234560修改成为 123456
命令：mysqladmin -uroot -p123456 password 1234

PS:MYSQL记录用户名和密码的两张表为：

user.MYD和user.MYI，备份下他们，以后就不用为重装改密码麻烦了

创建mysql innodb数据库

blurxx@yahoo.cn(Java伴侣) — Wed,10 Mar 2010 14:10:16 +0800

       InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。InnoDB 提供了行锁(locking on row level)，提供与 oracle 类型一致的不加锁读取(non-locking read in Selects)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lock escalation)，因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。
            要想正常的使用InnoDB带给我们的好处，首先要建立InnoDB数据库。示例采用的mysql版本是4.0.12，这个版本属于mysql的beta版本。假设你的mysql已经安装完毕，并且采用的是win xp操作系统，接下来我们就要创建InnoDB数据库。
            当创建一个 InnoDB 数据库时最好以命令行方式来运行 MySQL 服务 mysqld ，而不是从 safe_mysqld 或 Windows 服务。当从命令行方式运行服务时可以看到建立过程。
      步骤1：通过 MS-DOS的 NET STOP MySQL 命令停止mysql服务。
      步骤2：修改c:/windows/my.ini文件，主要是设置Innodb的参数。从设置最小的角度考虑，可以
             设置一下几项:
               innodb_data_home_dir                                  /*innodb数据库数据文件目录*/
               innodb_data_file_path                                  /*数据文件名称及大小*/
                       innodb_buffer_pool_size                      /*缓冲池的大小，一般设置为主存的50%-80%*/
               innodb_additional_mem_pool_size            /**/
               innodb_log_file_size                                   /*日志文件的大小,一般设置为主存的25%*/
               innodb_log_buffer_size                               /**/
               innodb_flush_log_at_trx_commit               /*事务*/
            具体设置如下：

            [mysqld]
            basedir=C:/mysql
            #bind-address=192.168.1.8
            datadir=C:/mysql/data
            innodb_data_file_path = /ibdata2:100M:autoextend:max:2000M
            set-variable = innodb_buffer_pool_size=200M
            set-variable = innodb_additional_mem_pool_size=15M

            set-variable = innodb_log_file_size=50M
            set-variable = innodb_log_buffer_size=10M
            innodb_flush_log_at_trx_commit=1
            #language=C:/mysql/share/your language directory
            #slow query log#=
            #tmpdir#=
            #port=3306
            #set-variable=key_buffer=16M
            [WinMySQLadmin]
            Server=C:/mysql/bin/mysqld-nt.exe
            user=zhangxl
            password=zhangxl

         解释：ibdata2为自定义的数据文件名称。在默认情况下，系统将自动创建ibdata0和ibdata1,这两个文件是mysql安装后自带的。因此，如果用户想以这两个文件作为设置的数据文件，首先要删除他们以及其他的3个日志文件，否则在使用mysql-max --console时会提示数据库创建失败。提醒一点的是，在删除文件之前一定要先停止mysql服务，在dos下执行net stop mysql即可，然后备份数据文件和日志文件，防止mysql启动失败，以备恢复。
      InnoDB 不会自己建立目录，必须自己使用操作系统命令建立相应的目录。检查你的 MySQL 服务程序在 datadir 目录里有足够的权限建立文件。
   这里没有设置innodb_data_home_dir参数，在默认情况下，mysql自动会指向/mysql/data目录，innodb_data_file_path 可以设置多个数据库文件，在本例中只设置一个，同时还可以通过autoextend属性把数据文件设为可扩展的，通过max属性设置最后一个数据文件的最大值，在windows下一般为2G。
      步骤3：通过命令net start mysql启动mysql,然后执行命令mysql-max --console命令。
      执行完上一步操作后，进入mysql安装目录的/data目录下可以看到生成的数据文件和日志文件。
      参考：
      http://dev.mysql.com/doc/refman/5.0/en/error-creating-innodb.html
      http://man.chinaunix.net/database/mysql/inonodb_zh/

mysql常见错误提示及解决方法

blurxx@yahoo.cn(Java伴侣) — Wed,10 Mar 2010 14:09:30 +0800

下面的内容转自（jxtm.cublog.cn ）

130 ：文件格式不正确。（还不是很清楚错误的状况）

145  ：文件无法打开。

1005：创建表失败。

1006：创建数据库失败。

1007：数据库已存在，创建数据库失败。

1008：数据库不存在，删除数据库失败。

1009：不能删除数据库文件导致删除数据库失败。

1010：不能删除数据目录导致删除数据库失败。

1011：删除数据库文件失败。

1012：不能读取系统表中的记录。

1016：文件无法打开，使用后台修复或者使用 phpmyadmin 进行修复。

Quote:

开始=>所有程序=>附件=>命令提示符

  输入 mysql 所在硬盘盘符

    cd mysql 所在目录

    cd bin

  输入 myisamchk -f D:usr/local/mysql/data/bbs/PW_members.MYI

  ps : D:usr/local/mysql/data/bbs 是你论坛数据库的路径

    -f 根据具体情况选择，一般也可以选择 -r

    注意你的系统C盘或放数据库的硬盘空间是否足够，一般小于 1G 很容易出现错误。

或用mysqlcheck命令进行修复。具体的方法：利用命令行进入mysql/bin目录，执行

mysqlcheck -o -r phpwind -uroot -p

其中phpwind是你数据库的名称，root是你的数据库用户名，然后会提示你输入密码。然后就会修复你的数据库。

1017：服务器非法关机，导致该文件损坏。

1020：记录已被其他用户修改。

1021：硬盘剩余空间不足，请加大硬盘可用空间。

1022：关键字重复，更改记录失败。

1023：关闭时发生错误。

1024：读文件错误。

1025：更改名字时发生错误。

1026：写文件错误。

1030：可能是服务器不稳定。（具体原因不是很清楚）

1032：记录不存在。

1036：数据表是只读的，不能对它进行修改。

1037：系统内存不足，请重启数据库或重启服务器。

1038：用于排序的内存不足，请增大排序缓冲区。

1040：已到达数据库的最大连接数，请加大数据库可用连接数。

Quote:

在my.ini 修改max_connections=100为max_connections=1000或更大,重启mysql

1041：系统内存不足。

1042：无效的主机名。

1043：无效连接。

1044：数据库用户权限不足，请联系空间商解决。

1045：数据库服务器/数据库用户名/数据库名/数据库密码错误，请联系空间商检查帐户。

Quote:

方法:确保论坛data目录下的sql_config.php用户名与密码都正确.如果用户忘记了数据库的密码,可以按如下方式进行密码的修改:

如果 MySQL 正在运行，首先停止。

启动 MySQL ：bin/safe_mysqld --skip-grant-tables &

就可以不需要密码就进入 MySQL 了。

然后就是

>use mysql

>update user set password=password("new_pass") where user="root";

>flush privileges;

1046：没有选择数据库。

1048：字段不能为空。

1049：数据库不存在。

1050：数据表已存在。

1051：数据表不存在。

1054：字段不存在，自行建立字段。

1060：字段重复，导致无法插入这个字段。

1062：字段值重复，入库失败

Quote:

  1.如果出类似主码为"65535"的错误,可以查看相关表的自增字段,将字段值改在就可以

  2.确保相关数据表中主码重复的字段是否存在,如果存在删除这条记录

  3.备份数据库,修复相关表(注:这种情况比较常见,如pw_posts表,对表进行修复的时候不要忘记备份).

1064：MySQL 不支持错误提示中的编码。

1065：无效的 SQL 语句，SQL 语句为空。

1067：MySQL 版本为 5，不支持空的默认值。

1081：不能建立 Socket 连接。

1114：数据表已满，不能容纳任何记录。

1115：设置的字符集在 MySQL 并没有支持。

1116：打开的数据表太多。

1129：数据库出现异常，请重启数据库。

1130：连接数据库失败，没有连接数据库的权限。

1133：数据库用户不存在。

1135：可能是内存不足够，请联系空间商解决。

1141：当前用户无权访问数据库。

1142：当前用户无权访问数据表。

1143：当前用户无权访问数据表中的字段。

1146：数据表缺失，请恢复备份数据

1147：未定义用户对数据表的访问权限。

1149：SQL 语句语法错误。

1158：网络错误，出现读错误，请检查网络连接状况。

1159：网络错误，读超时，请检查网络连接状况。

1160：网络错误，出现写错误，请检查网络连接状况。

1161：网络错误，写超时，请检查网络连接状况。

1169：字段值重复，更新记录失败。

1177：打开数据表失败。

1180：提交事务失败。

1181：回滚事务失败。

1193：不支持字符集限定(SET NAMES)。

1203：当前用户和数据库建立的连接已到达数据库的最大连接数，请增大可用的数据库连接数或重启数据库。

1205：加锁超时。

1211：当前用户没有创建用户的权限。

1216：外键约束检查失败，更新子表记录失败。

1217：外键约束检查失败，删除或修改主表记录失败。

1226：当前用户使用的资源已超过所允许的资源，请重启数据库或重启服务器。

1227：权限不足，您无权进行此操作。

1235：MySQL版本过低，不具有本功能。

1250：客户端不支持服务器要求的认证协议，请考虑升级客户端。

1251:Client 不能支持 authentication protocol 的要求

Client does not support authentication protocol requested by server; consider upgrading MySQL client

Quote:

方法1：mysql> SET PASSWORD FOR

-> ' some_user '@' some_host ' = OLD_PASSWORD(' newpwd ');

结合我们的实际情况,在 MySQL Command Line Client 下运行:

set password for root@localhost = old_password('123456');

方法2:

mysql> Update mysql.user SET Password = OLD_PASSWORD('newpwd')

    -> Where Host = 'some_host' AND User = 'some_user';

mysql> FLUSH PRIVILEGES;

上面红色的部分请按自己实际情况修改。

1267：不合法的混合字符集。

2002：服务器端口不对，请咨询空间商正确的端口。

2003：MySQL 服务没有启动，请启动该服务。

2008：MySQL client ran out of memory

错误指向了MySQL客户mysql。这个错误的原因很简单，客户没有足够的内存存储全部结果。

2013：远程连接数据库是有时会有这个问题，MySQL 服务器在执行一条 SQL 语句的时候失去了连接造成的。

10048:

Quote:

建议在my.ini文件中修改最大连接数，

  把 mysql_connect() 方法都改成了 mysql_pconnect() 方法.

  要修改mysql_pconnect()，可以在论坛的data目录的sql_config.php中

  $pconnect = 0; //是否持久连接

  修改成$pconnect = 1;

  开启防刷新,严禁刷新太快.

10055:没有缓存空间可利用

Quote:

查看下你的C盘空间是否已经满,清除一些没有用的文件.

  可以在后台的"论坛核心设置","核心功能设置"里"进程优化"开启,"GZIP 压缩输出"关闭.

查找了一下10055（没有缓存空间可利用）出错的原因，分析了my.ini的配制文件，在my.ini中如下：

default-storage-engine=INNODB

innodb_additional_mem_pool_size=2M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=10M

innodb_log_file_size=10M

innodb_thread_concurrency=8

觉得可以把innodb_buffer_pool_size=10M　加大如100M或是1000M

以上是对mysql5的

如果是mysql4可以在my.ini中增加如下：

#innodb_data_file_path = ibdata1：2000M;ibdata2：2000M

#innodb_data_home_dir = c：ibdata

#innodb_log_group_home_dir = c：iblogs

#innodb_log_arch_dir = c：iblogs

#set-variable = innodb_mirrored_log_groups=1

#set-variable = innodb_log_files_in_group=3

#set-variable = innodb_log_file_size=5M

#set-variable = innodb_log_buffer_size=8M

#innodb_flush_log_at_trx_commit=1

#innodb_log_archive=0

#set-variable = innodb_buffer_pool_size=16M

#set-variable = innodb_additional_mem_pool_size=2M

#set-variable = innodb_file_io_threads=4

#set-variable = innodb_lock_wait_timeout=50

把前面的#去了

10061:

Quote:

启动这台机器上的MySQL服务

  如服务启动失败

  一定是你的my.ini文件出了差错，

  MySQL服务不能正常启动

  你删除了它后，MySQL就会按其默认配置运行，

  那就没有问题了

如何开启mysql的日志或如何查看mysql的日志文件

blurxx@yahoo.cn(Java伴侣) — Wed,10 Mar 2010 13:00:46 +0800

mysql有以下几种日志：
错误日志： -log-err
查询日志： -log
慢查询日志: -log-slow-queries
更新日志: -log-update
二进制日志： -log-bin

在mysql的安装目录下，打开my.ini,在后面加上上面的参数，保存后重启mysql服务就行了。
例如：

复制内容到剪贴板程序代码

#Enter a name for the binary log. Otherwise a default name will be used.
#log-bin=
#Enter a name for the query log file. Otherwise a default name will be used.
#log=
#Enter a name for the error log file. Otherwise a default name will be used.
log-error=
#Enter a name for the update log file. Otherwise a default name will be used.
#log-update=

上面只开启了错误日志，要开其他的日志就把前面的“#”去掉

查看命令：

复制内容到剪贴板程序代码

①show variables like 'log_%';查看所有的log命令

②show variables like 'log_bin';查看具体的log命令

有效配置MySQL参数

blurxx@yahoo.cn(Java伴侣) — Wed,10 Mar 2010 11:33:35 +0800

1．获取当前配置参数

要优化配置参数，首先要了解当前的配置参数以及运行情况。使用下列命令可以获得目前服务器使用的配置参数：

mysqld –verbose –help

mysqladmin variables extended-status –u root –p

在MySQL控制台里面，运行下列命令可以获取状态变量的值：

mysql> SHOW STATUS;

如果只要检查某几个状态变量，可以使用下列命令：

mysql> SHOW STATUS LIKE ‘[匹配模式]’; ( 可以使用%、?等 )

2．优化参数

参数优化基于一个前提，就是在我们的数据库中通常都使用InnoDB表，而不使用MyISAM表。在优化MySQL时，有两个配置参数是最重要的，即table_cache和key_buffer_size。

table_cache

table_cache指定表高速缓存的大小。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了（上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得）。注意，不能盲目地把table_cache设置成很大的值。如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

对于有1G内存的机器，推荐值是128－256。

案例1：该案例来自一个不是特别繁忙的服务器

table_cache – 512

open_tables – 103

opened_tables – 1273

uptime – 4021421 (measured in seconds)

该案例中table_cache似乎设置得太高了。在峰值时间，打开表的数目比table_cache要少得多。

案例2：该案例来自一台开发服务器。

table_cache – 64

open_tables – 64

opened-tables – 431

uptime – 1662790 (measured in seconds)

虽然open_tables已经等于table_cache，但是相对于服务器运行时间来说，opened_tables的值也非常低。因此，增加table_cache的值应该用处不大。

案例3：该案例来自一个upderperforming的服务器

table_cache – 64

open_tables – 64

opened_tables – 22423

uptime – 19538

该案例中table_cache设置得太低了。虽然运行时间不到6小时，open_tables达到了最大值，opened_tables的值也非常高。这样就需要增加table_cache的值。

key_buffer_size

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得）。

key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

对于1G内存的机器，如果不使用MyISAM表，推荐值是16M（8-64M）。

案例1：健康状况

key_buffer_size – 402649088 (384M)

key_read_requests – 597579931

key_reads - 56188

案例2：警报状态

key_buffer_size – 16777216 (16M)

key_read_requests – 597579931

key_reads - 53832731

案例1中比例低于1:10000，是健康的情况；案例2中比例达到1:11，警报已经拉响。

优化query_cache_size

从4.0.1开始，MySQL提供了查询缓冲机制。使用查询缓冲，MySQL将Select语句和查询结果存放在缓冲区中，今后对于同样的Select语句（区分大小写），将直接从缓冲区中读取结果。根据MySQL用户手册，使用查询缓冲最多可以达到238％的效率。

通过检查状态值Qcache_*，可以知道query_cache_size设置是否合理（上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得）。如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况，如果Qcache_hits的值也非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小；如果Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。此外，在Select语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。

与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。query_cache_type指定是否使用查询缓冲，可以设置为0、1、2，该变量是SESSION级的变量。query_cache_limit指定单个查询能够使用的缓冲区大小，缺省为1M。query_cache_min_res_unit是在4.1版本以后引入的，它指定分配缓冲区空间的最小单位，缺省为4K。检查状态值Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多，这就表明查询结果都比较小，此时需要减小query_cache_min_res_unit。

开启二进制日志( Binary Log )

二进制日志包含所有更新数据的语句，其目的是在恢复数据库时用它来把数据尽可能恢复到最后的状态。另外，如果做同步复制( Replication )的话，也需要使用二进制日志传送修改情况。

开启二进制日志，需要设置参数log-bin。log_bin指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。MySQL会在文件名后面自动添加数字索引，每次启动服务时，都会重新生成一个新的二进制文件。

此外，使用log-bin-index可以指定索引文件；使用binlog-do-db可以指定记录的数据库；使用binlog-ignore-db可以指定不记录的数据库。注意的是：binlog-do-db和binlog-ignore-db一次只指定一个数据库，指定多个数据库需要多个语句。而且，MySQL会将所有的数据库名称改成小写，在指定数据库时必须全部使用小写名字，否则不会起作用。

在MySQL中使用SHOW MASTER STATUS命令可以查看目前的二进制日志状态。

开启慢查询日志( slow query log )

慢查询日志对于跟踪有问题的查询非常有用。它记录所有查过long_query_time的查询，如果需要，还可以记录不使用索引的记录。下面是一个慢查询日志的例子：

开启慢查询日志，需要设置参数log_slow_queries、long_query_times、log-queries-not-using-indexes。log_slow_queries指定日志文件，如果不提供文件名，MySQL将自己产生缺省文件名。long_query_times指定慢查询的阈值，缺省是10秒。log-queries-not-using-indexes是4.1.0以后引入的参数，它指示记录不使用索引的查询。

配置InnoDB

相对于MyISAM表来说，正确配置参数对于InnoDB表更加关键。其中，最重要的参数是innodb_data_file_path。它指定表数据和索引存储的空间，可以是一个或者多个文件。最后一个数据文件必须是自动扩充的，也只有最后一个文件允许自动扩充。这样，当空间用完后，自动扩充数据文件就会自动增长（以8MB为单位）以容纳额外的数据。例如：

innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:50M:autoextend

两个数据文件放在不同的磁盘上。数据首先放在ibdata1中，当达到900M以后，数据就放在ibdata2中。一旦达到50MB，ibdata2将以8MB为单位自动增长。

如果磁盘满了，你需要在另外的磁盘上面增加一个数据文件。为此，你需要查看最后一个文件的尺寸，然后计算最接近的整数（MB）。然后手工修改该文件的大小，并添加新的数据文件。例如：假设ibdata2已经有109MB数据，那么可以修改如下：

innodb_data_file_path=/disk1/ibdata1:900M;/disk2/ibdata2:109M;/disk3/ibdata3:500M:autoextend

flush_time

如果系统有问题并且经常锁死或重新引导，应将该变量设置为非零值，这将导致服务器按flush_time 秒来刷新表的高速缓存。用这种方法来写出对表的修改将降低性能，但可减少表讹误或数据丢失的机会。

一般使用缺省值。

Binlog_cache_size

The size of the cache to hold the SQL statements for the binary log during a transaction. A binary log cache is allocated for each client if the server supports any transactional storage engines and if the server has binary log enabled(--log-bin option). If you often use big, multiple-statement transactions, you can increase this to get more performance. The Binlog_cache_use and Binlog_cache_disk_use status variables can be useful for tuning the size of this variable.

3．存储引擎

在MYSQL 3.23.0版本中，引入了MyISAM存储引擎。它是一个非事务型的存储引擎，成为了MYSQL的缺省存储引擎。但是，如果使用设置向导来设置参数，则它会把InnoDB作为缺省的存储引擎。InnoDB是一个事务型的存储引擎。

创建表的时候，可以为表指定存储引擎，语法如下：

Create TABLE t (i INT) ENGINE = MyISAM

Create TABLE t (i INT) TYPE = MyISAM

如果没有指定，则使用缺省的存储引擎。也可以使用Alter TABLE来更换表引擎，语法如下：

Alter TABLE t ENGINE = MyISAM

同一数据库中可以包含不同存储引擎的表。

事务型表具有以下特点：

Ø Safer. Even if MySQL crashes or you get hardware problems, you can get your data back, either by automatic recovery or from a backup plus the transaction log.

Ø You can combine many statements and accept them all at the same time with the COMMIT statement (if autocommit is disabled).

Ø You can execute ROLLBACK to ignore your changes (if autocommit is disabled).

Ø If an update fails, all your changes will be restored. (With non-transaction-safe tables, all changes that have taken place are permanent.)

Ø Transaction-safe storage engines can provide better concurrency for tables that get many updates concurrently with reads.

非事务型表具有以下优点：

Ø Much faster

Ø Lower disk space requirements

Ø Less memory required to perform updates

4．MyISAM存储引擎

下面MyISAM的参数是MySQL手册推荐的参数，据说适应于大部分情况。对于如何监视参数设置是否合理，仍然没有头绪。

max_connections=200

read_buffer_size=1M

read_rnd_buffer_size＝8M

sort_buffer_size=1M

Read_buffer_size

Each thread that does a sequential scan allocates a buffer of this size for each table it scans. If you do many sequential scans, you might want to increse this value.

Read_rnd_buffer_size

When reading rows in sorted order after a sort, the rows are read through this buffer to avoid disk seeks. Setting the variable to a large value can improve orDER BY performance by a lot. However, this is a buffer allocated for each client, so you should not set the global variable to a large value. Instead, change the session variable only from within those clients that need to run large queries.

Bulk_insert_buffer_size

该参数于4.0.3中引入。MyISAM使用一个树型的缓冲区来加速大量的插入，如Insert…Select，Insert…VALUES(…),VALUES(…),…,LOAD DATA INFILE等。该参数指定了缓冲区的大小。缺省值为8M，设置为0则表示不使用该优化。

如果不使用MyISAM表，则可以将其设置为0。

5．InnoDB存储引擎

参考了很多资料，都没有明确地表明如何优化InnoDB参数，以及如何监视这些参数设置是否合理，只有根据MySQL用户手册上面的介绍来进行设置。

innodb_buffer_pool_size

对于InnoDB表来说，innodb_buffer_pool_size的作用就相当于key_buffer_size对于MyISAM表的作用一样。InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%。

根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。

innodb_flush_log_at_trx_commit

该值指定InnoDB记录日志的方式。如果设置为1，则每个事务提交的时候，MySQL都会将事务日志写入磁盘。如果设置为0或者2，则大概每秒中将日志写入磁盘一次。（还不清楚0和2的区别）

实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要2秒，设置为0时只需要1秒，而设置为1时则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅提高速度。

根据MySQL手册，在存在丢失最近部分事务的危险的前提下，可以把该值设为0。

innodb_log_file_size

The size of each log file in a log group. The default is 5MB. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But large log files also mean that recovery will be slower in case of a crash.

根据MySQL手册，推荐值是innodb_buffer_pool_size的25%。

注意：在重新设置该值时，好像要把原来的文件删除掉。

innodb_log_buffer_size

The size of the buffer that InnoDB uses to write to the log files on disk. Sensible values range from 1MB to 8MB. The default is 1MB. A large log buffer allows large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger will save disk I/O.

根据MySQL手册，推荐值是8M。

innodb_additional_mem_pool_size

该参数指定InnoDB用来存储数据字典和其他内部数据结构的内存池大小。缺省值是1M。通常不用太大，只要够用就行，应该与表结构的复杂度有关系。如果不够用，MySQL会在错误日志中写入一条警告信息。

根据MySQL手册，对于2G内存的机器，推荐值是20M。

SHOW INNODB STATUS

显示InnoDB存储引擎的状态

InnoDB表报错: table is full

blurxx@yahoo.cn(Java伴侣) — Wed,10 Mar 2010 11:30:17 +0800

事碰到麻烦，寻求帮忙，问题是这样的：
有个InnoDB表，想要用 LOAD DATA INFILE 的方式倒数据进去，发现报错：table is full。
我看了一下，日志中没有相关可用信息，该表使用的是共享表空间，总共6个ibdata*文件，只有2个文件的修改时间是最新的，觉得可能不是因为表空间慢的缘故，于是尝试插入少量数据试试看先。分多次插入10,20,100条记录都没问题，一次性插入500多条记录时，就又报table is full了。看来，事务没有问题，再把焦点转会表空间问题上来。尝试性的关闭mysqld，新加一个表空间文件，启动，再插入更多数据，发现这次没问题了，搞定。

小结：InnoDB的共享表空间文件写入并不是预想的那样，按照顺序写入，先写 ibdata1，再写 ibdata2这样的，而是采用自己的机制。一开始没确定是表空间的问题，是因为发现最后一个表空间文件并没有被更新，认为表空间还有空闲，产生了这个惯性思维误区，呵呵。

MySQL InnoDB 数据库的恢复

blurxx@yahoo.cn(Java伴侣) — Thu,04 Mar 2010 09:24:30 +0800

误将MySQL重装，安装在不同的磁盘。还好数据文件还在。

先备份，以防随后恢复时可能的破坏。

在网上查找，用关键字“InnoDB 迁移”，“InnoDB 恢复”，只找到这么一行有用的：

innodb_force_recovery = 4

尝试恢复。

停止MySQL服务。

修改my.ini，在文件最后增加上面一行指令。

修改文件中datadir以及innodb_data_home_dir的指向－－指向之前的数据文件目录。

保存my.ini。

重新启动MySQL。

检查数据库及表，是否恢复正常。

如正常，去掉my.ini中的innodb_force_recovery = 4

重新启动MySQL。

OK。

InnoDB还是MyISAM 再谈MySQL存储引擎的选择

blurxx@yahoo.cn(Java伴侣) — Thu,04 Mar 2010 09:16:53 +0800

核心提示：作为MySQL数据库的两种主要的存储引擎，MyISAM和Innodb各有长处，本文的作者探讨了MyISAM不只适合小型项目，它的稳定性、扩展性和高可用性让作者对它情有独钟。

两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁.而MyISAM不支持.所以MyISAM往往就容易被人认为只适合在小项目中使用。

　　我作为使用MySQL的用户角度出发，Innodb和MyISAM都是比较喜欢的，但是从我目前运维的数据库平台要达到需求：99.9%的稳定性，方便的扩展性和高可用性来说的话，MyISAM绝对是我的首选。

引用内容

　　原因如下：

　　1、首先我目前平台上承载的大部分项目是读多写少的项目，而MyISAM的读性能是比Innodb强不少的。

　　2、MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。

　　3、从平台角度来说，经常隔1，2个月就会发生应用开发人员不小心update一个表where写的范围不对，导致这个表没法正常用了，这个时候 MyISAM的优越性就体现出来了，随便从当天拷贝的压缩包取出对应表的文件，随便放到一个数据库目录下，然后dump成sql再导回到主库，并把对应的 binlog补上。如果是Innodb，恐怕不可能有这么快速度，别和我说让Innodb定期用导出xxx.sql机制备份，因为我平台上最小的一个数据库实例的数据量基本都是几十G大小。

　　4、从我接触的应用逻辑来说，select count(*) 和order by 是最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级锁，那个只是 where对它主键是有效，非主键的都会锁全表的。

　　5、还有就是经常有很多应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的frm.MYD,MYI的文件，让他们自己在对应版本的数据库启动就行，而Innodb就需要导出xxx.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。

　　6、如果和MyISAM比insert写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，虽然MyISAM可能会逊色Innodb,但是那么高并发的写，从库能否追的上也是一个问题，还不如通过多实例分库分表架构来解决。

　　7、如果是用MyISAM的话，merge引擎可以大大加快应用部门的开发速度，他们只要对这个merge表做一些select count(*)操作，非常适合大项目总量约几亿的rows某一类型(如日志，调查统计)的业务表。

　　当然Innodb也不是绝对不用，用事务的项目如模拟炒股项目，我就是用Innodb的，活跃用户20多万时候，也是很轻松应付了，因此我个人也是很喜欢Innodb的，只是如果从数据库平台应用出发，我还是会首选MyISAM。

　　另外，可能有人会说你MyISAM无法抗太多写操作，但是我可以通过架构来弥补，说个我现有用的数据库平台容量：主从数据总量在几百T以上，每天十多亿 pv的动态页面，还有几个大项目是通过数据接口方式调用未算进pv总数，(其中包括一个大项目因为初期memcached没部署,导致单台数据库每天处理 9千万的查询)。而我的整体数据库服务器平均负载都在0.5-1左右。