Tiny Php.Mysql.Administration

tiny-pma.php

<?php



/*
 * Configuration
 */

define('DB_HOST',   'localhost');
define('DB_USER',   'username');
define('DB_PASS',   'password');
define('DB_BASE',   'database');
define('DB_PREFIX', '');





/*
 * Affichage des images
 */

if( isset($_GET['img']) )
{
	$imgArrow = 'iVBORw0KGgoAAAANSUhEUgAAACYAAAAWCAMAAACbmR4xAAAABGdBTUEAAK/INwWK6QAAABl0RVh0U29mdHdhcmUAQWRvYmUgSW1hZ2VSZWFkeXHJZTwAAAAGUExURQAAAAAAAKVnuc8AAAACdFJOU/8A5bcwSgAAAIdJREFUeNpiYCQKAAQQA3HKAAKIgTgNAAEEl2RgwKcOIIAYEKrwqQMIIAYkVXjUAQQQA7Iq3OoAAohILwAEEJHKAAKISGUAAUSkMoAAIlIZQAARqQwggIhUBhBARCoDCCAilQEEEJIyHAAsCRBARKY3gAAiUhlAABGpDCCAiFQGEEBEKgMIMAAZagMLzQih2gAAAABJRU5ErkJggg==';
	
	switch ($_GET['img'])
	{
		case 'arrow': 
			$img = $imgArrow;
		break;
	}
	
	header('Content-Type: image/png');
	echo base64_decode($img);
	exit;
}





/*
 * Connexion
 */

$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Connexion au server MySQL impossible");
mysql_select_db(DB_BASE, $link) or die('Connexion à la base '. DB_BASE .' impossible');





/*
 * Actions (sur les tables)
 */

if( isset($_POST['action']) )
{
	if( isset($_POST['tables']) && is_array($_POST['tables']) )
	{
		$action_tablenames = array_map(
				create_function('$tablename', 'return "`". mysql_real_escape_string($tablename) ."`";'),
				$_POST['tables']
		);
		$action_tablenames_str = join(' , ', $action_tablenames);
	}
	
	
	switch($_POST['action'])
	{
		case 'check':
			$action_query = "CHECK TABLE {$action_tablenames_str}";
		break;
		
		case 'optimize':
			$action_query = "OPTIMIZE TABLE {$action_tablenames_str}";
		break;
		
		case 'repair':
			$action_query = "REPAIR TABLE {$action_tablenames_str}";
		break;
		
		case 'analyze':
			$action_query = "ANALYZE TABLE {$action_tablenames_str}";
		break;
	}
	
	
	if( isset($action_query) && isset($action_tablenames) )
	{
		$action_results = array();
		
		$results = mysql_query($action_query);
		while( $result = mysql_fetch_assoc($results) )
			$action_results[] = $result;
	}
}





/*
 * Informations de la base
 */

$database = array();

$result = mysql_fetch_assoc(mysql_query("SHOW VARIABLES LIKE 'collation_database'"));
$database['collation_database'] = $result['Value'];

$result = mysql_fetch_assoc(mysql_query("SHOW VARIABLES LIKE 'table_type'"));
$database['table_type'] = $result['Value'];

$result = mysql_fetch_assoc(mysql_query("SHOW VARIABLES LIKE 'default_storage_engine'"));
$database['default_storage_engine'] = $result['Value'];

$result = mysql_fetch_assoc(mysql_query("SHOW VARIABLES LIKE 'version'"));
$database['version'] = $result['Value'];





/*
 * Informations des tables
 */

$tables = array();

$db_base = DB_BASE;
$db_prefix = DB_PREFIX;

$query = "SELECT * 
FROM `information_schema`.`TABLES`
WHERE `TABLE_SCHEMA` = '{$db_base}'
  AND `TABLE_NAME` LIKE '{$db_prefix}%'
ORDER BY `TABLE_NAME`";

$results = mysql_query($query, $link);
while( $result = mysql_fetch_assoc($results) )
{
	$tables[ $result['TABLE_NAME'] ] = $result;
}





/*
 * Export
 */

