Practice
I often have to dump databases over ssh, and I wanted to make that a bit simpler. I couldn't find a 100% fitting solution, so I wrote a small script of my own — it searches for all the databases on the server and lets you pick one to back up (into a gz archive that the dump gets packed into):
|
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
|
#!/usr/local/bin/bash
# MySQL backup script
# Copyright (c) 2012 Roman Gushel
# ---------------------------------------------------------------------
#ulimit -t 3600
#user name
USER="user"
#user password
PASSWORD=" password "
#MYSQL host address ("localhost" by default)
HOST="host"
#dirrectory for backups
DIR="dir"
GZIP="$(which gzip)"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
NOW=$(date +"%Y_%m_%d_%H-%M")
echo "Choose database number:"
i=0
for db in $($MYSQL -u$USER -h$HOST -p$PASSWORD -Bse 'show databases')
do
echo "$i - $db"
DATABASES[$i]=$db
i=$((i+1))
done
read RESULT
if [ "$RESULT" -ge 0 ] && [ "$RESULT" -lt ${#DATABASES[@]} ]
then
eval "DB=${DATABASES[$RESULT]}"
eval "FILE=$DIR$DB-$NOW.sql.gz"
$MYSQLDUMP -u$USER -h$HOST -p$PASSWORD $DB | $GZIP -9 > $FILE
else
echo "Wrong number!"
fi
|
If the database being dumped is large, you may run into the error Cputime limit exceeded: [current max cpu time]. To fix this, you should raise the maximum CPU time in seconds using the ulimit command with the -t flag, but that's not always possible, since this command may require elevated privileges, and running it will simply return an error.
Settings you need to specify:
USER=”user” – username
PASSWORD=” password ” – the user's password
HOST=”host” – the host the DBMS is running on
DIR=”dir” – the directory the dumps will be saved to
Comments