Linux中19個MySQL數據庫管理命令

嵌入式ARM 2023-07-04 12:01

高性能SGT MOS產品，保障BMS高效可靠運行 找芯片數據手冊、參考設計和開發工具？

mysqladmin是一個MySQL服務器命令行工具，它由Database Administrators執行一些基本的命令諸如更改 root 密碼、監控 mysql 進程、重新加載權限、檢查服務器狀態等任務。

1. 如何更改 MySQL Root密碼？

如果你想更改MySQLroot 密碼，然后你需要鍵入以下命令。

舊密碼123456
新密碼rumenz123

>?mysqladmin?-u?root?-p?123456?password?rumenz123

2. 如何檢查 MySQL Server是否運行？

查看MySQL服務器運行狀態

>?mysqladmin?-u?root?-p?ping

Enter?password:
mysqld?is?alive

3. 查看MySQL版本？

顯示MySQL版本以及當前運行狀態。

>?mysqladmin?-u?root?-p?version

Enter?password:
mysqladmin??Ver?8.0.27?for?macos10.14?on?x86_64?(Homebrew)
Copyright?(c)?2000,?2021,?Oracle?and/or?its?affiliates.

Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its
affiliates.?Other?names?may?be?trademarks?of?their?respective
owners.

Server?version??8.0.27
Protocol?version?10
Connection??Localhost?via?UNIX?socket
UNIX?socket??/tmp/mysql.sock
Uptime:???5?days?5?hours?46?min?40?sec

Threads:?2??Questions:?140408??Slow?queries:?0??Opens:?727??Flush?tables:?3??Open?tables:?624??Queries?per?second?avg:?0.310

4. 查看 MySQL服務器的當前狀態？

查看當前MySQL服務器狀態。mysqladmin命令顯示狀態uptime，threads和queries.

>?mysqladmin?-u?root?-p??status

Uptime:?452778??Threads:?2??Questions:?140406??Slow?queries:?0??Opens:?727??Flush?tables:?3??Open?tables:?624??Queries?per?second?avg:?0.310

5. 查看所有 MySQL服務器變量和值的狀態？

檢查MySQL服務器變量和值

>?mysqladmin?-u?root?-p?extended-status

Enter?password:
+------------------------------------------+-------------+
|?Variable_name????????????????????????????|?Value???????|
+------------------------------------------+-------------+
|?Aborted_clients??????????????????????????|?3???????????|
|?Aborted_connects?????????????????????????|?3???????????|
|?Binlog_cache_disk_use????????????????????|?0???????????|
|?Binlog_cache_use?????????????????????????|?0???????????|
|?Binlog_stmt_cache_disk_use???????????????|?0???????????|
|?Binlog_stmt_cache_use????????????????????|?0???????????|
|?Bytes_received???????????????????????????|?6400357?????|
|?Bytes_sent???????????????????????????????|?2610105?????|
|?Com_admin_commands???????????????????????|?3???????????|
|?Com_assign_to_keycache???????????????????|?0???????????|
|?Com_alter_db?????????????????????????????|?0???????????|
|?Com_alter_db_upgrade?????????????????????|?0???????????|
|?Com_alter_event??????????????????????????|?0???????????|
|?Com_alter_function???????????????????????|?0???????????|
|?Com_alter_procedure??????????????????????|?0???????????|
|?Com_alter_server?????????????????????????|?0???????????|
|?Com_alter_table??????????????????????????|?0???????????|
|?Com_alter_tablespace?????????????????????|?0???????????|
+------------------------------------------+-------------+

6. 查看所有 MySQL服務器的變量和值？

查看MySQL變量和值

>?mysqladmin??-u?root?-p?variables

