Backupstrategi för MySQL
 

Jag kör dagligen ett jobb på en MySQL-server som dumpar databasen till fil och sen lägger på en filserver offsite.

mysqldump -uxxxxx -pxxxxxxxx -q --opt --all-databases | bzip2 -c > /path/db.sql.bz2

Problemet är att detta hänger databasen i ca 5 minuter, därför kör jag inte det oftare. Jag har fått för mig att den inte ska låsa databasen. Det är dock många miljoner rader som den ska dumpa.

mysqlhotcopy har jag testat, kommer inte ihåg varför jag inte kör den dock :)

Nån som har några förslag på bättre strategi? Vill gärna ha backuper oftare. Hur ska man göra?

Gillar dock det här med att ha det som sql-fil, lättare att ha att göra med om man ska hämta ur delmängder av data osv...

Om du inte gör det redan så utför det vid tider som inte är affärskritiska.
Om dbn inte är tillgänglig visa en sida för användaren om att underhåll utförs (eller likn).

Ta statistik på den sidan och tweaka lite med tiden om du får många hits.

mysqlhotcopy har inte stöd för InnoDB, därför vill du inte använda det.

Det går att säga åt mysqldump att inte låsa tabellerna (--opt innefattar låsning av tabellerna, för att stänga av det, använd --no-lock-tables), men då får du inte en lika ren dump eftersom tabellerna kan ändras under tiden som du tar backupen.

Ja, det går ju redan sen på småtimmarna, men problemet är ju att det inte finns några tider utan många användare inloggade.

Ok, men om man inte är en bank eller nåt annat kritiskt så fungerar det väl ändå? :)

Det vet man aldrig. Tänk dig detta scenario:

* Insättning i tabell 1
* Insättning i tabell 2, använd id från tabell 1

Det finns två saker som kan göra din databas "korrupt" här och kräva manuell handpåläggning:
1) Backupen görs på tabell 2 innan insättningen men i tabell 1 efter insättningen.
eller
2) Backupen görs på tabell 1 innan insättningen, men tabell 2 efter insättningen.

Det första är förmodligen oftast rätt harmlöst, men det andra kan vara ett problem om till exempel en ny användare registrerar sig och redan har saker på sitt konto (som exempel).

Mitt råd är därför att göra åtminstone en backup om dygnet med låsta tabeller om du kan.

För övrigt kan --single-transaction vara intressant på InnoDB-tabeller (då kan du få en logiskt konsistent backup utan att låsa tabellerna), men det gör ingen nytta på MyISAM.

Sätt först upp en slav-dbserver till din existerande master. Sedan dumpar du från slaven.

Försöker undvika det just nu, men det kanske är oundvikligt.

Finns det ingen lösning där man helt enkelt kör backup på query-loggen istället, då går det ju att återställa?

Tesade
--no-lock-tables
med den känner inte igen det, men däremot:
--lock-tables=false

Fick ingen kontakt med servern efter ca en halvminut där heller.

Du kan sätta max_allowed_packet för mysqldump i configen också. Det bör hjälpa en del.

Jag förstår inte varför det skulle vara lättare att göra backup från query logs? Kör du en slav som du sedan kör en backup mot är det ju igentligen vad du gör förutom att du slipper att göra det vid ett tillfälle. Den syncas från masterns binary logs vilka är statement based.

Annars är det många som använder LVM snapshots för backup av MySQL.

Sen finns Xtrabackup från Percona som hanterar innodb, har bra stöd för throttling och är open source.

Jag gör backup på min 250 MB MySQL-databas med php-programmet MySQLDumper. Det stör inte databasens funktion alls. Min backup tar 5 minuter, men återställning tar 2 timmar.
http://www.mysqldumper.net/