MySQL Database maintenance

example
Database Server IP	192.168.2.170
User name	Root
Password	123456
Database name	submetering
Backup copies	7 (only the last 7 times of backup data are retained.) Keep historical data for the last 7 days if the scheduled task is executed once a day)
Backup frequency and time	Every night 10 o'clock
Backup path	C:\Submetering\backup

I. Database backup

1. Backup Scripts

Create a new backup script as a text file Backupmysql.bat, the following is the contents of Backupmysql.bat:

1Echooff2 3 ifexist Mark_a.txt (4 5Echo 2nd Day Backup6 del mark_a.txt7Dir>Mark_b.txt8Mysqldump-H192.168.2.170 -U root-p123456-R-E submetering>Submetering2.sql9rem rar A-DF Backup_2.rar backup_2.dmp backup_2.LogTen  One)Else ifexist Mark_b.txt ( A  -Echo 3rd Day Backup - del mark_b.txt theDir>Mark_c.txt -Mysqldump-H192.168.2.170 -U root-p123456-R-E submetering>Submetering3.sql -rem rar A-DF Backup_3.rar backup_3.dmp Backup_3.Log -  +)Else ifexist Mark_c.txt ( -  +Echo 4th Day Backup A del mark_c.txt atDir>Mark_d.txt -Mysqldump-H192.168.2.170 -U root-p123456-R-E submetering>Submetering4.sql -rem rar A-DF Backup_4.rar backup_4.dmp Backup_4.Log -  -)Else ifexist Mark_d.txt ( -  inEcho 5th Day Backup - del mark_d.txt toDir>Mark_e.txt +Mysqldump-H192.168.2.170 -U root-p123456-R-E submetering>Submetering5.sql -rem rar A-DF Backup_5.rar backup_5.dmp Backup_5.Log the  *)Else ifexist Mark_e.txt ( $ Panax NotoginsengEcho 6th Day Backup - del mark_e.txt theDir>Mark_f.txt +Mysqldump-H192.168.2.170 -U root-p123456-R-E submetering>Submetering6.sql Arem rar A-DF Backup_6.rar backup_6.dmp Backup_6.Log the  +)Else ifexist Mark_f.txt ( -  $Echo 7th Day Backup $ del mark_f.txt -Dir>Mark_g.txt -Mysqldump-H192.168.2.170 -U root-p123456-R-E submetering>Submetering7.sql therem rar A-DF Backup_7.rar backup_7.dmp backup_7.Log - Wuyi)Else ( the    -Echo 1st Day Backup Wu   ifexist mark_g.txt del mark_g.txt -Dir>Mark_a.txt AboutMysqldump-H192.168.2.170 -U root-p123456-R-E submetering>Submetering1.sql $rem rar A-DF Backup_1.rar backup_1.dmp Backup_1.Log -  -)

Backupmysql.bat End of content

2. Configure Scheduled Tasks

Step One: Enter the operating system Timer task configuration Interface

Step Two: Create a timed task

Step three: Enter a timed task name

Step four: Click on the "Tiggers" tab, select the New button, in the pop-up box to configure the task trigger parameters, every night 10 o'clock to execute

Step five: Click on the "Actions" tab, select the New button, configure the backup script path and backup directory in the pop-up box

Step Six: Check whether the backup directory produces a database backup file after the scheduled task configuration time

Two. Database restore

1. Configure Environment variables

Step one: Computer Right-click Properties to open the Computer property window

Step Two: Open the Advanced System Configuration window

Step three: Open the Environment Variables window and add the MySQL Bin directory path to the "path" variable value

Step Four: Save

2. Restore

Step one: Back up the database

Open Windows Command Window (no spaces after-p)

1 - 192.168. 2.170 - - - - > C:\Submetering\Backup \submeteringbackup.sql

Step two: Restore the database

Open the backup directory and check to see if the backup directory produces a database backup file.

If you do not have a file with the extension "SQL", or if the file is 0KB, the backup is unsuccessful, please recheck the backup script and the scheduled task configuration without continuing the database restore operation.

If the backup succeeds, it is sorted by the modified time, and the file that was last backed up is restored. For example, the file path for the most recent backup is "C:\Submetering\backup \submetering2.sql".

1) Open Windows Command window, connect MySQL database (no space after-p)

1 - 192.168. 2.170 - -p123456

2) Delete the database (not reversible after deletion, please confirm that the backup file succeeds and continue operation)

1 Drop database submetering;

3) Create a database

1 Create Database character set UTF8;

4) Import Backup data

Select the database you want to restore

1  use submetering;

Set character sets

1 set names UTF8;

Restore or import a database

1 Source C:\Submetering\backup \submetering2.sql

MySQL Database maintenance