This KB will demonstrate how you can import and export your MySQL/MariaDB database using WordPress CLI because at times you need to download (export), and upload (import) your database and this is easily achievable using WordPress CLI and you do not require a web browser to operate WP-CLI.
For example, if you have a database locally available on your computer, and you want to transfer to your server to import (upload) the database on your application, or you want the database of your server to be exported (downloaded) to the server first so you can transfer it to your local computer then below processes will help.
WP-CLI is pre-installed on all the servers deployed on Cloudways.
What is WordPress CLI?
WP-CLI is a command-line interface for WordPress, and it is a set of command-line tools for managing WordPress installations on a server. It allows the WordPress admin users to carry out different administrative tasks without actually using a web browser. This management through the command-line interface allows the users to manage their WordPress websites by executing a set of defined commands. The scope of the tasks mainly includes upgrades, taking database backups, publishing new posts, and much more.
How to Import and Export Database Using WordPress CLI
Here are a few simple steps to import and export the database to MySQL/MariaDB using WordPress CLI.
Step# 1
First of all, you need to connect to your server remotely via SSH so you can use WP-CLI.
If you would like to know what SSH is and why it is used, then Click Here to find out. In this example, we are using Master Credentials to access the server remotely.
You can connect to your server via SSH in two ways, so choose your preferred option from the below options. You can also click on the hyperlink text of bullet points to learn about connecting remotely to the server.
Using SSH Client.
Using the Cloudways Integrated SSH Terminal.
In this example, we have used an SSH client named PuTTY. You will see a similar window after successful connection as shown below:
Step# 2
Now, you need to go to that specific directory where your webroot is located. In other words, where your wp-config.php file is located else, WP-CLI will not work. By default, it is in the public_html directory, and we will also follow the default path to operate WP-CLI in this example. Enter a command mentioned below to go into the public_html folder.
cd applications/<your_application_name>/public_html/
Important
Angle brackets are included to indicate the positions of your inputs so make sure to remove the angle brackets. Your application name is the same as your Database name (DB name). Click Here to find out where your application name is located.
You will see a similar window after running the above command successfully.
Step# 3
You may start using WP-CLI once you are in the public_html directory.
Part A: Exporting Database
Exporting a database means that you want to export (download) your database on your server so that you can transfer it to your local computer afterward.
Executing the following command will export (download) the database and create a file in the server. In this example, we are naming the file as a backup.sql, and you can name it as per your choice, but the syntax must be followed.
wp db export <file>.sql
For example: wp db export backup.sql
<file> = The name of the SQL file to export. If ‘-‘, then outputs to STDOUT. If omitted, it will be ‘{dbname}-{Y-m-d}-{random-hash}.sql’.
This new file will be located in the same directory where you are running WP-CLI. In this example, it was a public_html directory where the wp-config.php file is located. Type list command to verify the file creation.
ls
Learn how to transfer this file to your local computer by Clicking Here.
Part B: Importing Database
Importing a database means that you have a database available locally on your computer, which you want to first transfer to your server so you can import that database into the application. Learn how to transfer your database to the server from your local computer by Clicking Here.
Important
Please make sure to transfer your file to that target directory where the wp-config.php file is located. By default, it is in the public_html folder where you are running WP-CLI as well.
Running the below command will help you to upload your MySQL/MariaDB database to the application. In this example, a file named backup.sql, which was first transferred from a local computer to the server and later was imported into the database of the application.
wp db import <file>.sql
For example: wp db import backup.sql
<file> = The name of the SQL file to import. If ‘-‘, then reads from STDIN. If omitted, it will look for ‘{dbname}.sql’.
Source: WordPress CLI
You can run your database manager afterward and verify as well. Click Here to learn about managing databases using Cloudways Database Manager.
How to transfer files back and forth between the server and the local computer?
First, establish a connection between your local computer and your server remotely via SFTP. Read this KB Article (Step# 1, and Step# 2-A) to learn about building an SFTP connection.
Once connected to the server, drag and drop your files back and forth between your local computer and server to transfer the files.
That’s it! We hope this article was helpful. If you need any help, then feel free to search your query on Cloudways Support Center or contact us via chat (Need a Hand > Send us a Message). Alternatively, you can also create a support ticket.