Import and export data in MySQL using command line tool

Import and Export Data To MySQL Using Command Line

by
Share this Article
Reading Time: 5 minutes
614 Views

In development, some times import and export to mysql database table is seems to be easier when you are a system admin. But in most of the cases we always prefer the UI tool of PhpMyAdmin to import and export data.

Just think at some situation when we don’t have any browser to open PhpMyAdmin or any softwares available to open MySQL, How we do import & export data to MySQL?

When you have a large size of database then we can not easily export from phpmyadmin or any other tool. So in that case we need to use mysql commands for it.

In this case, we will use the concept of MySQL interactive Mode i.e MySQL Command Line Interface. Inside this article we will cover the complete understanding over commands of MySQL Import And Export Data via Interactive Mode.

Learn more –

Let’s get started.

Import & Export Using PhpMyAdmin Manual Interface

As we have two options available to import and export data in MySQL. Options are –

  • Manual Interface of PhpMyAdmin
  • Using Command Line of MySQL

Import Data To MySQL

As in the image we can see we have import tab and select file option. So when we scroll down this page, we will see we have several different different formats available to upload data.

Export Data From MySQL

This is all about using Manual Tool of PhpMyAdmin Interface to interact with MySQL. Let’s see by using Command Line.

MySQL Import And Export Data via Interactive Mode

Inside this case we are going to use terminal and few commands of mysql to use MySQL interactive mode.

Also here, we have multiple options to choose file formats for import and export as well. We will use .sql to understand.

Import data to MySQL Table

Syntax: 

$ mysql -u {username} -p {database_name} < {file_path}

Example: 

$ mysql -u root -p db_management < /var/www/html/dbt/data.sql

When you press enter you need to pass password and then data will be automatically import to your selected database.

Export Data From MySQL Specific Table

Syntax: 

$ mysqldump -u {username} -p {database_name} {table_name} > {file_path}

Example: 

$ mysqldump -u root -p db_management tbl_roles > /var/www/html/dbt/roles.sql

Table data will be exported to roles.sql file and saved to the specified path.

Export Compete database From MySQL

Syntax: 

$ mysqldump -u {username} -p {database_name} > {file_path}

Example: 

$ mysqldump -u root -p db_management > /var/www/html/dbt/data.sql

db_management database will be exported to data.sql file and will save at given location.

We hope this article helped you to learn about Import & Export data From MySQL Using Command Line in a very detailed way.

Buy Me a Coffee

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.

Related Posts: