MySQL and PostgreSQL Database Backups and Data Restoration

This guide explains how MySQL/MariaDB and PostgreSQL databases are backed up on MasterDC Managed Servers, where the backups are stored, and how to restore an individual table or an entire database.

How Backups Work

Backups are created by a script run from cron, typically at 1:30 AM. The exact time may vary in individual cases and can also be adjusted upon request. These are logical backups, meaning that the data is exported to SQL files rather than copied directly from the database’s binary data files.

The basic principles are the same for both database types:

• Backups are created on a per-table basis – each table has its own .sql file. This makes it possible to restore a single table without affecting the rest of the database.
• Each day’s output is packaged into a ZIP archive named using the YYYY-MM-DD.zip format.
• Backups for the last 7 days are retained on the server by default. The retention period can be adjusted upon request. Older archives are deleted automatically.
• The archives are stored on the same server as the database itself. These archives are backed up together with other system data as part of the Managed Server backup. The database data itself (/var/lib/mysql, /var/lib/postgresql) is not included in the same Managed Server backup.

MySQL / MariaDB Backups

Mysqldump

Backups are stored in /backup/mysql/.

All databases on the server are backed up except for the system database information_schema. Each table is exported using mysqldump, including the DROP TABLE IF EXISTS statement, the table definition (CREATE TABLE), and the data as INSERT statements with explicit column names.

Archive structure:

Mydumper

On some servers, we use mydumper instead of mysqldump. It performs backups in parallel using multiple threads and is significantly faster for large databases. Unlike the mysqldump variant, it also includes stored procedures, functions, triggers, and scheduled events.

Backups are stored in /backup/mydumper/localhost/, in a subdirectory named according to the database server and backup date. These backups are not stored as ZIP archives. Each backup is kept in a separate directory, with individual files compressed using Zstandard (.zst).

The metadata file contains the backup start and end times. For servers using replication, it also includes the binary log position.

Backups are performed without locking tables to avoid blocking the running application. As a result, they do not provide a guaranteed consistent point-in-time snapshot across all tables. Some databases or tables may also be intentionally excluded from the backup, typically temporary or cache tables. We can provide the list of exclusions configured on your server upon request.

PostgreSQL Backups

Backups are stored in /backup/postgre/.

All databases are backed up except for the template databases (template0, template1). Depending on the server configuration, one of the two variants below is used. You can identify the variant by checking the contents of the archive.

Variant A – Schema and Data

Two files are created for each table:

• table_name.schema.sql – table structure (CREATE TABLE, indexes, keys, sequences);
• table_name.sql – table data only, in COPY format.

Variant B – Data Only

A single data file is created for each table:

What Is Not Included in the Backup

Because backups are created on a per-table basis, the archive does not include objects that are not directly associated with individual tables:

• views, stored procedures and functions, custom data types, and extensions;
• users, roles, and their permissions;
• PostgreSQL tables located outside the default public schema;
• database server configuration.

Restoring Data in MySQL / MariaDB

Mysqldump

1. Extract the archive for the required date into a temporary directory:
   
   mkdir -p /root/restore
unzip /backup/mysql/2026-09-07.zip -d /root/restore
2. Check which tables are available:
   
   ls /root/restore/2026-09-07/database_name/
3. Restore a single table:
   
   mysql database_name < /root/restore/2026-09-07/database_name/table_name.sql
4. Restore an entire database (if the database does not exist, create it first; use the same character set and collation as the original database):
   
   mysql -e "CREATE DATABASE IF NOT EXISTS database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;"
5. Then import all tables in a single session:
   
   cd /root/restore/2026-09-07/database_name
( echo "SET FOREIGN_KEY_CHECKS=0;"; cat *.sql ) | mysql database_name
   Disabling foreign key checks ensures that the order in which the tables are imported does not matter. Once the import is complete, foreign key checks automatically return to their original state.
6. Check the result:
   
   mysql -e "SELECT COUNT(*) FROM database_name.table_name;"
7. Once the restore has completed successfully, remove the extracted data to avoid using unnecessary disk space:
   
   rm -rf /root/restore/2026-09-07

Mydumper (Myloader)

