MySQL Backup

[Exiled Hero]

This is something I quickly whipped up for a friend. Instead of generating a sql script or a bak file, it generates a PHP script file that will allow to recreate your database using PHP's MySQL functions. It will restore everything you had in your database.

It restores using the UTF-8 character set and does not support views, triggers or stored procedures.

If you use this, it's recommended that you set up a cronjob so it backs up regularly.

Class:

<?php

interface ibackup
{

    public function params( $dbServer, $user, $password, $file = 'default', $dbName = null );

    public function mysql();
}

final class backup implements ibackup
{
    private $dbserver,
            $user,
            $password,
            $file,
            $dbname;

    public function params( $dbServer, $user, $password, $file = 'default', $dbName = null )
    {
        $date = date( 'Ymd_Hi' );
        $this->dbserver = $dbServer;
        $this->user = $user;
        $this->password = $password;
        $this->file = ($file == 'default' ) ? 'backup_' . $date . '.php' : $file;
        $this->dbname = $dbName;
    }

    public function mysql()
    {
        $fd = fopen( $this->file, 'w' );
        $t = date( 'H:i:s' );
        $d = date( 'Y/m/d' );

        $head = "<?php\n\n
        /* \$Id: {$this->file},v 1.0 $d $t $ */
        /*
         * MySQL Backup by:
         * Exiled Hero
         */

        \$user = '{ $this->user }';\n
        \$password = '{ $this->password }';\n
        \$con = mysql_connect(' { $this->dbserver }', \$user, \$password );
        \n\n";
        
        fwrite( $fd, $head );
        
        $con    = mysql_connect( $this->dbserver, $this->user, $this->password );
        $dblist = mysql_list_dbs( $con );

        while( $row  = mysql_fetch_object( $dblist ) ) 
        {
            $db = $row->Database;
            
            if( $db !== 'mysql' ) 
            {
                $dbcreate = 'mysql_query( "CREATE DATABASE `$db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci\" );';
                fwrite( $fd, '$dbcreate\n' );
                
                $dbselect = mysql_select_db( $db );

                fwrite( $fd, "mysql_select_db( '$db' ); \n" );
                
                $tbl = mysql_list_tables( $db );

                while( $table = mysql_fetch_row( $tbl ) ) 
                {
                    $fld = mysql_query( "SELECT * FROM `$table[0]`" );

                    if( $fld )

                        $fields = mysql_num_fields( $fld );
                    else
                        $fields = 0;

                    unset( $tblcreate );

                    $tblcreate = "CREATE TABLE `$table[0]` (";
                    $arrfield = array();
                    $arrfieldnum = 0;

                    for( $i = 0; $i < $fields; $i++ ) 
                    {
                        $fieldinfo = mysql_fetch_field( $fld, $i );

                        unset( $fieldname );

                        $fieldname = $fieldinfo->name;
                        $arrfield[ $arrfieldnum ] = $fieldname;
                        $arrfieldnum++;
                        $fieldlen = mysql_field_len( $fld, $i );
                        
                        if( $fieldlen == '65535' ) 
                        {
                            $fieldtype = 'TEXT';
                        }
                        else
                        {
                            $fieldtype = 'VARCHAR(' . $fieldlen . ')';
                        }

                        $tblcreate .= "`$fieldname` $fieldtype NOT NULL";

                        if( $i !== $fields - 1 ) 
                        {
                            $tblcreate .= ", ";
                        }
                    }

                    $tblcreate .= ')';

                    fwrite( $fd, "     mysql_query(\"$tblcreate\", \$con);\n" );

                    $readtable = "SELECT * FROM `$table[0]`";
                    $retval = mysql_query( $readtable );

                    if( $retval )
                        while( $col = mysql_fetch_row( $retval ) ) 
                        {
                            $insert = "mysql_query(\"INSERT INTO `$table[0]` (";

                            for( $ifield = 0; $ifield < count( $arrfield ) - 1; $ifield++ ) 
                            {
                                $insert .= "`{$arrfield[$ifield]}`, ";
                            }

                            $insert .= "`{$arrfield[$ifield]}`) VALUES(";

                            for( $icol = 0; $icol < count( $col ) - 1; $icol++ )
                            {
                                $col[$icol] = str_replace( "\"", "'", $col[$icol] );
                                $insert .= "'{$col[$icol]}', ";
                            }

                            $insert .= "'{$col[$icol]}'";
                            $insert .= ")\", \$con);";

                            fwrite( $fd, "          $insert\n" );
                        }
                }
            }
        }

        mysql_close( $con );

        $tail = "\nmysql_close(\$con);\n\n
        ?>\n\n
        <html><title>MySQL Restoration by Exiled Hero</title><body>\n
        <font face=\"arial\" size=4 color=\"#000000\"><b>Database Restored!</b></font>\n
        </body></html>";

        fwrite( $fd, $tail );
        fclose( $fd );
    }
}

