I have a hard drive that is filling up and I'm going to add another one. On that hard drive I have a MySQL DB and I want to know if placing another HD can, with that same instance of MySQL, store tables on the new disk, or if I have to have 2 instances of MySQL running on the same PC to do so.
Marc's answer is correct. However, I would choose to move all the table subdirectory to your new disk (the following example would work in linux)
Since you have mounted the new disk in /var/newdisk , you would have to
that would be:
mkdir /var/newdisk/mysql_new
sudo service mysql stop
rsync -avzr /var/lib/mysql/ /var/newdisk/mysql_new/
then:
mysqld.cnf ) and just in case, by copying it to mysqld.cnf.bak in case something goes wrong. datadir parameter in that file setting or replacing the existing parameter:
datadir=/var/newdisk/mysql_new/
(with sudo)
sudo service mysql start
Your original directory is still intact so you can step back by resetting the original configuration.
Good ZiPoTaTo,
If you can, to make this option you must use the option that has MYSQL (with InnoDB ) to be able to create and store the tables in another unit that is not the default directory ( file_per_table ).
First you should put the variable in ON state if it is not:
//Miramos el estado de la variable
SHOW VARIABLES LIKE 'innodb_file_per_table';
//Si no está en ON (1) la ponemos en ON
SET GLOBAL innodb_file_per_table=1;
An example of how to create a table in a different unit from the one assigned by default is:
CREATE TABLE nombreTabla (col1 INT PRIMARY KEY) DATA DIRECTORY = '/path';
This way you are indicating that the table you are creating is going to be stored in the path provided.