Issue with duplicating a MySQL database

Question

I'm trying to "clone" a MySQL database. The problem is with views. When I export a view, the .sql defines the view as database_name.view_name. It doesn't do this for tables, just views. This obviously creates a problem when importing to the second database - the view doesn't get created.

Answer 1

I think I've found the answer. The problems I was running into were being created by phpMyAdmin. From the command line (make sure to create the target database first):

mysqldump -u [username] -p[password] [old_database_name] > dump.sql
mysql -u [username] -p[password] [new_database_name] < dump.sql

No problems.

Answer 2

One thing you may want to try is SqlYog Community, I use it all the time for MySQL and it seems to do a great job of copying entire databases from one server to another, or even on the same server.