第76講：MySQL數據庫中常用的命令行工具的基本使用

文章目錄

- 1.mysql客戶端命令工具
- 2.mysqladmin管理數據庫的客戶端工具
- 3.mysqlbinlog查看數據庫中的二進制日志
- 4.mysqlshow統計數據庫中的信息
- 5.mysqldump數據庫備份工具
- 6.mysqllimport還原備份的數據
- 7.source命令還原SQL類型的備份文件

MySQL數據庫提供了很多的命令行工具，在日常運維過程中可以使用這些工具來操作數據庫。

1.mysql客戶端命令工具

mysql命令時MySQL數據庫的客戶端工具，如果在非數據庫的服務器上想要操作目標數據庫，就需要去安裝mysql客戶端，通過mysql客戶端可以對數據庫進行增刪改查操作，還可以進入mysql的交互式界面。

mysql工具的語法格式：mysql [options] [database]

mysql工具常用的選項：

-u,--user：指定登陸數據庫的用戶名。
-p,--password：指定登錄數據庫的密碼
-h,--host：指定要登陸的MySQL服務器地址。
-P,--port：指定MySQL數據庫的端口號。
-e,--excute：通過此參數可以在非交互式下操作數據庫，常用于腳本中。

#查看MySQL中有那些數據庫
[root@mysql ~]# mysql -u root -p123456 -h 192.168.20.10 -P 3306 -e "show databases;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| db-3               |
| db_1               |
| db_2               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+#該警告可以忽略，提示將密碼顯示在命令行不安全。

2.mysqladmin管理數據庫的客戶端工具

mysqladmin可是管理數據庫操作的客戶端程序，可以用它來檢查服務器的配置和當前的運行狀態，還可以來創建刪除刷新數據庫等等。

mysqladmin管理工具的參數很多，可以通過mysqladmin --help來查看幫助信息，根據需要去查找對應的參數。

mysqladmin管理工具可以通過命令參數對數據庫進行很多的操作，如下所示，有flush刷新數據庫的功能、password修改用戶的密碼、processlist查看當前數據庫的進程、shutdown關閉數據庫、variables查看數據庫的參數配置、version查看數據庫的版本。

mysqladmin工具的語法格式：mysqladmin [options] command

選項參數：

-u,--user：指定登陸數據庫的用戶名。
-p,--password：指定登錄數據庫的密碼
-h,--host：指定要登陸的MySQL服務器地址。
-P,--port：指定MySQL數據庫的端口號。

1）查看mysql數據庫的版本

[root@mysql ~]# mysqladmin -uroot -p123456 version
mysqladmin  Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)
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.26
Protocol version	10
Connection		Localhost via UNIX socket
UNIX socket		/var/lib/mysql/mysql.sock
Uptime:			2 days 11 hours 41 min 32 secThreads: 2  Questions: 101  Slow queries: 0  Opens: 200  Flush tables: 3  Open tables: 119  Queries per second avg: 0.000

2）查看mysql數據庫當前的運行的進程

[root@mysql ~]# mysqladmin -uroot -p123456 processlist
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
+----+-----------------+-----------+----+---------+--------+------------------------+------------------+
| Id | User            | Host      | db | Command | Time   | State                  | Info             |
+----+-----------------+-----------+----+---------+--------+------------------------+------------------+
| 5  | event_scheduler | localhost |    | Daemon  | 214901 | Waiting on empty queue |                  |
| 17 | root            | localhost |    | Query   | 0      | init                   | show processlist |
+----+-----------------+-----------+----+---------+--------+------------------------+------------------+

3）創建一個數據庫

[root@mysql ~]# mysqladmin -uroot -p123456 create db_test

4）刪除一個數據庫

[root@mysql ~]# mysqladmin -uroot -p123456 drop db_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 'db_test' database [y/N] y
Database "db_test" dropped

5）查看MySQL運行的狀態

[root@mysql ~]# mysqladmin -uroot -p123456 status
Uptime: 214852  Threads: 2  Questions: 98  Slow queries: 0  Opens: 200  Flush tables: 3  Open tables: 119  Queries per second avg: 0.000

3.mysqlbinlog查看數據庫中的二進制日志

通過mysqlbinlog命令可以查看數據庫生成的二進制文件內容，通過二進制日志可以恢復沒有備份且誤刪除的數據。

