Dump MySQL database


Let's say your MySQL username is admin and password is MyLittleSecret.

To dump shop database into shop.sql run the following:

mysqldump -u admin -p MyLittleSecret \
          shop \
          > shop.sql

If you need to dump only specific list of tables from shop database then use:

mysqldump -u admin -p MyLittleSecret \
          shop user item comment \
          > shop-partial.sql

You can also dump all databases:

mysqldump -u admin -p MyLittleSecret \
          --all-databases \
          > all.sql

If all you need is just a schema of the database but don't need the data then use:

mysqldump -u admin -p MyLittleSecret \
          --no-data shop \
          > shop-schema.sql

In the opposite situation when you don't need the schema but need the data run it this way:

mysqldump -u admin -p MyLittleSecret \
          --no-create-info \
          --skip-triggers \
          --no-create-db \
          shop \
          > shop-data.sql

To restore a database from its dump use the following:

mysql -u admin -p MyLittleSecret \
      shop \
      < shop.sql
Rate this post:
Share this page:

See also how to:

How to fix “Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement” error
How to update multiple rows within a single query in MySQL
How to install MySQL database server on CentOS 7