PHP MySQL 在线管理优化系统

我将为您开发一个功能完整的 MySQL 在线管理优化系统，包含数据库管理、SQL执行、性能优化等功能。

系统架构

mysql_manager/
├── index.php          # 主入口
├── config.php         # 配置文件
├── auth.php           # 认证模块
├── db_connect.php     # 数据库连接
├── css/
│   └── style.css      # 样式文件
├── js/
│   └── main.js        # JavaScript功能
├── modules/
│   ├── database.php   # 数据库管理
│   ├── query.php      # SQL执行器
│   ├── optimize.php   # 优化工具
│   ├── backup.php     # 备份恢复
│   └── monitor.php    # 监控面板
└── includes/
    ├── functions.php  # 公共函数
    └── security.php   # 安全处理

核心代码实现

1. config.php - 配置文件

<?php
// config.php
session_start();

// 数据库配置
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
define('DB_NAME', 'mysql');

// 系统配置
define('SITE_NAME', 'MySQL管理优化系统');
define('VERSION', '1.0.0');
define('MAX_QUERY_SIZE', 1048576); // 1MB
define('QUERY_TIMEOUT', 30); // 30秒超时
define('BACKUP_PATH', __DIR__ . '/backups/');

// 错误报告
error_reporting(E_ALL);
ini_set('display_errors', 0);
ini_set('log_errors', 1);

// 时区设置
date_default_timezone_set('PRC');

// 创建备份目录
if (!is_dir(BACKUP_PATH)) {
    mkdir(BACKUP_PATH, 0755, true);
}
?>

2. auth.php - 认证模块

<?php
// auth.php
require_once 'config.php';

class Auth {
    private $users = [];
    private $auth_file = __DIR__ . '/users.json';
    
    public function __construct() {
        $this->loadUsers();
        if (!isset($_SESSION['login_time'])) {
            $_SESSION['login_time'] = time();
        }
    }
    
    private function loadUsers() {
        if (file_exists($this->auth_file)) {
            $this->users = json_decode(file_get_contents($this->auth_file), true);
        } else {
            // 默认管理员账号（请及时修改）
            $this->users['admin'] = password_hash('admin123', PASSWORD_DEFAULT);
            $this->saveUsers();
        }
    }
    
    private function saveUsers() {
        file_put_contents($this->auth_file, json_encode($this->users, JSON_PRETTY_PRINT));
    }
    
    public function login($username, $password) {
        if (isset($this->users[$username]) && password_verify($password, $this->users[$username])) {
            $_SESSION['user'] = $username;
            $_SESSION['login_time'] = time();
            return true;
        }
        return false;
    }
    
    public function logout() {
        session_destroy();
        return true;
    }
    
    public function isLoggedIn() {
        if (!isset($_SESSION['user'])) {
            return false;
        }
        
        // 会话超时检查（30分钟）
        if (time() - $_SESSION['login_time'] > 1800) {
            $this->logout();
            return false;
        }
        
        return true;
    }
    
    public function changePassword($username, $oldPass, $newPass) {
        if (isset($this->users[$username]) && password_verify($oldPass, $this->users[$username])) {
            $this->users[$username] = password_hash($newPass, PASSWORD_DEFAULT);
            $this->saveUsers();
            return true;
        }
        return false;
    }
    
    public function addUser($username, $password, $isAdmin = false) {
        if (!isset($this->users[$username])) {
            $this->users[$username] = password_hash($password, PASSWORD_DEFAULT);
            $this->saveUsers();
            return true;
        }
        return false;
    }
}
?>

3. db_connect.php - 数据库连接

<?php
// db_connect.php
require_once 'config.php';

class DatabaseManager {
    private $connection = null;
    private $error = '';
    
