WNMAX 欲望文字 WNMAX 欲望文字
注册
WNMAX NETWORK
博客 欲望文字 那年花开 开发笔记 日志

PHP 开发一个轻量级 MySQL 在线管理优化系统

  • Comment：6206
  • Views：444057
  • Like：1484
  • 点赞

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管理功能，可以根据实际需求进行扩展和优化。

已有 6206 条评论

    1. AvaJohnson AvaJohnson

      The inclusion of a monitor panel is smart. Real-time database monitoring helps catch issues before they become problems.

    2. 王浩然 王浩然

      备份恢复模块一定要做好，数据库管理工具最怕操作失误导致数据丢失。期待看到完善的备份机制。

    3. MiaWilson MiaWilson

      Great project idea! Many developers need a lightweight alternative to heavy tools. The structure looks promising.

    4. 陈思琪 陈思琪

      优化系统这个定位很精准，很多小团队确实需要一个简单好用的MySQL管理工具。期待后续代码更新！

    5. OliverMiller OliverMiller

      The modular approach is excellent. Being able to extend or replace individual modules without touching the core is a great design pattern.

  1. 1
  2. ...
  3. 3
  4. 4
  5. 5
  6. 6
  7. 7