Mysql开启远程连接(添加远程连接用户)

在默认情况下,mysql是禁止远程连接的,而服务器又不是随随便便就要登录的,所以就要开启远程连接。
开启数据库远程连接的时候,记得要把服务器的安全组也开放数据库的端口(mysql默认3306),不然也会出现无法访问的情况

创建远程连接数据库的用户

  1. 登录mysql

    [root@VM_149_46_centos etc]# mysql -u root -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 15
    Server version: 5.7.21 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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.
  2. 创建远程连接用户

    mysql> use mysql
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    
    mysql> CREATE USER 'cwm'@'%' IDENTIFIED BY 'Cwmniwo,1';
    Query OK, 0 rows affected (0.01 sec)

    为用户创建一个数据库试试

    mysql> CREATE DATABASE cwm1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
    Query OK, 1 row affected (0.00 sec)
  3. 授权

    mysql> GRANT ALL PRIVILEGES ON `cwm1`.* TO 'cwm'@'%' ;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> FLUSH PRIVILEGES;
    Query OK, 0 rows affected (0.00 sec)
  4. 测试

    mysql> exit;
    Bye
    [root@VM_149_46_centos etc]# mysql -u cwm -p
    Enter password: 
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 16
    Server version: 5.7.21 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, 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> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | cwm1               |
    +--------------------+
    2 rows in set (0.00 sec)

    已经连接成功并可以查询,经过测试,本地使用navicat也可以连接。

以下是修改配置文件达到目的的步骤(请查看参考连接进行配置)

  1. 查找配置文件在哪里
    先要查找数据库安装在了哪里

    [root@VM_149_46_centos etc]# which mysqld
    /usr/sbin/mysqld

    这里可以得到数据库安装在了/usr/sbin/mysqld

    [root@VM_149_46_centos etc]# /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
    Default options are read from the following files in the given order:
    /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

    这里可以得出配置文件的地址在 /etc/my.cnf/etc/mysql/my.cnf/usr/etc/my.cnf~/.my.cnf

  2. 修改配置文件
  3. 重启数据库服务
  4. 测试

参考链接:
http://www.jb51.net/article/96009.htm
http://www.cnblogs.com/linjiqin/p/5270938.html
https://www.cnblogs.com/hfdp/p/6088288.html
http://blog.csdn.net/piaocoder/article/details/53704126

相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页