MySQL优化

编译Mysql时configure: error: No curses/termcap library found 的错误解决方法

在网上找了很多的解决办法,发现以下这个正解,放上以备查用

安装ncurses

cd /usr/local/src/mysql

cat Makefile | grep  CONF_COMMAND

编译安装MySQL

# tar xzvf mysql-5.0.27.tar.gz

# cd mysql-5.0.27

# ./configure \

“–prefix=/usr/local/mysql” \

“–localstatedir=/var/lib/mysql” \

“–with-comment=Source” \

“–with-server-suffix=-Comsenz” \

“–with-mysqld-user=mysql” \

“–without-debug” \

“–with-big-tables” \

“–with-charset=” \ #这个后边需要指定你所需要的字符集参数(gbk,utf8……)

“–with-collation= ” \ #字符集校正码(gbk_chinese_ci，……)

“–with-extra-charsets=all” \

“–with-pthread” \

“–enable-static” \

“–enable-thread-safe-client” \

“–with-client-ldflags=-all-static” \

“–with-mysqld-ldflags=-all-static” \

“–enable-assembler” \

“–without-isam” \

“–without-innodb” \

“–without-ndb-debug”\

“enable-profiling”

–enable-assembler \使用一些字符函数的汇编版本

with-extra-charsets=all 对多语言的支持

./configure –prefix=/usr/local/mysql5 –without-debug –with-big-tables –with-charset=gbk –with-extra-charsets=all  –with-pthread –enable-static –enable-thread-safe-client –with-client-ldflags=-all-static –with-mysqld-ldflags=-all-static –enable-assembler –without-innodb –without-ndb-debug

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

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 458624 K

MySQL如何计算打开文件数

open_files_limit加入 my.cnf

open_files_limit = n

open_files_limit 比内核最大限制数ulimit -n还大，因此以 open_files_limit 为准

设置 mysql 打开文件限制方法：把 set-variable=open_files_limit=10240 加到配置文件中。

[client]

port            = 3306

socket          = /tmp/mysql.sock

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

#关闭不需要的表类型

skip-bdb

skip-innodb

skip-locking

back_log=500

local-infile = 0

interactive_timeout=10

#open_files_limit = 3000

skip-locking

# 避免MySQL的外部锁定，减少出错几率增强稳定性

max_tmp_tables

客户端可以同时打开的临时表的最大数。

max_connect_errors = 30000

如果中断的与主机的连接超过该数目，该主机则阻塞后面的连接。你可以用 FLUSH HOSTS语句解锁锁定的主机。

max_connections = 1000

允许的并行客户端连接数目。

#禁止MySQL中用”LOAD DATA LOCAL INFILE”命令。这个命令会利用MySQL把本地文件读到数据库中，然后用户就可以非法获取敏感信息了。网络上流传的一些攻击方法中就有用它的，它也是很多新发现的SQL Injection攻击利用的手段！

local-infile = 0

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

wait_timeout= 10

#设置超时时间,能避免长连接

有时候，如果负载过重，连接会挂起，并且会占用连接表空间。

如果有多个交互用户或使用了到数据库的持久连接，那么将这个值设低一点并不可取！

指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。

服务器在关闭它之前在一个连接上等待行动的秒数。也可见interactive_timeout。

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

connect_timeout=3

####################################################################

connect_timeout 3

<=MYSQLD用來判斷此連線是否有效的時間，建議預設如果網路緩慢可以調高

###################################################################

max_connections=3000(内存1G)    10000（内存2G）

long_query_time=1

key_buffer = 256M(内存1G)      512M(内存2G)

max_allowed_packet = 2M

table_cache = 512(内存1G)      1024内存2G）

sort_buffer_size = 2M(内存1G)  32（内存2G）

read_buffer_size = 2M(内存1G) 32（内存2G）

join_buffer_size = 2M(内存1G)  32（内存2G）

myisam_sort_buffer_size = 64M(内存1G)  128（内存2G）

thread_cache = 8(内存1G)  64（内存2G）

query_cache_limit=1M(内存1G)  4（内存2G）

query_cache_size=32M(内存1G)  64（内存2G）

query_cache_type=1

# Try number of CPU’s*2 for thread_concurrency

thread_concurrency = 4

#skip-networking

# Replication Master Server (default)

# binary logging is required for replication

#log-bin(关闭二进制)

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id       = 1

[mysqldump]

quick

max_allowed_packet = 16M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

[isamchk]

key_buffer = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

[myisamchk]

key_buffer = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

query_cache   table_cache   key_buffer_size

########################################################

进入MySQL，用命令：show variables;

show variables like “max%”;

查看数据库最大可连接数的变量值：max_connections

但实际MySQL服务器允许的最大连接数16384;

添加了最大允许连接数，对系统消耗增加不大。

查看threads_connected

进入MySQL，用命令：show status;

show status like “thread%”;

查看当前活动的连接线程变量值：threads_connected

如果 threads_connected == max_connections 时，

数据库系统就不能提供更多的连接数了，这时，如果程序还想新建连接线程，数据库系统就会拒绝，如果程序没做太多的错误处理，就会出现类似强坛的报错信息。

该错误的简便的检查办法是，在刷新强坛页面时，不断监视threads_connected的变化。如果max_connections足够大，而 threads_connected值不断增加以至达到max_connections，那么，就应该检查程序了

此处重要的值是 Threads_created，每次 mysqld 需要创建一个新线程时，这个值都会增加。

如果这个数字在连续执行 SHOW STATUS 命令时快速增加，就应该尝试增大线程缓存。