    public function __construct($host = null, $user = null, $pass = null, $db = null) {
        $host = $host ?: DB_HOST;
        $user = $user ?: DB_USER;
        $pass = $pass ?: DB_PASS;
        $db = $db ?: DB_NAME;
        
        try {
            $this->connection = new mysqli($host, $user, $pass, $db);
            
            if ($this->connection->connect_error) {
                throw new Exception("连接失败: " . $this->connection->connect_error);
            }
            
            $this->connection->set_charset("utf8mb4");
            $this->connection->query("SET SESSION sql_mode = ''");
            
        } catch (Exception $e) {
            $this->error = $e->getMessage();
            $this->connection = null;
        }
    }
    
    public function getConnection() {
        return $this->connection;
    }
    
    public function isConnected() {
        return $this->connection !== null;
    }
    
    public function getError() {
        return $this->error;
    }
    
    public function executeQuery($sql) {
        if (!$this->connection) {
            return false;
        }
        
        // 设置查询超时
        $this->connection->query("SET SESSION MAX_EXECUTION_TIME=" . (QUERY_TIMEOUT * 1000));
        
        $startTime = microtime(true);
        $result = $this->connection->query($sql);
        $executionTime = microtime(true) - $startTime;
        
        if ($result === false) {
            return [
                'success' => false,
                'error' => $this->connection->error,
                'time' => $executionTime
            ];
        }
        
        // 获取结果
        $data = [];
        if ($result instanceof mysqli_result) {
            while ($row = $result->fetch_assoc()) {
                $data[] = $row;
            }
            $result->free();
            $affected = count($data);
        } else {
            $affected = $this->connection->affected_rows;
        }
        
        return [
            'success' => true,
            'data' => $data,
            'affected' => $affected,
            'time' => $executionTime,
            'insert_id' => $this->connection->insert_id
        ];
    }
    
    public function getDatabases() {
        $result = $this->executeQuery("SHOW DATABASES");
        if ($result['success']) {
            return array_column($result['data'], 'Database');
        }
        return [];
    }
    
    public function getTables($database) {
        $this->connection->select_db($database);
        $result = $this->executeQuery("SHOW TABLE STATUS");
        if ($result['success']) {
            return $result['data'];
        }
        return [];
    }
    
    public function getTableStructure($database, $table) {
        $this->connection->select_db($database);
        $result = $this->executeQuery("SHOW FULL COLUMNS FROM `$table`");
        if ($result['success']) {
            return $result['data'];
        }
        return [];
    }
    
    public function getTableIndexes($database, $table) {
        $this->connection->select_db($database);
        $result = $this->executeQuery("SHOW INDEX FROM `$table`");
        if ($result['success']) {
            return $result['data'];
        }
        return [];
    }
    
    public function optimizeTable($database, $table) {
        $this->connection->select_db($database);
        return $this->executeQuery("OPTIMIZE TABLE `$table`");
    }
    
    public function analyzeTable($database, $table) {
        $this->connection->select_db($database);
        return $this->executeQuery("ANALYZE TABLE `$table`");
    }
    
    public function repairTable($database, $table) {
        $this->connection->select_db($database);
        return $this->executeQuery("REPAIR TABLE `$table`");
    }
    
    public function getServerStatus() {
        $status = [];
        $variables = $this->executeQuery("SHOW GLOBAL STATUS");
        if ($variables['success']) {
            foreach ($variables['data'] as $var) {
                $status[$var['Variable_name']] = $var['Value'];
            }
        }
        return $status;
    }
    
    public function getServerVariables() {
        $variables = [];
        $result = $this->executeQuery("SHOW GLOBAL VARIABLES");
        if ($result['success']) {
            foreach ($result['data'] as $var) {
                $variables[$var['Variable_name']] = $var['Value'];
            }
        }
        return $variables;
    }
    
    public function getProcessList() {
        return $this->executeQuery("SHOW FULL PROCESSLIST");
    }
    
    public function killProcess($processId) {
        return $this->executeQuery("KILL {$processId}");
    }
    