if( isset($_GET['action']) && $_GET['action']=='dump' )
{
	mysql_query('SET NAMES utf8');
	
	$sql_dump  = '-- Tiny Php.Mysql.Administration' ."\n";
	$sql_dump .= '-- version 1.0' ."\n";
	$sql_dump .= '-- http://seebz.net' ."\n";
	
	$sql_dump .= '--' ."\n";
	
	$sql_dump .= '-- Host: '. DB_HOST ."\n";
	$sql_dump .= '-- Generation Time: '. date('M d, Y \a\t h:i A') ."\n";
	$sql_dump .= '-- Server version: '. $database['version'] ."\n";
	$sql_dump .= '-- PHP Version: '. PHP_VERSION ."\n";
	
	$sql_dump .= "\n";
	
	$sql_dump .= 'SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";' ."\n\n\n";
	$sql_dump .= '/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */' ."\n";
	$sql_dump .= '/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */' ."\n";
	$sql_dump .= '/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */' ."\n";
	$sql_dump .= '/*!40101 SET NAMES utf8 */;' ."\n";
	
	$sql_dump .= "\n";
	
	$sql_dump .= "--\n-- Database: `". DB_BASE ."` \n--\n";
	
	
	foreach($tables as $tablename=>$ignore)
	{
		$sql_dump .= export_table($tablename);
	}
	
	
	header('Content-Disposition: attachment; filename="'. DB_BASE .'.sql"');
	header('Content-Type: text/x-sql');
	
	
	echo $sql_dump;
	exit;
}





/*
 * Fonctions utiles
 */

function human_size( $size, $precision = 2 )
{
	if( $size<1024 ) return $size .' B';
	
	$size = $size/1024;
	if( $size<1024 ) return number_format($size, $precision, '.', ',') .' KiB';
	
	$size = $size/1024;
	if( $size<1024 ) return number_format($size, $precision, '.', ',') .' MiB';
	
	$size = $size/1024;
	if( $size<1024 ) return number_format($size, $precision, '.', ',') .' GiB';
}


function export_table( $tablename, $link = null )
{
	$query = "SHOW CREATE TABLE `{$tablename}`";
	$result = mysql_query($query);
	if( $result )
	{
		$result = mysql_fetch_array($result);
		
		$out  = "\n-- --------------------------------------------------------\n";
		$out .= "\n--\n-- Table structure for table `{$tablename}`\n--\n\n";
		$out .= str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $result[1]) .';'."\n\n";
		
		
		$query = "SELECT * FROM `{$tablename}`";
		$results = mysql_query($query);
		
		$out .= "--\n-- Dumping data for table `{$tablename}`\n--\n\n";
		$count = 0;
		while( $result = mysql_fetch_assoc($results) )
		{
			if( !isset($keys) )
			{
				$keys = array_map(
						create_function('$key', 'return "`{$key}`";'),
						array_keys($result)
				);
				$keys = join(', ', $keys);
			}
			$values = array_map(
					create_function('$value', 'return "\'". mysql_real_escape_string($value) ."\'";'),
					$result
			);
			$values = join(', ', $values);
			
			if($count%50==0)
			{
				if($count) $out .= ";\n";
				$out .= "INSERT INTO `{$tablename}` ({$keys}) VALUES \n({$values})";
			}
			else
			{
				$out .= ",\n";
				$out .= "({$values})";
			}
			
			$count++;
		}
		if($count) $out .= ";\n";
		
		return $out;
	}
}




?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<script type="text/javascript">
// Events
function actionChange(self) {
	var value = self.options[ self.selectedIndex ].value;
	if( value.length )
		document.getElementById('form').submit();
}
function checkAll() {
	var trs = getTrs();
	for(i=0; i<trs.length; i++)
	{
		var tr = trs[i];
		tr.getElementsByTagName('input')[0].checked = true;
	}
}
function uncheckAll() {
	var trs = getTrs();
	for(i=0; i<trs.length; i++)
	{
		var tr = trs[i];
		tr.getElementsByTagName('input')[0].checked = false
	}
}
function checkOverhead() {
	var trs = getTrs();
	for(i=0; i<trs.length; i++)
	{
		var tr = trs[i];
		tr.getElementsByTagName('input')[0].checked = ( tr.className.toString().match(/overhead/) !== null );
	}
}

function doDump() {
	window.location = '<?php echo $_SERVER['PHP_SELF'] ?>?action=dump';
}

// Utils
function getTrs() {
	var tbodys = document.getElementsByTagName('tbody');
	var tbody = tbodys[ (tbodys.length-2) ];
	return tbody.getElementsByTagName('tr');
}
</script>

