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 dbnameand 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; |
or just mysql -u root -pyourpassword < thedbname.sql
This command populates the copy of your database with the contents of your original database backed up in ~/db_name.sql file.