mysqldump scheduled backup

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"

##xtrabackupfasterfasterrealizes innodb hot backup, There are requirements for storage enginesPowerfulLarge-scale backup

Backup method	Backup speed	Restore speed	Convenience	Function	Generally used for
cp	fast	fast	general, low flexibility	very weak	a small amount of data backup
mysqldump	SLOW	SLOW	General, the difference in storage engines can be ignored	General	Backup of small and medium-sized data
lvm2	fast	fast	General, supports almost hot standby, fast speed	General	Backup of small and medium-sized data

##　

Of course, this article only talks about mysqldump [official document]. If there is an opportunity, I can share other methods with you. 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 activation of the mysqldump command quick, add-drop-table, add-locks, extended-insert, lock-tables

• >: export

• xxxdb: to be Exported database
Note:

Be sure to remember to change the database password to your own, otherwise the export The file that comes out 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->Administrative Tools->Task Scheduler->Create Task

##General

---

##Trigger

(Execute a backup at 3 a.m. every day)

---

Directly Save, then set the final step

Action

---

Find your batch

Process file

, add it and confirm to save, then the task just set will be displayed in the list.

Note:

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

The above is the detailed content of mysqldump scheduled backup. For more information, please follow other related articles on the PHP Chinese website!