Roboto, sans-serif

Developerbook

DeveloperBook

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.

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

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 

Upload the Database file from old server to new server using Command in directory : /var/www/html/DB 

wget http://your_old_server_ip/data-dump.sql

To begin, log in as root or another user with adequate access to add new databases to MySQL:

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:

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

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

Easiest way to import and Export Large Database files in MySQL or MariaDB using the command line. 

Lets Try

More Stories from Developerbook