MySQL Databases

Hello all again,

How do you guys backup your MySQL databases?

I have a few programs that have an default back up that make an .sql file which is great but I also use OpenX which runs on PHP and has a database. That does not have an auto-back-up feature so do I just download the database and that’s it?

Any recommend for the tool?

Thanks

I use a cronjob to generate the backups running the command

suffix=$(date +%u)
mysqldump --opt -u<backup_username> -p<backup_password> -h <mysl_host> <database_name> > <file_name>-$suffix.sql
tar -czf <file_name>-$suffix.tar.gz <file_name>-$suffix.sql
uuencode <file_name>-$suffix.tar.gz | mail -s "MySQL Backup" <your_email_address>

I then have this run daily so it generates a new file each day of the week, and then it will overwrite the prior week. You can then have it e-mail the file to you or store it in a folder on your server that you can download later. You can even change the %u to %F so it writes the file for each day of the year (they will never get overwritten)

If you run on cPanel, it has MySQL database backup feature as well as full site backup.

On other way you can use some php code to make a backups, XCloner is also a great solution for backup management.

Or some custom PHP like:

// Function to backup the table and save it to a sql file
function backup_tables($host,$user,$pass,$name,$tables,$bckpfilename)
{
$link = mysql_connect($host,$user,$pass);
mysql_select_db($name,$link);
$return = “”;
mysql_query(“set names ‘utf8’”);

	// Get all of the tables
	if($tables == '*') {
		$tables = array();
		$result = mysql_query('SHOW TABLES');
		while($row = mysql_fetch_row($result)) {
			$tables[] = $row[0];
		}
	} else {
		if (is_array($tables)) {
			$tables = explode(',', $tables);
		}
}
	// Cycle through each provided table
	foreach($tables as $table) {
		$result = mysql_query('SELECT * FROM `'.$table.'`');
		$num_fields = mysql_num_fields($result);
	
		// First part of the output - remove the table
		$return .= 'DROP TABLE `' . $table . '`;&lt;|||||||&gt;';

		// Second part of the output - create table
		$row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE `'.$table.'`'));
		$return .= "\


" . $row2[1] . ";<|||||||>

";

		// Third part of the output - insert values into new table
		for ($i = 0; $i &lt; $num_fields; $i++) {
			while($row = mysql_fetch_row($result)) {
				$return.= 'INSERT INTO `'.$table.'` VALUES(';
				for($j=0; $j&lt;$num_fields; $j++) {
					$row[$j] = addslashes($row[$j]);
					$row[$j] = ereg_replace("\

“,”\
“,$row[$j]);
if (isset($row[$j])) {
$return .= '”’ . $row[$j] . ‘"’;
} else {
$return .= ‘“”’;
}
if ($j<($num_fields-1)) {
$return.= ‘,’;
}
}
$return.= ");<|||||||>
“;
}
}
$return.=”


";
}

	// Save the sql file
	$handle = fopen($filess.'.sql','w+');
	fwrite($handle,$return);
	fclose($handle);

// Close MySQL Connection
mysql_close();

}
@ini_set(‘max_execution_time’, 600); //300 seconds = 5 minutes
@ini_set(‘max_input_time’, 600);
@ini_set(‘memory_limit’, ‘256M’);
@ini_set(‘post_max_size’, ‘256M’);

// Call the backup function for all tables in a DB

backup_tables(‘localhost’, ‘database-user’, ‘database-pass’, ‘database-name’, ‘*’, ‘mysql-backup-file-name’);

  // Print the message
print('The backup has been created successfully!');

///