MySQL and MariaDB Database Backup (Export) and Restore (Import) Guide

💡 What Will You Learn in This Guide?

In this guide, you will learn step by step how to backup (export) and restore (import) MySQL and MariaDB databases.
You'll also discover how to improve performance by compressing large .sql files, backing up only selected tables, and safely moving user privileges.

🧠 Basic Concepts

• mysqldump: Tool that backs up the database to the .sql file (logical “dump”).
• mysql: Client used to restore the backed up file.
• Import / Export: Data import or export operations.
• GRANT: SQL commands that define user privileges.

---

⚙️ Requirements

Before you start, have:

1. MySQL or MariaDB must be installed on the server.
2. You must have a user account with sufficient permissions.
3. You must have Terminal (SSH) access.
4. A sample database to be backed up should be ready.

---

📤 1. Database Backup (Export)

To export the full database, run this command:

mysqldump -u kullanici_adi -p veritabani_adi > yedek.sql

🧾 This command saves the structure and content of the database named database_name in the backup.sql file.

To check if the file is valid after processing:

head -n 5 yedek.sql

If the -- MySQL dump statement appears in the first lines, the backup is successful.

📥 2. Database Restore (Import)

Create the target database before restoring the backup:

CREATE DATABASE yeni_veritabani;

Then in terminal:

mysql -u kullanici_adi -p yeni_veritabani < yedek.sql

✅ This command loads the backup into the database you just created.

🧩 3. Transferring Specific Tables

If you want to move only specific tables, specify the table names:

mysqldump -u root -p veritabani_adi tablo1 tablo2 > secili_tablolar.sql

Import:

mysql -u root -p hedef_vt < secili_tablolar.sql

💡 In large databases, moving only necessary tables increases performance.

👥 4. Transferring User Accounts and Authorizations

User accounts are not included in normal backups. These are kept in the mysql system database.

🔹 Transport with GRANT Commands (Safest Method)

mysql -B -N -u root -p -e "SELECT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') FROM mysql.user WHERE user NOT IN ('mysql.sys','root')" \
| mysql -B -N -u root -p \
| sed 's/$/;/' > kullanici_yetkileri.sql

On the new server:

mysql -u root -p < kullanici_yetkileri.sql
FLUSH PRIVILEGES;

✅ FLUSH PRIVILEGES; The command immediately enables new user permissions.

⚡ 5. Working with Large SQL Files

🔸 Compression (gzip)

mysqldump -u root -p veritabani_adi | gzip > vt_adi.sql.gz

To restore the backup:

gunzip < vt_adi.sql.gz | mysql -u root -p veritabani_adi

🔸 Temporarily Turning Off Foreign Keys

SET foreign_key_checks = 0;
SET unique_checks = 0;
SET autocommit = 0;

At the end of the process:

SET foreign_key_checks = 1;
SET unique_checks = 1;
COMMIT;

🔸 Performance on Large InnoDB Tables

mysqldump -u root -p --single-transaction --quick veritabani_adi > dump.sql

--single-transaction: Backs up without locking the table. --quick: Uses memory efficiently.

🧱 6. Common Mistakes and Solutions

Error	Reason	Solution
Unknown database	No target database	Use CREATE DATABASE
Table already exists	Same table available	Export with --add-drop-table
Broken Turkish characters	Charset mismatch	add --default-character-set=utf8mb4
Missing authorization error	Insufficient user permission	Work as root or full user
import into wrong DB	Data can be crushed	Verify DB name before running command

💬 Frequently Asked Questions (FAQ)

1. What is the difference between mysqldump and binary backup?

mysqldump is a SQL-based logical backup, platform independent. Binary backup, on the other hand, is fast at the file level but version dependent.

2. How do I backup just the table structure?

mysqldump -u root -p --no-data veritabani > yapilar.sql

This command only backs up the table structure (schema).

3. Can I back up multiple databases at the same time?

mysqldump -u root -p --databases vt1 vt2 vt3 > toplu_yedek.sql

4. How to solve the “Table already exists” error?

Use the --add-drop-table parameter to overwrite existing tables.

5. Can I transfer MySQL backup to MariaDB?

Yes, the two systems are compatible. Only examine the .sql file if there are very new MySQL features.

🏁 Result

In this guide, you learned the steps to backup and restore MySQL and MariaDB databases, transfer permissions and work with large files. By applying backup strategies correctly, you can minimize the risk of data loss and create a portable infrastructure.

☁️ You can instantly apply all these steps on GenixNode and easily automate your database backup processes.