How to Import and Export Databases in MySQL or MariaDB

Technical writer

MySQL

10.02.2025

Reading time: 8 min

Database management is a crucial aspect of Linux server and web application administration. Importing and exporting databases are essential tasks for DevOps and system administrators.

At a minimum, developers should know how to back up databases and transfer them between servers. This guide explains how to import and export database dumps in MySQL or MariaDB on a Linux server (using Ubuntu as an example).

Working with Databases

MySQL and MariaDB are popular relational database management systems (RDBMS) used for storing data in large applications.

MariaDB is a fork of MySQL developed by its original creators due to licensing concerns following Oracle's acquisition of MySQL.
Both MariaDB and MySQL share identical or similar APIs and operating mechanisms.

Creating a Database

Connect to MySQL or MariaDB with root privileges:

For MySQL:

mysql -u root -p

For MariaDB:

mariadb -u root -p

Create a database (if it doesn't already exist):

CREATE DATABASE IF NOT EXISTS <database_name>;

Viewing Databases

To see the list of available databases:

SHOW DATABASES;

Switching Databases

To switch to a specific database:

USE <database_name>;

Viewing Tables

To list all tables in the selected database:

SHOW TABLES;

Common SQL Commands

Creating a table:

CREATE TABLE IF NOT EXISTS users (
  user_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(100) NOT NULL
);

This creates a table named users with fields user_id and username.

Inserting data into the table:

INSERT INTO users (username) VALUES ('John Doe');

This adds a new row to the users table.

Selecting all rows from the table:

SELECT * FROM users;

Monitoring MySQL/MariaDB status

To check the server's global status and statistics:

SHOW GLOBAL STATUS;

Exporting Databases

Exporting data from MySQL or MariaDB can be efficiently done using the mysqldump CLI utility or third-party tools like phpMyAdmin. The mysqldump utility allows you to save your database as an SQL dump, which contains the necessary commands for creating columns and populating them with data. This dump file can be easily managed, imported, or transferred.

You will need:

A database you want to export.
User credentials with at least read access to the database.

In the terminal, run the following command:

mysqldump -u <username> -p<password> <database_name> > db_dump.SQL

Where:

-p<password>: Password for the database user (you can omit the password and simply use -p to prompt for it manually).
db_dump.SQL: The name of the output dump file.
<username>: The privileged user with read access.
<database_name>: The name of the database you are exporting.

To create dumps from a remote server, add the -h flag:

mysqldump -h <ip-address> -u <username> -p<password> <database_name> > db_dump.SQL

If the MySQL server uses a non-standard port, specify it with the -P flag:

mysqldump -h <ip-address> -P <port> -u <username> -p<password> <database_name> > db_dump.SQL

While the default export format is SQL, mysqldump also supports exporting data as CSV, XML, and other formats by configuring additional parameters.

The SQL dump typically includes:

Information about the RDBMS (MySQL or MariaDB)
Commands for creating the required tables and their columns
Data to populate those columns

By default, it provides a comprehensive snapshot of the database structure and contents, making it an essential tool for database backups and migrations.

Importing Data into MySQL or MariaDB

To import a database dump, you don’t need mysqldump; a direct call to MySQL will suffice.

Run the following command in your terminal:

mysql -u <username> -p<password> <new_database_name> < db_dump.SQL

Where:

-p<password>: The user's password (use -p without the password to be prompted manually).
db_dump.SQL: The dump file containing your database data.
<username>: A privileged user with write access.
<new_database_name>: The name of the target database to which you are importing the dump.

If the process completes successfully, there will be no output. If any errors or warnings occur, MySQL or MariaDB will display them.

You can check if the import was successful with these commands:

SHOW DATABASES;  -- Lists all databases
USE <database_name>;  -- Selects the target database
SHOW TABLES;  -- Lists all tables within the selected database

By executing these commands, you can confirm that the database structure and data have been imported correctly.

Creating a systemd Service for Backup

Suppose you want to automate the database backup (export) process. In this guide, we will create a service-timer that will trigger a script for backing up data.

A Timer is a mechanism used to schedule the execution of a specific service at a given time or through certain intervals.

Follow these steps to set it up:

First, connect to the server and create the directory for backup scripts:

mkdir -p /usr/bin/backup_scripts  # Create directory for backup scripts

Create and open the file /usr/bin/backup_scripts/mysql_backup.sh in any text editor (e.g., nano):

nano /usr/bin/backup_scripts/mysql_backup.sh

Inside the file, add the following script:

TIMESTAMP=$(date +'%F')  # Get the current date
BACKUP_DIR='<path_to_backup_directory>'
MYSQL_USER='<username>'
MYSQL_PASSWORD='<password>'
DATABASE_NAME='<database_name>'

mkdir -p "$BACKUP_DIR/$TIMESTAMP"  # Create directory for this date
mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD $DATABASE_NAME > "$BACKUP_DIR/$TIMESTAMP/$DATABASE_NAME-$TIMESTAMP.sql"  # Create dump
find "$BACKUP_DIR" -type d -mtime +28 -exec rm -rf {} \;  # Delete backups older than 28 days

Replace the placeholder variables with the actual backup directory path, MySQL user credentials, and the database name.

Grant execution permissions to the script:

chmod +x /usr/bin/backup_scripts/mysql_backup.sh

Create the service file /etc/systemd/system/mysql-backup.service:

sudo nano /etc/systemd/system/mysql-backup.service

Add the following content:

[Unit]
Description=MySQL Database Backup Service

[Service]
Type=oneshot
ExecStart=/usr/bin/backup_scripts/mysql_backup.sh

[Install]
WantedBy=multi-user.target

Create the timer file:

sudo nano /etc/systemd/system/mysql-backup.timer

Add this content to schedule the backup:

[Unit]
Description=Run MySQL Backup Service Weekly

[Timer]
OnCalendar=weekly
Persistent=true

[Install]
WantedBy=timers.target

Reload the systemd configuration, enable the timer for autostart, and start it:

systemctl daemon-reload  # Reload systemd configuration
systemctl enable mysql-backup.timer  # Enable timer to start automatically
systemctl start mysql-backup.timer  # Start the timer

Check the status of the timer and ensure it is working:

systemctl status mysql-backup.timer
systemctl list-timers  # Lists active timers

Now, your system will automatically create a backup of the specified database every week.

Export and Import via phpMyAdmin

You can perform database imports and exports not only through command-line utilities but also through the phpMyAdmin web interface. This method is typically more convenient when the dump size is small (less than 1GB).

This section will cover the basic process of importing and exporting databases.

Export

To export a database:

Go to the phpMyAdmin interface and select the desired database from the left-hand panel.
Click on the Export tab.
Choose export method:
- Quick Export: Select this if you want a basic export with default settings.
- Custom Export: Choose this for more specific export options, such as selecting certain tables, formats, or compression methods.
Click Export.

To export specific tables:

Click on the database name in the left sidebar to view its tables.
Select the tables you want to export by checking the boxes next to their names.
At the bottom of the page, choose Export from the list of actions.
On the next page, verify the export format and options, then click Go to save the dump to your local machine.

Import

The process of importing a database is very similar to exporting. Follow these steps:

Open phpMyAdmin and navigate to the database you want to import into. If the database doesn't exist, create it by clicking Create Database from the left sidebar.
Inside the database, click on the Import tab.
Click the Choose File button to browse for and select the SQL dump file from your device.
Choose the necessary options like file encoding, format (typically default settings are fine), and other options as needed.
Click Go to start the import process.

Once the process is complete, you will see the imported files and their contents listed in the left sidebar. You can also modify them if needed.

Conclusion

The choice of method depends on your needs:

phpMyAdmin is ideal for manually creating backups or for quick access to a database. It’s user-friendly and simple for small-scale tasks.
Using command-line utilities would be more efficient and flexible for automation or if you’re transferring infrastructure.

MySQL

10.02.2025

Reading time: 8 min

Similar

MySQL

How to Import and Export Databases in MySQL or MariaDB

Working with Databases

Creating a Database

Viewing Databases

Switching Databases

Viewing Tables

Common SQL Commands

Exporting Databases

Importing Data into MySQL or MariaDB

Creating a systemd Service for Backup

Export and Import via phpMyAdmin

Export

Import

Conclusion

Similar

How to Secure MySQL Server

How to Install MySQL on Debian

Creating an SSH Tunnel for MySQL Remote Access

Do you have questions, comments, or concerns?

Do you have questions,
comments, or concerns?