mysql backup script – 3 Ways to Automate MySQL Database Backups in PHP

mysql backup script using PHP – There are 3 Best methods you can use to take fully backup of your MySQL database step by step. Perform MySQL Database Backup using PHP. You can use it to dump a full database or only some tables.

Here is a tiny Best PHP script that We use to simply backup MySQL databases on Godaddy Linux web servers with Auto backup MySQL database PHP script.

mysql backup script – Perform MySQL Database Backup using PHP script

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

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

Read Also:  Remove Last Character from String in PHP

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

Read Also:  Woocommerce Get custom product attributes

<?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.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.