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:

$ 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.

$ 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.

$ 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:

$ 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.

$ 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.

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

References:

• mysqldump reference

Happy Learning 🙂