Use mysqldump to regularly back up the database script _ MySQL

Use mysqldump to regularly back up the database script mysqldump

Script Description

All data is backed up once every 7 days, and binlog is backed up every day, that is, incremental backup.

(If there is little data, you can back up the complete data once a day, and there may be no need for incremental backup)

I am not familiar with shell scripts, so I am very stupid in many places :)

Enable bin log

In mysql 4.1, only error logs are generated by default, and no other logs exist. you can modify the configuration to open the bin log. there are many methods, one of which is in/etc/my. add the mysqld part in cnf:

[Mysqld]

Log-bin

This log is mainly used for incremental backup or replication (it may be used for other purposes ).

To perform incremental backup, you must enable this log.

For mysql with frequent database operations, this log will become very large and there may be multiple logs.

Flush-logs in the database, or use mysqladmin and mysqldump to call flush-logs and use the delete-master-logs parameter. These log files will disappear, and generate a new log file (which is empty at the beginning ).

Therefore, it is unnecessary to enable the log if the backup is never performed.

You can call flush-logs at the same time as the full backup. before the incremental backup, flush-logs is used to back up the latest data.

Full Backup Script

If there is a large amount of database data, we usually back up the data once a day or a week to avoid affecting application operation. if the data volume is small, it doesn't matter if we back up the data once a day.

Download assume that we have a large amount of data, and the backup script is as follows: (refer to the mysql backup script on the network, thanks :))

#! /Bin/sh
# Mysql data backup script
# By scud http://www.jscud.com
#2005-10-30
#
# Use mysqldump -- help, get more detail.
#
BakDir =/backup/mysql
LogFile =/backup/mysql/mysqlbak. log
DATE = 'date + % Y % m % d'
Echo ""> $ LogFile
Echo ""> $ LogFile
Echo "-----------------------------------------"> $ LogFile
Echo $ (date + "% y-% m-% d % H: % M: % S") >>$ LogFile
Echo "--------------------------"> $ LogFile
Cd $ BakDir
DumpFile = $ DATE. SQL
GZDumpFile = $ DATE. SQL. tgz
Mysqldump -- quick -- all-databases -- flush-logs
-- Delete-master-logs -- lock-all-tables> $ DumpFile
Echo "Dump Done" >>$ LogFile
Tar czvf $ GZDumpFile $ DumpFile >>$ LogFile 2> & 1
Echo "[$ GZDumpFile] Backup Success! ">>> $ LogFile
Rm-f $ DumpFile
# Delete previous daily backup files: files backed up in incremental mode. if the backup is complete, the files backed up in incremental mode are deleted.
Cd $ BakDir/daily
Rm-f *
Cd $ BakDir
Echo "Backup Done! "
Echo "please Check $ BakDir Directory! "
Echo "copy it to your local disk or ftp to somewhere !!! "
Ls-al $ BakDir