MySQL backup solution under FreeBSD

Core tips: how to implement full and incremental MySQL backup under FreeBSD to ensure maximum data reliability. MySQL replication and the new version of FreeBSD's Integrated snapshot function are used here.

 
Convention: This backup scheme is executed on the slave server of MySQL replication. MySQL is installed in/usr/local/mysql, and datadir is/data/mysql, while logs and innodb log_file are stored in/logs/mysql. The directory used to save the backup file is/backup/mysql/. The backup solution is divided into full backup once a day and Incremental backup once an hour. Therefore, if data is lost due to incorrect MySQL operations or other situations, the worst case may be less than one hour of data generated when this solution is adopted. Of course, real-time Incremental Backup can also be implemented, which will be discussed in future articles.
I. Full backup
Let's take a look at the full backup script:
 
#! /Bin/sh
#
# Created by yejr, 2007/06/03
#
# This script is used for regular full backup. The backup object is all data on slave.
# Run "stop slave; flush tables;" before each backup, and copy all the files to the backup directory.
#
 
Echo "[backupmysql_fullly]"
Date
 
Today = 'date + "% Y _ % m _ % d "'
 
Logdir =/logs/mysql
Datadir =/data/mysql
Bkdir =/backup/mysql/$ today/full_backup
Expdays = 7
 
If [! -D $ bkdir/$ today]; then
Mkdir-p $ bkdir
Fi
 
# Stop synchronization, refresh logs, and refresh tables
Echo "stop slave; flush logs; flush tables ;"
Mysqladmin-uroot stop-slave
Mysqladmin-uroot flush-logs
Mysqladmin-uroot flush-tables
 
Cd $ logdir
# Backing up log files
Echo "backup errro log"
Cp $ logdir/error. log * $ bkdir/
 
Echo "backup ib_logfile * master.info relay.info"
Cp ib_logfile * master.info relay.info $ bkdir/
 
# Copying all objects
Echo "backup my. cnf"
Cp/usr/local/mysql/my. cnf $ bkdir/
 
# If FreeBSD of the old version does not support the snapshot function, copy all database files
# Cd $ datadir
# Cp-rf * $ bkdir/
 
# If it is a new version of FreeBSD, it uses its unique snapshot function to create an image first, then attach the image, and then copy the data file.
Echo "make snapshot of/backup"
/Sbin/mksnap_ffs/data // data/. snap/mysql_snap _ $ today
 
# Run slave-start;
Mysqladmin-uroot start-slave
 
# Mount the image file and then copy the data
/Sbin/mdconfig-a-t vnode-f/data/. snap/mysql_snap _ $ today-u 4
/Sbin/mount-r/dev/md4/backup/. snap
Cp-rf/backup/. snap/mysql/data/* $ bkdir/
/Sbin/umount/backup/. snap
/Sbin/mdconfig-d-u 4
 
# Deleting expired files 7 days ago
Find/backup/mysql-type d-mtime + $ expdays-maxdepth 1 | xargs rm-rf
Find/data/. snap-type f-mtime + $ expdays-maxdepth 1 | xargs rm-f
 
# Modifying the owner of the backup file
Chown-R nobody: nobody $ bkdir
 
Echo "[/backupmysql_fullly]"
 
Next, add a record to the crontab so that the backup script is executed every day.
 
0 0 *** (/bin/sh/backup/mysql/backupmysql_fullly.sh>/backup/mysql/backupmysql. log) 2. Incremental backup
The following is an incremental backup script:
 
#! /Bin/sh
#
# Created by yejr, 2007/06/03
#
# This script is used for regular Incremental backup. The backup object is binlog.
# Run flush-logs before each backup, and then move the latest binlog to the backup directory.
#
 
Echo "[backupmysql_hourly]"
Date
 
Logdir =/logs/mysql
Bkdir =/backup/mysql
Now = 'date + "% Y _ % m _ % d _ % H "'
Today = 'date + "% Y _ % m _ % d "'
 
If [! -D $ bkdir/$ today]; then
Mkdir-p $ bkdir/$ today
Fi
 
# Run "flush logs"
Echo "flush logs; backup error log"
Mysqladmin-uroot flush-logs
 
# The total number of deprecated binlog files. Remove the last and binlog. index files.
Total = 'ls $ logdir/logbin. * | wc-l'
Total = 'expr $ total-2'
 
# Move all binlog files cyclically
Echo "backup binary logs"
For f in 'ls $ logdir/logbin. * | head-n $ total'
Do
Bf = 'basename $ F'
Mv-if $ f $ bkdir/$ today/$ bf
Done
 
Chown-R nobody: nobody $ bkdir
Echo "[/backupmysql_hourly]" then adds a record to the crontab so that the backup script is executed hourly.
 
0 */'*** (/bin/sh/backup/mysql/backupmysql_hourly.sh>/backup/mysql/backupmysql. log) in linux, you can also use this backup scheme. However, the backup policy does not support snapshot, that is, copying all files. Alternatively, you can use linux's built-in lvm function to implement snapshot functions similar to FreeBSD.
The above script is used in the FreeBSD 6.2 + MySQL 5.0.37 environment.
 
