Import a MySQL backup file from Web Hosting to a MySQL database on another cloud server-Cloud Web Hosting(CWH)-阿里云帮助中心

This topic describes how to import a MySQL backup file from Web Hosting to a MySQL database on another cloud server, such as an ECS instance that runs a Linux operating system.

Background

Importing data from your Web Hosting MySQL database to another cloud server, such as an ECS instance running a Linux operating system, allows you to perform tasks like debugging and data analysis on the target database.

To import the backup file, perform the following steps:

Use the backup and recovery feature in the Host Management Console to extract the MySQL backup file from your Web Hosting instance and download it to your local machine.
Upload the downloaded backup file to the target ECS instance that runs a Linux operating system and decompress the file.
Use database commands to import the backup file.

Procedure

Extract and download the compressed MySQL backup file from your Web Hosting instance.
1. Log on to the Web Hosting console.
2. Find your Web Hosting instance and click Manage in the Actions column.
3. In the left-side navigation pane, choose Tools > Backup and Recovery.
4. On the Backup and Recovery page, on the Database Backup tab, find the backup file and click Extract in the Actions column.
5. In the Extract Backup dialog box, click OK.
  
  The system automatically redirects you to the My Tasks tab. Wait until the task Status changes to Extraction successful.
6. Click the Database Backup tab, find the extracted backup file, and then click Download in the Actions column.
7. In the Download File dialog box, right-click Download and select Save link as....
8. In the Save As dialog box, select a path to save the backup file and click Save.
  
  Note
  Download the backup file to a known path on your local host for easy access during the upload step.
9. In the Download File dialog box, click OK.
Upload the compressed backup file from your local host to the target ECS instance that runs a Linux operating system.
1. Log on to the ECS instance that runs a Linux operating system.
  
  For more information, see Select a method to connect to an ECS instance.
2. Upload and decompress the backup file on the ECS instance.
  
  This example describes how to upload the compressed backup file bak.zip to the /opt directory of an ECS instance that runs a Linux operating system. For detailed instructions, see Transfer files to a Linux instance by using scp, rsync, or sftp.
3. Navigate to the directory that contains the compressed backup file, such as /opt, and run the following command to decompress it.
```
unzip <compressed_backup_file>
```
  For example, decompress the bak.zip file.
```
[root@gp-cm5001a-ud1 opt]# unzip bak.zip
Archive:  bak.zip
  extracting: qdm123557567_db/qdm123557567_db.sql
```
  Important
  The unzip command is required. If it is not installed, run the following command to install it:
  sudo yum install unzip
4. Run the ls command to view the decompressed backup folder.
```
[root@gp-cm5001a-ud1 opt]# ls
bak.zip  qdm123557567_db
```
5. Run the following commands to navigate to the backup folder and view the SQL backup file.
```
cd <backup_folder>
ls 
```
  For example, go to the decompressed backup folder qdm123557567_db to view the backup file qdm123557567_db.sql.
Import the backup file into the MySQL database on the ECS instance.
1. Run the following command to connect to the MySQL database.
```
mysql -u <mysql_database_username> -p
```
  Enter the password when prompted.
2. Run the following command to create a new database.
```
create database <new_database_name>;
```
  For example, to create a new database named test, run the following command:
```
create database test;
```
3. Run the following command to verify that the test database was created.
```
show databases;
```
```
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.02 sec)
mysql>
```
4. Run the following commands to import the backup file into the newly created database.
```
use <new_database_name>
source <SQL_backup_file_name>
```
  For example, to import the backup file qdm123557567_db.sql into the test database, run the following commands:
```
use test
source qdm123557567_db.sql
```

Results

When the import is complete, the output is similar to the following:

Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>

To view the tables in the new database, run the following command:

show tables;

The following output shows that the tables from the Web Hosting database were successfully imported into the new database:

mysql> show tables;
+----------------------------+
| Tables_in_test             |
+----------------------------+
| wp_commentmeta             |
| wp_comments                |
| wp_links                   |
| wp_options                 |
| wp_postmeta                |
| wp_posts                   |
| wp_term_relationships      |
| wp_term_taxonomy           |
| wp_terms                   |
| wp_usermeta                |
| wp_users                   |
+----------------------------+
11 rows in set (0.00 sec)
mysql>