Hi,
I have found this code on another website and tried editing it for my use
#!/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
- 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