Reset the database after 3 hrs & make it behave as a new database through php script

How to reset the database after 3 hrs & make 开发者_如何学JAVAit behave as a new database through php script

Possibly the easiest way would be to have a cron job that executes every three hours and calls mysql with "clean" database set up. The crontab set up would be something along the lines of:

* */03 * * * mysql -u XXX -pXXX < clean_database.sql

However, the "clean_database.sql" file would need to use "DROP TABLE IF EXISTS ..." for each of the tables you want to reset. That said, you can simply use mysqldump with a "known good" version of the database to create this file. (You'll need to add a "use <database name>;" statement at the top that said.)

The easiest way is to drop the database and recreate it using your create scripts. If you don't have create scripts you can get them by making a dump of your database.

To delete the data in each table without dropping the tables you can use the TRUNCATE TABLE tablename command on each table.

If you don't have permission to use TRUNCATE you can use DELETE FROM tablename without a WHERE clause.

Note that if you have foreign key constraints you may have to run the statements in a specific order to avoid violating these constraints.

To get a list of all tables you can use SHOW TABLES.

steps to do:

1. connect to database server
2. select database
3. mysql_query("SHOW TABLES");
4. read in array or object
5. foreach($tables as $tableName) of the item mysql_query("TRUNCATE TABLE $tableName")

I hope the principle is clean to you ;-)

mysql_query('DROP DATABASE yourdatabase');
mysql_query('CREATE DATABASE yourdatabase');
mysql_query('CREATE TABLE yourdatabase.sometable ...'); // etc.

This will drop the database, and create it anew. You can then use the CREATE TABLE syntax to recreate the tables - note that as this script has significant powers, you should consider creating a special mySQL user for it, one that's not used during normal operations.