How to Automatically Back up MySQL Database


by Julie Seymour · March 8, 2011

For Windows Users
Specifically, we should begin with the steps below using Task Scheduler:

1. Click Start button, then select Program → Accessories → System Tools → Task Scheduler (or Settings → Control Panel → Task Scheduler),to start Task Scheduler in Windows 2000.

2. Double click Add Scheduled Task icon to start Scheduled Task Wizard, then click Next button ,then select the application from the list of programs to run automatically, Then click Next button . And then set the appropriate name and set the the frequency of running Task Scheduler automatically (e.g. daily, weekly, monthly, one-time, even every time you start the computer, each login, etc.), and then click Next button.
The system will require you to run a specific time settings, such as a few numbers, a few minutes, what time to run, and we just need to set it appropriately according to their own condition.

3. Then we will be required to set the appropriate user name and its password.

4. Finally, we just click Finish button to add the appropriate task to the Windows 2000, then it will be automatically remembered and run. When the task have come into with the right time, it will be very convenient (even every time you start Windows 2000, Task Scheduler will automatically start and run in the background.
Now we are going to test it, right-click php icon in the pop-up menu, select Run, then the program icon will be running properly. If you fail to see it, you need to check the user and its password, and the Service List on Task Scheduler. It may be also useful to get information from the system log.
Well, we are giving two examples described as follow:

First, allow PHP to run it regularly!
1, edit the following code and save it as test.php:
<? Php
$ Fp = @ fopen (test.txt, a +);
fwrite ($ fp, date (Ymd H: i: s). allow PHP to run it regularly! \ n);
fclose ($ fp);

2, add a task plan, (see Figure 2) This step enter the command: D: \ php4 \ php.exe-q D: \ php4 \ test.php

3, the time is set to run once every 1 minute, then run this task.

4, we now look at d: \ php4 \ test.txt file contents of the time was successful. If the content is as follows, then congratulations, you succeeded.

2011-03-08 11:08:01 PHP timed to run it!
2011-03-08 11:09:02 PHP timed to run it!
2011-03-08 11:10:01 PHP timed to run it!
2011-03-08 11:11:02 PHP timed to run it!

Second, make automatic backups MYSQL become possible!
1, edit the following code and save it as backup.php, if you want to compress can copy rar.exe:
<? Php
if ($ argc! = 2 | | in_array ($ argv [1], array (‘- help’,’-?’))) {
backup Ver 0.01, for Win95/Win98/WinNT/Win2000/WinXP on i32
Copyright (C) 2000 ptker All rights reserved.
This is free software, and you are welcome to modify and redistribute it
under the GPL license

PHP Shell script for the backup MySQL database.

Usage: <? Php echo $ argv [0];?> <option>

<option> can be database name you would like to backup.
With the – help, or -? Options, you can get this help and exit.
<? Php
} Else {
$ Dbname = $ argv [1];
$ Dump_tool = c: \ \ mysql \ \ bin \ \ mysqldump;
$ Rar_tool = d: \ \ php4 \ \ rar;
@ Exec ($ dump_tool – opt-u user-ppassword $ dbname>. / $ Dbname.sql);
@ Exec ($ rar_tool a-ag_yyyy_mm_dd_hh_mm $ dbname.rar $ dbname.sql);
@ Unlink ($ dbname.sql);
echo Backup complete!;

2, add a task plan, (see Figure 2) This step enter the command:
D: \ php4 \ php.exe-q D: \ php4 \ backup.php databasename
3, the time is set to run once a day, and then run this task.
4, the last in d: \ php4 \ directory, create a database name and the current time to the composition of RAR files.

For the Linux users:

Using the shell script to automatically back up the database ,for the
Linux programs is updating its MySQL database daily from its server, sowe could also write a shell script with crontab , to regularly backup of the database.
The script is written as follows:
#! / Bin / sh
# File: / home / mysql /
# Database info
DB_NAME = test
DB_USER = username
DB_PASS = password

# Others vars
BIN_DIR = / usr / local / mysql / bin
BCK_DIR = / home / mysql / backup
DATE = `date +% F`

$ BIN_DIR / mysqldump – opt-u $ DB_USER-p $ DB_PASS $ DB_NAME | gzip> $ BCK_DIR / db_ $ DATE.gz

Then use this script to add / etc / crontab time task:

01 5 * * 0 mysql / home / mysql /

Well, every Sunday morning 5:01 am the system will automatically run file backup MySQL database.

Leave a Reply