After you have set up a working MySQL replication from the master to the slave, I assume that you want to do automatic backups of the slave database to the directory /home/sqlbackup. First, we must create that directory:
mkdir /home/sqlbackup
Next we create the shell script /usr/local/sbin/mysqlbackup.sh that stops the slave, makes an SQL Server dump of the whole MySQL database in /home/sqlbackup (the file name of the SQL Server dump will look like this: backup-20070423-18.sql; this is a dump taken on April 23, 2007, at 18.00h), restarts the slave afterwards (the slave will then catch up on everything that has happened on the master in the meantime so that no data is lost), and deletes all SQL Server dumps in /home/sqlbackup that are older than two days:
vi /usr/local/sbin/mysqlbackup.sh
#!/bin/sh
datum=`/bin/date +%Y%m%d-%H`
/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword stop-slave
/usr/bin/mysqldump --user=root --password=yourrootsqlpassword --lock-all-tables \
--all-databases > /home/sqlbackup/backup-${datum}.sql
/usr/bin/mysqladmin --user=root --password=yourrootsqlpassword start-slave
for file in "$( /usr/bin/find /home/sqlbackup -type f -mtime +2 )"
do
/bin/rm -f $file
done
exit 0
(Please make sure that you replace yourrootsqlpassword with the password of the root MySQL user on the slave!)
Now we must make the script executable:
chmod 755 /usr/local/sbin/mysqlbackup.sh
Of course, we don't want to run the /usr/local/sbin/mysqlbackup.sh manually; instead, we create a cron job that runs the script automatically every three hours:
crontab -e
0 */3 * * * /usr/local/sbin/mysqlbackup.sh &> /dev/null
Of course, you are free to modify the cron job to run as often as you need it.
That's it, using this method you can now back up your MySQL database without interrupting the MySQL service on the master server.
|