Full Database Backup

Yii 1.1 Tips, Snippets and Tutorials
21

This is weird,

on my dev server im getting it to back up ok, but the producition server has suddenly started giving this error:

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 13894280 bytes) in /home/user/public_html/domain.co.uk/protected/components/Helpers.php on line 33

Line 33 is at the bottom of the loop $return = substr($return, 0, strlen($return) - 1);

It was fine before… any idea?

Im going to experiment with memory values, i.e.




ini_set("memory_limit","60M");
22

Not sure about that… It sounds like a php configuration issue though, did you find a solution?

23

Thanks for sharing this. I have looked for this stuff too much over the Internet. It has really helped me solve my problems. All works well.

24

Yes, found a solution sorry for not posting back, I kept increasing the limit until it worked.

25

Hi,

I’ve done something similar in the past and it’s working very nice, but i’m using the mysqldump method from the command line in linux, it’s very fast and efficient, and it work’s without any problems i believe in every web servers runing linux. The command is:

//Full Backup of bd

$out = system("mysqldump --opt --host=localhost --user=yourusername --password=yourpassword databasename | gzip -9 > pathtosavefile_bak.sql.gz", $ret);

//Partial backup of bd

$out = system("mysqldump --opt --host=localhost --user=yourusername --password=yourpassword databasename tablename1 tablename2 … | gzip -9 > pathtosavefile_bak.sql.gz", $ret);

The system command is a php command that allows to run command line programs.

The $ret is a variable that returns values from the command line execution.

The gzip is only to compress the file you don’t really need that.

You can find more options to the command in mysql dev pages searching for mysqldump, i’m not yet allowed to post links :)

Attention that this is for mysql only.

26

Thanks for the quick tip.

27

@n3okill:

That’s really a great idea! Thanks for your post! :)

28

Hey, back to the backups today!

I’ve increased the time and memory limit again, because as the backup grows in size it take longer to do - but that cant be an ideal solution, when do i stop increase server limits?! :o

Also, Im trying to add a page that loads to say that the backup is in progress, and then shows the Finished page… I’ve added the ‘backup_started’ bit to the code which loads the page, but it doesnt then load the finished page after the backup is done…

What am i doing wrong?


/**

     * Backup Database.

    */

    public function actionBackupDatabase()

    {

        $this->render('backup_started',array( 

            'model'=>$model,

        ));

		

        set_time_limit(300); //5mins

        ini_set("memory_limit","64M");

        $backupFileName = "/home/user/backups/backup_bcd_".date('dmy_Hi').".sql";

	

        Helpers::backupDb($backupFileName);

		

        $this->render('backup_finished',array( 

            'model'=>$model,

        ));

    }

Also, it would be great for the User if they saw a progress bar loading, can you build that in? :D

(actually, I just noticed that the backups arent working at all, oh dear, what have i changed! no errors, just reverts to homepage after waiting and no sql file :-X )

29

Thanks jc for the code, I’ve finally used it (but David Walsh’s one directly in fact).

Here are some adjustments that I find quite useful:

[list=1]

[*]wrapping the SQL dump into a transaction

[*]disabling foreign keys constraints at the beginning and re-enabling them at the end

[*]gzipping the output

[/list]


/*

* PHP MySQL Backup Function

* Adapted to Yii from David Walsh's code http://davidwalsh.name/backup-mysql-database-php 

*/