Enter?password:
+---------------------------------------------------+----------------------------------------------+
|?Variable_name?????????????????????????????????????|?Value????????????????????????????????????????|
+---------------------------------------------------+----------------------------------------------+
|?auto_increment_increment??????????????????????????|?1????????????????????????????????????????????|
|?auto_increment_offset?????????????????????????????|?1????????????????????????????????????????????|
|?autocommit????????????????????????????????????????|?ON???????????????????????????????????????????|
|?automatic_sp_privileges???????????????????????????|?ON???????????????????????????????????????????|
|?back_log??????????????????????????????????????????|?50???????????????????????????????????????????|
|?basedir???????????????????????????????????????????|?/usr?????????????????????????????????????????|
|?big_tables????????????????????????????????????????|?OFF??????????????????????????????????????????|
|?binlog_cache_size?????????????????????????????????|?32768????????????????????????????????????????|
|?binlog_direct_non_transactional_updates???????????|?OFF??????????????????????????????????????????|
|?binlog_format?????????????????????????????????????|?STATEMENT????????????????????????????????????|
|?binlog_stmt_cache_size????????????????????????????|?32768????????????????????????????????????????|
|?bulk_insert_buffer_size???????????????????????????|?8388608??????????????????????????????????????|
|?character_set_client??????????????????????????????|?latin1???????????????????????????????????????|
|?character_set_connection??????????????????????????|?latin1???????????????????????????????????????|
|?character_set_database????????????????????????????|?latin1???????????????????????????????????????|
|?character_set_filesystem??????????????????????????|?binary???????????????????????????????????????|
|?character_set_results?????????????????????????????|?latin1???????????????????????????????????????|
|?character_set_server??????????????????????????????|?latin1???????????????????????????????????????|
|?character_set_system??????????????????????????????|?utf8?????????????????????????????????????????|
|?character_sets_dir????????????????????????????????|?/usr/share/mysql/charsets/???????????????????|
|?collation_connection??????????????????????????????|?latin1_swedish_ci????????????????????????????|
+---------------------------------------------------+----------------------------------------------+

7.查看MySQL服務器的所有運行進程？

>?mysqladmin?-u?root?-p?processlist

Enter?password:
+-------+---------+-----------------+---------+---------+------+-------+------------------+
|?Id????|?User????|?Host????????????|?db??????|?Command?|?Time?|?State?|?Info?????????????|
+-------+---------+-----------------+---------+---------+------+-------+------------------+
|?18001?|?rsyslog?|?localhost:38307?|?rsyslog?|?Sleep???|?5590?|???????|??????????????????|
|?18020?|?root????|?localhost???????|?????????|?Query???|?0????|???????|?show?processlist?|
+-------+---------+-----------------+---------+---------+------+-------+------------------+

8. 在 MySQL 服務器中創建數據庫？

創建數據庫。

>?mysqladmin?-u?root?-p?create?rumenz_test

Enter?password:

9. 在 MySQL 服務器中刪除數據庫？

通過mysqladmin創建數據庫

>?mysqladmin?-u?root?-p?drop?rumenz_test

Dropping?the?database?is?potentially?a?very?bad?thing?to?do.
Any?data?stored?in?the?database?will?be?destroyed.

Do?you?really?want?to?drop?the?'rumenz_test'?database?[y/N]?y
Database?"rumenz_test"?dropped

10. 重新加載/刷新 MySQL 權限？

reload命令告訴服務器重新加載授權表。refresh命令刷新所有表并重新打開日志文件。

>?mysqladmin?-u?root?-p?reload
>?mysqladmin?-u?root?-p?refresh

11. 安全關閉 MySQL 服務器？

>?mysqladmin?-u?root?-p?shutdown

Enter?password:

12. MySQL Flush 常用命令

flush-hosts：從主機緩存中刷新所有主機信息。
flush-tables: 刷新所有表。
flush-threads：刷新所有線程緩存。
flush-logs：刷新所有信息日志。
flush-privileges：重新加載授權表（與重新加載相同）。
flush-status：清除狀態變量。

#?mysqladmin?-u?root?-p?flush-hosts
#?mysqladmin?-u?root?-p?flush-tables
#?mysqladmin?-u?root?-p?flush-threads
#?mysqladmin?-u?root?-p?flush-logs
#?mysqladmin?-u?root?-p?flush-privileges
#?mysqladmin?-u?root?-p?flush-status

13. 殺死正在休眠的 MySQL 客戶端進程？

找出休眠的進程

>?mysqladmin?-u?root?-p?processlist

