MySQL Server Maintenance

As shutting down every single web app seems too work intensive I thought about taking offline only the database server.

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 /etc/mysql/my.cnf

...
[mysqld]
# bind_address = xxx.xxx.xxx.xxx
skip-networking
...

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.

Pro:
  • Only one server has to be taken offline
  • Data will stay consistent throughout the move and checks
Con:
  • Web apps will fail with some ugly PHP error codes
Update:

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.

Veröffentlicht von

Ruggero De Pellegrini

PHOTOGRAPHER, DEVELOPER & INDIVIDUALIST

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.