How to clone MySQL database – ServerAdminBlog

Here is a way to create a duplicate of one database, with all its tables and their data

Dump your source database into sql file

# mysqldump -uroot -p production -r production.sql

If you need only schema (database with empty tables)

# mysqldump -uroot -p production -r production.sql --no-data

Open up a MySQL shell and login as root

# mysql -uroot -p

Create a new database and populate it with the dumped data

CREATE DATABASE production_copy;
USE production_copy;
SOURCE production.sql;

Now if you like, you can create a new user and give it permissions to the new database

CREATE USER new_user IDENTIFIED BY 'some_password';
GRANT ALL ON production_copy.* TO 'new_user'@'localhost' IDENTIFIED BY 'some_password';
FLUSH PRIVILEGES;

Note: this procedure works on Windows and Linux

Leave a Reply