1

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

Improve this question

2 Answers 2

Reset to default
1

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

Improve this answer
4
  • 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
    Commented Jun 14, 2012 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
    Commented Jun 14, 2012 at 11:02
  • The size of the DB is roughly 50 GB.
    – Pietro
    Commented Jun 14, 2012 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
    Commented Jun 14, 2012 at 15:12
1

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?

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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