Moving the MySQL's datadir directory.

By default, MySQL's datadir is placed in the /var/lib/mysql directory. However, if you are planning on using MySQL tables to store a lot of data and your /var partition is small, it might cause you problem at a later stage. In such a scenario, it is better to move the MySQL's datadir to another partition (like /home.

The steps are

1. Stop your mysql server before starting this operation
2. Create the directories that will be new datadir
3. chown the directory to the mysql:mysql user
4. copy the files from the old datadir to the new location. However, make sure that the files named ib_arch_log_0000000000, ib_logfile0 etc. are not copied to the newer location.
5. Make sure that the files and directories are owned by mysql user
6. Make changes in the my.cnf to point the new datadir.
7. Restart the MySQL database
8. You might need to do varying degree of troubleshooting to get the server working if there is some problem
9. Create a new database and verify that the files for this database are getting created in the new datadir
10. After the server is running for a few days properly, get rid of the old data.
11. Sleep

I tested these steps on MySQL 4.0.24 and all my tables were MyISAM type.