Im running a linux server and I wondering if and how could I change the contents of Mysql server to be saved on an external hardrive instead of the place where it is being stored now.
2 Answers
The exact method depends on whether you're using MyISAM or InnoDB tables.
For MyISAM, each database is a directory with the tables, formats, indexes, etc. inside. All these directories are typically in the /var/lib/mysql/ dir. To put in a different volume, you can either change the datadir configuration option or alternatively make /var/lib/mysql/ the mountpoint of that new volume.
For InnoDB, the default is to use a single file in the same /var/lib/mysql/ directory; so mounting a new volume there would work too. Again, a configuration variable can be used to change the exact location, if you want to put all your volumes somewhere else.
Also, InnoDB can use a whole partition or block device as a datastore, skipping the filesystem. In most cases, the supposed speed gains are negligible, so it's usually not advised unless you already have a good volume management environment.
Finally, if you already have some data that you want to move, the easiest is to simply copy the whole /var/lib/mysql/ directory after shutting the mysqld process down and before directing it there.
Comments
In addition to the other answers, I suggest have a MySQL dump procedure (perhaps mysqldump --all-databases), then clearing your MySQL sever and reloading it.
Of course, this is a bit slower than other techniques (directly moving internal MySQL servers), but it has the decisive advantage of exercising your database backup and restore skills. It also enables you to change slightly the database configuration if you wanted to.
I do hope you do have a database backup procedure that you run periodically.
