Backup Your MySQL Database Using PHP

One of the most important tasks any developer needs to do often is back up their MySQL database. In many cases, the database is what drives most of the site. While most web hosts do a daily backup of a customer's database, relying on them to make backups and provide them at no cost is risky to say the least. That's why I've created a database backup function that I can call whenever I want -- including nightly CRONs.

The PHP & MySQL Code

backup_tables('localhost','username','password','blog');

/* backup the db OR just a table */
function backup_tables($host,$user,$pass,$name,$tables = '*')
{
 
 $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; $j < $num_fields; $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 < ($num_fields-1)) { $return.= ','; }
    }
    $return.= ");\n";
   }
  }
  $return.="\n\n\n";
 }
 
 //save file
 $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+');
 fwrite($handle,$return);
 fclose($handle);
}

Of course, you'll need to provide database credentials to the function, as well as an array of tables you'd like to backup. If you provide a "*" or no tables, a complete database backup will run. The script does the rest!

Never take chances when your website is on the line. Make frequent backups or pay the price later!

How to take backup of a single table in a MySQL database?

By default, mysqldump takes the backup of an entire database. I need to backup a single table in MySQL. Is it possible? How do I restore it?

 Answers

Dump and restore a single table from .sql

Dump

mysqldump db_name table_name > table_name.sql

Dumping from a remote database

mysqldump -u <db_username> -h <db_host> -p db_name table_name > table_name.sql

Restore

mysql -u <user_name> -p db_name
mysql> source <full_path>/table_name.sql

or in one line

mysql -u username -p db_name < /path/to/table_name.sql

---

Dump and restore a single table from a compressed (.sql.gz) format

Credit: John McGrath

Dump

mysqldump db_name table_name | gzip > table_name.sql.gz

Restore

gunzip < table_name.sql.gz | mysql -u username -p db_name

---

---

try

for line in $(mysql -u... -p... -AN -e "show tables from NameDataBase");
do 
mysqldump -u... -p.... NameDataBase $line > $line.sql ; 
done

• $line cotent names tables ;)

---

You can use easily to dump selected tables using MYSQLWorkbench tool ,individually or group of tables at one dump then import it as follow: also u can add host information if u are running it in your local by adding -h IP.ADDRESS.NUMBER after-u username

mysql -u root -p databasename < dumpfileFOurTableInOneDump.sql 

---

You can use the below code:

1. For Single Table Structure alone Backup

-

mysqldump -d <database name> <tablename> > <filename.sql>

2. For Single Table Structure with data

-

mysqldump <database name> <tablename> > <filename.sql>

Hope it will help.

PHP script to make a backup copy of a MySQL table

Yesterday I looked at how to copy a table with MySQL using some SQL queries. This post looks at how to automate the process with a PHP script saving you having to type in and adjust queries each time you want to back up a table.

The script below connects to a MySQL database server on "localhost" using the login name "test" and password "123456" and then connects to the database "test". It then copies the table structure and data from the table "products" to a new table "products_bak". If the target table already exists the function returns false.

The table_exists() function comes from an earlier post on this blog titled PHP function to check if a MySQL table existsand published in June 2008. Read that post for more information about that particular function.

mysql_connect('localhost', 'test', '123456');
mysql_select_db('test');

if(copy_table('products', 'products_bak')) {
    echo "success\n";
}
else {
    echo "failure\n";
}

function copy_table($from, $to) {

    if(table_exists($to)) {
        $success = false;
    }
    else {
        mysql_query("CREATE TABLE $to LIKE $from");
        mysql_query("INSERT INTO $to SELECT * FROM $from");
        $success = true;
    }
   
    return $success;
   
}