    public function backupDatabase($database, $tables = null) {
        $this->connection->select_db($database);
        $backup = "-- MySQL Backup\n";
        $backup .= "-- Database: {$database}\n";
        $backup .= "-- Date: " . date('Y-m-d H:i:s') . "\n\n";
        
        if ($tables === null) {
            $tables = $this->getTables($database);
            $tables = array_column($tables, 'Name');
        }
        
        foreach ($tables as $table) {
            // 获取表结构
            $create = $this->executeQuery("SHOW CREATE TABLE `$table`");
            if ($create['success'] && !empty($create['data'])) {
                $backup .= "DROP TABLE IF EXISTS `$table`;\n";
                $backup .= $create['data'][0]['Create Table'] . ";\n\n";
            }
            
            // 获取数据
            $data = $this->executeQuery("SELECT * FROM `$table`");
            if ($data['success'] && !empty($data['data'])) {
                foreach ($data['data'] as $row) {
                    $values = [];
                    foreach ($row as $value) {
                        $values[] = "'" . $this->connection->real_escape_string($value) . "'";
                    }
                    $backup .= "INSERT INTO `$table` VALUES (" . implode(',', $values) . ");\n";
                }
                $backup .= "\n";
            }
        }
        
        $filename = BACKUP_PATH . "{$database}_" . date('Ymd_His') . ".sql";
        file_put_contents($filename, $backup);
        
        return $filename;
    }
    
    public function getSlowQueries() {
        $result = $this->executeQuery("SHOW VARIABLES LIKE 'slow_query_log_file'");
        if ($result['success'] && !empty($result['data'])) {
            $logFile = $result['data'][0]['Value'];
            if (file_exists($logFile)) {
                return file_get_contents($logFile);
            }
        }
        return "慢查询日志未开启";
    }
    
    public function getRecommendations() {
        $recommendations = [];
        $variables = $this->getServerVariables();
        $status = $this->getServerStatus();
        
        // 检查缓冲池大小
        if (isset($variables['innodb_buffer_pool_size'])) {
            $bufferSize = intval($variables['innodb_buffer_pool_size']) / 1024 / 1024;
            if ($bufferSize < 1024) {
                $recommendations[] = "建议增加 InnoDB 缓冲池大小到至少 1GB";
            }
        }
        
        // 检查查询缓存
        if (isset($variables['query_cache_size']) && intval($variables['query_cache_size']) == 0) {
            $recommendations[] = "建议启用查询缓存以提高性能";
        }
        
        // 检查临时表使用
        if (isset($status['Created_tmp_disk_tables']) && isset($status['Created_tmp_tables'])) {
            $tmpDiskRatio = floatval($status['Created_tmp_disk_tables']) / floatval($status['Created_tmp_tables']) * 100;
            if ($tmpDiskRatio > 25) {
                $recommendations[] = "临时表过多使用磁盘，建议增加 tmp_table_size 和 max_heap_table_size";
            }
        }
        
        // 检查慢查询
        if (isset($status['Slow_queries']) && intval($status['Slow_queries']) > 0) {
            $recommendations[] = "存在慢查询，请检查慢查询日志优化相关SQL";
        }
        
        return $recommendations;
    }
}
?>

4. index.php - 主入口

<?php
// index.php
require_once 'auth.php';
require_once 'db_connect.php';

$auth = new Auth();

// 处理登录
if (isset($_POST['login'])) {
    if ($auth->login($_POST['username'], $_POST['password'])) {
        header('Location: index.php');
        exit;
    } else {
        $error = "用户名或密码错误";
    }
}

// 处理登出
if (isset($_GET['logout'])) {
    $auth->logout();
    header('Location: index.php');
    exit;
}

