Simple class to create DB backups
Requirements ¶
Tested on 1.1.10.
Usage ¶
- Download latest version from GitHub(Click "Download as zip")
- Extract
yii-database-dumperto 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());
simple but useful
hi..
thank for nice extension.. simple but useful..
cut several manual steps into one action.
:-D
halo
Where to put the code from above?? view or controller??
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.
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';
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?
to maximaexchen
Are you talking abount SDatabaseDumper.php source code?
There is only 104 lines. Anyway, can you show your code?
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. ;-)
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 "
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...
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
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.
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 ;)
[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
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"
thanks
great work @francis ja, thanks for share ! Now I've another strange error
Mysql Message: Documentation
2006 - MySQL server has gone away
?_?
@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; }same error ... mysql server gone away
Nada ... same issue :(
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
Suggestion
In my case, there were two problems, triggers modifying data during import and tables with spatial data. Here are the modifications I made in the class.
Note: I am also splitting the result in several parts, to avoid locking.
<?php /** * Creates DB dump. * * Usage: * <pre> * Yii::import('ext.yii-database-dumper.SDatabaseDumper'); * $dumper=new SDatabaseDumper; * // Get path to backup file * $file=Yii::getPathOfAlias('webroot.protected.backups').DIRECTORY_SEPARATOR.'dump_'.date('Y-m-d_H_i_s').'.sql'; * * // Gzip dump * if(function_exists('gzencode')) * file_put_contents($file.'.gz', gzencode($dumper->getDump())); * else * file_put_contents($file, $dumper->getDump()); * </pre> */ class SDatabaseDumper { /** * Dump all tables * @return string sql structure and data */ public function getDump() { ob_start(); echo 'SET FOREIGN_KEY_CHECKS = 0;', PHP_EOL; echo 'LOCK TABLES WRITE;', PHP_EOL; foreach($this->getTables() as $key => $val) $this->dumpTable($key); echo 'UNLOCK TABLES;', PHP_EOL; echo 'SET FOREIGN_KEY_CHECKS = 1;', PHP_EOL; $result=ob_get_contents(); ob_end_clean(); return $result; } /** * Create table dump * @param $tableName * @return mixed */ public function dumpTable($tableName) { $db=Yii::app()->db; $pdo=$db->getPdoInstance(); $q=$db->createCommand('SHOW CREATE TABLE ' . $db->quoteTableName($tableName) . ';')->queryRow(); $columns=$db->createCommand('SELECT COLUMN_NAME, DATA_TYPE FROM information_schema.COLUMNS WHERE TABLE_NAME = ' . $pdo->quote($tableName) . ';')->queryAll(); $schema=array(); foreach($columns as $value) $schema[$value['COLUMN_NAME']]=$value['DATA_TYPE']; $selects=array(); foreach($schema as $key => $value) { if($value == 'geometry') $selects[]='ST_AsText(' . $db->quoteColumnName($key) . ') AS ' . $db->quoteColumnName($key); else $selects[]=$db->quoteColumnName($key); } $rows=$db->createCommand('SELECT ' . implode(', ', $selects) . ' FROM ' . $db->quoteTableName($tableName) . ';')->queryAll(); if(empty($rows)) return; $i=0; $rowsCount=count($rows); $rowsCount -= 1; $sql=''; $header='INSERT INTO ' . $db->quoteTableName($tableName) . ' (' . implode(', ', array_map(array($db, 'quoteColumnName'), array_keys($schema))) . ') VALUES' . PHP_EOL; $part=$header; echo 'DROP TABLE IF EXISTS ' . $db->quoteTableName($tableName) . ';', PHP_EOL; echo $q['Create Table'] . ';', PHP_EOL; foreach($rows as $row) { foreach($row as $key => $value) { if($value === null) $row[$key]='NULL'; elseif($schema[$key] == 'int') $row[$key]=$value; elseif($schema[$key] == 'geometry') $row[$key]='ST_GeomFromText(' . $pdo->quote($value) . ')'; else $row[$key]=$pdo->quote($value); } $part .= '(' . implode(', ', $row) . ')'; if($i < $rowsCount && strlen($part) < 2048) { $part .= ','; $part .= PHP_EOL; } else { $part .= ';'; $part .= PHP_EOL; $sql .= $part; $part=$header; } $i++; } echo $sql; } /** * Get mysql tables list * @return array */ public function getTables() { $db=Yii::app()->db; return $db->getSchema()->getTables(); } }If you have any questions, please ask in the forum instead.
Signup or Login in order to comment.