This article illustrates how to back up your MySQL databases hosted with DreamHost.
DreamHost provides a handful of backup methods for MySQL databases, with the actions ranging from extremely simple to mildly technical. The tools offered include the DreamHost panel, the provided phpMyAdmin interface, and using the server command line to generate a backup available for FTP downloading.
Backing up using the panel backup tool
The account backup tool is located on your (Panel > ‘Billing & Account’ > ‘Backup Your Account’) page. It downloads all the data in your account, including all users, mailboxes, and databases (MySQL stored procedures are captured as well), in a series of .zip archive files.
You can read more about this in the following article:
Backing up using phpMyAdmin
phpMyAdmin is a MySQL database administration tool that provides a (relatively) easy-to-use UI through a web browser. Follow the instructions below to create a MySQL database backup using phpMyAdmin.
- Find the database user/password. Visit the (Panel > 'Goodies' > 'MySQL Databases') page where you can find a listing of your MySQL hostnames and existing databases.
- The specific username tied to the database is listed to the right of your databases.
- Click the MySQL username.
- Click the Show button to obtain it’s password. The database’s MySQL user and password are your credentials when logging into phpMyAdmin.
- Open phpMyAdmin. Do this by opening the URL of your MySQL hostname in your browser (e.g., mysql.example.com). Or, click the phpMyAdmin link to the right of the hostname.
- This opens phpMyAdmin for your database server. A user name and password prompt appear.
- Enter your MySQL username and password, and then click Go.
Notes: phpMyAdmin requires that the domain for your MySQL hostname is Fully Hosted and uses DreamHost's DNS. You can do this in one of two ways:
- The phpMyAdmin user interface appears.
- To the left, click on the name of your database you wish to backup.
Note: The “information_schema” database is automated by DreamHost and should be ignored. You are not allowed to modify the entries of this database.
- Click the Export tab at the top.
- A pane marked “View dump (schema) of database” appears.
- Underneath the list marked "Export", click the 'Select All' link.
- All the tables are now highlighted.
- Make sure you select the SQL radio button in the Export panel. The phpMyAdmin interface provides various export formats, including CSV, SQL, XLS (MS Excel), and XML.
- Check the Structure box and the following three settings in this section:
- Add IF NOT EXISTS
- Add AUTO_INCREMENT value
- Enclose table and field names with backquotes.
- Check the Data box and the following three settings in this section:
- Complete inserts: Exporting your database with “complete inserts” enabled allows for a more complete backup and is recommended if you plan to import the data into a new database with a differing storage engine. In any other case, leave it unchecked.
- Extended inserts: Database exports exceeding 100MB should uncheck “extended inserts” as this prevents errors importing large database backups in the future.
- Use hexadecimal for BLOB
- Check the box marked Save as file on the bottom left.
- Click the Go button.
phpMyAdmin downloads a SQL file named as you specified. When the file download dialog appears in your browser, specify on your computer where you wish to store the file and complete the download.
Backing up through the SHELL
DreamHost shared hosting servers, VPS, and Dedicated hosting support MySQL commands which are entered from within the server command line.
To backup your database with a single command, log into your web server via SSH. Visit the following article for full instructions on how to do this:
Once logged into the server, run the following command. Make sure to change the username, password, yourMySQLHostname, and dbname to the correct credentials. You can find those in your panel on the (Panel > 'Goodies' > 'MySQL Databases') page:
You can use any name for "nameofyourbackup.sql" – just make sure it ends with .sql.
You can now use the following FTP article for instructions on how to log into your server and download the .sql file: