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 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'
- 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
- California Teen Invents Device That Can Recharge Cellphones in Just 20 Seconds - DeviceMAG 21 May 2013
- Xbox 720: Microsoft prepares to unveil next-generation console - The Guardian (blog) 21 May 2013
- Busy Monday for Yahoo: Acquires Tumblr; announces Flickr overhaul - Vancouver Sun (blog) 21 May 2013
- One giant leap for Britain: UK's first official astronaut Major Tim Peake on ... - The Independent 21 May 2013
- Porsche launches new Cayman - The Sun Daily 21 May 2013