前言:
安装过程不再赘述, 直接说问题, MySQL 的远程连接需要解决两个问题: 1. 允许 root 用户远程连接. 2. 允许任意 ip 远程连接数据库. 当然, 在测试和解决问题之前, 得首先保证你的数据库与远程主机之间的网络通信没有问题, 简单的来说, 就是互相 ping 通, 其次, 为了避免防火墙的干扰, 将本地主机和数据库主机的防火墙都关闭, 当然, 生产环境下防火墙肯定是要打开的, 并且需要额外的安全配置.
问题解决:
1. 新安装的数据库默认是需要初始化的, 在数据库服务启动的情况下, 使用下面的命令来进行初始化.
- [[email protected] ~]# mysql_secure_installation
- New password:
- Re-enter new password:
- Password updated successfully!
- Reloading privilege tables..
- ... Success!
- By default, a MariaDB installation has an anonymous user, allowing anyone
- to log into MariaDB without having to have a user account created for
- them. This is intended only for testing, and to make the installation
- go a bit smoother. You should remove them before moving into a
- production environment.
- Remove anonymous users? [Y/n]
- ... Success!
- Normally, root should only be allowed to connect from 'localhost'. This
- ensures that someone cannot guess at the root password from the network.
- Disallow root login remotely? [Y/n] n #如果是为 root 用户配置远程登陆, 这里需要选择 n, 不选择禁止 root 用户远程登陆, 别的选择都无关紧要.
- ... skipping.
- By default, MariaDB comes with a database named 'test' that anyone can
- access. This is also intended only for testing, and should be removed
- before moving into a production environment.
- Remove test database and access to it? [Y/n]
- - Dropping test database...
- ... Success!
- - Removing privileges on test database...
- ... Success!
- Reloading the privilege tables will ensure that all changes made so far
- will take effect immediately.
- Reload privilege tables now? [Y/n]
- ... Success!
- Cleaning up...
- All done! If you've completed all of the above steps, your MariaDB
- installation should now be secure.
- Thanks for using MariaDB!
- [[email protected] ~]# systemctl restart mariadb #完成初始化之后, 重启服务.
2. 允许 root 用户远程连接与允许任意 ip 远程连接数据库都是可以通过在数据库里执行一条命令来实现的.
这里分为两种情况:
1)新建 admin 用户远程连接 MySQL 数据库(新建任意用户, 以 admin 为例)
- grant all on *.* to admin@'%' identified by '123456' with grant option;
- flush privileges;
允许任何 ip 地址 (% 表示允许任何 ip 地址) 的电脑用 admin 帐户和密码 (123456) 来访问这个 MySQL server.
注意 admin 账户不一定要存在.
2)支持 root 用户允许远程连接 MySQL 数据库
- grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
- flush privileges;
需要注意的是, 为 root 用户配置远程登陆, 密码需要和之前配置的密码一致.
3.Ubuntu 系统的额外配置.
Ubuntu 系统的 my.cnf 文件在 VIM /etc/MySQL/MySQL.conf.d/mysqld.cnf 中, 注释掉其中的
bind-address = 127.0.0.1
CentOS 系统的配置文件中默认没有该行.
检查的办法也很简单, 使用 netstat -an | grep 3306 查看端口的连接信息.
如图所示则为允许任意 IP 连接.
来源: http://www.bubuko.com/infodetail-3339614.html