mysqladmin 工具的使用格式：

mysqladmin [option] command [command option] command ......

option 選項：

-c number 自動執行次數統計，必須和 -i 一起使用

-i number 間隔多長時間重複執行

每個兩秒檢視一次伺服器的狀態，總共重複5次。

./mysqladmin -uroot -p -i 2 -c 5 status

     -#, --debug[=name] Output debug log. Often this is 'd:t:o,filename'.
-f, --force         Don't ask for confirmation on drop database; with
                      multiple commands, continue even if an error occurs. 禁用錯誤，drop 資料庫時不提示，執行多條命令時出錯繼續執行
-C, --compress      Use compression in server/client protocol.
--character-sets-dir=name 字符集所在目錄
                      Directory where character sets are.
--default-character-set=name 設定預設字符集
                      Set the default character set.
-?, --help          Display this help and exit. 顯示幫助

-h, --host=name     Connect to host. 連線的主機名或iP
-p, --password[=name] 登入密碼，如果不寫於引數後，則會提示輸入
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
-P, --port=#        Port number to use for connection. 指定資料庫埠
--protocol=name     The protocol of connection (tcp,socket,pipe,memory). 指定連線協議
-r, --relative      Show difference between current and previous values when
                      used with -i. Currently works only with extended-status. 顯示前後變化的值，必須結合- i

./mysqladmin -uroot -p -i 2 -r extended-status

假如此處顯示的uptime 將永遠是2，因為與之前取的結果比只相差2.

-O, --set-variable=name

                      Change the value of a variable. Please note that this
                      option is deprecated; you can set variables directly with
                      --variable-name=value.修改變數的值，該選項已經不再使用，請使用--variable-name=value 的方式修改變數值
-s, --silent        Silently exit if one can't connect to server.
-S, --socket=name   Socket file to use for connection. 指定socket file
-i, --sleep=#       Execute commands again and again with a sleep between. 間隔一段時間執行一次
-u, --user=name     User for login if not current user.登入資料庫使用者名稱
-v, --verbose       Write more information. 寫更多的資訊
-V, --version       Output version information and exit. 顯示版本

./mysql -uroot -p -V

-E, --vertical      Print output vertically. Is similar to --relative, but
                      prints output vertically.
-w, --wait[=#]      Wait and retry if connection is down. 如果連線斷開，等待w 指定的時間後重試
--connect_timeout=#
--shutdown_timeout=#

1、檢視伺服器的狀況：status
cd /usr/local/mysql/bin

./mysql -uroot -p status

顯示結果：

Uptime: 4883162 Threads: 1 Questions: 86 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 18 Queries per second avg: 0.000

2.修改root 密碼：

mysqladmin -u root -poldpassword password 'newpassword'

3.檢查mysqlserver是否可用：

mysqladmin -uroot -p ping

顯示結果：

mysqld is alive

4.查詢伺服器的版本

mysqladmin -uroot -p version

5.檢視伺服器狀態的當前值：

mysqladmin -uroot -p extended-status

顯示結果：

+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Aborted_clients                   | 12       |
| Aborted_connects                  | 300      |
| Binlog_cache_disk_use             | 0        |
| Binlog_cache_use                  | 0        |
| Bytes_received                    | 23608    |
| .......

|Threads_created                   | 3        |
| Threads_running                   | 1        |
| Uptime                            | 4884294 |
+-----------------------------------+----------+

6.查詢伺服器系統變數值：

mysqladmin -uroot -p variables

顯示結果：

+---------------------------------+----------------------------------------+
| Variable_name                   | Value                                  |
+---------------------------------+----------------------------------------+
| auto_increment_increment        | 1                                      |
| auto_increment_offset           | 1                                      |
| automatic_sp_privileges         | ON                                     |
| back_log                        | 50                                     |
| basedir                         | /usr/local/mysql/                      |

....

7.顯示伺服器所有執行的程式：

mysqladmin -uroot -p processlist

mysqladmin -uroot -p-i 1 processlist 每秒重新整理一次

顯示結果：

+-----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+-----------+----+---------+------+-------+------------------+
| 331 | root | localhost | | Query | 0 | | show processlist |
+-----+------+-----------+----+---------+------+-------+------------------+

8.建立資料庫

./mysqladmin -uroot -p create daba-test

驗證：

./mysql -uroot -p 登入後：show databases;

9.顯示伺服器上的所有資料庫

./mysqlshow -uroot -p

10.顯示資料庫daba-test下有些什麼表：

./mysqlshow -uroot -p daba-test

11.統計daba-test 下資料庫表列的彙總

./mysqlshow -uroot -p daba-test -v

12.統計daba-test 下資料庫表的列數和行數

./mysqlshow -uroot -p daba-test -v -v

13. 刪除資料庫 daba-test

./mysqladmin -uroot -p drop daba-test

14. 過載許可權資訊

./mysqladmin -uroot -p reload

15.重新整理所有表快取，並關閉和開啟log

./mysqladmin -uroot -p refresh

16.使用安全模式關閉資料庫

./mysqladmin -uroot -p shutdown

You can also use “/etc/rc.d/init.d/mysqld stop” to shutdown the server. To start the server, execute“/etc/rc.d/init.d/mysql start”--如果不是以服務來執行則這兩條命令無效/etc/init.d/mysql start

17.mysqladmin flush commands
# mysqladmin -u root -ptmppassword flush-hosts

# mysqladmin -u root -ptmppassword flush-logs
# mysqladmin -u root -ptmppassword flush-privileges
# mysqladmin -u root -ptmppassword flush-status
# mysqladmin -u root -ptmppassword flush-tables
# mysqladmin -u root -ptmppassword flush-threads

flush-hosts: Flush all information in the host cache.

flush-privileges: Reload the grant tables (same as reload).

flush-status: Clear status variables.

flush-threads: Flush the thread cache.

18 .mysqladmin 執行kill 程式：

./mysqladmin -uroot -p processlist

./mysqladmin -uroot -p kill idnum

19.停止和啟動MySQL replication on a slave server

mysqladmin -u root -p stop-slave

mysqladmin -u root -p start-slave

20 .同時執行多個命令

mysqladmin -u root -p process status version

mysqladmin 管理命令詳細應用

相關文章