Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Running MySQL 5.0.32 on Debian 4.0 (Etch).

I'm trying to clone a WordPress MySQL database completely (structure and data) on the same server. I tried a dump to an .sql file and an import into a new empty database from the command line, but the import fails with errors saying the user does not have the "SHOW VIEW" or "CREATE VIEW" privilege. Trying it with PHPMyAdmin doesn't work either. I also tried doing this with the MySQL root user (not named "root" though) and it shows an "Access Denied" error. I'm terribly confused as to where the problem is.

Any pointers on cloning a MySQL DB and granting all privileges to a user account would be great (specifically for MySQL 5.0.32).

Thanks!

Update: To add more clarity on the issue.

SHOW GRANTS for the existing user works (all privileges granted).

I created a new user and database. Here's what I see with the grant commands.

 $ mysql -A -umyrootaccount --password=myrootaccountpassword

 mysql> grant all privileges on `newtarget_db`.* to 'newtestuser'@'localhost';
 ERROR 1044 (42000): Access denied for user 'myrootaccount'@'localhost' to database 'newtarget_db'

 mysql> grant all privileges on `newtarget_db`.* to 'existingsourcedbuser'@'localhost';
 ERROR 1044 (42000): Access denied for user 'myrootaccount'@'localhost' to database 'newtarget_db'
share|improve this question
add comment

1 Answer

Grants and privileges are stored in the mysql database, not in the database you cloned.

Do SHOW GRANTS FOR user@host on the source server, then do the same for the destination server. You'll probably find the difference there.

share|improve this answer
    
what he said; you'll need a user with full permissions on the database, basically, to restore your snapshot. –  cpbills May 19 '10 at 7:34
    
Please see the update in the question above. I still see errors while trying to grant privileges. –  user43537 May 20 '10 at 9:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.