How to Backup MySQL databases without interrupting MySQL?

Normally when we create a backup of our MySQL using Mysqldump it creates a Read Lock on the database, so no changes can be made to the database at that time. It may take from a second to few minutes to take up backup of your database. So, sysadmin either prefer to stop MySQL or issue a READ lock on the MySQL table, so that database remains consistent. In this howto, i will demonstrate a method so that you can take up backup of your database without interrupting MySQL.

In this howto i use a method that i discussed in my previous howto how to replicate your MySQL database on Linux. Now once we created a slave MySQL server you can create a cronjob that can create a take regular backups of your database using shell script.

Key Idea: We will stop the slave server and then take the backup of the server. Once backup is done we will start the slave server again and it will catch up with the mysql master server and no data will be lost.

We will create a shell script that will stop the mysql slave, and then take a dump of that slave server and after that is completed again start the slave server.

#!/bin/sh

date = `date +%Y-%h-%d-%H`

mysqladmin --user=root --password=rootpassword stop-slave

mysqldump --user=root --password=rootpassword dbname > ~/backupsql/backup_dbname-${date}.sql

mysqladmin --user=root --password=rootpassword start-slave

Give the shell script executable permission.

[root]# chmod +x mysqlbackup.sh

Add this on cron:

[root]$ crontab -e

0 */6 * * *  /path/to/shellscript/mysqlbackup.sh > /dev/null 2>&1

Read this for more details on How to set a cron job.

This was one of the method that i tested out for creating backup. This works great if your database updates very frequently or you need frequent distributed backups.