Migration and database pruning

Posted by spade on August 29, 2008 at 11:15pm

Hello,

Since some ISPs limit the size of the file that can be imported into a database through phpmyadmin at a time, it would be helpful to know which tables can be disposed off, in order to possibly get it done in one scoop.

I have read that one should empty the cache table before dumping the database and moving it to another site, but what about the following tables, can't they be dropped as well:
cache_content
cache_filter
cache_menu
cache_page
cache_views

Will these tables be recreated automatically, or do I need to copy them?
locales_source
locales_target

This one can be dropped, if one doesn't care for revisions of the content:
node_revisions

Will this not be recreated as well (it takes up substantial space)?
search_dataset
search_index

Wo needs to copy this (especially since it contains full URLs and is quite big as well)?
watchdog

Are there other tables that could be disposed off without jeopardizing the integrety of the site?

Kind regards,

Frank

Comments

Don't "drop" any tables,

Posted by cog.rusty on August 30, 2008 at 12:45am

Don't "drop" any tables, because they won't be created again. Just "truncate" them (empty them).

You can empty:
- all the cache tables
- the session table.
- the watchdog table -- it can get really large
- the search tables -- they will be rebuilt gradually whenever cron runs

You don't want to empty the locale tables if you have translations which you want to keep.

If you have any content, don't empty the node_revisions table! That is where your content is, not in the nodes table! If you want to get rid of old revisions, try the http://drupal.org/project/revision_deletion module.

Howto automate truncating tables

Posted by spade on August 30, 2008 at 11:11am

Thanks, that is good news (about what I expected but didn't find documented). It reduced the size of the file by 50%.

Is there a way that supports this process, like in a batch job?

Thanks also for the tipp.

Yes, there some tools

Posted by GN on August 30, 2008 at 12:01pm

In phpmyadmin (or another similar tool), you can export only the structure of cache*, search*, etc. tables, without the data. And you need to do it only once, and then keep this small dump file intact for the future.

As for automation - there is a very compact and convenient tool called Sypex Dumper; its user interface is currently available only in Russian, but there is a non-official English translation, too --
see e.g. http://ztz.110mb.com/dumper.en.zip.
When you run this tool, you can export/import all tables in a database (by default), or define a mask for table inclusion/exclusion.
For example,
^cache*, ^search*, ^session, ^watchdog
is a mask to export/import all tables except for cache*, search*, session, and watchdog.

Thanks

Posted by spade on August 30, 2008 at 6:46pm

Hi GN,

I saw you talk about this somewhere else and this post saved me checking out the other tools you mentioned.

Thanks a bunch and keep up the good work.

Kind regards,

Frank

Thank you, too -- your

Posted by GN on August 31, 2008 at 5:34pm

Thank you, too -- your question motivated me to find out that this tool is already translated into English (which I did not know before). :))
Keep in mind that Sypex Dumper is a very simple script, and it does not have any interface to download/upload sql dump files to/from your local computer, it only saves them or looks for them in its /dumper/backup directory -- so that you have to upload them there on your own if you need to import an sql dump into a database.
Its advantage is that it works much faster than phpmyadmin, and is able to import really big files without running into timeout, or memory limitations, or php upload limit.