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.
Next, run the mysqldump command below to export and compress the database, where you replace DB_USER with the database username associated with your database and DB_NAME with the name of the database you wish to export:
mysqldump --no-tablespaces --password --user DB_USER DB_NAME | gzip > DB_NAME.sql.gz
Once you run the above command, you'll be prompted for the password of the database user associated with the database you're exporting. After you've successfully entered the correct password, the export process will begin.
Note: The MySQL username and password are not the same as the system user password you used to SSH into your server.
If you're unsure of your database credentials, you may be able to obtain your database name, username, and password from your app's respective configuration file. For example, WordPress sites store this information in the wp-config.php file. Configuration files that contain this information will vary depending on the software you're using. All databases that have been created via ServerPilot can be found on the Databases tab for your app along with the associated username, however, passwords are not stored. If you do not remember the password for a database user, you can reset the password following our guide here.
If the database is rather large in size, it may take a while for this command to complete. A successful export will not show any output and you will simply be returned to the command line prompt where you can enter commands again. You can confirm that the export was successful using the command below in the same directory you ran the mysqldump command:
ls -lah | grep .sql.gz
Its output will be similar to below, in which you should see the .sql.gz file for the database export you just created as noted in bold below:
-rw-r--r-- 1 serverpilotuser serverpilotuser 20 Apr 5 00:59 DB_NAME.sql.gz
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.