sysadmin-scripts/mysql-backup.sh at master · vab/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/MariaDB Databases

# Author:		V. Alex Brennen <vab@cryptnet.net>
# Copyright:	None
# License:		Public Domain
# Date:			2006.10.20
# Dependencies:	Mysql or MariaDB; bzip2

# Description:	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.


# 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
        eval "/bin/mkdir $BACKDIR"
fi

if [ ! -w "$BACKDIR" ]; then
        eval "/bin/chown mysql:mysql $BACKDIR"
        eval "/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
        eval "/bin/mkdir $BACKDIR"
fi

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

# Go to the back up directory
cd "$BACKDIR" || return 1

# 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
	eval "$MYSQLDUMP --opt -h $DB_SRVR --user=$USER --password=$PASS $db > $FILE"

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