Backup MYSQL DataBase Automatically With Windows
Every e-commerce store or internet site have the two entities which have to be backup up :
- the web space
- the DataBase.
Backup target have to be in a very safe place.
We know that a lot of e-commerce solutions has an internal solution to do a backup for the store . But the biggest problem is that the backup is not an automated routine . So if you forgot to backup your store a few days and you faced a problem you are in troubles.
The right and the best solution is:
Schedule a backup automatically on specific events or on some times on a day on specific hours 7 days a week * 365 days all year around.
Shopcall suggest a free and comprehensive solution which really run in production.
What are the alternatives ?
- As we mentioned , use the built-in solution in the product if it is available.
- Buy a specific solution . there are a lot of products/extension which can do it , but the cost is high
- Use our ideas ' solutions and peace of code to solve the problem. it is free and without mony cost.
SHOPCALL has developed an idea and a system for a MySQL based website that every one with some knowledge in PHP, MySQL and windows can implement it immediately.
The backup solution principles
1. Backup all your webspace.
2. Backup your DB
3. Set times to schedule the backups.
4. Target backup to Microsoft's OneDrive(Remark: the target can be any other solution like google drive just with only few changes and you are done).
What are the requirements to implement the solution ?
1. Windows PC or a server
2. PHP enabled on a pc
3. Microsoft's OneDrive , business or personal and space depending on your needs.
4. Access to your site(including the MySQL DB) via the PC(need to open ports and etc).
5. A backup system.
Let's go to the solution.
The Components List
1. A PHP script which will trigger MySQLDump to perform the backup and to notify the user about success or failure of the backup.
3. A Windows Batch File to execute the PHP script and to notify visa a report on the backup results.
2. Definition in Microsoft's Tasks Scheduler to execute Windows batch file.
3. The COBIAN backup system which is a highly recommended freeware backup solution which we recommend.
4. Tablegen class , need to be dowloaded from shopcall and saved as a PHP file in your webspace.
5. Folder definition the for the target MySql Backups . need to be tailored in the PHP backup script later after downloading it.
5. Definition of a new network location which is linked to your site folder on your host site and name it mySite or any other name. if you don't know how to do it her is a good tutorial.
The PHP script
On execution the script will issue a backup with MySQLDump and will write the script to a special directory suffixed with the current date and will issue a report to the user.
On completion the script will inform to the user about the backup results.
All the lines pointed by an arrow need to be tailored carefully befor running the script. later is the link to download the script and a class for the report about the results of the backup which was done.
her is a link to the full script and the required tablegen class
Change the script according the directions documented at the remarks in the beginning of the script.
save the script and the tablegen class in your webspace and name it like: job_mysqlbackup.php
The windows batch file:
the window batch file have only one line:
Save it in a directory in your root(C:\) or another folder as you wish and name it:myJob.bat
Definition in Microsoft's Tasks Scheduler to execute Windows batch file.
Go to the microsoft's Task Scheduler and do the following definitions:
1. To execute the script : myjob.bat
2. The schedule when and how many times to run the script.
If you don't know how to do it , here is a link to a good tutorial of Microsofts task Schedulet
Download and install cobian backup software
- Download the cobian backup Free Software : click to download
- Install the software and read the cobian tutorial carefully.
- Later we will explain what to do with the Cobian backup
- REMARK: we recommend using the Cobian backup for doing general backups on your Personal Computer or even of a server because of it's rich functionality and robustness.
Install and implement OneDrive
We recommend that you will install the personal OneDrive which comes with a default of 15GB storage. if you need more storage it will cost you mony , about 5$ per month for 1Tb but if your site is important to you it is nonsense.
Now we are almost done. you will have to do some definitions befor your backup solution will be perfect.
Here are the steps:
1. In the cobian backup do an increment backup from the folder of the MySQL target backups to the one drive folder. from her the backups will be synchronized to the Onedrive online folder. you can set in the cobian scheduler the frequency and as much backups you need.
2. In the cobian backup do a full backup from the network location you defined.
That’s all enjoy and we hope we helped something to secure your site.
thanks a lot.