MySQL DBA常用手冊小結
1.mysql的遠端連線命令可以遠端匯入匯出資料
mysqldump --default-character-set=gb2312 -h255.255.000.00 -uroot -pxxxxxx mydatabase>d:\data.sql
將指定的資料庫匯出到一個外部SQL檔案中去！
還原命令：
mysql --default-character-set=gb2312 -h255.255.000.00 -uroot -pxxxxxx mydatabase<d:\data.sql
2. MYSQL三種升級方法
安裝辦法：二進位制包編譯安裝
第一種：適用於任何一種儲存引擎。
1. 下載並安裝好新版本的MySQL資料庫，並將其埠改為3307（避免和舊版本的3306衝突），啟動服務。
2. 在新版本下建立同名資料庫。
# mysqldump -p3307 -uroot create mysqlsystems_com
3. 在舊版本下備份該資料庫。
# mysqldump -p3306 -uroot mysqlsystems_com > mysqlsystems_com.bk
Note: 你也可以加上–opt選項，這樣可以使用最佳化方式將你的資料庫匯出，減少未知的問題。
4. 將匯出的資料庫備份匯入到新版本的MySQL資料庫中。
# mysql -p3307 -uroot mysqlsystems_com < mysqlsystems_com.bk
5. 再將舊版本資料庫中的data目錄下的mysql資料庫全部覆蓋到新版本中。
# cp -R /opt/mysql-5.1/data/mysql /opt/mysql-5.4/data（許可權庫保持不變）
Note: 大家也都知道這個預設資料庫的重要性。
6. 在新版下執行mysql_upgrade命令，其實這個命令包含一下三個命令：
# mysqlcheck –check-upgrade –all-databases –auto-repair
# mysql_fix_privilege_tables
# mysqlcheck –all-databases –check-upgrade –fix-db-names –fix-table-names
Note: 在每一次的升級過程中，mysql_upgrade這個命令我們都應該去執行，它透過mysqlcheck命令幫我們去檢查表是否相容新版本的資料庫同時 作出修復，還有個很重要的作用就是使用mysql_fix_privilege_tables命令去升級許可權表。
7. 關閉舊版本，將新版的資料庫的使用埠改為3306，重新啟動新版本MySQL資料庫。到此，一個簡單環境下的資料庫升級就結束了。
第二種，同樣適用任何儲存引擎。
1. 同樣先安裝好新版本的MySQL。
2. 在舊版本中，備份資料庫。
# mkdir /opt/mysqlsystems_bk ; mysqldump -p3306 -uroot –tab=/opt/mysqlsystems_bk mysqlsystems_com
Note: –tab選項可以在備份目錄mysqlsystems_bk下生成字尾為*.sql和*.txt的兩類檔案；其中，.sql儲存了建立表的SQL語句而.txt儲存著原始資料。
3. 接下來在新版本的資料庫下更新資料。
# mysqladmin -p3307 -uroot create mysqlsystems_com
# cat /opt/mysqlsystems_bk/*.sql | mysql -p3307 -uroot mysqlsystems_com ( Create Tables )
# mysqlimport mysqlsystems_com /opt/mysqlsystems_bk/*.txt ( Load Data )
4. 之後的所有步驟與第一種方法的後三步5、6、7相同。
第三種，適用於MyISAM儲存引擎，全部是檔案間的複製。
1. 安裝。
2. 從舊版本mysqlsystems_com資料庫下將所有.frm、.MYD 和.MYI檔案複製到新版本的相同目錄下。
3.之後的步驟依然同於第一種的後三步。
以上就是三種升級MySQL的方法，看似沒有出現什麼問題，其實，在實際的生產環境中，為會有諸多問題發生，這就需要我們在升級之前充分了解新版本中增加了哪些新功能，進一步分析升級以後這些新特性是否將會對我們原來應用產生影響。
3. MYSQL遠端連線不了的解決方案
有可能是這個使用者許可權不夠。檢視一下許可權表。
4. MYSQL忘記密碼解決辦法
在windows下：
開啟命令列視窗，停止mysql服務：Net stop mysql
到mysql的安裝路徑啟動mysql，在bin目錄下使用mysqld-nt.exe啟動，在命令列視窗執行：mysqld-nt --skip-grant-tables
然後另外開啟一個命入令行視窗，執行mysql，此時無需輸入密碼即可進入。
>use mysql
>update user set password=password("new_pass") where user="root";
>flush privileges;
>exit
使用工作管理員，找到mysqld-nt的程式，結束程式！
在重新啟動mysql-nt服務，就可以用新密碼登入了。
在linux下：
如果 MySQL 正在執行，首先殺之： killall -TERM mysqld。
啟動 MySQL ：bin/safe_mysqld --skip-grant-tables &
就可以不需要密碼就進入 MySQL 了。
然後就是
>use mysql
>update user set password=password("new_pass") where user="root";
>flush privileges;
重新殺 MySQL ，用正常方法啟動 MySQL 。
5. 更改MYSQL的預設字符集
法1、 用 SET 語法來指定，不加 "GLOBAL" 的話就只對本次會話有效
SET [GLOBAL] character_set_client = utf8;
SET [GLOBAL] character_set_connection = utf8;
SET [GLOBAL] character_set_database = utf8;
SET [GLOBAL] character_set_results = utf8;
SET [GLOBAL] character_set_server = utf8;
方法2、 也用SET語法，只對本次會話有效
SET NAMES 'utf8';
方法3、） 直接修改 my.cnf，增加一行內容，然後重啟 MySQL，使之全域性生效
default-character-set = utf8
6.MYSQL慢查詢分析工具：mysqldumpslow
mysqldumpslow命令
/path/mysqldumpslow -s c -t 10 /database/mysql/slow-log
這會輸出記錄次數最多的10條SQL語句，其中：
* -s, 是表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒敘；
* -t, 是top n的意思，即為返回前面多少條的資料；
* -g, 後邊可以寫一個正則匹配模式，大小寫不敏感的；
比如
/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log
得到返回記錄集最多的10個查詢。
/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
得到按照時間排序的前10條裡面含有左連線的查詢語句。
檢視mysql的某個選項
show variables like ‘%VAR_NAME%';
select @@VAR_NAME;
在Linux下管理MySQL資料庫的時候總有一些很緊急的情況，發現資料庫突然變得壓力很大了，那麼作為一個DBA，也許需要一些常用的手段或者說命令去分析問題出現在哪裡，然後解決：
資料庫突然產生壓力時檢視正在查詢的SQL:(如果這裡內容太多表示併發執行的SQL過多，或許資料庫堵塞了，會越來越慢，正常情況下這裡應該很少有東西的，也就是連線都在Sleep狀態）
/usr/local/mysql/bin/mysql -uroot -ppassword databaseName -e “show full processlist” | grep -v Sleep
正在執行的SQL太多了，看不過來，那需要排序了，看持續執行時間最長的那些SQL：
/usr/local/mysql/bin/mysql -uroot -ppassword databaseName -e “show full processlist” | grep -v Sleep | sort -k6rn >sort.tmp
如果發現IOWait很高，請檢視臨時表的生成情況，特別是disk tmp table:
/usr/local/mysql/bin/mysql -uroot -ppassword databaseName -e “show global status like ‘%tmp%'”
透過這樣一些辦法可以檢視資料庫都在忙什麼，那些忙的SQL又具體在哪一個步驟上卡住了，是在建立磁碟臨時檔案、Sending Data、statistics？依照不同的原因來解決問題
—————————————————————
關於Mysql Replication日常管理，重做，問題分析時常用的辦法：
重做Slave，或者Master變化等等，需要將Slave與新的Master同步：
change master to master_host=IP,master_user='replication userName',master
_password='replication Passwrod',master_log_file='log-bin.000001′,master_log_pos=0;
匯出資料成SQL文字，慎用，根據你的DB大小會鎖表，導致堵塞其他訪問：
nohup /usr/local/mysql/bin/mysqldump –database DATABASEName -uUserName -pPassWord –lock-all-tables -F >DATA20070519.sql &
-F後會重新整理Master Log這樣配合上面的Change Master可以讓Slave進行同步
只匯出資料庫的結構（沒有任何內容）
/usr/local/mysql/bin/mysqldump -d DATABASEName -uUserName -pPassWord >DATA20070519.structure
只匯出資料庫的資料（沒有建立表結構的語句等等）
/usr/local/mysql/bin/mysqldump -t DATABASEName -uUserName -pPassWord >DATA20070519.data
同步的時候出現問題（或者其他問題）了，根據同步出現問題的位置（偏移量），檢視Binlog的具體內容
/usr/local/mysql/bin/mysqlbinlog binlogFileName –start-position=偏移量
呵呵，我們碰到過Master執行的SQL到了Slave會報語法錯誤，夠詭異吧！不過就是這樣查到了原因：如果透過儲存過程將bit的內容改為1就會出現這樣的問題，後來將bit改為tinyint(1)就好了
授權給某一臺Slave擁有複製的許可權：
grant replication slave on *.* to 使用者名稱@IP identified by ‘密碼';
檢視Slave狀態：
Show slave status \G
檢視Master狀態：
Show master status;
重置Slave（慎用）
reset slave;
Slave出現問題了，先跳過這一條語句（請確認所要跳過的具體內容不會影響後面的同步，確認方法檢視Binlog檔案）：
set global sql_slave_skip_counter=1; (記得先暫停Slave：stop slave; 然後重啟Slave：start slave;)
mysqldump --default-character-set=gb2312 -h255.255.000.00 -uroot -pxxxxxx mydatabase>d:\data.sql
將指定的資料庫匯出到一個外部SQL檔案中去！
還原命令：
mysql --default-character-set=gb2312 -h255.255.000.00 -uroot -pxxxxxx mydatabase<d:\data.sql
2. MYSQL三種升級方法
安裝辦法：二進位制包編譯安裝
第一種：適用於任何一種儲存引擎。
1. 下載並安裝好新版本的MySQL資料庫，並將其埠改為3307（避免和舊版本的3306衝突），啟動服務。
2. 在新版本下建立同名資料庫。
# mysqldump -p3307 -uroot create mysqlsystems_com
3. 在舊版本下備份該資料庫。
# mysqldump -p3306 -uroot mysqlsystems_com > mysqlsystems_com.bk
Note: 你也可以加上–opt選項，這樣可以使用最佳化方式將你的資料庫匯出，減少未知的問題。
4. 將匯出的資料庫備份匯入到新版本的MySQL資料庫中。
# mysql -p3307 -uroot mysqlsystems_com < mysqlsystems_com.bk
5. 再將舊版本資料庫中的data目錄下的mysql資料庫全部覆蓋到新版本中。
# cp -R /opt/mysql-5.1/data/mysql /opt/mysql-5.4/data（許可權庫保持不變）
Note: 大家也都知道這個預設資料庫的重要性。
6. 在新版下執行mysql_upgrade命令，其實這個命令包含一下三個命令：
# mysqlcheck –check-upgrade –all-databases –auto-repair
# mysql_fix_privilege_tables
# mysqlcheck –all-databases –check-upgrade –fix-db-names –fix-table-names
Note: 在每一次的升級過程中，mysql_upgrade這個命令我們都應該去執行，它透過mysqlcheck命令幫我們去檢查表是否相容新版本的資料庫同時 作出修復，還有個很重要的作用就是使用mysql_fix_privilege_tables命令去升級許可權表。
7. 關閉舊版本，將新版的資料庫的使用埠改為3306，重新啟動新版本MySQL資料庫。到此，一個簡單環境下的資料庫升級就結束了。
第二種，同樣適用任何儲存引擎。
1. 同樣先安裝好新版本的MySQL。
2. 在舊版本中，備份資料庫。
# mkdir /opt/mysqlsystems_bk ; mysqldump -p3306 -uroot –tab=/opt/mysqlsystems_bk mysqlsystems_com
Note: –tab選項可以在備份目錄mysqlsystems_bk下生成字尾為*.sql和*.txt的兩類檔案；其中，.sql儲存了建立表的SQL語句而.txt儲存著原始資料。
3. 接下來在新版本的資料庫下更新資料。
# mysqladmin -p3307 -uroot create mysqlsystems_com
# cat /opt/mysqlsystems_bk/*.sql | mysql -p3307 -uroot mysqlsystems_com ( Create Tables )
# mysqlimport mysqlsystems_com /opt/mysqlsystems_bk/*.txt ( Load Data )
4. 之後的所有步驟與第一種方法的後三步5、6、7相同。
第三種，適用於MyISAM儲存引擎，全部是檔案間的複製。
1. 安裝。
2. 從舊版本mysqlsystems_com資料庫下將所有.frm、.MYD 和.MYI檔案複製到新版本的相同目錄下。
3.之後的步驟依然同於第一種的後三步。
以上就是三種升級MySQL的方法，看似沒有出現什麼問題，其實，在實際的生產環境中，為會有諸多問題發生，這就需要我們在升級之前充分了解新版本中增加了哪些新功能，進一步分析升級以後這些新特性是否將會對我們原來應用產生影響。
3. MYSQL遠端連線不了的解決方案
有可能是這個使用者許可權不夠。檢視一下許可權表。
4. MYSQL忘記密碼解決辦法
在windows下：
開啟命令列視窗，停止mysql服務：Net stop mysql
到mysql的安裝路徑啟動mysql，在bin目錄下使用mysqld-nt.exe啟動，在命令列視窗執行：mysqld-nt --skip-grant-tables
然後另外開啟一個命入令行視窗，執行mysql，此時無需輸入密碼即可進入。
>use mysql
>update user set password=password("new_pass") where user="root";
>flush privileges;
>exit
使用工作管理員，找到mysqld-nt的程式，結束程式！
在重新啟動mysql-nt服務，就可以用新密碼登入了。
在linux下：
如果 MySQL 正在執行，首先殺之： killall -TERM mysqld。
啟動 MySQL ：bin/safe_mysqld --skip-grant-tables &
就可以不需要密碼就進入 MySQL 了。
然後就是
>use mysql
>update user set password=password("new_pass") where user="root";
>flush privileges;
重新殺 MySQL ，用正常方法啟動 MySQL 。
5. 更改MYSQL的預設字符集
法1、 用 SET 語法來指定，不加 "GLOBAL" 的話就只對本次會話有效
SET [GLOBAL] character_set_client = utf8;
SET [GLOBAL] character_set_connection = utf8;
SET [GLOBAL] character_set_database = utf8;
SET [GLOBAL] character_set_results = utf8;
SET [GLOBAL] character_set_server = utf8;
方法2、 也用SET語法，只對本次會話有效
SET NAMES 'utf8';
方法3、） 直接修改 my.cnf，增加一行內容，然後重啟 MySQL，使之全域性生效
default-character-set = utf8
6.MYSQL慢查詢分析工具：mysqldumpslow
mysqldumpslow命令
/path/mysqldumpslow -s c -t 10 /database/mysql/slow-log
這會輸出記錄次數最多的10條SQL語句，其中：
* -s, 是表示按照何種方式排序，c、t、l、r分別是按照記錄次數、時間、查詢時間、返回的記錄數來排序，ac、at、al、ar，表示相應的倒敘；
* -t, 是top n的意思，即為返回前面多少條的資料；
* -g, 後邊可以寫一個正則匹配模式，大小寫不敏感的；
比如
/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log
得到返回記錄集最多的10個查詢。
/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
得到按照時間排序的前10條裡面含有左連線的查詢語句。
檢視mysql的某個選項
show variables like ‘%VAR_NAME%';
select @@VAR_NAME;
在Linux下管理MySQL資料庫的時候總有一些很緊急的情況，發現資料庫突然變得壓力很大了，那麼作為一個DBA，也許需要一些常用的手段或者說命令去分析問題出現在哪裡，然後解決：
資料庫突然產生壓力時檢視正在查詢的SQL:(如果這裡內容太多表示併發執行的SQL過多，或許資料庫堵塞了，會越來越慢，正常情況下這裡應該很少有東西的，也就是連線都在Sleep狀態）
/usr/local/mysql/bin/mysql -uroot -ppassword databaseName -e “show full processlist” | grep -v Sleep
正在執行的SQL太多了，看不過來，那需要排序了，看持續執行時間最長的那些SQL：
/usr/local/mysql/bin/mysql -uroot -ppassword databaseName -e “show full processlist” | grep -v Sleep | sort -k6rn >sort.tmp
如果發現IOWait很高，請檢視臨時表的生成情況，特別是disk tmp table:
/usr/local/mysql/bin/mysql -uroot -ppassword databaseName -e “show global status like ‘%tmp%'”
透過這樣一些辦法可以檢視資料庫都在忙什麼，那些忙的SQL又具體在哪一個步驟上卡住了，是在建立磁碟臨時檔案、Sending Data、statistics？依照不同的原因來解決問題
—————————————————————
關於Mysql Replication日常管理，重做，問題分析時常用的辦法：
重做Slave，或者Master變化等等，需要將Slave與新的Master同步：
change master to master_host=IP,master_user='replication userName',master
_password='replication Passwrod',master_log_file='log-bin.000001′,master_log_pos=0;
匯出資料成SQL文字，慎用，根據你的DB大小會鎖表，導致堵塞其他訪問：
nohup /usr/local/mysql/bin/mysqldump –database DATABASEName -uUserName -pPassWord –lock-all-tables -F >DATA20070519.sql &
-F後會重新整理Master Log這樣配合上面的Change Master可以讓Slave進行同步
只匯出資料庫的結構（沒有任何內容）
/usr/local/mysql/bin/mysqldump -d DATABASEName -uUserName -pPassWord >DATA20070519.structure
只匯出資料庫的資料（沒有建立表結構的語句等等）
/usr/local/mysql/bin/mysqldump -t DATABASEName -uUserName -pPassWord >DATA20070519.data
同步的時候出現問題（或者其他問題）了，根據同步出現問題的位置（偏移量），檢視Binlog的具體內容
/usr/local/mysql/bin/mysqlbinlog binlogFileName –start-position=偏移量
呵呵，我們碰到過Master執行的SQL到了Slave會報語法錯誤，夠詭異吧！不過就是這樣查到了原因：如果透過儲存過程將bit的內容改為1就會出現這樣的問題，後來將bit改為tinyint(1)就好了
授權給某一臺Slave擁有複製的許可權：
grant replication slave on *.* to 使用者名稱@IP identified by ‘密碼';
檢視Slave狀態：
Show slave status \G
檢視Master狀態：
Show master status;
重置Slave（慎用）
reset slave;
Slave出現問題了，先跳過這一條語句（請確認所要跳過的具體內容不會影響後面的同步，確認方法檢視Binlog檔案）：
set global sql_slave_skip_counter=1; (記得先暫停Slave：stop slave; 然後重啟Slave：start slave;)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15498/viewspace-2083186/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL DBA 常用手冊小結MySql
- mysql常用命令使用手冊MySql
- Linux使用手冊-vi使用手冊Linux
- golang常用手冊：指標、結構體Golang指標結構體
- Dynamips結合Dynagen使用手冊薦
- Docker叢集管理工具 - Kubernetes 使用手冊 (運維小結)Docker運維
- TcpDump使用手冊TCP
- MyBatis 使用手冊MyBatis
- sqlmap使用手冊SQL
- Mybatis應用手冊MyBatis
- Tmux使用手冊UX
- Docker使用手冊Docker
- Promise使用手冊Promise
- Redis使用手冊Redis
- iptables 使用手冊
- DotProject 使用手冊Project
- git 基本使用手冊Git
- Cobra框架使用手冊框架
- Memcached Client 使用手冊client
- jQuery使用手冊（收藏）jQuery
- 儀器使用手冊
- MySQL 基準測試工具tpcc-mysql使用手冊及報告分析MySql
- PerfDog WEB端使用手冊Web
- 【C#】CsvHelper 使用手冊C#
- Room Database完全使用手冊OOMDatabase
- MongoDB和pymongo自用手冊MongoDB
- Git 快速使用手冊（二）Git
- less語法實用手冊
- Linux iptables應用手冊Linux
- gcc使用手冊(2)(轉)GC
- gcc使用手冊(3)(轉)GC
- gcc使用手冊(4)(轉)GC
- mysql小結MySql
- 異常小總結
- 《Redis 使用手冊》- 字串（PHP 版本）Redis字串PHP
- ThreadLocal 使用手冊 | 按需收藏thread
- Linux parallel 命令使用手冊LinuxParallel
- BeautifulSoup使用手冊（查詢篇）