Help importing an SQL flat file to a new DB. "Access denied" issue in terminal.

Hi, I hope I can get help through here. I am trying to create a copy of my vBulletin forums at sc2mx.com so I can manage one as a development system and the other as production system, so to speak.

My production database is romri_sc2mx, my new development database is romri_sc2mx_dev. I am trying to migrate all content from the former to the latter.

I created an sql dump from romri_sc2mx using phpMyAdmin called localhost3.sql but when I try to import it into romri_sc2mx_dev, I get the following:

> [romri@web113 sc2mx_dev]$ mysql -u romri_sc2mx_dev -p -D romri_sc2mx_dev < localhost3.sql

Enter password:

ERROR 1044 (42000) at line 21: Access denied for user 'romri_sc2mx_dev'@'localhost' to database 'information_schema'

I cannot import through phpMyAdmin because the SQL dump is too big (lol).

Any help with this issue is greatly appreciated!

asked May 29 '11 at 21:18

RommelTJ
7●1●5

One Answer:

oldest newest most voted

Hi,

It seems your SQl dump contains the dump for information_schema table which is a system table hence inaccessible to normal users.

You would need to remove any lines referring to that database from the dump for it to be successful.

Please also make sure that the dump contains the tables and data only for a s ingle database which is "romri_sc2mx_dev" in this case.

Hope that helps!

answered May 29 '11 at 21:32

neeravk ♦♦
1406●1●3●11

After doing that, I get:

> [romri@web113 sc2mx_dev]$ mysql -u romri_sc2mx_dev -p -D romri_sc2mx_dev < localhost2.sql

Enter password:

ERROR 1044 (42000) at line 21: Access denied for user 'romri_sc2mx_dev'@'localhost' to database 'romri_sc2mx'

I'm currently editing the sql file, but if you have any insights that would be great as I'm not very familiar with SQL.

(May 29 '11 at 22:20) RommelTJ

The problem is that by default the user romri_sc2mx_dev only has access to the database romri_sc2mx_dev not romri_sc2mx which is causing the problem.

Hence, you need to either replace occurrences of romri_sc2mx with romri_sc2mx_dev in your file or grant access to romri_sc2mx to romri_sc2mx_dev using: http://community.webfaction.com/questions/803/providing-phpmyadmin-access-to-other-users

Hope that helps!

(May 29 '11 at 22:28) neeravk ♦♦

Your answer

If you have an answer to the above question, then use the form below. Otherwise, use the appropriate 'add new comment' button above to post your feedback.

toggle preview

community wiki

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

migration ×23
import ×19
access ×16
sql ×5
denied ×4

Asked: May 29 '11 at 21:18

Seen: 2,934 times

Last updated: May 29 '11 at 22:28

Help importing an SQL flat file to a new DB. "Access denied" issue in terminal.

Follow this question

Got questions? We have answers!

Related questions