PHP Create MySQL backup

Introduction

PHP Create MySQL backup for your client site. Sometimes you need to keep a backup before making a big change or a periodic backup will help you to avoid losing of data. And also if you have risk of hacking the backup will help you to get back to live with backups.

There  are two kinds of backup

1. Whole database backup

2. Selected tables backup.

Selected Table Backup

The selected tables will be separating tables from multiple sites. You can backup the selected tables for your needs. It’s depending on the tables you need you can take backup.

<?php 
 $tables = array('first_table', 'second_table', 'third_table');
        $return = "    # MySQL dump of database '".$db_connections["name"]."' on host '".$db_connections["host"]."'
    # Backup Date and Time: ".date('Y-m-d h:i a')."
       # Module Author : Kvvaradha
    # https://www.kvcodes.com/2017/09/php-create-mysql-backup/
    # Tutorial: PHP Create MySQL backup    
    SET SQL_MODE='';
    # Table Backups ".$db_connections[$_SESSION["wa_current_user"]->company]["dbname"]."
    ------------------------------------------------------------------------------------    ";
    //cycle through 
foreach($tables as $table)  {
        $table_exist = db_query("SHOW TABLES LIKE '".$table."'");
        $tbl_ext = db_num_rows($table_exist) > 0;
                if($tbl_ext){
            $result = db_query('SELECT * FROM '.$table);
            $num_fields = db_num_fields($result);
                        $return.= "### Structure of table `".$table."` ### \n\n DROP TABLE IF EXISTS ".$table.";";
            $row2 = db_fetch_row(db_query('SHOW CREATE TABLE '.$table));
            $return.= "\n\n".$row2[1].";\n\n"; 
                       for ($i = 0; $i < $num_fields; $i++) {
                while($row = db_fetch_row($result)){
                    $return.= 'INSERT INTO '.$table.' VALUES(';
                    for($j=0; $j < $num_fields; $j++) {
                        $row[$j] = addslashes($row[$j]);
                        $row[$j] = preg_replace("#\n#", "\\n", $row[$j])
;                        if (isset($row[$j])) { $return.= '"'.$row[$j].'"' ; } else { $return.= '""'; }
                        if ($j < ($num_fields-1)) { $return.= ','; }
                    }
                    $return.= ");\n";
                }
            }
            $return.="\n\n # -------------------------------------------------------------------------------------\n\n";
        }
            }
    //save file    
$handle = fopen('kvcodes-'.time().'.sql','w+');
    fwrite($handle,$return);
    fclose($handle); ?>

Whole Database Backup

It’s less complicated because you don’t need to loop and check all tables. Just take a backup of whole database by iterating the loop.

Note : If you have table prefix, that will Help you to get right prefixed tables.