GNU/Linux, Open Source, Cloud Computing, DevOps and more...

Using PHP to backup MySQL databases

88 comments

This post shows how to perform a partial or full backup of MySQL databases using only PHP code. This is very useful for example when a client doesn’t provide you with access data to the database of a web application and you only have an FTP connection available, or when you do have the connection data to the database but you can’t access it through the network (only available from localhost) and you can’t use tools like mysqldump to back up the information you need because you don’t have privileges for this, or simply because you have no access to a shell to which connect and run commands.

UPDATE (08-28-2017): moved the source code from myphp-backup.php and myphp-restore.php scripts to my GitHub daniloaz/myphp-backup repositoryhttps://github.com/daniloaz/myphp-backup

Backup of MySQL database tables

In any of the above scenarios, simply by copying and executing my myphp-backup.php PHP script as indicated below, you will be able to make a full or partial (only some tables) export of a database to a .sql or .sql.gz file that you will be able to download later from the same FTP account. The script includes the Backup_Database class that performs all the necessary operations and has different configuration options to control in which subdirectory you want the backup files to be stored and if you want them to be compressed with gzip or not. You’ll also need to establish the user credentials for accessing the database, as well as the name of that database:

/**
 * Define database parameters here
 */
define("DB_USER", 'your_username');
define("DB_PASSWORD", 'your_password');
define("DB_NAME", 'your_db_name');
define("DB_HOST", 'localhost');
define("BACKUP_DIR", 'myphp-backup-files'); // Comment this line to use same script's directory ('.')
define("TABLES", '*'); // Full backup
//define("TABLES", 'table1 table2 table3'); // Partial backup
define("CHARSET", 'utf8');
define("GZIP_BACKUP_FILE", true); // Set to false if you want plain SQL backup files (not gzipped)

PHP applications usually have a configuration file such as config.php or similar in which you probably can find database user and password if you don’t remember them or if your customer has not provided you with this information.

If you don’t have sufficient permissions to create the subdirectory where your backup files will be stored (by default myphp-backup-files/), you can use an existing directory. PHP applications usually have some subdirectory with enough permissions to create new files (cache, tmp, temp, etc.), which is where you will tell myphp-backup.php script to leave the file with the database backup. You can also use the root directory itself (DocumentRoot). To do this, simply assign a point (‘.’) to the BACKUP_DIR constant.

The .sql.gz or .sql file generated as a result of using the Backup_Database class of myphp-backup.php script will have a name with the following format (myphp-backup-{DB_NAME}-YYYYYYmmdd_HHHMMSS.sql.gz):

$ ls myphp-backup-files/*.sql.gz
myphp-backup-files/myphp-backup-smf-20160131_111735.sql.gz

Restoring tables from a MySQL database

The previous file will contain compressed (or not) SQL code ready to restore the structure and content of the original database in another database or in the same one but replacing the existing data with those of your backup file. To perform the restoration you will simply run the other script in my repository: myphp-restore.php. The only thing you will have to do to perform the restoration is to upload both the myphp-restore.php script and the backup file itself to a folder in which you have write permissions on the server where you want to import data and run it through your browser (https://www.example.com/myphp-restore.php) or from command line. Don’t forget to set backup file and folder names before using the BACKUP_FILE and BACKUP_DIR constants.

All the configuration options available for myphp-restore.php script are as follows:

/**
 * Define database parameters here
 */
define("DB_USER", 'your_username');
define("DB_PASSWORD", 'your_password');
define("DB_NAME", 'your_db_name');
define("DB_HOST", 'localhost');
define("BACKUP_DIR", 'myphp-backup-files'); // Comment this line to use same script's directory ('.')
define("BACKUP_FILE", 'your-backup-file.sql.gz'); // Script will autodetect if backup file is gzipped or not based on .gz extension
define("CHARSET", 'utf8');

The script will automatically detect whether the backup file is compressed with gzip or not based on the .gz extension, so you don’t have to make any additional settings for this.