Backups created with mydumper are restored using myloader, which is part of the same package. There is no need to extract the backup directory. Myloader reads the compressed files directly.

1. Select the backup you want to restore and make sure it is not marked as failed:
   
   ls -l /backup/mydumper/localhost/
cat /backup/mydumper/localhost/20260907_00/metadata
2. Restore the entire backup (all databases included in the backup):
   
   myloader \
  --user=root \
  --directory=/backup/mydumper/localhost/20260907_00 \
  --threads=4 \
  --overwrite-tables
3. Restore a single database under a different name (useful for verifying the backup without affecting production data):
   
   mysql -e "CREATE DATABASE database_name_test;"
myloader \
  --user=root \
  --directory=/backup/mydumper/localhost/20260907_00 \
  --source-db=database_name \
  --database=database_name_test \
  --threads=4
4. Restore a single table:
   
   myloader \
  --user=root \
  --directory=/backup/mydumper/localhost/20260907_00 \
  --source-db=database_name \
  --tables-list=table_name \
  --overwrite-tables
5. Manual restore without myloader. If you only need to inspect the data or do not have myloader available, you can decompress the files using zstd (the zstd package):

   cd /backup/mydumper/localhost/20260907_00
zstdcat database_name.table_name-schema.sql.zst | mysql database_name
zstdcat database_name.table_name.*.sql.zst | mysql database_name
   The data for a single table is split across multiple numbered files. The *.sql.zst pattern imports all of them in the correct order.
6. Check the result:
   
   mysql -e "SELECT COUNT(*) FROM database_name.table_name;"

Restoring Data in PostgreSQL

Run PostgreSQL-related commands as the postgres user (su - postgres), or use the -U postgres option. Commands such as mkdir and chown should be run as root.

On some servers, PostgreSQL runs on a non-standard port (for example, 5433). To check which port your server uses, run:

If it differs from the default port 5432, add the --host localhost --port 5433 options to the commands below.

1. Extract the archive for the required date:
   
   mkdir -p /root/restore
unzip /backup/postgre/2026-09-07.zip -d /root/restore
chown -R postgres:postgres /root/restore/2026-09-07
2. Restore a single table. Import the schema first, followed by the data:
   
   psql -U postgres -d database_name -f /root/restore/2026-09-07/database_name/table_name.schema.sql
psql -U postgres -d database_name -f /root/restore/2026-09-07/database_name/table_name.sql
   If the table already exists in the database and you only want to restore its contents, skip the .schema.sql step and empty the table before importing the data:
   
   psql -U postgres -d database_name -c "TRUNCATE TABLE table_name;"
3. Restore an entire database. If the database does not exist, create it first:
   
   createdb -U postgres database_name
   Import the schemas for all tables:
   
   cd /root/restore/2026-09-07/database_name
for f in *.schema.sql; do psql -U postgres -q -d database_name -f "$f"; done
   Then import the data. Because of foreign key constraints, all data needs to be imported in a single session with triggers temporarily disabled:
   
   ( echo "SET session_replication_role = replica;"
  for f in $(ls *.sql | grep -v '\.schema\.sql$'); do echo "\\i $f"; done
) | psql -U postgres -d database_name
   The grep -v condition is important. Otherwise, the *.sql pattern would also include the schema files, which have already been imported in this step.
4. Check the sequence values. Sequences associated with serial columns are restored together with the data, but manually created sequences may need to be adjusted:
   
   psql -U postgres -d database_name -c "SELECT setval('sequence_name', (SELECT MAX(id) FROM table_name));"
5. Update the query planner statistics:
   
   psql -U postgres -d database_name -c "ANALYZE;"
6. Once the restore has completed successfully, remove the extracted data:
   
   rm -rf /root/restore/2026-09-07

Recommendations

• Test the restore procedure in advance. A backup that has never been restored has not been verified. The simplest test is to restore an archive to a test database and compare the record counts.
• Create a current backup before making changes to production. The latest automatic backup may be up to 24 hours old.
• Do not keep your backups in only one location. The archives are stored on the same server as the database. If the server is lost, the local archives will be lost as well. We recommend downloading them regularly or using our backup space service.
• If you need a backup older than 7 days, contact our technical support team.