ubuntu 连接MySQL详细步骤（亲测可用）

最新推荐文章于 2024-03-05 09:50:48 发布

IT界刘德华

最新推荐文章于 2024-03-05 09:50:48 发布

阅读量6.2k

点赞数 1

分类专栏： Linux 文章标签： Ubuntu 远程连接Mysql Mysql Mysql远程连接授权连接服务器MySQL错误解决

本文链接：https://blog.csdn.net/qq_31150503/article/details/70194312

版权

Linux 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

连接ubuntu(服务器) MySQL 步骤（亲测好用）

MySQL -h192.168.0.104 -uroot -pXXX

 mysql: [Warning] Using a password on the command line interface can be insecure.
 ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.1.104' (111)

解决方案：

1、授予远程登录权限
grant all privileges on *.* To '用户名'@'%' identified by '密码' with grant option；
    -- 5.7 后加用户名登录验证插件 如果密码过于简单 可能会出现（如果没有可直接进行第二步）：
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements  
    -- 验证解决方案
     mysql> select @@log_error;
        +---------------------+
        | @@log_error         |
        +---------------------+
        | /var/log/mysqld.log |
        +---------------------+
        1 row in set (0.00 sec)
    mysql> set global validate_password_policy=0;
            Query OK, 0 rows affected (0.00 sec)
    mysql> set global validate_password_mixed_case_count=2;
            Query OK, 0 rows affected (0.00 sec)
    mysql> set global validate_password_length=4;
            Query OK, 0 rows affected (0.00 sec)
    mysql> set global validate_password_special_char_count=0;
            Query OK, 0 rows affected (0.00 sec)
    mysql> SHOW VARIABLES LIKE 'validate_password%';
            +--------------------------------------+-------+
            | Variable_name                        | Value |
            +--------------------------------------+-------+
            | validate_password_dictionary_file    |       |
            | validate_password_length             | 4     |
            | validate_password_mixed_case_count   | 2     |
            | validate_password_number_count       | 0     |
            | validate_password_policy             | LOW   |
            | validate_password_special_char_count | 0     |
            +--------------------------------------+-------+
            6 rows in set (0.00 sec)
    mysql> flush privileges;
    -- 重新远程授权
     mysql> grant all privileges on *.* To '用户名'@'%' identified by '密码' with grant option；
     Query OK, 0 rows affected (0.00 sec)
     mysql>exit

2、关闭防火墙（本地服务关闭，连接电脑也需要关闭防火墙）     
    whl@whl: sudo ufw disable 
            防火墙在系统启动时自动禁用

3、测试远程连接    whl@whl:ifconfig    -- 查看当前ip地址    whl@whl:mysql -h192.168.1.104 -uroot -proot
    ERROR 2003 (HY000): Can't connect to MySQL server on 'h192.168.1.104' (111)
     -- 但是通过127.0.0.1或者loclalhost可以连接上，通过固定ip连接出现错误

4、查看配置文件
   whl@whl: sudo vim /etc/mysql/my.cnf
             # bind-address = 127.0.0.1（如果有这句话就注释）
    -- 打开之后自己5.7版本的并没有，然后在找文件
   whl@whl: sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
    -- 发现里面有这句话 # bind-address = 127.0.0.1（进行注释）

5、重启mysql服务   
      whl@whl: service mysql restart
      whl@whl:/etc/mysql$ mysql -h192.168.1.104 -uroot -pXXX
    mysql: [Warning] Using a password on the command line interface can be insecure.
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.17-0ubuntu0.16.04.2 (Ubuntu)

    Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql>

 6、如果连接还出现失败就要查看端口号是否为3306
   mysql> show variables;
          | port                | 3306  
   whl@whl: sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf 
     （修改配置文件中的端口号即可，然后重启服务即可）