MYSQL backup by command in php

add a xml file called database.xml and also folders sql/ and sql_zip/ and

add a php file call backupdb.php and paste this class snippet


	/**
	* Database Backup classed for all the db at once
	*/
	class DatabaseBackup
	{

		private $dbhost = '';
		private $dbuser = '';
		private $dbpass = '';

		public $connection = null;
		
		function __construct($dbhost = 'localhost',$dbuser = 'root',$dbpass = '')
		{
			$this->dbhost = $dbhost;
			$this->dbuser = $dbuser;
			$this->dbpass = $dbpass;

			$this->connection = mysqli_connect($this->dbhost, $this->dbuser ,$this->dbpass);

		}


		// export all single database recursively 
		public function exportAll($extract_type = 'sql', $path = 'sql/')
		{
			
			$res = mysqli_query($this->connection,"SHOW DATABASES");
			
			while ($row = mysqli_fetch_assoc($res)) {
			   
				$dbname = $row['Database'];   

				if($extract_type == 'zip'){
				 
				$backup_file = $dbname . date("Y-m-d-H-i-s") . '.sql.gz';
				$command = "mysqldump --opt 
							-h $this->dbhost 
							-u $this->dbuser $dbname 
							| gzip > sql_zip/$backup_file";

				system($command);
				}
				else {
					$backup_file = $dbname . date("Y-m-d-H-i-s") . '.sql';
				$command = "mysqldump --user=$this->dbuser --password=$this->dbpass --host=$this->dbhost $dbname  > sql/" . $backup_file;
				// return $command;

				exec($command);
				}



				$db[] = [

				 'filename' => $backup_file,
				 'dbname' 	=> $dbname

				];

				 
			}

			return $db;
		}

		// export all single database recursively and save log to xml
		public function dbToXMLRecord($file = null)
		{
			$dbs = $this->exportAll();

			$xml = new SimpleXMLElement('<?xml version="1.0" encoding="utf-8"?><databases></databases>');

			$xml->addAttribute('version', '1.0');

			foreach ($dbs as $key => $db) {

				$item  =  $xml->addChild('item');
				$item->addChild('filename',$db['filename']);
				$item->addChild('dbname',$db['dbname']);

			
			}

			$databaseXML = fopen("databases.xml", "w") or die("Unable to open file!");
			
			fwrite($databaseXML, $xml->asXML());
			
			fclose($databaseXML);

			return $xml->asXML();
		
		}

		// import single db from xml recursively from xml file
		public function xmlToDBRecord($file = 'databases.xml')
		{
			$xmlDoc = new DOMDocument();
			$xmlDoc->load($file);
			$x = $xmlDoc->documentElement;

			$count = 1;

			foreach ($x->childNodes AS $items) {

				print_r($items->childNodes[1]->nodeValue);


				$res = mysqli_query($this->connection,"CREATE DATABASE IF NOT EXISTS ". $items->childNodes[1]->nodeValue);
				mysqli_query($this->connection,"USE ".$items->childNodes[1]->nodeValue);
				
				$command = "mysql -u $this->dbuser -p $this->dbpass < sql/". $items->childNodes[0]->nodeValue ;
				// return $res;

				exec($command,$output=array(),$worked);

				switch($worked){
				    case 0:
				        echo 'Import file <b>' .$items->childNodes[0]->nodeValue .'</b> successfully imported to database <b>' .$items->childNodes[1]->nodeValue .'</b>';
				        break;
				    case 1:
				        echo 'There was an error during import. Please make sure the import file is saved in the same folder as this script and check your values:<br/><br/><table><tr><td>MySQL Database Name:</td><td><b>' .$items->childNodes[1]->nodeValue .'</b></td></tr><tr><td>MySQL User Name:</td><td><b>' .$this->dbuser .'</b></td></tr><tr><td>MySQL Password:</td><td><b>NOTSHOWN</b></td></tr><tr><td>MySQL Host Name:</td><td><b>' .$this->dbhost .'</b></td></tr><tr><td>MySQL Import Filename:</td><td><b>' .$items->childNodes[0]->nodeValue .'</b></td></tr></table>';
				        break;
				}
				
				echo $command . "<br>";

				$count++;
								
			}

			return $count;
		}

		// test import 
		public function tryImport()
		{
			$command = "mysql -u $this->dbuser -p $this->dbpass < sql/music2017-02-02-23-21-12.sql" ;
            return $command;
            exec($command);
		}

		// all database in one sql file and xml
		public function tryAllExport()
		{
			// return $extract_type;
			$res = mysqli_query($this->connection,"SHOW DATABASES");
			// return $res;

			$databases = '';
			while ($row = mysqli_fetch_assoc($res)) {

				$databases .= $row['Database'] . ' ';
				
			}

			// return $databases;

			$command = "mysqldump -u $this->dbuser -p $this->dbpass --databases $databases > all_backup_2.sql" ;
			// $command = "mysqldump -u $this->dbuser -p $this->dbpass --databases c9db cashmemo clientapi db_lms > all_backup.sql" ;

			// return $command;

			$command = "mysqldump -u $this->dbuser -p $this->dbpass --all-databases > alldb_backup2.sql" ;
            
            // exec($command);
            exec('mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql');
		}



	}

	$dbBackup = new DatabaseBackup();

	if(isset($_GET['type']) && ($_GET['type'] == 'zip')){
		var_dump($dbBackup->exportAll('zip'));
	}
	else if(isset($_GET['to']) && ($_GET['to'] == 'xml')){
			var_dump($dbBackup->dbToXMLRecord());
	}
	else if(isset($_GET['to']) && ($_GET['to'] == 'db')){
			var_dump($dbBackup->xmlToDBRecord());
			// var_dump($dbBackup->tryImport());
	}
	else if(isset($_GET['to']) && ($_GET['to'] == 'tryx')){
			// var_dump($dbBackup->xmlToDBRecord());
			var_dump($dbBackup->tryAllExport());
	}
	else{
		var_dump($dbBackup->exportAll());
	}

in the browser serve “http://localhost/backupdb.php?to=xml
// here
to=xml for export from db to individuals sql file and xml enty
to=tryx all in a single file and also xml entry
to=zip all sqls file to zip in the sql_zip folder
to=db import from xml entry from sql files to db
record are saved as sql files in the sql folder and sql_zip folder

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s