MySQLdump – Backup Specific Database by Date

backupMySQLmysqldumpwindows

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

Best Answer

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

UPDATE 2012-12-21 19:30 EDT

Since this database uses innodb, will there be a growing temp table I can look for?

Let's say you want to run

OPTIMIZE TABLE mydb.mytable;

Look at two scenarios involving innodb_file_per_table and this OPTIMIZE TABLE

SCENARIO #1 : innodb_file_per_table disabled

Running OPTIMIZE TABLE will cause all the data and index pages for mydb.mytable to be appended contiguously to ibdata1. This will leave the space previously occupied to add further to the fragmentation madness.

In this scenario, the temp table would exist as InnoDB, and it would materialize in ibdata1. Thus, there would be nothing to visually monitor.

SCENARIO #2 : innodb_file_per_table enabled

For each InnoDB table mydb.mytable you would have

/var/lib/mysql/mydb/mytable.frm
/var/lib/mysql/mydb/mytable.ibd

The .ibd file contains the data and index pages for the table.

Running OPTIMIZE TABLE will cause all the data and index pages for mydb.mytable to be written to an external .ibd file that you can see in Windows Explorer.

CAVEAT

Enabling innodb_file_per_table and running OPTIMIZE TABLE will never reclaim the space formerly occupied within ibdata1. You will have to carry out the InnoDB Cleanup to recreate ibdata1 in such a way that data and indexes never, ever again reside in ibdata1.

CONCLUSION

As stated earlier, MariaDB can monitor OPTIMIZE TABLE;

If you want something you can monitor and you have innodb_file_per_table enabled, you can replace

OPTIMIZE TABLE LOGTABLEFOO1,LOGTABLEFOO2,LOGTABLEFOO3;

with the following mechanical equivalent:

CREATE TABLE LOGTABLEFOO1_NEW LIKE LOGTABLEFOO1;
INSERT INTO LOGTABLEFOO1_NEW SELECT * FROM LOGTABLEFOO1;
ALTER TABLE LOGTABLEFOO1 RENAME LOGTABLEFOO1_OLD;
ALTER TABLE LOGTABLEFOO1_NEW RENAME LOGTABLEFOO1;
DROP TABLE LOGTABLEFOO1_OLD;
ANALYZE TABLE LOGTABLEFOO1;

CREATE TABLE LOGTABLEFOO2_NEW LIKE LOGTABLEFOO2;
INSERT INTO LOGTABLEFOO2_NEW SELECT * FROM LOGTABLEFOO2;
ALTER TABLE LOGTABLEFOO2 RENAME LOGTABLEFOO2_OLD;
ALTER TABLE LOGTABLEFOO2_NEW RENAME LOGTABLEFOO2;
DROP TABLE LOGTABLEFOO2_OLD;
ANALYZE TABLE LOGTABLEFOO2;

CREATE TABLE LOGTABLEFOO3_NEW LIKE LOGTABLEFOO3;
INSERT INTO LOGTABLEFOO3_NEW SELECT * FROM LOGTABLEFOO3;
ALTER TABLE LOGTABLEFOO3 RENAME LOGTABLEFOO3_OLD;
ALTER TABLE LOGTABLEFOO3_NEW RENAME LOGTABLEFOO3;
DROP TABLE LOGTABLEFOO3_OLD;
ANALYZE TABLE LOGTABLEFOO3;

Please keep in mind the OPTIMIZE TABLE is nothing but ALTER TABLE ... ENGINE=InnoDB; followed by ANALYZE TABLE.

With these steps, you know in advance what the temp tables are

LOGTABLEFOO1_NEW.ibd
LOGTABLEFOO2_NEW.ibd
LOGTABLEFOO3_NEW.ibd

Simply Monitor Each File's Existence and Size Until it Disappears.

Mysql – Automating MySQL DB Creation with Specific date

This script will dynamically create the database prepended with the date format you want

SET @dbname = '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 @newDBNAME = CONCAT(DATE_FORMAT(NOW(),'%y%m%d_%H%i_'),@dbname);
SET @sql = CONCAT('CREATE DATABASE IF NOT EXISTS ',@newdbname);
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

Just set the @dbname in the script.

If you want it as a Stored Procedure, here you go:

DELIMITER $$

DROP PROCEDURE IF EXISTS `MakeRestoreDB` $$
CREATE PROCEDURE `MakeRestoreDB`(GivenDBName VARCHAR(20))
BEGIN

    SET @dbname = GivenDBName ;
    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 @newDBNAME = CONCAT(DATE_FORMAT(NOW(),'%y%m%d_%H%i_'),@dbname);
    SET @sql = CONCAT('CREATE DATABASE IF NOT EXISTS ',@newdbname);
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

END $$

DELIMITER ;

Then, just call MakeRestoreDB('rolando');, and you are good to go.

Give it a Try !!!

Hmmmmm ... some of these statements seem familiar :-)

Best Answer

STEP 01

STEP 02 and 03

DEMONSTRATION

Give it a Try !!!

UPDATE 2013-12-06 18:51 EST

STEP #1

STEP #2

EPILOGUE

Related Solutions

MySQL – Is There a Progress Indicator for OPTIMIZE TABLE?

UPDATE 2012-12-21 19:30 EDT

SCENARIO #1 : innodb_file_per_table disabled

SCENARIO #2 : innodb_file_per_table enabled

CAVEAT

CONCLUSION

Mysql – Automating MySQL DB Creation with Specific date

Related Question