Importing new MYSQL databases

So, I have a bunch of MySQL databases from my previous hosting provider which I need to import.

I exported them to tar.gz files and am trying to follow the instructions on http://wiki.dreamhost.com/Migrate_MySQL

I created a database and a database user in the Panel:

User my_user_name has access to the following MySQL databases:
Database Name sometest

What may my_user_name do to tables in these databases?
Select

Insert

Update

Delete

Create

Drop

Index

Alter
(all allowed)

Allowable Hosts
From what hosts (computers) may my_user_name connect to these databases?
(One per line, use % as a wildcard.)
%.dreamhost.com

Would you like to change my_user_name's password?
New Password:
Currently: my_password

(where the username and password have been changed to protect the innocent)

I have unzipped the sql file(s) in the shell and am trying the command
eros:~> mysql -u my_user_name -pmy_password -h mysql.tobyport.com db_bbps < Database-db_bbps.sql
ERROR 1044 (42000): Access denied for user 'my_user_name'@'208.113.160.0/255.255.224.0' to database 'db_bbps'

(where my_user_name is the username of of the SQL user I created earlier)

What gives?

In reply to:

---

Database Name sometest ..... I have unzipped the sql file(s) in the shell and am trying the command
eros:~> mysql -u my_user_name -pmy_password -h mysql.tobyport.com db_bbps < Database-db_bbps.sql
ERROR 1044 (42000): Access denied for user 'my_user_name'@'208.113.160.0/255.255.224.0' to database 'db_bbps'

---

It looks to me as though you named your database "sometest" but you specified in the command line above to use the database "db_bbps" .

If you don't have a database named "db_bbps", then that command line will fail.

--rlparker

Cheers - so I am a step further. Im a cactus and should use the same database name as I created earlier.

So i tried it with 'sometest' as the destination database and i get a different error. The SQL statement (from the export) seems to include instructions to generate a new database :
CREATE DATABASE `db_bbps` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_c$
USE `db_bbps`;

generating this error:
ERROR 1044 (42000) at line 12: Access denied for user 'my_user_name'@'208.113.160.0/255.255.224.0' to database 'db_bbps'

How to proceed? I mean I seem to have a chicken and the egg problem.
If i create no database in MYSQL admin panel, I have no user to access mysql with. If I use one with the same name or similar name, it doesnt like me having the 'create' statement inside.

Am I missing something obvious?

Actually, you are almost there...

In reply to:

---

So i tried it with 'sometest' as the destination database and i get a different error. The SQL statement (from the export) seems to include instructions to generate a new database :
CREATE DATABASE `db_bbps` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_c$
USE `db_bbps`;

---

That is to be expected, as you do not have sufficient permissions on DreamHost to CREATE DATABASE from the command line (or from a script even - you must create all databases from the Control Panel).

Not to worry ... you have already created the database, so the Create statement that is in your exported SQL file is redundant (and, as you saw, will fail anyway).

Just open the SQL file in a "pure" text editor (not a "Word Processor), and delete the line:

CREATE DATABASE `db_bbps` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_c$
USE `db_bbps`;

Then, run your command line again, with the correct db name matching the one your created in the Control Panel, and you should be in business.

"Chicken and Egg" problem is resolved by creating the database/use *first*, as you have done, and then just deleting the "CREATE DATABASE" statement. Table creation and other commands in the SQL file should work fine with the permissions you have given that initial MySQL user.

--rlparker

Cheers mate! That was the trick. So simple when you know it.

I think that calls for an amendment in the support wiki :)

Good deal, and I'm glad you have it working now! I'll go take a look at that wiki page, and see if there is an appropriate place to squeeze something about that situation into the article.

--rlparker