How to create a copy of a mysql database using mysqldump, create database and source commands and make your tests on that copy of your original(production)database (of course, in case you have one server to test it on).

1. Back-up the original database that you want to "clone"

In case you you have a mysql password for user root you need to use mysql(dump) -u root -p dbname and you will be asked for root password by mysql program. If you don't wish to be asked you can youse mysql(dump) -u root -ppassword (yes, one word) dbname to access or backup the database (mysqldump) and you can redirect the output (in case of backup) to a file (preferably .sql file).

$ mysqldump -u root -p dbname >~/db_name.sql

I used home directory absolute path because we will use it in step 3 - Populating the copy of the mysql database.

2. Creating the copy of the mysql database

Now that we have the file that is the database backup (~/db_name.sql) we can go ahead and connect to mysql database using mysql command:

$ mysql -u root -pmypassword Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 812 Server version: 5.1.20-beta-log FreeBSD port: mysql-server-5.1.20 Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> create database dbname_copy mysql> use dbname_copy

note that mysql> is mysql prompt. From here you can give all commands thatyou will give to this mysql db.
1st command creates a new database and 2nd command tells the client to use that database.

3. Populating the copy of the mysql database

(previous step use dbname_copy) mysql> source ~/db_name.sql

This command populates the copy of your database with the contents of your original database backed up in ~/db_name.sql file.

Now you can check both mysql databases dbname and dbname_copy and see that they are identical and you can perform any tests on dbname_copy database.

This whole operation was similar to a mysql database backup or mysql database move procedure which I will cover in a future tutorial.

Note: dbname and dbname_copy must be changed to your needs.

Helpfull Links:
One command to backup all mysql databases
Cyberciti.biz - How to backup MySQL databases, web server files to a FTP server automatically
Cyberciti.biz - Shell script to backup MySql database
support.adobe.com - How To: How do I copy or move a MySQL database?

Bookmarks:

Echo "How to copy a mysql database using mysqldump" around:

-------------------advertising-----------------