Enter?password:
+----+------+-----------+----+---------+------+-------+------------------+
|?Id?|?User?|?Host??????|?db?|?Command?|?Time?|?State?|?Info?????????????|
+----+------+-----------+----+---------+------+-------+------------------+
|?5??|?root?|?localhost?|????|?Sleep???|?14???|???????|??????|
|?8??|?root?|?localhost?|????|?Query???|?0????|???????|?show?processlist?|
+----+------+-----------+----+---------+------+-------+------------------+

用kill和process ID殺死

>?mysqladmin?-u?root?-p?kill?5

Enter?password:
+----+------+-----------+----+---------+------+-------+------------------+
|?Id?|?User?|?Host??????|?db?|?Command?|?Time?|?State?|?Info?????????????|
+----+------+-----------+----+---------+------+-------+------------------+
|?12?|?root?|?localhost?|????|?Query???|?0????|???????|?show?processlist?|
+----+------+-----------+----+---------+------+-------+------------------+

如果需要kill多個進程，用逗號分隔多個process ID

>?mysqladmin?-u?root?-p?kill?5,10

14、同時運行多個mysqladmin命令？

同時執行多個 mysqladmin 命令

>?mysqladmin??-u?root?-p?processlist?status?version

Enter?password:
+----+------+-----------+----+---------+------+-------+------------------+
|?Id?|?User?|?Host??????|?db?|?Command?|?Time?|?State?|?Info?????????????|
+----+------+-----------+----+---------+------+-------+------------------+
|?8??|?root?|?localhost?|????|?Query???|?0????|???????|?show?processlist?|
+----+------+-----------+----+---------+------+-------+------------------+
Uptime:?3801??Threads:?1??Questions:?15??Slow?queries:?0??Opens:?15??Flush?tables:?1??Open?tables:?8??Queries?per?second?avg:?003
mysqladmin??Ver?42?Distrib?28,?for?Linux?on?i686
Copyright?(c)?2000,?2012,?Oracle?and/or?its?affiliates.?All?rights?reserved.

Oracle?is?a?registered?trademark?of?Oracle?Corporation?and/or?its
affiliates.?Other?names?may?be?trademarks?of?their?respective
owners.

Server?version??????????28
Protocol?version????????10
Connection??????????????Localhost?via?UNIX?socket
UNIX?socket?????????????/var/lib/mysql/mysql.sock
Uptime:?????????????????1?hour?3?min?21?sec

Threads:?1??Questions:?15??Slow?queries:?0??Opens:?15??Flush?tables:?1??Open?tables:?8??Queries?per?second?avg:?003

15.連接遠程mysql服務器

連接遠程MySQL服務器，使用-h(host）和IP Address參數

>?mysqladmin??-h?rumenz.com?-u?root?-p

16.在遠程MySQL服務器上執行命令

如果想查看遠程的MySQL服務器的status

>?mysqladmin??-h?rumenz.com?-u?root?-p?status

17. 在從服務器上啟動/停止 MySQL 主從復制？

要在從服務器上啟動/停止MySQL 復制，使用以下命令。

>?mysqladmin??-u?root?-p?start-slave

>?mysqladmin??-u?root?-p?stop-slave

18. 將 MySQL 服務器的 Debug Information 存儲到日志中？

將有關正在使用的鎖、已用內存和查詢使用情況的調試信息寫入MySQL日志文件(包括有關事件調度程序的信息)。

>?mysqladmin??-u?root?-p?debug

Enter?password:

19.查看mysqladmin幫助

了解更多myslqadmin命令選項和用法。

>?mysqladmin?--help

END

來源：入門小站

版權歸原作者所有，如有侵權，請聯系刪除。

▍推薦閱讀

被一個gpio口搞死的一天

為什么Keil比IAR更受歡迎？

分享幾個不錯的嵌入式開源項目

→點關注，不迷路←

登錄閱讀全文



免責聲明：該內容由專欄作者授權發布或作者轉載，目的在于傳遞更多信息，并不代表本網贊同其觀點，本站亦不保證或承諾內容真實性等。若內容或圖片侵犯您的權益，請及時聯系本站刪除。侵權投訴聯系：? nick.zong@aspencore.com！

嵌入式ARM 關注這個時代最火的嵌入式ARM，你想知道的都在這里。

進入專欄

評論