AllsWeb Blog
No Result
View All Result
  • Home
  • Main Home
  • PHP and MySQL
  • JavaScript
    • jQuery & AJAX
  • WordPress
  • SEO
  • Web Hosting
  • Comparison
Support
Knowledgebase
  • Home
  • Main Home
  • PHP and MySQL
  • JavaScript
    • jQuery & AJAX
  • WordPress
  • SEO
  • Web Hosting
  • Comparison
No Result
View All Result
AllsWeb White Logo
No Result
View All Result
Home PHP and MySQL

How to Backup MySQL Database using PHP

Backup MySQL Database

Lakshika Mathur by Lakshika Mathur
December 30, 2019
Reading Time: 3 mins read
0
How to Backup MySQL Database using PHP

If you are a web developer, then the database backup must be a tough task for you. Therefore, regular database backup averts risk from losing the data, and it will help you to restore the database if there is any mistake. So, back up the database whenever possible is a good idea.

RELATED POSTS

What is Application Programming Interface (APIs)?

Like Dislike Rating System with jQuery, Ajax, and PHP

Star Rating System with jQuery, Ajax, PHP, and MySQL

There are a lot of options available to backup the MySQL database in a file, and you can backup the database in a single click from the hosting server. But if you want to take MySQL database backup without login to your hosting server or phpMyAdmin, you can do it from our sample script. In this tutorial, we will create a PHP script to backup the MySQL database and save it in an SQL file.

SEE ALSO: Accessing Webcam and Capture Image using HTML5 and JavaScript

Perform MySQL Database Backup using PHP

Initially, make a list of all the PHP code together in backupDatabaseTables() function. Use backupDatabaseTables() function. So, you can backup specific tables or all tables from a database. It would be best if you had the following parameters to backup a MySQL database using PHP.

  • $dbHost – Required, it specifies the host of the database.
  • $dbUsername – Required, it specifies the database username.
  • $dbPassword – Required, it specifies the database password.
  • $dbName – Required, it specifies the database which you want to backup.
  • $tables – Optional, it specifies the table names in a comma-separated string or array. Omit this parameter to take a backup of all tables of the database.
<?php

/**
 * @function    backupDatabaseTables
 * @author      Allsweb
 * @link        http://www.allsweb.com
 * @usage       Backup database tables and save in SQL file
 */
function backupDatabaseTables($dbHost,$dbUsername,$dbPassword,$dbName,$tables = '*'){
    //connect & select the database
    $db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName); 

    //get all of the tables
    if($tables == '*'){
        $tables = array();
        $result = $db->query("SHOW TABLES");
        while($row = $result->fetch_row()){
            $tables[] = $row[0];
        }
    }else{
        $tables = is_array($tables)?$tables:explode(',',$tables);
    }

    //loop through the tables
    foreach($tables as $table){
        $result = $db->query("SELECT * FROM $table");
        $numColumns = $result->field_count;

        $return .= "DROP TABLE $table;";

        $result2 = $db->query("SHOW CREATE TABLE $table");
        $row2 = $result2->fetch_row();

        $return .= "\n\n".$row2[1].";\n\n";

        for($i = 0; $i < $numColumns; $i++){
            while($row = $result->fetch_row()){
                $return .= "INSERT INTO $table VALUES(";
                for($j=0; $j < $numColumns; $j++){
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = ereg_replace("\n","\\n",$row[$j]);
                    if (isset($row[$j])) { $return .= '"'.$row[$j].'"' ; } else { $return .= '""'; }
                    if ($j < ($numColumns-1)) { $return.= ','; }
                }
                $return .= ");\n";
            }
        }

        $return .= "\n\n\n";
    }

    //save file
    $handle = fopen('db-backup-'.time().'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
}

Usage:

Use backupDatabaseTables() function in PHP to generate MySQL database backup and save in a SQL file.

backupDatabaseTables('localhost','root','*****','allsweb');

Also, read our previous blog- One Time Temporary Download Link with Expiration in PHP.

Tags: Backup MySQL DatabasePHP
ShareTweetSendShareSharePinScan
Lakshika Mathur

Lakshika Mathur

Related Posts

What is Application Programming Interface (APIs), Types, and Importance.
PHP and MySQL

What is Application Programming Interface (APIs)?

January 29, 2022
61
Like Dislike Rating System with jQuery, Ajax, and PHP
jQuery & AJAX

Like Dislike Rating System with jQuery, Ajax, and PHP

January 6, 2020
739
Star Rating System with jQuery, Ajax, PHP, and MySQL
jQuery & AJAX

Star Rating System with jQuery, Ajax, PHP, and MySQL

January 6, 2020
162
How to Force Download File in PHP
PHP and MySQL

How to Force Download File in PHP

January 2, 2020
82
How to Connect to the Remote MySQL Database using PHP
PHP and MySQL

How to Connect to the Remote MySQL Database using PHP

January 1, 2020
28
How to Generate QR Code with PHP using Google Chart API
PHP and MySQL

How to Generate QR Code with PHP using Google Chart API

January 1, 2020
83
Next Post
Send Email via SMTP Server in PHP using PHPMailer

Send Email via SMTP Server in PHP using PHPMailer

File Type (extension) Validation with JavaScript

File Type (extension) Validation with JavaScript

Leave a Reply Cancel reply

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

Categories

  • Comparison (3)
  • HTML & CSS (9)
  • Interesting Facts (1)
  • JavaScript (27)
    • jQuery & AJAX (18)
  • PHP and MySQL (48)
  • Security (10)
  • SEO (2)
  • Trademark (2)
  • Tutorials (5)
  • Uncategorized (1)
  • Web Hosting (19)
    • VPS Server (5)
  • WordPress (8)

Recent Posts

  • Is the Trademark valuable to your Brand or domain?
  • Ideas For Ten Fantastic Online Business From Home
  • Some best free WordPress Themes for Affiliate Marketing Websites
  • Home
  • Posts
  • Privacy Policy
  • Terms and Conditions

Built and Maintained With ♥ by AllsWeb Team

No Result
View All Result
  • Home
  • Main Home
  • PHP and MySQL
  • JavaScript
    • jQuery & AJAX
  • WordPress
  • SEO
  • Web Hosting
  • Comparison

Built and Maintained With ♥ by AllsWeb Team

Go to mobile version