Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

/backup/daily/database1/database1-2011-01-01.sql
/backup/daily/database1/database1-2011-01-02.sql
/backup/daily/database1/database1-2011-01-03.sql
/backup/daily/database1/database1-2011-01-04.sql
/backup/daily/database1/database1-2011-01-05.sql
/backup/daily/database1/database1-2011-01-06.sql
/backup/daily/database1/database1-2011-01-07.sql
/backup/daily/anotherdb/anotherdb-2011-01-01.sql
/backup/daily/anotherdb/anotherdb-2011-01-02.sql
/backup/daily/anotherdb/anotherdb-2011-01-03.sql
/backup/daily/anotherdb/anotherdb-2011-01-04.sql
/backup/daily/anotherdb/anotherdb-2011-01-05.sql
/backup/daily/anotherdb/anotherdb-2011-01-06.sql
/backup/daily/anotherdb/anotherdb-2011-01-07.sql
/backup/daily/stuff/stuff-2011-01-01.sql
/backup/daily/stuff/stuff-2011-01-02.sql
/backup/daily/stuff/stuff-2011-01-03.sql
/backup/daily/stuff/stuff-2011-01-04.sql
/backup/daily/stuff/stuff-2011-01-05.sql
/backup/daily/stuff/stuff-2011-01-06.sql
/backup/daily/stuff/stuff-2011-01-07.sql

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.

share|improve this question
add comment

4 Answers

up vote 0 down vote accepted

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

#!/bin/bash
for FILE in $( ls /tmp/all/*-2011-01-07.sql )
do
    echo "Importing $FILE ... "
    mysql -u root -ppassword < "$FILE" && echo "OK"
done
share|improve this answer
add comment

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
share|improve this answer
    
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
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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