mysql 创建数据库赋权_mysql系列：创建数据库和用户及赋权

创建数据库和用户及赋权

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

4 rows in set (0.00 sec)

# 创建数据库,8.0以后mysql的utf8字符集分为utf8mb3和utf8mb4

mysql> create database develop default character set utf8mb4;

Query OK, 1 row affected, 1 warning (0.03 sec)

# 查询用户

mysql> select user,host from user;

+------------------+-----------+

| user | host |

+------------------+-----------+

| mysql.infoschema | localhost |

| mysql.session | localhost |

| mysql.sys | localhost |

| root | localhost |

+------------------+-----------+

4 rows in set (0.00 sec)

# 使用数据库

mysql> use develop

Database changed

# 8.0以后如果要让nevicat连接成功，需要这样修改密码

mysql> alter user 'root'@'%' identified with mysql_native_password by '123456';

Query OK, 0 rows affected (0.33 sec)

# 给root账户赋权所有数据库，任何IP都可以登录的权限

mysql> grant all privileges on *.* to 'root'@'%' with grant option;

Query OK, 0 rows affected (0.00 sec)

# 创建用户

mysql> create user 'dev'@'%' identified by '123456';

Query OK, 0 rows affected (0.01 sec)

# 8.0以后如果要让nevicat连接成功，需要这样修改密码

mysql> alter user 'dev'@'%' identified with mysql_native_password by '123456';

Query OK, 0 rows affected (0.37 sec)

# 8.0版本以前的修改方式

mysql> alter user 'dev'@'%' identified by '123456';

Query OK, 0 rows affected (0.01 sec)

# 指定develop数据库给用户赋权

mysql> grant all privileges on develop.* to 'dev'@'%' with grant option;

Query OK, 0 rows affected (0.00 sec)

# 检查用户权限

mysql> show grants for dev;

+--------------------------------------------------------------------+

| Grants for dev@% |

+--------------------------------------------------------------------+

| GRANT USAGE ON *.* TO `dev`@`%` |

| GRANT ALL PRIVILEGES ON `develop`.* TO `dev`@`%` WITH GRANT OPTION |

+--------------------------------------------------------------------+

2 rows in set (0.00 sec)

# 刷新权限

mysql> flush privileges;

Query OK, 0 rows affected (0.32 sec)

# 查询用户密码

mysql> select host,user,authentication_string from mysql.user;

+-----------+------------------+------------------------------------------------------------------------+

| host | user | authentication_string |

+-----------+------------------+------------------------------------------------------------------------+

| % | dev | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| % | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |

| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |

+-----------+------------------+------------------------------------------------------------------------+

MySQL 8.0 配置mysql_native_password身份验证插件的密码

方法一：

# 登录MySQL后输入：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourPassword';

FLUSH PRIVILEGES;

方法二：

# 编辑my.cnf文件，更改默认的身份认证插件。

vi /etc/my.cnf

# 在[mysqld]中添加下边的代码

default_authentication_plugin=mysql_native_password

# 然后重启MySQL

service mysqld restart