temporarily disable mysql remote access

Question

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.

Answer 1

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 root@localhost

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

Step 3) Perform your maintenance

Step 4) Reload the Grants

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

Give this a Try !!!!

PS

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