共計 5418 個字符，預(yù)計需要花費 14 分鐘才能閱讀完成。

這期內(nèi)容當(dāng)中丸趣 TV 小編將會給大家?guī)碛嘘P(guān) MySQL DBA 常用小知識有哪些，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

1.mysql 的遠(yuǎn)程連接命令可以遠(yuǎn)程導(dǎo)入導(dǎo)出數(shù)據(jù)

mysqldump –default-character-set=gb2312 -h355.255.000.00 -uroot -pxxxxxx mydatabase d:\data.sql

將指定的數(shù)據(jù)庫導(dǎo)出到一個外部 SQL 文件中去！

還原命令：

mysql –default-character-set=gb2312 -h355.255.000.00 -uroot -pxxxxxx mydatabase d:\data.sql

2. MYSQL 三種升級方法

安裝辦法：二進(jìn)制包編譯安裝

第一種：適用于任何一種存儲引擎。

1. 并安裝好新版本的 MySQL 數(shù)據(jù)庫，并將其端口改為 3307（避免和舊版本的 3306 沖突），啟動服務(wù)。

2. 在新版本下創(chuàng)建同名數(shù)據(jù)庫。

# mysqldump  -p3307  -uroot  create mysqlsystems_com

3. 在舊版本下備份該數(shù)據(jù)庫。

# mysqldump  -p3306  -uroot  mysqlsystems_com mysqlsystems_com.bk

Note: 你也可以加上 ndash;opt 選項，這樣可以使用優(yōu)化方式將你的數(shù)據(jù)庫導(dǎo)出，減少未知的問題。

4. 將導(dǎo)出的數(shù)據(jù)庫備份導(dǎo)入到新版本的 MySQL 數(shù)據(jù)庫中。

# mysql -p3307 -uroot mysqlsystems_com mysqlsystems_com.bk

5. 再將舊版本數(shù)據(jù)庫中的 data 目錄下的 mysql 數(shù)據(jù)庫全部覆蓋到新版本中。

# cp -R /opt/mysql-5.1/data/mysql  /opt/mysql-5.4/data（權(quán)限庫保持不變）

Note: 大家也都知道這個默認(rèn)數(shù)據(jù)庫的重要性。

6. 在新版下執(zhí)行 mysql_upgrade 命令，其實這個命令包含一下三個命令：

# mysqlcheck ndash;check-upgrade ndash;all-databases ndash;auto-repair

# mysql_fix_privilege_tables

# mysqlcheck ndash;all-databases ndash;check-upgrade ndash;fix-db-names ndash;fix-table-names

Note: 在每一次的升級過程中，mysql_upgrade 這個命令我們都應(yīng)該去執(zhí)行，它通過 mysqlcheck 命令幫我們?nèi)z查表是否兼容新版本的數(shù)據(jù)庫同時 作出修復(fù)，還有個很重要的作用就是使用 mysql_fix_privilege_tables 命令去升級權(quán)限表。

7. 關(guān)閉舊版本，將新版的數(shù)據(jù)庫的使用端口改為 3306，重新啟動新版本 MySQL 數(shù)據(jù)庫。到此，一個簡單環(huán)境下的數(shù)據(jù)庫升級就結(jié)束了。

第二種，同樣適用任何存儲引擎。

1. 同樣先安裝好新版本的 MySQL。

2. 在舊版本中，備份數(shù)據(jù)庫。

# mkdir /opt/mysqlsystems_bk  ;  mysqldump -p3306 -uroot ndash;tab=/opt/mysqlsystems_bk  mysqlsystems_com

Note: ndash;tab 選項可以在備份目錄 mysqlsystems_bk 下生成后綴為 *.sql 和 *.txt 的兩類文件；其中，.sql 保存了創(chuàng)建表的 SQL 語句而.txt 保存著原始數(shù)據(jù)。

3. 接下來在新版本的數(shù)據(jù)庫下更新數(shù)據(jù)。

# 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 數(shù)據(jù)庫下將所有.frm、.MYD 和.MYI 文件拷貝到新版本的相同目錄下。

3. 之后的步驟依然同于第一種的后三步。

以上就是三種升級 MySQL 的方法，看似沒有出現(xiàn)什么問題，其實，在實際的生產(chǎn)環(huán)境中，為會有諸多問題發(fā)生，這就需要我們在升級之前充分了解新版本中增加了哪些新功能，進(jìn)一步分析升級以后這些新特性是否將會對我們原來應(yīng)用產(chǎn)生影響。

3. MYSQL 遠(yuǎn)程連接不了的解決方案

有可能是這個用戶權(quán)限不夠。查看一下權(quán)限表。

4. MYSQL 忘記密碼解決辦法

在 windows 下：

打開命令行窗口，停止 mysql 服務(wù)：Net stop mysql

到 mysql 的安裝路徑啟動 mysql，在 bin 目錄下使用 mysqld-nt.exe 啟動，在命令行窗口執(zhí)行：mysqld-nt –skip-grant-tables

然后另外打開一個命入令行窗口，執(zhí)行 mysql，此時無需輸入密碼即可進(jìn)入。

use mysql

update user set password=password(new_pass) where user= root

flush privileges;

exit

使用任務(wù)管理器，找到 mysqld-nt 的進(jìn)程，結(jié)束進(jìn)程！

在重新啟動 mysql-nt 服務(wù)，就可以用新密碼登錄了。

在 linux 下：

如果 MySQL 正在運行，首先殺之：killall -TERM mysqld。

啟動 MySQL：bin/safe_mysqld –skip-grant-tables

就可以不需要密碼就進(jìn)入 MySQL 了。

然后就是

use mysql

update user set password=password(new_pass) where user= root

flush privileges;

重新殺 MySQL，用正常方法啟動 MySQL。