例如，可以在 my.cnf 中使用 thread_cache_size  = 512 来实现此目的

thread_cache_size线程缓存大小

Threads_created太大,就要增加my.cnf中thread_cache_size的值.可以用Threads_created/Connections计算cache命中率

Threads_created           942

Connections               10841380

从上面的对比中.如何多的connections中才942个Threads_created.建议减小thread_cache_size值

########################################################

1.key_buffer_size

key_buffer_size只对MyISAM表起作用

key_buffer_size指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。一般我们设为16M

show status like ‘key_read%’;   ———>key_buffer

key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好

show status like ‘Qcache%’;————>query_cache_size

Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况,同时Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，此时需要增加缓冲大小

Qcache_hits的值不大，则表明你的查询重复率很低，这种情况下使用查询缓冲反而会影响效率，那么可以考虑不用查询缓冲。

Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多query_cache_type指定是否使用查询缓冲

得到如下状态值:

Qcache che指定表高速缓存的大小。

得到如下状态值:

Qcache queries in cache 12737 表明目前缓存的条数

Qcache inserts 20649006

Qcache hits 79060095 　看来重复查询率还挺高的

Qcache lowmem prunes 617913　有这么多次出现缓存过低的情况

Qcache not cached 189896

Qcache free memory 18573912　　目前剩余缓存空间

Qcache free blocks 5328 这个数字似乎有点大　碎片不少

Qcache total blocks 30953

SHOW STATUS LIKE ‘Open%tables’;———–>table_cache

。每当MySQL访问一个表时，如果在表缓冲区中还有空间，该表就被打开并放入其中，这样可以更快地访问表内容。通过检查峰值时间的状态值Open_tables和Opened_tables，可以决定是否需要增加table_cache的值。如果你发现open_tables等于table_cache，并且opened_tables在不断增长，那么你就需要增加table_cache的值了

如果设置得太高，可能会造成文件描述符不足，从而造成性能不稳定或者连接失败。

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

笔者设置table_cache = 256

得到以下状态:

Open tables 256

Opened tables 9046

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

如果运行了6个小时就出现上述值 那就要考虑增大table_cache

SHOW STATUS LIKE “com_select”;

SHOW STATUS LIKE “handler_read_rnd_next”;

Handler_read_rnd_next / Com_select 得出了表扫描比率

在本例中是 521:1。如果该值超过 4000，就应该查看 read_buffer_size，例如 read_buffer_size = 4M。

如果这个数字超过了 8M，就应该与开发人员讨论一下对这些查询进行调优了！

SHOW STATUS LIKE ‘created_tmp%’;

备注：

定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用show status like ‘key_reads’获得）。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

#要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。

back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。默认数值是50

#一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。缺省地，该值太小必能捕捉大的(可能错误)包。如果你正在使用大的BLOB列，你必须增加该值。它应该象你想要使用的最大BLOB的那么大max_allowed_packet = 4M

max_allowed_packet 客户机通信所使用的缓冲区大小的最大值。如果有客户机要发送大量的BLOB 或TEXT 的值，该服务器变量值可能需要增大。

#允许的同时客户的数量。增加该值增加 mysqld要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到链接过多，请联系空间商错误。 默认数值是100

max_connections=1024

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

#每个线程排序所需的缓冲sort_buffer_size = 4M

#当一个查询不断地扫描某一个表，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果你认为连续扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。read_buffer_size = 4M

#加速排序操作后的读数据，提高读分类行的速度。如果正对远远大于可用内存的表执行GROUP BY或ORDER BY操作，应增加read_rnd_buffer_size的值以加速排序操作后面的行读取。仍然不明白这个选项的用处……read_rnd_buffer_size = 8M

#用于REPAIR TABLE。不明白这个选项的用处，百度上找到的设置方向也是五花八门，有128M、64M、32M等，折中选一个myisam_sort_buffer_size = 64M

#可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用thread_cache_size = 128

#查询结果缓存。第一次执行某条SELECT语句的时候，服务器记住该查询的文本内容和它返回的结果。服务器下一次碰到这个语句的时候，它不会再次执行该语句。作为代替，它直接从查询缓存中的得到结果并把结果返回给客户端。query_cache_size = 32M

#最大并发线程数,cpu数量*2  thread_concurrency = 2

#设置超时时间,能避免长连接wait_timeout = 120

#关闭不需要的表类型,如果你需要,就不要加上这个

skip-innodb

skip-bdb

#设定缓存的连接数,节省连接时的开销

back_log  = 64

#禁用文件系统外部锁

external-locking  = 0

#禁用BDB,如果你确实不需要的话,innodb也是如此

skip-bdb

#索引缓冲,如果是专用的数据库服务器,可以设置高达服务器内存的一半,如果不是专用的,还是设置得低一点

key_buffer  = 512M

#缓存数据表数量,如果内存较大,可以设置稍微高一点,否则还是设置低一点

#设置这个参数可以参见系统状态中的 open_tables(表示当前打开的数据表总数) 和 opened_tables(表示所有打开的数据表总数)

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

#禁用dns解析,如果你的授权信息中采用dns授权方式了,就不能启用该选项

skip-name-resolve

skip-name-resolve

主机名不被解析。所有在授权表的Host的列值必须是IP数字或localhost。

#记录慢查询和没有使用索引的查询,便于帮助分析问题所在

long_query_times指定慢查询的阈值，缺省是10秒

long_query_time    = 1

log-slow-queries  = /usr/local/mysql/data/slow.log

log-queries-not-using-indexes

(1)、back_log：

