Backup MySQL Database Using PHP

It is always a good idea to backup your MySQL database often as possible, because you don’t want to risk your entire work over some technical failure, you want to make latest copies of your database and keep it somewhere safe. Some web hosts do offer such backup feature, you can also backup things using PHP exec() function (if supported by host) but if not, you can use this PHP functions to back up your entire database tables.

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
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
function __backup_mysql_database($params)
{
    $mtables = array(); $contents = "-- Database: `".$params['db_to_backup']."` --\n";
   
    $mysqli = new mysqli($params['db_host'], $params['db_uname'], $params['db_password'], $params['db_to_backup']);
    if ($mysqli->connect_error) {
        die('Error : ('. $mysqli->connect_errno .') '. $mysqli->connect_error);
    }
   
    $results = $mysqli->query("SHOW TABLES");
   
    while($row = $results->fetch_array()){
        if (!in_array($row[0], $params['db_exclude_tables'])){
            $mtables[] = $row[0];
        }
    }

    foreach($mtables as $table){
        $contents .= "-- Table `".$table."` --\n";
       
        $results = $mysqli->query("SHOW CREATE TABLE ".$table);
        while($row = $results->fetch_array()){
            $contents .= $row[1].";\n\n";
        }

        $results = $mysqli->query("SELECT * FROM ".$table);
        $row_count = $results->num_rows;
        $fields = $results->fetch_fields();
        $fields_count = count($fields);
       
        $insert_head = "INSERT INTO `".$table."` (";
        for($i=0; $i < $fields_count; $i++){
            $insert_head  .= "`".$fields[$i]->name."`";
                if($i < $fields_count-1){
                        $insert_head  .= ', ';
                    }
        }
        $insert_head .=  ")";
        $insert_head .= " VALUES\n";       
               
        if($row_count>0){
            $r = 0;
            while($row = $results->fetch_array()){
                if(($r % 400)  == 0){
                    $contents .= $insert_head;
                }
                $contents .= "(";
                for($i=0; $i < $fields_count; $i++){
                    $row_content =  str_replace("\n","\\n",$mysqli->real_escape_string($row[$i]));
                   
                    switch($fields[$i]->type){
                        case 8: case 3:
                            $contents .=  $row_content;
                            break;
                        default:
                            $contents .= "'". $row_content ."'";
                    }
                    if($i < $fields_count-1){
                            $contents  .= ', ';
                        }
                }
                if(($r+1) == $row_count || ($r % 400) == 399){
                    $contents .= ");\n\n";
                }else{
                    $contents .= "),\n";
                }
                $r++;
            }
        }
    }
   
    if (!is_dir ( $params['db_backup_path'] )) {
            mkdir ( $params['db_backup_path'], 0777, true );
     }
   
    $backup_file_name = "sql-backup-".date( "d-m-Y--h-i-s").".sql";
         
    $fp = fopen($backup_file_name ,'w+');
    if (($result = fwrite($fp, $contents))) {
        echo "Backup file created '--$backup_file_name' ($result)";
    }
    fclose($fp);
}

Usage

Just call above function within your script and pass these parameters to it.

1
2
3
4
5
6
7
8
9
$para = array(
    'db_host'=> 'localhost',  //mysql host
    'db_uname' => 'username',  //user
    'db_password' => 'password123', //pass
    'db_to_backup' => 'wordpress', //database name
    'db_backup_path' => '/home/my_wordpress/', //where to backup
    'db_exclude_tables' => array('wp_comments','wp_w3tc_cdn_queue') //tables to exclude
);
__backup_mysql_database($para);

I’ve written this PHP code using MySQLi, and it has been used to backup WordPress database and few other successfully, but if you’ve found glitches please share.

9 Comments Add Comment

  • Thank you – what a great little function. One issue is that it doesn’t handle tables with hyphens in it.

    To allow this (it may already work on some other systems), can we change two lines…

    $results = $mysqli->query(“SHOW CREATE TABLE “.$table );
    to
    $results = $mysqli->query(“SHOW CREATE TABLE `”.$table .’`’);

    and

    $results = $mysqli->query(“SELECT * FROM “.$table);
    to
    $results = $mysqli->query(“SELECT * FROM `”.$table .’`’);

     Reply
  • aConvolutedConscious

    I get the date warning and below that I get “Backup file created ‘–sql-backup-28-06-2017–07-35-30.sql’ (26)”

    Then when I go to the file it’s only 1kb. Can you explain why this happens or help please?

     Reply
  • Hello,
    How to backup database on D: drive

     Reply
  • Hi,
    Thank you for this useful code.
    but you must check NULL value at line 53.

     Reply
  • Gabriele Guizzardi

    Sorry pal but at the end of the function you create the folder if not exist but after you save the file outside the folder… the following for me is better:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
        if (!is_dir ( $params['db_backup_path'] )) {
                mkdir ( $params['db_backup_path'], 0777, true );
        }
       
        $backup_file_name = "sql-backup-".date( "d-m-Y--h-i-s").".sql";
             
        $fp = fopen($params['db_backup_path'].$backup_file_name ,'w+');
        if (($result = fwrite($fp, $contents))) {
            echo "Backup file created '--$backup_file_name' ($result)";
        }
        fclose($fp);

    with $params[‘db_backup_path’] before the $backup_file_name.

    Anyway good job!

     Reply
  • hello,
    if we want to restore database of backup file ,how it can be done?

     Reply
  • Peter van Kroonenburg

    Nice script. Works fine. Only the path settings don’t work by me. The sql file always goes to the same dir as the php script.

     Reply
  • Nice Job,

    It works perfectly…

     Reply
  • Hello
    If we want to take backup of all databases in single shot, how it can be done?

    Thanks

     Reply