function table_exists($tablename, $database = false) {

    if(!$database) {
        $res = mysql_query("SELECT DATABASE()");
        $database = mysql_result($res, 0);
    }
   
    $res = mysql_query("
        SELECT COUNT(*) AS count
        FROM information_schema.tables
        WHERE table_schema = '$database'
        AND table_name = '$tablename'
    ");
   
    return mysql_result($res, 0) == 1;
}

Note that there is no error checking after the calls to mysql_query so you'd want to add this in yourself and/or change the mysql_* calls to use whatever database abstraction library you use in your own projects.

Note also that the copy_table() function could also call table_exists() on the $from table as well to make sure that exists too, in order to prevent errors in the query calls.

To copy multiple tables you can either call the copy_table function multiple times, or put the to and from tablenames into an associative array and loop through them like so:

foreach($tables as $from => $to) {

    echo "copying $from to $to: ";
   
    if(copy_table($from, $to)) {
        echo "success\n";
    }
    else {
        echo "failure\n";
    }

}

In Thursday's PHP post I'll look at how to enhance this script to run it from the command line passing the from and to table names from the command line, and will also add in error checking.

Related posts:

• Create a CSV file from MySQL with PHP
• Copy a MySQL table with phpMyAdmin
• Copy a table in MySQL with CREATE TABLE LIKE
• PHP function to check if a MySQL table exists
• Check if a MySQL table exists

MySQL Backups with a Command Line PHP Script

I run a number of web servers with PHP and MySQL and have a PHP command line script that runs on a daily basis to back up MySQL databases using the mysqldump command. It would be possible to do this using a simple bash script as well, and I know I used to use a bash script in the past but for some reason switched it to PHP at some stage.

The script runs as follows, with an explanation below about what each thing does:

#!/usr/bin/php -q
<?php

$datadir = '/var/lib/mysql';
$backupdir = '/var/mysql-backup/' . date('l');
$mysqldump = '/usr/bin/mysqldump';
$password = 'root-password-here';

$dir = opendir($datadir);
while($database = readdir($dir)) {

  if($database != '.' && $database != '..' && is_dir("$datadir/$database")) {

    if(!is_dir("$backupdir/$database")) {
      mkdir("$backupdir/$database");
      chown("$backupdir/$database", "mysql");
      chgrp("$backupdir/$database", "mysql");
    }

    system("$mysqldump --user=root --password=$password --quick --add-drop-table -all --add-locks --force -d $database > $backupdir/$database/$database.sql");
    system("$mysqldump --user=root --password=$password --quick --force -t -T$backupdir/$database $database");
    system("bzip2 -f $backupdir/$database/*.txt");

  }

}
closedir($dir);

?>

$datadir is the directory that MySQL actually stores its database files. It is required by this script to be able to work out what it actually needs to back up by looking at the directories. It could instead connect to the database and use a database query to get the databases, but this works just as well.

$backupdir is where the database backups should be stored. I create backups for each day of the week, hence the date('l') added to the end of the directory. This returns the current day of the week name (eg Sunday, Monday, Tuesday etc).

$mysqldump is the location of the mysqldump command.

$password is the MySQL root user's password.

The next section of the script loops through all the files in $datadir. The opendir() and readdir() functions will also show the . and .. directories, so we don't want to attempt to back up databases with those names, so the loop excludes those.

If the directory that we want to dump data into for the specific database doesn't exist, then it is created. The script then also changes the ownership and group to be the user that MySQL runs as; this is to avoid any errors that might happen when attempting to dump the data because MySQL won't be able to write to the directory if the MySQL user cannot write to it.

Finally, mysqldump is called twice. The first time is to dump the database structure and the second time to dump the data itself. The data dump is into tab delimited files with one file for each tablename. These tab files are then compressed using bzip2.

In the next couple of days, I'll look at the mysqldump command in more detail and then how to restore data from a backup in this format.

Backing up MySQL with mysqldump

A simple way to back up MySQL databases is with the mysqldump command line tool. Mysqldump can be used to back up a single database or multiple databases, and can backup MySQL databases into a text file containing multiple SQL statements, or into CSV or tab delimited text files.

The simplest way to use mysqldump is like so, substituting [username] for the username you use to connect to MySQL, and [database] for the MySQL database you wish to backup:

mysqldump -u [username] -p [database] > [database].sql

The above example will output the dump to a file called [database].sql, but you can instead dump the data to standard output (ie a scrolling list on the command line) by omitting the > [database].sql part.

You will be prompted for the password (that's what the -p flag is for), and an example dump with a database containing a couple of basic tables with a couple of rows of data each might look like this:

-- MySQL dump 10.11
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version       5.0.45

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `something`
--

DROP TABLE IF EXISTS `something`;
CREATE TABLE `something` (
  `something_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `value` varchar(50) NOT NULL,
  `ts_updated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`something_id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `something`
--

LOCK TABLES `something` WRITE;
/*!40000 ALTER TABLE `something` DISABLE KEYS */;
INSERT INTO `something` VALUES (1,'foo1','bar','2007-12-14 04:20:43');
INSERT INTO `something` VALUES (2,'foo2','baz','2007-12-14 04:20:43');
/*!40000 ALTER TABLE `something` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `something_else`
--

DROP TABLE IF EXISTS `something_else`;
CREATE TABLE `something_else` (
  `else_id` int(11) NOT NULL auto_increment,
  `somevalue` varchar(20) NOT NULL,
  PRIMARY KEY  (`else_id`),
  KEY `somevalue` (`somevalue`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `something_else`
--

LOCK TABLES `something_else` WRITE;
/*!40000 ALTER TABLE `something_else` DISABLE KEYS */;
INSERT INTO `something_else` VALUES (1,'blah');
INSERT INTO `something_else` VALUES (2,'blah blah');
INSERT INTO `something_else` VALUES (3,'blah blah blah');
/*!40000 ALTER TABLE `something_else` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2007-12-14  4:22:26

Dumping a single table, or a selection of tables

If you wanted to back up a single table or a selection of tables from the database, you can specify these on the command line after the database name as shown in the following examples. The first example below would dump the table "foo", the second "foo" and "bar", and the third "foo", "bar" and "baz".

mysqldump -u [username] -p testdb foo > dump1.sql
mysqldump -u [username] -p testdb foo bar > dump2.sql
mysqldump -u [username] -p testdb foo bar baz > dump3.sql

Loading the data from the file

Now that you've successfully dumped the data from the database into a text file, how do you get it back into the database again? It's really easy, again doing it from the command line. By default, the mysqldump command adds "DROP TABLE IF EXISTS `tablename`" to the query, so you can simply run the contents of the file against the database and it will delete the table if it exists, and then load the data into the database.

mysql -u [username] -p [database] < dumpfile.sql

This can be useful for a) backing up databases on a regular basis and b) when copying the contents of the database or table(s) from a remote server to your local development machine.

There are a lot of additional flags for dumping data from MySQL using mysqldump. I will explore some of these in later posts and there's also an excellent man page (man mysqldump) and running mysqldump --help will list the many options available.

Mysql: Back up mysql databases

<?php

// dump mysql file
$myUser = 'root';
$myPass = 'root';
$myHost = 'localhost';
$myDbs  = array('db1', 'db2');
$destDir = './backup/';
$date = date('Ymd');
$files = array();

foreach ($myDbs as $v) {
 $file = $destDir.$v.'-'.$date.'.sql.gz';
 $files[] = $file;
 exec("/usr/bin/mysqldump -u $myUser -h $myHost -p$myPass --opt $v | /bin/gzip -9 > $file");
}
?>

Mysql: Backup Your MySQL Database Using PHP

<?php

// Backup Your MySQL Database Using PHP // ------------------------------------ // Use : backup_tables('localhost','username','password','blog'); // ( - 5th param would be the tables, empty or * will get all the complete db. ) /* Backup the db OR just a table */ function backup_tables($host,$user,$pass,$name,$tables = '*') { $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; $j<$num_fields; $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<($num_fields-1)) { $return.= ','; } } $return.= ");\n"; } } $return.="\n\n\n"; } // Save file $handle = fopen('db-backup-'.time().'-'.(md5(implode(',',$tables))).'.sql','w+'); fwrite($handle,$return); fclose($handle); } ?>