Linux: Duplicate MySQL Database

My boss wants to duplicate our main database so it can be used by our developer team for our new design project. The database is already run on live system and the developer need to have same contents of database in order to complete the project.

The easiest way to duplicate MySQL database is just using mysqldump command. Based on following variables:

OS: CentOS 6.0 64bit
MySQL root password: q1w2e3!@#
Database name: grand_shopper
Duplicate database name:  grand_shopper_dev
New database user: dev_user
Password: D3eVVbf7

Firstly, since I just want to copy and duplicate database inside the same server, I need to create the database:

$ mysql -u root -p'q1w2e3!@#' -e "create database grand_shopper_dev"

And use following command to duplicate the database content  from grand_shopper to grand_shopper_dev:

$ mysqldump -u root -p'q1w2e3!@#' grand_shopper | mysql -u root -p'q1w2e3!@#' grand_shopper_dev

Format: mysqldump [user] [password] [source database] | mysql [user] [password] [destination database]

Now create MySQL user to associate with the new database. Access the MySQL server using command:

$ mysql -u root -p'q1w2e3!@#'

And run following SQL command:

mysql> GRANT USAGE ON grand_shopper_dev.* TO dev_user@localhost IDENTIFIED BY 'D3eVVbf7';
mysql> GRANT ALL PRIVILEGES ON grand_shopper_dev.* TO dev_user@localhost ;

Try access the new database with new user:

$ mysql -h localhost -u dev_user -p'D3eVVbf7'

Related Posts

• MySQL – Recover Data Using mysqlbinlog
• Linux: Install and Configure PostgreSQL with pgAdmin
• CentOS: Increase MySQL Uptime with MySQL Proxy
• Create MySQL Database Backup Every Half an Hour
• Linux: Email Alert on MySQL Replication Failure
• Move MySQL Directory to Another Location
• MySQL General Security Guidelines
• MySQL Daily Backup and Transfer to other Server
• Install MySQL Replication and Cluster using Galera
• The Best Way to Setup MySQL Replication