0

I'm using a cloud CentOS 5 instance and I realized that I left the default partitioning in the beginning. Everything on / is mounted already on hda that is about 10GB. The other drive (sda) is about 90GB used for backups but I can free that up if needed.

My MySQL DB is now getting bigger and I'm thinking about mounting one of the sda drives onto /var/lib/mysql. Is this the usual practice for mysql (to give it its own partition)?

What do I need to do to make sure so that this move is painless and without data-loss? The server is live with users reading and writing to the DB, so I figure I will do it late night and turn off mysqld first. Then I'm planning to move everything in /var/lib/mysql into some temp dir and then mounting sda1 onto /var/lib/mysql and then moving the contents from the temp dir back into the new mounted /var/lib/mysql dir. Is this the right approach? Are there some other ways to give MySQL more space that I have not thought of?

I'm paranoid that a simple mv might miss some hidden files inside the dir (I apologize for my ignorance of how mv works).

lamp_scaler
  • 577
  • 1
  • 5
  • 18

3 Answers3

2

Seems like a good approach. Some things to look out for in the moving. If mysqld isnt running as root, make sure that it has permissions to RW on the new partition. Don't forget to edit /etc/fstab so that you dont loose the mount on reboot.

csfreak
  • 41
  • 3
1

This is what I did:

  1. stop MySQL server
  2. mount /dev/sda1 to /data (remember adding it to /etc/fstab)

  3. create directory structure:

    mkdir -p /data/var/lib

  4. move MySQL datadir to new folder:

    mv /var/lib/mysql /data/var/lib/

  5. change the owner:

    chown -R mysql:mysql /data/var/lib/mysql

  6. create a symlink to the old location:

    ln -s /data/var/lib/mysql /var/lib/mysql

  7. open 2 consoles (I like the splitting feature in GNOME Terminator) one start MySQL server while another tail -f /var/log/mysqld.log.

This avoid to move datadir twice and you can use /dev/sda1 for the other purposes.

quanta
  • 50,327
  • 19
  • 152
  • 213
1

If you want to make it a separate mount, I agree with quanta's suggestion of mounting the new drive to /data and symlinking /var/lib/mysql -> /data/mysql. This way you can use the /data drive for other things too without polluting the mysql directory.

Another alternative, if you're using LVM, is to add a new drive to the logical volume and extending the filesystem.

In my cloud server environment, I get each new machine with a 10GB root(/) partition and then I add a new 40GB partition to it. Here's the process I follow:

pv="/dev/sdb"
vg="VolGroup00"
lv="VolGroup00-LogVol00"

sudo /usr/sbin/pvcreate $pv
sudo /usr/sbin/vgextend $vg $pv
sudo /usr/sbin/lvextend -l +100%FREE /dev/mapper/$lv
sudo /sbin/resize2fs /dev/mapper/$lv

In my case, the root partition will now be a 50GB filesystem instead of only 10GB

These defaults typically work on a CentOS-5 box that was built with LVM, but you may need to modify the variables to match your setup.

joe miller
  • 106
  • 5
  • yea the LVM was not setup on the other drives provided by our host. also, if using LVM, doesn't data get fragmented pretty easily? – lamp_scaler Aug 10 '11 at 05:09