How to move the MySQL datadir with minimal downtime?

Question

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?

score 8 · Answer 1 · answered Mar 04 '13 at 18:39

8

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.

answered Mar 04 '13 at 18:39

toppledwagon

4,245
25
15

1

+1 for "set up a slave" *in general* -- Slave servers will save your bacon in SO. MANY. WAYS. – voretaq7 Mar 05 '13 at 02:26

score 3 · Answer 2 · answered Mar 04 '13 at 17:16

3

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.

answered Mar 04 '13 at 17:16

Jenny D

27,780
21
75
114

score 2 · Answer 3 · edited Apr 13 '17 at 12:14

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.

score 1 · Answer 4 · answered Mar 05 '13 at 02:21

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.

How to move the MySQL datadir with minimal downtime?

4 Answers4