If you find yourself in a situation that requires changes to your site, and your developer isn't available to help, DreamHost's skilled support team may be able to assist you for a small fee. You can find more information about supported services and associated costs in the DreamHost Professional Services article.
MySQL databases can be exported into a single file which you can then use to migrate the database to another hosting company.
Very often, this MySQL file contains a CREATE DATABASE and a USE statement. These statements are intended to automate the process of migrating the database to a new location, however, attempting to import your database to DreamHost with such a file will fail as you do not have CREATE DATABASE permissions.
When you import a database with these statements via SSH or phpMyAdmin, you'll see the following error message:
ERROR 1044 (42000): Access denied for user 'your_user_name'@'yourhost/hostmask' to database 'newdbname'
This error message indicates that you don't have sufficient permissions to create the new database and is caused by the CREATE DATABASE statement in the MySQL file being imported.
The only way to create a database at DreamHost is in the panel. Because of this, the CREATE DATABASE statement that is in your MySQL file is not needed.
To resolve this issue:
- Open the MySQL file in a text editor (not a word processor, such as MS-Word).
- Delete the lines that begin with CREATE DATABASE and USE. For example:
CREATE DATABASE `newdbname` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_c$ USE `newdbname`;
- The exact text of the CREATE DATABASE statement differs depending on the database name and the character set used.
- Make sure to delete everything from the beginning of the line through, and including, the semi-colon at the end of the line.
- Once you have deleted these statements, save your edited MySQL file, and then try importing again.
If you restore a MySQL backup to a database under another user and have any 'views' in your database, you'll get an error similar to this:
ERROR 1227 (42000) at line 694: Access denied; you need the SUPER privilege for this operation.
You'll need to remove the DEFINER mentioned in the .sql file at the line specified in the error.