Restore SQL backup

hello, i have read dreamhost's wiki about restoring sql backup (http://wiki.dreamhost.com/Restore_SQL_backup) and tried it in my domain with no success. mysql command, with database's user's access, get me the error : ERROR 1045 (28000): Access denied for user 'user'@'kirby.dreamhost.com' (using password: YES). So i tried ftp's user's access that get me this error: ERROR 1007 (HY000) at line 15: Can't create database 'database1'; database exists. The thing is that the wiki says to create the database (through panel) before entering this command. I also tried "mysql administrator" with custom access (using my ip) but i got an error because the backup is made with mysqldump. I tried also mysqldump command but i also get an "access denied" error (1044 this time). Can someone pls help me? I've been trying for 2 days to restore my backup!!

Because of the way DreamHost MySQL is set up, you do not have the ability to create a database in any other way than from the panel. You already realize this, I'm sure, as you have already created the database.

The problem you are having arises because the .SQL file you are trying to to use to do your restore has a "create" query in it, and *that* is what is generating the "access denied for user" error (your user does not have "create" access).

The way to get around this is to open the .SQL file with a text editor, find and remove the "create" query, and save it (I'd save it as some other name, so your original is intact if you bork it).

After having done that, try again to restore using your "edited" version, which does not have the "create" query that you do not have access to run, and if you did it correctly it should now work.

--rlparker

Thanks! My problem has been solved!

You are welcome. That's great to hear, and I'm glad you now have it working!

--rlparker

Hi riparker,

Sorry to resurrect an old thread, but I'm having the same issue. However, my problem is that I have no idea what the "create" query is. Could you please enlighten me as to what I should be looking for? There's quite a few "create"s in the SQL file that I have, and I've deleted the "CREATE DATABASE `dbxxxxxxxx`;
USE dbxxxxxxxx;" and that doesn't resolve the issue.

Thanks!

Well, I can try to help with this, but without seeing the actual .sql file, I can only give you general guidance.

In essence, what you are trying to avoid are the kinds or things described here:

http://wiki.dreamhost.com/MySQL#MySQL_limitations_due_to_shared_hosting

When is say a "create" query, I'm just talking about a query (which is what those SQL commands in the SQL are) that uses the "create" command.

If you have several of those, I'd be curious to know what they are doing - most often if you are only trying to restore a single database, there is only one - a query that creates the database that they data is to be imported to (and on DreamHost, you create the database in the account control panel *before* doing the import, so the "create" is not needed, and will actually break the iimport).

--rlparker
--DreamHost Tech Support