Export a MySQL table / database


Problem

When working on a new system you'll probably have situtations where you want to copy data from one database to another. MySQL offers a number of tools to do this out of the box, but you're required to get your hands dirty by opening a console/terminal window.



Solution

Let's assume we've got a database called my_db with a user called my_user and a password of my_password. Our database has two tables, persons and cities.

In your terminal, you can know type the following command:

mysqldump -u my_user -p my_db persons

Please note that -p defines that the user requires a password, but the password isn't entered afterwards. mysqldump will ask for the password once you execute the command. Putting a password in the command line isn't adviseable as it's keep in the history of commands.

The command above will print the SQL queries to create that table right in your terminal. Let's save it in a file:

mysqldump -u my_user -p my_db persons > dump.sql

If you want to dump the complete database, just skip the table name:

mysqldump -u my_user -p my_db > dump.sql

If you want to dump multiple tables, just create a list of table names like that

mysqldump -u my_user -p my_db persons cities > dump.sql

Comments




Please sign-in to post a comment