Linux – Creating more space for MySQL with new mount

centoslinuxmountMySQLrhel5

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).

Best Answer

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.

Related Topic