要求 MySQL 能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。

back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。 试图设定back_log高于你的操作系统的限制将是无效的。

当你观察你的主机进程列表，发现大量 264084 | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待连接进程时，就要加大 back_log 的值了。默认数值是50，我把它改为500。

(2)、interactive_timeout：

服务器在关闭它前在一个交互连接上等待行动的秒数。一个交互的客户被定义为对 mysql_real_connect()使用 CLIENT_INTERACTIVE 选项的客户。 默认数值是28800，我把它改为7200。

(3)、key_buffer_size：

索引块是缓冲的并且被所有的线程共享。key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，到你能负担得起那样多。如果你使它太大，系统将开始换页并且真的变慢了。默认数值是8388600(8M)，我的MySQL主机有2GB内存，所以我把它改为 402649088(400MB)。

(4)、max_connections：

允许的同时客户的数量。增加该值增加 mysqld 要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 Too many connections 错误。 默认数值是100，我把它改为1024 。

(5)、record_buffer：

每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。默认数值是131072(128K)，我把它改为16773120 (16M)

(6)、sort_buffer：

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。默认数值是2097144(2M)，我把它改为 16777208 (16M)。

(7)、table_cache：

为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量。MySQL对每个唯一打开的表需要2个文件描述符。默认数值是64，我把它改为512。

(8)、thread_cache_size：

可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。我把它设置为 80。

(10)、wait_timeout：

服务器在关闭它之前在一个连接上等待行动的秒数。 默认数值是28800，我把它改为7200。

################################################################################################

my-medium.cnf这个配制文件就能满足我们的大多需要

du -h–max-depth=1

1. 修复并优化所有数据库:

本地:

# mysqlcheck -A -r -o -p

远程:

# mysqlcheck -A -r -o -p -h服务器地址

2. 修复并优化指定的数据库:

本地:

# mysqlcheck -u用户名 -p密码 -r -o 库名

远程:

# mysqlcheck –u用户名 -p密码 -h服务器地址 -r -o 库名

有的时候因为掉电或者其他原因导致数据库损坏,我们可以使用mysql自带的mysqlcheck命令来快速修复所有的数据库或者特定的数据库;例如

检查优化并修复所有的数据库用:

# mysqlcheck -A -o -r -p

Enter password:

database1 OK

database2 OK

———-

修复指定的数据库用

# mysqlcheck -A -o -r Database_NAME -p

即可

另外如果只是对某个表进行修复可以用：myisamchk或isamchk

其中myisamchk适用于MYISAM类型的数据表，而isamchk适用于ISAM类型的数据表。这两条命令的主要参数相同，一般新的系统都使用MYISAM作为缺省的数据表类型，这里以myisamchk为例子进行说明。当发现某个数据表出现问题时可以使用：

myisamchk tablename.MYI

进行检测，如果需要修复的话，可以使用：

myisamchk -of tablename.MYI

关于myisamchk的详细参数说明，可以参见它的使用帮助。需要注意的时在进行修改时必须确保MySQL服务器没有访问这个数据表，保险的情况下是最好在进行检测时把MySQL服务器Shutdown掉。

###############################################################################################

清理mysql的日志文件

发现var/db/mysql目录下有这么多

mysql-bin.000001

…

mysql-bin.000023

mysql-bin.index

而且比较大

数据库的操作日志

mysql> reset master;

可以清理这些文件。

如果做了replication，清理之前要确认slave端已经同步了所有log

my.cnf中去掉log-bin就可不让生成这些日志文件了。

#################################################################################################

win2003

优化实例my.ini

内存1G的优化：

[mysqld]

basedir=D:/AndyServer/MySQL5.0

datadir=D:/AndyServer/MySQL5.0/data

port=3306

key_buffer=256M

max_allowed_packet=2M

table_cache=512

thread_cache=32

join_buffer_size=16M

sort_buffer=16M

record_buffer=16M

max_connections=500

wait_timeout=120

interactive_timeout=120

max_connect_errors=30000

long_query_time=1

max_heap_table_size=128M

tmp_table_size=64M

thread_concurrency=8

myisam_sort_buffer_size=64M

内存2G的优化:

[mysqld]

basedir=D:/AndyServer/MySQL5.0

datadir=D:/AndyServer/MySQL5.0/data

port=3306

key_buffer=384M

max_allowed_packet=4M

table_cache=1024

thread_cache=64

join_buffer_size=32M

sort_buffer=32M

record_buffer=32M

max_connections=500

wait_timeout=120

interactive_timeout=120

max_connect_errors=30000

long_query_time=1

max_heap_table_size=256M

tmp_table_size=128M

thread_concurrency=8

myisam_sort_buffer_size=128M

skip-bdb

back_log=500

skip-locking

skip-innodb

skip-name-resolve

query_cache_limit=1M

query_cache_size=64M

query_cache_type=1

max_allowed_packet = 2M

修改了mysql系统数据库中user表的host字段为ip地址

注意win服务器加了这个选项 数据库的连接必须用ip地址了

另外还必须到C:\WINDOWS\system32\drivers\etc\hosts

加入 ip  localhost才能

开启慢查询

long_query_time = 2

log-slow-queries = D:\Program Files\MySQL\mysqlslowquery.log

分析：连接数超过了mysql设置的值 max_connections 和wait_timeout 都有关系。wait_timeout的值越大，连接的空闲等待就越长，这样就会造成当前连接数越大。

解决：修改my.ini的max_connections 和wait_timeout 前者调大后者调小一些。

########################################################

故障处理：

