Yii 1.1: database-dumper

Allows to create backups of mysql database structure and data
17 followers

Simple class to create DB backups

Requirements

Tested on 1.1.10.

Usage

  1. Download latest version from GitHub(Click "Download as zip")
  2. Extract yii-database-dumper to extensions directory
Yii::import('ext.yii-database-dumper.SDatabaseDumper');
$dumper = new SDatabaseDumper;
 
// Get path to new backup file
$file = Yii::getPathOfAlias('webroot.protected.backups').'/dump.sql';
 
// Gzip dump
if(function_exists('gzencode'))
    file_put_contents($file.'.gz', gzencode($dumper->getDump()));
else
    file_put_contents($file, $dumper->getDump());

Resources

Total 17 comments

#12745 report it
paskuale at 2013/04/09 12:21pm
views problem

Hi all, how can import as well as tables also views? Very strange, when it encounters a view, it writes "DROP TABLE IF EXISTS" but does not write "CREATE TABLE $table_name", why ? tnx

#9969 report it
paskuale at 2012/09/25 04:32pm
same error ... mysql server gone away

Nada ... same issue :(

#9938 report it
francis ja at 2012/09/24 03:41am
@paskuale

as i said earlier

"Also if a table contains large records we will have to break the insert query So one table has more than 5000 records its better to split into different insert query else there will be issues max_allowed_packet ".

Change dumpTable in SDatabaseDumper.php


    public function dumpTable($tableName)
    {
    $db = Yii::app()->db;
    $pdo = $db->getPdoInstance();
    echo '
        --
        -- Structure for table '.$tableName.'
        --
        '.PHP_EOL;
    echo 'DROP TABLE IF EXISTS '.$db->quoteTableName($tableName).';'.PHP_EOL;
    $q = $db->createCommand('SHOW CREATE TABLE '.$db->quoteTableName($tableName).';')->queryRow();
    echo $q['Create Table'].';'.PHP_EOL.PHP_EOL;
        $rows = $db->createCommand('SELECT * FROM '.$db->quoteTableName($tableName).';')->queryAll();
    if(empty($rows))
        return;
        echo '
        --
        -- Data for table '.$tableName.'
        --
        '.PHP_EOL;
    $attrs = array_map(array($db, 'quoteColumnName'), array_keys($rows[0]));
    //echo 'INSERT INTO '.$db->quoteTableName($tableName).''." (", implode(', ', $attrs), ') VALUES';
    $i=0;
    $j=0;
    $rowsCount = count($rows);
    foreach($rows AS $row)
    {
       if($j==1000)
       $j=0;
       if($j==0)
          echo 'INSERT INTO '.$db->quoteTableName($tableName).''." (", implode(', ', $attrs), ') VALUES '.PHP_EOL;;
          // Process row
          foreach($row AS $key => $value)
         {
             if($value === null)
         $row[$key] = 'NULL';
         else
         $row[$key] = $pdo->quote($value);
          }
              echo " (", implode(', ', $row), ')';
          if($j==999)
             echo ';';
          elseif($i<$rowsCount-1)
              echo ',';
          else
          echo ';';
          echo PHP_EOL;
          $i++;
          $j++;
         }
     echo PHP_EOL;
    echo PHP_EOL;
    }
#9918 report it
paskuale at 2012/09/21 01:52pm
thanks

great work @francis ja, thanks for share ! Now I've another strange error

Mysql Message: Documentation

2006 - MySQL server has gone away

?_?

#9917 report it
francis ja at 2012/09/21 01:21pm
[solved] Problem in database with CONSTRAINT (relation)

If we use this in a database with CONSTRAINT it will definitely cause error because other may not created when we add attribute so we can slove it by

SDatabaseDumper.php

public function getDump()
{
    ob_start();
    echo 'SET FOREIGN_KEY_CHECKS = 0;'.PHP_EOL;
    foreach($this->getTables() as $key=>$val)
        $this->dumpTable($key);
     echo 'SET FOREIGN_KEY_CHECKS = 1;'.PHP_EOL;
    $result=ob_get_contents();
    ob_end_clean();
    return $result;
}

echo 'SET FOREIGN_KEY_CHECKS = 0;'.PHP_EOL; will disable checking or validating foreign key CONSTRAINT during insertion.

Also if a table contains large records we will have to break the insert query So one table has more than 5000 records its better to split into different insert query else there will be issues with ** "max_allowed_packet"**

#9777 report it
paskuale at 2012/09/10 03:11am
Re: import data issue

Ok, you've hit the problem! I also use the module rights. There's a way to put all the tables with relationships before, may return the case as that of the table "authassignments".

thanks ;)

