We often have very large databases we want to create in mysql.<\/p>\n
If we don\u2019t 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.<\/p>\n
This can fairly easily be done by using innodb_file_per_table parameter in your my.cnf and using symbolic links.\u00a0 The only tricky part is getting it to work under SELinux.\u00a0 ( Don\u2019t turn off SELINUX like other people say )\u00a0 just follow these instructions to get it to allow what you want.<\/p>\n
Steps to add new database that will use external storage:<\/p>\n
Create the database using:<\/p>\n
mysqladmin -uroot -p [pw] create newdbname<\/p>\n
Shutdown mysql<\/p>\n
\/etc\/init.d\/mysqld stop<\/p><\/blockquote>\n
Edit \/etc\/my.cnf\u00a0 adding line for innodb_file_per_table under the mysqld section<\/p>\n
[mysqld]<\/p>\n
innodb_file_per_table<\/p><\/blockquote>\n
While db is shut down \u2014 move the directory for the new database to a partition with space.<\/p>\n
mv \/var\/lib\/mysql\/newdbname \/bigpartition\/mysql\/newdbname<\/p><\/blockquote>\n
Create a symbolic link to the new location ( mysql looks only in \/var\/lib\/mysql for databases on startup)<\/p>\n
cd \/var\/lib\/mysql<\/p>\n
ln -s \/bigpartition\/mysql\/newdbname newdbname<\/p>\n
ls -l \/bigpartition\/mysql\/newdbname<\/p><\/blockquote>\n
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 )<\/p>\n
chcon -c -t mysqld_db_t \/bigpartition\/mysql<\/p>\n
chcon -c -t mysqld_db_t \/bigpartition\/mysql\/newdbname<\/p><\/blockquote>\n
Re-start the MYSQL database<\/p>\n
\/etc\/init.d\/mysql start<\/p><\/blockquote>\n
Restore your database dump or create new tables.\u00a0 The command I use is:<\/p>\n
nohup time gunzip < backup.dmp.gz | mysql -uroot -p[dbpw] newdbname > restore.err 2>&1 &<\/p><\/blockquote>\n","protected":false},"excerpt":{"rendered":"
We often have very large databases we want to create in mysql. If we don\u2019t 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 … Continue reading