首頁 » SQL » mysql

MySQL - Backup

由 datahunter 在 一, 04/03/2013 - 17:21 發表

最後更新: 2020-12-18

工具介紹

 

automysqlbackup

* Email notification

* rotation

* daily, weekly, monthly 的 backup 及 rotation工具

mylvmbackup

obtains a read lock on all tables and flushes all server caches to disk,

makes an LVM snapshot of the volume containing the MySQL data directory

Scripts

一些簡單的 backup script

 

automysqlbackup

 

HomePage:

https://sourceforge.net/projects/automysqlbackup/files/AutoMySQLBackup/

注意:

backup file 內一句 USE `YOUR_DB_NAME`;

安裝:

apt-get install automysqlbackup

獲得:

* /etc/default/automysqlbackup

* /etc/cron.daily/automysqlbackup

* /usr/sbin/automysqlbackup

設定: /etc/default/automysqlbackup

USERNAME="username"
PASSWORD="password"
DBHOST=localhost
DBNAMES="DB1 DB2 DB3"
BACKUPDIR="/backups"
MAILCONTENT="stdout"
MAILADDR="[email protected]"

在 Debain 上的設定比教簡易: 因為好多東西都是設定好了, 比如 USERNAME, PASSWORD, DBNAMES

USERNAME=`grep user /etc/mysql/debian.cnf | tail -n 1 | cut -d"=" -f2 | awk '{print $1}'`
PASSWORD=`grep password /etc/mysql/debian.cnf | tail -n 1 | cut -d"=" -f2 | awk '{print $1}'`
DBNAMES=`mysql --defaults-file=/etc/mysql/debian.cnf --execute="SHOW DATABASES" | 
 awk '{print $1}' | grep -v ^Database$ | grep -v ^mysql$ | tr \\\r\\\n ,\ `

# 每月 backup

MDBNAMES="mysql $DBNAMES"

其他設定

# 星期日為 weekly backup 的日子

DBEXCLUDE=""
DOWEEKLY=6
COMP=gzip

# Command to run before backups (uncomment to use)
#PREBACKUP="/etc/mysql-backup-pre"

# Command run after backups (uncomment to use)
#POSTBACKUP="/etc/mysql-backup-post"

Backup file 的格式:

roundcubemail_2013-03-04_17h05m.Monday.sql.gz

 

mylvmbackup

 

root@server1:~# mylvmbackup --user=root --password=yourrootsqlpassword \
                          --mycnf=/etc/mysql/my.cnf --vgname=server1 --lvname=mysql --backuptype=tar

20120416 19:16:58 Info: Connecting to database...
20120416 19:16:58 Info: Flushing tables with read lock...
20120416 19:16:58 Info: Taking position record...
20120416 19:16:58 Info: Taking snapshot...
File descriptor 3 left open
  Logical volume "mysql_snapshot" created
20120416 19:16:58 Info: Unlocking tables...
20120416 19:16:58 Info: Disconnecting from database...
20120416 19:16:58 Info: Mounting snapshot...
20120416 19:16:59 Info: Copying my.cnf...
20120416 19:16:59 Info: Taking actual backup...
20120416 19:16:59 Info: Creating tar archive /var/cache/mylvmbackup/backup/backup-20120416_191658_mysql.tar.gz

.....................................

20120416 19:17:00 Info: DONE
20120416 19:17:00 Info: Cleaning up...
20120416 19:17:00 Info: LVM Usage stats:
20120416 19:17:00 Info:   LV             VG      Attr   LSize Origin Snap%  Move Log Copy%  Convert
20120416 19:17:00 Info:   mysql_snapshot server1 swi-a- 5.00G mysql    0.00
  Logical volume "mysql_snapshot" successfully removed

 

Provide the password to mysqldump

 

Provide the password to mysqldump

 - provide the password on the command line through the -p option
 - provide the password via the MYSQL_PWD environment variable
 - put your configuration in the ~/.my.cnf file under the [mysqldump] section

sqlpw.key

[mysqldump]                # NEEDED FOR DUMP
# host=server_name_or_IP   # 非必要
user=username
password=password

Usage

mysqldump --defaults-extra-file=/path/to/sqlpw.key DB > DB.sql

--defaults-extra-file 與 --defaults-file

