Администрирование MySQL с помощью PHP: обзор методов

Расширенные примеры программного кода

Автоматическое резервное копирование всех баз данных с разделением по файлам

Скрипт получает список всех баз данных (исключая системные) и создаёт отдельный SQL-файл для каждой. Логирует результат.

$dbUser = 'root';
$dbPass = 'password';
$backupDir = '/backups/mysql/' . date('Y-m-d');
if (!is_dir($backupDir)) mkdir($backupDir, 0755, true);

$pdo = new PDO("mysql:host=localhost", $dbUser, $dbPass);
$stmt = $pdo->query("SHOW DATABASES WHERE `Database` NOT IN ('information_schema','performance_schema','mysql','sys')");
$databases = $stmt->fetchAll(PDO::FETCH_COLUMN);

foreach ($databases as $db) {
    $file = "$backupDir/{$db}.sql";
    $command = "mysqldump --defaults-extra-file=/root/.my.cnf --databases $db > $file 2>&1";
    exec($command, $output, $returnVar);
    if ($returnVar === 0) {
        echo "Успешно: $db -> $file\n";
    } else {
        echo "Ошибка при бэкапе $db: " . implode('\n', $output) . "\n";
    }
}

Успешно: mydb -> /backups/mysql/2025-03-22/mydb.sql
Успешно: test -> /backups/mysql/2025-03-22/test.sql

Пояснение: используется файл ~/.my.cnf для безопасного хранения пароля. Команда mysqldump выполняется для каждой базы данных. Вывод ошибок перенаправляется в переменную.

Мониторинг состояния сервера MySQL и отправка уведомления

Скрипт подключается к MySQL, получает основные метрики (время работы, количество подключений, размер баз данных) и отправляет email, если какой-либо показатель превышает порог.

$pdo = new PDO('mysql:host=localhost', 'monitor', 'password');
$uptime = $pdo->query("SHOW STATUS LIKE 'Uptime'")->fetch(PDO::FETCH_ASSOC)['Value'];
$connections = $pdo->query("SHOW STATUS LIKE 'Threads_connected'")->fetch(PDO::FETCH_ASSOC)['Value'];
$maxConnections = 150;
if ($connections > $maxConnections) {
    mail('admin@example.com', 'MySQL: много подключений', "Текущее подключений: $connections (максимум $maxConnections)");
}
$sizeQuery = $pdo->query("SELECT SUM(ROUND(((data_length+index_length)/1024/1024),2)) AS size_mb FROM information_schema.TABLES");
$sizeMb = $sizeQuery->fetch(PDO::FETCH_ASSOC)['size_mb'];
echo "Uptime: $uptime seconds, Connections: $connections, Total size: $sizeMb MB";

Uptime: 1234567 seconds, Connections: 42, Total size: 234.56 MB

Пояснение: скрипт предназначен для запуска по cron. Использует системную функцию mail (требуется настройка MTA).

Создание пользователя MySQL с ограниченными правами через PHP

$pdo = new PDO('mysql:host=localhost', 'root', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$username = 'newuser';
$password = 'secret';
$host = '192.168.1.%';
$database = 'mydb';
$pdo->exec("CREATE USER IF NOT EXISTS '$username'@'$host' IDENTIFIED BY '$password'");
$pdo->exec("GRANT SELECT, INSERT, UPDATE, DELETE ON `$database`.* TO '$username'@'$host'");
$pdo->exec("FLUSH PRIVILEGES");
echo "Пользователь $username создан и получил права на базу $database";

Пользователь newuser создан и получил права на базу mydb

Пояснение: имя и пароль экранируются? В данном примере используется прямая подстановка, что опасно для SQL-инъекций, если данные приходят извне. На практике следует экранировать идентификаторы и использовать подготовленные запросы, но для CREATE USER это сложнее.

Массовая вставка данных с использованием prepared statements

$pdo = new PDO('mysql:host=localhost;dbname=mydb', 'root', 'password');
$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (:name, :email)');
$users = [
    ['name' => 'Alice', 'email' => 'alice@example.com'],
    ['name' => 'Bob', 'email' => 'bob@example.com'],
    ['name' => 'Charlie', 'email' => 'charlie@example.com'],
];
foreach ($users as $user) {
    $stmt->execute($user);
}
echo "Вставлено " . count($users) . " записей";

Вставлено 3 записи

Для увеличения производительности можно объединить вставки в один запрос: INSERT INTO users (name, email) VALUES (?, ?), (?, ?), ... и подготовить строку динамически.

Работа с транзакциями: выполнение нескольких запросов с откатом при ошибке

$pdo->beginTransaction();
try {
    $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    $pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
    $pdo->commit();
    echo "Перевод выполнен успешно";
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Ошибка, транзакция откачена: " . $e->getMessage();
}

Перевод выполнен успешно

Импорт большого SQL-файла с отображением прогресса

$file = '/path/to/large_dump.sql';
$handle = fopen($file, 'r');
$totalSize = filesize($file);
$processed = 0;
$buffer = '';
$pdo = new PDO('mysql:host=localhost;dbname=target', 'root', 'password');
while (!feof($handle)) {
    $line = fgets($handle);
    $buffer .= $line;
    $processed += strlen($line);
    if (strpos($line, ';') !== false) {
        $pdo->exec($buffer);
        $buffer = '';
        $percent = round(($processed / $totalSize) * 100, 2);
        echo "\rОбработано: $percent%";
    }
}
fclose($handle);
echo "\nИмпорт завершен";

Обработано: 100%
Импорт завершен

Пояснение: скрипт читает файл построчно, накапливает запросы до точки с запятой и выполняет. Для очень больших файлов может потребоваться увеличение времени выполнения и памяти.

Проверка состояния репликации MySQL (SHOW SLAVE STATUS)

$pdo = new PDO('mysql:host=localhost', 'repl_user', 'password');
$stmt = $pdo->query("SHOW SLAVE STATUS");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
if ($row) {
    $ioRunning = $row['Slave_IO_Running'];
    $sqlRunning = $row['Slave_SQL_Running'];
    $lag = $row['Seconds_Behind_Master'];
    if ($ioRunning === 'Yes' && $sqlRunning === 'Yes') {
        echo "Репликация работает. Отставание: $lag секунд";
    } else {
        echo "Проблема: IO=$ioRunning, SQL=$sqlRunning";
    }
} else {
    echo "Не удалось получить статус репликации";
}

Репликация работает. Отставание: 5 секунд

Шифрованное соединение с MySQL через SSL в PHP (PDO)

$options = [
    PDO::MYSQL_ATTR_SSL_CA => '/etc/mysql/ssl/ca-cert.pem',
    PDO::MYSQL_ATTR_SSL_CERT => '/etc/mysql/ssl/client-cert.pem',
    PDO::MYSQL_ATTR_SSL_KEY => '/etc/mysql/ssl/client-key.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => false, // для тестов
];
$pdo = new PDO('mysql:host=db.example.com;port=3306;dbname=secure_db', 'user', 'password', $options);
echo "Подключение по SSL установлено";

Подключение по SSL установлено

Пояснение: сертификаты должны быть предварительно созданы для клиента. Опция MYSQL_ATTR_SSL_VERIFY_SERVER_CERT отключает проверку сертификата сервера (не рекомендуется для продакшена).