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