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

#1 User is offline   jcsmesquita 

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

Posted 28 February 2012 - 11:13 PM

Hey Y!!

For my current project I needed to perform a full database backup. I took the following code from David Walsh and adapted into usage in Yii. I wanted to use the existing Yii::app()->db connection instead of having to create a new one using mysql_connect($host,$user,$pass). I have the function in a Helpers class at the moment. Example usage:

Helpers::backupDb('/home/user/backups/db.sql'); // Performs full backup
Helpers::backupDb('/home/user/backups/db.sql', 'Users'); // Backs up `Users` table
Helpers::backupDb('/home/user/backups/db.sql', 'Users, Orders, Categories'); // Multiple tables specified
Helpers::backupDb('/home/user/backups/db.sql', array('Users', 'Orders', 'Categories'); // Does the same as above

/*
* 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 = '';

    foreach ($tables as $table) {
        $result = Yii::app()->db->createCommand('SELECT * FROM ' . $table)->query();
        $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";
    }
    //save file
    $handle = fopen($filepath, 'w+');
    fwrite($handle, $return);
    fclose($handle);
}


Cheers!
jc
3

#2 User is offline   Maurizio Domba Cerin 

  • Yii - Yesss It Is !!!
  • Yii
  • Group: Yii Dev Team
  • Posts: 4,353
  • Joined: 12-October 09
  • Location:Croatia

Posted 29 February 2012 - 03:29 AM

NOTE_ moved to proper section (Tips, Snippets and Tutorials instead of General Discussion for Yii 1.1.x)

Nice tip, thanks for sharing it... this can be a nice start for a good extension ;)
Find more about me.... btw. Do you know your WAN IP?
0

#3 User is offline   fouss 

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

Posted 01 March 2012 - 02:45 AM

That works fine but the problems appear when we want to restore. about InnoDB tables with foreign keys!
Posted Image
0

#4 User is offline   Y!! 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 978
  • Joined: 18-June 09

Posted 01 March 2012 - 03:05 AM

queryAll() might also break someones neck. :D
0

#5 User is offline   jcsmesquita 

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

Posted 01 March 2012 - 04:17 AM

I just realized there was an error in the code,

substr($return, 0, sizeof($return) - 1); <-- This is wrong!

should be...

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

I've edited my first post to fix this. Also made some modifications based on Y!!'s neckbreaking suggestion ;)

View Postmdomba, on 29 February 2012 - 03:29 AM, said:

NOTE_ moved to proper section (Tips, Snippets and Tutorials instead of General Discussion for Yii 1.1.x)

Nice tip, thanks for sharing it... this can be a nice start for a good extension ;)


Thanks for moving it mdomba! :)

View Postfouss, on 01 March 2012 - 02:45 AM, said:

That works fine but the problems appear when we want to restore. about InnoDB tables with foreign keys!


I can't comment about InnoDB because I generally avoid using them :/ maybe somebody can patch the code to work for InnoDB with foreign keys.

View PostY!!, on 01 March 2012 - 03:05 AM, said:

queryAll() might also break someones neck. :D


I thought about what you said and after digging a bit deeper I found that using Yii::app()->db->schema->getTableNames() is a much cleaner way to do it! This should hopefully avoid getting them neckbraces out :) Thanks!
0

#6 User is offline   Y!! 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 978
  • Joined: 18-June 09

Posted 01 March 2012 - 04:39 AM

View Post=...NITRO...=, on 01 March 2012 - 04:17 AM, said:

I thought about what you said and after digging a bit deeper I found that using Yii::app()->db->schema->getTableNames() is a much cleaner way to do it! This should hopefully avoid getting them neckbraces out :) Thanks!


No what I meant was that queryAll() will fetch all rows in a table. While this might be faster than other methods, one can run out of memory very quickly. You could use query() instead which returns a CDbDataReader instance. Then you can traverse through all rows with low memory allocation (i.e. read and process every row one by one).
0

#7 User is offline   jcsmesquita 

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

Posted 01 March 2012 - 10:43 AM

Hey thanks for the tip, I realized I missed that call after I posted. Wasn't sure about the alternative and I had to run out. I seem to remember trying query() before but when dbugging saw an object and didn't know what to do with it. I think I know the direction now.

Thanks again for the tip I'll re-post when I fix it tomorrow!
0

#8 User is offline   jcsmesquita 

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

Posted 01 March 2012 - 10:17 PM

View PostY!!, on 01 March 2012 - 04:39 AM, said:

No what I meant was that queryAll() will fetch all rows in a table. While this might be faster than other methods, one can run out of memory very quickly. You could use query() instead which returns a CDbDataReader instance. Then you can traverse through all rows with low memory allocation (i.e. read and process every row one by one).


Got it :) code updated!
0

#9 User is offline   ThePaulius 

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

Posted 26 March 2012 - 09:46 AM

Just what i'm looking for, great, but how do i use it? where do I save it, and do i run it by creating a link to Helpers::backupDb('/home/user/backups/db.sql'); ?

I think this is a nicer solution to a standard PHP / CRON combination.
0

#10 User is offline   jcsmesquita 

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

Posted 26 March 2012 - 10:09 AM

@ThePaulius:

Hey, the way I use this function is by putting it inside a 'Helpers' class I keep in protected/components/Helpers.php something like this:

<?php
class Helpers {
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 = '';

    foreach ($tables as $table) {
        $result = Yii::app()->db->createCommand('SELECT * FROM ' . $table)->query();
        $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";
    }
    //save file
    $handle = fopen($filepath, 'w+');
    fwrite($handle, $return);
    fclose($handle);
}
}
?>


Then I can just call Helpers::backupDb('/home/user/backups/db.sql'); in my chosen controller action...

If your trying this in a Windows environment you wanna point to C:\xampp\htdocs\My_Project\db.sql or whatever applies to your project. Although eventually you might want to use a relational path like dirname(Yii::app()->request->scriptFile) . DIRECTORY_SEPARATOR . 'db.sql'

I'm not sure what you mean by this being a better solution to a standard PHP/CRON combination. I use this function as part of a backup system which is triggered by a cron job...

Maybe if you elaborate on your problem or application for this code I can help you further :)

Cheers,
jc
0

#11 User is offline   ThePaulius 

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

Posted 29 March 2012 - 05:21 AM

View Postjcsmesquita, on 26 March 2012 - 10:09 AM, said:


Then I can just call Helpers::backupDb('/home/user/backups/db.sql'); in my chosen controller action...


Ok Great, i will also create a Helper.php file, and see if i can get it to do something... if i create a new Public Function in a controller, im then starting to wonder what i do next![im fighting the urge all the time to abandon Yii ways, but I know one day it will all make sense) How do I create a Yii link which fires backupDatabase()... :-X

    /**
     * Backup Database.
    */
    public function backupDatabase()
    {
        Helpers::backupDb('/home/user/backups/db.sql');
    }


I think i need to read up on helpers.

View Postjcsmesquita, on 26 March 2012 - 10:09 AM, said:

I'm not sure what you mean by this being a better solution to a standard PHP/CRON combination. I use this function as part of a backup system which is triggered by a cron job...

Maybe if you elaborate on your problem or application for this code I can help you further :)


