暂时禁用mysql远程访问 [英] temporarily disable mysql remote access
问题描述
我需要在Linux服务器上的每晚mysql维护期间取消远程访问,以便在此期间没有人可以查询数据库.我不能做SERVICE MYSQL STOP,因为那样我就不能做我需要做的事情(截断并重建几个表).是否可以在短时间内关闭外部访问?
提前致谢.
I need to disble remote access during nightly mysql maintenance on Linux server so that no one can query the database during that time. I can't do SERVICE MYSQL STOP because then I couldn't do what I needed to do (truncate and rebuild a couple tables). Is there a way to turn off external access for a short time?
Thanks in advance.
推荐答案
这是一种无需触摸操作系统中任何内容的好方法:
Here is a great way without touching anything in the OS:
第1步)将所有用户导出到SQL文件,如下所示:
Step 1) Export all users to SQL file like this:
mysql -h本地主机-u根 -p rootpassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR''',user,'''@''',host,''';')FROM mysql.user WHERE user<>''"| mysql -hlocalhost -uroot -prootpassword --skip-column-names -A | sed's/$/;/g'>/root/MySQLGrants.sql
mysql -h localhost -u root -p rootpassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hlocalhost -uroot -prootpassword --skip-column-names -A | sed 's/$/;/g' > /root/MySQLGrants.sql
第2步)禁用除root @ localhost外的所有用户
Step 2) Disable all users except root@localhost
DELETE FROM mysql.user WHERE CONCAT(user,host) <> 'rootlocalhost';
FLUSH PRIVILEGES;
第3步)进行维护
第4步)重新加载赠款
mysql -h localhost -u root -p rootpassword < /root/MySQLGrants.sql
尝试一下!!!!
PS
服务mysql重新启动--skip-networking
service mysql restart --skip-networking
仍然是最简单,最快的方法,再加上在维护之前注销所有连接
would still be the simplest and fastest way plus logging off all connections before maintenance
在维护后只需运行服务mysql重新启动
just run service mysql restart after your maintenance
这篇关于暂时禁用mysql远程访问的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!