【mysql】常用操作：维护用户/开启远程/忘记密码/常用命令

一、维护用户

1.1 创建用户

-- 语法
> CREATE USER '[username]'@'[host]' IDENTIFIED BY '[password]';

-- 例子：
-- 添加用户user007，密码123456，并且只能在本地可以登录
> CREATE USER 'user007'@'localhost' IDENTIFIED BY '123456';
-- 添加用户user007，密码123456，并且可以从任意远程主机登录
> CREATE USER 'user007'@'%' IDENTIFIED BY '123456';

1.2 删除用户

-- 语法
> DROP USER '[username]'@'[host]';

-- 例子：
-- 删除从本地登录的账户user007
> DROP USER 'user007'@'localhost';
-- 删除从任意主机登录的账户user007
> DROP USER 'user007'@'%';

1.3 授权

-- 语法
> GRANT [privileges] ON [databasename].[tablename] TO '[username]'@'[host]'
-- privileges: SELECT,INSERT,UPDATE

-- 例子：
-- 给从本地主机访问的user007添加指定权限：select, insert
> GRANT SELECT,INSERT ON db_demo.* TO 'user007'@'localhost';
-- 给从任意主机访问的user007添加所有权限
> GRANT ALL ON *.* TO 'user007'@'%';

用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:

-- 语法
> GRANT [privileges] ON [databasename].[tablename] TO '[username]'@'[host]' WITH GRANT OPTION;

-- 例子
-- 给从本地主机访问的user007添加指定权限：select, insert，并且user007可以给别的用户授权
> GRANT SELECT,INSERT ON db_demo.* TO 'user007'@'localhost' WITH GRANT OPTION;

1.4 检验授权信息

-- 查看所有授权
> SHOW GRANTS;

-- 查看指定人/机授权：查看从本地登录的用户user007的授权
> SHOW GRANTS FOR 'user007'@'localhost';

-- 查看指定人授权：查看从任意主机登录的用户user007的授权
> SHOW GRANTS FOR 'user007'@'%';

 1.4 撤销授权

-- 语法
> REVOKE [privileges] ON [databasename].[tablename] FROM '[username]'@'[host]';

-- 例子：
-- 撤销从本地登录的user007在数据库demo上的select和insert授权
> REVOKE SELECT,INSERT ON db_demo.* FROM 'user007'@'localhost';
-- 撤销从任意主机登录的user007的所有授权
> REVOKE ALL ON *.* FROM 'user007'@'%';

这里需要注意，撤销授权必须跟授权命令匹配，否则不能撤销。

-- 授权
GRANT SELECT,INSERT ON db_demo.* TO 'user007'@'localhost';
-- 对应的撤销授权
REVOKE SELECT,INSERT ON db_demo.* FROM 'user007'@'localhost';

-- 授权
GRANT ALL ON *.* TO 'user007'@'%';
-- 对应的撤销授权
REVOKE ALL ON *.* FROM 'user007'@'%';

1.5 修改密码

-- 语法
> SET PASSWORD FOR '[username]'@'[host]' = PASSWORD('[newpassword]');

-- 例子
-- 修改当前用户的密码为 12345678
> SET PASSWORD = PASSWORD("12345678");

 二、开启远程

开启远程需要注意端口安全，以下场景可以考虑开启远程：

• 内网可访问，比如web应用需要访问时，把web服务器和mysql服务器置于同一内网网段
• 测试服务器
• 修改端口并且设置远程访问用户

开启远程其实就是基于1.3的授权。

2.1 查询授权

mysql> use mysql;
mysql> select User,authentication_string,Host from `user`;

2.2 开启远程

-- 开启root可以远程访问，并且访问密码是 123456
mysql> grant all privileges on *.* to 'root'@'%' IDENTIFIED BY '123456';

-- 修改完记得重新加载权限数据
mysql> flush privileges;

2.3 验证授权

mysql> use mysql;
mysql> select User,authentication_string,Host from `user`;

三、忘记密码

当忘记密码的时候，我们需要跳过权限验证进行登录，然后修改密码。

3.1 如果是CentOS7

3.1.1 停止服务

> sudo systemctl stop mysqld

3.1.2 修改服务配置跳过权限验证

> vi /usr/lib/systemd/system/mysqld.service

打开mysql的服务文件，编辑运行主服务那一行：

ExecStart=/usr/bin/mysqld_safe --basedir=/usr

添加 --skip-grant-tables：

ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables

3.1.3 重启服务

# 重新加载修改的服务文件
> sudo systemctl daemon-reload

# 启动MySQL服务
> sudo systemctl start mysqld

3.1.4 登录mysql

> mysql -uroot -p

运行命令后直接进入mysql，无需密码。

3.1.5 修改密码

# 修改从本地登录的root账号的密码为123456
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

# 刷新权限
mysql> flush privileges;

3.1.6 移除跳过权限验证的参数

这是为了还原#3.1.2步的操作，移除增加的参数：

> vi /usr/lib/systemd/system/mysqld.service

打开mysql的服务文件，编辑运行主服务那一行：

ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables

移除 --skip-grant-tables：

ExecStart=/usr/bin/mysqld_safe --basedir=/usr

3.1.7 重新登录

> mysql -uroot -p

此时提示需要密码，输入 123456后登录mysql：

3.2 如果是CentOS6

3.2.1 停止服务

# 方式1
> /etc/init.d/mysql stop   

# 方式2
> service mysqld stop

3.2.2 无需权限启动mysql

> /usr/bin/mysqld_safe --skip-grant-tables&

 也是  --skip-grant-tables 这个参数。

3.2.3 登录mysql

# 方式1
> mysql 

# 方式2
> mysql -uroot -p

运行命令后直接进入mysql，无需密码。

 3.2.4 修改密码

# 1、切换到数据库 mysql 
mysql> use mysql

# 2、修改密码
# 2.1 如果是5.6
mysql> update user set password=password("123456") where user="root";

# 2.2 如果是5.7+
mysql> update user set authentication_string=password('888888') where user='root';

# 3、刷新权限
mysql> flush privileges;

3.2.5 重启服务

在3.2.2中我们以无权限的方式启动mysql，需要重启以正常方式启动：

# 杀死无权限启动服务
> sudo killall mysqld_safe&

# 正常启动mysql
> /etc/init.d/mysql start

 3.2.6 重新登录

> mysql -uroot -p

此时提示需要密码，输入 123456后登录mysql：

 四、常用命令

-- 查看视图：
show table status where comment='view';

-- 查看存储过程：
show procedure status;

-- 查看进程：
show full processlist;