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

Netbeans 7.3 connecting to local mysql

I thought I’ll write this one up since I just spent 30 minutes trying to connect to local mysql.

I just downloaded the new Netbeans 7.3 Beta 2, and am now trying to play around with MySql. So, firstly, I simply use the Synaptic Package Manager and install mysql. Once that’s done, it’s time to connect.

I have used Oracle in the past and have managed to connect to it via Netbeans Services > Database (and downloading the ojdbc drivers, connecting, etc etc, they all goes without saying). With MySql however, I notice, there is a dedicated menu Register MySql Server.

Ooooooooooooooohhhhhhhhhhh…… fantastic!!!

Okay …

Then comes the page this this MySQL Server Properties. Hmmm, what values should I put in? …

Well, I spent some time looking up google and also trying various entries. I thought I’ll just share the screenshot here:

mysqlConfig