命令行创建MySQL数据库

上次的一片文章中整理了一下如何在命令行中导入导出网站MySQL备份。不过没有提到新建数据库和用户的操作，这一篇来补充一下。

建表语句

CREATE DATABASE test_db
DEFAULT CHARACTER SET utf8mb4 # 指定字符集
DEFAULT COLLATE utf8mb4_general_ci; # 指定排序字符集(排序规则)

后面两行的内容都是可以省略的。

另外可以加上 IF NOT EXISTS 避免表存在的情况下创建，例如

CREATE DATABASE IF NOT EXISTS example_db;

utf8mb4 是 MySQL在 5.5.3 之后增加的新编码，是 utf8 的超集。MySQL中 UTF8 使用1~3个字节去编码存储（是一种变长的编码格式），而 utf8mb4 使用四个字节存储，支持存储emoji等特殊字符。现在也推荐使用 utf8mb4。

常用的排序规则：

• utf8mb4_general_ciMySQL默认的字符检索策略。没有实现Unicode排序规则（遇到特殊字符排序结果可能不一致），但是速度和性能较好。后面带有 ci 表示 case insensitive，不区分大小写。
• utf8mb4_unicode_ci基于标准的Unicode来排序和比较，对特殊字符兼容性较好。理论上性能可能慢于 general_ci ，但是远远不足以成为考虑性能的因素，索引涉及、SQL设计才是。
• utf8mb4_bin使用二进制比较内容，区分大小写。

创建用户

CREATE USER '用户名'@'连接主机' IDENTIFIED BY '密码';

没有特殊字符时用户名的单引号可省略，例如：

# 创建一个只允许从本地连接的用户test_user
CREATE USER test_user@'localhost' IDENTIFIED BY '123456';
# 创建一个只允许从192.168.1.10连接的用户test_user2
CREATE USER test_user2@'192.168.1.10' IDENTIFIED BY '123456';

如果要允许用户从任何来源连接，将 @ 后面的地址换成 '%'

# 创建一个只允许从本地连接的用户test_user
CREATE USER test_user@'localhost' IDENTIFIED BY '123456';

查看和删除用户

# 显示所有的用户(5.7版本之前)
select host, user, password from mysql.user;
# 显示所有的用户(MySQL 5.7及之后) 密码字段名不一样
select host, user, authentication_string from mysql.user;

# 删除用户
drop user '用户名'@'%';
# 或者
delete from mysql.user where user='用户名';

权限

创建用户后，还需要授予相应操作数据库的权限

# 查看授予某个用户的权限
SHOW GRANTS FOR 用户名;

授权的命令如下

grant 权限类型 on 数据库名.表名 to '用户名'@'ip地址';

权限类型可选如下：

• select：读取
• create：创建
• delete：删除
• update：更新
• drop：删除数据库、表
• all：所有权限

例如：

# 允许用户test_user从本地访问数据库test_db的所有表，并赋予所有类型权限
grant all privileges on test_db.* to 'test_user'@'localhost';

收回用户权限可以用以下命令(一般用root用户操作)：

revoke 权限类型 on 数据库名.表名 to '用户名'@'ip地址';

例如

revoke select on test_db.* from "test_user"@"%";
revoke all on test_db.* from "test_user"@"%";

修改用户密码

如果创建用户并授权后还是没有权限连接数据库，可以试试手动修改一下密码

update mysql.user set authentication_string=password('新密码') where user='用户名';
# MySQL 5.7 以前用以下语句
update mysql.user set password=password('新密码') where user='用户名';

手动修改了MySQL的用户数据，需要执行以下命令手动刷新一下权限。使用 grant 和 revoke 命令则不需要

flush privileges;