What is the best way to do testing database (MYSQL spesific)

Question

Right now i'm on testing something in a database. It's a wordpress database. i have to write and delete and do other operation on it. As you know it, it has indexing mechanism that will always make every new post inherit the next highest possible ID.

Please consider that this database is a copying of used database. it has been written before. So, i will need to make sure when i finish my testing, it will be the same

Right now, my only solution is making backup. So if i have end in some section of planned testing, i will backup it and start next testing on another copy of it.

Fortunately, the size of database is only a small one. so delete and copy and backup it will be easy. but i know this way of database testing is only partial solution.It force me to create too many backup copy. I don't know what i will do if the database has bigger size. it will be a very long of testing nightmare.

so i wonder is there any solution that work just like rollback. So it will just lock the database and just put new entry as some kind of cache. I can erase it or write it into the database.

i use mysql and phpmyadmin and use it to developed some custom solution.

EDIT ::: How to effectively doing testing on database when developing PHP solution ?

Answer 1

After you delete the record, when you insert the new record, if you set the id to the number you want, rather than leaving it blank, it should just fill that index again.

Answer 2

If your file system supports writeable snapshots - LVM, ZFS, Veritas, etc. - you can take an instant copy of the entire database partition, mount that in another place, start a new instance of MYSQL which uses the snapshot, perform your testing, remove your snapshot - all without disturbing your replica.

The snapshot only needs storage for the amount of data that gets changed during your testing, and so might only need a few GB.

Answer 3

Is the post number an autoincrement field? If not, hack into WordPress (temporarily) and look for the code where the new posts are stored. Before saving why don't you add a constant e.g. 1000 to post id number. When you are finished with your testing simply delete the id numbers that are greater than your constant.