Support Online
MirrorLooking Glass
GenixNodeGenixNode
Client Panel
Skip to main content

MySQL Import Export Guide: Database Migration with mysqldump

In this guide, you will learn how to safely export and restore MySQL and MariaDB databases.
You will go through the steps of taking backups, migrating a server, and managing large .sql files.

What Will You Learn in This Guide?​

  • Database export with mysqldump
  • Import process with mysql client
  • Move only certain tables
  • Transfer user authorizations and permissions
  • Optimizing large .sql files

Prerequisites​

  • Ubuntu, Debian or CentOS server
  • MySQL or MariaDB installation
  • user with sudo privilege
  • A database to transfer

Database Export (Backup)​

mysqldump is the standard logical backup tool for MySQL and MariaDB.

mysqldump -u kullanici_adi -p veritabani_adi > yedek.sql
  • This command writes the schema and data of the database to the .sql file.

Check that the file has been created:


head -n 5 yedek.sql
  • This output indicates that the file is a valid SQL dump.

Database Import (Restore)​

  1. First you need to create the target database.

mysql -u root -p
  • This command opens the MySQL shell.

CREATE DATABASE yeni_veritabani;
  • After the database is created, exit and start the import process:

mysql -u kullanici_adi -p yeni_veritabani < yedek.sql
  • This command transfers the backup to the target database.

Exporting Only Specific Tables​

  1. You can move selected tables instead of the entire database.

mysqldump -u kullanici_adi -p veritabani_adi tablo1 tablo2 > tablolar.sql
  • This command backs up only the specified tables.

Moving Users and Authorizations​

  1. User accounts are not included in database dumps.
  • Authorizations are stored in the mysql system database.

Method 1: Exporting Authorization Tables​


mysqldump -u root -p mysql user db tables_priv columns_priv procs_priv > yetkiler.sql
  • This command moves users and permissions.
Import process:​

mysql -u root -p mysql < yetkiler.sql
  • Then renew the authorizations:

FLUSH PRIVILEGES;
  1. This method ensures compatibility between versions.

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

mysql -u root -p < grantlar.sql

Working with Large SQL Files​

  1. Compressing and Exporting

mysqldump -u kullanici_adi -p veritabani | gzip > veritabani.sql.gz
  • This command saves disk space.
Import process:​

gunzip < veritabani.sql.gz | mysql -u kullanici_adi -p veritabani
  1. Optimizing for Performance

Recommended flags when exporting:


mysqldump -u kullanici_adi -p --single-transaction --quick veritabani > yedek.sql
  • These settings take backup without locking the table.
  1. Shredding Large File

split -l 5000 buyuk.sql parca_
  • This command splits the large dump file into small pieces.

for f in parca_*; do mysql -u kullanici_adi -p veritabani < "$f"; done

Frequently Asked Questions (FAQ)​

1. Does mysqldump take a binary backup? No. Takes SQL-based logical backup.

2. Can MySQL dump be imported into MariaDB? Yes. It is largely compatible.

3. Can I export just the schema? Yes. Use the --no-data flag.


mysqldump -u kullanici_adi -p --no-data veritabani > schema.sql

4. Import is very slow, what should I do? Temporarily turn off foreign key checks and compress the file.

5. Is it safe to import into a live database? Not recommended. Test it in a staging environment first.

Result​

In this guide, you learned how to safely import and export MySQL and MariaDB databases. These methods are critical in backup, migration and recovery scenarios.

You can try the GenixNode platform now for high-performance database servers and backup infrastructures.