Ok I have a bunch of files in this file structure format.


And there are lots lots more.

ultimately I want to import all the 2011-01-07.sql files into my mysql database.

This works for one

mysql -u root -ppassword < /backup/daily/database1/database1-2011-01-07.sql

That will nicely restore that database from this backupfile.

I want to run a process where it does this for all databases.

So my plan is to first cp all 2011-01-07 sql files into a tmp dir e.g.

cp /backup/daily/*/*2011-01-07*.sql /tmp/all

The command above unfortunately isn't working I get an error:

cp: cannot stat ..... No such file or directory

So can you guys help me out with this. For bonus points if you can tell me how to do the next step which is import all databases in one command doing one at a time that would be great too.

I really want to do these in two separate steps because I need to delete a few sql files manually from the tmp dir before I run the restore command.

So I need:

1) command to copy all 2011-01-07 sql files to a tmp dir

2) command to import all those files in that dir into mysql

I know its possible to do in one but for lots of reasons I really would prefer to do it in two steps.


To copy all the 2011-01-07 sql files to a temporary directory (/tmp/all as per question)

find /backup/daily -name '*-2011-01-07.sql' -exec cp -t /tmp/all {} +

to import

for FILE in $( ls /tmp/all/*-2011-01-07.sql )
    echo "Importing $FILE ... "
    mysql -u root -ppassword < "$FILE" && echo "OK"

Use find to find them and then do it all at once.

find /backup/daily -name '*-2011-01-07.sql' -exec cat {} + | mysql -u root -p
  • that looks interesting, the only issue i have with that is there are alot of files and alot of info in some of them so cat would basically put it into one buffer I think that would be too big for msyql to handle all in one go. Is there any way to run the command individually on each file so as to avoid this problem? Also I do really need to cp the files first because I need to exclude a few databases. – Derek Organ Jan 16 '11 at 22:27
  • also if you don't mind me asking what does the + symbol mean in that command above? – Derek Organ Jan 16 '11 at 22:29
  • 1
    You can exclude certain files by putting \! -name <pattern> before the current -name predicate. -exec doesn't handle redirection so you'd have to use a call to a shell: -exec bash -c 'mysql -u root -p < {} ' \;. The + is an argument to -exec that tells it to put as many filenames as possible in the replacement ({}) before executing the command. – Ignacio Vazquez-Abrams Jan 16 '11 at 22:38
  • Derek, see my solution using parallel which avoids the problem of too many files per batch. – Phil Hollenback Jan 17 '11 at 0:54

I've done something similar. At work, I've made a bash script that does a for loop - for each subdirectory, cd into it, cp all files to the single target dir, and then cd back up.

If you had multiple levels for this, you could get even fancier and everytime you find a subdirectory, cd into and increment a counter. When completed, cd backup and decrement the counter.

You could also do something with find ./ -type -f, and then do a cp of each file into the target directory while (probably using sed) strip all the subdirectory names from the destination argument.


Here's a cool way to do this: use find(1) and gnu parallel:

find /backup/daily -type f -name \*2011-01-07\.*.sql -print0 | parallel -0 -j1 'mysql -u root -ppassword < {}'

The find command locates all files (type f) under /backup/daily matching that name string. Then you pipe it to parallel, which feeds each filename to the mysql command line tool. The -print0 / -0 combo makes sure that no weird filenames or filenames with spaces cause problems, by terminating with nulls instead of spaces. Some versions of find do not support this, however. The -j1 tells parallel to execute one job at a time, you can increase that if it is safe to load multiple files into mysql at once.

That also answers your question of how do you do it all at once with one command.

  • For added karma use GNU SQL (part of GNU Parallel): find /backup/daily -type f -name *2011-01-07\.*.sql -print0 | parallel -0 -j1 'sql mysql://root:password@/ < {}' – Ole Tange Jan 17 '11 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.