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
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 [email protected] IDENTIFIED BY 'D3eVVbf7'; mysql> GRANT ALL PRIVILEGES ON grand_shopper_dev.* TO [email protected] ;
Try access the new database with new user:
$ mysql -h localhost -u dev_user -p'D3eVVbf7'