创建数据库和用户及赋权
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