Advanced MYSQL Backups [Windows]

[Dan]

--------------------------------------------------------------------------------------------------------------------------------------------------------
ANY ISSUES? FEEL FREE TO POST BELOW OR EMAIL ME ( ) AND I WILL DO MY BEST TO ASSIST YOU.
--------------------------------------------------------------------------------------------------------------------------------------------------------

In this tutorial, I'm going to give you an easy way to take a database backup from the databases of your choice and automatically zip them up using Winrar, even sending them to another FTP server - perfect for backing up those all-important databases.

Firstly you will need to ensure the latest Winrar is installed to C:\Program Files\Winrar. You will also need MYSQL installed.

1. Create a folder at the following location: C:\Database Backups

2. Create a folder inside C:\Database Backups called RAW

3. Navigate to C:\Program Files\Mysql (version)\bin and inside create a batch file called Backups.bat - inside that batch file, copy and paste the following code...

Originally Posted by BACKUPS.BAT

mysqldump.exe -uroot -pDATABASEPASSWORD DATABASE_NAME > "C:\Database Backups\DATABASE_NAME.sql"
echo "-----MYSQL BACKUP OF DATABASE_NAME COMPLETED SUCCESSFULLY-----"

c:
cd \
cd "Program Files\Winrar
del "C:\Database Backups\Databases.rar"
rar a "C:\Database Backups\Databases.rar" "C:\Database Backups\RAW\*.sql" > "C:\Database Backups" > "C:\Database Backups\Databasebackuplog.txt"
del "C:\Database Backups\RAW\*.sql"
C:
cd \
cd "Database Backups"
ftp -s:ftprules.txt > ftplog.txt
quit

You must edit the parts in bold to your servers information / database name.

NOTE: Ensure that you do not have a space between "-pDATABASEPASSWORD", also the same with the database username. Also take note of the underlinings as this is separating two different parameters in the command line.


-------IF YOU DO NOT WANT TO TRANSFER THE BACKUPS TO ANOTHER SERVER, IGNORE THE FOLLOWING-------

4. Now that we have the primary batch file setup and placed in C:\Program Files\Mysql (version)\bin we will need to adjust the FTP rules.

Navigate to C:\Database Backups and create a new TXT file called FTPRULES.TXT. Inside this file should be the following...

Originally Posted by FTPRULES.TXT

open FTP_IP_OR_URL
FTP_USERNAME
FTP_PASSWORD
lcd "C:\database backups"
del databases.rar
binary
mput *.rar
y
y
byebye
y

Once again, editing the parts in bold above.

You should now have a fully working system which should

1. Create database backups.
2. Compress the backups into a highly compressible format.
3. Transfer the much precious backups to an additional server (if required)