// 检查登录状态
if (!$auth->isLoggedIn()) {
    ?>
    <!DOCTYPE html>
    <html lang="zh-CN">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>登录 - MySQL管理系统</title>
        <style>
            * { margin: 0; padding: 0; box-sizing: border-box; }
            body {
                font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
                background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
                height: 100vh;
                display: flex;
                justify-content: center;
                align-items: center;
            }
            .login-container {
                background: white;
                padding: 40px;
                border-radius: 10px;
                box-shadow: 0 10px 30px rgba(0,0,0,0.2);
                width: 350px;
            }
            .login-container h2 {
                text-align: center;
                margin-bottom: 30px;
                color: #333;
            }
            .form-group {
                margin-bottom: 20px;
            }
            .form-group label {
                display: block;
                margin-bottom: 5px;
                color: #666;
            }
            .form-group input {
                width: 100%;
                padding: 10px;
                border: 1px solid #ddd;
                border-radius: 5px;
                font-size: 14px;
            }
            button {
                width: 100%;
                padding: 10px;
                background: #667eea;
                color: white;
                border: none;
                border-radius: 5px;
                font-size: 16px;
                cursor: pointer;
                transition: background 0.3s;
            }
            button:hover {
                background: #5a67d8;
            }
            .error {
                background: #f8d7da;
                color: #721c24;
                padding: 10px;
                border-radius: 5px;
                margin-bottom: 20px;
                text-align: center;
            }
        </style>
    </head>
    <body>
        <div class="login-container">
            <h2>MySQL管理系统</h2>
            <?php if (isset($error)): ?>
                <div class="error"><?php echo $error; ?></div>
            <?php endif; ?>
            <form method="POST">
                <div class="form-group">
                    <label>用户名</label>
                    <input type="text" name="username" required>
                </div>
                <div class="form-group">
                    <label>密码</label>
                    <input type="password" name="password" required>
                </div>
                <button type="submit" name="login">登录</button>
            </form>
            <div style="text-align: center; margin-top: 20px; font-size: 12px; color: #999;">
                默认账号: admin / admin123
            </div>
        </div>
    </body>
    </html>
    <?php
    exit;
}

// 获取当前模块
$module = isset($_GET['module']) ? $_GET['module'] : 'dashboard';
?>
<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title><?php echo SITE_NAME; ?> - <?php echo VERSION; ?></title>
    <link rel="stylesheet" href="css/style.css">
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
</head>
<body>
    <div class="container">
        <div class="sidebar">
            <div class="logo">
                <h2>MySQL Manager</h2>
                <p>版本 <?php echo VERSION; ?></p>
            </div>
            <ul class="nav">
                <li><a href="?module=dashboard" class="<?php echo $module == 'dashboard' ? 'active' : ''; ?>">📊 仪表盘</a></li>
                <li><a href="?module=database" class="<?php echo $module == 'database' ? 'active' : ''; ?>">🗄️ 数据库管理</a></li>
                <li><a href="?module=query" class="<?php echo $module == 'query' ? 'active' : ''; ?>">✏️ SQL执行器</a></li>
                <li><a href="?module=optimize" class="<?php echo $module == 'optimize' ? 'active' : ''; ?>">⚡ 性能优化</a></li>
                <li><a href="?module=backup" class="<?php echo $module == 'backup' ? 'active' : ''; ?>">💾 备份恢复</a></li>
                <li><a href="?module=monitor" class="<?php echo $module == 'monitor' ? 'active' : ''; ?>">📈 系统监控</a></li>
                <li><a href="?logout=1" onclick="return confirm('确定要退出吗？')">🚪 退出登录</a></li>
            </ul>
            <div class="user-info">
                <p>当前用户: <?php echo $_SESSION['user']; ?></p>
            </div>
        </div>
        <div class="content">
            <div class="header">
                <h1><?php echo getModuleTitle($module); ?></h1>
            </div>
            <div class="main-content">
                <?php
                // 加载对应模块
                switch($module) {
                    case 'dashboard':
                        include 'modules/dashboard.php';
                        break;
                    case 'database':
                        include 'modules/database.php';
                        break;
                    case 'query':
                        include 'modules/query.php';
                        break;
                    case 'optimize':
                        include 'modules/optimize.php';
                        break;
                    case 'backup':
                        include 'modules/backup.php';
                        break;
                    case 'monitor':
                        include 'modules/monitor.php';
                        break;
                    default:
                        include 'modules/dashboard.php';
                }
                ?>
            </div>
        </div>
    </div>
    <script src="js/main.js"></script>
