How do I manually download a MySQL dump file from an S3 bucket?

Answer

It is assumed that you have already uploaded your dump file to an S3 bucket.

• <S3-BUCKET> - The name of the S3 Bucket where the file is stored (e.g., mybucket).
• <DUMPFILE> - The mysqldump file (e.g., mydump-201104161345.gz). Be sure to include the file extension. The file's permissions must be set to "public-read" so that the file can be accessed.
• <DBSCHEMA> - The database schema name. (e.g., myschema).

SSH into your running server and run the following commands, substituting the variables with your own values:

wget http://<S3-BUCKET>.s3.amazonaws.com/<DUMPFILE>
gunzip <DUMPFILE>
mysqladmin -u root create <DBSCHEMA>
mysql <DBSCHEMA> < <DUMPFILE>

To verify that the mysqldump file was properly fetched from the S3 bucket, you can run the following commands:

Note: The name of the database (DBSCHEMA) is "myschema" in the example below.

# mysql

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myschema           |
| mysql              |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use myschema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+--------------------+
| Tables_in_myschema |
+--------------------+
| phptest            |
+--------------------+
1 row in set (0.00 sec)

mysql> select * from phptest;
+----+-----------+-------------+
| id | firstname | lastname    |
+----+-----------+-------------+
|  1 | John      | Doe         |
|  2 | Greg      | Smith       |
|  3 | Elon      | Johnson     |
|  4 | Dean      | Stevenson   |
+----+-----------+-------------+
4 rows in set (0.00 sec)

mysql> exit

See also

• S3 Browser