Yii Framework Forum: Full Database Backup - Yii Framework Forum

Jump to content

  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • You cannot reply to this topic

Full Database Backup Performing full MySQL database backup under Yii Rate Topic: ***** 2 Votes

#21 User is offline   ThePaulius 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 126
  • Joined: 17-February 12

Posted 18 April 2012 - 11:25 AM

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");

0

#22 User is offline   jcsmesquita 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 27-July 11

Posted 19 April 2012 - 10:54 PM

View PostThePaulius, on 18 April 2012 - 11:25 AM, said:

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");



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

#23 User is offline   Yasir Liaqat 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 25
  • Joined: 14-June 11

Posted 22 April 2012 - 09:59 AM

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.
Yii - Yes it is
Simply a best php framework on earth

Cheap Business Cards Printing | Cheap Flyers Printing | Cheap Posters Printing | Cheap Website Design
0

#24 User is offline   ThePaulius 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 126
  • Joined: 17-February 12

Posted 08 May 2012 - 09:07 AM

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

View Postjcsmesquita, on 19 April 2012 - 10:54 PM, said:

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

0

#25 User is offline   n3okill 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 7
  • Joined: 12-September 09

Posted 11 May 2012 - 12:06 PM

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.
4

#26 User is offline   pckabeer 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 19-May 12

Posted 26 May 2012 - 08:48 AM

Thanks for the quick tip.

View Postn3okill, on 11 May 2012 - 12:06 PM, said:

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.

0

#27 User is offline   jcsmesquita 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 26
  • Joined: 27-July 11

Posted 05 June 2012 - 03:38 AM

@n3okill:

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

#28 User is offline   ThePaulius 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 126
  • Joined: 17-February 12

Posted 12 June 2012 - 05:13 AM

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 )
0

#29 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,165
  • Joined: 05-January 12
  • Location:Morocco

Posted 20 September 2012 - 04:41 PM

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:
  • wrapping the SQL dump into a transaction
  • disabling foreign keys constraints at the beginning and re-enabling them at the end
  • gzipping the output


/*
* PHP MySQL Backup Function
* Adapted to Yii from David Walsh's code http://davidwalsh.na...ql-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);
}

0

#30 User is offline   yiqing95 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 586
  • Joined: 27-December 10
  • Location:china

Posted 28 September 2012 - 05:05 AM

:lol: thanks for sharing !
0

#31 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 385
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 06 November 2012 - 03:09 AM

View Postbennouna, on 20 September 2012 - 04:41 PM, said:

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:
  • wrapping the SQL dump into a transaction
  • disabling foreign keys constraints at the beginning and re-enabling them at the end
  • gzipping the output


/*
* PHP MySQL Backup Function
* Adapted to Yii from David Walsh's code http://davidwalsh.na...ql-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);
}


What's this?

Quote

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group' at line 1. The SQL statement executed was: SELECT * FROM group

Posted Image
0

#32 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,165
  • Joined: 05-January 12
  • Location:Morocco

Posted 06 November 2012 - 05:50 AM

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();

1

#33 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 385
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 06 November 2012 - 12:45 PM

View Postbennouna, on 06 November 2012 - 05:50 AM, said:

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();



Thanks ...problem solved!
Posted Image
0

#34 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 385
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 24 December 2012 - 04:44 PM

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!
Posted Image
1

#35 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,165
  • Joined: 05-January 12
  • Location:Morocco

Posted 25 December 2012 - 05:57 AM

View Postfouss, on 24 December 2012 - 04:44 PM, said:

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!


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 :)
0

#36 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 385
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 25 December 2012 - 09:22 PM

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";
	    }

Posted Image
0

#37 User is offline   bennouna 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,165
  • Joined: 05-January 12
  • Location:Morocco

Posted 26 December 2012 - 04:21 AM

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

#38 User is offline   fouss 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 385
  • Joined: 05-October 10
  • Location:Bamako Mali

Posted 26 December 2012 - 07:41 PM

View Postbennouna, on 26 December 2012 - 04:21 AM, said:

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


Ok... sans faute alors!
Posted Image
0

#39 User is offline   schmunk 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 566
  • Joined: 02-November 08
  • Location:Stuttgart, Germany

Posted 27 December 2012 - 08:09 AM

Hi guys,

this extension may also be interesting:
http://www.yiiframew...tabase-command/

Best regards,
Schmunk
Phundament - Yii Application Boilerplate with composer support
Fork on github

Follow phundament on Twitter

DevSystem: Mac OS X 10.7 - PHP 5.3 - Apache2 - Yii 1.1 / trunk - Firefox or Safari
0

#40 User is offline   San Hu 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 03-November 11

Posted 11 June 2013 - 01:15 PM

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

Share this topic:


  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users