MySQL Backup Script

MySQL is one of the most popular RDBMS in the world, however, there is no out-of-box backup method. mysqldump is not sufficient when we mix InnoDB and MyISAM in one scheme.

I have written a PowerShell script to backup tables from MySQL databases, using either --single-transaction for InnoDB and --lock-tables for MyISAM tables.

Run the script from shell and you will get backup-DBNAME.sql files in your current directory for each database. This script will use table locks for MyISAM tables and transactions for InnoDB tables. Note if you are using READ-COMMITTED isolation level, backups might not be consistent between transactions. This should not matter when you are using READ-COMMITTED.

In the following example, MediaWiki is read with a table lock because it has one MyISAM table for indexing while the others are InnoDB.

I'm using this script to dump my database everyday and zipping them to store for a week. If you want to perform differential or incremental backups, you need to make use of binary logging feature of MySQL.

The following script is public-domain.

$innodb_dbs = @("bamboo","confluence","crowd","jira")
$myisam_dbs = @("mediawiki")
$backup_user = "backup"
$backup_password = "password"
$mysqldump = "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe"
$password_option = "–password=" + $backup_password
foreach($db in $innodb_dbs){
&"$mysqldump" -u $backup_user $password_option –quick –opt –single-transaction $db > backup-$db.sql
}
foreach($db in $myisam_dbs){
&"$mysqldump" -u $backup_user $password_option –quick –opt –lock-tables $db > backup-$db.sql
}
&"$mysqldump" -u $backup_user $password_option –quick –opt –lock-tables –flush-privileges mysql > backup-mysql.sql