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