Mysql table backup in php

Делаем бэкап базы данных MySQL на PHP

Можно, конечно, полагаться на случай, что пронесет, а можно просто написать небольшой скрипт, установить его в CRON и больше не парится за то, что ваш сайт в один прекрасный момент ляжет и его придется с визгом и матом долго и нудно поднимать. При работе с популярными движками сайтов, бэкап базы сделать конечно в разы проще, там на это счет написанна несметная куча модулей, которые за вас все сохранят, заархивируют да еще и на мыло отправят. Но как правило они используют API самих движков, а это уже не три строчки кода (на тот случай если захочится позаимствовать такое решение у Drupal или Joomla).

Потому если движок самописный или просто если нужно что-то очень простое и понятно как работающее — то данный снипет является решением всех проблем.

backup_tables('localhost','username','password','*'); // можно либо перечислить все таблицы, которые нужны или отметить звездочкой, что нужно делать дамп всей базы function backup_tables($host,$user,$pass,$name,$tables = '*') < $link = mysql_connect($host,$user,$pass); mysql_select_db($name,$link); // Если бекапим все таблицы if($tables == '*') < $tables = array(); $result = mysql_query('SHOW TABLES'); while($row = mysql_fetch_row($result)) < $tables[] = $row[0]; >> else < $tables = is_array($tables) ? $tables : explode(',',$tables); >// проходим по массиву таблиц foreach($tables as $table) < $result = mysql_query('SELECT * FROM '.$table); $num_fields = mysql_num_fields($result); $return.= 'DROP TABLE '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "nn".$row2[1].";nn"; for ($i = 0; $i < $num_fields; $i++) < while($row = mysql_fetch_row($result)) < $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $jelse < $return.= '""'; >if ($j <($num_fields-1)) < $return.= ','; >> $return.= ");n"; > > $return.="nnn"; > // пишем результат в файл $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle); >

Источник

How to Backup MySQL Database using PHP

The database backup is a most important task for every web developer. Regular database backup prevents risk to lose the data and it helps to restore the database if any issue occurred. So, backup the database whenever possible is a good idea.

There many ways available to backup MySQL database in a file and you can backup database in one click from hosting server. But if you want to take MySQL database backup without login to your hosting server or phpMyAdmin, you can do it from the script using PHP. In this tutorial, we will build a PHP script to backup MySQL database and save in a SQL file.

Perform MySQL Database Backup using PHP

All the PHP code will be grouped together in backupDatabaseTables() function. Using backupDatabaseTables() function you can backup specific tables or all tables from a database. The following parameters are required to backup MySQL database using PHP.

• $dbHost – Required. Specifies the host of the database.
• $dbUsername – Required. Specifies the database username.
• $dbPassword – Required. Specifies the database password.
• $dbName – Required. Specifies the database which you wants to backup.
• $tables – Optional. Specifies the table names in comma separated string or array. Omit this parameter to take backup all tables of the database.

/** 
* @function backupDatabaseTables 
* @author CodexWorld 
* @link http://www.codexworld.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','*****','codexworld');

Are you want to get implementation help, or modify or enhance the functionality of this script? Click Here to Submit Service Request

If you have any questions about this script, submit it to our QA community — Ask Question

Источник

How to Backup MySQL Database using PHP

Dynamically creating database backup via program will be hand in many a situations. We can schedule the backup process periodically by using CRON jobs.

Taking the database backup using PHP programming is easy. In this tutorial, we are going to learn how to backup MySQL database with a simple core PHP code.

First, I get the database connection object to get the table schema and data to be dumped into a file. I read all the table names from the database and store into an array.

Then, I iterate the array of database tables to prepare the SQL script for the data/structure. This SQL script will be written into a file which will be downloaded to the user’s browser and removed from the target.

Getting Database Table Names

The code shows how to get the database connection and set the default character set before executing queries. Since we are dumping the database structure and the data, we need to be careful about the consistency.

By setting the default character set, it tells the database server about the character encoding.

The SHOW TABLES statement is used to fetch the table names. The table names are stored in an array which will be iterated to prepare the backup SQL script for the database structure and the data.

set_charset("utf8"); // Get All Table Names From the Database $tables = array(); $sql = "SHOW TABLES"; $result = mysqli_query($conn, $sql); while ($row = mysqli_fetch_row($result)) < $tables[] = $row[0]; >?> 

Create SQL Script for Table Data/Structure

After fetching the list of the database table name in an array, I loop through this array to generate the SQL script. For each loop iteration, I have generated the SQL script for creating the table structure and dumping data into the table.

The SHOW CREATE TABLE statement is used to get the SQL for creating a table structure. Then, I get the column name and data to prepare the SQL for dumping data.

 else < $sqlScript .= '""'; >if ($j < ($columnCount - 1)) < $sqlScript .= ','; >> $sqlScript .= ");\n"; > > $sqlScript .= "\n"; > ?> 

Save and Download Database Backup File

After preparing the SQL script for the database table and the structure, it will be written into a backup file which is created dynamically in the specified target.

Then, this file will be downloaded to the user’s browser and removed from the target location. The code for saving and downloading the database backup file is,

Источник

how to backup MySQL Database tables using php

In any situation we required a MySQL database tables backup with php scripting. Sometimes its helpful and its very simple to take backup MySQL database tables using php. Easy faster and very simple and clean script for getting your MySQL database backup.

You must have the following details for using the backup MySQL database table script. you should have host name , username ,password and the database name . You can easily customize the script for your requirement and add or remove DROP commands etc.

backup MySQL database tables using php

Just create a file name as backupmysql.php and have the following codes.

 error_reporting(0); backup_tables('localhost','root','','');//host-name,user-name,password,DB name echo "
Done"; /* backup the db OR just a table */ function backup_tables($host,$user,$pass,$name,$tables = '*') < $return = ""; $link = mysql_connect($host,$user,$pass); mysql_select_db($name,$link); //get all of the tables if($tables == '*') < $tables = array(); $result = mysql_query('SHOW TABLES'); while($row = mysql_fetch_row($result)) < $tables[] = $row[0]; >> else < $tables = is_array($tables) ? $tables : explode(',',$tables); >//cycle through foreach($tables as $table) < $result = mysql_query('SELECT * FROM '.$table); $num_fields = mysql_num_fields($result); $return.= 'DROP TABLE '.$table.';'; $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); $return.= "\n\n".$row2[1].";\n\n"; for ($i = 0; $i < $num_fields; $i++) < while($row = mysql_fetch_row($result)) < $return.= 'INSERT INTO '.$table.' VALUES('; for($j=0; $jelse < $return.= '""'; >if ($j <($num_fields-1)) < $return.= ','; >> $return.= ");\n"; > > $return.="\n\n\n"; > //save file $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); // echo $return; fwrite($handle,$return); fclose($handle); > 

