第76讲：MySQL数据库中常用的命令行工具的基本使用

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 sec

Threads: 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;