Home / server / Questions / 275803
Accepted
o01
Asked: 2011-06-02 01:32:35 +0800 CST

MySQL export tables with prefix from Unix command line

  • 772

I want to export certain tables from one database and import them to another. The tables in question are used for my MediaWiki installation and are prefixed with "wiki_".

I do not have access to phpMyAdmin, but I am able to connect to the server via SSH. I'm guessing I need to use the mysqldump command, but how do I specify that I only want to dump tables prefixed with "wiki_"?

mysql command-line-interface mediawiki export

2 Answers

  1. Best Answer

    You have to generate the list of tables you want to dump then act upon it.

    mysql -u USER -p -D test -Bse "show tables like 'wiki_%'" >tables.out
mysqldump -u USER -p test <tables.out >wiki_tables.dump

    or as a one liner

    mysqldump -u USER -p test $(mysql -u USER -p -D test -Bse "show tables like 'wiki_%'")

    but you still get to enter the password twice.

    • 7

  2. Since mysqldump can't work with wildcards, you will have to specify the tables you want:

    mysqldump -u username -p database wiki_table1 wiki_table2 .. > database.sql

    Or you could specify the tables you don't want to export:

    mysqldump -u username -p database --ignore-table=atable --ignore-table=anothertabl > database.sql
    • 0