Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm new in here and i don't really know where to start but here is my concern. I need to backyp a MySQL DB running under windows once a week. For e.g every friday i need to backup and transfer to another server the MYSQL DB created on the Thursday.

I have the DB created with the following naming : YYMMDD_HHMM_dbname.

i've tried loads of mysqldump options so far but can't get that specific DB that i would like too.

I would higly appreciate if you guys could help me on that.

With thks..

share|improve this question
    
Are you running on Linux? If so, you want to use cron. The people on serverfault will be able to help you with that –  FreshPhilOfSO Dec 5 '13 at 19:50
    
OK the database you want to backup is running under Windows. Is the other server running MySQL in Windows or Linux ??? –  RolandoMySQLDBA Dec 5 '13 at 20:34
    
Hi,Yes the Db is under windows and the other server is under windows as well. –  jerprosp Dec 5 '13 at 21:57
    
+1 for the mysqldump in Windows puzzle !!! –  RolandoMySQLDBA Dec 5 '13 at 23:20
add comment

1 Answer

up vote 2 down vote accepted

Your best option is the following:

  • STEP 01 : Create a SQL script that will write a Windows batch file
  • STEP 02 : Execute the SQL script to create the Windows batch file
  • STEP 03 : Execute the Windows batch file

STEP 01

First let's create the SQL file. The SQL file will have these rules

  • Find the Last Thursday as of Today
  • Locate the database
  • If today is Thursday and you run this script, then pick DB for the Thursday before that
  • Echo the Windows batch file, starting with @echo off

Here is the Windows batch file

SET @dborigin='rolando';
SET @today = DATE(NOW());
SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
SET @ut2 = UNIX_TIMESTAMP(@today);
SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
SET @user = 'root';
SET @pass = 'password';
SET @dumpfile = 'C:\\TransferDB.sql';
SELECT '@echo off';
SET @SQL='SELECT CONCAT(''mysqldump -u'',@user,'' -p'',@pass,'' --routines --triggers '',db,'' > '',@dumpfile) mysqldump_command FROM (SELECT schema_name db FROM information_schema.schemata WHERE LEFT(schema_name,6) = DATE_FORMAT(@last_thurs,''%y%m%d'') AND RIGHT(schema_name,LENGTH(@dborigin))=@dborigin) A';
PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;

Save the SQL script as C:\Make_mysqldump.sql

STEP 02 and 03

C:\> mysql -uroot -ppass -AN < C:\Make_mysqldump.sql > C:\Exec_mysqldump.bat
C:\> C:\Exec_mysqldump.bat

I will demonstrate Steps 01 and 02

DEMONSTRATION

Here the databases on my Windows machine

mysql> show databases;
+---------------------+
| Database            |
+---------------------+
| information_schema  |
| 131128_1234_rolando |
| 131129_1234_rolando |
| 131130_1234_rolando |
| 131203_1234_rolando |
| 131204_1234_rolando |
| 131205_1234_rolando |
| 131206_1234_rolando |
| alisha              |
| beercan             |
| musicdata           |
| mysql               |
| performance_schema  |
| test                |
+---------------------+
14 rows in set (0.00 sec)

mysql>

Here is the text file:

C:\>type Make_mysqldump.sql
SET @dborigin='rolando';
SET @today = DATE(NOW());
SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
SET @ut2 = UNIX_TIMESTAMP(@today);
SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
SET @user = 'root';
SET @pass = 'password';
SET @dumpfile = 'C:\\TransferDB.sql';
SELECT '@echo off';
SET @SQL='SELECT CONCAT(''mysqldump -u'',@user,'' -p'',@pass,'' --routines --triggers '',db,'' > '',@dumpfile) mysqldump
_command FROM (SELECT schema_name db FROM information_schema.schemata WHERE LEFT(schema_name,6) = DATE_FORMAT(@last_thur
s,''%y%m%d'') AND RIGHT(schema_name,LENGTH(@dborigin))=@dborigin) A';
PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;