</body>
</html>
<?php
function getModuleTitle($module) {
    $titles = [
        'dashboard' => '系统仪表盘',
        'database' => '数据库管理',
        'query' => 'SQL执行器',
        'optimize' => '性能优化',
        'backup' => '备份与恢复',
        'monitor' => '系统监控'
    ];
    return isset($titles[$module]) ? $titles[$module] : 'MySQL管理系统';
}
?>

5. modules/dashboard.php - 仪表盘

<?php
// modules/dashboard.php
$db = new DatabaseManager();

if (!$db->isConnected()) {
    echo '<div class="error">数据库连接失败: ' . $db->getError() . '</div>';
    return;
}

$status = $db->getServerStatus();
$variables = $db->getServerVariables();
$recommendations = $db->getRecommendations();
?>

<div class="dashboard">
    <div class="stats-grid">
        <div class="stat-card">
            <div class="stat-title">运行时间</div>
            <div class="stat-value">
                <?php 
                $uptime = isset($status['Uptime']) ? $status['Uptime'] : 0;
                echo formatUptime($uptime);
                ?>
            </div>
        </div>
        <div class="stat-card">
            <div class="stat-title">数据库版本</div>
            <div class="stat-value">
                <?php echo isset($variables['version']) ? $variables['version'] : 'N/A'; ?>
            </div>
        </div>
        <div class="stat-card">
            <div class="stat-title">总查询数</div>
            <div class="stat-value">
                <?php echo isset($status['Questions']) ? number_format($status['Questions']) : 0; ?>
            </div>
        </div>
        <div class="stat-card">
            <div class="stat-title">慢查询数</div>
            <div class="stat-value <?php echo isset($status['Slow_queries']) && $status['Slow_queries'] > 0 ? 'warning' : ''; ?>">
                <?php echo isset($status['Slow_queries']) ? number_format($status['Slow_queries']) : 0; ?>
            </div>
        </div>
    </div>
    
    <div class="recommendations">
        <h3>优化建议</h3>
        <?php if (empty($recommendations)): ?>
            <div class="success">系统运行良好，暂无优化建议</div>
        <?php else: ?>
            <ul>
                <?php foreach ($recommendations as $rec): ?>
                    <li>✓ <?php echo $rec; ?></li>
                <?php endforeach; ?>
            </ul>
        <?php endif; ?>
    </div>
    
    <div class="key-metrics">
        <h3>关键指标</h3>
        <table class="data-table">
            <tr>
                <th>指标</th>
                <th>值</th>
            </tr>
            <?php
            $metrics = [
                '当前连接数' => 'Threads_connected',
                '最大并发连接数' => 'max_connections',
                'InnoDB缓冲池命中率' => 'innodb_buffer_pool_hit_rate',
                '临时表磁盘使用率' => 'tmp_disk_ratio'
            ];
            
            foreach ($metrics as $name => $key) {
                $value = 'N/A';
                if ($key == 'innodb_buffer_pool_hit_rate') {
                    if (isset($status['Innodb_buffer_pool_reads']) && isset($status['Innodb_buffer_pool_read_requests'])) {
                        $reads = intval($status['Innodb_buffer_pool_reads']);
                        $requests = intval($status['Innodb_buffer_pool_read_requests']);
                        if ($requests > 0) {
                            $value = round((1 - $reads / $requests) * 100, 2) . '%';
                        }
                    }
                } elseif ($key == 'tmp_disk_ratio') {
                    if (isset($status['Created_tmp_disk_tables']) && isset($status['Created_tmp_tables'])) {
                        $disk = intval($status['Created_tmp_disk_tables']);
                        $total = intval($status['Created_tmp_tables']);
                        if ($total > 0) {
                            $value = round($disk / $total * 100, 2) . '%';
                        }
                    }
                } elseif (isset($status[$key])) {
                    $value = number_format($status[$key]);
                } elseif (isset($variables[$key])) {
                    $value = number_format($variables[$key]);
                }
                ?>
                <tr>
                    <td><?php echo $name; ?></td>
                    <td><?php echo $value; ?></td>
                </tr>
            <?php } ?>
        </table>
    </div>
</div>

