Automatic database backup help script not working

hargi

hargi

hargi

Associate
Joined
19 Jul 2006
Posts
1,847
Hi,
I have found this code on another website and tried editing it for my use

Code: 
#!/bin/bash
unset PATH

# USER VARIABLES
MYSQLUSER=root					# The mysql user
MYSQLPWD=******					# The mysql user password
MYSQLHOST=localhost				# This should stay localhost
MYSQLBACKUPDIR=/root/bin/mysql_backup			# A temporary folder where the backupped databases will stay (don't worry, they will be mirrored later

# PATH VARIABLES
MK=/bin/mkdir;								# Location of the mk bin
RM=/bin/rm;								# Location of the rm bin
GREP=/bin/grep;								# Location of the grep bin
MYSQL=/usr/bin/mysql;							# Location of the mysql bin
MYSQLDUMP=/usr/bin/mysqldump;						# Location of the mysql_dump bin
DATABASEBACKUPLOG=/var/log/databasebackup.log				#change as needed

##                                                      ##
##      --       DO NOT EDIT BELOW THIS HERE     --     ##
##                                                      ##


# CREATE MYSQL BACKUP
# Remove existing backup dir - because we backuped the files before onto our backup server, this is safe to do!
$RM -Rf $MYSQLBACKUPDIR
# Create new backup dir
$MK $MYSQLBACKUPDIR

# Stop MySQLdatabase
/etc/init.d/mysql stop
echo `date + \%d/%m/%y.%H:%M:%S ` >> $DATABASEBACKUPLOG
echo 'Stopping MySQL database.' >> $DATABASEBACKUPLOG

#Dump new files
for i in $(echo 'SHOW DATABASES;' | $MYSQL -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST|$GREP -v '^Database$'); do
  $MYSQLDUMP                                                    \
  -u$MYSQLUSER -p$MYSQLPWD -h$MYSQLHOST                         \
  -Q -c -C --add-drop-table --add-locks --quick --lock-tables   \
  $i > $MYSQLBACKUPDIR/$i.sql;
done;

#Start MySQLDatabase
/etc/init.d/mysql restart
echo `date +\%d/%m/%y.%H:%M:%S`  >> $DATABASEBACKUPLOG
echo 'Starting MySQL database.' >> $DATABASEBACKUPLOG

A few problems occurred
1. It does not print the date in the log
But the bigger one is it does not back up the database It creates the /root/bin/mysql_backup folder but nothing appears in there.
I have run chmod +x /etc/cron.daily/mysql_backup.sh and chmod +x /var/log/databasebackup.log

The error that i get is /ect/cron.daily/mysql_backup.sh: line 31: No such file or directory
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

I added the lines to stop the mysql server as i thought that would be safer than to try to back up a database that is in use.

Any suggestions
 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
Click to expand...
That error suggests either the database server isn't running, there's been an authentication failure or the socket is located elsewhere, I'd try connecting with mysql on the command line to debug that.

As you're doing the backup on the database server itself, mysqlhotcopy will save you stopping/starting the database server whilst running backups. MySQL's init script can sometimes be /etc/init.d/mysqld instead (RedHat and derivatives mostly I think), as well.
 
You must log in or register to reply here.
Back
Top Bottom