Data Import/Export

MariaDB, like other relational databases, provides several methods for importing and exporting data. This gives you the flexibility to choose a technique that best suits your needs, whether you are performing a bulk import/export or moving data from one database to another. In this comprehensive guide, we will explore different ways to import and export data using MariaDB.

The three primary methods of data import/export we will discuss are:

1. SQL dump files: Using the 'mysqldump' utility to export data as SQL scripts.
2. CSV files: Importing and exporting data using CSV files.
3. SELECT INTO OUTFILE and LOAD DATA INFILE: Exporting and importing data using MariaDB commands.

SQL Dump Files

The 'mysqldump' utility creates a text file that contains SQL commands which can be used to recreate the database on any MariaDB/MySQL installation. This makes it a convenient tool to backup data, migrate data to a new server, or restore data. Here is how you can use it.

Export Data with mysqldump

Open your terminal and enter the following command to export your database.

mysqldump -u username -p database_name > data-dump.sql

In the command, replace 'username' with your MariaDB username and 'database_name' with the name of the database you want to be exported. Upon pressing enter, you will be prompted to enter your password. The output will be a file named "data-dump.sql" containing the SQL commands necessary to recreate your database.

Import Data with mysqldump

To import data from a SQL dump file, use the 'mysql' command as shown below.

mysql -u username -p database_name < data-dump.sql

Replace 'username' with your MariaDB username and 'database_name' with the name of the database where you want to import your data. After hitting enter, you will be prompted for your password and the data will be loaded into your specified database.

CSV Files

MariaDB allows you to import from and export to CSV files, which are simple text files containing tabular data. Below is the process of how to do this.

Export Data to a CSV File

To export data to a CSV file, you can use the 'SELECT INTO OUTFILE' command. Here is an example:

SELECT * INTO OUTFILE '/path/to/yourfile.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name;

Here, table_name should be replaced with the name of your table and '/path/to/yourfile.csv' by the desired destination of your CSV file. The FIELDS TERMINATED and ENCLOSED BY parts of the query define the delimiters for your data.

Import Data from a CSV File

To import data from a CSV file, use the 'LOAD DATA INFILE' command. Below is an example:

LOAD DATA INFILE '/path/to/yourfile.csv' 
INTO TABLE table_name 
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Replace '/path/to/yourfile.csv' with the path to your CSV file and 'table_name' with the name of your table.

SELECT INTO OUTFILE and LOAD DATA INFILE

This is another effective way to export and import data in MariaDB. Let’s see how these commands work.

Export Data with SELECT INTO OUTFILE

The 'SELECT INTO OUTFILE' statement allows you to export data from a database table directly into a text file on the server. The syntax for this statement is as follows:

SELECT column_name(s) 
INTO OUTFILE 'file_path' 
FROM table_name;

Replace 'column_name(s)' with the names of the columns you want to export, 'file_path' with the path to the file where you want to export the data, and 'table_name' with the name of your table.

Import Data with LOAD DATA INFILE

To import data from a text file into a table in the database, you can use the 'LOAD DATA INFILE' statement. The syntax for this command is as follows:

LOAD DATA INFILE 'file_path' 
INTO TABLE table_name;

Replace 'file_path' with the path to your data file and 'table_name' with the name of the table where you want to import the data.

In conclusion, data import/export in MariaDB can be done through various methods to suit different scenarios. Whether you need to migrate data, perform backups, or simply share data, these techniques should provide a reliable way to get your tasks done efficiently.