[ThePaulius]

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

[jcsmesquita]

ThePaulius, 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?

[Yasir Liaqat]

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.

[ThePaulius]

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

jcsmesquita, 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?

[n3okill]

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.

[pckabeer]

Thanks for the quick tip.

n3okill, 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.

[jcsmesquita]

@n3okill:

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

[ThePaulius]

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?!

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?

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

[bennouna]

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

[yiqing95]

thanks for sharing !

[fouss]

bennouna, 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

[bennouna]

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

[fouss]

bennouna, 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!

[fouss]

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!

[bennouna]

fouss, 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

[fouss]

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

[bennouna]

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

[fouss]

bennouna, on 26 December 2012 - 04:21 AM, said:

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

Ok... sans faute alors!

[schmunk]

Hi guys,

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

Best regards,
Schmunk