442
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 https://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
最後更新：2017-01-04 22:34:31