About

How To Import and Export Large Database files in MySQL or MariaDB

Apr 4 2022

1 min read

Introduction
The process of importing and exporting databases occurs frequently during the course of creating, updating and moving Web Apps. If you lose or damage your data, data dumps can be used to restore it. Data migration is another option that may be taken advantage of using these tools.

There are a number of ways to work with MySQL or MariaDB database dumps in this tutorial (the commands are interchangeable). You will import a database from a dump file once you have exported a database.


Prerequisites
To import or export a MySQL or MariaDB database, you will need root Access to cloud VPS server or Dedicated server. 

Why Import and export through command line?
Normally small databases can be imported and exported through phpMyAdmin. but in case of big databease files you will face many issues such as File upload size limit, Server time out. Those issues can easily overcome below method. 

Import and Export Large Database files in MySQL or MariaDB
Login into your web server with root user using SSH. 

Step 1 — Exporting a MySQL or MariaDB Database

Use mysqldump to export your database:
mysqldump -u username -p database_name > data-dump.sql

username is the username you can log in to the database with
database_name is the name of the database to export
data-dump.sql is the file in the current directory that stores the output.
Above mysql Dump is saved in root folder of server. to access mysqldump you need to move dump file from root folder to /var/www/html/DB folder. 

Step 2 — Importing a MySQL or MariaDB Database

You must create a new database in MySQL or MariaDB to import an existing dump file. The imported data will be stored in this database.

Upload the Database file into the server using Filezilla.  
In our case uploaded the file in directory : /var/www/html/DB 

OR

Upload the Database file from old server to new server using Command in directory : /var/www/html/DB 
cd /var/www/html/DB
wget http://your_old_server_ip/data-dump.sql

Method 1 : 

To begin, log in as root or another user with adequate access to add new databases to MySQL:
mysql -u root -p
This command will open the MySQL shell. 

Create a new database with the command below. In this case, new_database and then use use command to use new_database import SQL DUMP using Source command inside  MySQL shell:
CREATE DATABASE new_database;

use new_database;

source /var/www/html/DB/data-dump.sql;


Method 2  : 

mysql -u root -p

CREATE DATABASE new_database;

exit

mysql -u username -p new_database < /var/www/html/DB/data-dump.sql

username is the username you can log in to the database with
newdatabase is the name of the freshly created database
data-dump.sql is the data dump database file to be imported, located in the current directory