Well I meant, I would normally put a php script on the server outside the /public_html/ and set a CRON to run it... I know Yii is php, but i try to split them into to two groups for my own sanity PHP(standard) and Yii(Yii'ha!)..

I'm after abit of both really, to be able to have it run automatically, but also allow a user to be able to click 'Backup' and it spit out a file. CSV would be great for the manual link(in case ofice staff need to work from Excel in emergency, i dont know), and SQL for auto (in case I need to restore)

Becasue im new to Yii, I feel handicapped!

p
0

#12 User is offline   jcsmesquita 

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

Posted 29 March 2012 - 06:04 AM

You've got the right idea. You use a controller action to run the backup procedure.

I'll assume that your Controller name is SiteController.php

Put this in a view you can access:

<?php echo CHtml::link('Run Backup',array('site/backupDatabase')); ?>


Now you should get a link which which will redirect into a blank page (because you're not rendering any view inside backupDatabase() action). But the backup file should be created :)

For cron tasks, read this wiki by samdark. Once you read it you will understand that you can make a controller inside protected/commands which your cron launches directly like any other php file. It's great because you can specify the action you want to perform by putting a parameter in cron. Read it! :)

By having the backup proceedure inside a components/helpers.php class means you can make a Helpers::backupDb(...) call in your commands controller just as easy! No need to duplicate the code :) p.s. make sure your console config has application.components.* in import, just like your config/main.php.

For CSV you're gonna have to look out there for some implementation which allows you to export a database in CSV, I don't know of any... You might have to write it yourself... Let me know if you do we can incorporate it with this function and grow it into an extension! :)

Keep fighting the urge to abandon, I've been using Yii for less than a year now, I know what you are feeling now! But trust me it's worth the effort to stick to it! Things will start to make a lot of sense soon, then you'll have the power to quickly develop apps which would take you forever in pure php. Use the community if you're really stuck on something. Also I really recommend getting a copy of the Yii 1.1 Application Cookbook by Alexander Makarov http://yiicookbook.org/

I would be miles ahead if I started my Yii journey with that book!

peace
jc
0

#13 User is offline   ThePaulius 

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

Posted 29 March 2012 - 07:41 AM

View Postjcsmesquita, on 29 March 2012 - 06:04 AM, said:

I'll assume that your Controller name is SiteController.php

Put this in a view you can access:

<?php echo CHtml::link('Run Backup',array('site/backupDatabase')); ?>


