mysql --help
/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf
備份和還原
backup
mysqldump -u root -p --databases test mysql > D:\backup.sql
restore
mysql -u root -p [dbname] < C:\backup.sql
啟動時指定配置檔案
mysqld [--defaults-file=/etc/my.cnf]
檢視當前引擎
show engines;
檢視配置引數
show variables like '%open%';
show status like 'open%';
配置table_open_cache
set global table_open_cache=10240;
檢視當前狀態及連線數
- mysql> status; show full processlist;
- Workbench中的Client Connections
- ...
執行SQL
mysql> \. file_name
建立使用者並授權
create user 'username'@'where' identified by 'aaa7B2249';
grant all on pcom.* to 'pcom'@'192.168.0.0/255.255.0.0' identified by 'aaa7B2249';
修改密碼
方法一:
(適用於管理員或者有全域性許可權的使用者重設其它使用者的密碼)
進入命令列模式
mysql -u root
-p
mysql>use mysql;
mysql> UPDATE user SET
password=PASSWORD("new password") WHERE user='username';
mysql> FLUSH
PRIVILEGES;
mysql> quit;
方法二:
mysql -u root -p
mysql>use
mysql;
mysql> SET PASSWORD FOR username=PASSWORD('new
password');
mysql> QUIT
方法三:
mysqladmin -u root "old
password" "new password"
注：new password請輸入你想要設定的密碼。
方法四：
mysql> use mysql;
mysql> update user set password=password('123') where user='root' and host='localhost';
mysql> flush privileges;
字符集設定
my.ini
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8
#日誌配置
log-error="C:/D/lamp/log/error.log"
general-log=1
general-log-file="C:/D/lamp/log/mysql.log"
long_query_time=3
slow_query_log=1
slow-query-log_file="C:/D/lamp/log/slowquery.log"
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
basedir = D:\mysql-5.6.32-winx64
datadir = D:\mysql-5.6.32-winx64\data
# port = .....
# server_id = .....
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
===================================================================================================
1 連線 Connections
經常會遇見”mysql: error 1040: too many connections”的情況，一種是訪問量確實很高，mysql伺服器抗不住，這個時候就要考慮增加從伺服器分散讀壓力，另外一種情況是mysql配置檔案中max_connections值過小：
mysql> show variables like ‘max_connections‘;
+-----------------+-------+
| variable_name | value |
+-----------------+-------+
| max_connections | 256 |
+-----------------+-------+
這臺mysql伺服器最大連線數是256，然後查詢一下伺服器響應的最大連線數：
mysql> show global status like ‘max_used_connections‘;
mysql伺服器過去的最大連線數是245，沒有達到伺服器連線數上限256，應該沒有出現1040錯誤，比較理想的設定是
max_used_connections / max_connections * 100% ≈ 85%
最大連線數占上限連線數的85％左右，如果發現比例在10%以下，mysql伺服器連線數上限設定的過高了。
2 執行緒 Thread
mysql> show global status like ‘thread%‘;
+-------------------+-------+
| variable_name | value |
+-------------------+-------+
| threads_cached | 46 |
| threads_connected | 2 |
| threads_created | 570 |
| threads_running | 1 |
+-------------------+-------+
如果我們在mysql伺服器配置檔案中設定了thread_cache_size，當客戶端斷開之後，伺服器處理此客戶的執行緒將會快取起來以響應下一個客戶而不是銷燬（前提是快取數未達上限）。
threads_created表示建立過的執行緒數，如果發現threads_created值過大的話，表明mysql伺服器一直在建立執行緒，這也是比較耗資源，可以適當增加配置檔案中thread_cache_size值，
查詢伺服器 thread_cache_size 配置：
mysql> show variables like ‘thread_cache_size‘;
+-------------------+-------+
| variable_name | value |
+-------------------+-------+
| thread_cache_size | 64 |
+-------------------+-------+
示例中的伺服器還是挺健康的。
3 快取 cache
3.1 檔案開啟數
mysql> show global status like ‘open_files‘;
+---------------+-------+
| variable_name | value |
+---------------+-------+
| open_files | 1410 |
+---------------+-------+
mysql> show variables like ‘open_files_limit‘;
+------------------+-------+
| variable_name | value |
+------------------+-------+
| open_files_limit | 4590 |
+------------------+-------+
比較合適的設定：open_files / open_files_limit * 100% <= 75％
3.2 資料表
3.2.1 開啟數 open_tables
mysql> show global status like ‘open%tables%‘;
+---------------+-------+
| variable_name | value |
+---------------+-------+
| open_tables | 919 |
| opened_tables | 1951 |
+---------------+-------+
open_tables: 開啟表的數量
opened_tables: 開啟過的表數量
如果 opened_tables 數量過大，說明配置中 table_cache(5.1.3之後這個值叫做table_open_cache)值可能太小，我們查詢一下伺服器table_cache值：
mysql> show variables like ‘table_cache‘;
+---------------+-------+
| variable_name | value |
+---------------+-------+
| table_cache | 2048 |
+---------------+-------+
比較合適的值為：
open_tables / opened_tables * 100% >= 85%
open_tables / table_cache * 100% <= 95%
3.2.2 臨時表 tmp_table
mysql> show global status like ‘created_tmp%‘;
+-------------------------+---------+
| variable_name | value |
+-------------------------+---------+
| created_tmp_disk_tables | 21197 |
| created_tmp_files | 58 |
| created_tmp_tables | 1771587 |
+-------------------------+---------+
每次建立臨時表，created_tmp_tables 增加，如果是在磁碟上建立臨時表，created_tmp_disk_tables也增加,created_tmp_files表示mysql服務建立的臨時檔案檔案數，比較理想的配置是：
created_tmp_disk_tables / created_tmp_tables * 100% <= 25%
比如上面的伺服器 created_tmp_disk_tables / created_tmp_tables * 100% ＝ 1.20%，應該相當好了。我們再看一下mysql伺服器對臨時表的配置：
mysql> show variables where variable_name in (‘tmp_table_size‘, ‘max_heap_table_size‘);
+---------------------+-----------+
| variable_name | value |
+---------------------+-----------+
| max_heap_table_size | 268435456 |
| tmp_table_size | 536870912 |
+---------------------+-----------+
只有 256mb 以下的臨時表才能全部放記憶體，超過的就會用到硬碟臨時表。
3.2.3 表鎖情況
mysql> show global status like ‘table_locks%‘;
+-----------------------+-----------+
| variable_name | value |
+-----------------------+-----------+
| table_locks_immediate | 490206328 |
| table_locks_waited | 2084912 |
+-----------------------+-----------+
table_locks_immediate 表示立即釋放表鎖數，
table_locks_waited 表示需要等待的表鎖數，
如果 table_locks_immediate / table_locks_waited > 5000，最好採用innodb引擎，因為innodb是行鎖而myisam是表鎖，對於高併發寫入的應用innodb效果會好些。
示例中的伺服器 table_locks_immediate / table_locks_waited ＝ 235，myisam就足夠了。
3.2.4 表掃描情況
mysql> show global status like ‘handler_read%‘;
+-----------------------+-------------+
| variable_name | value |
+-----------------------+-------------+
| handler_read_first | 5803750 |
| handler_read_key | 6049319850 |
| handler_read_next | 94440908210 |
| handler_read_prev | 34822001724 |
| handler_read_rnd | 405482605 |
| handler_read_rnd_next | 18912877839 |
+-----------------------+-------------+
各欄位解釋參見 http://hi.baidu.com/thinkinginlamp/blog/item/31690cd7c4bc5cdaa144df9c.html ，調出伺服器完成的查詢請求次數：
mysql> show global status like ‘com_select‘;
+---------------+-----------+
| variable_name | value |
+---------------+-----------+
| com_select | 222693559 |
+---------------+-----------+
計算表掃描率：
表掃描率 ＝ handler_read_rnd_next / com_select
如果表掃描率超過 4000，說明進行了太多表掃描，很有可能索引沒有建好，增加 read_buffer_size 值會有一些好處，但最好不要超過8mb。
3.3 key_buffer_size
key_buffer_size是對myisam表效能影響最大的一個引數，下面一臺以myisam為主要儲存引擎伺服器的配置：
mysql> show variables like ‘key_buffer_size‘;
+-----------------+------------+
| variable_name | value |
+-----------------+------------+
| key_buffer_size | 536870912 |
+-----------------+------------+
分配了 512mb 記憶體給 key_buffer_size ，我們再看一下 key_buffer_size 的使用情況：
mysql> show global status like ‘key_read%‘;
+------------------------+-------------+
| variable_name | value |
+------------------------+-------------+
| key_read_requests | 27813678764 |
| key_reads | 6798830 |
+------------------------+-------------+
一共有 27813678764個 索引讀取請求，有 6798830個 請求在記憶體中沒有找到直接從硬碟讀取索引，計算索引未命中快取的概率：
key_cache_miss_rate ＝ key_reads / key_read_requests * 100%
比如上面的資料，key_cache_miss_rate為0.0244%，4000個索引讀取請求才有一個直接讀硬碟，已經很bt了，key_cache_miss_rate在0.1%以下都很好（每1000個請求有一個直接讀硬碟），如果key_cache_miss_rate在0.01%以下的話，key_buffer_size分配的過多，可以適當減少。
【注意】key_read_buffer 預設值為 8M 。在專有的資料庫伺服器上，該值可設定為 RAM * 1/4
mysql伺服器還提供了key_blocks_*引數：
mysql> show global status like ‘key_blocks_u%‘;
+------------------------+-------------+
| variable_name | value |
+------------------------+-------------+
| key_blocks_unused | 0 |
| key_blocks_used | 413543 |
+------------------------+-------------+
key_blocks_unused 表示未使用的快取簇(blocks)數
key_blocks_used 表示曾經用到的最大的blocks數
比如這臺伺服器，所有的快取都用到了，要麼增加 key_buffer_size，要麼就是過渡索引了，把快取佔滿了。比較理想的設定：
key_blocks_used / (key_blocks_unused + key_blocks_used) * 100% ≈ 80%
3.4 排序使用情況 sort_buffer
mysql> show global status like ‘sort%‘;
+-------------------+------------+
| variable_name | value |
+-------------------+------------+
| sort_merge_passes | 29 |
| sort_range | 37432840 |
| sort_rows | 9178691532 |
| sort_scan | 1860569 |
+-------------------+------------+
sort_merge_passes 包括兩步。mysql 首先會嘗試在記憶體中做排序，使用的記憶體大小由系統變數 sort_buffer_size 決定，如果它的大小不夠把所有的記錄都讀到記憶體中，mysql 就會把每次在記憶體中排序的結果存到臨時檔案中，等 mysql 找到所有記錄之後，再把臨時檔案中的記錄做一次排序。這再次排序就會增加 sort_merge_passes。實際上，mysql 會用另一個臨時檔案來存再次排序的結果，所以通常會看到 sort_merge_passes 增加的數值是建臨時檔案數的兩倍。因為用到了臨時檔案，所以速度可能會比較慢，增加 sort_buffer_size 會減少 sort_merge_passes 和 建立臨時檔案的次數。但盲目的增加 sort_buffer_size 並不一定能提高速度，見 how fast can you sort data with mysql?（引自http://qroom.blogspot.com/2007/09/mysql-select-sort.html ，貌似被牆）
另外，增加read_rnd_buffer_size(3.2.3是record_rnd_buffer_size)的值對排序的操作也有一點的好處，參見：http://www.mysqlperformanceblog.com/2007/07/24/what-exactly-is-read_rnd_buffer_size/
3.5 查詢快取
mysql> show global status like ‘qcache%‘;
+-------------------------+-----------+
| variable_name | value |
+-------------------------+-----------+
| qcache_free_blocks | 22756 |
| qcache_free_memory | 76764704 |
| qcache_hits | 213028692 |
| qcache_inserts | 208894227 |
| qcache_lowmem_prunes | 4010916 |
| qcache_not_cached | 13385031 |
| qcache_queries_in_cache | 43560 |
| qcache_total_blocks | 111212 |
+-------------------------+-----------+
mysql 查詢快取變數解釋：
qcache_free_blocks：快取中相鄰記憶體塊的個數。數目大說明可能有碎片。flush query cache會對快取中的碎片進行整理，從而得到一個空閒塊。
qcache_free_memory：快取中的空閒記憶體。
qcache_hits：每次查詢在快取中命中時就增大
qcache_inserts：每次插入一個查詢時就增大。命中次數除以插入次數就是命中比率。
qcache_lowmem_prunes：快取出現記憶體不足並且必須要進行清理以便為更多查詢提供空間的次數。這個數字最好長時間來看；如果這個數字在不斷增長，就表示可能碎片非常嚴重，或者記憶體很少。（上面的 free_blocks和free_memory可以告訴您屬於哪種情況）
qcache_not_cached：不適合進行快取的查詢的數量，通常是由於這些查詢不是 select 語句或者用了now()之類的函式。
qcache_queries_in_cache：當前快取的查詢（和響應）的數量。
qcache_total_blocks：快取中塊的數量。
我們再查詢一下伺服器關於query_cache的配置：
mysql> show variables like ‘query_cache%‘;
+------------------------------+-----------+
| variable_name | value |
+------------------------------+-----------+
| query_cache_limit | 2097152 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 203423744 |
| query_cache_type | on |
| query_cache_wlock_invalidate | off |
+------------------------------+----------+
各欄位的解釋：
query_cache_limit：超過此大小的查詢將不快取
query_cache_min_res_unit：快取塊的最小大小
query_cache_size：查詢快取大小
query_cache_type：快取型別，決定快取什麼樣的查詢，示例中表示不快取 select sql_no_cache 查詢
query_cache_wlock_invalidate：當有其他客戶端正在對myisam表進行寫操作時，如果查詢在query cache中，是否返回cache結果還是等寫操作完成再讀表獲取結果。
query_cache_min_res_unit的配置是一柄”雙刃劍”，預設是4kb，設定值大對大資料查詢有好處，但如果你的查詢都是小資料查詢，就容易造成記憶體碎片和浪費。
查詢快取碎片率 = qcache_free_blocks / qcache_total_blocks * 100%
如果查詢快取碎片率超過20%，可以用flush query cache整理快取碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小資料量的話。
查詢快取利用率 = (query_cache_size - qcache_free_memory) / query_cache_size * 100%
查詢快取利用率在25%以下的話說明query_cache_size設定的過大，可適當減小；查詢快取利用率在80％以上而且qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多。
查詢快取命中率 = (qcache_hits - qcache_inserts) / qcache_hits * 100%
示例伺服器 查詢快取碎片率 ＝ 20.46％，查詢快取利用率 ＝ 62.26％，查詢快取命中率 ＝ 1.94％，命中率很差，可能寫操作比較頻繁吧，而且可能有些碎片。
4 其他
4.1 read_buffer_size
4.2 慢查詢
mysql> show variables like ‘%slow%‘;
+------------------+-------+
| variable_name | value |
+------------------+-------+
| log_slow_queries | on |
| slow_launch_time | 2 |
+------------------+-------+
mysql> show global status like ‘%slow%‘;
+---------------------+-------+
| variable_name | value |
+---------------------+-------+
| slow_launch_threads | 0 |
| slow_queries | 4148 |
+---------------------+-------+
配置中開啟了記錄慢查詢，執行時間超過2秒的即為慢查詢，系統顯示有4148個慢查詢，你可以分析慢查詢日誌，找出有問題的sql語句，慢查詢時間不宜設定過長，否則意義不大，最好在5秒以內，如果你需要微秒級別的慢查詢，可以考慮給mysql打補丁：http://www.percona.com/docs/wiki/release:start，記得找對應的版本。
開啟慢查詢日誌可能會對系統效能有一點點影響，如果你的mysql是主－從結構，可以考慮開啟其中一臺從伺服器的慢查詢日誌，這樣既可以監控慢查詢，對系統效能影響又小。