How to Import and Export Databases in MySQL or MariaDB

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).

And if you’re looking for a reliable, high-performance, and budget-friendly solution for your workflows, Hostman has you covered with Linux VPS Hosting options, including Debian VPS, Ubuntu VPS, and VPS CentOS. And if you're interested in good hosting for your website, check our WordPress hosting.

Working with Databases

Copy link

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

• 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

Copy link

1. Connect to MySQL or MariaDB with root privileges:

For MySQL:

mysql -u root -p  

For MariaDB:

mariadb -u root -p  

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

CREATE DATABASE IF NOT EXISTS <database_name>;

Viewing Databases

Copy link

To see the list of available databases:

SHOW DATABASES;

Switching Databases

Copy link

To switch to a specific database:

USE <database_name>;

Viewing Tables

Copy link

To list all tables in the selected database:

SHOW TABLES;

Common SQL Commands

Copy link

• 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

Copy link

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.

When exporting over the internet, refer to our Creating an SSH Tunnel for MySQL tutorial for detailed steps on setting up local port forwarding, adjusting firewall rules, and using key-based authentication to secure your data and credentials in transit.

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

Copy link

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

Copy link

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:

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

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

2. 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.

3. Grant execution permissions to the script:

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

4. 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

5. 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

6. 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

7. 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

Copy link

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

Copy link

To export a database:

1. Go to the phpMyAdmin interface and select the desired database from the left-hand panel.
2. Click on the Export tab.
3. 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.

4. Click Export.

To export specific tables:

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

Import

Copy link

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

1. 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.
2. Inside the database, click on the Import tab.
3. Click the Choose File button to browse for and select the SQL dump file from your device.
4. Choose the necessary options like file encoding, format (typically default settings are fine), and other options as needed.
5. 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

Copy link

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.

Frequently Asked Questions

Copy link

How do I export a MySQL or MariaDB database?

Copy link

Use mysqldump just like in MySQL: mysqldump -u user db > file.sql.

How do I import a database dump in MariaDB?

Copy link

Run: mysql -u user dbname < dump.sql.

Can I use the same dump file for both MySQL and MariaDB?

Copy link

Yup — standard SQL dumps work seamlessly between the two.

How do I perform a MySQL import database operation from a dump file?

Copy link

Use the command mysql -u username -p database_name < backup.sql to import a database from a SQL dump file.