> For the complete documentation index, see [llms.txt](https://darren.gitbook.io/project/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://darren.gitbook.io/project/devops/shu-ju-ku/mysql-ku-chang-yong-cao-zuo-ji-bei-fen-hai-yuan.md).

# mysql 库常用操作及备份还原

### 查看所有数据库

```
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| learn              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
```

### 切换数据库

```
mysql> use learn
Database changed
```

### 查看当前所在的数据库

```
mysql> select database();
+------------+
| database() |
+------------+
| learn      |
+------------+
```

### 创建数据库

```
create database [库名]  DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
```

### 查看建库语句

```
show create database learn;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| learn    | CREATE DATABASE `learn` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-------------------------------------------------------------------+
```

### 修改数据库的编码

```
alter database learn DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
```

### 备份数据库

> 备份单个数据库

```
$ mysqldump -uUsername -pPassword [dbname] > filename.sql
```

> 备份多个数据库

```
$ mysqldump -uUsername -pPassword --databases [dbname1] [dbname2] [dbname3] > filename.sql
```

### 还原数据库

```
$ mysql -uUsername -pPassword [dbname] < filename.sql
```