Seegatesite.com

Seegatesite.com - Programming Tutorial , Sharing , How and Learn Together

🏠 » PHP » How To Back Up The Entire MySql Database with PHP

How To Back Up The Entire MySql Database with PHP

By ∙ ∙ PHP ∙ Leave a Comment

Share : TwitterFacebookTelegramWhatsapp

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

How To 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.

    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.

    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.

    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

Related Articles :

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.

Bulk Mysql Database Backup With Php

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.

1
2
3
4
5
if(strtoupper(PHP_OS) == strtoupper("LINUX"))
{
}else
{
}

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:

Back Up Database Mysql With Php

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

Another PHP Related Post :

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.