Posted over 5 years ago. Visible to the public.

MySQL: How to clone a database

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

  1. Make a dump of your source database:

    Copy
    mysqldump -uroot -p my_project -r my_project.sql

    Or, if you only want to dump the database's table structure (schema) without any contents:

    Copy
    mysqldump -uroot -p my_project -r my_project.sql --no-data
  2. Open up a MySQL shell:

    Copy
    mysql -uroot -p
  3. From the MySQL shell, create a new database and populate it with the dumped data:

    Copy
    CREATE DATABASE my_project_copy; USE my_project_copy; SOURCE my_project.sql;
  4. Create a user and give it permissions to the new database:

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

Once an application no longer requires constant development, it needs periodic maintenance for stable and secure operation. makandra offers monthly maintenance contracts that let you focus on your business while we make sure the lights stay on.

Author of this card:

Avatar
Arne Hartherz
Last edit:
about 2 years ago
by Arne Hartherz
Keywords:
copy, db, guide
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Arne Hartherz to makandropedia