MySQLdump on specific db date

Question

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..

Answer 1

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 !!!