469,922 Members | 2,159 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,922 developers. It's quick & easy.

Multiple Data Folders

Hi,

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?

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?

Kind Regards,
Mark
Jul 23 '05 #1
1 2936
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.
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Tank | last post: by
4 posts views Thread by Ken Madden | last post: by
reply views Thread by Jaco Karsten | last post: by
6 posts views Thread by Ludvig | last post: by
9 posts views Thread by Graham | last post: by
reply views Thread by BillE | last post: by
reply views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.