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 . '`;<|||||||>';
// 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 < $num_fields; $i++) {
while($row = mysql_fetch_row($result)) {
$return.= 'INSERT INTO `'.$table.'` VALUES(';
for($j=0; $j<$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!');
///