3

As a fan of svelte sql backups I've successfully used, for several years this bash script for backing up my Drupal site's databases. By excluding "noisy" tables, I've seen remarkable reduction in the size of the resulting sql file. I am so confident of it, I nearly used it for my Drupal 8 project. However, I presume the schema has undergone some rather significant changes.

Here are the following tables my current script excludes, expressed as a grep pattern:

echo "Starting to dump the table data."
TABLES2=`echo "$TABLES" | grep -Ev "^(accesslog|cache.*|flood|search_.*|semaphore|sessions|feeds_log|watchdog)$"`
mysqldump --complete-insert --disable-keys --single-transaction --no-create-info -u ${USER} --password=${PASS} -h ${HOST} ${DB} ${TABLES2} >> ${DB}.${DATE}.sql

I'm curious how Drupal 8 changes which tables are safe for exclusion. Note that I'd rather err on the "safe" side. So, for example, sessions (as I understand them in D7) have limited value for backup, but that's for my general application. Your mileage may vary. Also, sorry for the unresponsive table, but there is apparently a row length limit on code posts.

| table name:                  | backup? | | table name:                  | backup? |  
| batch                        | Yes     | | node__comment                | Yes     |  
| block_content                | Yes     | | node__field_image            | Yes     |  
| block_content__body          | Yes     | | node__field_tags             | Yes     |  
| block_content_field_data     | Yes     | | node_access                  | Yes     |  
| block_content_field_revision | Yes     | | node_field_data              | Yes     |  
| block_content_revision       | Yes     | | node_field_revision          | Yes     |  
| block_content_revision__body | Yes     | | node_revision                | Yes     |  
| cache_bootstrap              | No      | | node_revision__body          | Yes     |  
| cache_config                 | No      | | node_revision__comment       | Yes     |  
| cache_container              | No      | | node_revision__field_image   | Yes     |  
| cache_data                   | No      | | node_revision__field_tags    | Yes     |  
| cache_default                | No      | | queue                        | Yes     |  
| cache_discovery              | No      | | router                       | Yes     |  
| cache_dynamic_page_cache     | No      | | search_dataset               | Yes     |  
| cache_entity                 | No      | | search_index                 | Yes     |  
| cache_menu                   | No      | | search_total                 | Yes     |  
| cache_render                 | No      | | semaphore                    | Yes     |  
| cache_toolbar                | No      | | sequences                    | Yes     |  
| cachetags                    | No      | | sessions                     | Yes     |  
| comment                      | Yes     | | shortcut                     | Yes     |  
| comment__comment_body        | Yes     | | shortcut_field_data          | Yes     |  
| comment_entity_statistics    | Yes     | | shortcut_set_users           | Yes     |  
| comment_field_data           | Yes     | | taxonomy_index               | Yes     |  
| config                       | Yes     | | taxonomy_term_data           | Yes     |  
| file_managed                 | Yes     | | taxonomy_term_field_data     | Yes     |  
| file_usage                   | Yes     | | taxonomy_term_hierarchy      | Yes     |  
| flood                        | Yes     | | url_alias                    | Yes     |  
| history                      | Yes     | | user__roles                  | Yes     |  
| key_value                    | Yes     | | user__user_picture           | Yes     |  
| key_value_expire             | Yes     | | users                        | Yes     |  
| menu_link_content            | Yes     | | users_data                   | Yes     |  
| menu_link_content_data       | Yes     | | users_field_data             | Yes     |  
| menu_tree                    | Yes     | | watchdog                     | No      |  
| node                         | Yes     | 
| node__body                   | Yes     | 
3

I would exclude batch, anything starting with cache_, flood, queue, sessions, sequences, semaphore, watchdog, although one may argue about the batch, flood, queue, sequences and semaphore tables.

Also don't forget to export their schema.

  • 2
    queue, and sequences shouldn't be excluded. A specific site might not need it for much, but for some modules, queue is important, and losing it might result in losing processing data. sequence is used for db_next_id(), losing it might result in errors if drupal is trying to re-use already used ID's. I think most sites also don't want to lose their user sessions? – Berdir Nov 15 '15 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.