mysql数据库开启和关闭远程连接

09
3 月2020
尘缘
没有评论

日前笔者的一台CentOS服务器数据库被恶意删除，留下一串：

To recover your lost Database and avoid leaking it: Send us 0.06 Bitcoin (BTC) to our Bitcoin address 1Kb1KnaiQtNWeRyj1F3eB1yymwxA5ZZyDY and contact us by Email with your Server IP or Domain name and a Proof of Payment. If you are unsure if we have your data, contact us and we will send you a proof. Your Database is downloaded and backed up on our servers. Backups that we have right now: dl_adb_all, dl_ddb_1, dl_dmdb_1, dl_ldb_1, dl_ldb_all, dl_mdb_1, dl_mdb_all, dl_tdb_1, dl_wg, test1. If we dont receive your payment in the next 10 Days, we will make your database public or use them otherwise.

看到这个心里有一万匹草拟马在奔腾，后来分析还是我太大意了，安全措施没做够，在本地开发调试结束之后，没有将mysql数据库关闭远程任意IP访问数据库。删除对应的数据库用户即可现将mysql数据库开启和关闭远程连接方法整理如下：
方法一：
关闭远程连接及权限，就是要撤回授权：

1、登录服务器进入mysql控制台：mysql -uroot -p
[root@root ~]# mysql -uroot -p
Warning: World-writable config file '/etc/my.cnf' is ignored
Enter password: #这里要验证数据库密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 46
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, 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
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

3、撤回任意IP连接root的权限：REVOKE ALL PRIVILEGES ON *.* from ‘root’@’%’;
mysql> REVOKE ALL PRIVILEGES ON *.* from 'root'@'%';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

4、删除这个用户：DELETE FROM user WHERE User= “root” and Host=”%”;
mysql> DELETE FROM user WHERE User= "root" and Host="%";
Query OK, 1 row affected (0.00 sec)

开启远程连接就是重新启用的授权关闭的帐户
1、登录服务器进入mysql控制台：mysql -uroot -p
[root@root ~]# mysql -uroot -p
Warning: World-writable config file '/etc/my.cnf' is ignored
Enter password: #这里要验证数据库密码
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 46
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, 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
mysql> use mysql
Database changed

3、选中root帐户：
update user set host = '%' where user = 'root'; #这里有可能会报错，因为已经默认有了"%"任意主机的配置，所以可直接进入下面的操作，给任意IP访问授权全部权限

4、 查看mysql所有用户名：select host, user from user;
mysql> select host, user from user;
+-----------+------+
| host | user |
+-----------+------+
| % | root |
| 127.0.0.1 | root |
| localhost | |
| root | |
+-----------+------+
4 rows in set (0.00 sec)

5、远程连接授权：GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '这里是root密码' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '这里是root密码' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

6、刷新权限表，使配置生效:FLUSH PRIVILEGES;
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

方法二：
登录 mysql： mysql -u root -h localhost -p
关闭远程连接，只能本地连接，也可以通过以下步骤操作：
use mysql #选中mysql数据库

update user set host=’localhost’ where user=’root’; #将host设置为localhost表示只能本地连接mysql

flush privileges; #刷新权限表，使配置生效

开启远程连接
use mysql #选中mysql数据库

update user set host=’%’ where user=’root’ and host=’localhost’; #将host设置为%表示任何ip都能连接mysql，当然您也可以将host指定为某个ip

flush privileges; #刷新权限表，使配置生效

CentOS, mysql, 原创