Exporting and Importing large database file(SQL) in MYSQL/Maridb using command line

Phpmyadmin does not support large files to import in mysql database. So use of command line is the best way to export or import large files in mysql database. Here are the steps to export and import a file in mysql using command line.

Prerequisite Steps

To import and/or export a MySQL or MariaDB database, you will need Access to a Windows/Linux server running MySQL or MariaDB

In Windows:

• Open command prompt from start>run>cmd .
• Go to mysql bin directory where you install mysql such D:\wamp\bin\mysql\mysql5.0.51b\bin
• D: enter
• type mysql enter and you will be confirm whether MySQL or MariaDB database is accessible or not.

In Linux:

• open terminal and type type mysql enter and you will be confirm whether MySQL or MariaDB database is accessible or not.

If MySQL or MariaDB database exist and server is accessible than you can export or import database.

Exporting the Database 

Run following command to export database

mysqldump -u root -p abc > abc.sql

• root is the username you can log in to the database with
• database_name is the name of the database that will be exported
• abc.sql is the file in the current directory that the output will be saved to

Importing the Database

We can export an existing dump file into MySQL or MariaDB in existing empty database or new database. If you want to create new database, login log in to the database as root or another user with sufficient privileges

mysql -u root -p

To create new database named xyzdb, run following command

CREATE DATABASE xyzdb;

Now run the import

mysql -u root -p xyzdb < E:\abcdb.sql

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

If the command runs successfully, it won’t produce any output. If any errors occur during the process, mysql will print them to the terminal instead.

Enjoy!