Backing Up A MySQL Database With PHP Using MySQLi

I needed to download a database as SQL and thought ‘why re-invent the wheel?’ However I could not find any snippets that use MySQLi rather than MySQL. Also some of the coding was a bit bizarre to say the least leaving me thinking ‘how long did they play about with that before they got it to work’.


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
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
    try {
        // open the connection to the database - $host, $user, $password, $database should already be set
        $mysqli = new mysqli($host, $user, $password, $database);

        // did it work?
        if ($mysqli->connect_errno) {
            throw new Exception("Failed to connect to MySQL: " . $mysqli->connect_error);
        }

        header('Pragma: public');
        header('Expires: 0');
        header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
        header('Content-Type: application/force-download');
        header('Content-Type: application/octet-stream');
        header('Content-Type: application/download');
        header('Content-Disposition: attachment;filename="backup_'.date('Y-m-d_h_i_s') . '.sql"');
        header('Content-Transfer-Encoding: binary');

        // start buffering output
        // it is not clear to me whether this needs to be done since the headers have already been set.
        // However in the PHP 'header' documentation (http://php.net/manual/en/function.header.php) it says that "Headers will only be accessible and output when a SAPI that supports them is in use."
        // rather than the possibility of falling through a real time window there seems to be no problem buffering the output anyway
        ob_start();
        $f_output = fopen("php://output", 'w');

        // put a few comments into the SQL file
        print("-- pjl SQL Dump\n");
        print("-- Server version:".$mysqli->server_info."\n");
        print("-- Generated: ".date('Y-m-d h:i:s')."\n");
        print('-- Current PHP version: '.phpversion()."\n");
        print('-- Host: '.$host."\n");
        print('-- Database:'.$database."\n");

        //get a list of all the tables
        $aTables = array();
        $strSQL = 'SHOW TABLES';            // I put the SQL into a variable for debuggin purposes - better that "check syntax near '), "
        if (!$res_tables = $mysqli->query($strSQL))
            throw new Exception("MySQL Error: " . $mysqli->error . 'SQL: '.$strSQL);

        while($row = $res_tables->fetch_array()) {
            $aTables[] = $row[0];
        }

        // Don't really need to do this (unless there is loads of data) since PHP will tidy up for us but I think it is better not to be sloppy
        // I don't do this at the end in case there is an Exception
        $res_tables->free();

        //now go through all the tables in the database
        foreach($aTables as $table)
        {
            print("-- --------------------------------------------------------\n");
            print("-- Structure for '". $table."'\n");
            print("--\n\n");

            // remove the table if it exists
            print('DROP TABLE IF EXISTS '.$table.';');

            // ask MySQL how to create the table
            $strSQL = 'SHOW CREATE TABLE '.$table;
            if (!$res_create = $mysqli->query($strSQL))
                throw new Exception("MySQL Error: " . $mysqli->error . 'SQL: '.$strSQL);
            $row_create = $res_create->fetch_assoc();

            print("\n".$row_create['Create Table'].";\n");


            print("-- --------------------------------------------------------\n");
            print('-- Dump Data for `'. $table."`\n");
            print("--\n\n");
            $res_create->free();

            // get the data from the table
            $strSQL = 'SELECT * FROM '.$table;
            if (!$res_select = $mysqli->query($strSQL))
                throw new Exception("MySQL Error: " . $mysqli->error . 'SQL: '.$strSQL);

            // get information about the fields
            $fields_info = $res_select->fetch_fields();

            // now we can go through every field/value pair.
            // for each field/value we build a string strFields/strValues
            while ($values = $res_select->fetch_assoc()) {

                $strFields = '';
                $strValues = '';
                foreach ($fields_info as $field) {
                    if ($strFields != '') $strFields .= ',';
                    $strFields .= "`".$field->name."`";

                    // put quotes round everything - MYSQL will do type convertion (I hope) - also strip out any nasty characters
                    if ($strValues != '') $strValues .= ',';
                    $strValues .= '"'.preg_replace('/[^(\x20-\x7F)\x0A]*/','',$values[$field->name].'"');
                }

                // now we can put the values/fields into the insert command.
                print("INSERT INTO ".$table." (".$strFields.") VALUES (".$strValues.");\n");
            }
            print("\n\n\n");

            $res_select->free();

        }


    } catch (Exception $e) {
        print($e->getMessage());
    }


    fclose($f_output);
    print(ob_get_clean());
    $mysqli->close();

If you found this usefull then you might want to

Share

9 Responses to “Backing Up A MySQL Database With PHP Using MySQLi”

  • Freelance Programmer says:

    Nice one. This is good example for to get the MySQL database backup. It is massively helpful. It just made my work easier.

    Thanks.

  • Don’t worry I have found out how to output this with fwrite now.
    Just replace

    1
    print('-- Database:'.$database."\n");

    with

    1
    fwrite($f_output,"-- Database:".$database."\n");

    Also to Quang I had to modify your line to this, as it wouldn’t work as you posted it

    1
    $val = preg_replace('/[^(\x20-\x7F)\x0A]*/','',$values[$field-'>'+name]);
    • Pete Pete says:

      Be careful of how and where you write this file if you do not want it to be accessed by other people – I have seen backup systems that allow anyone to download the SQL files!. Even if your data is not necessarily private revealing your database name and structure would be useful to hackers. Although your system should be robust to XSS attacks etc it is still a worthwhile precaution to take.

  • Is there a way to save this to a file instead of downloading?
    Been messing with fwrite, but not sure how to do it.

    Thanks in advance.

    • Pete Pete says:

      Nobody has notice the error in my code – in fact I started to set it up so that it was easy to write to file or screen but then forgot what I was doing.
      The last three lines should be:

      1
      2
      3
      fputs($f_output,ob_get_clean());
      fclose($f_output);
      $mysqli->close();

      Near the top of the file you I have set up f_output to write to the browser by using the php://output stream. You should replace this with the filename you want i.e. replace

      1
      $f_output = fopen("php://output", 'w');

      with

      1
      $f_output = fopen("myfile", 'w');

      You can also loose all the header(… stuff

  • Yuvaraj says:

    Hi Great Code, I’ve faced small problem. While restore that, I’ve seen inserting in values contain it not finished if doesn’t contain ” it work fine. thank you

  • Just Me says:

    Awesome! The OO-code works nicely. Thanks.
    Please add code for procedural mysqli.
    Please add code for backup up the DB structure.
    (For others: Make sure your mySQLi extension is enabled in your PHP.ini configuration file.)


Leave a Reply

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

Captcha: * Time limit is exhausted. Please reload CAPTCHA.

This blog is kept spam free by WP-SpamFree.

Subscribe