MySQL implementation of automatic remote backup method _ Server

First, the implementation of local automatic backup database is simple:
1. Log in as Root
2. Scheduled Tasks (3:30 automatic execution/usr/bin/mysqlbackup per day)
Crontab
0 3:30 * * */usr/bin/mysqlbackup
3. Write an automatic backup database script
Vi/usr/bin/mysqlbackup
#!/bin/bash
Mv/home/.../db_bak5.tgz/home/.../db_bak6.tgz
Mv/home/.../db_bak4.tgz/home/.../db_bak5.tgz
Mv/home/.../db_bak3.tgz/home/.../db_bak4.tgz
Mv/home/.../db_bak2.tgz/home/.../db_bak3.tgz
Mv/home/.../db_bak1.tgz/home/.../db_bak2.tgz
Rm-rf/home/.../db_bak.txt
/usr/bin/mysqldump-uroot-ppassword dbname
>/home/.../db_bak.txt
Cd/home/.../
Tar czvf db_bak1.tgz db_bak.txt
Two, this daily 3:30 will be in the local directory/home/.../to generate a corresponding database backup, beginning because the/home/.../directory is missing data system will prompt errors, this is normal
Third, as for remote backup can be used in conjunction with FTP or SCP command implementation, the method is very simple, check the use of related commands, and then change the Mysqlbackup script will be


The MySQL database is automatically backed up daily under Win2003

Finally let the server back up the MySQL database every morning and automatically pack, and delete 5 days ago backup files. Share as follows.

1. Environment: Windows Server 2003 + Apache 2.0 + PHP5 + MySQL 4.0.26.

2. Suppose the PHP installation directory is d:/php, the MySQL installation directory is d:/mysql.

3. Create a directory WinRAR under d:/php and copy WinRAR.exe and Rarreg.key from your WinRAR installation directory to D:/php/winrar.

4. Establishment of document Mysql_backup.php under d:/php:

/*/////////////////////////
#FileName: mysql_backup.php
#Author: Faisun
#Website: http://www.softpure.com
////////////////////////*/
Save the directory with a backslash. You need to build it manually.
$store _folder = ' D:\databse_backup ';
User name and password
The account must have the right to operate [all] databases and file
Otherwise, some databases cannot be backed up.
$db _username = "root";
$db _password = "";
$time =time ();
$nowdir = "$store _folder\\". Date ("Ymd", $time). "";
if (File_exists ("$nowdir. rar")) die ("File exists.\n");
@mkdir ($nowdir);
mysql_connect ("localhost", "$db _username", "$db _password");
$query =mysql_list_dbs ();
while ($result =mysql_fetch_array ($query)) {
System (DirName (__file__). ' \.. \mysql\bin\mysqldump--opt '. ' $result [Database]
-u{$db _username} ". ($db _password?) -p{$db _password} ":" ". >
$nowdir \ $result [database].sql ");
echo "Dumping database" $result [database] ' ... \ n ';
}
echo "\nwinrar loading...\n";
System (DirName (__file__). " \\WinRAR\\WinRAR.exe a-ep1-r-o+-m5-df \ "$nowdir. Rar\"
"$nowdir" ");
Delete a file 5 days ago
@unlink ("$store _folder\\". Date ("Ymd", $time -86400*5). ". RAR ");
echo "\nok!\n";
>

5. The d:/php establishes the document Mysql_backup.bat, the content is only one sentence:

Php.exe mysql_backup.php

6. Double-click the bat file to run, if you can back up, OK, next add the task plan.

7. Add the D:/php/mysql_backup to the task schedule and select the time each day. According to the monitoring results of the server, 5-8 hours per morning for the low peak traffic. Due to the cleanup of some databases at 5-7, the time can be set at 8 o'clock in the morning.

MySQL Automatic backup------This is from the Web

Time= ' Date +%g%i%d%h%m ' #年月日时分

Db=products
echo "#产品库 \ r \ n" >/sqlbak/${db}_$time.sql #备份文件名为 [Library name _ month date. sql]
Mysqldump--add-drop-table-c--allow-keywords--force--password=1234567890--user=root $db >>/sqlbak/${db}_$ Time.sql


Db=news #另一个库
echo "#新闻库 \ r \ n" >/sqlbak/${db}_$time.sql
Mysqldump--add-drop-table-c--allow-keywords--force--password=1234567890--user=root $db |bzip2-9 >/sqlbak/${db} _$time.sql.bz2


#--add-drop-table is to add a delete command.
#-c is a full insert, each row inserts include the field name
#--force is a mistake and continues
#--allow-keywords the field name with an inverted quotation mark for the reserved word as field name.