how to move a mysql database to another mount point

Question

I have a MySQL database, it is getting larger and larger and I want to move the whole database to another mount point, where I have enough storage. I want my current data to be transferred, and that new data get saved to the new position.

software stack: MySQL 5 running on FreeBSD 6

Answer 1

If you can tolerate the database being down for the move:

1. Shutdown MySQL
2. rsync the files to the new mount point
3. either:
   1. change mysql.conf to tell MySQL where to find the files, or
   2. make the current directory a symlnk to the new one
4. restart MySQL

Answer 2

• Stop mysqld
• Copy /var/lib/mysql (or whatever $datadir in my.cnf was set to) to the new location
• Either mount the new location under the old $datadir or modify the MySQL configuration in the file my.cnf to reflect the new location.
• Start mysqld

There's no magic involved. ;) But you should make sure, that you copy all permissions with the files of MySQL.

Answer 3

Of course other answers are valid. But if you want to keep the default configuration, do following:

1. stop mysqld
2. mv /var/lib/mysql /var/lib/mysql.backup
3. mount your new partition under /var/lib/mysql
4. cp -r /var/lib/mysql.backup /var/lib/mysql
5. start mysqld