Here is the creation of the batch file and its contents:

C:\>mysql -AN < C:\Make_mysqldump.sql > C:\Exec_mysqldump.bat

C:\>type C:\Exec_mysqldump.bat
@echo off
mysqldump -uroot -ppassword --routines --triggers 131129_1234_rolando > C:\\TransferDB.sql

C:\>

CAVEAT : I'll leave the rest to your imagination to run the mysqldump and execute C:\TransferDB.sql on the target DB server.

Give it a Try !!!

UPDATE 2013-12-06 18:51 EST

Here is a crazy idea requiring some elbow grease

STEP #1

Remove the first line of C:\Make_mysqldump.sql do it looks like this:

SET @today = DATE(NOW());
SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
SET @ut2 = UNIX_TIMESTAMP(@today);
SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
SET @user = 'root';
SET @pass = 'password';
SET @dumpfile = 'C:\\TransferDB.sql';
SELECT '@echo off';
SET @SQL='SELECT CONCAT(''mysqldump -u'',@user,'' -p'',@pass,'' --routines --triggers '',db,'' > '',@dumpfile) mysqldump_command FROM (SELECT schema_name db FROM information_schema.schemata WHERE LEFT(schema_name,6) = DATE_FORMAT(@last_thurs,''%y%m%d'') AND RIGHT(schema_name,LENGTH(@dborigin))=@dborigin) A';
PREPARE s FROM @SQL; EXECUTE s; DEALLOCATE PREPARE s;

STEP #2

Create a new batch file (Call it C:\MakeBackupJob.bat) whose sole purpose is to make a dborigin variable and append the rest. It should have these lines:

@echo off
echo set @dborigin='%1'; > C:\MakeJob_%1.sql
type C:\Make_mysqldump.sql >> C:\MakeJob_%1.sql

EPILOGUE

To use C:\MakeBackupJob.bat, simply specify the database tag name. Suppose the databases are rolando,pamela,dominique,diamond. Simply run the following:

C:
cd \
MakeBackupJob.bat rolando
MakeBackupJob.bat pamela
MakeBackupJob.bat dominique
MakeBackupJob.bat rolando

You should have 4 SQL files in C:\

MakeJob_rolando.sql
MakeJob_pamela.sql
MakeJob_dominique.sql
MakeJob_diamond.sql

Now, run the following:

echo @echo off  > C:\Exec_mysqldump.bat
C:\>mysql -AN < C:\MakeJob_rolando.sql   >> C:\Exec_mysqldump.bat
C:\>mysql -AN < C:\MakeJob_pamela.sql    >> C:\Exec_mysqldump.bat
C:\>mysql -AN < C:\MakeJob_dominique.sql >> C:\Exec_mysqldump.bat
C:\>mysql -AN < C:\MakeJob_diamond.sql   >> C:\Exec_mysqldump.bat

Now, C:\Exec_mysqldump.bat contains 4 mysqldumps to separate files.

Give it a Try !!!

share|improve this answer
    
Well explained! –  Vasilis Lourdas Dec 6 '13 at 6:14
    
Hi,Thanks for the explanation. I'll give a try straight away and revert back. –  jerprosp Dec 6 '13 at 14:33
    
Hi Rolanda,I've tried it, but it come up with an empty bat file with only line @echo off .. What could be wrong? –  jerprosp Dec 6 '13 at 17:06
    
Look over the query and see if it matches the database names. If it does not invade privacy, please post your SHOW DATABASES; and SQL script you made (hide your passwords). –  RolandoMySQLDBA Dec 6 '13 at 17:08
    
My mistake..I got it to work..there was an invisible character hidden in the text file. Now it's working perfectly. Thank you very much for your sharing..i owe you one ;) Have a good one.. –  jerprosp Dec 6 '13 at 17:11
show 4 more comments

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.