MySQLdump on specific db date 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..
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,'' &gt; '',@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:\&gt; mysql -uroot -ppass -AN &lt; C:\Make_mysqldump.sql &gt; C:\Exec_mysqldump.bat
C:\&gt; C:\Exec_mysqldump.bat


I will demonstrate Steps 01 and 02

DEMONSTRATION

Here the databases on my Windows machine

mysql&gt; 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&gt;


Here is the text file:

C:\&gt;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,'' &gt; '',@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:\&gt;mysql -AN &lt; C:\Make_mysqldump.sql &gt; C:\Exec_mysqldump.bat

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

C:\&gt;


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,'' &gt; '',@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'; &gt; C:\MakeJob_%1.sql
type C:\Make_mysqldump.sql &gt;&gt; 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  &gt; C:\Exec_mysqldump.bat
C:\&gt;mysql -AN &lt; C:\MakeJob_rolando.sql   &gt;&gt; C:\Exec_mysqldump.bat
C:\&gt;mysql -AN &lt; C:\MakeJob_pamela.sql    &gt;&gt; C:\Exec_mysqldump.bat
C:\&gt;mysql -AN &lt; C:\MakeJob_dominique.sql &gt;&gt; C:\Exec_mysqldump.bat
C:\&gt;mysql -AN &lt; C:\MakeJob_diamond.sql   &gt;&gt; C:\Exec_mysqldump.bat


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

Give it a Try !!!