Mysqldump Alternative 

Mysqldump Alternative

Although phpMyAdmin can do most of the Import and Export job for small scale database, I still required an automated way to do the backup together with cron tool. Furthermore, phpMyAdmin is not realiable because it is an PHP level implementation. Running a large scale database such as a well establish forum can be a pain if you have phpMyAdmin as your only option.

exec Mysqldump

Mysqldump is a very important sub application provided by the Mysql database system to perform backup, fast and reliable. However, as PHP has no function interface to access this application, the only choice leave are calling it thru exec, system, or passthru:

exec("mysqldump --add-drop-table --host=localhost --user='' --password='' dbname");

However, due to security reasons, most share hosting provider has blocked this php function, making the solution impossible.

SELECT INTO OUTFILE

Since then, programmer has been creatively using alternative to solve the issue.

Mysql query provide this SELECT INTO OUTFILE feature to allow programmers to dump chuck of data from SELECT into a file specified.

$backupFile = 'backup/mypet.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM `blogs`";
$result = mysql_query($query);

Again, I was unable to use this function on my share hosting server. ACCESS DENIED error issued.

PHP Implementation

I then forced to search for other alternative on PHP level.

I do not like this option as it is very unpredictable/unstable due to the maximum script execution time setting which would cause the script to quit half way while backing up a very large database. Worst still, without any warning. As result, the exported sql file is corrupted and unusable.

The open source community have come out few class for it.

Mysql Backup by Vagharshak Tozalakyan

Download: http://www.phpclasses.org/browse/package/2779.html

Using the class is easy:

<?php

require_once 'mysql_backup.class.php';
$backup_obj = new MySQL_Backup();
$backup_obj->server = 'localhost';
$backup_obj->port = 3306;
$backup_obj->username = '';
$backup_obj->password = '';
$backup_obj->database = '';

//Tables you wish to backup. All tables in the database will be backed up if this array is null.
$backup_obj->tables = array();

if (!$backup_obj->Execute(MSB_SAVE, 'backup.sql', true))
{
    $output = $backup_obj->error;
}

?>

However, I found the class is not working on unicode data. To fix it:

1. edit mysql_backup.class.php
2. goto line 101
3. add in the following line at the beginning of funciton _Query($sql):
   // try to make utf8 compatible query
   mysql_query("SET NAMES 'utf8'", $this->link_id);
4. save the file

MySQL Dump by Marcus Vinícius

Download: http://www.phpclasses.org/browse/package/2527.html

Using the class is easy:

<?php

mysql_connect("localhost", "root", "");
require("class_mysqldump.php");
$dump = new MySQLDump();
print $dump->dumpDatabase("mydb");

?>

I haven't try this class yet. It would be useful to backup the whole database at once.