MySQL Forums
Forum List  »  General

Backup and Restore Scripts - Windows Batch
Posted by: Dan Hallman
Date: March 04, 2011 12:17PM

Hello,

I have decided to bring my company's database to MySQL for Windows (for now, but Linux is in the future plans) from MSAccess and from SQL Server Express. The natural first step for moving to a new database is to get a backup and restore strategy, and then prove that it works. After reading this forum I have accomplished this, and included the batch files for you to critique and use.

The first step I took was to add the following to my my.ini file in the mysqld group:

log-bin = "D:/MySQLBack/bin.log"
max_binlog_size = 1000000

The max_binlog_size is set to 1 MB to prevent my copying the same data over and over for my daily backups from the backup drive to the firebox. I may change this number after see the binlog count between backups.

After the change is made, I restarted the MySQL service.

Then I created a file called d:\MySQLBack\Backup.bat, which contains the following:

@echo off
dir bin.* /B > files.txt
for /F "tokens=2-4 delims=/- " %%A in ('date/T') do set dt=%%C%%B%%A
for /F "tokens=1-2 delims=: " %%A in ('time/T') do set tm=%%A%%B
md "%dt%%tm%"
move databases.txt %dt%%tm%

"C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump" -h localhost -u root -pMySQLPassword --all-databases --flush-logs > databases.txt

FOR /f %%a in (files.txt) do call:MY_SUB %%a
GOTO SUB_DONE

:MY_SUB
if not %1==bin.index move %1 %dt%%tm%
GOTO :EOF

:SUB_DONE
del files.txt
echo Done

This file performs the following in order:
1) Creates a list of all binary logs in the folder
2) Creates a subfolder using current date and time
3) Moves pre-existing mysqldump file to new subfolder
4) Flushes current binary log and creates new MySQL dump
5) Moves each previously-existing (now closed) binary log to new folder
6) Deletes the list created at the top


Now the subfolder contains the previous backup, and the main folder has a current backup.

To restore any of these backups, I created the following file called d:\MySQLBack\Restore.bat which contains the following:

@echo off
dir bin.* /oe /B > files.txt

mysqladmin -u root -pMySQLPassword flush-logs
mysql -u root -pMySQLPassword < databases.txt

FOR /f %%a in (files.txt) do call:MY_SUB %%a
GOTO SUB_DONE

:MY_SUB
if not %1==bin.index mysqlbinlog %1 | mysql -u root -pMySQLPassword
GOTO :EOF

:SUB_DONE
del files.txt
echo Done


This file performs the following:
1) Creates a list of all binary logs in the folder
2) Flushes the current log
3) Restores the dump
4) Restores each previously-existing binary log in the current directory
5) Deletes the file created in step 1

The Restore.bat file can be placed into the main backup dir to restore the most recent backup, or can be placed in one of the subdirs created by backup.bat to restore that backup.

Remember to obtain exclusive access to your database before running a restore. We don't want rogue users or applications editing our data during a restore.

I did just come up with a question:
Should I flush the logs again after doing a restore, and then delete the log that was created during the restore?

Anyway, questions, comments, changes etc are all welcome. I'm an experienced rookie.

Dan



Edited 3 time(s). Last edit at 03/08/2011 02:57PM by Dan Hallman.

Options: ReplyQuote


Subject
Written By
Posted
Backup and Restore Scripts - Windows Batch
March 04, 2011 12:17PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.