<?php
function formatUptime($seconds) {
    $days = floor($seconds / 86400);
    $hours = floor(($seconds % 86400) / 3600);
    $minutes = floor(($seconds % 3600) / 60);
    
    $parts = [];
    if ($days > 0) $parts[] = $days . '天';
    if ($hours > 0) $parts[] = $hours . '小时';
    if ($minutes > 0) $parts[] = $minutes . '分钟';
    
    return implode(' ', $parts) ?: '刚启动';
}
?>

6. modules/optimize.php - 性能优化

<?php
// modules/optimize.php
$db = new DatabaseManager();

if (!$db->isConnected()) {
    echo '<div class="error">数据库连接失败</div>';
    return;
}

$action = isset($_POST['action']) ? $_POST['action'] : '';
$database = isset($_POST['database']) ? $_POST['database'] : '';
$table = isset($_POST['table']) ? $_POST['table'] : '';

$message = '';
$messageType = '';

if ($action && $database && $table) {
    switch ($action) {
        case 'optimize':
            $result = $db->optimizeTable($database, $table);
            if ($result['success']) {
                $message = "表 {$table} 优化成功！";
                $messageType = 'success';
            } else {
                $message = "优化失败: " . $result['error'];
                $messageType = 'error';
            }
            break;
        case 'analyze':
            $result = $db->analyzeTable($database, $table);
            if ($result['success']) {
                $message = "表 {$table} 分析成功！";
                $messageType = 'success';
            } else {
                $message = "分析失败: " . $result['error'];
                $messageType = 'error';
            }
            break;
        case 'repair':
            $result = $db->repairTable($database, $table);
            if ($result['success']) {
                $message = "表 {$table} 修复成功！";
                $messageType = 'success';
            } else {
                $message = "修复失败: " . $result['error'];
                $messageType = 'error';
            }
            break;
    }
}

$databases = $db->getDatabases();
?>

<div class="optimize-panel">
    <?php if ($message): ?>
        <div class="alert alert-<?php echo $messageType; ?>"><?php echo $message; ?></div>
    <?php endif; ?>
    
    <div class="section">
        <h3>表优化工具</h3>
        <form method="POST" id="optimizeForm">
            <div class="form-group">
                <label>选择数据库</label>
                <select name="database" id="databaseSelect" required>
                    <option value="">请选择数据库</option>
                    <?php foreach ($databases as $dbName): ?>
                        <option value="<?php echo htmlspecialchars($dbName); ?>">
                            <?php echo htmlspecialchars($dbName); ?>
                        </option>
                    <?php endforeach; ?>
                </select>
            </div>
            
            <div class="form-group">
                <label>选择数据表</label>
                <select name="table" id="tableSelect" required disabled>
                    <option value="">请先选择数据库</option>
                </select>
            </div>
            
            <div class="form-group">
                <label>优化操作</label>
                <select name="action" required>
                    <option value="optimize">优化表 (OPTIMIZE)</option>
                    <option value="analyze">分析表 (ANALYZE)</option>
                    <option value="repair">修复表 (REPAIR)</option>
                </select>
            </div>
            
            <button type="submit" class="btn-primary">执行操作</button>
        </form>
    </div>
    
    <div class="section">
        <h3>表状态信息</h3>
        <div id="tableInfo" style="display:none;">
            <table class="data-table">
                <thead>
                    <tr><th>表名</th><th>引擎</th><th>行数</th><th>数据大小</th><th>索引大小</th><th>碎片率</th></tr>
                </thead>
                <tbody id="tableInfoBody">
                </tbody>
            </table>
        </div>
    </div>
    
    <div class="section">
        <h3>慢查询分析</h3>
        <div class="slow-query-info">
            <?php
            $slowLog = $db->getSlowQueries();
            echo '<pre>' . htmlspecialchars(substr($slowLog, 0, 5000)) . '</pre>';
            if (strlen($slowLog) > 5000) {
                echo '<p>...日志内容过多，已截断</p>';
            }
            ?>
        </div>
    </div>
</div>