Use cron to automatically back up the MySQL database at every day. The file name is named after the year, month, and day of the current day. The database name is prefixed with the name. After the backup, the backup of the previous six days is automatically deleted.
1. Create a backup directory. Take user as an example.
$ Mkdir/usr/home/user/dbbak
$ Mkdir/usr/home/user/dbbak/user_db
2. Write the/usr/home/user/dbbak. sh script. DBName = database name BackupPath = Backup Directory DBbinPath = directory of utilities such as mysqldump
 
#! /Bin/sh
DBName = user_db
BackupPath =/usr/home/user/dbbak/
DBbinPath =/usr/local/bin/mysqldump
If $ {DBbinPath} -- opt -- extended-insert = false-uroot $ {DBName} >$ {BackupPath }$ {DBName} "/" $ {DBName} 'date "+ % y-% m-% d "'". SQL ";
Then
Find $ {DBbinPath }$ {DBName} "/"-mtime + 6-exec rm {};
Else
Exit
Fi
3. Add dbbak. sh to cron
# Add the following line to vi/etc/crontab:
0 2 *** root/usr/home/user/dbbak. sh
 
 
 
Bytes ---------------------------------------------------------------------------------------------------
 
Use cron to automatically back up the MySQL database at every day. The file name is named after the day of the day, and the prefix is the database name. After the backup, the backup of the previous six days is automatically deleted.
1. Create a backup directory. Take the user shaobing as an example.
$ Mkdir/home/shaobing/dbbak
$ Chmod 755/home/shaobing/dbbak // make sure the file is writable.
 
$ Mkdir/home/shaobing/dbbak/rd_db
$ Chmod 755/home/shaobing/dbbak/rd_db // make sure this file is writable.
 
2. Write the/home/shaobing/dbbak. sh script. DBName = database name BackupPath = Backup Directory DBbinPath = directory of utilities such as mysqldump
 
#! /Bin/sh
DBName = npc_people
BackupPath =/home/shaobing/dbbak/rd_dbbak/
DBbinPath =/usr/local/bin/mysqldump
If $ {DBbinPath} -- opt -- extended-insert = false-uroot $ {DBName} >$ {BackupPath }$ {DBName} "/" $ {DBName} 'date "+ % y-% m-% d "'". SQL ";
Then
Find $ {DBbinPath }$ {DBName} "/"-mtime + 6-exec rm {};
Else
Exit
Fi
3. Add dbbak. sh to cron
# Add the following line to vi/etc/crontab:
# Back up data at six o'clock P.M. every day and delete the backup files from the previous six or six days
0 18 *** root/home/shaobing/dbbak. sh
28 11 * root/home/shaobing/dbbak. sh
 
Bytes ------------------------------------------------------------------------------------------
 
 
Copy database files directly
Write cp-r/var/db/mysql/npc_people /.../.......
 
#! /Bin/sh
Mkdir/home/shaobing/dbbak/rd_dbbak/'date "+ % Y-% m-% d "'/
Cp-r/var/db/mysql/npc_people // home/shaobing/dbbak/rd_dbbak/'date "+ % Y-% m-% d "'/