temporarily disable mysql remote access

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.

-------------Problems Reply------------

Here is a great way without touching anything in the OS:

Step 1) Export all users to SQL file like this:

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

Step 2) Disable all users except [email protected]

DELETE FROM mysql.user WHERE CONCAT(user,host) <> 'rootlocalhost';

Step 3) Perform your maintenance

Step 4) Reload the Grants

mysql -h localhost -u root -p rootpassword < /root/MySQLGrants.sql

Give this a Try !!!!


service mysql restart --skip-networking

would still be the simplest and fastest way plus logging off all connections before maintenance

just run service mysql restart after your maintenance

Disable the login credentials used by the remote apps?

Firewall the MySQL port so it can't be accessed by the remote apps?

Category:mysql Views:0 Time:2011-04-18
Tags: mysql linux

Related post

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.117 (s). 11 q(s)