database-dumper

Yii Framework Extensions

Createnew extension

Popular Tags

database-dumper Allows to create backups of mysql database structure and data

Requirements
Usage
Resources

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-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 ¶

Project page at GitHub

8 0

15 followers

0 downloads

Yii Version: 1.1

License: MIT

Category: Database

Tags: backup, dump, MySQL

Developed by:

kmdm

Created on: Apr 20, 2012

Last updated: 13 years ago

Related Extensions

User Contributed Notes 18

#7833

0 0

simple but useful

hi..

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

Peter JK at Apr 21, 2012, 12:28:25 PM

#7851

0 0

halo

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

jowen at Apr 23, 2012, 12:43:33 AM

#7853

0 0

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.

kmdm at Apr 23, 2012, 7:38:16 AM

#7859

1 0

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

Peter JK at Apr 23, 2012, 3:58:52 PM

#8383

0 0

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?

maximaexchen at May 30, 2012, 1:26:11 PM

#8384

0 0

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?

kmdm at May 30, 2012, 1:38:51 PM

#8386

0 0

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

maximaexchen at May 30, 2012, 1:49:18 PM

#9767

1 0

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 "

paskuale at Sep 9, 2012, 3:58:57 PM

#9771

0 0

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

Daniel at Sep 9, 2012, 10:22:40 PM

#9772

0 0

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

paskuale at Sep 9, 2012, 10:25:28 PM

#9775

0 0

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.

Daniel at Sep 10, 2012, 3:00:02 AM

#9777

0 0

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

paskuale at Sep 10, 2012, 7:11:20 AM

#9917

2 0

[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"

francis ja at Sep 21, 2012, 5:21:47 PM

#9918

0 0

thanks

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

Mysql Message: Documentation

2006 - MySQL server has gone away

?_?

paskuale at Sep 21, 2012, 5:52:09 PM

#9938

0 0

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

francis ja at Sep 24, 2012, 7:41:56 AM

#9969

0 0

same error ... mysql server gone away

Nada ... same issue :(

paskuale at Sep 25, 2012, 8:32:35 PM

#12745

0 0

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

paskuale at Apr 9, 2013, 4:21:41 PM

#20066

0 0

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

Odirlei at Apr 13, 2017, 8:51:22 PM

Categories

Popular Tags

database-dumper Allows to create backups of mysql database structure and data

Requirements ¶

Usage ¶

Resources ¶

Related Extensions