How to implement automatic remote backup in mysql

1. Implementing Local Automatic database backup is simple:
1. log in as root
2. scheduled tasks (automatically executed at every day/usr/bin/mysqlbackup)
Crontab
0 :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
2. The local directory/home /... /... /If the directory is missing data, an error will be prompted. This is normal.
3. Remote Backup can be implemented using ftp or scp commands. The method is simple. Check the usage of related commands and modify the mysqlbackup script.

Automatic daily backup of MySQL databases under win2003

Finally, let the server back up the MySQL database and automatically package it every morning, and delete the backup files five days ago.

1. Environment: windows server 2003 + Apache 2.0 + PHP5 + MySQL 4.0.26.

2. Assume that the PHP installation directory is D:/php, And the MySQL installation directory is D:/mysql.

3. Create the WinRAR directory in D:/php and copy WinRAR.exe and RARReg. KEY in the winrar installation directory to D:/php/WinRAR.

4. D:/php file mysql_backup.php:

/*/////////////////////////
# FileName: mysql_backup.php
# Author: faisun
# Website: http://www.softpure.com
////////////////////////*/
// Save the directory. The path must use a backslash. You need to manually create it.
$ Store_folder = 'd: \ databse_backup ';
// User name and password
// This account must have the permission to operate [all] databases and files
// 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 the file five days ago
@ Unlink ("$ store_folder \". date ("Ymd", $ time-86400*5). ". rar ");
Echo "\ nOK! \ N ";
>

5. D:/php creates the file mysql_backup.bat, which contains only one sentence:

Php.exe mysql_backup.php

6. Double-click the bat file to run the task. If the file can be backed up, OK. Next, add the task plan.

7. add D:/php/mysql_backup to the task schedule. Select daily as the time. based on the monitoring results of the server, the traffic is low every morning from 5 to 8. because some database cleanup operations are performed during the 5-7 period, the time can be set to eight o'clock in the morning.

Automatic mysql backup ------ this article comes from the mobile network

Time = 'date + % G % I % d % H % m' # time of year, month, and day

Db = products
Echo "# product library \ r \ n">/sqlbak/$ {db }_$ time. SQL # The backup file name is [database name_year Month day hour. SQL]
Mysqldump -- add-drop-table-c -- allow-keywords -- force -- password = 1234567890 -- user = root $ db>/sqlbak/$ {db} _ $ time. SQL

Db = news # Another database
Echo "# News library \ r \ n">/sqlbak/$ {db} _ $ time. SQL
Mysqldump -- add-drop-table-c -- allow-keywords -- force -- password = 1234567890 -- user = root $ db | bzip2-9>/sqlbak/${db1__$time. SQL .bz2

# -- Add-drop-table is to add a DELETE command.
#-C is the complete insert. each row of insert includes the field name.
# -- Force
# -- Allow-keywords: backquotes are added to the field names when reserved words are used as field names.