MySQL 的max_allowed_packet保护导致MySQL Server 不断的启停

可能请求的连接包过大，检查配置文件，其中并没有max_allowed_packet声明。在配置文件中，设置max_allowed_packet=2M后，重新启动MySQL，问题解决！

导出SQL的时候发生了错误,提示Table XXX is marked as crashed and should be repaired when using LOCK TABLES,应该是数据表结构或内容损坏,使用命令myisamchk修复数据库的MYI文件即可

myisamchk -c -r *.MYI

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

Mysql的安全设置

打开/etc/my.cnf文件，修改以下设置，如果没有，可手动添加。

#取消文件系统的外部锁

skip-locking

#不进行域名反解析,注意由此带来的权限/授权问题

skip-name-resolve

这个参数可以解决，远程连接MySQL速度慢的解决办法

修改了mysql系统数据库中user表的host字段就

#禁止MySQL中用“LOAD DATA LOCAL INFILE”命令。这个命令会利用MySQL把本地文件读到数据库中，然后用户就可以非法获取敏感信息了。网络上流传的一些攻击方法中就有用它的，它也是很多新发现的SQL Injection攻击利用的手段！

local-infile = 0

#关闭远程连接，即3306端口。这是MySQL的默认监听端口。由于此处MySQL只服务于本地脚本，所以不需要远程连接。尽管MySQL内建的安全机制很严格，但监听一个TCP端口仍然是危险的行为，因为如果MySQL程序本身有问题，那么未授权的访问完全可以绕过MySQL的内建安全机制。（你必须确定，你是否真的不需要远程连接mysql）

skip-networking

修改完my.cnf后，还需要对mysql的用户名、帐号、及默认数据库进行调整

首先先登录mysql，在终端窗口输入  /home/mysql/bin/mysql -u root -p

然后会提示输入密码，输入正确密码后，会出现mysql>提示符。

输入以下命令：

mysql>use mysql;

mysql>update user set user=”centos” where user=”root”;      (将mysql的root用户名修改成centos，防止root的密码被暴力破解)

mysql>select Host,User,Password,Select_priv,Grant_priv from user;

mysql>delete from user where user=”;                  （删除user用户）

mysql>delete from user where password=”;              （删除user用户）

mysql>delete from user where host=”;                    （删除user用户）

mysql>drop database test;            (删除默认的test数据库)

mysql>flush privileges;              （刷新mysql的缓存，让以上设置立即生效）

mysql>quit;

为了使以上优化和安全设置生效，请重启Mysql服务或Linux。

chmod 600 /etc/my.cnf

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

Windows用户请注意： FAT和VFAT (FAT32)不适合MySQL的生产使用。应使用NTFS

在默认情况下，MySQL创建的MyISAM表允许的最大尺寸为4GB。你可以使用SHOW TABLE STATUS语句或myisamchk -dv tbl_name检查表的最大尺寸。

个人建议：MySQL的主要瓶颈在PORT的连接数上，因此，将表结构优化好以后，相应单个MySQL服务的CPU占用仍然在10％以上，就要考虑将服务拆分到多个PORT上运行了

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

方法：

用MySQL的status查看MySQL服务的运行统计和show processlist来查看当前服务中正在运行的SQL，如果某个SQL经常出现在PROCESS LIST中，一．有可能被查询的此时非常多，二里面有影响查询的字段没有索引，三．返回的结果数过多数据库正在排序（SORTING）；所以做一个脚本：比如每2秒运行以下show processlist;把结果输出到文件中，看到底是什么查询在吃CPU。

十四、重要的MySQL启动选项

back_log 如果需要大量新连接，修改它。

thread_cache_size 如果需要大量新连接，修改它。

key_buffer_size 索引页池，可以设成很大。

bdb_cache_size BDB表使用的记录和键吗高速缓存。

table_cache 如果有很多的表和并发连接，修改它。

delay_key_write 如果需要缓存所有键码写入，设置它。

log_slow_queries 找出需花大量时间的查询。

max_heap_table_size 用于GROUP BY

sort_buffer 用于ORDER BY和GROUP BY

myisam_sort_buffer_size 用于REPAIR TABLE

join_buffer_size 在进行无键吗的联结时使用。

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

Mysql的优化设置

打开/etc/my.cnf文件，修改以下设置，如果没有，可手动添加。调整设置时，请量力而行，这与你的服务器的配置有关，特别是内存大小。以下设置比较适合于1G内存的服务器，但并不绝对。

#指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads，可以知道key_buffer_size设置是否合理。比例key_reads / key_read_requests应该尽可能的低，至少是1:100，1:1000更好（上述状态值可以使用show status like ’key_reads’获得）。key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表，但是内部的临时磁盘表是MyISAM表，也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。

key_buffer = 384M

#要求MySQL能有的连接数量。当主要MySQL线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间(尽管很短)检查连接并且启动一个新线程。back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，你需要增加它，换句话说，这值对到来的TCP/IP连接的侦听队列的大小。你的操作系统在这个队列大小上有它自己的限制。试图设定back_log高于你的操作系统的限制将是无效的。默认数值是50

back_log = 200

#一个包的最大尺寸。消息缓冲区被初始化为net_buffer_length字节，但是可在需要时增加到max_allowed_packet个字节。缺省地，该值太小必能捕捉大的(可能错误)包。如果你正在使用大的BLOB列，你必须增加该值。它应该象你想要使用的最大BLOB的那么大。

max_allowed_packet = 4M