public static function backupDb($filepath, $tables = '*') {

    if ($tables == '*') {

        $tables = array();

        $tables = Yii::app()->db->schema->getTableNames();

    } else {

        $tables = is_array($tables) ? $tables : explode(',', $tables);

    }

    $return = 'SET FOREIGN_KEY_CHECKS=0;' . "\r\n";

    $return.= 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";' . "\r\n";

    $return.= 'SET AUTOCOMMIT=0;' . "\r\n";

    $return.= 'START TRANSACTION;';


    foreach ($tables as $table) {

        $result = Yii::app()->db->createCommand('SELECT * FROM ' . $table)->query();

        // Maybe put the 'DROP TABLE' instructions optional?

        //$return.= 'DROP TABLE IF EXISTS ' . $table . ';';

        $row2 = Yii::app()->db->createCommand('SHOW CREATE TABLE ' . $table)->queryRow();

        $return.= "\n\n" . $row2['Create Table'] . ";\n\n";

        foreach ($result as $row) {

            $return.= 'INSERT INTO ' . $table . ' VALUES(';

            foreach ($row as $data) {

                $data = addslashes($data);


                // Updated to preg_replace to suit PHP5.3 +

                $data = preg_replace("/\n/", "\\n", $data);

                if (isset($data)) {

                    $return.= '"' . $data . '"';

                } else {

                    $return.= '""';

                }

                $return.= ', ';

            }

            $return = substr($return, 0, strlen($return) - 1);

            $return.= ");\n";

        }

        $return.="\n\n\n";

    }

    $return .= 'SET FOREIGN_KEY_CHECKS=1;' . "\r\n";

    $return .= 'COMMIT;';


    //save file under gzip format

    $gz = gzopen($filepath, 'w9');

    gzwrite($gz, $return);

    gzclose($gz);

}
30

:lol: thanks for sharing !

31

What’s this?

32

You seem to have a table in your database named group. I guess you should modify the code and quote the table names in order to avoid that.

Like below:


    foreach ($tables as $table) {

        $result = Yii::app()->db->createCommand('SELECT * FROM `' . $table . '`')->query();

        // Maybe put the 'DROP TABLE' instructions optional?

        //$return.= 'DROP TABLE IF EXISTS `' . $table . '`;';

        $row2 = Yii::app()->db->createCommand('SHOW CREATE TABLE `' . $table . '`')->queryRow();
33

Thanks …problem solved!

34

Not really working correctly.

For the restoration to work you need to add




	                $return= substr($return, 0,- 1);

just after




	                $return.= ', ';

this way:




	                $return.= ', ';

	                $return= substr($return, 0,- 1);

then backup and restore work for me

hope this will help!

35

Thanks for spotting that.

In fact it’s equivalent to


            foreach ($row as $data) {

                …

                $return.= ','; // removing the space after the comma

            }

            $return = substr($return, 0, strlen($return) - 1);

or


            foreach ($row as $data) {

                …

                $return.= ', '; // I added that space for readability without re-testing. My bad!

            }

            $return = substr($return, 0, strlen($return) - 2); // removing last 2 characters

Cheers :)

36

Not sure we are talking about the same thing

I just wanted to avoid something like




INSERT table VALUES("data1","data2",); //the last comma is not correct

and this




INSERT table VALUES("data1","data2"); //no comma after "data2" ... last data

is correct

so




	     foreach ($tables as $table) {

	     	$result = Yii::app()->db->createCommand('SELECT * FROM `' . $table . '`')->query();

	        // Maybe put the 'DROP TABLE' instructions optional?

	        //$return.= 'DROP TABLE IF EXISTS `' . $table . '`;';

	        $row2 = Yii::app()->db->createCommand('SHOW CREATE TABLE `' . $table . '`')->queryRow();


	        $return.= "\n\n" . $row2['Create Table'] . ";\n\n";

	        foreach ($result as $row) {

	            $return.= 'INSERT INTO ' . $table . ' VALUES(';

	            foreach ($row as $data) {

	                $data = addslashes($data);


	                // Updated to preg_replace to suit PHP5.3 +

	                $data = preg_replace("/\n/", "\\n", $data);

	                if (isset($data)) {

	                    $return.= '"' . $data . '"';

	                } else {

	                    $return.= '""';

	                }

	                $return.= ', ';

	                $return= substr($return, 0,- 1); //remove the comma after the last data in INSERT TO 

	            }

	            $return = substr($return, 0, strlen($return)- 1);

	            $return.= ");\n";

	        }

	        $return.="\n\n\n";

	    }
37

@fouss Yes we 're talking about the same thing.

38

Ok… sans faute alors!

39

Hi guys,

this extension may also be interesting:

http://www.yiiframework.com/extension/database-command/

Best regards,

Schmunk

40

But be careful to use substr function. If you run this function hundreds of thousands of times, you may get memory or CPU issue.