How to clone MySQL database

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

Note: this procedure works on Windows and Linux

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.