<style type="text/css">
html { margin:0; padding:0; }
body { background-color:#F5F5F5; color:#000000; font-family:sans-serif; margin:0.5em; padding:0; font-size:13px; }
a { color:#0000FF; text-decoration:none; }
a:hover { color:#FF0000; text-decoration:underline; }

table { margin:1em 0; font-size:12px; }
table#tables_list { min-width:690px; }
table th, table td { margin:0.1em; padding:0.1em 0.5em; vertical-align:top; white-space:nowrap; }
table th { background-color:#D3DCE3; }
table .value { text-align:right; font-family:monospace; font-size:90% }
table tr.odd th,
table tr.even th { text-align:left; }
table tr.odd,
table tr.odd th { background-color:#E5E5E5; }
table tr.even,
table tr.even th { background-color:#D5D5D5; }
table tr.odd:hover,
table tr.odd:hover th,
table tr.even:hover,
table tr.even:hover th { background-color:#CCFFCC; }

#action_result {}
#action_query { display:inline-block; margin:1em 0; padding:1em; font-family:monospace; font-size:10px; color:#990099; border:2px solid #D3DCE3; background-color:#EEEEEE; }
#action_query span.tablename { color:#008000; }
</style>
</head>
<body>




<?php

	if( isset($action_query) && isset($action_results) && !empty($action_results) ) :
	
	# Une action a été exécutée

?>
<div id="action_result">


<code id="action_query">
<?php
	
	$search  = $action_tablenames;
	$replace = array_map(
				create_function('$tablename', 'return "<span class=\"tablename\">{$tablename}</span>";'),
				$search
	);

	echo str_replace($search, $replace, $action_query);
	
?>
</code>


<table id="action_results">
<?php

	$columns_name = array_keys($action_results[0]);

	echo '<tr>';
	foreach($columns_name as $column_name)
		echo "<th>{$column_name}</th>";
	echo '</tr>';

	$i = 0;
	
	foreach($action_results as $action_result)
	{
		$i++;
		echo '<tr class="'. ( ($i%2==0) ? 'even' : 'odd' ) .'">';
		foreach($action_result as $value)
			echo "<td>{$value}</td>";
		echo '</tr>';
	}

?>
</table>


</div>
<?php

	endif

?>



<form action="" method="post" id="form">


<table id="tables_list">
<thead>
	<tr>
		<th></th>
		<th>Table</th>
		<th>Records</th>
		<th>Type</th>
		<th>Collation</th>
		<th>Size</th>
		<th>Overhead</th>
	</tr>
</thead>
<tbody>
<?php

$i = $total = $total_rows = $total_length = $total_overhead = 0;

	foreach($tables as $table) :
		
		$table_length = ( $table['DATA_LENGTH'] + $table['INDEX_LENGTH'] );
		
		$total++;
		$total_rows		+= $table['TABLE_ROWS'];
		$total_length	+= $table_length;
		
		$as_overhead = (bool) ( $table['ROW_FORMAT']!='Compact' && $table['DATA_FREE'] );
		
		if( $as_overhead )
			$total_overhead += $table['DATA_FREE'];
		
		$i++;

?>
	<tr class="<?php if($as_overhead) echo 'overhead' ?> <?php echo ($i%2==0) ? 'even' : 'odd' ?>">
		<td><input type="checkbox" name="tables[]" id="checked_<?php echo $table['TABLE_NAME'] ?>" value="<?php echo $table['TABLE_NAME'] ?>" /></td>
		<th><label for="checked_<?php echo $table['TABLE_NAME'] ?>"><?php echo $table['TABLE_NAME'] ?></label></th>
		<td class="value"><?php echo number_format($table['TABLE_ROWS'], 0, '.', ',') ?></td>
		<td><?php echo $table['ENGINE'] ?></td>
		<td><?php echo $table['TABLE_COLLATION'] ?></td>
		<td class="value"><?php echo human_size($table_length, 1) ?></td>
		<td class="value"><?php echo ( $as_overhead ? human_size($table['DATA_FREE'], 1) : '-' ) ?></td>
	</tr>
<?php

	endforeach

?>
</tbody>
<tbody>
	<tr>
		<th></th>
		<th><?php echo $total ?> table(s)</th>
		<th class="value"><?php echo number_format($total_rows, 0, '.', ',') ?></th>
		<th><?php echo $database['default_storage_engine'] ?></th>
		<th><?php echo $database['collation_database'] ?></th>
		<th class="value"><?php echo human_size($total_length, 1) ?></th>
		<th class="value"><?php echo human_size($total_overhead, 1) ?></th>
	</tr>
</tbody>
</table>


<div id="actions">
	
	&nbsp; <img src="<?php echo $_SERVER['PHP_SELF'] ?>?img=arrow" alt="" />
	
	<a href="#" onclick="checkAll(); return false;">Check All</a> / 
	<a href="#" onclick="uncheckAll(); return false;">Uncheck All</a> / 
	<a href="#" onclick="checkOverhead(); return false;">Check tables having overhead</a>
	
	&nbsp; &nbsp; &nbsp;
	
	<select name="action" id="action_select" onchange="actionChange(this);">
		<option value="" selected="selected">With selected :</option>
		<option value="check">Check table</option>
		<option value="optimize">Optimize table</option>
		<option value="repair">Repair table</option>
		<option value="analyze">Analyze table</option>
	</select>
	
	&nbsp; &nbsp; &nbsp; &nbsp;
	
	<input type="button" value="Backup Database" onclick="doDump();" />
	
</div>


</form>


</body>
</html>