Scheduled Remote MySQL backup using PHP

There's a nice 10 ways to backup mysql database from here.

There's also the use of MySQL Administrator or MySQL Workbench if both machines are Windows-based.

However, if you have the MySQL remotely and can be on Linux; some are on Windows; on different machines; and you want to grab backup from all of them, this one can be helpful.

On my development desktop, I have PHP and MySQL installed. Though it is not necessary to have these installed, it's rather handy on a development workstation.

1. Copy, paste it to your favorite text editor and save it somewhere as backup.php (or any filename you wish, just make sure it's recognized by PHP). For the sake of this tutorial, I saved it at D:\DBbackup\

<?php
/**
* This should be run on command prompt    
* @params
*		host
*		user
*		password
*		[db] defaults as {host}
*		[port] defaults as 3306
*/
#host,user,password,database,port
$DATABASES = array(
    array(
        'user' => 'user1'
        ,'host' => '8.8.8.8.8'
        ,'password' => 'mypassword'
        )
    ,array(
        'user' => 'user2'
        ,'host' => 'mysql.mysql.com'
        ,'password' => 'anotherpassword'
	,'port' => '6609'
        )
    ,array(
        'user' => 'user2'
        ,'host' => 'host.localdomain.net'
        ,'password' => 'itspassword'
	,'db' => 'mydatabase'
	,'port' => '3308'
        )
	# add as many database connection as needed using the structure above
    );
if (defined('STDIN')){ # make sure it's only run on command line interface (CLI)
    $start = date('H:i:s'); 
	$date = date('Ymd');
    foreach($DATABASES as $instance){
        $port = (isset($instance['port']) ? $instance['port'] : '3306');
        $db = (isset($instance['db']) ? $instance['db'] : $instance['user']);
        exec("mysqldump "
            ." -h".$instance['host'] #host
            ." -u".$instance['user'] #user
            ." -p".$instance['password'] #password
            ." -P".$port 
            ." --compress" #you may customize the options appropriate to your servers
            ." --create-options" 
            ." --extended-insert"
            ." --no-create-db"
            ." --quote-names"
            ." --verbose"
            ." ".$db
            ." --result-file=".dirname(__FILE__).DIRECTORY_SEPARATOR.$date."-".$db."@".$instance['host'].".sql"
            );
    }
# you may add other routines here like:
# compress using 7z
    exec("echo 'STARTED: ".$start." DONE: ".date('H:i:s')."' > ".$date.".txt"); #keep log
} else{
    echo 'This should be run on a command line';
}
?>

2. Test it on your local
a. open a command prompt on d:\dbbackup\ directory
> d:
> cd dbbackup
b. test php [PHPpath]php --info
> C:\PHP\php.exe D:\dbbackup\backup.php
c. it will create *.sql files on d:\dbbackup\ directory for every database you want to backup

3. Add as task to automate execution
a. Open Task Scheduler
b. Add new task that starts a program.
Program to execute: C:\PHP\php.exe
Parameters (or add it to Program to execute depending on your Windows version)
: D:\dbbackup\backup.php
This will basically execute as
C:\PHP\php.exe D:\dbbackup\backup.php
Put the desired frequency and schedule to backup.

The length of time to execute the script will depend on the amount of data and speed of your connection to the server.