# --defaults-file=filename
# Must be given as first option.

Set filename as the file to read default options from, override global defaults files.

# --defaults-extra-file=filename
# Must be given as first option.

Set filename as the file to read default options from after the global defaults files has been read.

MYSQL_PWD

[方式1]

MYSQL_PWD=xxxx mysql -u root -e "statement"

[方式2]

export MYSQL_PWD=xxxx

 

Scripts

 

Check Login / Server Status

chk_status.sh

#!/bin/bash

USER="root"
PASSWORD="???????????"

function check_login() {
        msg='mysqld is alive'
        result=$(MYSQL_PWD=$PASSWORD mysqladmin -u$USER ping 2> /dev/null)
        if [[ x$result != x$msg ]]; then
                echo "Login Fail"
                exit 1
        fi
        echo "Login Success"
}

check_login

基礎 backup script

功能: backup 單個一 DB 並保留 7 天

準備:

1. save db root 的 pw

touch ~/.my.cnf; chmod 600 ~/.my.cnf

~/.my.cnf

[mysqldump]
password=?

2. 建立存放 backup 的地方

mkdir /home/db_backup/DB_NAME

chmod 700 home/db_backup

Script

daily_backup_db.sh

#!/bin/bash

DB=XXX
DST=/home/db_backup/$DB

# Backup Code
NOW=$(date +%a)     # Tue Wed ...
mysqldump -R $DB --ignore-table=${DB}.EventLog | gzip > ${DST}/${DB}/${NOW}.sql.gz

# for logging
ls -lh ${DST}/${DB}

Verify backup

7za t crmdb.Wed.sql.gz            # yum install p7zip -y

Scanning the drive for archives:
1 file, 213034719 bytes (204 MiB)

Testing archive: crmdb.Wed.sql.gz
--
Path = crmdb.Wed.sql.gz
Type = gzip
Headers Size = 10

Everything is Ok

Size:       2464521055
Compressed: 213034719

backup script

daily_backup_db.sh

#!/bin/bash

DB=MyDbName
ROOT_PW=xxx
KeepWeekly=n                  # n | y
BAKPATH=/backup               # y: /backup/Tue
Host=mysql56
MYSQLDUMP=/usr/bin/mysqldump

#### Code ####
set -e
# Display date for log
date

# MySQL Login
export MYSQL_PWD=$ROOT_PW

# for except tables
ini=$(realpath $0).ini
# KeepWeekly ?
if [ $KeepWeekly == 'y' ]; then
    DAY=`date +%a`
    BAKPATH=${BAKPATH}/$DAY
    echo "Keep backup one weekly: $BAKPATH"
fi

# Create backup path & fix permission
mkdir -p $BAKPATH &> /dev/null
chmod 700 $BAKPATH &> /dev/null
touch $ini
# backup routines & triggers
$MYSQLDUMP --defaults-file=$ini -h $Host \
 --routines --triggers --no-create-db --no-data \
 --lock-tables=FALSE \
 --default-character-set=utf8 \
 "$DB" | gzip > $BAKPATH/${DB}-routines-triggers.sql.gz
# backup data
$MYSQLDUMP --defaults-file=$ini -h $Host \
 --lock-tables=FALSE \
 --skip-triggers \
 --default-character-set=utf8 \
 ${DB} | gzip > $BAKPATH/${DB}-data.sql.gz
# For verify backup size
ls -lh $BAKPATH
echo "Finish"

Except tables

touch daily_backup_db.ini

bakdb.ini

[mysqldump]
ignore-table=MyDbName.EventLog
ignore-table=MyDbName.Module_bak

Remark

--defaults-extra-file

must be the first option

--set-gtid-purged

This option enables control over global transaction ID (GTID) information written to the dump file,

by indicating whether to add a SET @@GLOBAL.gtid_purged statement to the output.

Use ON if the intention is to deploy a new replication replica using only some of the data from the dumped server.

Use OFF if the intention is to repair a table by copying it within a topology.

Backup Script1:

# Backup 特定 DB 及 Keep 指定份數

link1

Backup Script2:

# 單獨 backup 每個 DB 及 Keep 指定天數

link2

 

»
  • 瀏覽次數： 2283

Creative Commons license icon Creative Commons license icon