5

I have 4 environments between my local, dev, staging, and production.

A large amount of content is published to the staging environment on a daily basis, so that is the center of my workflow. Once content is QA'ed in staging it is then sql-sync'ed to production. This works fine at the moment. However, we're now going to allow for user registration/user generated content. Is there a way to push these content updates with sql-sync and never overwrite certain tables? Even more specifically, is there something I can place in drushrc.php that will always skip a list of tables across all environments?

Improve this question

3 Answers 3

Reset to default
9

YES

If you know which tables you want to transfer

--tables-list

A comma-separated list of tables to transfer. Optional.

If you know which tables you want to skip

--skip-tables-list

A comma-separated list of tables to exclude completely. Optional.

And here is how I know this:


$ drush help sql-sync
Copy and import source database to target database. Transfers via rsync.

Examples:
 drush sql-sync @prod @dev                 Copy the DB defined in sites/prod to the DB in sites/dev.


Arguments:
 from                                      Name of subdirectory within /sites or a site-alias.
 to                                        Name of subdirectory within /sites or a site-alias.


Options:
 --cache                                   Skip dump if result file exists and is less than "cache" hours old. Optional; default is 24 hours.
 --create-db                               Create a new database before importing the database dump on the target machine.
 --db-su=                            Account to use when creating a new database. Optional.
 --db-su-pw=                         Password for the "db-su" account. Optional.
 --dump-dir                                Directory to store sql dump files in when --source-dump or --target-dump are not used.  Takes precedence over --temp.
 --no-cache                                Do not cache the sql-dump file.
 --no-dump                                 Do not dump the sql database; always use an existing dump file.
 --no-ordered-dump                         Do not pass --ordered-dump to sql-dump.  sql-sync orders the dumpfile by default in order to increase the efficiency of rsync.
 --sanitize                                Obscure email addresses and reset passwords in the user table post-sync. Optional.
   --sanitize-password                     The password to assign to all accounts in the sanitization operation, or "no" to keep passwords unchanged.  Default is "password".
   --sanitize-email                        The pattern for test email addresses in the sanitization operation, or "no" to keep email addresses unchanged.  May contain replacement patterns %uid,
                                           %mail or %name.  Default is "user+%uid@localhost".
   --confirm-sanitizations                 Prompt yes/no after importing the database, but before running the sanitizations
 --skip-tables-key                         A key in the $skip_tables array. @see example.drushrc.php. Optional.
 --skip-tables-list                        A comma-separated list of tables to exclude completely. Optional.
 --source-database                         A key in the $db_url (D6) or $databases (D7+) array which provides the data.
 --source-db-url                           Database specification for source system to dump from.
 --source-dump                             Path to dump file. Optional; default is to create a temporary file.
 --source-remote-host                      Remote machine to run sql-dump file on. Optional; default is local machine.
 --source-remote-port                      Override sql database port number in source-db-url. Optional.
 --structure-tables-key                    A key in the $structure_tables array. @see example.drushrc.php. Optional.
 --structure-tables-list                   A comma-separated list of tables to include for structure, but not data. Optional.
 --tables-key                              A key in the $tables array. Optional.
 --tables-list                             A comma-separated list of tables to transfer. Optional.
 --target-database                         A key in the $db_url (D6) or $databases (D7+) array which shall receive the data.
 --target-db-url
 --target-dump
 --target-remote-host
 --target-remote-port
 --temp                                    Use a temporary file to hold dump files.  Implies --no-cache.
Improve this answer
1
  • make sure you dont put spaces after the commas if you're transferring more than 1 table. If it has spaces it doesn't work
    – Ronnie
    Aug 8, 2019 at 21:47
4