mysqlbinlog使用格式：mysqlbinlog [options] 二進制日志文件

可用的選項：

-d：指定數據庫名稱，只列出與指定數據庫相關的操作語句。
-o：忽略日志中前n行的指令。
-r：將輸出的文本格式日志輸出到指定的文件。
-s：顯示簡單的格式。
--start-datetime=date1 --stop-datetime=date2：顯示指定日期間隔內的所有信息。
--start-position=pos1 --stop-position=pos2：顯示指定位置間隔內的所有日志。

查看這個/var/lib/mysql/binlog.000003二進制日志中的數據。

[root@mysql ~]# mysqlbinlog -d db_1 /var/lib/mysql/binlog.000003

二進制日志中記錄的都是實時對數據庫操作的SQL語句。

4.mysqlshow統計數據庫中的信息

通過mysqlshow命令可以統計MySQL數據庫中有多少個數據庫，每個數據庫對應有多少張表，每張表下有多少條數據。

mysqlshow使用語法：mysqlshow [options] [db_name [table_name [col_name]]]

選項：

--count：顯示數據庫及表的統計信息。
-i：顯示指定數據庫或者指定表的狀態信息。

1）查看MySQL中有多少個數據庫、每個數據庫對應多少張表、每個數據庫下共有多少條數據。

[root@mysql ~]# mysqlshow -uroot -p123456 --count
+--------------------+--------+--------------+
|     Databases      | Tables |  Total Rows  |
+--------------------+--------+--------------+
| db-3               |      2 |            0 |
| db_1               |     23 |      1027304 |
| db_2               |      0 |            0 |
| information_schema |     79 |        32856 |
| mysql              |     37 |         3960 |
| performance_schema |    110 |       232877 |
| sys                |    101 |         5078 |
+--------------------+--------+--------------+
7 rows in set.#Tables表示該數據庫下有多少張表，Total Rows表示當前數據庫中有多少條數據。

2）查看指定數據庫下有那些表，分別有多少條數據。

[root@mysql ~]# mysqlshow -uroot -p123456 db_1 --count
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: db_1
+-------------------------+----------+------------+
|         Tables          | Columns  | Total Rows |
+-------------------------+----------+------------+
| bmxxb                   |        2 |          7 |
| dabiao                  |       62 |      27126 |
| erp_user                |        5 |          5 |
| jszx_xgymjzxxb          |       13 |         15 |
| ryxxb                   |        8 |         22 |
| tb_memory               |        2 |          0 |
| tb_user                 |        9 |          7 |
| tb_user_100w            |        6 |    1000000 |
| tb_user_pro             |        3 |          7 |
| v_jszx_xgymjzxxb_nozjhm |       12 |         15 |
| v_ryxx_bmxx             |        3 |         22 |
| v_ryxxb_1               |        3 |          1 |
| v_ryxxb_2               |        3 |          1 |
| v_ryxxb_3               |        3 |          1 |
| v_ryxxb_4               |        3 |          1 |
| v_ryxxb_5               |        3 |          1 |
| v_ryxxb_6               |        3 |          1 |
| v_ryxxb_c               |        1 |          1 |
| xscjb                   |        6 |         10 |
| xscjb_logs              |        5 |         33 |
| xzdjb                   |        3 |          8 |
| yexxb                   |        3 |          2 |
| ygxxb                   |        8 |         18 |
+-------------------------+----------+------------+
23 rows in set.#Columns表示表中有多少個字段

3）查看指定表的統計信息。

[root@mysql ~]# mysqlshow -uroot -p123456 db_1 xscjb --count

4）查看指定字段的統計信息。

[root@mysql ~]# mysqlshow -uroot -p123456 db_1 xscjb ywcj --count
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
Database: db_1  Table: xscjb  Rows: 10  Wildcard: ywcj
+-------+------+-----------+------+-----+---------+-------+---------------------------------+--------------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges                      | Comment      |
+-------+------+-----------+------+-----+---------+-------+---------------------------------+--------------+
| ywcj  | int  |           | YES  | MUL |         |       | select,insert,update,references | 語文成績 |
+-------+------+-----------+------+-----+---------+-------+---------------------------------+--------------+

5）查看指定數據庫中表的狀態。

