Detailed introduction to scheduled data backup in mysqldump (picture and text)

MYSQL There are many types of database backups (cp, tar, lvm2, mysqldump, xtarbackup), etc. Which one to use depends on the size of your data. A table is given below

#Excerpted from "Learn to back up Mysql database in various postures"

##cpmysqldumplvm2xtrabackup

##Backup method	Backup speed	Restore speed	Convenience	Function	Generally used for
fast	Fast	General, low flexibility	Very weak	Small amount of data backup
SLOW	SLOW	General, the difference in storage engines can be ignored	General	Backup of small and medium-sized data volumes
Fast	Fast	General, supports almost hot standby, fast speed	General	Small and medium-sized Backup of data volume
faster	faster	realizes innodb hot backup and requires storage engine	Powerful	Large-scale backup

##　

Of course, this article only talks about mysqldump [official document], other methods can be shared with you if you have the opportunity. When using mysqldump for backup, my method is relatively simple. It is done using batch processing + timing.

Mysqldump backup means to save the required statements.

Main features

:

Adapts to all mysql engines, supports warm backup, full backup, partial backup (supports InnoDB hot backup)

1. First write the shell script

rem *******************************start*****************************@echo offset "Ymdhms=%date:~0,4%%date:~5,2%%date:~8,2%_%time:~0,2%%time:~3,2%%time:~6,2%"
C:\myserver\mysql-5.7.10-winx64\bin\mysqldump --opt -u root --password=123456 xxxdb > 
C:\myserver\mysql-5.7.10-winx64\db_backup\backup_%Ymdhms%.sql@echo onrem *******************************end*****************************

Instructions

rem: Remarks

• Ymdhms: Time

• --opt: Represents the quick, add-drop-table, add-locks, extended-insert that activates the mysqldump command , lock-tables

• >: export

• xxxdb: database to be exported
Note:

Be sure to remember to change the database password to your own, otherwise the exported file will be empty
If you export a certain table, just write the table name after the database. For example:...xxxdb xxxtable > C:\mysql...
Copy the above shell script and make it into a .bat batch script and put it in the appropriate location.

Restore as follows:

mysql>source C:\xxx.sql

2. Windows Scheduled Tasks

Open: Control Panel->Management Tools->Task Scheduler->Create Task

##General

---

Trigger

(Execute a backup at 3 am every day)

---

Save directly and then set the last step

Operation

---

Find your batch

processing file, add it and confirm to save, then list it The task just set is displayed.

Note: The most important thing is don’t forget to turn on

##

The above is the detailed content of Detailed introduction to scheduled data backup in mysqldump (picture and text). For more information, please follow other related articles on the PHP Chinese website!