Error: Your Requested widget " ai_widget-6" is not in the widget list.
  • [do_widget_area above-nav-left]
    • [do_widget_area above-nav-right]
      • [do_widget_area footer-1]
        • [do_widget id="wpp-4"]
      • [do_widget_area footer-2]
        • [do_widget id="recent-posts-4"]
      • [do_widget_area footer-3]
        • [do_widget id="recent-comments-3"]
      • [do_widget_area footer-4]
        • [do_widget id="archives-4"]
      • [do_widget_area logo-bar]
        • [do_widget id="oxywidgetwpml-3"]
      • [do_widget_area menu-bar]
        • [do_widget id="search-3"]
      • [do_widget_area sidebar]
        • [do_widget id="search-4"]
        • [do_widget id="ai_widget-2"]
        • [do_widget id="categories-5"]
        • [do_widget id="ai_widget-3"]
        • [do_widget id="ai_widget-4"]
        • [do_widget id="ai_widget-5"]
      • [do_widget_area sub-footer-1]
        • [do_widget id="text-4"]
      • [do_widget_area sub-footer-2]
        • [do_widget_area sub-footer-3]
          • [do_widget_area sub-footer-4]
            • [do_widget_area upper-footer-1]
              • [do_widget id="search-2"]
              • [do_widget id="recent-posts-2"]
              • [do_widget id="recent-comments-2"]
              • [do_widget id="archives-2"]
              • [do_widget id="categories-2"]
              • [do_widget id="meta-2"]
            • [do_widget_area upper-footer-2]
              • [do_widget_area upper-footer-3]
                • [do_widget_area upper-footer-4]
                  • [do_widget_area widgets_for_shortcodes]
                    • [do_widget id="search-5"]
                    • [do_widget id="ai_widget-6"]
                  • [do_widget_area wp_inactive_widgets]
                    • [do_widget id="wpp-2"]
                    • [do_widget id="text-1"]
                    • [do_widget id="recent-posts-3"]
                    • [do_widget id="categories-3"]
                    • [do_widget id="archives-3"]
                    • [do_widget id="icl_lang_sel_widget-3"]

                  Source code of backup and restore scripts

                  You can find the source code for both backup and restoration scripts in my Github daniloaz/myphp-backup repository at https://github.com/daniloaz/myphp-backup.

                  daniloaz-myphp-backup: Simple and fast MySQL backups using PHP

                  And nothing else. If you can think of any additional feature that you miss and might be useful for you or other users, don’t hesitate to leave a comment. Good luck!

                   

                  About the author

                  Daniel López Azaña
                  Freelance AWS Cloud Solution Architect & Linux Sysadmin

                  Entrepreneur, a generator of ideas and restless mind. Passionate about new technologies, especially Linux systems and Open Source Software. I also like to write about Technology News, Cloud Computing, AWS, DevOps, DevSecOps, System Security, Web Development and Programming, SEO, Science, Innovation, Entrepreneurship, etc.

                  DanielUsing PHP to backup MySQL databases

                  Related Posts

                  88 comments

                  Join the conversation
                  • George_p - 23/03/2012 reply

                    Hi Daniel,
                    thanks for the excellent work. Saving the database is very useful. I tried the class on different databases and it works great. Only on a OpenCart db returns errors for only 3 tables: OPTION, ORDER and RETURN

                    this is the error

                    Backing up return table…
                    Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in /home/………………/run.php on line 119

                    Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/………………/run.php on line 122
                    OK

                    I can not understand why!
                    Thanks

                    Daniel - 24/03/2012 reply

                    Hi George,

                    Thanks for the feedback. I think that the three tables you mentioned have some kind of corruption, and that’s why you get those warning messages. I got the same results when I tried to backup a database with corrupted tables:



                    Backing up livehelp_transcripts table… OK
                    Backing up livehelp_users table… OK
                    Backing up livehelp_visit_track table… OK
                    Backing up livehelp_visits_daily table…
                    Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/daniloaz.com/httpdocs/crafty/bd_export.php on line 119

                    Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /var/www/vhosts/daniloaz.com/httpdocs/crafty/bd_export.php on line 122

                    As you can see next, the failing livehelp_visits_daily table is corrupted:

                    mysql> CHECK TABLE livehelp_visits_daily FAST QUICK;
                    +——————————+——-+——-+—————————-+
                    | Table | Op | Msg | Msg_text |
                    +——————————+——-+——-+—————————-+
                    | crafty.livehelp_visits_daily | check | warn | Table is marked as crashed |
                    | crafty.livehelp_visits_daily | check | error | Size of indexfile is: 892928
                    Should be: 897024 |
                    | crafty.livehelp_visits_daily | check | error | Corrupt |
                    +——————————+——-+——-+—————————-+
                    3 rows in set (0.01 sec)

                    Hope this helps!

                    Regards.

                    George_p - 25/03/2012 reply

                    Hi Daniloaz,
                    is true, in fact I checked the tables and had a few problems. Thank you!

                    George_p - 27/03/2012 reply

                    Hi daniloaz, I double checked again and i must say that the error occurred
                    again.
                    However I solved it.
                    The problem occurs only on a server where I run the script.
                    To fix I added this symbol “`” before and after the name of all code contain
                    tables.

                    For exemple:
                    $result = mysql_query(‘SELECT * FROM `’.$table.’`’);
                    $numFields = mysql_num_fields($result) or die(mysql_error());

                    I hope can help someone who has had the same problem!
                    Thanks

                    Daniel - 12/06/2012 reply

                    Yes, it’s true. When a table name matches an internal command of MySQL, the script fails to export that table. This is solved as you say adding ` and ` before and after the name of the table. Thank you very much for your clarification!

                  • Salvo - 09/06/2012 reply

                    Hi! I used something like this to backup my database… Also, I used the Dropbox API to copy the backup directly inside “Backup” folder of my Dropbox… I wrote an post on it..check it out http://blog.salvocannamela.it/dropbox-api-php/
                    The backup is automatic with setcronjob.com

                    What do you think about this? 🙂

                    Daniel - 12/06/2012 reply

                    It seems very interesting. I will examine it more closely. Thanks for your input!

                  • ankit - 11/06/2012 reply

                    thanks ,too good script

                  • Jorge - 09/07/2012 reply

                    Thanks men, the only code what i find and works!!!

                  • vina - 07/08/2012 reply

                    sorry , i have this :
                    Deprecated: Function ereg_replace() is deprecated in C:xampphtdocsinteld.php on line 133

                    i cant understand why .. help me please 🙂

                    Daniel - 07/08/2012 reply

                    ereg_replace() function is deprectaded in PHP 5.3. Sorry, I should update the script. In the mean time, you can avoid this problem just replacing calls to ereg_replace() function with preg_replace(), wich have the same syntax than ereg_replace and works properly in PHP 5.3.

                    Burhan Ibrahimi - 24/03/2015 reply

                    the preg_replace functions is not working everything is blank and not fild in backup file..

                  • vina - 07/08/2012 reply

                    have you the script for restore ?

                    Daniel - 07/08/2012 reply

                    No, sorry, but you can restore using the command line like this:

                    $ mysql -u db_user -p db_name < db-backup-db_name-20120807111744.sql db-backup-db_name-20120807111744.sql is the resulting file from the above script excution.

                  • Sergio N Hernandez - 21/12/2012 reply

                    Thank you very much

                    You saved my life, I’ve combined your code with “Windows Scheduled Tasks” and this tool http://code.google.com/p/auto-mysql-backup-windows/ (it’s free)

                    It works fine

                    Regards

                  • mazzoeth - 23/01/2013 reply

                    sorry , i have this :
                    Warning: preg_replace() [function.preg-replace]: Empty regular expression in C:xampphtdocsdatakaderBCABackup_Database.php on line 133

                  • Genwons - 18/02/2013 reply

                    thanks for the codes.. but i m having the error “Deprecated: Function ereg_replace() is deprecated in C:wampwwwTry123 – ORIGINAL – WITHOUT SUB-CATEGORYtry123.in.datap18.php on line 133”.

                    Kindly hel..

                    Daniel - 02/04/2013 reply

                    Please, check comment #5 in this thread.

                  • John - 09/03/2013 reply

                    Hi

                    Im getting this output :

                    Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/abc/public_html/Backup_Database.php on line 98

                    Backup result: OK

                    Is there anyway i can gzip the sql file and save?

                    John

                    Daniel - 19/03/2013 reply

                    Hi John, have a look to the first comment in this thread and subsequent replies and you will be able to fix that warning.

                    My class doesn’t implement gzip compression, but you can try this method from Stackoverflow: http://stackoverflow.com/questions/6073397/how-do-you-create-a-gz-file-using-php

                  • bcehprx - 22/04/2013 reply

                    I could not get a direct substitution of ereg_replace by preg_replace to work. The data
                    was just replaced with a null. Nor could I find any easy help in the manuals. Life is too
                    short so change the error reporting to:
                    error_reporting (E_ALL ^ E_DEPRECATED);

                  • Jippie - 11/08/2013 reply

                    Spent some time updating the script so it runs without warnings on younger installs.

                    http://git.linformatronics.nl/gitweb/?p=backupDatabase.php;a=summary

                    – fixed ereg_replace
                    – ported mysql_ => PDO

                  • Rick Leijten - 12/10/2013 reply

                    Great script, but when I was backing up my Magento DB it seems to be “sort of a timed out”. After a couple tries it gets “stuck” without any error report on the table “report_event”. On small databases the script works great, but do you have any tips how I can let the session last longer so it can finish the whole DB backup?

                  • Anthony - 19/11/2013 reply

                    Perfectly made script – but it fails with big databases (with tables over 10k rows). Needs adaptation useing data splitting to chunks for both MySQL queries (… LIMIT , ) and file writing – otherwise you will get either “<ySQL client is out of memory" or null-sized dump files.

                  • Rachid - 25/11/2013 reply

                    It works fine, great work thank you so much,
                    I had an old Joomla website with only FTP access and I was able to backup the whole database.

                    Again Thank you so much

                  • antonis - 03/02/2014 reply

                    Hello,

                    thanks for the perfect script!
                    It worked perfect for 2 wordpress sites and one joomla.
                    But in 2 opencart sites, when I try to restore the database to a new one I get this error:

                    DROP TABLE IF EXISTS OPTION ;
                    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘option’ at line 1

                    Any idea why?

                    Daniel - 06/02/2014 reply

                    Thanks! But the script is still far from perfect. In fact, the problem you mentioned is due to script’s wrong interpretation of the reserved word OPTION, wich is the same name you used for your table. Use an alternative name for your table (using a custom prefix is always a good idea), or quote the table name. Hope this helps!

                  • Dinu - 07/08/2014 reply

                    Hi all, if you are looking for a more advanced backup scheme, we have just developed web3backup; it’s a PHP script that backs up MySQL, SVN, local files with binary incremental backup, and rotates daily, weekly, monthly and yearly backups. You can get it at:
                    http://www.exteon.ro/en/products/programming-tools/web3backup

                  • ibrahim - 30/08/2014 reply

                    where is the export database ??
                    i don’t find it

                    also i get this error

                    Warning: fopen(cachedb-backup-nhegroup-20140830-165015.sql) [function.fopen]: failed to open stream: No such file or directory in C:AppServwwwnhegroupexport.php on line 163

                    Warning: fwrite(): supplied argument is not a valid stream resource in C:AppServwwwnhegroupexport.php on line 164

                    Warning: fclose(): supplied argument is not a valid stream resource in C:AppServwwwnhegroupexport.php on line 165
                    Backup result: OK

                  • sawan - 03/09/2014 reply

                    thanks for this script

                  • haji - 16/09/2014 reply

                    thanks a million.. works great!
                    I tried several other db backup scripts which failed miserably 🙁
                    but this one worked like a charm 🙂 🙂 🙂

                  • Mitesh Patel - 19/09/2014 reply

                    Thank you very much. Your script is very effective.

                  • Francesco - 23/09/2014 reply

                    Hi Danilo, thanks for the script. I have a problem with preg_replace function.
                    After i’ve changed the ereg_replace() with new preg_replace i receive an error “Warning: preg_replace(): Empty regular expression in…”
                    Backup result: OK….
                    Can you help me to resolve?

                    Daniel - 23/09/2014 reply

                    Try to put slashes before and after the regular expression, as preg_replace() function requires them. Write something like preg_replace(“/n/”,”\n”,$row[$j]);

                  • Francesco - 24/09/2014 reply

                    Simple and effective. Thanks so much Daniel.
                    Have a nice day

                  • Gene - 26/09/2014 reply

                    Hi how again do i use your backup script for a restore script?? where is the line i need to replace?

                    Daniel - 26/09/2014 reply

                    This script is only able to perform backups generating a SQL file wich you can restore via mysql command, but you can’t restore it via PHP. You would have to make major changes to the script to achieve it. Sorry, it’s a pending task…

                  • Mark - 09/10/2014 reply

                    Thanks for the script, just what i’ve been looking for!
                    I’ve executed the PHP script and got the “Backup result: OK” message but i cant find the resulting SQL file? Where would I need to look for it?
                    Thanks again,
                    Mark.

                    Daniel - 09/10/2014 reply

                    Backup destination is set using the OUTPUT_DIR constant defined at the the beginning of the script. In my case I set the cache directory, but this directory does’t have to exist in all installations where you run the script. Therefore, you should define a directory that is valid in your particular case, or not directory at all and let the script to save the file in your webroot directory.

                  • imran - 13/10/2014 reply

                    Hello Daniel,
                    very Nice script for database backup.
                    but my table in 48698 records that time this script can’t take backup.
                    There is display message like this “Backing up tags table… OK Backing up third_party_review table… OK Backing up third_party_review_data table…”

                    third_party_review_data table in 48698 records therefor can’t take backup.
                    any suggestion for get big database table backup.

                  • Mr . Chien - 31/10/2014 reply

                    Thanks you !

                  • Michelle Firstiant - 07/11/2014 reply

                    Thank you for the script,
                    But how to make my browser auto download the file ?
                    I only got “Backing up df_home table… OK” but i don’t know where the file go. Can you help me ? Thanks before 🙂

                  • Jean Letourneau - 21/11/2014 reply

                    Thanks you
                    for your script. That run very well!

                  • James - 20/12/2014 reply

                    This script replaces NULL with “”. So my NULL date values were getting imported as 0000-00-00 because they were the wrong format. To fix this, simply add an if/else for NULL:

                    if (isset($row[$j]))
                    {
                    if($row[$j]!=NULL) {
                    $sql .= “‘”.$row[$j].”‘” ;
                    }
                    else {
                    $sql .= “NULL” ;
                    }
                    }

                    Note how the NULL is not in quotes. (I’ve also changed the double quotes to single too)

                    Also, for some reason, I have changed the * to a specific table name, yet it somehow export all tables in the database. I can’t see why this happens?

                  • bok - 11/01/2015 reply

                    hello daniel i want to know how to backup the VIEWS in mysql can you give sample codes

                    -thanks in advance

                  • Chris - 18/01/2015 reply

                    it doesn`t take the values from table :
                    INSERT INTO language VALUES(“”,””,””,””,””,””,””);
                    this is a part from saved SQL file

                  • anj - 03/02/2015 reply

                    Hi! Thanks for posting this. I tried this script and did some modification because I’m using mysqli.
                    I can now have the sql backup file but when I open it, the values in every table are all “0”.
                    (e.g. INSERT INTO info VALUES(“0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0″,”0”); ) How am I going to fix it? Hoping for you assistance. Thanks.

                  • blender - 06/02/2015 reply

                    Hi,
                    I have changed the ereg_replace function with preg_replace but its not goes fine.
                    It gives error like
                    Warning: preg_replace(): Empty regular expression
                    Help me to make this happen.
                    Thank you

                    Mariana - 11/08/2015 reply

                    You can use str_replace, instead:
                    $row[$j] = str_replace( array(“rn”, “r”, “n”) , ‘\r\n’ , $row[$j] );

                    * Note that I used double-quotes on first argument, and single-quotes on the second, because I want it on a string on the .sql code. Otherwise, they will be interpreted by PHP. (You may use double-quotes if you want). I also included other options, ’cause some servers uses rn

                  • Renjit - 16/03/2015 reply

                    Sir, I am getting following error. Kindly please help

                    Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given in /home/public_html/find/html/dbbackup.php on line 101
                    Backup result: OK

                  • aniket - 24/03/2015 reply

                    hii ,
                    i want to get database backup automatically using php code for windows.
                    i have wamp server , win7 os , mysql. Can you give me proper php code for this.
                    pls give me reply fast…

                  • Carlos Guzman - 09/04/2015 reply

                    Hi, this ready correctly for version PHP 5.5, change to new msqli.

                    <?php
                    /**
                    * This file contains the Backup_Database class wich performs
                    * a partial or complete backup of any given MySQL database
                    * @author Daniel López Azaña
                    * @version 1.0
                    */

                    // Report all errors
                    error_reporting(E_ALL);

                    /**
                    * Define database parameters here
                    */
                    define(“DB_USER”, ‘root’);
                    define(“DB_PASSWORD”, ”);
                    define(“DB_NAME”, ‘backup’);
                    define(“DB_HOST”, ‘localhost’);
                    define(“OUTPUT_DIR”, ‘home’);
                    define(“TABLES”, ‘*’);

                    /**
                    * Instantiate Backup_Database and perform backup
                    */
                    $backupDatabase = new Backup_Database(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
                    $status = $backupDatabase->backupTables(TABLES, OUTPUT_DIR) ? ‘OK’ : ‘KO’;
                    echo ”

                    Backup result: “.$status;

                    /**
                    * The Backup_Database class
                    */
                    class Backup_Database {
                    /**
                    * Host where database is located
                    */
                    var $host = ‘localhost’;

                    /**
                    * Username used to connect to database
                    */
                    var $username = ‘root’;

                    /**
                    * Password used to connect to database
                    */
                    var $passwd = ”;

                    /**
                    * Database to backup
                    */
                    var $dbName = ‘backup’;

                    /**
                    * Database charset
                    */
                    var $charset = ‘utf-8’;

                    /**
                    * Constructor initializes database
                    */

                    function Backup_Database($host, $username, $passwd, $dbName, $charset = ‘utf8’)
                    {
                    $this->host = $host;
                    $this->username = $username;
                    $this->passwd = $passwd;
                    $this->dbName = $dbName;
                    $this->charset = $charset;

                    $this->initializeDatabase();
                    }

                    protected function initializeDatabase()
                    {
                    $conn = mysqli_connect($this->host, $this->username, $this->passwd);
                    mysqli_select_db( $conn, $this->dbName);
                    if (! mysqli_set_charset ( $conn, $this->charset ))
                    {
                    mysql_query(‘SET NAMES ‘.$this->charset);
                    }
                    }

                    /**
                    * Backup the whole database or just some tables
                    * Use ‘*’ for whole database or ‘table1 table2 table3…’
                    * @param string $tables
                    */
                    public function backupTables($tables = ‘*’, $outputDir = ‘.’)
                    {
                    try
                    {
                    /**
                    * Tables to export
                    */

                    if($tables == ‘*’)
                    {
                    $tables = array();

                    $conn = mysqli_connect($this->host, $this->username, $this->passwd);
                    mysqli_select_db( $conn, $this->dbName);
                    if (! mysqli_set_charset ( $conn, $this->charset ))
                    {
                    mysql_query(‘SET NAMES ‘.$this->charset);
                    }

                    $result = mysqli_query($conn, ‘SHOW TABLES’);

                    while ( $row = mysqli_fetch_array( $result ) )
                    {
                    $tables[] = $row[0];
                    }
                    }
                    else
                    {
                    $tables = is_array($tables) ? $tables : explode(‘,’,$tables);
                    }

                    $sql = ‘CREATE DATABASE IF NOT EXISTS ‘.$this->dbName.”;nn”;
                    $sql .= ‘USE ‘.$this->dbName.”;nn”;

                    /**
                    * Iterate tables
                    */
                    foreach($tables as $table)
                    {
                    echo “Backing up “.$table.” table…”;

                    $result = mysqli_query($conn, ‘SELECT * FROM ‘.$table);
                    $numFields = mysqli_field_count ( $conn);

                    $sql .= ‘DROP TABLE IF EXISTS ‘.$table.’;’;
                    $row2 = mysqli_fetch_row(mysqli_query($conn, ‘SHOW CREATE TABLE ‘.$table));
                    $sql.= “nn”.$row2[1].”;nn”;

                    for ($i = 0; $i < $numFields; $i++)
                    {
                    while($row = mysqli_fetch_row($result))
                    {
                    $sql .= 'INSERT INTO '.$table.' VALUES(';
                    for($j=0; $j<$numFields; $j++)
                    {
                    $row[$j] = addslashes($row[$j]);
                    $row[$j] = str_replace("n",'n',$row[$j]);
                    if (isset($row[$j]))
                    {
                    $sql .= '"'.$row[$j].'"' ;
                    }
                    else
                    {
                    $sql.= '""';
                    }

                    if ($j getMessage());
                    return false;
                    }

                    return $this->saveFile($sql, $outputDir);
                    }

                    /**
                    * Save SQL to file
                    * @param string $sql
                    */
                    protected function saveFile(&$sql, $outputDir = ‘.’)
                    {
                    if (!$sql) return false;

                    try
                    {
                    $handle = fopen($outputDir.’/db-backup-‘.$this->dbName.’-‘.date(“Ymd-His”, time()).’.sql’,’w+’);
                    fwrite($handle, $sql);
                    fclose($handle);
                    }
                    catch (Exception $e)
                    {
                    var_dump($e->getMessage());
                    return false;
                    }

                    return true;
                    }
                    }

                    ?>

                  • sanjay - 15/04/2015 reply

                    Warning: preg_replace() [function.preg-replace]: Empty regular expression in D:xampphtdocsAccuratebackup1.php

                    getting above error on line $row[$j] = preg_replace(“n”,”\n”,$row[$j]);

                  • dio - 23/04/2015 reply

                    it works great, thank you man.

                  • Nisan Rai - 12/06/2015 reply

                    It not work properly the error is-

                    Deprecated: Function ereg_replace() is deprecated in C:xampphtdocsstockadminbackup1.php on line 136

                  • Michael - 05/07/2015 reply

                    Hi, I have try the scripts but it could only backup the first table. The rest just print out
                    DROP TABLE IF EXISTS table_name; in sql file. But showing Backing table is OK for all the tables. Please advise. thank you.

                    ;

                  • Sushil - 17/08/2015 reply

                    Warning: mysql_fetch_row() expects parameter 1 to be resource, boolean given in test.php on line 102
                    Backup result: OK

                    The backup file is created, first two lines written and that’s all. What could be the problem?

                  • MarPlo - 12/09/2015 reply

                    Hi
                    Here is a similar php script to backup and restore tables from mysql database:
                    http://coursesweb.net/php-mysql/simple-backup-mysql-database_s2
                    It can use MYSQLi and PDO.

                  • rj - 15/09/2015 reply

                    thank you. awesome script 🙂 help me a LOT.
                    🙂

                  • Mohammed Umar - 28/05/2016 reply

                    Hi,
                    When i run this script it generate message OK OK but i am not able to find where it store files. I am using ubuntu.

                    Please let me know as soon as possible

                  • Joshy Francis - 31/10/2016 reply

                    I also had a situation and i could’nt find a tool to satisfy me. So for the sake of backup/restore mysql data from PHP i have made a program that can compress the data into a zip file that you can download. Later you can upload and restore full database. You can find it in my Github page https://github.com/JoshyFrancis/mysql_backup_restore_php

                    Daniel - 01/11/2017 reply

                    Thanks Joshy Francis, this feature is already implemented.

                  • 8720067448 - 09/09/2017 reply

                    Awesome it was very usefull …. thankyou so much….

                  • kintu micheal - 01/11/2017 reply

                    When restoring all the ‘NULL’ values are replaced with zeros(0) i donot know if there is any way to go by this and null values stay null values ,script works great am really grateful

                    Daniel - 01/11/2017 reply

                    Thanks kintu micheal! Now it’s fixed.

                  • Preet - 20/04/2018 reply

                    Daniel Hi, Awesome it was very useful , help , after use this
                    Im dump mysql backup file and after uploading my data size change
                    71 tables Sum 140,500 InnoDB latin1_swedish_ci 70.8 MiB
                    71 tables Sum 140,500 InnoDB latin1_swedish_ci 86.9 MiB

                    is this normal?

                    Daniel - 21/04/2018 reply

                    It depends on the file system in which the two files are stored. The same file can be of different size on two different file systems.

                  • Preet - 21/04/2018 reply

                    Daniel Thank you for fast reply , is a way to check if file data have been lost or missed text ,,

                  • Daniel - 21/04/2018 reply

                    You can download it again after upload and perform a diff. There shouldn’t be any difference between them.

                  • Preet - 21/04/2018 reply

                    cool , please help in one more thing your code work fine ,, and my code email me after the backup successfully saved file , I need php variable so email can come with all the data result , showing table name plus status ,

                    Daniel - 21/04/2018 reply

                    I added such variable to the script. You can see differences here: https://github.com/daniloaz/myphp-backup/commit/d6bc9191aee2d8f6a11ec1f33c385feffd98b397

                  • Preet - 25/04/2018 reply

                    thank you so much ,wish you success in your life

                  • Preet - 26/04/2018 reply

                    Hi Daniel , Please help code work fine, we have few DB and after backup we get email which we need , say if we have 20 DB we getting 20 email is there a way in code have a option multi DB backup and give one result. (–)

                    Daniel - 02/08/2018 reply

                    Not at this time, sorry.

                  • Ravi - 14/07/2018 reply

                    awesome.. it was very useful when a rogue developer did not give access to the host. Kudos to Daniel for writing this..

                  • Raj - 27/07/2018 reply

                    Hello Daniel (Sir),
                    i want to take back up (export) tables from the database in local system(it should ask where to store these files) or the path (another drive). And in case of import the table it should take files from local drive or which i have to choose. I am using CentOS. So, please let me know, how to do this….

                    Daniel - 02/08/2018 reply

                    You only have to set the BACKUP_DIR constant at the beginning of either the backup or restore script with the proper directory where you want the backup files to be created or read.

                  • Jean - 20/08/2018 reply

                    It works great, thank you Daniel !

                  • seul - 19/10/2018 reply

                    Awesome script, but I have one small issue. When I run it I get this error: ob_flush(): failed to flush buffer. No buffer to flush in /home… on line 364

                    The error does not affect the script running or generating the backups, so this might be a false positive! Is it something I can fix on my own!?

                    Daniel - 27/10/2018 reply

                    Thanks Seul, I added a check before performing ob_flush() to prevent “ob_flush(): failed to flush buffer. No buffer to flush.” errors. I think you shouldn’t have that problem again.

                  • farooq khan - 26/11/2018 reply

                    Hey, I have one question .
                    Is this possible to use all codes within a single function??
                    I just want to merge backup and restore files in a sinle file .
                    Problem I’m facing is I’m unable to call a function for post method ( I’m using manual inputs) .
                    Can you clarify me this ?

                  • sd - 22/01/2019 reply

                    How to restore database into remote server (cpanel Host)

                    Daniel - 22/01/2019 reply

                    You have to upload both the restoration script and the SQL file to the remote server via FTP or similar, and then run the first one pointing to https://www.example.com/myphp-restore.php using your browser.

                  • Scott Rowley - 09/04/2019 reply

                    Exactly what I’ve been searching for and failing to find! I kept running out of memory, thank you very much!

                  • Henry - 01/05/2019 reply

                    Hi Daniel, thank you so much for the script. It works great for me!

                  • Akeem - 09/05/2019 reply

                    Good script, but it corrupt BLOB file (images).
                    pls what can one do to this
                    regards

                  • Orpee - 26/05/2019 reply

                    Excellent! I cannot thank you enough. However, is there a way to manipulate the script in such a way that all “VIEWS” do not have INSERT INTO … just the way phpMyAdmin would’ve done it? The CREATE ALGORITHM should be there.

                  • Dangulus - 16/07/2019 reply

                    Thank you , works great!!!

                  Leave a Reply

                  Your email address will not be published.