<script>
$(document).ready(function() {
    $('#databaseSelect').change(function() {
        var database = $(this).val();
        if (database) {
            $.ajax({
                url: 'ajax/get_tables.php',
                method: 'POST',
                data: {database: database},
                dataType: 'json',
                success: function(response) {
                    if (response.success) {
                        var $tableSelect = $('#tableSelect');
                        $tableSelect.empty().prop('disabled', false);
                        $tableSelect.append('<option value="">请选择数据表</option>');
                        $.each(response.tables, function(i, table) {
                            $tableSelect.append('<option value="' + table.Name + '">' + table.Name + '</option>');
                        });
                        
                        // 显示表信息
                        $('#tableInfoBody').empty();
                        $.each(response.tables, function(i, table) {
                            var dataSize = formatBytes(table.Data_length);
                            var indexSize = formatBytes(table.Index_length);
                            var fragment = table.Data_free > 0 ? (table.Data_free / table.Data_length * 100).toFixed(2) + '%' : '0%';
                            $('#tableInfoBody').append(`
                                <tr>
                                    <td>${table.Name}</td>
                                    <td>${table.Engine}</td>
                                    <td>${table.Rows}</td>
                                    <td>${dataSize}</td>
                                    <td>${indexSize}</td>
                                    <td>${fragment}</td>
                                </tr>
                            `);
                        });
                        $('#tableInfo').show();
                    }
                }
            });
        } else {
            $('#tableSelect').empty().prop('disabled', true);
            $('#tableInfo').hide();
        }
    });
    
    function formatBytes(bytes) {
        if (bytes === 0) return '0 B';
        var k = 1024;
        var sizes = ['B', 'KB', 'MB', 'GB', 'TB'];
        var i = Math.floor(Math.log(bytes) / Math.log(k));
        return (bytes / Math.pow(k, i)).toFixed(2) + ' ' + sizes[i];
    }
});
</script>

7. css/style.css - 样式文件

/* css/style.css */
* {
    margin: 0;
    padding: 0;
    box-sizing: border-box;
}

body {
    font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
    background: #f5f5f5;
}

.container {
    display: flex;
    min-height: 100vh;
}

/* 侧边栏样式 */
.sidebar {
    width: 250px;
    background: #2c3e50;
    color: white;
    position: fixed;
    height: 100vh;
    overflow-y: auto;
}

.logo {
    padding: 20px;
    text-align: center;
    border-bottom: 1px solid #34495e;
}

.logo h2 {
    font-size: 20px;
    margin-bottom: 5px;
}

.logo p {
    font-size: 12px;
    color: #95a5a6;
}

.nav {
    list-style: none;
    padding: 20px 0;
}

.nav li a {
    display: block;
    padding: 12px 20px;
    color: #ecf0f1;
    text-decoration: none;
    transition: all 0.3s;
}

.nav li a:hover {
    background: #34495e;
    padding-left: 30px;
}

.nav li a.active {
    background: #3498db;
    color: white;
}

.user-info {
    position: absolute;
    bottom: 20px;
    left: 0;
    right: 0;
    text-align: center;
    padding: 10px;
    font-size: 12px;
    color: #95a5a6;
    border-top: 1px solid #34495e;
}

/* 内容区域 */
.content {
    margin-left: 250px;
    flex: 1;
    padding: 20px;
}

.header {
    background: white;
    padding: 20px;
    margin-bottom: 20px;
    border-radius: 5px;
    box-shadow: 0 1px 3px rgba(0,0,0,0.1);
}

.main-content {
    background: white;
    padding: 20px;
    border-radius: 5px;
    box-shadow: 0 1px 3px rgba(0,0,0,0.1);
}

/* 统计卡片 */
.stats-grid {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(250px, 1fr));
    gap: 20px;
    margin-bottom: 30px;
}

.stat-card {
    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
    color: white;
    padding: 20px;
    border-radius: 10px;
    box-shadow: 0 5px 15px rgba(0,0,0,0.1);
}

.stat-title {
    font-size: 14px;
    opacity: 0.9;
    margin-bottom: 10px;
}

