Skip certain tables with mysqldump

Question

Is there a way to restrict certain tables from the mysqldump command   For example  I d use the following syntax to dump only table1 and table2   mysqldump -u username -p database table1 table2  gt  database sql   But is there a similar way to dump all the tables except table1 and table2  I haven t found anything in the mysqldump documentation  so is brute-force  specifying all the table names  the only way to go

User · Answer

You can use the mysqlpump command with the   --exclude-tables name   command  It specifies a comma-separated list of tables to exclude   Syntax of mysqlpump is very similar to mysqldump  buts its way more performant  More information of how to use the exclude option you can read here  https   dev mysql com doc refman 5 7 en mysqlpump html mysqlpump-filtering

User · Answer

For sake of completeness  here is a script which actually could be a one-liner to get a backup from a database  excluding  ignoring  all the views  The db name is assumed to be employees   ignore   mysql --login-path root1 INFORMATION SCHEMA       --skip-column-names --batch       -e  select            group concat              concat  --ignore-table    table schema       table name  SEPARATOR                          from tables          where table type    VIEW  and table schema    employees     mysqldump --login-path root1 --column-statistics 0 --no-data employees  ignore  gt     backups som file sql       You can update the logic of the query  In general using group concat and concat you can generate almost any desired string or shell command

User · Answer

Dump all databases with all tables but skip certain tables  on github  https   github com rubo77 mysql-backup sh blob master mysql-backup sh     bin bash   mysql-backup sh  if   -z   1      then   echo   echo  ERROR  root password Parameter missing     exit fi DB host localhost MYSQL USER root MYSQL PASS  1 MYSQL CONN  -u  MYSQL USER  -p  MYSQL PASS    MYSQL CONN     BACKUP DIR  backup mysql   mkdir  BACKUP DIR -p  MYSQLPATH  var lib mysql   IGNORE  database1 table1  database1 table2  database2 table1      strpos  1  2   3    strpos haystack needle  optional offset of an input string  strpos         local str   1      local offset   3      if   -n    offset      then         str  substr    str     offset       else         offset 0     fi     str   str   2         if       str   -eq     1      then         return 0     fi     echo       str    offset       cd  MYSQLPATH for i in     do     if    i     performance schema       then      DB  basename   i         echo  backup  DB- gt  BACKUP DIR DB sql lzo      mysqlcheck   DB   MYSQL CONN --silent --auto-repair  gt  tmp tmp grep mysql-backup     grep -E -B1  note warning support auto increment required locks   tmp tmp grep mysql-backup gt  tmp tmp grep mysql-backup not     grep -v    cat  tmp tmp grep mysql-backup not    tmp tmp grep mysql-backup      tbl count 0     for t in   mysql -NBA -h  DB host  MYSQL CONN -D  DB -e  show tables        do       found   strpos   IGNORE    DB    t          if     found            then          echo  DUMPING TABLE   DB  t          mysqldump -h  DB host  MYSQL CONN  DB  t --events --skip-lock-tables   lzop -3 -f -o  BACKUP DIR  DB  t sql lzo         tbl count     tbl count   1          fi     done     echo   tbl count tables dumped from database   DB  into dir  BACKUP DIR      fi done   With a little help of https   stackoverflow com a 17016410 1069083  It uses lzop which is much faster  see http   pokecraft first-world info wiki Quick Benchmark  Gzip vs Bzip2 vs LZMA vs XZ vs LZ4 vs LZO

User · Answer

You can use the --ignore-table option  So you could do mysqldump -u USERNAME -pPASSWORD DATABASE --ignore-table DATABASE table1  gt  database sql  There is no whitespace after -p  this is not a typo   To ignore multiple tables  use this option multiple times  this is documented to work since at least version 5 0  If you want an alternative way to ignore multiple tables you can use a script like this     bin bash PASSWORD XXXXXX HOST XXXXXX USER XXXXXX DATABASE databasename DB FILE dump sql EXCLUDED TABLES   table1 table2 table3 table4 tableN        IGNORED TABLES STRING    for TABLE in  quot   EXCLUDED TABLES     quot  do      IGNORED TABLES STRING   quot  --ignore-table   DATABASE    TABLE  quot  done  echo  quot Dump structure quot  mysqldump --host   HOST  --user   USER  --password   PASSWORD  --single-transaction --no-data --routines   DATABASE   gt    DB FILE   echo  quot Dump content quot  mysqldump --host   HOST  --user   USER  --password   PASSWORD    DATABASE  --no-create-info --skip-triggers   IGNORED TABLES STRING   gt  gt    DB FILE

User · Answer

Another example for ignoring multiple tables  usr bin mysqldump -uUSER -pPASS --ignore-table  db test test1 db test test3  db test gt  db test sql  using --ignore-table and create an array of tables  with syntaxs like --ignore-table  db test table1 db test table3 db test table4   Extra  Import database    if file is  sql  mysql -uUSER  -pPASS db test  lt  backup database sql    if file is  sql gz  gzip -dc  lt  backup database sql gz   mysql -uUSER -pPASSWORD db test  Simple script to ignore tables and export in  sql gz to save space    bin bash   tables to ignore  TIGNORE   my database table1 my database table2 my database tablex     create text for ignore tables  TDELIMITED  quot   IFS  quot   quot   echo  quot    TIGNORE      --ignore-table   quot   quot    don t forget to include user and password  usr bin mysqldump -uUSER -pPASSWORD --events    TDELIMITED  --databases my database   gzip -v  gt  backup database sql gz  Links with information that will help you  mysqldump  amp  gzip mysqldump to a tar gz How do I load a sql gz file to my database    Note  tested in ubuntu server with mysql Ver 14 14 Distrib 5 5 55

User · Answer

Building on the answer from  Brian-Fisher and answering the comments of some of the people on this post  I have a bunch of huge  and unnecessary  tables in my database so I wanted to skip their contents when copying  but keep the structure   mysqldump -h  lt host gt  -u  lt username gt  -p  lt schema gt  --no-data  gt  db-structure sql mysqldump -h  lt host gt  -u  lt username gt  -p  lt schema gt  --no-create-info --ignore-table schema table1 --ignore-table schema table2  gt  db-data sql   The resulting two files are structurally sound but the dumped data is now  500MB rather than 9GB  much better for me  I can now import these two files into another database for testing purposes without having to worry about manipulating 9GB of data or running out of disk space

User · Answer

Another example for ignoring multiple tables  usr bin mysqldump -uUSER -pPASS --ignore-table  db test test1 db test test3  db test gt  db test sql  using --ignore-table and create an array of tables  with syntaxs like --ignore-table  db test table1 db test table3 db test table4   Extra  Import database    if file is  sql  mysql -uUSER  -pPASS db test  lt  backup database sql    if file is  sql gz  gzip -dc  lt  backup database sql gz   mysql -uUSER -pPASSWORD db test  Simple script to ignore tables and export in  sql gz to save space    bin bash   tables to ignore  TIGNORE   my database table1 my database table2 my database tablex     create text for ignore tables  TDELIMITED  quot   IFS  quot   quot   echo  quot    TIGNORE      --ignore-table   quot   quot    don t forget to include user and password  usr bin mysqldump -uUSER -pPASSWORD --events    TDELIMITED  --databases my database   gzip -v  gt  backup database sql gz  Links with information that will help you  mysqldump  amp  gzip mysqldump to a tar gz How do I load a sql gz file to my database    Note  tested in ubuntu server with mysql Ver 14 14 Distrib 5 5 55

User · Answer

You can use the mysqlpump command with the   --exclude-tables name   command  It specifies a comma-separated list of tables to exclude   Syntax of mysqlpump is very similar to mysqldump  buts its way more performant  More information of how to use the exclude option you can read here  https   dev mysql com doc refman 5 7 en mysqlpump html mysqlpump-filtering

User · Answer

