Losing a database means losing orders, users, and content in seconds. For a WordPress site owner, backup is not "nice to have," it's mandatory. But manually logging into phpMyAdmin every day wastes time, and when you need it, the backup simply isn't there.

A PHP script solves the problem: you give it database access, it exports structure and data to an SQL file. In five minutes of setup you get a repeatable, programmable backup that you won't forget to make.

Next: ready-made scripts for MySQL backup and restore in PHP. We'll break down how they work, where to get credentials, and how to run auto-backup on schedule so your database is never left unprotected.

💡 Quick overview:

• Connect to MySQL via mysqli, get table list and export structure
• Form SQL dump: CREATE TABLE plus INSERT for each table, save to file
• Restore: read dump and execute queries in one batch via mysqli_multi_query
• Automation via Cron (Linux) or Task Scheduler (Windows) runs the script without your involvement
• Compression to .gz and rotation of old copies save space and keep backup history

Step 1: Credentials and environment, what you need before running

Before writing code, gather three things: MySQL credentials, a directory for storing backups, and an understanding of where the script will run.

To connect to the database you need host (usually localhost or 127.0.0.1), username, password, and database name. On a WordPress site this data lives in wp-config.php: constants DB_HOST (server), DB_USER (login), DB_PASSWORD (password), and DB_NAME (database name).

Create the backup directory outside the site's public folder. Put the backups/ folder one level above public_html, so SQL files won't be accessible from the browser. Write permissions (chmod 755 or chmod 775) are mandatory: without them fopen() and fwrite() will fail with an error.

The PHP script runs from command line: php backup.php. Web execution (via browser) is also possible, but command line is safer, no one will guess the script URL and dump your database.

Step 2: PHP script for database backup

The script makes three passes: gets table list, for each one exports structure (SHOW CREATE TABLE), then data (SELECT *). The result is assembled into one string and written to a .sql file. Save the code as backup.php and run from terminal.

1
<?php
2
 
3
$connection = mysqli_connect('localhost', 'user', 'pass', 'my_database');
4
 
5
if (!$connection) {
6
    die('Ошибка подключения: ' . mysqli_connect_error());
7
}
8
 
9
$tables = [];
10
$result = mysqli_query($connection, "SHOW TABLES");
11
while ($row = mysqli_fetch_row($result)) {
12
    $tables[] = $row[0];
13
}
14
 
15
$dump = '';
16
foreach ($tables as $table) {
17
    $dump .= 'DROP TABLE IF EXISTS ' . $table . ";\n";
18
 
19
    $create = mysqli_query($connection, "SHOW CREATE TABLE " . $table);
20
    $row = mysqli_fetch_row($create);
21
    $dump .= $row[1] . ";\n\n";
22
 
23
    $data = mysqli_query($connection, "SELECT * FROM " . $table);
24
    $num_fields = mysqli_num_fields($data);
25
 
26
    while ($row = mysqli_fetch_row($data)) {
27
        $dump .= "INSERT INTO " . $table . " VALUES(";
28
        for ($j = 0; $j < $num_fields; $j++) {
29
            if (isset($row[$j])) {
30
                $row[$j] = mysqli_real_escape_string($connection, $row[$j]);
31
                $dump .= '"' . $row[$j] . '"';
32
            } else {
33
                $dump .= 'NULL';
34
            }
35
            if ($j < $num_fields - 1) {
36
                $dump .= ',';
37
            }
38
        }
39
        $dump .= ");\n";
40
    }
41
    $dump .= "\n\n";
42
}
43
 
44
$filename = 'backups/dump_' . date('Y-m-d_H-i-s') . '.sql';
45
$handle = fopen($filename, 'w+');
46
fwrite($handle, $dump);
47
fclose($handle);
48
 
49
echo "Бэкап сохранён: " . $filename . "\n";
50
 
51
?>

What happens here line by line. mysqli_connect() opens a connection, if access is denied the script dies with a clear message rather than silently continuing. The SHOW TABLES loop collects table names into an array.

For each table SHOW CREATE TABLE returns exactly one row with the second field being the full DDL query. We add DROP TABLE IF EXISTS before it so the dump can be applied to a clean database without conflicts.

SELECT * reads all rows. Function mysqli_real_escape_string() escapes quotes and special characters inside values, unlike addslashes(), it respects connection encoding and doesn't miss injections through multibyte sequences. NULL values are inserted as NULL, not as empty string.

The filename contains run date and time, convenient for rotation. File opens in w+ mode (create or overwrite) and receives the entire dump in one fwrite() operation.

Step 3: Script for database restore from SQL dump

The reverse script reads the .sql file and executes queries against the database. In practice dumps often weigh tens of megabytes, so file_get_contents() and explode by ; is not the best option for production. But for learning purposes and small databases the approach works.

Save as restore.php. Before running make a full backup of current database, restore overwrites tables.

1
<?php
2
 
3
$connection = mysqli_connect('localhost', 'root', '', 'test');
4
 
5
if (!$connection) {
6
    die('Ошибка подключения: ' . mysqli_connect_error());
7
}
8
 
