How To Back Up The Entire MySql Database with PHP

Have you experienced data loss because the database was never backed up? yes, I just experienced because my hard drive has been damaged. From this experience, I am trying to make a short script to back up the entire MySQL database in bulk. If you have hundreds of databases, and to backup manually definitely exhausting and very boring. For that, you can use a simple script that I created to perform backup the entire MySQL database with PHP. Let’s follow the tutorial below:

My way back up the entire MySQL database with PHP

1. Create a project folder named “backupdb“.
2. Create 3 pieces PHP file named index.php, dbconn.php, backup.php.
3. I am using PDO to communicate between PHP and MySql.
4. Copy the PDO class connection below and save it in dbconn.php.
   <?php $dbuserx='root'; $dbpassx='123456'; class dbconn { public $dblocal; public function __construct() { } public function initDBO() { global $dbuserx,$dbpassx; try { $this->dblocal = new PDO("mysql:host=localhost;dbname=mysql;charset=latin1",$dbuserx,$dbpassx,array(PDO::ATTR_PERSISTENT => true)); $this->dblocal->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); } catch(PDOException $e) { die("Sory, can't connect with your database, error :".$e->getMessage()); } } } ?>

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

   <?php $dbuserx='root'; $dbpassx='123456'; class dbconn { public $dblocal; public function __construct() {   } public function initDBO() { global $dbuserx,$dbpassx; try { $this->dblocal = new PDO("mysql:host=localhost;dbname=mysql;charset=latin1",$dbuserx,$dbpassx,array(PDO::ATTR_PERSISTENT => true)); $this->dblocal->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); } catch(PDOException $e) { die("Sory, can't connect with your database, error :".$e->getMessage()); }   } } ?>

   the script above is how to use PDO in PHP.

5. Copy the query class below and save it on file backup.php.
   <?php class backup extends dbconn { public function __construct() { $this->initDBO(); } public function show_db(){ $db = $this->dblocal; try { $stmt = $db->prepare("SHOW DATABASES"); $stmt->execute(); $stat[0] = true; $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC); return $stat; } catch(PDOException $ex) { $stat[0] = false; $stat[1] = $ex->getMessage(); return $stat; } } }

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

   <?php class backup extends dbconn { public function __construct() { $this->initDBO(); } public function show_db(){ $db = $this->dblocal; try { $stmt = $db->prepare("SHOW DATABASES"); $stmt->execute(); $stat[0] = true; $stat[1] = $stmt->fetchAll(PDO::FETCH_ASSOC); return $stat; } catch(PDOException $ex) { $stat[0] = false; $stat[1] = $ex->getMessage(); return $stat; } }   }

   I keep a collection of database queries into a class backup.

6. The script below is used to run mysql_dump in PHP and save it in index.php.
   <?php include "dbconn.php"; include "backup.php"; $restric_db = array('information_schema','mysql','performance_schema','phpmyadmin'); $sql = new backup(); $db = $sql->show_db(); echo "Process backup database ...<br />"; echo "Check folder is exists ? <br />"; $location = getcwd().'/'.date("Y-m-d"); if (!file_exists($location)) { mkdir($location, 0777, true); echo "Create new folder : ".$location."<br />"; } foreach ($db[1] as $key) { if(!in_array($key['Database'], $restric_db)) { $name = $location.'/'.$key['Database'].'-'.date('Y-m-d--H-i-s').'.sql'; if(strtoupper(PHP_OS) == strtoupper("LINUX")) { if($dbpassx=='') { $command = "mysqldump -u ".$dbuserx." ".$key['Database']." --routines > $name"; }else { $command = "mysqldump -u ".$dbuserx." -p".$dbpassx." ".$key['Database']." --routines > $name"; } }else{ if($dbpassx=='') { $command = "C:/xampp/mysql/bin/mysqldump -u ".$dbuserx." ".$key['Database']." --routines > $name"; }else { $command = "C:/xampp/mysql/bin/mysqldump -u ".$dbuserx." -p".$dbpassx." ".$key['Database']." --routines > $name"; } } exec($command,$output); echo "Backup database : ".$key['Database'].", success : $name <br />"; } } ?>

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

   <?php include "dbconn.php"; include "backup.php"; $restric_db = array('information_schema','mysql','performance_schema','phpmyadmin'); $sql = new backup(); $db = $sql->show_db(); echo "Process backup database ...<br />"; echo "Check folder is exists ? <br />"; $location = getcwd().'/'.date("Y-m-d"); if (!file_exists($location)) { mkdir($location, 0777, true); echo "Create new folder : ".$location."<br />"; } foreach ($db[1] as $key) { if(!in_array($key['Database'], $restric_db)) { $name = $location.'/'.$key['Database'].'-'.date('Y-m-d--H-i-s').'.sql'; if(strtoupper(PHP_OS) == strtoupper("LINUX")) { if($dbpassx=='') { $command = "mysqldump -u ".$dbuserx." ".$key['Database']." --routines > $name"; }else { $command = "mysqldump -u ".$dbuserx." -p".$dbpassx." ".$key['Database']." --routines > $name"; } }else{ if($dbpassx=='') { $command = "C:/xampp/mysql/bin/mysqldump -u ".$dbuserx." ".$key['Database']." --routines > $name"; }else { $command = "C:/xampp/mysql/bin/mysqldump -u ".$dbuserx." -p".$dbpassx." ".$key['Database']." --routines > $name"; } }   exec($command,$output); echo "Backup database : ".$key['Database'].", success : $name <br />"; } }   ?>

A brief description:

$restric_db=array('information_schema','mysql','performance_schema','phpmyadmin')
The above array is used to hold the database’s name that does not participate to back up

In this example, I create a folder with the format date(‘Y-m-d’) to accommodate the entire back up’s file. Each performs a backup process, the system will check whether the folder already exists or not. If no, the system will create a new folder.

Using PHP in_array function, the system will check the database which is not allowed to be backed up.

The following functions are used to check whether a server using windows XAMPP  or Linux.

Then the script will be executed using a command exec($command,$output)

Done, if you test it will be more or less like the following picture:

Download full source project how to backup the entire mysql database with php below

To facilitate the process of database backups on a regular basis, you can use crontabs on ubuntu or windows. I have not tried to do, I will update the next time.

Thus my article on How To Back Up The Entire MySql Database with PHP, hope useful