Full Database Backup


(Jcsmesquita) #1

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


(Maurizio Domba Cerin) #2

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


(Jsfousseni) #3

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


(Y!!) #4

queryAll() might also break someones neck. :D


(Jcsmesquita) #5

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

Thanks for moving it mdomba! :)

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

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!


(Y!!) #6

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


(Jcsmesquita) #7

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!


(Jcsmesquita) #8

Got it :) code updated!


(Pcmcdermott) #9

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.


(Jcsmesquita) #10

@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


(Pcmcdermott) #11

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.

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


(Jcsmesquita) #12

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


(Pcmcdermott) #13

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


(Jcsmesquita) #14

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


(Pcmcdermott) #15

Error 404

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

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


(Jcsmesquita) #16

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




public function actionBackupDatabase()

{

    Helpers::backupDb('/home/user/backups/db.sql');

}



try that :)


(Pcmcdermott) #17

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:


(Abennouna) #18

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?


(Jcsmesquita) #19

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


(Pcmcdermott) #20

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

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!