I have inherited a client site that has an extremely large database for no reason. There is a moderate amount of content and very little enabled modules. However, the database is too large for moving around easily and I want to clean it out.
I have cleared out the standard cache tables, syslog and accesslog.
Are there any other tables I can safely truncate in a standard Drupal site?
Use the backup & migrate module, it comes with good defaults for skipping not necessary data. By default it generates a DB backup without cache, watchdog and some other tables.
If this does not help have a look with phpMyAdmin and tell us which tables have a lot of entries.
Here is a list of tables in Drupal 7 that you can either clear (to reduce the database size) or safely exclude to do a migration (as in the question about How to reduce the locally exported database size to get around my server import limit?):
Usually tables such as search_index and watchdog use a lot of database space, so just eliminating those 2 tables can make a huge difference already.
Check the size of your remaining tables and identify which one of them are the biggest in size.
Typically you might find session tables for which no cleanup procedure is in place. Such tables you can probably also exclude.
To further reduce the challenge as detailed in "How to reduce the locally exported database size to get around my server import limit?", look at the Backup and Migrate module also. Here is a quote from its project page (bold markup added here):
Back up and restore your Drupal MySQL database, code, and files or migrate a site between environments. Backup and Migrate supports gzip, bzip and zip compression as well as automatic scheduled backups.
With Backup and Migrate you can dump some or all of your database tables to a file download or save to a file on the server or offsite, and to restore from an uploaded or previously saved database dump. You can choose which tables and what data to backup and cache data is excluded by default.
And there is even more: if your local environment (e.g. Win or Mac) differs from the OS that the server of your hosted website is running (like Linux), then these differences between OS-es imply potential extra challenges. I've had good experiences with the Backup and Migrate module between different OSes, which didn't cause any problems (worked fine) in situations where the typical MySql export / import failed before.
cache_ prepended or _cache appended are safe to truncate as well, such as features_cache or views_data_object_export_cache etc.
_drupal_session_garbage_collection will keep that table tidy, based on system settings.
In my experience, I purge all of the "cache_*" tables.
I sometimes run this SQL to keep an eye on the growth of the top tables:
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'yourdbnamehere'
ORDER BY table_rows DESC
Watchdog and sessions can also be cleared, keep in mind that all users will be logged out.
With mySQL you can do fun things with the mysqldump program to export the database in its entirety or in parts. For instance this just exports the structure:
mysqldump -u root -pBatteryHorseStapleObviously -h some_host --no-data dbname > ~/dbname.sql
You can then use the 'ignore table' option to further export data, e.g.
mysqldump -u root -pBatteryHorseStapleObviously -h some_host --ignore-table=dbname.huge_table --ignore-table=dbname.massive_table --ignore-table=dbname.useless_table some_host >> ~/dbname.sql
That places the data on the end of the earlier file ignoring some massive tables.
If you then need the massive tables then you can export them to a different file using the above approach, you can then import them in chunks (although f k checks off might be needed).
You did gzip your file before uploading, or is that a silly question?
Use OptimizeDB module to clean the cache tables. The Database Administration is also helpful.
Don't forget to have a backup of databases.
not the super expert on this but sharing my experience... if you're not using the backup and migrate module and manually export them some of the tables you could empty/truncate would be watchdog, cache, cache_menu, cache_block, cache_content, cache_form as they might contain a large amount of cached stuff clearing which I suppose wouldn't hurt... but again this is my experience and I haven't encountered troubles or data loss because of this.
Some ideas:
Additional tables that can be cleared:
Other things that might take up quite some space: - older versions of your content (not possible to clean with a simple truncate). - locales_source and locales_target. If you have languages that are not used anymore or string translations for modules that you don't use anymore. These tables seem to never get cleaned.
Check the example.drushrc.php which list these:
$options['structure-tables']['common'] = array('cache', 'cache_*', 'history', 'search_*', 'sessions', 'watchdog');
$options['skip-tables']['common'] = array('migration_*');
It's safe to clear them in terms of moving the database between different environments (especially when you're working with big databases). However you still need to understand what you're clearing.
{cache_form}are not really correct. This is not a true cache table. It contains in progress form submissions. If you delete all of the data in this table, your user may lose data. The proper thing to do with this table is to expire entries.