MySql 常用命令收藏

目录

1、登录

2、显示数据库

3、显示数据库中的表

4、显示表结构

5、退出MySQL命令行模式

6、备份数据库

1.备份所有数据库

2.备份指定数据库

7、还原备份

1.恢复所有数据库

普通还原

带压缩还原

2.恢复指定数据库

普通还原

带压缩还原

8、删除数据库

---

1、登录

mysql -uroot -proot

参数说明：

-u 用户名

-p 密码 

[email protected]:/# mysql -uroot -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.19 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

2、显示数据库

show databases;

mysql> show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    8
Current database: *** NONE ***

+--------------------+
| Database           |
+--------------------+
| information_schema |
| jeecg-boot         |
| mysql              |
| nacos              |
| performance_schema |
| sys                |
| xxl_job            |
+--------------------+
7 rows in set (0.01 sec)

3、显示数据库中的表

use mysql;
show tables;

 use 数据库名称; 选择数据库

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
........
| user                      |
+---------------------------+
33 rows in set (0.00 sec)

4、显示表结构

describe 表名;

mysql> describe user;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
.........................
| User_attributes          | json                              | YES  |     | NULL                  |       |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.00 sec)

5、退出MySQL命令行模式

exit

mysql> exit
Bye

6、备份数据库

1.备份所有数据库

mysqldump -u用户名 -p密码 --all-databases [|gzip] > 备份名称[.gz]

 []的部分为可选

 | gzip 压缩备份

例如：

[email protected]:/# mysqldump -uroot -proot --all-databases > allbackup.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

 带压缩

[email protected]:/# mysqldump -uroot -proot --all-databases |gzip > allbackup.sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.

 可以用ls -l -h输出结果，压缩后可以节约不少空间。

[email protected]:/# ls -l -h
total 9.7M
-rw-r--r--   1 root root 6.1M Mar  6 16:03 allbackup.sql
-rw-r--r--   1 root root 1.3M Mar  6 16:14 allbackup.sql.gz

2.备份指定数据库

mysqldump -u用户名 -p密码 --databases 数据库名[可以多个数据库用空格分开] [|gzip] > 备份名称[.gz]

 例如：

[email protected]:/# mysqldump -uroot -proot mysql > mysql.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

 带压缩

[email protected]:/# mysqldump -uroot -proot mysql |gzip > mysql.sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.

7、还原备份

1.恢复所有数据库

普通还原

mysql -u用户名 -p密码 -e "source 备份名称"

[email protected]:/# mysql -uroot -proot -e "source allbackup.sql"
mysql: [Warning] Using a password on the command line interface can be insecure.

带压缩还原

gunzip < 备份名称.gz | mysql -u用户名 -p密码

gunzip < allbackup.sql.gz | mysql -uroot -proot

2.恢复指定数据库

普通还原

mysql -u用户名 -p密码 数据库名称 < 备份名称

[email protected]:/# mysql -uroot -proot mysql < mysql.sql
mysql: [Warning] Using a password on the command line interface can be insecure.

带压缩还原

gunzip < 备份名称.gz | mysql -u用户名 -p密码 数据库名称

[email protected]:/# gunzip < mysql.sql.gz | mysql -uroot -proot mysql 
mysql: [Warning] Using a password on the command line interface can be insecure.

8、删除数据库

​drop database `数据库名称`;

注意：`数据库名称` 名称两边的【`】，有特殊符号的数据库名称必须加。