sysadmin-scripts/backup_mysql.sh at master · tim-rix/sysadmin-scripts · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
#!/bin/bash

# Shell script to backup MySQL databases
#
# This script will create a list of all databases that exist on a
# MySQL database server and dump the databases one by one each to
# its own compressed, named, and dated, SQL file.

# Author:        V. Alex Brennen <vab@MIT.EDU>
# Copyright:	 None
# License:       Public Domain
# Date:          2006.10.20


# Locations of Programs we'll be using
MYSQL="/usr/bin/mysql"
MYSQLDUMP="/usr/bin/mysqldump"
BZIP2="/usr/bin/bzip2"
NICE="/bin/nice"

# The argument to specify a compression level if required
CLVL="-9"

# Use nice
BE_NICE=1

# Nice level
NLVL=19

# Directory to store the backups in
BACKDIR="/home/backup/mysql"

# Set Database Sever Address
DB_SRVR="127.0.0.1"
DB_SRVR_NAME="$(hostname)"

# Set Username
USER="root"

# Set Password
PASS=""

# Get the date information that we'll use for the backup directories and
# to construct the backup file names
YEAR="$(date +"%Y")"
MONTH="$(date +"%m")"
DAY="$(date +"%d")"

# Make Sure the back-up directory for the current year exists and is
# writable by the back-up script.
BACKDIR="$BACKDIR/$YEAR"

if [ ! -d "$BACKDIR" ]; then
        /bin/mkdir $BACKDIR
fi

if [ ! -w "$BACKDIR" ]; then
        /bin/chown mysql:mysql $BACKDIR
        /bin/chmod 755 $BACKDIR
fi


# Make sure the back-up directory for the current month exists and is
# writable by the back-up script.
BACKDIR="$BACKDIR/$MONTH"

if [ ! -d "$BACKDIR" ]; then
        /bin/mkdir $BACKDIR
fi

if [ ! -w "$BACKDIR" ]; then
        /bin/chown mysql:mysql $BACKDIR
        /bin/chmod 755 $BACKDIR
fi

# Go to the back up directory
cd $BACKDIR

# Construct the date information to be used in the backup filename.
DATE="$YEAR$MONTH$DAY"

# Get a list of all the dbs
DBS="$($MYSQL -h $DB_SRVR --user=$USER  --password=$PASS --silent --batch --execute='show databases')"

# Iterate through the database list
for db in $DBS
do
	# Set the filename
	FILE="$db.$DB_SRVR_NAME.$DATE.sql"

	# Dump the data
	$MYSQLDUMP --opt -h $DB_SRVR --user=$USER --password=$PASS $db > $FILE

	# Compress the dump file
	if [ $BE_NICE -eq 1 ]; then
		$NICE -$NLVL $BZIP2 $CLVL $FILE
	else
		$BZIP2 $CLVL $FILE
	fi
done