Now you should get a link which which will redirect into a blank page (because you're not rendering any view inside backupDatabase() action). But the backup file should be created :)


Well, I do have SiteController.php and the link appears on the page, and yep i get the 404, but no file appears in the path i specify inside backupDatabase().. i've also placed it inside the controller for the model im currently viewing, no luck either

I will keep trying.

Thanks for your help btw
0

#14 User is offline   jcsmesquita 

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

Posted 29 March 2012 - 08:08 AM

You shouldn't get a 404, just a blank page. If you're getting a 404 you should be able to get a debug trace of what's causing it. Make sure your index.php file has these lines uncommented:

defined('YII_DEBUG') or define('YII_DEBUG',true);
defined('YII_TRACE_LEVEL') or define('YII_TRACE_LEVEL',3);


I would guess that there's an error at the point the function tried to create the file. Perhaps you don't have write permission in your '/home/user/backups/' folder. You might want to try chmod 755 /home/user/backups... Or even chmod 777 albeit it's generally a good idea to avoid 777 permission levels.

jc
0

#15 User is offline   ThePaulius 

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

Posted 29 March 2012 - 09:19 AM

Error 404
The system is unable to find the requested action "backupDatabase".

Directory is already 755 will change to 777 just while im testing
0

#16 User is offline   jcsmesquita 

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

Posted 29 March 2012 - 10:05 AM

oh! it's your action function, it should be written like this:

public function actionBackupDatabase()
{
    Helpers::backupDb('/home/user/backups/db.sql');
}


try that :)
0

#17 User is offline   ThePaulius 

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

Posted 13 April 2012 - 03:48 AM

Ah ha, I see that i missed out added 'action' - well spotted!

Sorry for delay, ive been busy on other projects,

When i click the link, I get "Error 403 You are not authorized to perform this action." i am logged in as SuperAdmin... and i use Yii User and Rights

Also, I was getting the 404 becasue I had the function in SiteController.php but the link was on a page that uses LeadsController.php.. now that ive moved the function into LeadsController.php, I get the 403..

:huh:


View Postjcsmesquita, on 29 March 2012 - 10:05 AM, said:

oh! it's your action function, it should be written like this:

public function actionBackupDatabase()
{
    Helpers::backupDb('/home/user/backups/db.sql');
}


try that :)

0

#18 User is offline   bennouna 

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

Posted 13 April 2012 - 05:22 AM

Quote

When i click the link, I get "Error 403 You are not authorized to perform this action." i am logged in as SuperAdmin... and i use Yii User and Rights

I don't use these extensions (yet) but I guess you have to check your controller's accessRules anyway. What do they say for your action?
1

#19 User is offline   jcsmesquita 

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

Posted 13 April 2012 - 05:36 AM

bennouna is right you probably haven't added the action to your accessControl rules.

Assuming you aren't using RBAC (role based access control) and you're just using the default when you create a yii app: look at your accessRules() inside the controller. Make sure you add the backupDatabase action!


...

    /** 
     * @return array action filters 
     */ 
    public function filters() 
    { 
        return array( 
            'accessControl', // perform access control for CRUD operations 
        ); 
    } 

    /** 
     * Specifies the access control rules. 
     * This method is used by the 'accessControl' filter. 
     * @return array access control rules 
     */ 
    public function accessRules() 
    { 
        return array( 
            array('allow',  // allow all users to perform 'index' and 'view' actions 
                'actions'=>array('index','view'), 
                'users'=>array('*'), 
            ), 
            array('allow', // allow authenticated user to perform 'create' and 'update' actions 
                'actions'=>array('create','update'), 
                'users'=>array('@'), 
            ), 
            array('allow', // allow admin user to perform 'admin' and 'delete' actions 
                'actions'=>array('admin','delete', 'backupDatabase'), // Here we've added the backupDatabase action!
                'users'=>array('admin'), 
            ), 
            array('deny',  // deny all users 
                'users'=>array('*'), 
            ), 
        ); 
    }

... 

    public function backupDatabase()
    {
        Helpers::backupDb('/home/user/backups/db.sql');
    }

...


Ignore the 3 dots ... they just mean that you probably have more lines of code in those areas ;)

jc
0

#20 User is offline   ThePaulius 

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

Posted 13 April 2012 - 08:08 AM

Ahhhh, yes, I added 'backupDatabase' to the controller access rule, and it now seems to try to do something!! progress!

Seriously, when I get this working, im going to have thise forum thread tattooed on my back lol

Now i get

Quote

Fatal error: Maximum execution time of 30 seconds exceeded in /home/user/public_html/directory/protected/components/Helpers.php on line 32


Sounds like somthing i can change in my php.ini - but id rather not, i'll see if its somethign that Yii has specified in the app itself..

#update#
I put the following code in place, and it still timed out at 60 seconds.. how long should it take? its not a big DB.. its 8.2MB..

public function actionBackupDatabase()
    {
        set_time_limit(60);
		Helpers::backupDb('/home/user/backups/bcd.sql');
    }


#update#

ok, I set it to 120 timeout, and the sql file has appeared as expected!! so that solution works, without needing to change the server..

great!

Now, the final issue, the screen is left blank.. can we get it to output a success message?

#update#

Done it! also added date and time to the file name, so daily backups dont overwrite.
public function actionBackupDatabase()
    {
        set_time_limit(190);
		$backupFileName = "/home/brsequit/backups/backup_bcd_".date('mdy_Hi').".sql";
		Helpers::backupDb($backupFileName);
		
		$this->render('backup_finished',array( 
			'model'=>$model,
		));
    }


Ha, now, I think i have to come back to this, and make it work with CRON, I read that WIKI really quickly, and seems to be more complicated than I expected..

so far so good! thank you!
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