#允许的同时客户的数量。增加该值增加 mysqld要求的文件描述符的数量。这个数字应该增加，否则，你将经常看到 链接过多，请联系空间商 错误。 默认数值是100

max_connections = 1024

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

table_cache = 512

#每个线程排序所需的缓冲

sort_buffer_size = 4M

#当一个查询不断地扫描某一个表，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果你认为连续扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

read_buffer_size = 4M

#加速排序操作后的读数据，提高读分类行的速度。如果正对远远大于可用内存的表执行GROUP BY或ORDER BY操作，应增加read_rnd_buffer_size的值以加速排序操作后面的行读取。仍然不明白这个选项的用处……

read_rnd_buffer_size = 8M

#用于REPAIR TABLE。不明白这个选项的用处，百度上找到的设置方向也是五花八门，有128M、64M、32M等，折中选一个。

myisam_sort_buffer_size = 64M

#可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。如果有很多新的线程，为了提高性能可以这个变量值。通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。

thread_cache_size = 128

#查询结果缓存。第一次执行某条SELECT语句的时候，服务器记住该查询的文本内容和它返回的结果。服务器下一次碰到这个语句的时候，它不会再次执行该语句。作为代替，它直接从查询缓存中的得到结果并把结果返回给客户端。

query_cache_size = 32M

#最大并发线程数,cpu数量*2

thread_concurrency = 2

#设置超时时间,能避免长连接

wait_timeout = 120

#关闭不需要的表类型,如果你需要,就不要加上这个

skip-innodb

skip-bdb

table_cache — 打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁，所以通常要加大缓存数量，使得足以最大限度 地缓存打开的表。它需要用到操作系统的资源以及内存，对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话，那么设置为 1024 也许比较合适（每个线程都需要打开表），如果连接数比较大那么就加大它的值。我曾经见过设置为 100,000 的情况。

thread_cache — 线程的创建和销毁的开销可能很大，因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created 的值也比较大，那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。

query_cache — 如果你的应用程序有大量读，而且没有应用程序级别的缓存，那么这很有用。不要把它设置太大了，因为想要维护它也需要不少开销，这会导致MySQL变慢。通 常设置为 32-512Mb。设置完之后最好是跟踪一段时间，查看是否运行良好。在一定的负载压力下，如果缓存命中率太低了，就启用它。

sort_buffer_size –如果你只有一些简单的查询，那么就无需增加它的值了，尽管你有 64GB 的内存。搞不好也许会降低性能

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

维护

启动MySQL，注意使用用户为MySQL：

#/usr/local/mysq/bin/mysqld_safe –user=mysql &

如果可能，偶尔运行一下OPTIMIZE table，这对大量更新的变长行非常重要。

偶尔用myisamchk -a更新一下表中的键码分布统计。记住在做之前关掉MySQL。

如果有碎片文件，可能值得将所有文件复制到另一个磁盘上，清除原来的磁盘并拷回文件。

如果遇到问题，用myisamchk或CHECK table检查表。

用mysqladmin -i10 processlist extended-status监控MySQL的状态。

用MySQL GUI客户程序，你可以在不同的窗口内监控进程列表和状态。

使用mysqladmin debug获得有关锁定和性能的信息

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

mysqld程序–内存管理、优化、查询缓存区

bulk_insert_buffer_size = n 为一次插入多条新记录的INSERT命令分配的缓存区长度(默认设置是8M)。

key_buffer_size = n 用来存放索引区块的RMA值(默认设置是8M)。

join_buffer_size = n 在参加JOIN操作的数据列没有索引时为JOIN操作分配的缓存区长度(默认设置是128K)。

max_heap_table_size = n HEAP数据表的最大长度(默认设置是16M); 超过这个长度的HEAP数据表将被存入一个临时文件而不是驻留在内存里。

max_connections = n MySQL服务器同时处理的数据库连接的最大数量(默认设置是100)。

query_cache_limit = n 允许临时存放在查询缓存区里的查询结果的最大长度(默认设置是1M)。

query_cache_size = n 查询缓存区的最大长度(默认设置是0，不开辟查询缓存区)。

query_cache_type = 0/1/2 查询缓存区的工作模式:0, 禁用查询缓存区; 1，启用查询缓存区(默认设置); 2，”按需分配”模式，只响应SELECT SQL_CACHE命令。

read_buffer_size = n 为从数据表顺序读取数据的读操作保留的缓存区的长度(默认设置是128KB); 这个选项的设置值在必要时可以用SQL命令SET SESSION read_buffer_size = n命令加以改变。

read_rnd_buffer_size = n 类似于read_buffer_size选项，但针对的是按某种特定顺序(比如使用了ORDER BY子句的查询)输出的查询结果(默认设置是256K)。

sore_buffer = n 为排序操作分配的缓存区的长度(默认设置是2M); 如果这个缓存区太小，则必须创建一个临时文件来进行排序。

table_cache = n 同时打开的数据表的数量(默认设置是64)。

tmp_table_size = n 临时HEAP数据表的最大长度(默认设置是32M); 超过这个长度的临时数据表将被转换为MyISAM数据表并存入一个临时文件

sort_buffer_size = 6M

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

read_buffer_size = 4M

读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享！

join_buffer_size = 8M

联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享

#可以复用的保存在中的线程的数量。如果有，新的线程从缓存中取得，当断开连接的时候如果有空间，客户的线置在缓存中。

如果有很多新的线程，为了提高性能可以这个变量值。

通过比较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。

thread_cache_size = 128

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

查看mysql线程

有时mysql会占用过多cpu,解决办法之一是查看mysql线程的运行情况

