How to Export MySQL Tables to a file from command line

By | 2019-04-04T22:15:09+05:30 April 2nd, 2019|mysql|

Here we will see how to Export MySQL tables to a file from command line.

Export MySQL tables to a file:

mysqldump command helps us to export MySQL tables to a file.

1. Export Mysql Tables with table Data:

$ mysqldump -u [mysql_user] -p database_name > [file_path]fimename.sql

1.1 Example:

Terminal
$ mysqldump -u root -p otp > schema.sql

It asks you to enter the MySQL password, after successful authentication, it will export all the tables schema and data of otp database to a schema.sql file.

2. Export Mysql Tables without table Data:

If you wanted to export only table schemas without data so that you can use –no-data option.

Terminal
$ mysqldump -u root -p --no-data otp > schema.sql

3. Export Mysql table data without table schema:

It is also possible to export table data without table schema, using –no-create-info option.

Terminal
$ mysqldump -u root -p --no-create-info otp > schema.sql

4. Exporting selected tables schema:

We can ship the selected tables into a file.

$ mysqldump -u root -p otp t1,t2,t3.. > schema.sql

4.1 Example:

Terminal
$ mysqldump -u root -p otp department,employee > schema3.sql

5. Ignoring Some of the tables while exporting:

We can even overlook some of the tables while exporting entire schema into a single file using  –ignore-table.

Terminal
$ mysqldump -u root -p --ignore-table=otp.employee otp > schema.sql

The above command exports all the database tables except the employee table under the otp database into the schema.sql file.

6. Exporting remote server tables to local file:

You can invoke mysqldump locally against a remote server, for this the only thing you have to know remote server host address.

Terminal
$ mysqldump -h hostname_of_the_server -u root -p otp > schema.sql

References:

Happy Learning 🙂

About the Author:

Hi Folks, you have reach this so far, that shows you like what you are learning. Then why don't you support us to improve for bettor tutorials by leaving your valuable comments and why not you keep in touch with us for latest updates on your favorite blog @ facebook , twitter , Or Google+ ,

Leave A Comment