How do you mysqldump specific table(s)?

  • How can I dump a specific table or set of tables without including the rest of the db tables?

  • If you are dumping tables t1, t2, and t3 from mydb

    mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql
    

    If you have a ton of tables in mydb and you want to dump everything except t1, t2, and t3, do this:

    DBTODUMP=mydb
    SQL="SET group_concat_max_len = 10240;"
    SQL="${SQL} SELECT GROUP_CONCAT(table_name separator ' ')"
    SQL="${SQL} FROM information_schema.tables WHERE table_schema='${DBTODUMP}'"
    SQL="${SQL} AND table_name NOT IN ('t1','t2','t3')"
    TBLIST=`mysql -u... -p... -AN -e"${SQL}"`
    mysqldump -u... -p... ${DBTODUMP} ${TBLIST} > mydb_tables.sql
    

    Give it a Try !!!

    UPDATE 2014-03-06 10:15 EST

    @RoryDonohue pointed out to me that the GROUP_CONCAT function needs to have its max length extended. I added the session variable group_concat_max_len to my answer with a length max of 10K. Thanks, @RoryDonohue.

    To exclude just a few tables you can use --ignore-table=Table1 --ignore-table=Table2 --ignore-table=Table3 etc.

    @codecowboy, you can change `SQL="${SQL} AND table_name NOT IN ('t1','t2','t3')"` to `SQL="${SQL} AND table_name NOT LIKE 'foo\_%'"`. I just tested it and it works. You could change the condition to '%foo%' to get all tables containing 'foo' anywhere in their names (including 'food', 'fool', etc).

    Isn't the approach for *excluding* tables here redundant and overkill given the existence of the `--ignore-table` argument? And if so, wouldn't it be better to scrap your script from the answer and just recommend `--ignore-table` instead?

    @codewaggle That gives the error `Illegal use of option --ignore-table=.` unless I specify the table as `schemaname.tablename`.

    cool stuff, any reason SQL variable is not multi-line?

    It s just not working `Access denied for user to database t1 when selecting database`

    How do I apply multiple tables `t1`, `t2`, `t3` using the option `--tables` ?

    You can pipe the output to zip command to compress the dump: mysqldump -u... -p... mydb t1 t2 t3 | zip mydb_tables.sql.zip

  • A note to expand on the answer by RolandoMySQLDBA.

    The script he included is a great approach for including (and table_name in) or excluding (and table_name NOT in) a list of tables.

    If you just need to exclude one or two tables, you can exclude them individually with the --ignore-table option:

    mysqldump -u -p etc. --ignore-table=Database.Table1 --ignore-table=Database.Table2 > dump_file.sql
    

    And this is the best answer!

  • When you have more than a few tables it is much better running something like this:

    mysql databasename -u [user] -p[password] -e 'show tables like "table_name_%"' 
           | grep -v Tables_in 
           | xargs mysqldump [databasename] -u [root] -p [password] > [target_file]
    

    Or somethink like this:

    mysqldump -u [user] -p[password] databasename `echo "show tables like 'table_name_%';" 
           | mysql -u[user] -p[password] databasename 
           | sed '/Tables_in/d'` > [target_file]
    

    Remember that those commands must be typed in one line only.

  • You can do it simply using below command:

    mysqldump -uusername -ppassword dbname \
      --ignore-table=schema.tablename1    \
      --ignore-table=schema.tablename2    \
      --ignore-table=schema.tablename3 > mysqldump.sql
    

    --ignore-table is to exclude certain tables from mysqldump, not to include . :)

    Yet one could list all the tables to exclude and it would dump just the ones remaining :-)

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM