Ubuntu添加或删除MySQL用户与权限、远程访问

1. 登录root账户

   mysql -u用户名 -p密码

$ mysql -uroot -p******** 

2. 创建新用户
   CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’;

$ CREATE USER 'testuser'@'%' IDENTIFIED BY '12345678';

• 注意：如果提示ERROR 1819 (HY000): Your password does not satisfy the current policy requirements，可查看当前密码策略

mysql> SHOW VARIABLES LIKE 'validate_password%'; 
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)

一般是密码长度，必须有大小写字母，有数字，改法如下：

mysql> set global validate_password_mixed_case_count=0;
Query OK, 0 rows affected (0.00 sec)

3. 赋予用户相关权限

   grant 权限1,权限2,…权限n on 数据库名称.表名称 to 用户名@用户地址;
   权限1,权限2,…权限n代表select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等权限

   例：赋予全部权限

mysql> grant all privileges on *.* to 'testuser'@'%';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec

4. 查看当前的用户

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | ding             |
| %         | root             |
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
5 rows in set (0.01 sec)

5. 删除用户

mysql> DELETE FROM user Where User='ding' and Host='%';
Query OK, 1 row affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
+-----------+------------------+
4 rows in set (0.00 sec)

• 设置远程访问

vi /etc/mysql/mysql.conf.d/mysqld.cnf

将bind-address = 127.0.0.1，修改为:0.0.0.0或者注释掉

root@root:~#service mysql restart
root@root:~#netstat -an | grep 3306
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN 

• 添加远程访问用户

mysql> GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

重启mysql服务

root@root:/# service mysql restart