• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
seegatesite header

Seegatesite.com

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

  • TOOLS
    • Bootstrap Navbar Online Generator
    • Customize Sidebar Menu Bootstrap 3
    • Bootstrap Demo
  • ADVERTISE
  • CATEGORIES
    • Android
    • Blogging Tips
    • Database
    • CSS
    • Info Gadget
    • Javascript
    • Linux
    • PHP
    • Various
    • WordPress
  • Q&A
  • PHP
  • JAVASCRIPT
  • JQUERY
  • ANGULAR
  • WORDPRESS
  • SEO
  • REACT
🏠 » PHP » How To Back Up The Entire MySql Database with PHP

How To Back Up The Entire MySql Database with PHP

By Sigit Prasetya Nugroho ∙ February 23, 2017 ∙ 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 />";
    }
    }
     
    ?>

Table of Contents

  • 1 A brief description:
  • 2 Thus my article on How To Back Up The Entire MySql Database with PHP, hope useful

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 :

  • Easy Way Convert SQL Query to Laravel Builder Using Orator
  • A Short Query To Get Details And Total Value With “ROLLUP” in MySQL
  • Using “str_replace PHP” In MySQL With REPLACE Function

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

URL : http://wp.me/a65Dpx-Gc

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 :

  • How To Replace String With Another String In PHP
  • Login Page – Tutorial CRUD Client and API Server Using JQuery And Lumen Part 2
  • Tutorial CRUD Client and API Server Using JQuery And Lumen Part 1
  • How To Solve Problems Illegal mix of collations (latin1_swedish_ci,IMPLICIT) In Laravel
  • How To Resolve No ‘Access-Control-Allow-Origin’ Header In Lumen
  • How To Create Custom Class In Laravel 5.5 For Beginners

Avatar for Sigit Prasetya Nugroho

About Sigit Prasetya Nugroho

This site is a personal Blog of Sigit Prasetya Nugroho, a Desktop developer and freelance web developer working in PHP, MySQL, WordPress.

Reader Interactions

Leave a Reply Cancel 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.

Primary Sidebar

Welcome to my Home,

Avatar for Sigit Prasetya NugrohoThis site is a personal Blog of Sigit Prasetya Nugroho, a Desktop developer and freelance web developer working in PHP, MySQL, WordPress.



Popular Articles

Checked checkbox AdminLTE Bootstrap in Jquery

November 4, 2014 By Sigit Prasetya Nugroho 7 Comments

Simple create date format validation with jqueryUI

December 21, 2014 By Sigit Prasetya Nugroho Leave a Comment

Create Simple Progress Bar for Fake Online Generator with Jquery

January 10, 2015 By Sigit Prasetya Nugroho Leave a Comment

22+ Coolest Free Jquery Plugin For Premium Theme

October 3, 2015 By Sigit Prasetya Nugroho Leave a Comment

Easy Build Your Anti Copy Paste Plugin

October 6, 2015 By Sigit Prasetya Nugroho Leave a Comment

Popular Tags

adminlte (15) adsense (13) adsense tips (4) affiliate amazon (13) amazon (12) Android (8) angular (16) angular 4 (12) angular 5 (4) asin grabber (3) Bootstrap (27) codeigniter (5) create wordpress theme (5) crud (8) css (6) free wordpress theme (7) google adsense (4) imacros (4) increase traffic (6) jquery (34) laravel (10) laravel 5 (5) learn android (5) modal dialog (5) mysql (6) nodeJs (4) optimize seo (4) pdo (6) php (30) plugin (53) pos (7) Publisher Tips (5) react (3) Reactjs (7) SEO (37) theme (17) tutorial angular (5) tutorial angular 4 (6) tutorial javascript (10) tutorial javascript beginners (4) twitter (3) widget (3) wordpress (18) wordpress plugin (13) XMLRPC (5)




  • About
  • Contact Us
  • Disclaimer
  • Privacy Policy
  • Terms and Conditions

©2021 Seegatesite.com