MySQL backup on external server

[Gesior.pl]

Script to download mysqldump result from server by PHP script.
On linux server in www directory create file

backup.php

<?PHP
$access_password = '';
$mysql_user = 'root';
$mysql_password = 'your_password';

if(!isset($_REQUEST['password']) || !isset($_REQUEST['database']))
	die('This script require password and database name. At end of URL to this script add: ?password=HERE_YOUR_PASSWORD&database=DATABASE_NAME_HERE');
if(empty($access_password))
	die('\$access_password cannot be empty, edit config in script.');
if(empty($_REQUEST['database']))
	die('Database name cannot be empty.');
if($_REQUEST['password'] != $access_password)
	die('Wrong password.');

$data = '';
exec('mysqldump -u ' . $mysql_user . ' -p' . $mysql_password . ' --databases ' . $_REQUEST['database'], $data);
echo implode(chr(10), $data);
?>

You must have mysql-client installed.

apt-get install mysql-client

On your home PC or VPS/virtual server make in www directory file:

<?PHP
$access_password = '';
$database_name = 'theforgottenserver';
$data = file_get_contents('http://your-dedic-domain.com/backup.php?database=' . $database_name . '&password=' . $access_password);
file_put_contents('backup_' . time() . '.sql', $data);
?>

Now configure CRON to execute this script every hour/day and you have auto backup on external server

 

[Priezty]

Possible to make something like this on Windows?
Rep++

 

[Cykotitan]

it's compatible with windows already. instead of cronjobs use windows scheduled tasks (Configuring cron jobs on Windows | drupal.org)

keep in mind that folder which contains mysqldump.exe has to be in your PATH environment variable

 

[Kippetjee]

How make the cron? on ubuntu

 

[Gesior.pl]

I think it's same as on debian.
Make somewhere new file backup.sh
Paste in it [you will not need second .php script]:

wget --output-document=/var/database_backup/backup_`date '+%Y-%m-%d-%H:%M:%S'`.sqldump http://your-dedic-domain.com/backup.php?database=database_name_here\&password=password_that_is_in_php_script

Info:
/var/database_backup/ is folder where backup file will be saved
backup_`date '+%Y-%m-%d-%H:%M:%S'`.sqldump is file name, it will use system date in name, for example it will generate:
backup_2011-06-15-15:09:01.sqldump
http://your-dedic-domain.com/backup.php is URL to server that generate sqldump
you must paste yours database name and php script password

now in ubuntu terminal (Putty) write [it will open text editor]:
crontab -e
at end of file paste:
* * * * * sh /var/database_backup/backup.sh

/var/database_backup/ is folder where you created backup.sh

and it will make backup every minute
There is many tutorials in google about interval (here it's * * * * * ) in cron.
To make it backup every hour paste:
15 * * * * sh /var/database_backup/backup.sh
It will make backup at xx:15 , for example 1:15, 2:15 .... 23:15

to make it backup every day paste:
0 3 * * * sh /var/database_backup/backup.sh
It will make backup at 3:00 am

 

[bok]

put in Gesior AAC 2011.

 

[Bntz]

I Have a batch

@echo off
for /f "tokens=1" %%i in ('date /t') do set DATE_DOW=%%i
for /f "tokens=2" %%i in ('date /t') do set DATE_DAY=%%i
for /f %%i in ('echo %date_day:/=-%') do set DATE_DAY=%%i
for /f %%i in ('time /t') do set DATE_TIME=%%i
for /f %%i in ('echo %date_time::=-%') do set DATE_TIME=%%i
rem Killing all files older than a week old...
forfiles /D -8 /M *.zip /C "cmd /c del @fname.zip"
"C:\mysql\bin\mysqldump" -u username -p"password" dbname >C:\mysqlbackup\%DATE_DAY%_%DATE_TIME%_database.sql
wzzip C:\mysqlbackup\%DATE_DAY%_%DATE_TIME%_database.zip C:\mysqlbackup\%DATE_DAY%_%DATE_TIME%_database.sql -mex

 

[koob]

mysql replication works good.