0

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'
2

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.

| 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy