MySQL: Duplicate MySQL database

One of the client are requesting to duplicate their database to development database in case their production database have change mistakenly.

Scenario:
– MySQL Database server
– Root password: mYR@oT
– Production Database: db1
– Development Database: db2
– Development User: db_user
– Development User Password: R@0TPa5s

To do this, I choose to use mysqldump to done it.

1. First of all, you must create the development database named db2.

mysql -u root -p'mYR@oT' -e "create database db2"

2. Next, you may duplicate the database data from db1 to db2.

mysqldump -u root -p'mYR@oT' db1 | mysql -u root -p'mYR@oT' db2

3. Now, you need to assign the MySQL user permission to the db2(development) database.
a) Log into the database server.

mysql -u root -p'mYR@oT'

b) Grant the database privilege,

mysql> GRANT ALL PRIVILEGES ON db2.* TO db_user@localhost ;

Published by

Mick Genie

Mick Genie is the founder of MickGenie.com and working at ExaBytes Network Sdn Bhd and WPWebHost web host company. He is expertise in Windows and Linux environment especially web hosting related information, tips and trick as well as the IT Information.