Blogs

Export and import MySQL databases | Media Temple Community

Posted on by admin

Command

line

Larger databases may require you to export or import directly from the command line

.

Exporting a MySQL database

1. Connect to your server using SSHSSH.

2. Navigate to the /html directory.

HTML

CD 3. In order to export your database, we will need information found in wp-config.php.

grep ‘DB_’ wp-config.php

Note the database name, user name,

  • password, host name, and four-digit port number. The port number is at the end of the host name.

define(‘DB_NAME’, ‘d92b2696256785’); /** MySQL database username */define(‘DB_USER’, ‘d92b2696256785’); /** MySQL database password */define(‘DB_PASSWORD’, ‘V|vU2QR,’); /** MySQL hostname */define(‘DB_HOST’, ‘d92c2896243585.db.2696146.hostedresource.com:3315’);

4. Use mysqldump to create a copy of the database.

mysqldump -h host_name -p port_number -u user_name -p database_name > name_of_backup.sql If

  • we use the example wp-config.php file above, our command will look like this

: mysqldump -h d92c2896243585.db.2696146.hostedresource.com -P 3315 -u d92b2696256785 -p d92b2696256785 > DBbackup.sql

  • If you want to export the backup to a location other than /html, specify the file path at the end:

> path/to/DBbackup.sql

This will create a copy of your WordPress database and place it in the specified directory. You can then use SCP or FTP to download your database.

Importing a MySQL database

1. Upload your database to the server using FTP.

2. In order to import your database backup, we will need information found in wp-config.php.

grep ‘DB_’ wp-config.php Note the database name, user name,

  • password, host name, and four-digit port number. The port number is at the end of the host name.

define(‘DB_NAME’, ‘d92b2696256785’); /** MySQL database username */define(‘DB_USER’, ‘d92b2696256785’); /** MySQL database password */define(‘DB_PASSWORD’, ‘V|vU2QR,’); /** MySQL hostname */define(‘DB_HOST’, ‘d92c2896243585.db.2696146.hostedresource.com:3315’);

3. Use mysql to import your database.

mysql -h host_name -P port_number -u user_name -p database_name < path/to/backup.sql

  • If we use the example wp-config.php file above, our command will look like this:

mysql -h d92c2896243585.db.2696146.hostedresource.com -P 3315 -u d92b2696256785 -p d92b2696256785 < data/backup1.sql

This should have imported your database. If necessary, be sure to delete the uploaded SQL file from your web-accessible directory (/html). Otherwise, anyone can download it from the web.

WP-CLI

Export a MySQL database

  1. Log in to your server via SSH.
  2. Use the command cd to navigate to the directory where your WordPress installation is located. By default, it will be /html, but use the file path that applies to your site. cd html/

    3. Use the wp command to export your database. wp

  3. db export

    4. filename.sql

  4. You should receive an output, OK: Exported to ‘filename.sql’ to confirm that the database was exported successfully

    5. .

  5. A sql file will be exported to the directory from which you ran the wp command.

Import a

MySQL database

Make sure that the database you need has already been created. If you haven’t, first create the database:

  1. use SFTP to upload your SQL file where our WordPress installation is located. By default, this will be /html. Use the file path that applies

    2. to your site

  2. Log in to your server via SSH.

    3. Use the cd command to

  3. navigate to the directory where you uploaded the sql file. cd html/
  4. Import the database by running the following command: wp db

    5. import filename.sql

  5. You should receive an output OK: Imported from ‘filename.sql’. to confirm that the import was successful.

For additional information on using wp-cli for database export and/or import (including possible variables), feel free to review the third-party resources below:

  • https://developer.wordpress.org/cli/commands/db/export/
  • https://developer.wordpress.org/cli/commands/db/import/
admin