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 that you 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.