Need Help with automatically Import from special mysqldump

Hi @ all
I need a little bit help with a tricky problem ...
Here�s the situation:

We�ve 2 MySQL-Servers, one is productive, the other is Backup.

At the productive Server there runs every 2 hours a cron Job which does a Dump from MySQL-DB with script 'automysqlbackup.sh' and
copy it then via rsync to the Backup-Server,
so at the Backup there�re the following files:

 Sep 15 08:58  daily_test_2014-09-15_08h58m_Monday.sql.gz
 Sep 15 10:58 daily_ test  _2014-09-15_10h58m_Monday.sql.gz
 Sep 15 12:58 daily_ test  _2014-09-15_12h58m_Monday.sql.gz
 Sep 15 14:58 daily_ test  _2014-09-15_14h58m_Monday.sql.gz
 Sep 15 16:58 daily_ test  _2014-09-15_16h58m_Monday.sql.gz
 Sep 15 18:58 daily_ test  _2014-09-15_18h58m_Monday.sql.gz
 Sep 15 20:58 daily_ test  _2014-09-15_20h58m_Monday.sql.gz
 Sep 15 22:58 daily_ test  _2014-09-15_22h58m_Monday.sql.gz
 Sep 16 00:58 daily_ test  _2014-09-16_00h58m_Tuesday.sql.gz
 Sep 16 02:58 daily_ test  _2014-09-16_02h58m_Tuesday.sql.gz
 Sep 16 04:58 daily_ test  _2014-09-16_04h58m_Tuesday.sql.gz
 Sep 16 06:58 daily_ test  _2014-09-16_06h58m_Tuesday.sql.gz
 

Now the problem:
I�ve to import the newest MySQL-Dump-File ( in this case above the file
Sep 16 06:58 daily_ test _2014-09-16_06h58m_Tuesday.sql.gz )
automatically in the DB 'test';
so I think the procedure is:

1. have to look which one ist the newest file ( my problem )
2. unzip this file
3. import via mysql
4. do a mysqlcheck
5. send a mail to root that import and check is fine

The points 2-5 I would do the following way:

via cron Script 'mysqlimport.sh':

 /bin/echo  "--------------------------------------------"
/bin/date
 echo Script  "MySQL-Import"
 cd /var/mysql_backup
 gunzip (procedure to find out which  file is newest) && mysql -u root test < Sep 16 06:58  daily_test_2014_09-16_06h58m_Tuesday.sql && mysqlcheck test
 

the output in Logfile 'mysqlimport.log'

 root     /root/mysqlimport.sh >> /var/log/mysqlimport.log
 

then do a tail of the last lines from 'mysqlimport.log' via script 'mysqlimport2.sh'

tail -50 /var/log/mysqlimport.log | awk  '{ print $0 "\x0c";}'
 

at last send via cron to root

/root/mysqlimport2.sh | mail -r root -s "Result MySQL-Import"  root

So I need help with Point 1, because I don�t know how�re the script-commands to find out which is the newest file, because they are different every 2 hours and every day.

Perhaps somebody could help me here?
Thanks in advance.
Regards

Hi,

Quick and dirty, although in your case you'll probably want field 5.

-rw-rw-r--   1 sc386dm sc386dm 49934602 Sep  9 08:55 AR092014(1).zip
-rw-rw-r--   1 sc386dm sc386dm 49712398 Sep 16 10:24 AR092014(2).zip
drwxr-xr-x.  2 sc386dm sc386dm     4096 Sep 16 10:24 .
drwx------. 46 sc386dm sc386dm     4096 Sep 16 10:24 ..

[~/Downloads] 
(10:42:55)-(sc386dm)-(684)-> ls -latr | tail -3 | head -1 | awk '{ print $9 }'
AR092014(2).zip

[~/Downloads] 
(10:43:03)-(sc386dm)-(685)-> 

Regards

Dave

For your point 1:

ls -lrt daily_test_*.sql.gz|awk '{print $NF}'|tail -1

enclose above in backticks (`) when you use this in code.

Hi,
thanks for your reply ...

ls -lrt daily_test_*.sql.gz|awk '{print $NF}'|tail -1

gives the right file back, but how can I get this in command to unzip it, in my script ?

The script mysqlimport.sh looks like this:

/bin/echo  "--------------------------------------------" 
/bin/date  
echo Script  "MySQL-Import"  
cd /var/mysql_backup
gunzip (command 'get the newest file' ) && mysql -u root test < ( even the output from command 'get the newest file' ) && mysqlcheck test

Regards

Hi,

Use something like;

MYFILE=`ls -lrt daily_test_*.sql.gz|awk '{print $NF}'|tail -1`
gunzip ${MYFILE}

Regards

Dave

Hi,

so when I try

MYFILE='ls -lrt daily_test*|awk '{print $NF}'|tail -1'

then I�ll get error message:

If '}|tail -1' is not a typo you can run the following command to lookup the package that contains the binary:
command-not-found }|tail -1
-bash: }|tail -1: command not found

The command

ls -lrt daily_test*|awk '{print $NF}'|tail -1

alone returns the right file, but with MYFILE in front of it, there comes the error above.
Where�s my mistake?

Hi,

You are using the wrong quotes, it should be;

`` not ''

Regards

Dave

Hi,
thanks, this works now.
I can do it with two commands in my script ...
1.

MYFILE=`ls -lrt daily_test*|awk '{print $NF}'|tail -1` && gunzip ${MYFILE}

MYFILE=`ls -lrt daily_test*|awk '{print $NF}'|tail -1` && mysql -u root test < ${MYFILE} && mysqlcheck test

At least, how can I delete the $MYFILE-Variable after this, to be sure, that every time the job runs, the newest file will be used, or is this not necessary ?