Blog

This is about web development and nagios monitoring.

Backup a MySQL database and upload file to a remote FTP server

Written from Klement

Are you tired to manually backup your MySQL or MariaDB by PHPmyAdmin or shell commands? I'm. So I created a little PHP script that backups your database and optionally transfers the compressed backup to another FTP server. It's also possibled to define multiple databases and FTP servers. You can get the following example on GitHub.

First we're setting the PHP time limit to 0 (unlimited) because a database backup could take a while if it's a big one. The two variables $db_count and $ftp_count will be used later to iterate through the database and FTP array if you like to add more than one.

set_time_limit(0);
$db_count   = -1;
$ftp_count  = -1;

 

Now we are defining the database credentials. If you want to add multiple databases just copy this code block multiple times.

//counter for the array
$db_count++;
//user for database login
$db[$db_count]['db_user'] 		= "";
//password for database login
$db[$db_count]['db_password'] 	= "";
//name of the database you want to backup
$db[$db_count]['db_name'] 		= "";
//name of the backup file with date and db name
$db[$db_count]['sql_file'] 		= "dump_".date('Y-m-d')."_{$db[$db_count]['db_name']}.sql";

 

If you just want to create a backup without uploading it to another FTP server you can skip this part. Otherwise define your FTP server credentials here.

//counter for the array
$ftp_count++;
//true or false for ftps protocol
$ftp[$ftp_count]['ftps'] 				= false;
//ip or hostname of your ftp server
$ftp[$ftp_count]['ftp_server'] 			= "";
//ftp user name for login
$ftp[$ftp_count]['ftp_user'] 			= "";
//ftp password for login
$ftp[$ftp_count]['ftp_password'] 		= "";
//true or false for passive mode (true should be ok the most time)
$ftp[$ftp_count]['ftp_passive_mode'] 	= true;
//leave it blank to upload into the root dir of your ftp or define a path
$ftp[$ftp_count]['ftp_remote_folder'] 	= "";

 

Now we need a loop to iterate through all databases from our config above.

foreach($db as $db_item)
{

...

}

 

Within this loop we're using the command line tool mysqldump to backup our database and then compress the file with gzip. With the exec function you can execute an external program via PHP. Some providers disabled this function or just permit some external commands. If you can't get it to work with the normal *.php file extension try out *.phpx. Some providers are using this extension to allow execution of external programs.

//Create SQL dump and save it as file (yes, there shouldn't be a space between -p and the password)
exec("mysqldump -u {$db_item['db_user']} -p{$db_item['db_password']} --allow-keywords --add-drop-table --complete-insert --hex-blob --quote-names {$db_item['db_name']} > {$db_item['sql_file']}");
//gzip the dumped file to save space
exec("gzip {$db_item['sql_file']}");

 

Still within the loop of the databases we are going to add this code to upload the gziped backup to the defined ftp servers.

//just go into the block if at least one ftp server has been defined
if($ftp_count >= 0)
{
    foreach($ftp as $ftp_item)
    {
    
    //Code for FTP upload of the next passage
    ...
    
    }
}

//Delete original *.sql file because we just keep the gziped file
if(file_exists($db_item['sql_file']))
    unlink($db_item['sql_file']);

 

The following code connects to your ftp server with the given login. If the FTP login was successful the script uploads the backup and closes the connection. Since we are within the database loop from above you can upload one backup file to multiple ftp servers.

//Initiate connection
if($ftp_item['ftps'])
	$connection_id = ftp_ssl_connect($ftp_item['ftp_server']);
else
	$connection_id = ftp_connect($ftp_item['ftp_server']);
	
if(!$connection_id)
	echo "Error: Can't connect to {$ftp_item['ftp_server']}\n";
	
//Login with user and password
$login_result = ftp_login($connection_id, $ftp_item['ftp_user'], $ftp_item['ftp_password']);

if(!$login_result)
	echo "Error: Login wrong for {$ftp_item['ftp_server']}\n";
	
//Passive mode?
ftp_pasv($connection_id, $ftp_item['ftp_passive_mode']);

//Upload file to ftp
if (!ftp_put($connection_id, $ftp_item['ftp_remote_folder']."/".$db_item['sql_file'].'.gz', $db_item['sql_file'].'.gz', FTP_BINARY))
{
	echo "Error: While uploading {$db_item['sql_file']}.gz to {$ftp_item['ftp_server']}.\n";
}

//Close ftp connection
ftp_close($connection_id);

 

It makes sense to set up a cronjob for the execution of this script if you want to create a daily based backup.

View the full code on GitHub.