Home / server / Questions / 484535
In Process
JohnnyFromBF
Asked: 2013-03-05 09:08:52 +0800 CST

How to move the MySQL datadir with minimal downtime?

  • 772

Let's say I have partition sda2 mounted on /var and it's almost full. I have a second disk sdb whose partition sdb1 I want to mount as /var.

Now since a running service (mysql) is using /var all the time, is there a way to switch without stopping the service?

linux

4 Answers

  1. Setup a second server as a slave. You probably want a master/master setup so either host can take writes. Move the IP over to this second machine. Now you can do your maint on the first machine. This will minimize the downtime for the MySQL service.

    • 9

  2. I'm sorry to say that the answer is no, there's not. If you start moving the filesystem while mysql is using it, you'll get your database entirely hosed.

    • 2

  3. First and foremost: Do like toppledwagon said and set up a slave server.

    But as a secondary point: You don't have to move MySQL (unless it's really the only thing taking up substantial space on /var): If you have lots of junk in /var/log, /var/spool, /var/www, etc. you can move those to separate filesystems too.

    In reality MySQL is probably your 1000-pound gorilla on the partition, but if you're REALLY crunched for space (and just deleting other stuff isn't an option) you can move it around as a stopgap while you get your slave server up.

    • 2

  4. Agreed with JennyD's answer, there is no way to do it without shutting the database down.

    However if the mysql database itself is not that large and the system's I/O speed reasonably high you could easily do this withy minimal down time, just by chaining a few commands together and running it at a time usage is low. I've successfully done this a number of times for critical mysql databases. Perhaps announce the maintenance and do a few test runs on a test system to find out how long it takes.

    Something like:

    /etc/init.d/mysql stop && mv /var/lib/mysql/database /mnt/newdisk/mysql/ && ln -s /mnt/newdisk/mysql/database /var/lib/mysql/database && /etc/init.d/mysql start

    It's a bit crude but effective. And practical in many cases (probably in all but the most mission critical "can't ever be down" systems). The "&&" in the command makes sure that if a previous command fails it will not continue.

    • 1