Perhaps --skip-tables-list is of interest to you. drush help COMMAND is your friend. You can set a global option in a drush.rc file to make this option hardcoded for every sql-sync for certain Site Aliases if you want. Because heaven forbid you sql-sync and forget to skip tables on some Friday at 5pm :(.

drush help sql-sync
Copy and import source database to target database. Transfers via rsync.

Examples:
 drush sql-sync @prod @dev                 Copy the DB defined in sites/prod to the DB in sites/dev.

Arguments:
 from                                      Name of subdirectory within /sites or a site-alias. 
 to                                        Name of subdirectory within /sites or a site-alias.

Options:
 --cache                                   Skip dump if result file exists and is less than "cache" hours old. Optional; default is 24 hours.                          
 --create-db                               Create a new database before importing the database dump on the target machine.                                             
 --db-su=                            Account to use when creating a new database. Optional.                                                                      
 --db-su-pw=                         Password for the "db-su" account. Optional.                                                                                 
 --dump-dir                                Directory to store sql dump files in when --source-dump or --target-dump are not used.                                      
 --no-cache                                Do not cache the sql-dump file.                                                                                             
 --no-dump                                 Do not dump the sql database; always use an existing dump file.                                                             
 --no-ordered-dump                         Do not pass --ordered-dump to sql-dump.  sql-sync orders the dumpfile by default in order to increase the efficiency of     
                                           rsync.                                                                                                                      
 --sanitize                                Obscure email addresses and reset passwords in the user table post-sync. Optional.                                          
   --sanitize-password                     The password to assign to all accounts in the sanitization operation, or "no" to keep passwords unchanged.  Default is      
                                           "password".                                                                                                                 
   --sanitize-email                        The pattern for test email addresses in the sanitization operation, or "no" to keep email addresses unchanged.  May contain 
                                           replacement patterns %uid, %mail or %name.  Default is "user+%uid@localhost".                                               
   --confirm-sanitizations                 Prompt yes/no after importing the database, but before running the sanitizations                                            
 --skip-tables-key                         A key in the $skip_tables array. @see example.drushrc.php. Optional.                                                        
 --skip-tables-list                        A comma-separated list of tables to exclude completely. Optional.                                                           
 --source-database                         A key in the $db_url (D6) or $databases (D7+) array which provides the data.                                                
 --source-db-url                           Database specification for source system to dump from.                                                                      
 --source-dump                             Path to dump file. Optional; default is to create a temporary file.                                                         
 --source-remote-host                      Remote machine to run sql-dump file on. Optional; default is local machine.                                                 
 --source-remote-port                      Override sql database port number in source-db-url. Optional.                                                               
 --structure-tables-key                    A key in the $structure_tables array. @see example.drushrc.php. Optional.                                                   
 --structure-tables-list                   A comma-separated list of tables to include for structure, but not data. Optional.                                          
 --tables-key                              A key in the $tables array. Optional.                                                                                       
 --tables-list                             A comma-separated list of tables to transfer. Optional.                                                                     
 --target-database                         A key in the $db_url (D6) or $databases (D7+) array which shall receive the data.                                           
 --target-db-url                                                                                                                                                       
 --target-dump                                                                                                                                                         
 --target-remote-host                                                                                                                                                  
 --target-remote-port                                                                                                                                                  
 --temp                                    Use a temporary file to hold dump files.  Implies --no-cache.
Improve this answer
2
  • 1
    Seems we said the same, with less than a minute time difference. What can I say, don't know why mine was accepted... After all, I was few seconds slower.
    – Mołot
    Nov 3, 2014 at 14:46
  • @Molot no worries have a great week!
    – tenken
    Nov 3, 2014 at 15:44
2

If you check out the Drush Commands page for sql-sync (or drush sql-sync --help) then you'll notice there is a parameter called --skip-tables-list.

--skip-tables-list : A comma-separated list of tables to exclude completely. Optional.

To answer the second part of your question, you can also add an array to drushrc.php (source):

/**
 * List of tables to be omitted entirely from SQL dumps made by the 'sql-dump'
 * and 'sql-sync' commands when the "--skip-tables-key=common" option is
 * provided on the command line.  This is useful if your database contains
 * non-Drupal tables used by some other application or during a migration for
 * example.  You may add new tables to the existing array or add a new element.
 */

In this instance you then pass the array key with --skip-tables-key:

--skip-tables-key : A key in the $skip_tables array. @see example.drushrc.php. Optional.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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