Your MySQL database can be exported into a single file using the mysqldump command. We can use this command to migrate our database to another server, another SQL database (for example, postgres), or for offline development.

We’re using a Linux environment but the same command is available on Windows. What we want to do first, if we don’t know already, is to get the name of our database.

First, log into the MySQL client.

mysql -u root -p

Once you’re logged into the MySQL client, get the exact name of the database you want to backup.

SHOW DATABASES;

That’s all we need from inside the MySQL client. Control + C out of the client and back to the terminal. This following command will output the SQL queries from our database and redirect that output into a file. Without the redirection, the >~/databasename.sql, the entire database would flash through our terminal.

mysqldump -u root -p databasename >~/databasename.sql

Now we have a copy of our database in a single file. Let’s say we copied this file to another host and wanted to insert this back into MySQL.

First, we need to create a database to put it into. Log into the MySQL client and create an empty database. Here, ‘databasename‘ is whatever we would like to name our database.

mysql -u root -p
CREATE DATABASE databasename

Now, leave the MySQL client and issue the following command to insert our SQL file into the database. The path and filename will be conditional to what you named your file and where you put it when you copied it to the new host.

mysql -u root -p databasename < ~/databasename.sql