We often have very large databases we want to create in mysql.
If we don’t have enough space in the /var/lib/mysql partition it is handy to be able to specify that a specific database be stored on another drive.
This can fairly easily be done by using innodb_file_per_table parameter in your my.cnf and using symbolic links. The only tricky part is getting it to work under SELinux. ( Don’t turn off SELINUX like other people say ) just follow these instructions to get it to allow what you want.
Steps to add new database that will use external storage:
Create the database using:
mysqladmin -uroot -p [pw] create newdbname
Shutdown mysql
/etc/init.d/mysqld stop
Edit /etc/my.cnf adding line for innodb_file_per_table under the mysqld section
[mysqld]
innodb_file_per_table
While db is shut down — move the directory for the new database to a partition with space.
mv /var/lib/mysql/newdbname /bigpartition/mysql/newdbname
Create a symbolic link to the new location ( mysql looks only in /var/lib/mysql for databases on startup)
cd /var/lib/mysql
ln -s /bigpartition/mysql/newdbname newdbname
ls -l /bigpartition/mysql/newdbname
Fix the context on the newly created directories or you the database will not be visible when mysql restarts ( SELinux will prohibit mysql from using files in these directories )
chcon -c -t mysqld_db_t /bigpartition/mysql
chcon -c -t mysqld_db_t /bigpartition/mysql/newdbname
Re-start the MYSQL database
/etc/init.d/mysql start
Restore your database dump or create new tables. The command I use is:
nohup time gunzip < backup.dmp.gz | mysql -uroot -p[dbpw] newdbname > restore.err 2>&1 &