[root@mysql ~]# mysqlshow -uroot -p123456 db_1 xscjb -i

5.mysqldump數據庫備份工具

mysqldump工具是對數據庫備份的工具，可以通過這個工具將數據進行備份，然后在不同數據庫之間遷移。

mysqldump的使用語法：

備份指定數據庫
- mysqldump 選項數據庫
備份指定數據庫下的某張表
- mysqldump 選項數據庫表
備份多個數據庫
- mysqldump 選項 --database/-B 數據庫1 數據庫2
備份所有數據庫
- mysqldump 選項 --all-databases/-A

mysqldump連接數據庫的選項：

-u,--user：指定登陸數據庫的用戶名。
-p,--password：指定登錄數據庫的密碼
-h,--host：指定要登陸的MySQL服務器地址。
-P,--port：指定MySQL數據庫的端口號。

mysqldump輸出選項：

--add-drop-database：在每個數據庫創建語句前加上 drop database 語句。
--add-drop-table：在每個表創建語句前加上 drop table 語句 , 默認開啟 ; 不開啟 (–skip-add-drop-table) 。
-n, --no-create-db：不包含數據庫的創建語句。
-t, --no-create-info：不包含數據表的創建語句。
-d --no-data：不包含數據。
-T, --tab=name：自動生成兩個文件：一個.sql文件，創建表結構的語句；一個.txt文件，數據文件。

1）備份db_1數據庫

[root@mysql backup]# mysqldump -uroot -p123456 db_1 > db_1.sql

在db_1.sql文件中備份了db_1數據庫的所有表以及數據、

2）備份db_1數據庫下的xscjb表，只備份表結構不備份數據

[root@mysql backup]# mysqldump -uroot -p123456 db_1 xscjb -d > db_1-xscjb.sql

此時備份文件里只有建表語句，不包含數據。

3）備份db_1數據庫下的xscjb表，只備份數據表不備份表結構和數據庫

[root@mysql backup]# mysqldump -uroot -p123456 db_1 xscjb -n -t > db_1-xscjb-data.sql

4）備份db_1數據庫下的xscjb表，生成兩個文件

當我們希望備份數據庫時，表結構和表數據分開存放，.sql文件存放表結構的備份，.txt文件存放表數據的備份，那么就需要使用-T參數了。

使用-T參數之前需要先查看MySQL受信任的備份路徑，否則只能備份表的結構，表數據無法備份成功。

1.查看mysql受信的備份路徑
mysql> show variables like '%secure_file_priv%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+2.備份時指定該路徑作為備份路徑即可
[root@mysql ~]# mysqldump -uroot -p123456 db_1 xscjb -T /var/lib/mysql-files/3.查看生成的備份文件
[root@mysql ~]# ll /var/lib/mysql-files/
總用量 12
-rw-r--r-- 1 root  root  5469 6月  29 23:09 xscjb.sql
-rw-r----- 1 mysql mysql  219 6月  29 23:09 xscjb.txt

.sql文件備份表結構，.txt文件備份表數據，使用-T參數導出的txt表數據，不是SQL語句，而是特定格式的數據。

6.mysqllimport還原備份的數據

mysqllimport工具可以還原mysqldump -T參數備份的txt格式的表數據文件，如果備份的數據是sql格式的，mysqllimport無法還原，mysqllimport只能還原txt格式的備份文件。

使用語法：mysqlimport [options] 數據庫 txtfile

將xscjb的數據清空，然后使用mysqllimport還原剛剛備份的xscjb的數據。

1.清空xscjb表的數據
[root@mysql ~]# mysql -uroot -p123456 -e "delete from db_1.xscjb;"2.還原備份的數據
[root@mysql ~]# mysqlimport -uroot -p123456 db_1 /var/lib/mysql-files/xscjb.txt 
db_1.xscjb: Records: 10  Deleted: 0  Skipped: 0  Warnings: 0#數據還原成功，還原的條數為10條

7.source命令還原SQL類型的備份文件

使用mysqllimport工具只能還原txt類型的備份文件，一般情況下備份的數據庫文件都是.sql格式的，可以通過source命令進行還原，source命令時在交互式中執行的。

[root@mysql ~]# mysql -uroot -p123456
mysql> use db_1;
mysql> source /root/backup/db_1.sql;