MySQL Database Backup

[bengoerz]

I had trouble finding simple, functional scripts to backup my MySQL database. So, after cobbling this together, I am sharing it with the world:

 

First, login to your server, customize the script below, and save it on your server with a text editor (like vi or nano).

 

 

#!/bin/sh


######USER SETTINGS#####
#########################################

### MySQL Server Login Info ###
MYUSER="your_mysql_user"
MYPASS="your_mysql_password"
# mysql server
MYHOST="localhost"    
MYDB="your_mysql_database"

### FTP server Setup ###
FTPDIR="/backups/surestudy"
FTPU="your_ftp_user"
FTPP="your_ftp_password"
FTPS="your_ftp_URL"

### System Setup ###
BACKUP="/home/your_system_user_account/backup"



######DO NOT MAKE MODIFICATION BELOW#####
#########################################


### Binaries ###
GZIP="$(which gzip)"
FTP="$(which ftp)"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"


# Move to Working directory
cd $BACKUP

### Today + hour in 24h format ###
NOW=$(date "+%Y%m%d")

DUMPFILE=$MYDB-$NOW.sql.gz

$MYSQLDUMP -q -c -u $MYUSER -h $MYHOST -p$MYPASS $ $i | $GZIP -9 > $DUMPFILE


### Dump backup using FTP ###
$FTP -n $FTPS <<END_SCRIPT
quote USER $FTPU
quote PASS $FTPP
cd $FTPDIR
mput $DUMPFILE
quit
END_SCRIPT


### Delete the backup file ###
rm $DUMPFILE

 

 

Make sure your script is executable:

$ chmod +x /path_to/backup_script.sh

 

 

Optionally, you can test-run your script and make sure that the backup file appears on the FTP server:

./path_to/backup_script.sh

 

 

Schedule the script to run regularly (say, 3:37AM on a 24-hour clock) via cron:

03 37 * * * /path_to/backup_script.sh

 

 

That's it -- you should now have a GZIP-compressed copy of your database uploaded to your FTP server regularly.

 

Works on CentOS 5.2 and should work on other Linux distros.

 

Note: This script DOES NOT delete old copies of the database that reside on the FTP server, so be careful if disk space is a problem.

[dreamwest]

Another way to do it to query the database and write the whole thing to a file

 

/*************** config ***************/
$mysql_host='';
$mysql_database='' database name
$mysql_username=''; // user name
$mysql_password=''; // password

$storing_dir = ""; // directory path with an ending slash

/******************* end config ************/
$file_name = $mysql_database."_".date('YmdHis').".sql";

$link = mysql_connect($mysql_host, $mysql_username, $mysql_password);
if (!$link)
{
   fwrite($fh, 'Could not connect: ' . mysql_error());
   exit();
}

$db_selected = mysql_select_db($mysql_database, $link);
if (!$db_selected)
{
fwrite($fh, 'Can\'t use $mysql_database : ' . mysql_error());
exit();
}

$myFile = $storing_dir . $file_name;
$fh = fopen($myFile, 'w') or die("can't open file");
_mysqldump($mysql_database);
fclose($fh);

function _mysqldump($mysql_database)
{
global $fh;
_mysqldump_schema_structure($mysql_database);
$sql="show tables;";
$result= mysql_query($sql);
if( $result)
{

	while( $row= mysql_fetch_row($result))
	{
		_mysqldump_table_structure($row[0]);
		_mysqldump_table_data($row[0]);
	}
}
else
{
	$content =  "/* no tables in $mysql_database */\n";
	fwrite($fh, $content);
}
mysql_free_result($result);
}
function _mysqldump_schema_structure($schema)
{
global $fh;
fwrite($fh, "/* database : `$schema` */\n");
$sql="show create schema `$schema`; ";
$result=mysql_query($sql);
if( $result)
{
	if($row= mysql_fetch_assoc($result))
	{
		fwrite($fh, $row['Create Database'].";\n\n");
	}
}
fwrite($fh, "USE `$schema`; \n\n");
mysql_free_result($result);
}

function _mysqldump_table_structure($table)
{
global $fh;
fwrite($fh, "/* Table structure for table `$table` */\n");
fwrite($fh, "DROP TABLE IF EXISTS `$table`;\n\n");
$sql="show create table `$table`; ";
$result=mysql_query($sql);
if( $result)
{
	if($row= mysql_fetch_assoc($result))
	{
		fwrite($fh, $row['Create Table'].";\n\n");
	}
}
mysql_free_result($result);
}

function _mysqldump_table_data($table)
{
global $fh;
$sql="select * from `$table`;";
$result=mysql_query($sql);
if( $result)
{
	$num_rows= mysql_num_rows($result);
	$num_fields= mysql_num_fields($result);

	if( $num_rows > 0)
	{
		fwrite($fh, "/* dumping data for table `$table` */\n");

		$field_type=array();
		$i=0;
		while( $i < $num_fields)
		{
			$meta= mysql_fetch_field($result, $i);
			array_push($field_type, $meta->type);
			$i++;
		}

		//print_r( $field_type);
		fwrite($fh, "insert into `$table` values\n");
		$index=0;
		while( $row= mysql_fetch_row($result))
		{
			fwrite($fh, "(");
			for( $i=0; $i < $num_fields; $i++)
			{
				if( is_null( $row[$i]))
					fwrite($fh, "null");
				else
				{
					switch( $field_type[$i])
					{
						case 'int':
							fwrite($fh, $row[$i]);
							break;
						case 'string':
						case 'blob' :
						default:
							fwrite($fh, "'".mysql_real_escape_string($row[$i])."'");

					}
				}
				if( $i < $num_fields-1)
					fwrite($fh, ",");
			}
			fwrite($fh, ")");

			if( $index < $num_rows-1)
				fwrite($fh, ",");
			else
				fwrite($fh, ";");
			fwrite($fh, "\n");

			$index++;
		}
	}
}
mysql_free_result($result);
fwrite($fh, "\n");
}