After setting the required details you will get a file in the current location (where this page running ) its format is sql and name something like “db-backup-1376317010-85f31415a609ccab9491d22d36b8b7d9.sql”

Yes you have done with backup MySQL database table using php. try it today?

28 thoughts on “ how to backup MySQL Database tables using php ”

1. How to restore database.? NDR’S Andreas Setiawan on April 9, 2016 at 8:20 amReply
   1. better to use CLI check this post http://www.walkswithme.net/import-mysql-db-using-command-line
      for smaller DB only this method recomanded WWM Support on April 10, 2016 at 9:56 pmReply
   1. set time limit using php init functions 🙂 WWM Support on August 24, 2015 at 10:06 pmReply
   1. is that a large DB then make sure your code never get timeout .
      It may create partial or empty files. WWM Support on December 24, 2014 at 10:22 pmReply
   1. You can simply set the path for saving file on server using fopen().
      Then simply type the url it will download the sql file. WWM Support on September 25, 2014 at 1:01 pmReply
      1. Please provide an example Anil on September 25, 2014 at 8:39 pmReply
         1. By default the file gets saved on the server where the page is running. like where you running this script inside a folder or in root, on the same path it will save the sql file.
            if you need custom path cab be set like

            fopen(‘yourpath/myfilename.sql’,’w+’);
            WWM Support on September 25, 2014 at 9:17 pmReply

            1. Sorry. I was asking for downloading the DB to local machine from server! Anil on September 25, 2014 at 9:20 pm
            1. Thanks. But it would be nice if the user can click and download it rather than typing the URL manually. Anil on September 27, 2014 at 12:27 pm
            1. just replace the following section

               $handle = fopen(‘db-backup-‘.time().’-‘.(md5(implode(‘,’,$tables))).’.sql’,’w+’);

               to

               $handle = fopen(‘DB-Name-‘.time().’.sql’,’w+’);
               WWM Support on September 15, 2014 at 8:09 pmReply

            1. You do not need to remove the DB when its import back it will remove the old tables and create new with data,
               Once you removed the DB then try to replace “DROP TABLE ” with “DROP TABLE IF EXISTS” in your backup file. Hope it works.. jobin jose on June 30, 2014 at 9:13 pmReply
            1. make sure your export works completely. means for large DB it may take long time to export entire table with data , so maximum execution time may exceed and incomplete file may generate,
               so in that case increase maximum execution time. jobin jose on May 8, 2014 at 7:21 pmReply
            1. Thanks Abdulraheem for the nice tips. jobin jose on April 9, 2014 at 8:15 pmReply
            2. Where to put this line . mysql_query(“set names utf8″); Tushar Desai on April 14, 2014 at 8:27 pmReply
               1. Try just above this line
                  mysql_query(‘SELECT * FROM ‘.$table);
                  something like,
                  mysql_query(“set names utf8″);
                  $result = mysql_query(‘SELECT * FROM ‘.$table); Hope its works jobin jose on April 15, 2014 at 11:30 amReply

               Источник