How to clone MySQL DB? Errors with CREATE VIEW/SHOW VIEW privileges
- by user38071
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 doing a dump to a .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).
The SHOW GRANTS command for the existing user works (the one who has privileges over the source database). It shows that the user has 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'