I like Rubo77 s solution  I hadn t seen it before I modified Paul s  This one will backup a single database  excluding any tables you don t want  It will then gzip it  and delete any files over 8 days old  I will probably use 2 versions of this that do a full  minus logs table  once a day  and another that just backs up the most important tables that change the most every hour using a couple cron jobs      bin sh PASSWORD XXXX HOST 127 0 0 1 USER root DATABASE MyFavoriteDB  now    date    d  m  Y  H  M    filename    DATABASE  db backup  now  backupfolder   opt backups mysql  DB FILE   backupfolder  filename  logfile   backupfolder  backup log    date    Y  m    txt  EXCLUDED TABLES   logs   IGNORED TABLES STRING    for TABLE in    EXCLUDED TABLES      do      IGNORED TABLES STRING    --ignore-table   DATABASE    TABLE   done  echo  Dump structure started at   date    d- m- Y  H  M  S     gt  gt    logfile  mysqldump --host   HOST  --user   USER  --password   PASSWORD  --single-transaction --no-data --routines   DATABASE    gt    DB FILE   echo  Dump structure finished at   date    d- m- Y  H  M  S     gt  gt    logfile  echo  Dump content  mysqldump --host   HOST  --user   USER  --password   PASSWORD    DATABASE  --no-create-info --skip-triggers   IGNORED TABLES STRING   gt  gt    DB FILE  gzip   DB FILE   find   backupfolder  -name   DATABASE  db backup   -mtime  8 -exec rm       echo  old files deleted   gt  gt    logfile  echo  operation finished at   date    d- m- Y  H  M  S     gt  gt    logfile  echo                      gt  gt    logfile  exit 0

User · Answer

To exclude some table data  but not the table structure  Here is how I do it   Dump the database structure of all tables  without any data   mysqldump -u user -p --no-data database  gt  database structure sql   Then dump the database with data  except the excluded tables  and do not dump the structure   mysqldump -u user -p --no-create-info       --ignore-table database table1       --ignore-table database table2 database  gt  database data sql   Then  to load it into a new database   mysql -u user -p newdatabase  lt  database structure sql mysql -u user -p newdatabase  lt  database data sql

User · Answer

for multiple databases   mysqldump -u user -p --ignore-table db1 tbl1 --ignore-table db2 tbl1 --databases db1 db2

User · Answer

You can use the --ignore-table option  So you could do mysqldump -u USERNAME -pPASSWORD DATABASE --ignore-table DATABASE table1  gt  database sql  There is no whitespace after -p  this is not a typo   To ignore multiple tables  use this option multiple times  this is documented to work since at least version 5 0  If you want an alternative way to ignore multiple tables you can use a script like this     bin bash PASSWORD XXXXXX HOST XXXXXX USER XXXXXX DATABASE databasename DB FILE dump sql EXCLUDED TABLES   table1 table2 table3 table4 tableN        IGNORED TABLES STRING    for TABLE in  quot   EXCLUDED TABLES     quot  do      IGNORED TABLES STRING   quot  --ignore-table   DATABASE    TABLE  quot  done  echo  quot Dump structure quot  mysqldump --host   HOST  --user   USER  --password   PASSWORD  --single-transaction --no-data --routines   DATABASE   gt    DB FILE   echo  quot Dump content quot  mysqldump --host   HOST  --user   USER  --password   PASSWORD    DATABASE  --no-create-info --skip-triggers   IGNORED TABLES STRING   gt  gt    DB FILE

User · Answer

In general  you need to use this feature when you don t want or don t have time to deal with a huge table  If this is your case  it s better to use --where option from mysqldump limiting resultset  For example  mysqldump -uuser -ppass database --where  1   1 LIMIT 500000   gt  resultset sql

User · Answer

You can use the --ignore-table option  So you could do mysqldump -u USERNAME -pPASSWORD DATABASE --ignore-table DATABASE table1  gt  database sql  There is no whitespace after -p  this is not a typo   To ignore multiple tables  use this option multiple times  this is documented to work since at least version 5 0  If you want an alternative way to ignore multiple tables you can use a script like this     bin bash PASSWORD XXXXXX HOST XXXXXX USER XXXXXX DATABASE databasename DB FILE dump sql EXCLUDED TABLES   table1 table2 table3 table4 tableN        IGNORED TABLES STRING    for TABLE in  quot   EXCLUDED TABLES     quot  do      IGNORED TABLES STRING   quot  --ignore-table   DATABASE    TABLE  quot  done  echo  quot Dump structure quot  mysqldump --host   HOST  --user   USER  --password   PASSWORD  --single-transaction --no-data --routines   DATABASE   gt    DB FILE   echo  quot Dump content quot  mysqldump --host   HOST  --user   USER  --password   PASSWORD    DATABASE  --no-create-info --skip-triggers   IGNORED TABLES STRING   gt  gt    DB FILE

User · Answer

for multiple databases   mysqldump -u user -p --ignore-table db1 tbl1 --ignore-table db2 tbl1 --databases db1 db2

User · Answer

In general  you need to use this feature when you don t want or don t have time to deal with a huge table  If this is your case  it s better to use --where option from mysqldump limiting resultset  For example  mysqldump -uuser -ppass database --where  1   1 LIMIT 500000   gt  resultset sql

User · Answer

For sake of completeness  here is a script which actually could be a one-liner to get a backup from a database  excluding  ignoring  all the views  The db name is assumed to be employees   ignore   mysql --login-path root1 INFORMATION SCHEMA       --skip-column-names --batch       -e  select            group concat              concat  --ignore-table    table schema       table name  SEPARATOR                          from tables          where table type    VIEW  and table schema    employees     mysqldump --login-path root1 --column-statistics 0 --no-data employees  ignore  gt     backups som file sql       You can update the logic of the query  In general using group concat and concat you can generate almost any desired string or shell command

User · Answer

I like Rubo77 s solution  I hadn t seen it before I modified Paul s  This one will backup a single database  excluding any tables you don t want  It will then gzip it  and delete any files over 8 days old  I will probably use 2 versions of this that do a full  minus logs table  once a day  and another that just backs up the most important tables that change the most every hour using a couple cron jobs      bin sh PASSWORD XXXX HOST 127 0 0 1 USER root DATABASE MyFavoriteDB  now    date    d  m  Y  H  M    filename    DATABASE  db backup  now  backupfolder   opt backups mysql  DB FILE   backupfolder  filename  logfile   backupfolder  backup log    date    Y  m    txt  EXCLUDED TABLES   logs   IGNORED TABLES STRING    for TABLE in    EXCLUDED TABLES      do      IGNORED TABLES STRING    --ignore-table   DATABASE    TABLE   done  echo  Dump structure started at   date    d- m- Y  H  M  S     gt  gt    logfile  mysqldump --host   HOST  --user   USER  --password   PASSWORD  --single-transaction --no-data --routines   DATABASE    gt    DB FILE   echo  Dump structure finished at   date    d- m- Y  H  M  S     gt  gt    logfile  echo  Dump content  mysqldump --host   HOST  --user   USER  --password   PASSWORD    DATABASE  --no-create-info --skip-triggers   IGNORED TABLES STRING   gt  gt    DB FILE  gzip   DB FILE   find   backupfolder  -name   DATABASE  db backup   -mtime  8 -exec rm       echo  old files deleted   gt  gt    logfile  echo  operation finished at   date    d- m- Y  H  M  S     gt  gt    logfile  echo                      gt  gt    logfile  exit 0

User · Answer

Building on the answer from  Brian-Fisher and answering the comments of some of the people on this post  I have a bunch of huge  and unnecessary  tables in my database so I wanted to skip their contents when copying  but keep the structure   mysqldump -h  lt host gt  -u  lt username gt  -p  lt schema gt  --no-data  gt  db-structure sql mysqldump -h  lt host gt  -u  lt username gt  -p  lt schema gt  --no-create-info --ignore-table schema table1 --ignore-table schema table2  gt  db-data sql   The resulting two files are structurally sound but the dumped data is now  500MB rather than 9GB  much better for me  I can now import these two files into another database for testing purposes without having to worry about manipulating 9GB of data or running out of disk space