5. 更改 MYSQL 的默認(rèn)字符集

法 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，增加一行內(nèi)容，然后重啟 MySQL，使之全局生效

default-character-set = utf8

6.MYSQL 慢查詢分析工具：mysqldumpslow

mysqldumpslow 命令

/path/mysqldumpslow -s c -t 10 /database/mysql/slow-log

這會輸出記錄次數(shù)最多的 10 條 SQL 語句，其中：

  * -s, 是表示按照何種方式排序，c、t、l、r 分別是按照記錄次數(shù)、時間、查詢時間、返回的記錄數(shù)來排序，ac、at、al、ar，表示相應(yīng)的倒敘；

  * -t, 是 top n 的意思，即為返回前面多少條的數(shù)據(jù)；

  * -g, 后邊可以寫一個正則匹配模式，大小寫不敏感的；

比如

/path/mysqldumpslow -s r -t 10 /database/mysql/slow-log

得到返回記錄集最多的 10 個查詢。

/path/mysqldumpslow -s t -t 10 -g“l(fā)eft join”/database/mysql/slow-log

得到按照時間排序的前 10 條里面含有左連接的查詢語句。

查看 mysql 的某個選項

show variables like lsquo;%VAR_NAME% rsquo;;

select @@VAR_NAME;

  在下管理 MySQL 的時候總有一些很緊急的情況，發(fā)現(xiàn)數(shù)據(jù)庫突然變得壓力很大了，那么作為一個 DBA，也許需要一些常用的手段或者說命令去分析問題出現(xiàn)在哪里，然后解決：

數(shù)據(jù)庫突然產(chǎn)生壓力時查看正在查詢的 SQL:(如果這里內(nèi)容太多表示并發(fā)執(zhí)行的 SQL 過多，或許數(shù)據(jù)庫堵塞了，會越來越慢，正常情況下這里應(yīng)該很少有東西的，也就是連接都在 Sleep 狀態(tài)）

/usr/local//bin/mysql -uroot -ppassword databaseName  -e“show full processlist”| grep -v Sleep

正在運行的 SQL 太多了，看不過來，那需要排序了，看持續(xù)執(zhí)行時間最長的那些 SQL：

/usr/local/mysql/bin/mysql -uroot -ppassword databaseName  -e“show full processlist”| grep -v Sleep | sort -k6rn sort.tmp

如果發(fā)現(xiàn) IOWait 很高，請查看臨時表的生成情況，特別是 disk tmp table:

/usr/local/mysql/bin/mysql -uroot -ppassword databaseName  -e“show global status like lsquo;%tmp% rsquo;”

通過這樣一些辦法可以查看數(shù)據(jù)庫都在忙什么，那些忙的 SQL 又具體在哪一個步驟上卡住了，是在創(chuàng)建磁盤臨時文件、Sending Data、statistics？依照不同的原因來解決問題

mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash;

關(guān)于 Mysql Replication 日常管理，重做，問題分析時常用的辦法：

重做 Slave，或者 Master 變化等等，需要將 Slave 與新的 Master 同步：

change master to master_host=IP,master_user= rsquo;replication userName rsquo;,master

_password= rsquo;replication Passwrod rsquo;,master_log_file= rsquo;log-bin.000001 prime;,master_log_pos=0;

導(dǎo)出數(shù)據(jù)成 SQL 文本，慎用，根據(jù)你的 DB 大小會鎖表，導(dǎo)致堵塞其他訪問：

nohup /usr/local/mysql/bin/mysqldump ndash;database DATABASEName -uUserName -pPassWord ndash;lock-all-tables -F DATA20070519.sql

- F 后會刷新 Master Log 這樣配合上面的 Change Master 可以讓 Slave 進(jìn)行同步

只導(dǎo)出數(shù)據(jù)庫的結(jié)構(gòu)（沒有任何內(nèi)容）

/usr/local/mysql/bin/mysqldump -d DATABASEName -uUserName -pPassWord DATA20070519.structure

只導(dǎo)出數(shù)據(jù)庫的數(shù)據(jù)（沒有創(chuàng)建表結(jié)構(gòu)的語句等等）

/usr/local/mysql/bin/mysqldump -t DATABASEName -uUserName -pPassWord DATA20070519.data

同步的時候出現(xiàn)問題（或者其他問題）了，根據(jù)同步出現(xiàn)問題的位置（偏移量），查看 Binlog 的具體內(nèi)容

/usr/local/mysql/bin/mysqlbinlog binlogFileName ndash;start-position= 偏移量

呵呵，我們碰到過 Master 執(zhí)行的 SQL 到了 Slave 會報語法錯誤，夠詭異吧！不過就是這樣查到了原因：如果通過存儲過程將 bit 的內(nèi)容改為 1 就會出現(xiàn)這樣的問題，后來將 bit 改為 tinyint(1) 就好了

授權(quán)給某一臺 Slave 擁有復(fù)制的權(quán)限：

grant replication slave on *.* to 用戶名 @IP identified by lsquo; 密碼 rsquo;;

查看 Slave 狀態(tài)：

Show slave status \G

查看 Master 狀態(tài)：

Show master status;

重置 Slave（慎用）

reset slave;

Slave 出現(xiàn)問題了，先跳過這一條語句（請確認(rèn)所要跳過的具體內(nèi)容不會影響后面的同步，確認(rèn)方法查看 Binlog 文件）：

set global sql_slave_skip_counter=1; (記得先暫停 Slave：stop slave; 然后重啟 Slave：start slave;)

mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; mdash; ndash;

純粹 Linux 相關(guān)的：

tcpdump -A“dst port 3306 Prime; 查看 3306 端口的通信具體內(nèi)容  

上述就是丸趣 TV 小編為大家分享的 MySQL DBA 常用小知識有哪些了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道。