MySQL 常用命令记录

折腾一些东西的时候经常会用到 MySQL ，但自己对数据库这方面不是很熟，也没有系统的去学过，每次要干啥的时候都要去查资料，十分的麻烦。

所以在这里记录一些 MySQL 常用的语句以便自己查阅（copy）。

登录与退出

以 root 用户登录（会要求你输入密码）：

mysql -u root -p

退出当前用户：

exit;

添加新用户

添加只允许本地访问的用户 testuser ，密码设为 testpassword ：

create user 'testuser'@'localhost' identified by 'testpassword';

允许外网 IP 访问（MySQL 需允许外网访问）的用户 testuser2 ，密码设为 testpassword2 ：

create user 'testuser2'@'%' identified by 'testpassword2';

新建数据库

新建 UTF-8 编码的数据库 testdb ：

create database testdb default character set utf8 collate utf8_general_ci;

新建 utf8mb4 编码的数据库 testdb2 ：

create database testdb2 default character set utf8mb4 collate utf8mb4_unicode_ci;

分配数据库权限

授予本地用户 testuser 对数据库 testdb 的所有权限：

grant all privileges on testdb.* to 'testuser'@'localhost' identified by 'testpassword';

给允许外网 IP 访问的用户授权也是类似操作。

刷新权限

MySQL 新设置用户或更改密码后都需要刷新 MySQL 的系统权限相关表，否则会出现拒绝访问：

flush privileges;

数据备份

使用 testuser 用户备份 testdb 数据库下的 person 表至 /root/backup 目录：

mysqldump -u testuser -p testdb person > /root/backup/test_person.sql

使用 testuser 用户备份整个 testdb 数据库至 /root/backup 目录：

mysqldump -u testuser -p testdb > /root/backup/test.sql

使用 testuser 用户备份多个数据库至 /root/backup 目录：

mysqldump -u testuser -p --databases testdb1 testdb2 > /root/backup/twotestdb.sql

备份 testuser 用户下的所有数据库至 /root/backup 目录：

mysqldump -u testuser -p --all-databases > /root/backup/alldb.sql

数据还原

为了方便描述，这里使用上面数据备份部分使用的数据库用户 、数据库名 、备份文件 做示例。

对于数据库下表的备份需先建立空数据库再导入（假设已建立了新数据库 testdb ）

mysql -u testuser -p testdb < /root/backup/test_person.sql

对于整个数据库/多个数据库/所有数据库的备份直接用相应用户导入即可：

mysql -u testuser -p < /root/backup/backup.sql

查看数据库大小

登录后复制以下代码并使用即可分别显示出当前用户下所有数据库的占用大小（以MB为单位显示）：

select 
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

显示效果如下图：

注：持续更新中……