Articles

How to Export a Database Using the Command Line

If you're not familiar with using SSH, it's easier to export a database using Adminer. However, if your database is very large (for example, more than 1GB), it can be easier to export a database using MySQL from the command line.

First, you'll need to SSH into your server. You should SSH in as the system user that owns the app, which is the serverpilot user by default.

Next, run the mysqldump command to export and compress the database, where DB_USER is your database username and DB_NAME is your database name:

mysqldump --password --user DB_USER DB_NAME | gzip > DB_NAME.sql.gz

Once you run the above command, you'll be prompted for the database user's password. Note that the MySQL username and password are not the same as the system user password you used to SSH into your server.

You can then use SFTP to download this exported database file and move it to a different server, if necessary.

Once you're ready, you can import your database using the command line.