At my job I’m running a MySQL database server with about 45 smallish databases for a load of web apps running on different webservers. I decided to move the database files to a NetApp volume in order to configure snapshot backups with Zmanda’s zrm-mysql-enterprise. We have a maintenance window every month where the system may be down for a while. So this is no procedure for 24/7 shops.
As shutting down every single web app seems too work intensive I thought about taking only the database server offline. The best way to do this is to edit
# bind_address = xxx.xxx.xxx.xxx
After this change, I shutdown MySQL with
/etc/init.d/mysql stop, moved the data files and logs to the new volume and changed the data file path in my.cnf.
skip-networking ensures that mysql-server only listens to local socket connections the next time I start MySQL again. I now can check if the move was successful and do some load testing with some select statements via CLI.
After everything is ready to go back online I removed skip-networking again an uncommented the bind_address line again.
Don’t forget to restart MySQL after every change or better stop it for serious datafile movement.
- Only one server has to be taken offline
- Data will stay consistent throughout the move and checks
- Web apps will fail with some ugly PHP error codes
I decided to to stop all Apache processes on the webservers as well. Still I think it’s good practice to ensure MySQL is offline by ’skip-networking‘ during maintenance work.