Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

For testing purposes I have to create a copy of a production MySQL database that cannot be touched.

These are the steps I would follow:

  1. Create a reduced size database backup:

    mysqldump prodDB --single-transaction --where="true limit 20" > test.sql

  2. Create a new test database: testDB

  3. Load the dump file in the new test database:

    mysql testDB < test.sql

Is this procedure correct?
Can I be sure that the original database will not be modified whatsoever?

Thank you!

System: MySQL 5.1 with InnoDB

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Is this procedure correct ?

If you have no foreign key constraints, then yes this is correct.

If you have foreign key constraints, some data may be inaccessible. You would have to fix it.

Can I be sure that the original database will not be modified whatsoever?

After the mysqldump do the following:

grep -c "DROP DATABASE" test.sql

If you get back 0, then yes the original database will not be modified whatsoever

share|improve this answer
    
I will check for any "DROP DATABASE" string. Could be there anything else that modifies/deletes anything else in the production DB, even a single bit? –  Pietro Jun 14 '12 at 8:04
    
Regarding foreign key constraints, since some of them are "CASCADE", others are unspecified, could be a good thing to make a dump of the whole DB, import this dump in a new DB, and then delete part of the records from there so that the constraints can be applied? (I still have to check the size of the DB itself, which should be a critical detail for any answer...). –  Pietro Jun 14 '12 at 11:02
    
The size of the DB is roughly 50 GB. –  Pietro Jun 14 '12 at 15:07
    
mysqldump will only output SQL. The test.sql file cannot affect prodDB while the mysqldump is running. Once the mysqldump is done, DO NOT USE test.sql AGAINST prodDB !!! Otherwise, it will destroy the data or just exit with duplicate key errors. –  RolandoMySQLDBA Jun 14 '12 at 15:12
    
Thank you Rolando! –  Pietro Jun 15 '12 at 13:20
add comment

It's a good idea to create a read only user for backups.

This is along the general principle of not granting a user privileges it doesn't need, to reduce the chances of unexpected side effects.

http://sys-log.bencane.com/2011/12/creating-a-read-only-backup-user-for-mysqldump/

GRANT LOCK TABLES, SELECT ON DATABASE.* TO 'BACKUPUSER'@'%' IDENTIFIED BY 'PASSWORD';

I also generally create a read only user for all my databases which I use for day to day querying, unless I am sure I will need to modify the tables/schema etc. Why be working with a user than can drop the database completely if I make an error, when I just need to select a few rows from tables?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.