Архивиране на БД с Cron Jobs

[ivo75]

Трябва ми скрипт за архивиране на БД и мисля да го пускам с Cron Jobs

 

[index]

http://web-tourist.net/login/login/view.php?st=2752

 

[Fakeheal]

Ето ти един клас, който си писах наскоро за бекъпване на database. Може да си го направиш в един файл и да ти се execute-ва него с cron.

!! Note: Направи си някакъв config file като този ми. После направи папка application или просто оправи пътя до него !!

/* MISC */
define('DS', DIRECTORY_SEPARATOR);

/* Database Server Variables */
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');

/* Backup Variables */

define('DATE_FORMAT', 'Y-m-d-h-i-s');
define('DB_EXT', '.sql');
define('FILE_EXT', '.zip');
define('STORAGE', 'storage'.DS);
define('ROOT', '..'.DS);

<?php

class Backup {
	
	private $dateFormat = DATE_FORMAT;
	private $extensionDB 	= DB_EXT;
	private $extensionFiles = FILE_EXT;
	public $folderPath = ROOT;
	public $storeFolder = STORAGE;
	private $selectedFolder;
	private $conn;
	
	
	public function __construct()
	{
	}
	
	
	public function init()
	{
		try 
		{
			$this->conn = $this->mysqli_connect();
		}
		catch (Exception $e)
		{
			echo $e->getMessage();
			return false;
		}
		
		try 
		{
			$this->selectedFolder = $this->selectFolder($this->folderPath);	
		}
		catch (Exception $e)
		{
			echo $e->getMessage();
			return false;
		}
		
		return true;
	}
	
	public function mysqli_connect()
	{
		$connection = new mysqli(DB_HOST, DB_USER, DB_PASS);
		
		if($connection->connect_errno)
		{
			throw new Exception('Couldn\'t connect: '. $connection->connect_error);
		}
		else
		{
			return $connection;
		}
	}
	
	public function selectFolder($arg)
	{
		if(!is_writable($arg))
		{
			throw new Exception('Backup not allowed. Access denied.');
		}
		else
		{
			$handle 	= opendir($arg);
			$fileList 	= array();
			while (false !== ($entry = readdir($handle))) {
				if ($entry != "." && $entry != "..") {
		       		$fileList[] = $entry;
				}
		    }
					
			return $fileList;
		}
	}
	
	public function showDatabases()
	{
		$mysqli = $this->conn;
		$dbQuery = $mysqli->query('show databases');
		while($row = $dbQuery->fetch_assoc())
		{
			$db[] = $row;
		}
		
		return $db;
	}
	
	public function database($dbName)
	{
		$mysqli = $this->conn;
		$mysqli->select_db($dbName);
		$date = date($this->dateFormat, time());
		$backupFile = $this->storeFolder.$dbName.$date.$this->extensionDB;
		
		
		$sql = "SHOW TABLES";
		$retval = $mysqli->query( $sql);
		if(! $retval )
		{
			die('Could not retrive tables: ' . mysql_error());
		}
		else
		{
			while($row = $retval->fetch_array())
			{
				$tables[] = $row[0];
			}
		}
		$starttime = microtime(true);
		$headers = "-- MySql Data Dump\n\n";
		$headers .= "-- Database : " . $dbName . "\n\n";
		$headers .= "-- Dumping started at : ". date("Y-m-d-h-i-s") .  "\n\n";
		$outputdata = '';
		for($t = 0; $t < @count($tables); $t++){
			$outputdata .= "\n\n-- Dumping schema for table : $tables[$t]\n\n";
			$tableschema = $mysqli->query("SHOW CREATE TABLE `$tables[$t]`");
			$tableschema = $tableschema->fetch_assoc();
			$outputdata .= "{$tableschema['Create Table']};\n\n";
			$outputdata .= "\n\n-- Dumping data for table : $tables[$t]\n\n";
			$sql = "SELECT * FROM $tables[$t]";
			$result = $mysqli->query($sql);
			$lines = '';
			while($row = $result->fetch_assoc()){
				$nor = count($row);
				$datas = array();
				foreach($row as $r)
				{
					$datas[] = $r;
				}
				
				$lines .= "INSERT INTO $tables[$t] VALUES (";
				for( $i= 0; $i < $nor; $i++)
				{
					if( $datas[$i] === NULL )
					{
						$lines .= "NULL";
					}
					else if((string)$datas[$i] == "0")
					{
						$lines .= "0";
					}
					else if(filter_var($datas[$i],FILTER_VALIDATE_INT) || filter_var($datas[$i],FILTER_VALIDATE_FLOAT))
					{
						$lines .= $datas[$i];
					}
					else
					{
						$lines .= "'" . str_replace("\n","\\n",$datas[$i]) . "'";
					}
					if($i==$nor-1)
					{
						$lines .= ");\n";
					}
					else
					{
						$lines .= ",";
					}
				}
				$outputdata .= $lines;
				$lines = "";
			}
		}
		$headers .= "-- Dumping finished at : ". date("Y-m-d-h-i-s") .  "\n\n";
		$endtime = microtime(true);
		$diff = $endtime - $starttime;
		$headers .= "-- Dumping data of $dbName took : ". $diff .  " Sec\n\n";
		$headers .= "-- --------------------------------------------------------";
		$datadump = $headers . $outputdata;
		
		$file_open = fopen($backupFile, 'w');
		fwrite($file_open, $datadump);
		fclose($file_open);
	}

Eто така се използва:

<?php
	include('config/application.php');
	include('libs/Backup.class.php');
	
	echo '<a href="dropbox.php">Upload a backup to Dropbox?</a>';
	
	$backup = new Backup('root', '', 'localhost');
	
	$init = $backup->init();
	
	if($init)
	{
		
		if(isset($_POST['backupDB']))
		{
			$database = $_POST['db'];
			if(is_array($database) && count($database) <= 1)
			{
				$backup->database($database[0]);
				echo 'Backup successful for <b>'.$database[0].'</b>. <br />';
			}
			else
			{
				foreach ($database as $db)
				{
					$backup->database($db);
					echo 'Backup successful for <b>'.$db.'</b>. <br />';
				}
			}
		}
		
		//All databases on that host.
		echo '<h3>Current databases: </h3>';
		echo '<form method="post" action="index.php">';
		$databases = $backup->showDatabases();
		foreach ($databases as $db)
		{
			echo '<input type="checkbox" name="db[]" value="'.$db['Database'].'" /><b>DB Name:</b> '.$db['Database'].'<br />';
		}
		echo '<input type="submit" name="backupDB" value="backup!" />';
		echo '</form>';
		
	}

P.S. Това малко е с друго предназначение. Дава ти да си избираш таблици и прочие. Трябваше да го интегрирам с дропбокс, ама ми мина мерака и се отказах