安装mysql8并且设置其他用户可创建数据库

sudo apt install mysql-server

首先我们安装了mysql 安装日志在cat /var/log/mysql/error.log

此时安装后的数据库为仅本地localhost可以连接 需要修改my.cnf 可以看到该文件里引用了两个目录

我们通过挨个cat内容 最后发现是/etc/mysql/mysql.conf.d/mysqld.cnf 里有bind-address        = 127.0.0.1 的限制

而mysqlx-bind-address    = 127.0.0.1 的意思是X Plugin的限制 在这里我们不需要修改此项

只修改bind-address        =0.0.0.0

为安全起见此项仅建议开发环境使用

首先我们修改密码root密码

alter user "root"@"localhost" identified by "your password";

然后我们新建一个全局的访问的用户

create user 'moxiao'@'%' identified by '123456';

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

这个错误。经过我查询有道词典之后发现是说密码不够复杂。于是我找到专门生成密码的网站生成了一个特别复杂的密码才没有再报这个错误。16位A-Za-z1-9外加!@#$%^&*才可以。也真是复杂。于是我马上拿起我的记事本软件记住了他。

这个时候呢 我想的就是想让moxiao用户可以创建数据库

并且不想看到默认的那四个数据库。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

我就只有先赋予moxiao所有的权限 然后再把这个4个数据库移出moxiao的权限就行。

GRANT privileges ON *.* TO 'moxiao'@'%' WITH GRANT OPTION;

mysql> revoke all  on sys.* to 'moxiao'@'%' WITH  GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to 'moxiao'@'%' WITH  GRANT OPTION' at line 1
mysql> revoke all  on *.* to 'moxiao'@'%' WITH  GRANT OPTION;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to 'moxiao'@'%' WITH  GRANT OPTION' at line 1
mysql> revoke all  on *.* from 'moxiao'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT privileges ON *.* TO 'moxiao'@'%' WITH GRANT OPTION; 
ERROR 1149 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
mysql> GRANT all ON *.* TO 'moxiao'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> revoke all  on sys.* from 'moxiao'@'%';
ERROR 1141 (42000): There is no such grant defined for user 'moxiao' on host '%'
mysql> revoke all  on *.* from 'moxiao'@'%';
Query OK, 0 rows affected (0.00 sec)

最后发现实现不了。。。

只好退而求其次。

如果需要新建数据库的时候就用root来新建。然后给moxiao这个用户这个数据库的所有权限。

CREATE DATABASE `testdb` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

GRANT all privileges ON testdb.* TO 'moxiao'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

GRANT Select ON samp_db.member TO bill@localhost INDETIFIED BY "rock"

Insert, Delete, Select, Update

https://www.cnblogs.com/sos-blue/p/6852945.html

# 修改为mysql57的密码策略 

create user 'mysql57'@'%' identified WITH mysql_native_password by 'mqnWXo23n0a023#msno@';