9
$filename = 'backups/dump_2026-06-14_12-00-00.sql';
10
 
11
if (!file_exists($filename)) {
12
    die('Файл дампа не найден: ' . $filename);
13
}
14
 
15
$contents = file_get_contents($filename);
16
 
17
if (mysqli_multi_query($connection, $contents)) {
18
    do {
19
        if ($result = mysqli_store_result($connection)) {
20
            mysqli_free_result($result);
21
        }
22
    } while (mysqli_next_result($connection));
23
    echo "База восстановлена из: " . $filename . "\n";
24
} else {
25
    echo "Ошибка восстановления: " . mysqli_error($connection) . "\n";
26
}
27
 
28
mysqli_close($connection);
29
 
30
?>

The key difference from line-by-line mysqli_query() is mysqli_multi_query(). It accepts a string with multiple SQL queries and executes them sequentially in one call. The do...while loop with mysqli_next_result() is mandatory: without it subsequent queries won't execute and the connection will hang.

mysqli_store_result() fetches the result of the current query, and mysqli_free_result() frees memory. If the query is INSERT or CREATE TABLE, the result will be false, and that's normal: the script just moves to the next one.

Notice: the script doesn't do DROP DATABASE and doesn't recreate the database itself. It expects the test database already exists. For safety the first line of the dump can hold CREATE DATABASE IF NOT EXISTS.

Step 4: Backup automation via Cron and compression

Manual execution works for one-off tasks. But on a live site backup should happen without you, every night while you sleep.

On a Linux server add a task to Cron: crontab -e and line 0 3 * * * php /path/to/backup.php runs the script every day at 3 AM. On Windows, Task Scheduler with action "start a program" php.exe and argument-path to the script.

Two improvements that turn a learning script into production-ready:

Compression. After writing the .sql file add exec("gzip " . $filename);, dump size will shrink 5-10 times. For restore gunzip unpacks the file back.

Rotation. Keep backups for the last 7 days, delete the rest. Simple option, add cleanup of files older than N days to the script start:

1
$retention_days = 7;
2
$backup_dir = 'backups/';
3
foreach (glob($backup_dir . '*.sql.gz') as $file) {
4
    if (filemtime($file) < time() - $retention_days * 86400) {
5
        unlink($file);
6
    }
7
}

As a result you have an autonomous system: script runs on schedule, creates compressed backup with date in filename and cleans old stuff. Straight hands and 20 minutes of setup replace a paid plugin.

The video above shows the full auto-backup cycle: from config file to checking compressed .sql.gz in the backups/ folder. The approach is exactly the same as described in this article, with additional error checks and logging.

⁉️🤔 FAQ

Is it safe to store MySQL password in a PHP script?

Storing credentials in plain text inside a script is acceptable only if the file lives outside the site's public directory and is inaccessible from the browser. Better to move credentials to a separate config.php outside public_html and include via require. Even more reliable, pass the password through environment variable: getenv('DB_PASS').

Can I back up only specific tables?

Yes. Replace SHOW TABLES with an explicit array of names: $tables = ['wp_posts', 'wp_postmeta', 'wp_options'];. Or exclude unimportant tables via if (!in_array($table, ['wp_litespeed_cache', 'wp_actionscheduler_logs'])). In practice cache and log tables take up a significant part of database size and are not needed for restore.

How much space does a backup of an average WordPress site database take?

Uncompressed SQL dump of a database with 500 posts and 15 plugins weighs 15-40 MB. After gzip, 2-8 MB. Over a month of daily backups with compression accumulates 100-300 MB, for a server with 20 GB disk this is negligible. Rotation for 7 days keeps occupied space within 50-100 MB.

What to do if the backup file is corrupted?

Before restore check integrity: gunzip -t dump.sql.gz for compressed, head -n 20 dump.sql for uncompressed, make sure the file starts with SQL comments, not binary garbage. Don't restore a corrupted dump, you'll lose data. That's exactly why you keep at least 2-3 latest copies.

How does this approach differ from WordPress backup plugins?

A plugin like UpdraftPlus gives a web interface, incremental backups and cloud upload "out of the box." A PHP script is full control over the process without extra code in admin. For a developer who already works with the server, the script is faster and more transparent. For a client without console access, a plugin is more convenient.

Manual script, mysqldump or plugin, what to choose for your task

A PHP backup script gives the main thing, understanding of exactly what happens to your database during backup. You don't trust a black box, you control every query.

If the site is in production and speed matters, use mysqldump. One command mysqldump -u user -p db_name | gzip > dump.sql.gz does the same thing but times faster, because it works at MySQL engine level, not row-by-row SELECT. A PHP script is for learning purposes, custom scenarios (backing up not the whole database but results of a specific query), and environments where mysqldump is unavailable.

If the site is on WordPress and you don't want to dive into console, any backup plugin will close the task without code. UpdraftPlus, BackWPup, Duplicator grab both database and site files.

Start with a manual script, understand how an SQL dump is formed. Then set up Cron and compression. Last step, verify restore on a test server: a backup without restore verification is not considered done. What tool for database backup do you use? Write in the comments.