Thread: MySQl backup script

I tried Crouse’s script from this thread.

Code:

#!/bin/sh 
for i in `mysql -B -e 'show databases'; | tail +2`; do
mysqldump --opt $i | gzip -9 > /var/backups/mysql/$i.`date --iso-8601`.gz
ls -t /var/backups/mysql/$i.* | tail +7 | xargs rm -f
done

But it gives me this error:

Code:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Do I have to grant privileges to user "root" in mysql or use the root password from the command line?

Thanks redhead. I run multiple websites on my server and I was thinking of a universal maintenance page for all the virtual hosts.

This is what I have done so far:

1) I created a /etc/httpd/conf.d/websites.conf file with the following config:

Code:

Include conf.d/websites.d/active/*.conf

2) I then created the following directories:

Code:

/etc/httpd/conf.d/websites.d/open
/etc/httpd/conf.d/websites.d/closed

The "open" directory contains all my virtualhost files. The "closed" directory contains only one file (closed.conf) looking like this:

Code:

ServerName localhost
NameVirtualHost *:80
ErrorDocument 404 /
<VirtualHost *:80>
    DocumentRoot /home/www/default/closed
</VirtualHost>

3) Next I created a symbolic link /etc/httpd/conf.d/websites.d/active which can point to either the "closed" or "open" directory. here’s how the directory listing of websites.d looks:

Code:

[root@web-001 bin]# ll /etc/httpd/conf.d/websites.d/
total 24
lrwxrwxrwx 1 root root    33 Nov 11 08:56 active -> /etc/httpd/conf.d/websites.d/open
drwxr-xr-x 2 root root  4096 Nov 10 09:25 closed
drwxr-xr-x 2 root root  4096 Nov 11 15:02 open
[root@web-001 bin]#

4) For the next step I created a web-store-front.sh script that flips between the "open" and "closed" directories depending on whether I use "open" or "closed" as my first argument. It also restarts apache to activate the change.

Code:

#!/bin/bash
LOCK_FILE="/var/lock/subsys/httpd"
#
# Remove symbolic link
#
if [ -d /etc/httpd/conf.d/websites.d/active ]; then
    rm -f /etc/httpd/conf.d/websites.d/active
fi
#
# Restore symbolic link
#
if [ $1 = "closed" ]; then
    ln -s /etc/httpd/conf.d/websites.d/closed /etc/httpd/conf.d/websites.d/active 
else
    ln -s /etc/httpd/conf.d/websites.d/open /etc/httpd/conf.d/websites.d/active 
fi
#
# Restart Apache only if Apache is already running
#
if [ -f "$LOCK_FILE" ]; then
    /sbin/service httpd restart
fi
sleep 10

The index.html file in the /home/www/default/closed directory has the maintenance message.

5) A brief version of my backup script then looks like this:

Code:

#!/bin/bash
web-store-front.sh closed
mysql-daily-backup.sh mysql-username mysql-password
web-store-front.sh open

I’m now working on expanding the backup script to read the username, password and any directories I may want backed up from a configuration file and then tar the DB backups and files all together in one big tar ball.

When I get that far, I’ll post the code. It’s turning into a nice little project.