windows下远程访问linux中的mysql数据库(创建数据库新用户)
如果想要远程访问数据库,数据库必须开启权限,允许可以被远程访问。
1.进入Linux系统mysql数据库查看user表
mysql> use mysql;
mysql> select User,Host from user;
+---------------+-----------+
| User | Host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
Host字段中,‘localhost’表示只可以进行本地连接,所以要先创建一个可以进行远程连接的用户。(其Host字段中值为‘%’)。
2.在Linux系统创建新用户
mysql> CREATE USER 'admin'@'%' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.03 sec)
mysql> GRANT SHOW VIEW, SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'admin'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> FLUSH PRIVILEGES; //更新权限
Query OK, 0 rows affected (0.00 sec)
!!创建新用户可能遇到的问题!!
密码策略问题异常信息:
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
解决办法:
1、查看 mysql 初始的密码策略,
输入语句 “ SHOW VARIABLES LIKE ‘validate_password%’; ” 进行查看,
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 |
+--------------------------------------+--------+
2、设置密码的验证强度等级,设置 validate_password_policy 的全局参数为 LOW 即可,输入设值语句 “ set global validate_password_policy=LOW; ”
mysql> set global validate_password_policy=LOW;
Query OK, 0 rows affected (0.00 sec)
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 | LOW |
| validate_password_special_char_count | 1 |
+--------------------------------------+-------+
好了,这样就可以随意设个简单的8位密码了
3.在Linux系统查看新用户
mysql> use mysql;
mysql> select User,Host from user;
+---------------+-----------+
| User | Host |
+---------------+-----------+
| admin | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4.在windows工具上连接Linux数据库!
MySQL Host Address
是你Linux的地址用户名
和密码
是新建的(有访问权限的)那个端口号
默认3306
测试连接
连接通过!大功告成!