Copy a MySQL Schema

I don’t know much about database and SQL, since I am not a database administrator. This post is probably way to easy for you. Nevertheless, I thought I’ll share the findings from my googling around.

What I am trying to do is simple. I have a schema, and I want to copy it (for backing up, for integration testing, etc etc).

Let us just say the schema name is dwuysan_prod and I would like to create an integration schema dwuysan_integration.

Here is the step-by-step todo:

  • Open a terminal, we will be using command mysql
  • Firstly, connect to mysql

    mysql -u root -p;
    
  • Create database named dwuysan_integration

    CREATE DATABASE dwuysan_integration;
    
  • Grant all privileges

    GRANT ALL PRIVILEGES ON dwuysan_integration.* TO dwuysan_prod@"%";
    
  • Exit the terminal, and let’s use mysqldump

    mysqldump --add-drop-table --complete-insert -uroot -p<your password> dwuysan_prod | mysql -uroot -p<your password> dwuysan_integration;
    

References

Ottuzzi, P 2009, ‘How to Clone a Schema in MySQL’, Brucalipto.org, accessed on 22 October 2015

Advertisements