.stat-value {
    font-size: 28px;
    font-weight: bold;
}

.stat-value.warning {
    color: #ff6b6b;
}

/* 数据表格 */
.data-table {
    width: 100%;
    border-collapse: collapse;
    margin-top: 20px;
}

.data-table th,
.data-table td {
    padding: 12px;
    text-align: left;
    border-bottom: 1px solid #ddd;
}

.data-table th {
    background: #f8f9fa;
    font-weight: 600;
    color: #333;
}

.data-table tr:hover {
    background: #f5f5f5;
}

/* 表单样式 */
.form-group {
    margin-bottom: 20px;
}

.form-group label {
    display: block;
    margin-bottom: 5px;
    color: #333;
    font-weight: 500;
}

.form-group input,
.form-group select,
.form-group textarea {
    width: 100%;
    padding: 10px;
    border: 1px solid #ddd;
    border-radius: 5px;
    font-size: 14px;
    font-family: inherit;
}

.form-group textarea {
    font-family: 'Courier New', monospace;
    resize: vertical;
}

/* 按钮样式 */
.btn-primary,
.btn-secondary,
.btn-danger {
    padding: 10px 20px;
    border: none;
    border-radius: 5px;
    cursor: pointer;
    font-size: 14px;
    transition: all 0.3s;
}

.btn-primary {
    background: #3498db;
    color: white;
}

.btn-primary:hover {
    background: #2980b9;
}

.btn-secondary {
    background: #95a5a6;
    color: white;
}

.btn-secondary:hover {
    background: #7f8c8d;
}

.btn-danger {
    background: #e74c3c;
    color: white;
}

.btn-danger:hover {
    background: #c0392b;
}

/* 提示消息 */
.alert {
    padding: 15px;
    margin-bottom: 20px;
    border-radius: 5px;
}

.alert-success {
    background: #d4edda;
    color: #155724;
    border: 1px solid #c3e6cb;
}

.alert-error {
    background: #f8d7da;
    color: #721c24;
    border: 1px solid #f5c6cb;
}

/* SQL编辑器 */
.sql-editor {
    font-family: 'Courier New', monospace;
    font-size: 14px;
    background: #f8f9fa;
    border: 1px solid #ddd;
    padding: 15px;
    border-radius: 5px;
    min-height: 300px;
}

/* 响应式设计 */
@media (max-width: 768px) {
    .sidebar {
        width: 200px;
    }
    .content {
        margin-left: 200px;
    }
    .stats-grid {
        grid-template-columns: 1fr;
    }
}

/* 加载动画 */
.loading {
    display: inline-block;
    width: 20px;
    height: 20px;
    border: 3px solid #f3f3f3;
    border-top: 3px solid #3498db;
    border-radius: 50%;
    animation: spin 1s linear infinite;
}

@keyframes spin {
    0% { transform: rotate(0deg); }
    100% { transform: rotate(360deg); }
}

安装使用说明

1. 环境要求

• PHP 7.4+
• MySQL 5.7+
• Web服务器（Apache/Nginx）

2. 安装步骤

# 1. 创建项目目录
mkdir mysql_manager
cd mysql_manager

# 2. 将所有文件复制到目录

# 3. 设置权限
chmod 755 -R .
chmod 777 backups/

# 4. 修改配置文件 config.php
# 设置正确的数据库连接信息

# 5. 访问 index.php

3. 安全建议

1. 修改默认管理员密码
2. 启用 HTTPS
3. 限制访问IP
4. 定期备份数据库
5. 监控系统日志

功能特性

1. 数据库管理 - 查看、创建、删除数据库和表
2. SQL执行器 - 执行任意SQL语句，支持语法高亮
3. 性能优化 - 表优化、分析、修复，慢查询分析
4. 备份恢复 - 数据库备份和恢复功能
5. 系统监控 - 实时监控服务器状态和性能指标
6. 优化建议 - 自动分析并提供优化建议

这个系统提供了完整的MySQL管理功能，可以根据实际需求进行扩展和优化。