Mark Everett wrote:
I am currently running out of space on one of my database servers. Is
it possible to move the relevant files for tables onto another drive
and instuct MySql to use both folders for it's data?
So basically can you have mulitple data folders setup and if so how is
this configured?
I've never found a way to have one mysql instance use multiple data
folders. You can specify only one datadir to the mysqld server, either
by command-line option or an entry in my.cnf.
However, you can use symbolic links to cause the actual storage for a
database or a table to be stored on another filesystem. See
http://dev.mysql.com/doc/mysql/en/symbolic-links.html
On another note I could delete some of the older data but the database
does not seem to reduce in size after my delete queries. How do I go
about truncating the database to free this space physically?
See
http://dev.mysql.com/doc/mysql/en/in...agmenting.html
This page is specific to InnoDB, but I assume some of the issues are
common to MyISAM tables too.
Suppose you have a table that is stored in a file 2 gigabytes in size,
and you delete some records that happen to be stored near the beginning
of the file.
Should the mysql engine copy content from the remainder of the file to
close the gap left by the deletes? No, that could take many minutes to
resolve a simple delete operation.
Instead, the space left by the deleted rows is marked as available, and
the next time you insert data, the space could be re-used (if the new
data fit in that gap).
One way to re-pack a table is to dump it out with mysqldump, drop the
table in your database, and then re-load from the dumped data. You can
do this one table at a time if necessary. (you might want to try some
experiments with a small test database to make sure you are comfortable
with these steps.)
If you use InnoDB, you can repack the table with a dummy alter table:
ALTER TABLE tbl_name TYPE=InnoDB;
Regards,
Bill K.
