How to Automate MySQL Database Backups in PHP

Using PHP SQL Command through PHP, Using MySQL binary mysqldump and Using phpMyAdmin user interface for mysql backup scripts.

How to export database in MySQL using PHP code?

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);
?>

Using MySQL binary mysqldump through PHP

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);
?>

Backup MySQL database using cron and php

/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;
?>

How to Backup MySQL Database using PHP?

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];
}
?>

Create SQL Script for Table Data/Structure

<?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";
}
?>

Save and Download Database Backup File

<?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.

• This blog post was originally published at: https://www.pakainfo.com

#mysql  #sql  #database