#9775 report it
Daniel at 2012/09/09 11:00pm
Re: import data issue

Hi,

If I am not wrong, you are created your yii application using rights extension (looking at authassignment table). If we use database-dumper, it will dump authassignment first before authitem, while on the constraint, it requires table authitem.

DROP TABLE IF EXISTS `authassignment`;
CREATE TABLE IF NOT EXISTS `authassignment` (
  `itemname` varchar(64) NOT NULL,
  `userid` varchar(64) NOT NULL,
  `bizrule` text,
  `data` text,
  PRIMARY KEY (`itemname`,`userid`),
  CONSTRAINT `AuthAssignment_ibfk_1` FOREIGN KEY (`itemname`) REFERENCES `authitem` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is my case when using chive (a phpyadmin alternative, developed using yii). If, we put authitem table first before authassignment. It should work. You may also need to check other relationship. Phpmyadmin does not have this problem since it move the contraint on the last after table definitions.

I don't know if we had same problem, but to me seems quite similar.

#9772 report it
paskuale at 2012/09/09 06:25pm
Re: import data issue

No I'm sorry but I have not understood, what chive? The name of your database? I do not understand. thanks

#9771 report it
Daniel at 2012/09/09 06:22pm
Re: import data issue

Hi,

I got the similar problem, actually, it was caused by the order of the table creation. I used chive to dump the database when import to phpmyadmin/chive itself it complained. After, restructured the auth* tables. I can import it either to phpmyadmin/chive.

Just tried it...

#9767 report it
paskuale at 2012/09/09 11:58am
import data issue

Hi thanks for share this extension, it seems to work but after creation dump.sql.gz file, I test to import in phpmyadmin, return me this:

1005 - Can't create table 'My_appName.authassignment' (errno: 150)

why ? Thanks

p.s. I click on details button, return : "InnoDB Documentazione Supports transactions, row-level locking, and foreign keys "

#8386 report it
maximaexchen at 2012/05/30 09:49am
I'm very sorry!!!!!

It was not your extension. Wrong extension page, one of the other sqldump extensions made those errors.

I did not say anything. ;-)

#8384 report it
kmdm at 2012/05/30 09:38am
to maximaexchen

"I don't understand the lines 131 to 138."

Are you talking abount SDatabaseDumper.php source code? There is only 104 lines. Anyway, can you show your code?

#8383 report it
maximaexchen at 2012/05/30 09:26am
some problems

Hi, it is crating an empty .sql in my app.

I played around a bit, beacause I wanted to send the dump via eMail.

I don't understand the lines 131 to 138. I found out the ',' in the first and last line of the "CREATE TABLE"-part is missing, also the ending ';' is missing in the generated sql-file.

Any advice?

#7859 report it
Peter JK at 2012/04/23 11:58am
controller

of course in controller, Jowen..

Like I do, you can add this in the end of code.. ... Yii::app()->user->setFlash('success','Great! backup process finished..'); this->redirect(array('/home')); ...

i also do this simple hacking for any day backup..

$file = Yii::getPathOfAlias('webroot.protected.backups').'/dump.'.Yii::app()->dateFormatter->format("yyyy-MM-dd",time()).'.sql';

#7853 report it
kmdm at 2012/04/23 03:38am
answer

in controller.


the code above will work anywhere in the project(views, controllers, widgets), but its bad idea to use such code in views.

#7851 report it
jowen at 2012/04/22 08:43pm
halo

Where to put the code from above?? view or controller??

#7833 report it
Peter JK at 2012/04/21 08:28am
simple but useful

hi..

thank for nice extension.. simple but useful.. cut several manual steps into one action. :-D

Leave a comment

Please to leave your comment.

Create extension
Downloads
No downloadable files yet