# How to import and export MySQL/MariaDB database

If you want to back up or restore a MySQL or MariaDB database, command-line tools offer a simple and reliable solution. Importing and exporting are done directly via the console and are suitable for both local testing environments and production systems. The only requirement is that the database file is in a valid SQL format.

## How to import MySQL database

Open a terminal and log in to the [MySQL](https://www.ionos.com/digitalguide/server/know-how/what-is-mysql/) client using the following command:

```bash
mysql -u root -p
```

The system will prompt you to enter the password for the MySQL root user. Once logged in, create the new, empty database:

```sql
CREATE DATABASE my_new_db;
```

Replace `my_new_db` with your desired database name, then exit the MySQL client using:

```sql
quit;
```

In the terminal, navigate to the directory where your SQL dump file is located. Then run the following import command:

```bash
mysql -u root -p my_new_db < my_db_dump.sql
```

Replace `my_new_db` with the name of your target database and `my_db_dump.sql` with the filename of your SQL file.

## How to export MySQL database

To back up a database, use the following command:

```bash
mysqldump -u root -p my_db > my_db_dump.sql
```

Replace `my_db` with the name of your database. The system will create a file containing all tables and data. If you want to back up only the table structure without any content, use the following command:

```bash
mysqldump -u root -p --no-data my_db > structure.sql
```

If you want to export only the data (without `CREATE TABLE` statements), use:

```bash
mysqldump -u root -p --no-create-info my_db > data_only.sql
```

## How to import MariaDB database

Importing into [MariaDB](https://www.ionos.com/digitalguide/server/know-how/what-is-mariadb/) works almost the same as with MySQL, but you’ll use the `mariadb` command instead of `mysql`.

Open a terminal and log in to the MariaDB client:

```bash
mariadb -u root -p
```

Next, enter the password and create the new database within the client:

```sql
CREATE DATABASE my_new_db;
```

Exit the session with:

```sql
quit;
```

In the terminal, run the following command to load the data into the database:

```bash
mariadb -u root -p my_new_db < my_db_dump.sql
```

Make sure to enter the correct file name and database name here as well.

## How to export MariaDB database

Use the following command to create a backup of the database:

```bash
mariadb-dump -u root -p my_db > my_db_dump.sql
```

If you only want to back up the structure of the database:

```bash
mariadb-dump -u root -p --no-data my_db > structure.sql
```

To export only the data without the table structure, enter:

```bash
mariadb-dump -u root -p --no-create-info my_db > data_only.sql
```

Make sure you have the necessary permissions to access the database and perform read and write operations. For production systems, it is strongly recommended to create a full backup of the target database before performing an import.


This is a markdown version of: [https://www.ionos.com/digitalguide/hosting/technical-matters/mysqlmariadb-database-import/](https://www.ionos.com/digitalguide/hosting/technical-matters/mysqlmariadb-database-import/) for AI/LLM consumption.