User · Answer

You can use the --ignore-table option  So you could do mysqldump -u USERNAME -pPASSWORD DATABASE --ignore-table DATABASE table1  gt  database sql  There is no whitespace after -p  this is not a typo   To ignore multiple tables  use this option multiple times  this is documented to work since at least version 5 0  If you want an alternative way to ignore multiple tables you can use a script like this     bin bash PASSWORD XXXXXX HOST XXXXXX USER XXXXXX DATABASE databasename DB FILE dump sql EXCLUDED TABLES   table1 table2 table3 table4 tableN        IGNORED TABLES STRING    for TABLE in  quot   EXCLUDED TABLES     quot  do      IGNORED TABLES STRING   quot  --ignore-table   DATABASE    TABLE  quot  done  echo  quot Dump structure quot  mysqldump --host   HOST  --user   USER  --password   PASSWORD  --single-transaction --no-data --routines   DATABASE   gt    DB FILE   echo  quot Dump content quot  mysqldump --host   HOST  --user   USER  --password   PASSWORD    DATABASE  --no-create-info --skip-triggers   IGNORED TABLES STRING   gt  gt    DB FILE

User · Answer

To exclude some table data  but not the table structure  Here is how I do it   Dump the database structure of all tables  without any data   mysqldump -u user -p --no-data database  gt  database structure sql   Then dump the database with data  except the excluded tables  and do not dump the structure   mysqldump -u user -p --no-create-info       --ignore-table database table1       --ignore-table database table2 database  gt  database data sql   Then  to load it into a new database   mysql -u user -p newdatabase  lt  database structure sql mysql -u user -p newdatabase  lt  database data sql

User · Answer

Dump all databases with all tables but skip certain tables  on github  https   github com rubo77 mysql-backup sh blob master mysql-backup sh     bin bash   mysql-backup sh  if   -z   1      then   echo   echo  ERROR  root password Parameter missing     exit fi DB host localhost MYSQL USER root MYSQL PASS  1 MYSQL CONN  -u  MYSQL USER  -p  MYSQL PASS    MYSQL CONN     BACKUP DIR  backup mysql   mkdir  BACKUP DIR -p  MYSQLPATH  var lib mysql   IGNORE  database1 table1  database1 table2  database2 table1      strpos  1  2   3    strpos haystack needle  optional offset of an input string  strpos         local str   1      local offset   3      if   -n    offset      then         str  substr    str     offset       else         offset 0     fi     str   str   2         if       str   -eq     1      then         return 0     fi     echo       str    offset       cd  MYSQLPATH for i in     do     if    i     performance schema       then      DB  basename   i         echo  backup  DB- gt  BACKUP DIR DB sql lzo      mysqlcheck   DB   MYSQL CONN --silent --auto-repair  gt  tmp tmp grep mysql-backup     grep -E -B1  note warning support auto increment required locks   tmp tmp grep mysql-backup gt  tmp tmp grep mysql-backup not     grep -v    cat  tmp tmp grep mysql-backup not    tmp tmp grep mysql-backup      tbl count 0     for t in   mysql -NBA -h  DB host  MYSQL CONN -D  DB -e  show tables        do       found   strpos   IGNORE    DB    t          if     found            then          echo  DUMPING TABLE   DB  t          mysqldump -h  DB host  MYSQL CONN  DB  t --events --skip-lock-tables   lzop -3 -f -o  BACKUP DIR  DB  t sql lzo         tbl count     tbl count   1          fi     done     echo   tbl count tables dumped from database   DB  into dir  BACKUP DIR      fi done   With a little help of https   stackoverflow com a 17016410 1069083  It uses lzop which is much faster  see http   pokecraft first-world info wiki Quick Benchmark  Gzip vs Bzip2 vs LZMA vs XZ vs LZ4 vs LZO

[sql] Skip certain tables with mysqldump

Examples related to sql

Examples related to mysql

Examples related to database

Examples related to mysqldump