Zálohování MySQL pomocí Subversion

Úvod

Pokud chcete uchovávat historii změn v databázi bez potřeby nějakého sofistikovaného mechanizmu, který by toto prováděl nad SQL tabulkami přímo v databázi a stačí vám mít zálohu někde bokem pro případ nutnosti vrátit se ke starším datům, tak pravděpodobně sáhnete po periodickém např. denním zálohování do komprimovaného souboru. Každý takový komprimovaný soubor pak zabírá velikost celé databáze. Počet takových souborů pak v čase roste, takže buď staré soubory smažete ručně nebo automaticky průběžně. Další možnost je použít nějakého mechanizmu pro vytváření rozdílových záloh a uchovávat pak jen jednu nebo několik plný záloh a mnoho menších rozdílových.

Existuje však také další možnost, která využívá pro uchování záloh schopnosti verzovacího systému Subversion. Ten umí efektivně uchovávat rozdílové zálohy ideálně textových, ale také binárních souborů. Zároveň jsou data na serveru komprimována. Pak výsledný repozitář po několika provedených změnách v souborech je stále dosti malý. Tento systém, který se s výhodou používá pro verzování zdrojových souborů a jiných textových souborů, tak lze použít také pro verzování výpisu dat z databází (dump). Ty jsou generovány v textové podobě. Pak zálohovací skript vygeneruje data z databází do složek a souborů a provede odevzdání (commit) dat do databáze.

Výhody:

• Malá velikost uložených dat na serveru
• Kompletní historie změn v databázi
• Uložená data lze prohlížet pomocí propracovaných nástrojů jako TortoiseSVN, Kdesvn, aj.
• Data lze spravovat a načítat i ze vzdálených klientů

Nevýhody:

• S každou revizí velikost repozitáře na serveru roste. Předchozí revize lze umazat např. provedením kompletního exportu a importu do nového repozitáře.
• Velikost pracovní kopie je dvojnásobek uložených exportů.

Příprava repozitáře

Repozitář lze vytvořit pomocí povelu:

svnadmin create jméno_repozitáře

Pokud je repozitář zpřístupněn z web serveru, tak lze pracovní kopii stáhnout přes

svn checkout http://jméno_serveru/jméno_repozitáře/ jméno_složky

nebo přímo na serveru jako lokální diskový zdroj:

svn checkout file://localhost/cesta/jméno_repozitáře jméno_složky

Příklady skriptů

Linux

backup_db_svn.sh:

#!/bin/bash

DB_USER=backup
DB_PASSWORD=heslo
BACKUP_DIR=/root/svnbackup

echo Zaloha databaze...

function backup_db
{
DB_NAME=$1
echo -ne "${DB_NAME}: "
cd ${BACKUP_DIR}
mkdir -p ${DB_NAME}
cd ${DB_NAME}
for I in $(mysql -N -B -u ${DB_USER} --password=${DB_PASSWORD} -e "show tables from \`${DB_NAME}\`" -s --skip-column-names);
do
  echo -ne "$I, "
  mysqldump -u ${DB_USER} --password=${DB_PASSWORD} ${DB_NAME} $I --skip-dump-date --extended-insert=FALSE> "$I.sql";
done
echo ""
}

backup_db database1
backup_db database2
backup_db database3
backup_db database4
backup_db database5
backup_db database6

# Commit working copy
cd ${BACKUP_DIR}
svn add * -q --force
svn commit -m "" .
svn cleanup

# Return to script dir
cd "$( dirname "${BASH_SOURCE[0]}" )"

echo Hotovo

Windows

mysql and svn binaries need to be accessible in PATH system variable.

backup_db_svn.bat:

pushd work

SET USER=username
SET PASSWORD=password
SET SCHEMA=database

mysql -u%USER% -p%PASSWORD% -s -N -e "show tables from %SCHEMA%" | ^
for /F "usebackq" %%D in (`findstr /V "information_schema performance_schema"`) do ^
mysqldump -u%USER% -p%PASSWORD% %SCHEMA% %%D --skip-dump-date --extended-insert=FALSE >%%D.sql   

svn add * -q --force
svn commit -m ""
svn cleanup

popd

Zpětné obnovení databáze

Více SQL souborů lze sloučit do jednoho pomocí:

cat *.sql > fullBackup.sql

Protože záloha databáze je kvůli efektivního ukládání rozdílových změn v SVN exportována v "extended insert" formátu a během obnovy může být prováděn opravdu velký počet insertů, tak při zpětné obnově je potřeba operaci vkládání provést nejlépe jako jednu transakci. To lze provést takto:

use db_name; SET autocommit=0 ; source the_sql_file.sql ; COMMIT ;