<?php
/********************************************************/
/* MySQL Bits - Version 0.1							
/* Backs up large MySQL database into smaller files	
/*
/* Copyright (c) 2009 CharlesKonsor
/* http://charleskonsor.com/
/*
/* Licensed under the MIT licenses:
/* http://www.opensource.org/licenses/mit-license.php
/********************************************************/


/********************************************************/
/*********************** Config *************************/
/********************************************************/

/**** Required Config ****/
// MySQL Variables
define('MY_S','localhost'); // Server (usually 'localhost')
define('MY_D','database_name'); // Database Name
define('MY_U','database_username'); // Username
define('MY_P','database_password'); // Password


/**** Optional Config ****/
// Query Chunks
$pp = 10000;

// Max File Size (get fatal error at 2gb (2147483647), fopen's limiit)
$max_file_size = 2000000000;

// Filename Format (Month-Day-Year_Timestamp)
$name = date('m-d-Y_').time();

// Relative Folder to Save To
$path = "backups/";

// Days to Keep Backup
$life = 30;

// Start Timer
$time_start = time();

// Table Counter
$t = 0;

// Timeout (2 hours = 7200 Seconds)
ini_set('max_execution_time',7200);
ini_set('mysql.connect_timeout',7200);
set_time_limit(7200);
/********************************************************/
/********************** Functions ***********************/
/********************************************************/

/************************** c ***************************/
/* Connects to DB										*/
/********************************************************/
if(!function_exists("c")) {
	function c() {
		$db = mysql_connect(MY_S, MY_U, MY_P) or die('unable to connect');
		return $db;
	}
}

/************************** q ***************************/
/* Runs MySQL Query										*/
/********************************************************/
if(!function_exists("q")) {
	function q($query, $database = MY_D) {
		global $CONNECT, $DB;
		
		// Connect
		if(!isset($CONNECT)) {
			$CONNECT = c();
		}
			
		// Select Database
		if($database != $DB) {
			mysql_query("USE ".$database, $CONNECT) or mysql_error();
			$DB = $database;
		}
			
		// Run MySQL Query
		$resource = mysql_query($query, $CONNECT) or mysql_error();
		
		return $resource;
	}
}

/************************** f ***************************/
/* Mimes mysql_fetch_array function						*/
/********************************************************/
if(!function_exists("f")) {
	function f($sql,$assoc = MYSQL_BOTH) {
		return mysql_fetch_array($sql,$assoc);
	}
}

/************************** n ***************************/
/* Mimes mysql_num_rows function						*/
/********************************************************/
if(!function_exists("n")) {
	function n($sql) {
		return mysql_num_rows($sql);
	}
}


/********************************************************/
/************************ Backup ************************/
/********************************************************/

/********************* New Backup ***********************/
/* Gets DB table information							*/
/********************************************************/
// Get Tables
$sql0 = q("SHOW TABLES",MY_D,false);
while($qry0 = f($sql0)) {
	$table = $qry0[0];
	$primary = NULL;
	
	// Get Primary Key
	$qry = f(q("SHOW CREATE TABLE ".$table,MY_D,false));
	preg_match('/PRIMARY KEY  \(`(.*?)`\)/',$qry[1],$p);
	$primary = $p[1];
	// Integer?
	$ints = array('int','tinyint','smallint','mediumint','bigint','double','float');
	preg_match('/`'.$primary.'` (.*?)\(/',$qry['Create Table'],$type);
	if(!in_array($type[1],$ints)) $primary = NULL;
	
	// Max Row
	if($primary) $qry1 = f(q("SELECT MAX(".$primary.") FROM ".$table,MY_D,false));
	else $qry1 = f(q("SELECT COUNT(*) FROM ".$table,MY_D,false));
	$rows = $qry1[0];
	if(!$rows) $rows = 0;
	
	// Min Row
	if($primary) {
		$qry1 = f(q("SELECT MIN(".$primary.") FROM ".$table,MY_D,false));
		$progress = $qry1[0] - 1;
	}
	else $progress = 0;
	
	// Tables Array
	$tables[$table][name] = $table;
	$tables[$table][primary] = $primary;
	$tables[$table][rows] = $rows;
	$tables[$table][progress] = $progress;
	$tables[$table][create] = 0;
}

// Clear Query Memory
mysql_free_result($sql0);

