Using PHP SQL Command through PHP, Using MySQL binary mysqldump and Using phpMyAdmin user interface for mysql backup scripts.
mysql backup script using SELECT INTO OUTFILE query
index.php
Here’s a basic MySQL backup script that you can customize to fit your specific needs:
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = '##****###';
$link = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $link ) {
die('Could not connect: ' . mysql_error());
}
$db_tbl_nm = "members_list";
$final_result_orignal_recovery_file = "/tmp/members_list.sql";
$sql_QQ = "SELECT * INTO OUTFILE '$final_result_orignal_recovery_file' FROM $db_tbl_nm";
mysql_select_db('pakainfo_v1');
$all_contents = mysql_query( $sql_QQ, $link );
if(! $all_contents ) {
die('Could not take data backup: ' . mysql_error());
}
echo "Backedup data successfully\n";
mysql_close($link);
?>
index.php
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = '##****###';
$link = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $link ) {
die('Could not connect: ' . mysql_error());
}
$db_tbl_nm = "members_list";
$final_result_orignal_recovery_file = "/tmp/members_list.sql";
$sql = "LOAD DATA INFILE '$final_result_orignal_recovery_file' INTO TABLE $db_tbl_nm";
mysql_select_db('pakainfo_v1');
$all_contents = mysql_query( $sql, $link );
if(! $all_contents ) {
die('Could not load data : ' . mysql_error());
}
echo "Loaded data successfully\n";
mysql_close($link);
?>
Simply save the .php file to a non publicly cron jobs accessible part of the server side as well as run main cpanel using cron Jobs: set script for “mysql backup”.
index.php
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = '##****###';
$final_result_orignal_recovery_file = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass ". "pakainfo_v1 | gzip > $final_result_orignal_recovery_file";
system($command);
?>
/usr/bin/php /home4/pakainfo/website-2021/pakainf-v1/backup-task.php
<?php
$DATABASE="db_name";
$DBUSER="root";
$DBPASSWD="#$%888****";
$PATH="/home4/pakainfo/website-2021/pakainf-v1/";
$FILE_NAME="pakainfo-cin-backup-" . date("Y-m-d") . ".sql.gz";
exec('/usr/bin/mysqldump -u '.$DBUSER.' -p'.$DBPASSWD.' '.$DATABASE.' | gzip --best > '.$PATH.$FILE_NAME);
echo "Good Luck, Database(".$DATABASE.") backup completed. Your Main File name: ".$FILE_NAME;
?>
Getting Database Table Names
<?php
$host = "localhost";
$username = "root";
$password = "#####**********####";
$database_name = "pakainf_v1";
$link = mysqli_connect($host, $username, $password, $database_name);
$link->set_charset("utf8");
$tables = array();
$sql = "SHOW TABLES";
$all_content = mysqli_query($link, $sql);
while ($data_res = mysqli_fetch_row($all_content)) {
$tables[] = $data_res[0];
}
?>
<?php
$mysql_backup_scripts = "";
foreach ($tables as $table) {
$sql_QQ = "SHOW CREATE TABLE $table";
$all_content = mysqli_query($link, $sql_QQ);
$data_res = mysqli_fetch_row($all_content);
$mysql_backup_scripts .= "\n\n" . $data_res[1] . ";\n\n";
$sql_QQ = "SELECT * FROM $table";
$all_content = mysqli_query($link, $sql_QQ);
$totalCounter = mysqli_num_fields($all_content);
for ($i = 0; $i < $totalCounter; $i ++) {
while ($data_res = mysqli_fetch_row($all_content)) {
$mysql_backup_scripts .= "INSERT INTO $table VALUES(";
for ($j = 0; $j < $totalCounter; $j ++) {
$data_res[$j] = $data_res[$j];
if (isset($data_res[$j])) {
$mysql_backup_scripts .= '"' . $data_res[$j] . '"';
} else {
$mysql_backup_scripts .= '""';
}
if ($j < ($totalCounter - 1)) {
$mysql_backup_scripts .= ',';
}
}
$mysql_backup_scripts .= ");\n";
}
}
$mysql_backup_scripts .= "\n";
}
?>
<?php
if(!empty($mysql_backup_scripts))
{
$final_result_orignal_recovery_file = $database_name . '_backup_' . time() . '.sql';
$fileHandler = fopen($final_result_orignal_recovery_file, 'w+');
$number_of_lines = fwrite($fileHandler, $mysql_backup_scripts);
fclose($fileHandler);
header('Content-Description: File Transfer');
header('Content-Type: application/octet-stream');
header('Content-Disposition: attachment; filename=' . basename($final_result_orignal_recovery_file));
header('Content-Transfer-Encoding: binary');
header('Expires: 0');
header('Cache-Control: must-revalidate');
header('Pragma: public');
header('Content-Length: ' . filesize($final_result_orignal_recovery_file));
ob_clean();
flush();
readfile($final_result_orignal_recovery_file);
exec('rm ' . $final_result_orignal_recovery_file);
}
?>
I hope you get an idea about Auto backup MySQL database PHP script.