Resetting MYSQL Database with Original Data Using Cron Job

The idea here is to run a Linux cron job to reset a mySQL database at the desired interval. In the case of this tutorial, the interval is every two hours.

The code below clearly shows how this can be done in its entirety. It is a cron file for the user named myUser. Although it appears that it has 4 cron sections, note that to are commented out. Also, note that the top two lines of cron commands and bottom two are for 2 different databases.

The top two cron jobs have backticks with backslashes that are required for database names with hyphens, but the latter two lines use underscores where they are not required.

So, you may notice that the top line that is commented out is nice and easy. But, you may also find that it is only useful when you want to dump data into an empty database. Thus, the uncommented option, will run two mysql commands that drop and create a database for the desired user 'myUser', then dumps the data from a sql file into the database.

That is all there is to it.


#0 */2 * * * mysql -u myUser -pmyPassword myUser_my-DB < /home/myUser/cron/db.sql >/dev/null 2>&1

0 */2 * * * mysql -u myUser -pmyPassword -e "DROP DATABASE \`myUser_my-DB\`; CREATE DATABASE \`myUser_my-DB\`";  mysql -u myUser -pmyPassword myUser_my-DB < /home/myUser/cron/db.sql >/dev/null 2>&1

#0 */2 * * * mysql -u myUser -pmyPassword myUser_my_db < /home/myUser/cron/db.sql >/dev/null 2>&1

0 */2 * * * mysql -u myUser -pmyPassword -e "DROP DATABASE myUser_my_db; CREATE DATABASE myUser_my_db"; mysql -u myUser -pmyPassword myUser_my_db < /home/myUser/cron/db.sql >/dev/null 2>&1