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'

1 Answer 1

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.

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

You must log in to answer this question.