I want to export a list of tables starting with a certain prefix using a wild card.
Ideally I would do:
mysqldump -uroot -p mydb table_prefix_* > backup.sql
Obviously that doesn’t work. But what is the correct way to do this?
Asked
Active
Viewed 839 times
1
Giacomo1968
- 25,759
- 11
- 71
- 103
Marty Wallace
- 34,046
- 53
- 137
- 200
-
I think that you cannot do this with `mysqldump` alone, but you can write a script to do it. What OS are you using? (I'm looking for a solution in linux) – Barranka Jun 19 '13 at 23:05
-
Also, you may want to read this post: http://stackoverflow.com/questions/5268936/mysqldump-only-tables-with-certain-prefix-mysqldump-wildcards?rq=1 – Barranka Jun 19 '13 at 23:37
2 Answers
1
If it has a prefix, make a user with select and lock permissions to the table with GRANT like this
GRANT SELECT, LOCK TABLES ON `table\_prefix\_%` . * TO 'backup-user'@'localhost';
then instead of running mysql dump as root, run it as backup-user with the option --all-databases
as backup user only has select and lock permission on these tables, They are the only ones which will be there.
its also safer using a user like this rather than the root account for everything
exussum
- 18,275
- 8
- 32
- 65
0
Ok. Here is a solution using a Bash script (I've tested it in Linux).
Create a text file named "dumpTablesWithPrefix.script" and put this in it:
tableList=$(mysql -h $1 -u $2 -p$3 $4 -e"show tables" | grep "^$5.*" | sed ':a;N;$!ba;s/\n/ /g')
mysqldump -h $1 -u $2 -p$3 $4 $tableList
Save it, and make it executable:
$ chmod +x dumpTablesWithPrefix.script
Now you can run it:
$ ./dumpTablesWithPrefix.script host user pwd database tblPrefix > output.sql
Now let me explain each piece:
The command-line arguments
Bash scripts store command-line arguments in the variables $1, $2, $3 and so on. So the first thing I need to tell you is the order of the arguments you need:
- The host (if the mysql server is in your machine, write
localhost) - Your user
- Your password
- The name of your database
- The prefix of the tables you want to export
The first line of the script
I'll split this line in three pieces:
mysql -h $1 -u $2 -p$3 $4 -e"show tables"
This piece retrieves the full table list of your database, one table per line.
| grep "^$5.*"
This piece filters the table list, using the prefix you specified.
| sed ':a;N;$!ba;s/\n/ /g'
This final piece (a gem I found here: How can I replace a newline (\n) using sed? ) replaces all the "new line" characters with spaces.
This three pieces, strung together, throw the filtered table list and store it in the tableList variable.
The second line of the script
mysqldump -h $1 -u $2 -p$3 $4 $tableList
This line is simply the MySQL Dump command that does what you need.
Hope this helps
