how do i automated MySQL data base restore everyday with crontab?

Question

i have some demo website user can register and login and share post add products with some function but its for demo purpose only so daily 10 to 15 people register and test how its work

but i don't need everyone data i have new mysql.sql file in this SQL don't have any much data i want to do automate task with crone tab

every day its will delete current database and upload my mysql.sql file

how can i do this?

os:ubuntu 19.04

Answer 1

Since all you want to do is "reinstall" your DB on a daily basis (is that correct?). You can add to your install script on the first line:

          DROP database <your databese>
          # Here you re-create your DB again (your current sql script)

Let's say you call this script "reinstall.sql"; you can add to your cron table the following line (which runs the command everyday @ 1am):

 0 1 * * *  mysql -u username -p password database_name < /path/to/your/reinstall.sql

To open the cron table you can do this:

 sudo crontab -l -u [USER_WITH_RIGHTS_TO_EDIT]

Hope it helps!

Answer 2

MySQL keeps all DB data inside one directory. By default this directory resides within the MySQL installation and is called data. For example if your default installation is at c:/Users/prakash/mysql-8.0.17, a directory named data will be available inside it.

In principle, you will have to keep a fresh copy of this data directory (without any online user information, as it was when you built the database first time by running DDL scripts) somewhere, let us say at c:/Users/prakash/mysql-8.0.17/fresh. You can then write a crone job to achieve following and schedule it at any convenient time.

1. Shutdown the database
2. Delete data directory (recursively)
3. Copy fresh directory (recursively) where data directory resides
4. Rename the copied directory to data
5. Restart the database

Answer 3

Initially, if your database contains stored procedure as well, just make sure your restore file contains query to delete the stored procedure as well before restoring the database.

To delete stored procedures add the following line in your mysql.sql file.

DELETE FROM mysql.proc WHERE db like '%{{database_name}}%' AND type = 'PROCEDURE'

After this you have to add a cron job which will restore your database everyday, to do this open terminal and type sudo crontab -e

Now enter 0 13 * * * mysql -u {{user_name}} -p {{password}} {{database_name}} < {{path_to_your_sql_file}} assuming you have to restore the database at 1 PM daily.

After adding the job, save the file.

Once the job is added you can check the it by typing sudo crontab -l in terminal