MySQL backup and recovery policy notes (3)

MySQL backup and recovery policy (iii) 12:11:54 big label: knowledge/exploration MySQL backup and recovery MySQL backup policy MySQL recovery policy mysqldailybackup. Sh note:

#! /Bin/sh
# Name: mysqldailybackup. Sh
# Ps: MySQL database daily backup.
# Write by: I. Stone
# Last modify: 2007-11-17
#
# Define variables. Modify the variables as needed
# Define database directories and Data Directories
Scriptsdir = 'pwd'
Mysqldir =/usr/local/MySQL
Datadir = $ mysqldir/Data
# Define the username and password used to back up the database
User = root
User Pwd = 111111
# Define the Backup Directory to back up files to $ databackupdir/daily on a daily basis
Databackupdir =/tmp/mysqlbackup
Dailybackupdir = $ databackupdir/daily
# Define the mail body File
Emailfile = $ databackupdir/email.txt
# Define email addresses
Email = alter@somode.com
# Define log files
Logfile = $ databackupdir/mysqlbackup. Log
# Obtain the Host Name of the Database Host
Hostname = 'uname-N'
#
Echo ""> $ emailfile
Echo $ (date + "% Y-% m-% d % H: % m: % s") >>$ emailfile
#
# Refresh the log so that the database can use the new binary log file
$ Mysqldir/bin/mysqladmin-U $ user-p $ userpwd flush-logs
CD $ datadir
# Obtain the binary log list
Filelist = 'cat $ HOSTNAME-bin.index'
Icounter = 0
For file in $ filelist
Do
Icounter = 'expr $ icounter + 1'
Done
Nextnum = 0
Ifile = 0
For file in $ filelist
Do
Binlogname = 'basename $ file'
Nextnum = 'expr $ nextnum + 1'
# Skip the last binary log (the binary log file currently used by the database)
If [[$ nextnum = $ icounter]; then
Echo "Skip lastest! ">/Dev/null
Else
DeST = $ dailybackupdir/$ binlogname
# Skip the binary log file that has been backed up
If [[-e $ DEST]; then
Echo "Skip exist $ binlogname! ">/Dev/null
Else
# Back up log files to the backup directory
CP $ binlogname $ dailybackupdir
If [[$? = 0]; then
Ifile = 'expr $ ifile + 1'
Echo "$ binlogname backup success! ">>$ Emailfile
Fi
Fi
Fi
Done
If [[$ ifile = 0]; then
Echo "No BINLOG backup! ">>$ Emailfile
Else
Echo "backup $ ifile file (s)." >>$ emailfile
Echo "backup MySQL BINLOG OK! ">>$ Emailfile

# If you do not need to transmit the backup to the backup server or the backup server is windows, comment out the lines marked green
# Move backup files to backup server.
# Suitable for Linux (MySQL Server) to Linux (Backup Server)

$ Scriptsdir/rsyncbackup. Sh
If [[$? = 0]; then
Echo "Move backup files to backup server success! ">>$ Emailfile
Else
Echo "Move backup files to backup server fail! ">>$ Emailfile
Fi
Fi
# Send email notification
Cat $ emailfile | mail-s "MySQL backup" $ email
# Write a log file
Echo "--------------------------------------------------------"> $ logfile
Cat $ emailfile> $ logfile

 

Rsyncbackup. Sh note:

#! /Bin/sh
# Name: rsyncbackup. Sh
# Ps: Move backup files to backup server.
# Write by: I. Stone
# Last modify: 2007-11-17
#
# Modify the configuration according to the actual situation. Note that "/" is available at the end.
# Define the Database Backup Directory
Databackupdir =/tmp/mysqlbackup/
# Define the directory on which backup data is stored on the backup server
Backupserverdir =/root/mysqlbackup/
# Define Backup Server
Backupserver = 192.168.0.200
#
# Synchronizing backup files to the backup server
Rsync-A -- delete $ databackupdir-e SSH $ backupserver: $ backupserverdir>/dev/null 2> & 1

Rmbackup. Sh notes:

#! /Bin/sh
# Name: rmbackup. Sh
# Ps: delete old backup.
# Write by: I. Stone
# Last modify: 2007-11-15
#
# Define the Backup Directory
Databackupdir =/tmp/mysqlbackup
# Deleting the log backup file of mtime> 2
Find $ databackupdir-name "MySQL _ *. GZ"-type F-mtime + 2-exec RM {}/;>/dev/null 2> & 1

(5) restore the database to the backup status

The file backed up with mysqldump is an SQL script that can be directly imported into the database. You can import the file directly using the mysql client.

/Usr/local/MySQL/bin/MySQL-uroot-puserpwd db_name <db_name. SQL

For any applicable update logs, use them as MySQL input:

% Ls-t-r-1 hostname-Bin * | xargs mysqlbinlog | mysql-uuser-puserpwd

The LS command generates a single column list of update log files and sorts them by the server's order (Note: If you modify any file, you will change the sorting order, this will cause the Update log to be used in the wrong order .)

This backup policy can only restore the database to the state of the last backup. If you want to lose as little data as possible during the crash, you should back up the database more frequently, to restore data to the state of the crash, use the master-slave replication mechanism (replication ). If you use this backup script to place log files and data files on different disks, this not only improves data writing speed, but also improves data security.

? /P>

 

Backup policy 4. Master-slave Replication)

Details: Click to read

Script: Click to download the script

 

Article reference: Comments (2) reference read (195) circle edit print prize report previous article: MySQL backup and recovery policy (2) next article: Achieve DNS-Based Load Balancing