Organize MySQL backups and put to ftp server

So we have very important MySQL databases hosted on FreeBSD OS we do not want to lose.
First step is to create backup procedures to make backups to local disk.
But this will not help us much if server will crash.
As the second step we will put local backups to remote ftp server configured to allow only upload with unique filenames. The download and even delete or overwrite is prohibited.
FTP server configuration is the subject of another post.

I prefer to put my scripts in /usr/local/opt/. Let's name the script "mysql_backup.sh"

# cat > /usr/local/opt/mysql_backup.sh
#!/bin/sh
username=USERNAME
password=PASSWORD
backupdir=/path/to/backup
ftp_to=ftp://host.name.to.put.backups.to/`hostname -s`/db
days=7

### Do not edit below
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin
umask 077
now=`date "+%Y-%m-%dT%H:%M:%S"`

databases=`mysql -u ${username} -p${password} -N -e "show databases"`

for db in ${databases} ; do
mysqldump -u ${username} -p${password} --opt -F -l ${db} > ${backupdir}/${db}-${now} && \
bzip2 -9 ${backupdir}/${db}-${now} && \
ftp -Vu ${ftp_to}/${db}-${now}.bz2 ${backupdir}/${db}-${now}.bz2
done

find ${backupdir} -name '*-*.bz2' -a -type f -mtime +${days} -delete

^D

Script enumerates all databases and then dumps each in backup folder using DBNAME-YEAR-MONTH-DAY_HOUR:MINUTE scheme as the name. After, script archives the dump using bzip2 and upload archive to the ftp server. Last part of the script removes files in backup folder older than, in my case, 7 days.

So, everything is ready, and let's modify /etc/crontab to run script every night at 05:30

# echo "30 5 * * * root /usr/local/opt/mysql_backup.sh" >> /etc/crontab

That's it.

P.S.
Will be wise to run script manually before putting to crontab.

Script to flush logs from replicant host to master

#!/bin/sh
# Format host:username:password
hosts="host1:username1:password1 host2:username2:password2"

###
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin
for system in $hosts ; do
host=`echo $system | cut -d : -f 1`
user=`echo $system | cut -d : -f 2`
password=`echo $system | cut -d : -f 3`

log=`mysql -S /tmp/$host-mysql.sock -B --skip-column-names mysql -e "show slave status" | cut -f 6`

if [ ${log} ]; then
mysql -h $host -u $user -p$password -e "PURGE MASTER LOGS TO '$log';"
fi
pid="/home/$host/`hostname`.pid"
kill -HUP `cat $pid`
done