/********************* File Loop ************************/
/* Loop creates files in chunks of 2gb (fopen limit) 	*/
/********************************************************/
for($c = 1;$c <= 1000;$c++) {
	
	// Destination File
	$file = $path.$name."_".$c.".sql";

	// Create Destination File
	$fp = fopen($file, 'w');
	fclose($fp);

	/************************ Loop **************************/
	/* Loops through DB in $pp chunks						*/
	/********************************************************/
	for($status = 1;$status < 2;) {
		// Clean Out Old Text
		$text = '';
	
		/*********************** Backup *************************/
		/* Queries rows and stores in .sql file					*/
		/********************************************************/
		if($status == 1) {
			// Row Counter
			$r = 0;
			
			// Loop Through Tables
			foreach($tables as $key => $table) {
				// Rows in this chunk less than $pp value and Still Rows to Count (or no rows)
				if($r < $pp and ($tables[$key][progress] < $tables[$key][rows] or ($tables[$key][rows] == 0 and $tables[$key][progress] <= 0))) {
				
					// 'Create Table' Code (if not yet done)
					if($tables[$key][create] == 0) {
						$qry = f(q("SHOW CREATE TABLE ".$tables[$key][name],MY_D,false));
						$text .= str_replace('CREATE TABLE','CREATE TABLE IF NOT EXISTS',$qry[1]).";\r\n\r\n";
						$tables[$key][create] = 1;
					}
					
					// Rows?
					if($tables[$key][rows] > 0) {
						// Get Rows (With Primary Key)
						if($tables[$key][primary]) {
							$end = $tables[$key][progress] + $pp;
							$query = "SELECT * FROM ".$tables[$key][name]." WHERE ".$tables[$key][primary]." > ".$tables[$key][progress]." AND ".$tables[$key][primary]." <= ".$end." ORDER BY ".$tables[$key][primary]." ASC";
						}
						// Get Rows (Without Primary Key)
						else $query = "SELECT * FROM ".$tables[$key][name]." LIMIT ".$tables[$key][progress].",$pp";
						
						// Query Rows
						//print "$query<br />";
						$q = 0;
						$sql = q($query,MY_D,false);
						if(n($sql)) {
							// Loop Through Rows
							$id = $tables[$key][progress];
							while($qry = f($sql,MYSQL_ASSOC)) {
								// Columns
								if($q == 0) {
									$text .= "INSERT IGNORE INTO ".$tables[$key][name]." (";
									$z = 0;
									foreach($qry as $column => $val) {
										if($z > 0) $text .= ",";
										$text .= $column;
										$z++;
									}
									$text .= ") VALUES "; 
								}
								
								// Values
								if($q > 0) $text .= ",";
								$text .= "(";
								$z = 0;
								foreach($qry as $val) {
									if($z > 0) $text .= ",";
									$text .= "'".$val."'";
									$z++;
								}
								$text .= ")";
								
								// Query Row Counter
								$q++;
								// Totoal Row Counter
								$r++;
								
								// Store Last Primary ID value
								if($tables[$key][primary]) $id = $qry[$tables[$key][primary]];
								
							}
							$text .= ";\r\n";
							
							// No Primary ID, Store Last Row #
							if(!$tables[$key][primary]) {
								$id = $tables[$key][progress] + $pp;
								if($id > $tables[$key][rows]) $id = $tables[$key][rows];
							}
							
							// Print Progress
							print "Rows ".$tables[$key][progress]."-".($id)." of table ".$tables[$key][name]." were successfully backed up.<br />";
							// Store Progress
							$tables[$key][progress] = $id;
							
							// Rows in Query < $pp, but still Rows in Table (set row count to max rows)
							if($tables[$key][rows] > $tables[$key][progress]) $r = $pp;
						}
						// No Rows in Query, Place Progress Marker at end of No Rows Selection
						else $tables[$key][progress] += $pp;
						
						// Clear Query Memory
						mysql_free_result($sql);
						
						// Finished Table
						if($tables[$key][progress] >= $tables[$key][rows]) {
							$text .= "\r\n\r\n";
							print "<b>Table ".$tables[$key][name]." has been sucessfully backed up.</b><br />";
							$t++;
						}
					}
					// No Rows in Table
					else {
						$tables[$key][progress] = 1;
						print "<b>Table ".$tables[$key][name]." has been sucessfully backed up.</b><br />";
						$t++;
					}
				}
			}
	
			// Save Backup to File
			if($f = fopen($file, 'a')) fwrite($f, $text);
			fclose($f);
			
			// File Size
			clearstatcache();
			$size = filesize($file);
			
			print "
			Backed Up Tables - $t<br />
			Remaining Tables - ".(count($tables) - $t)."<br /><br />";
			
			// No Tables Left to Store - or - Filesize fast approaching Max File Size (need to create new file)
			if($t >= count($tables) or $size >= $max_file_size) $status = 2;
		}
	
		/********************** Compress ************************/
		/* Compresses .sql file into .gz file					*/
		/********************************************************/
		if($status == 2) {
			$dest = $file.'.gz';
			$mode = 'wb1';
			if($fp_out = gzopen($dest,$mode)) {
				if($fp_in = fopen($file,'rb')) {
					while(!feof($fp_in)) gzwrite($fp_out,fread($fp_in,4096));
					fclose($fp_in);
				}
				else print "Error compressing file.<br />";
				gzclose($fp_out);
			}
			else print "Error creating compressed file.<br />";
			
			
			// Backup Complete
			if($t >= count($tables) or $t > $max_tables) {
				// Calcualte Time
				$time = time() - $time_start;
				if($time < 60) $time.= " seconds";
				else {
					$min = floor($time / 60);
					$sec = $time - ($min * 60);
					$time = $min.":".str_pad($sec,2,0,STR_PAD_LEFT);
				}
				
				print "<br /><b>Backup Complete</b> ($time) - $c file".($c == 1 ? '' : 's')."<br />";
				
				// Stop Looping
				$c = 9999999;
			}
		}
	}
}

/******************** Clear Backups *********************/
/* Deletes backups than $bak days old					*/
/********************************************************/
$past = time() - (86400 * $life);
if($f = opendir($path)) {
	while(false !== ($file = readdir($f))) {
		if(strlen($file) > 2) {
			preg_match('/([0-9]{10})/',$file,$time);
			if($time[1]) {
				if($time[1] < $past) {
					print "Deleted Backup - $file (".date('m/d/Y',$time[1]).")<br />";
					unlink($path.$file);
				}
				else print "Keeping Backup - $file (".date('m/d/Y',$time[1]).")<br />";
			}
		}
	}
}
?>