Exporting and importing large databases As soon as your website’s database dump file will get to be 1GB or extra, phrases like “oh, simply obtain and import a DB dump” cannot actually be taken without any consideration anymore. So listed below are some ideas for coping with giant databases, particularly these of Drupal Development Service selection. Exporting Earlier than we will import, we should export. With an enormous DB, you do not need to simply do a daily outdated mysqldump > outfile.sql and name it a day. Listed below are some ideas. Discover Drupal Development Service measurement earlier than exporting It may possibly typically be helpful to see how large Drupal Development Service export goes to be earlier than you really export something. That method, you may know forward of time if it’s essential be doing this or that to cut back Drupal Development Service measurement, or if it will not matter since Drupal Development Service complete factor will not be that large anyway. This is a question you may run to see Drupal Development Service measurement per DB desk Drupal 10 Upkeep and Help Service SELECT TABLE_SCHEMA, TABLE_NAME, DATA_LENGTH / POWER(1024,1) Data_KB, DATA_LENGTH / POWER(1024,2) Data_MB, DATA_LENGTH / POWER(1024,3) Data_GB FROM information_schema.tables WHERE table_schema NOT IN   (‘information_schema’,’performance_schema’,’mysql’) ORDER BY DATA_LENGTH; And this is one other question you may run to see what Drupal Development Service whole measurement for Drupal Development Service total DB is Drupal 10 Upkeep and Help Service  SELECT Data_BB / POWER(1024,1) Data_KB, Data_BB / POWER(1024,2) Data_MB, Data_BB / POWER(1024,3) Data_GB FROM (SELECT SUM(data_length)   Data_BB FROM information_schema.tables WHERE table_schema NOT IN   (‘information_schema’,’performance_schema’,’mysql’)); Dump with out pointless information For these instances the place you want Drupal Development Service database construction for all of Drupal Development Service tables, however you do not want Drupal Development Service information for all of them, this is a method you need to use. It will seize Drupal Development Service total DB construction, however allows you to exclude information for any tables that you really want. For instance, search_index, cache_*, or periods tables can be good locations to chop out some fats. # First we export Drupal Development Service desk construction. mysqldump –no-data database_name > /export.sql # Seize desk information, excluding tables we do not want. mysqldump –no-create-info   –ignore-table=database_name.table_name1   –ignore-table=database_name.table_name2   database_name >> export.sql Simply substitute “table_name1” and “table_name2” with Drupal Development Service tables that you just need to skip, and also you’re golden. Additionally be aware that you need to use Drupal Development Service % character as a wildcard, so for instance, you could possibly ignore “cache%” for all cache tables. After you try this, you may have a single export.sql file that incorporates Drupal Development Service DB construction for all tables and Drupal Development Service DB information for all tables besides Drupal Development Service ones you excluded. Then, you may in all probability need to compress it… Compress all Drupal Development Service issues This one might go with out saying, however if you happen to’re not compressing your database dumps then both they’re actually tiny, otherwise you’re dumber than a dummy.  drush sql-dump –gzip –result-file=db.sql Examine that with Drupal Development Service common outdated Drupal 10 Upkeep and Help Service drush sql-dump –result-file=db.sql …and you are going to see an enormous distinction. Or if you have already got Drupal Development Service SQL dump that it’s essential compress, you may compress Drupal Development Service file instantly utilizing Drupal 10 Upkeep and Help Service gzip -v db.sql That can output a db.sql.gz file for you. Importing Now you’ve got a pleasant clear compressed DB dump with all the pieces you want and nothing you do not, and also you’re able to import. Listed below are just a few methods to ease Drupal Development Service ache. Import a compressed dump instantly As an alternative of getting to decompress Drupal Development Service dump earlier than importing, you are able to do it inline Drupal 10 Upkeep and Help Service gunzip -c db.sql.gz | drush sqlc Exclude information when importing In the event you obtain a DB dump that has a variety of information you do not want (caches, periods, search index, and so forth.), then you may simply ignore that stuff when importing it as nicely. This is somewhat one-liner for this Drupal 10 Upkeep and Help Service gunzip -c db.sql.gz | grep -Ev “^INSERT INTO `(cache_|search_index|periods)” | drush sqlc What that is doing is utilizing “grep” as a intermediary and saying “skip any traces which are insertion traces for these particular tables we do not care about”. You possibly can edit what’s in Drupal Development Service parenthesis so as to add/take away tables as wanted. Monitor import progress There’s nothing worse than simply sitting and ready and having no concept how far alongside Drupal Development Service import has made it. Monitoring progress makes an extended import appear sooner, as a result of there isn’t any questioning.  In case you have Drupal Development Service skill to put in it (from Homebrew or apt-get or no matter), Drupal Development Service “pv” (Pipe Viewer) command is nice right here Drupal 10 Upkeep and Help Service pv db.sql | drush sqlc Or in case your database is compressed Drupal 10 Upkeep and Help Service pv db.sql.gz | gunzip | drush sqlc Utilizing “pv” will present you a progress bar and a completion share. It is fairly superior. If you do not have “pv” then you may accept Drupal Development Service poor man’s model Drupal 10 Upkeep and Help Service watch “mysql database_name -Be ‘SHOW TABLES’ | tail -n2” That slick little man will present you Drupal Development Service desk that’s presently importing, and auto-updates because it runs, so you may not less than see how far by way of Drupal Development Service desk listing it has gone. Instruments and Useful resource On this publish I attempted to deal with instructions that everybody already has. If this simply is not chopping it for you, then look into these instruments which may assist much more Drupal 10 Upkeep and Help Service SyncDB – a pair Drush instructions that break up DB dumps into separate information and import them in parallel, drastically dashing issues up Drush SQL Sync Pipe – a substitute for “drush sql-sync” that makes use of pipes the place attainable to hurry issues up mcrittenden Wed, 01/11/2017 – 23 Drupal 10 Upkeep and Help Service15 Drupal 10 Improvement and Help