/* EOF */

Example:

$backup = new backup;
$backup->params( '127.0.0.1', 'dbuser', 'dbpassword' );
$backup->mysql();
unset( $backup ); 

[MentaL]

[Vusion]

Great job, bit messy though.

Also,

for( $i = 0; $i < $fields; $i++ )

Shouldn't it be $i <= $fields?

[Torsen]

Great job, bit messy though.

Also,

for( $i = 0; $i < $fields; $i++ )

Shouldn't it be $i <= $fields?

Well, considering the entire code is completely correct syntax wise, and he actually wrote this himself. I say you have absolutely no say in this :)
First learn an actual language, then actually write something from scratch. (Hello world doesn't count).

Then, and only then will you have the right to criticize someone's work.

[TimeBomb]

Well, considering the entire code is completely correct syntax wise, and he actually wrote this himself. I say you have absolutely no say in this :)
First learn an actual language, then actually write something from scratch. (Hello world doesn't count).

Then, and only then will you have the right to criticize someone's work.

..What?

When you make a post in the showcase forum, you should expect constructive criticism, which is what Vusion gave.

Vusion is correct on both remarks.

The code is somewhat messy, although a lot of that appears to be due to the fact that Exiled Hero appears to be using string manipulation to generate a PHP file. I'm not quite sure what his thinking is here; I would have probably just (assuming I did this in PHP) created a PHP script that generates a backup every so often, set it up to run via cron, and then just run the database file appropriately if necessary to restore the backup.

Though, a periodical MySQL database backup can be made via cron and linux simply with the command mysqldump, and it can be restored similarly via mysql. This is what I do, and I imagine there are good alternatives for Windows servers running MySQL.

Nonetheless, his code does seem to have valid syntax and it is... unique :).
Keep at it, Exiled Hero.

[Exiled Hero]

I'm actually a VERY clean coder. I've moaned at clean coders to be cleaner.

It's messy because I'm a lazy bastard and the fact I lost a lot of interest in programming. I program to keep my imagination and creativity going. I have a lot of unique stuff I have made over the years and I'd be happy to share them with people to educate their brain.

< and <= both equal to less than. PHP docs just say <= for for() when in fact it doesn't matter.

[EliteGM]

< : less than
<= : less than or equal

for($i = 0; $i < 3; $i++)
{
echo $i;
}

will give you 012
the loop only runs while $i is less than 3


for($i = 0; $i <= 3; $i++)
{
echo $i;
}

will give you 0123
the loop only runs while $i is less than or equal to 3


P.S.:

I like the way you code.

[Vusion]

< : less than
<= : less than or equal

for($i = 0; $i < 3; $i++)
{
echo $i;
}

will give you 012
the loop only runs while $i is less than 3


for($i = 0; $i <= 3; $i++)
{
echo $i;
}

will give you 0123
the loop only runs while $i is less than or equal to 3


P.S.:

I like the way you code.

Exactly my point,

                    if( $fld )

                        $fields = mysql_num_fields( $fld );
                    else
                        $fields = 0;

Therefore, $i < $fields would miss one row.

[EliteGM]

Exactly my point,

                    if( $fld )

                        $fields = mysql_num_fields( $fld );
                    else
                        $fields = 0;

Therefore, $i < $fields would miss one row.

I don't think so, actually. The first field is probably identified with 0. So say there are 300 fields, the last one would be identified with 299, making $i < $fields (where $fields is 300) work without problems.

[TimeBomb]

I apologize. I got a bit confused with the < vs <=. I, for some reason, thought that Vusion was saying that he should use < instead of <=.


To reiterate what EliteGM said above, because arrays start at the key 0 instead of 1, $i = 0; $i < $arrayCount is correct, wherein using the <= symbol is not.

[Vusion]

I apologize. I got a bit confused with the < vs <=. I, for some reason, thought that Vusion was saying that he should use < instead of <=.


To reiterate what EliteGM said above, because arrays start at the key 0 instead of 1, $i = 0; $i < $arrayCount is correct, wherein using the <= symbol is not.

It differs with arrays, ahh.

Thanks for the information.