Mysql的优化设置
时间:2015-02-25 01:58 来源:linux.it.net.cn 作者:IT
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
(责任编辑：IT)
| 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 (责任编辑：IT) |