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:
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?