mysqladmin proc stat

mysqladmin proc stat -i 1 (间隔1s)

mysqladmin kill pid (kill掉死锁的线程的pid)

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

mysql优化工具使用

wget http://hackmysql.com/scripts/mysqlreport-3.5.tgz

tarzxvf mysqlreport-3.5.tgz

cd mysqlreport-3.5

./mysqlreport –user root –password phpwind.net –socket=/tmp/mysql.sock

即可查看 mysql运行的参数

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

mysqlsla, hackmysql.com推出的一款日志分析工具

mysqlsla -lt slow /tmp/slow-log

整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等.

格式说明如下:

总查询次数 (queries total), 去重后的sql数量 (unique)

输出报表的内容排序(sorted by)

最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数.

Count, sql的执行次数及占总的slow log数量的百分比.

Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.

95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.

Lock Time, 等待锁的时间.

95% of Lock , 95%的慢sql等待锁时间.

Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量.

Rows examined, 扫描的行数量.

Database, 属于哪个数据库

Users, 哪个用户,IP, 占到所有用户执行的sql百分比

Query abstract, 抽象后的sql语句

Query sample, sql语句

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

增加系统和MySQL服务器的打开文件数量。(在safe_mysqld脚本中加入ulimit -n #)。

增加系统的进程和线程数量。

如果你有相对较少的大表，告诉文件系统不要将文件打碎在不同的磁道上(Solaris)。

使用支持大文件的文件系统(Solaris)。

选择使用哪种文件系统。在Linux上的Reiserfs对于打开、读写都非常快。文件检查只需几秒种

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

如何知晓MySQL解决一条查询

运行项列命令并试图弄明白其输出：

SHOW VARIABLES;

SHOW COLUMNS FROM …G

EXPLAIN SELECT …G

FLUSH STATUS;

SELECT …;

SHOW STATUS;

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

Mysql 的优化经验

从数据库结构做起

字段类型的定义时遵循以下规则:

选用字段长度最小

优先使用定长型

尽可能的定义 “NOT NULL”

数值型字段中避免使用 “ZEROFILL”

如果要储存的数据为字符串, 且可能值已知且有限, 优先使用 enum 或 set

索引的优化至关重要(以下如果没有特殊说明, 均指查询密集的情况)

被索引的字段的长度越小, 该索引的效率越高

被索引的字段中, 值的重复越少, 该索引的效率越高

查询语句中, 如果使用了 “group” 子句, 根据其中字段出现的先后顺序建立多字段索引

查询语句中, 如果使用了 “distinct”, 根据其中字段出现的先后顺序建立多字段索引

“where” 子句中, 出现对同一表中多个不同字段的 “and” 条件时, 按照字段出现的先后顺序建立多字段索引

“where” 子句中, 出现对同一表中多个不同字段的 “or” 条件时, 对重复值最少的字段建立单字段索引

进行 “内/外连接” 查询时, 对 “连接字段” 建立索引

对 “主键” 的 “unique” 索引 毫无意义, 不要使用

被索引字段尽可能的使用 “NOT NULL” 属性

对写入密集型表, 尽量减少索引, 尤其是 “多字段索引” 和 “unique” 索引

查询语句的优化

多多利用 “explain” 查询索引使用情况, 以便找出最佳的查询语句写法和索引设置方案

慎用 “select *”, 查询时只选出必须字段

查询使用索引时, 所遍历的索引条数越少, 索引字段长度越小, 查询效率越高 (可使用 “explain” 查询索引使用情况)

避免使用 mysql 函数对查询结果进行处理, 将这些处理交给客户端程序负责

使用 “limit” 时候, 尽量使 “limit” 出的部分位于整个结果集的前部, 这样的查询速度更快, 系统资源开销更低

在 “where” 子句中使用多个字段的 “and” 条件时, 各个字段出现的先后顺序要与多字段索引中的顺序相符

在 “where” 子句 中使用 “like” 时, 只有当通配符不出现在条件的最左端时才会使用索引

在 mysql 4.1 以上版本中, 避免使用子查询, 尽量使用 “内/外连接” 实现此功能

减少函数的使用, 如果可能的话, 尽量用单纯的表达式来代替

避免在 “where” 子句中, 对不同字段进行 “or” 条件查询, 将其拆分成多个单一字段的查询语句效率更高

`

附: Mysql 字段长度说明

TINYINT 1 字节                SMALLINT 2 字节

MEDIUMINT 3 字节              INTEGER: 4 字节

BIGINT 8 字节                 DOUBLE 8 字节

FLOAT X <=24: 4 字节          DECIMAL M<D: D+2 字节

(X)   X > 24: 8 字节          (M,D)   M>=D: M 字节

DATE 3 字节                   DATETIME 8 字节

TIMESTAMP 4 字节              TIME 4 字节

YEAR 1 字节

CHAR(M) M 字节                VARCHAR(M) 值长度 + 1 字节

TINYBLOB 值长度 + 1 字节      TINYTEXT 值长度 + 1 字节

BLOB 值长度 + 2 字节          TEXT 值长度 + 2 字节

MEDIUMBLOB 值长度 + 3 字节    MEDIUMTEXT 值长度 + 3 字节

LONGBLOB 值长度 + 4 字节      LONGTEXT 值长度 + 4 字节

ENUM1或2字节取决于枚举值数 SET     1，2，3，4, 8取决于成员数量

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

max_connections指最大的并发连接数，你的数据库一天有80万的访问数，并不一定代表每秒会有超过2038个用户在同时访问你的数据库，虽然2038个连接不一定全部可用，但是就算每秒有1000个并发连接数，你一小时的访问量：1000*3600=360,0000，所以你要修改的话我希望你慎重考虑，因为我不知道修改后会不会有什么结果？mysql可能会不稳定或者其他方面的影响。我不知道我这样说对不对，有错请指正。

如果你一定要修改的话，可以按如下步骤去修改：

1.打开mysql的配置文件my.ini文件，在[mysqld]下加上如下两个变量

max_connections=10000（默认的为100）

open_files_limit=3000（默认的2048）可以通过命令：show   variables   like   %”limit%”;查看

2.保存后，重启mysql服务。

mysql>   show   variables   like   “%max%”;

–skip_bdb

禁用BDB存储引擎。这样可以节省内存，并可能加速某些操作。如果你需要BDB表则不要使用该选项。

–skip_concurrent_insert

关闭在同一时间在MyISAM表中选择和插入的能力。(只有你发现缺陷时才使用该选项）。

–skip_grant_tables

该选项使服务器不使用权限系统。该权限允许访问服务器的用户不受限制地访问所有数据库

–skip_external_locking

不要使用系统锁定。

–skip_host_cache

为了更快地在名称-IP之间进行解析，不要使用内部主机名缓存。相反，每次客户端连接时查询DNS服务器。

–skip_innodb

禁用InnoDB存储引擎。这样可以节省内存，并可能加速某些操作。

–skip_networking

不帧听TCP/IP连接。必须通过命名管道或共享内存(在Windows中)或Unix套接字文件(在Unix中)完成mysqld的相互操作。

对于只允许本地客户端的系统，大力推荐该选项。

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

1、看机器配置，指三大件：cpu、内存、硬盘

2、看mysql配置参数

3、查系mysql行状态，可以用mysqlreport工具来查看

4、查看mysql的慢查询

依次解决了以上问题之后，再来查找程序方面的问题

my.cnf缓存优化

在 my.cnf 中添加/修改以下选项:

#取消文件系统的外部锁

skip-locking

#不进行域名反解析,注意由此带来的权限/授权问题

skip-name-resolve

#索引缓存,根据内存大小而定,如果是独立的db服务器,可以设置高达80%的内存总量

key_buffer = 512M

#连接排队列表总数

back_log = 200

max_allowed_packet = 2M

#打开表缓存总数,可以避免频繁的打开数据表产生的开销

table_cache = 512

#每个线程排序所需的缓冲

sort_buffer_size = 4M

#每个线程读取索引所需的缓冲

read_buffer_size = 4M

#MyISAM表发生变化时重新排序所需的缓冲

myisam_sort_buffer_size = 64M

#缓存可重用的线程数

thread_cache = 128

#查询结果缓存

query_cache_size = 128M

#设置超时时间,能避免长连接

set-variable = wait_timeout=60

#最大并发线程数,cpu数量*2

thread_concurrency = 4

#记录慢查询,然后对慢查询一一优化

log-slow-queries = slow.log

long_query_time = 1

#关闭不需要的表类型,如果你需要,就不要加上这个

skip-innodb

skip-bdb

设置建议:

对于单台运行的WEB服务器,建议加上:

skip-locking

skip-name-resolve

skip-networking

在PHP链接数据库时使用”LOCALHOST”.这样MySQL 客户端库将覆盖之并尝试连接到本地套接字.(

我们可以从PHP.INI中

代码: ; Default socket name for local MySQL connects.  If empty, uses the built-in

; MySQL defaults.

mysql.default_socket = /tmp/mysql.sock看出 默认情况下 UNIX 将访问/tmp/mysql.sock)

以下是部分选项解释:

my.cnf默认是不存在的.你可以在/usr/local/share/mysql/下看到:

my-huge.cnf

my-innodb-heavy-4G.cnf

my-large.cnf

my-medium.cnf

my-small.cnf等文件.将其中合适你机器配置的文件拷贝到/etc/my.cnf或mysql data目录/my.cnf(/var/db/mysql)下或~/.my.cnf.文件内都有详细的说明

[mysqld]

port = 3306

serverid = 1

socket = /tmp/mysql.sock

skip-locking

# 避免MySQL的外部锁定，减少出错几率增强稳定性。

skip-name-resolve

禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！

back_log = 384

指定MySQL可能的连接数量。当MySQL主线程在很短的时间内接收到非常多的连接请求，该参数生效，主线程花费很短的时间检查连接并且启动一个新线程。

back_log 参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中。 如果系统在一个短时间内有很多连接，则需要增大该参数的值，该参数值指定到来的TCP/IP连接的侦听队列的大小。不同的操作系统在这个队列大小上有它自 己的限制。

试图设定back_log高于你的操作系统的限制将是无效的。默认值为50。对于Linux系统推荐设置为小于512的整数。

key_buffer_size = 256M

# key_buffer_size指定用于索引的缓冲区大小，增加它可得到更好的索引处理性能。

对于内存在4GB左右的服务器该参数可设置为256M或384M。

注意：该参数值设置的过大反而会是服务器整体效率降低！

max_allowed_packet = 4M

thread_stack = 256K

table_cache = 128K

sort_buffer_size = 6M

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

read_buffer_size = 4M

读查询操作所能使用的缓冲区大小。和sort_buffer_size一样，该参数对应的分配内存也是每连接独享！

join_buffer_size = 8M

联合查询操作所能使用的缓冲区大小，和sort_buffer_size一样，该参数对应的分配内存也是每连接独享！

myisam_sort_buffer_size = 64M

table_cache = 512

thread_cache_size = 64

query_cache_size = 64M

指定MySQL查询缓冲区的大小。可以通过在MySQL控制台执行以下命令观察：

代码: # > SHOW VARIABLES LIKE ‘%query_cache%’;

# > SHOW STATUS LIKE ‘Qcache%’;如果Qcache_lowmem_prunes的值非常大，则表明经常出现缓冲不够的情况；

如果Qcache_hits的值非常大，则表明查询缓冲使用非常频繁，如果该值较小反而会影响效率，那么可以考虑不用查询缓冲；Qcache_free_blocks，如果该值非常大，则表明缓冲区中碎片很多。

tmp_table_size = 256M

max_connections = 768

指定MySQL允许的最大连接进程数。如果在访问论坛时经常出现Too Many Connections的错误提 示，则需要增大该参数值。

max_connect_errors = 10000000

wait_timeout = 10

指定一个请求的最大连接时间，对于4GB左右内存的服务器可以设置为5-10。

thread_concurrency = 8

该参数取值为服务器逻辑CPU数量×2，在本例中，服务器有2颗物理CPU，而每颗物理CPU又支持H.T超线程，所以实际取值为4 × 2 ＝ 8

skip-networking

开启该选项可以彻底关闭MySQL的TCP/IP连接方式，如果WEB服务器是以远程连接的方式访问MySQL数据库服务器则不要开启该选项！否则将无法正常连接！

win服务器

2G内存,优质型的设置,标准型的优化

table_cache=2048 根据物理内存设置,物理内存越大,设置就越大.调到1024-4096最佳

innodb_additional_mem_pool_size=8M  默认为2M

innodb_flush_log_at_trx_commit=0 等到innodb_log_buffer_size列队满后再统一储存,默认为1

innodb_log_buffer_size=4M            默认为1M

innodb_thread_concurrency=8          你的服务器CPU有几个就设置为几,默认为8

key_buffer_size=256M                     默认为218         调到128最佳

tmp_table_size=64M                     默认为16M        调到64-256最挂

read_buffer_size=4M                      默认为64K

read_rnd_buffer_size=16M             默认为256K

sort_buffer_size=32M                     默认为256K

max_connections=1024                 默认为1210

thread_cache_size=120               默认为60

query_cache_size=64M         大于64M建议用默认

wait_timeout = 5

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

字符集(Character set)和排序方式(Collation)。

对于字符集的支持细化到四个层次: 服务器(server)，数据库(database)，数据表(table)和连接(connection)。

SHOW VARIABLES LIKE ‘character_set_%’;

SHOW VARIABLES LIKE ‘collation_%’;

mysqldump  -u root -p –default-character-set=utf8 数据库名 >路径+文件名

–default-character-set=utf8(解决乱码,可以用status来查看你当前数据库默认的是什么字符集来确定是不是要用utf8)

mysql -uroot -p < 路径+文件名

-opt

这只是一个快捷选项，等同于同时添加 -add-drop-tables -add-locking -create-option -disable-keys –extended-insert

-lock-tables -quick -set-charset 选项。

本选项能让 mysqldump 很快的导出数据，并且导出的数据能很快导回。该选项默认开启，但可以用 -skip-opt 禁用。

注意，如果运行 mysqldump 没有指定 -quick 或 -opt 选项，则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。

如何转换数据库字符集

两种方法，

第一种—-更改存储字符集

主要的思想就是把数据库的字符集有latin1改为gbk，big5,或者utf8; 以下操作必须拥有主机权限。假设当前操作的数据库名为：database

导出

首先需要把数据导为mysql4.0的格式，具体的命令如下：

mysqldump -uroot -p –default-character-set=latin1 –set-charset=gbk –skip-opt databse > d4.sql

–default-characte-set 以前数据库的字符集，这个一般情况下都是latin1的，

–set-charset 导出的数据的字符集，这个可以设置为gbk，utf8,或者big5

导入

首先使用下面语句新建一个GBK字符集的数据库（test）

CREATE DATABASE `d4` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

然后把刚才导出的数据导入到当前的数据库中就ok了。

mysql -uroot -p –default-character-set=gbk -f d4<d4.sql

通过以上的导出和导入就把数据库的字符集改为正确的存储方式了。

其中d4为新建库的名称，d4.sql为导出文件的名字

但是这种方法，发现数据库数据存储量无端变大30%，真是郁闷

降级的时候导出库可以用这个方法

mysqldump -uroot -p –default-character-set=latin1 set-charset=gbk –skip-opt databse  –compatible=mysql40 > d4.sql

这样导出的就是4.0的库勒

至于mysql版本的升级，

如果数据文件中有中文信息，那么将MySQL 4.0的数据文件，直接拷贝到MySQL 4.1中就是不可以的，即便在my.ini中设置了default-character-set为正确的字符集。虽然貌似没有问题，但MySQL 4.1的字符集有一处非常恼人的地方，以gbk为例，原本MySQL 4.0数据中varchar,char等长度都会变为原来的一半，这样存储中文容量不变，而英文的存储容量就少了一半。这是直接拷贝数据文件带来的最大问题。

所以，升级的根本，如果想使用“正确”的字符集，还是先用mysqldump导出成文件，然后导入。

针对MYISAM存储引擎

mysqldump -uroot -pxxxxxx –lock-all-tables test < test_backup.sql

针对INNODB存储引擎

mysqldump -uroot -p –skip-opt –single-transaction –add-drop-table –create-options –quick –extended-insert –set-charset –disable-keys test > test_backup.sql