Holdorf.dk/Software

Software Development Blog

phpMyDumper released

After one week of coding I am ready to release my newest software project: phpMyDumper.

I wanted to do automatic backup of my website databases and normally you would do this using mysqldump and a cronjob. At the moment I am hosting my websites on my own server, but I plan to move the websites to an external webhost. However since I have no way to make sure that I have access to mysqldump on my future webhost especially if cost should be as low as possiible, I decided to write a php tool myself to do the job.

I googled for php scripts but many of them were based on using a webbrowser or the dump file format was not acceptable. I wanted the script to dump a file formatted just like phpMyAdmin.

At last I found MySQLDump 2.0 by Daniele Viganò, creativefactory.it. His php class was posted at coders4fun.com blog and after reading through the blog post, testing it on my databases and looking at the source code I decided to use his php class and improve it.

First it had a bug when importing its table structures of Joomla databases into MySQL using eg. phpMyAdmin. This was fixed when I rewrote it to use SHOW CREATE TABLE to construct database table structures. Also this simplified the code a lot! :) Furthermore, I added the AUTOINCREMENT statement to the table structures. This was important to me because I am developer of a webgame, that uses autoincrement as post ids in forums, messages ids and account ids etc…

Then I went on and added an option to make it format the dump file like phpMyAdmin. Originally the php class dumped all structures first and then all the data. But I wanted it to format the dump like phpMyAdmin dumping data right after the each table structure.

Also, the php class dumped expanded INSERT statements but I changed it to dump INSERT statements without field names and I made it optional to let the php class dump DROP TABLE and CREATE TABLE statements as well as the INSERT statements.

I used Thors code changes in his MySQLDump 2.01 version from the coders4fun.com blog. That includes the dash-bug and null not save bugfixes suggested by Petr ‘PePa’ Pavel and that the php class returns a stream, if no filename is provided. However, I forgot to document how to use the stream feature in the release example, but I will show that in a future post.

At last I optimized the code for dumping INSERT statements. The original code saved data to file when data reached 1MB. Checking the string with strlen() every cycle, however, that method used a lot of cpu power, so I changed it to save to file for each INSERT statement. This reduced the dumping time and cpu usage a lot, especially for large databases.

This example shows how to use phpMyDumper. Have fun with the php class! :D

<?php
/*
* phpMyDumper
* -------------
* Version: 1.00
* Copyright (c) 2009 by Micky Holdorf
* Holdorf.dk/Software - micky.holdorf@gmail.com
* GNU Public License http://opensource.org/licenses/gpl-license.php
*
*/


@include_once('phpMyDumper.php');

$dbhost = "localhost";
$dbuser = "user";
$dbpass = "password";
$dbname = "database";

$yyyymmdd  = date("Ymd");
$path      = "backup/";
$filename  = $path.$dbname."_".$ccyymmdd.".sql";
$compress  = false; // Dump as a compressed file, default: false

$connection = @mysql_connect($dbhost,$dbuser,$dbpass);
$dump = new phpMyDumper($dbname,$connection,$filename,$compress);

$dump->dropTable = true; // Dump DROP TABLE statement, default: true
$dump->createTable = true; // Dump CREATE TABLE statement, default: true
$dump->tableData = true; // Dump table data, default: true
$dump->expInsert = false; // Dump expanded INSERT statements, default: false
$dump->hexValue = false; // Dump strings as hex values, default: false
$dump->phpMyAdmin = true; // Formats dump file like phpMyAdmin export, default: true

$dump->doDump();

?>

Comments are closed.