How to restore a MariaDB database from a .sql backup file

You will restore a MariaDB database by loading a compressed or uncompressed .sql file using the mysql client utility. These steps target MariaDB 11.4 running on Ubuntu 24.04 or any Linux distribution with the standard client installed. You must have the backup file available and access to the database server via the command line.

Prerequisites

• Linux operating system (Ubuntu 24.04, AlmaLinux 9, or similar).
• MariaDB 11.4 installed and running on the target server.
• A .sql backup file located in a directory you can read.
• Root or a user with the GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' privilege.
• Enough disk space to accommodate the uncompressed backup data.

Step 1: Verify the backup file

Before importing, confirm the file exists and check its size to ensure it is not corrupted or empty. Use the ls command to list the file details and the file command to inspect the format.

ls -lh /path/to/backup/mydb_backup.sql
file /path/to/backup/mydb_backup.sql

You should see output similar to the following, confirming the file is a standard SQL dump:

-rw-r--r-- 1 root root 45K Dec 20 10:00 /path/to/backup/mydb_backup.sql
mydb_backup.sql: ASCII text

If the file is compressed with gzip, the output will show gzip compressed data. You must decompress it before importing.

Step 2: Decompress the backup file (if necessary)

If the file extension is .sql.gz or .sql.bz2, you must decompress it to a standard .sql file before loading. Use the gunzip command for gzip files or bunzip2 for bzip2 files. Run the command in the directory containing the backup.

gunzip /path/to/backup/mydb_backup.sql.gz

The command will produce output indicating the file was successfully uncompressed:

gunzip: /path/to/backup/mydb_backup.sql.gz: writing /path/to/backup/mydb_backup.sql

Verify the uncompressed file exists and is readable:

ls -lh /path/to/backup/mydb_backup.sql

Step 3: Create the database (optional)

Decide whether the backup file creates the database structure or if the database already exists. If the .sql file does not contain a CREATE DATABASE statement, you must create the database manually first. Connect to the MariaDB server using the mysql client and execute the creation command.

mysql -u root -p

Enter your root password when prompted. Inside the mysql prompt, run:

CREATE DATABASE IF NOT EXISTS mydb;

Exit the mysql client by typing exit. The output will be empty if the database already exists or was created successfully.

Step 4: Import the backup file

Use the mysql command to load the .sql file into the server. Specify the database name you created or the one defined in the backup file. Redirect the SQL content from the file into the client using input redirection.

mysql -u root -p mydb < /path/to/backup/mydb_backup.sql

Enter your password when prompted. The command will process the file and display progress messages. You will see lines like Query OK, 10 rows affected or Showing rows as tables are created and data is inserted. The process completes when the output ends with 0 rows affected or a message indicating the file is finished.

Verify the installation

Confirm the restoration was successful by checking the database size and row counts. Connect to the MariaDB server and run a SELECT query against the information_schema to count rows in the restored tables. This proves the data loaded correctly.

mysql -u root -p mydb -e "SELECT COUNT(*) FROM mydb.users;"

You should see output similar to the following, showing the number of rows restored:

+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

Also verify the database exists in the list of databases:

mysql -u root -p -e "SHOW DATABASES LIKE 'mydb';"

The output should list mydb in the database name column.

Troubleshooting

If the import fails, check the error messages displayed in the terminal. Common issues include permission errors, character set mismatches, and locked tables.

Error: Access denied for user 'root'@'localhost'
This occurs when the mysql client cannot connect to the server. Ensure the root user exists and has a password set. If you are using a socket file connection, verify the SOCKET variable in the mysql client configuration matches the server socket path. Try running mysql -u root -p first to test connectivity.

Error: Table 'mydb.table_name' doesn't exist
This happens if you are trying to import a backup that expects a database to exist but you did not create it first. Ensure you ran the CREATE DATABASE command in Step 3 before running the import command. If the backup file creates the database, ensure the USE statement inside the file is ignored or the file is run against a fresh server.

Error: Got error 'Error: Can't create/write to file' (errno 28)
This indicates the disk is full or the tmpdir is full. Check disk space with df -h and clear temporary files. Increase the tmpdir size in the MariaDB configuration if necessary.

Error: Duplicate entry for key 'PRIMARY'
This means the database already contains data with the same primary keys. You must truncate the tables before importing or use the --ignore-db-error flag if you want to skip errors. To truncate tables, run TRUNCATE TABLE mydb.table_name; inside the mysql client before importing.

Error: Incorrect table definition; check if CREATE USES PARSER
This error often appears when the backup file uses a different MariaDB version than the server. Ensure the backup file is compatible with MariaDB 11.4. If the error persists, check the my.cnf file for parser settings and ensure the innodb_file_format matches the backup source.

If the import stops prematurely, check the error.log file located in the MariaDB data directory. The path is typically /var/log/mysql/error.log or /var/log/mariadb/error.log. Look for the specific line where the import failed and adjust the command or configuration accordingly.

To resume an interrupted import, delete the partially loaded tables or drop the database and start over. Use mysql -u root -p mydb -e "DROP DATABASE mydb;" to reset the environment and re-run the import command.

Always test the import process on a non-production server first to ensure the backup file is valid and the server has sufficient resources.