Indholdsfortegnelse
Hvad er den binære log?
Det kan siges meget kort: Den binære log indeholder alle ændringer til data og struktur i databasen, siden et bestemt tidspunkt.
Når binær logging er slået til i MySQL, vil mysql server skrive alle ændringer i alle databaser til filen så snart de indtræffer… medmindre andet er konfigureret.
Som navnet meget tydeligt antyder, er loggen binær, og derfor kan du ikke bare åbne og læse den. Du skal først konvertere den til en tekstfil, og det kan du gøre med mysqlbinlog:
|
1 |
# mysqlbinlog binlog.000001 > /tmp/statements.sql |
Nu kan du åbne og læse statements.sql som en tekstudgave af binlog.000001
Læs mere om mysqlbinlog i dokumentationen.
Aktivér den binære log
Hvorvidt den binære log er slået til i MySQL, afhænger af hvordan MySQL installationen på dit OS er skruet sammen. På Ubuntu 16.04 LTS er den f.eks. ikke slået til som standard. Men det kan du gøre til hver en tid. Det kræver bare en ændring i konfigurationsfilen og en genstart af mysql:
Åben /etc/mysql/my.cnf
Hvis du ikke tidligere har rettet i denne fil, vil den være tom, bortset fra nogle kommentarer og et par linier med include sætninger. Derunder tilføjer du denne sektion:
|
1 2 3 |
[mysqld] server-id=1 log-bin=mysql-bin |
Overvej nu hvilke data du vil inkludere i den binære log. Systemdatabasen mysql er f.eks. ikke kompatibel mellem MySQL versioner. Det gælder især mysql.user tabellen der hverken er bagud- eller forudkompatibel. Jeg udelukker den derfor fra binær logging, og nøjes med separate dumps af denne tabel. Databaserne information_schema og performance_schema er også gode kandidater for udelukkelse, da de dannes automatisk hvis du starter helt forfra.
|
1 2 3 4 5 6 7 |
[mysqld] server-id=1 log-bin=mysql-bin binlog-ignore-db=mysql binlog-ignore-db=performance_schema binlog-ignore-db=information_schema binlog-ignore-db=en_anden_database |
Overvej også udelukkelse af andre databaser som f.eks. phpmyadmin og databaser til test- og udviklingsmiljøer m.fl.
Nu skal MySQL genstartes:
|
1 |
# service mysql restart |
Gå til /var/lib/mysql/. Nu burde du se filen mysql-bin.000001.
Du kan også tjekke server variablerne i en mysql prompt, for at bekræfte at binær logging er slået til:
|
1 2 3 4 5 6 7 8 9 10 |
mysql> SHOW VARIABLES LIKE 'log%'; +---------------------------------------------------+ | Variable_name | Value | +---------------------------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/mysql-bin | | log_bin_index | /var/lib/mysql/mysql-bin.index | +---------------------------------------------------+ |
Rotér den binære log
MySQL nummererer den binære log, fordi den bliver roteret. Dvs. næste gang du genstarter MySQL, vil du se en ny fil der hedder mysql-bin.000002.
Du kan også rotere loggen manuelt:
|
1 |
mysql> FLUSH LOGS; |
Hvis den seneste binære log har nummer 000002, vil du nu se en ny fil med nummer 000003.
Bemærk at der er 5 nuller foran nummeret. Det kan godt give det indtryk at det højeste nummer en binær log kan have, er 999999. Og hvad sker der så? Faktisk ingenting. MySQL vil fortsætte nummereringen helt op til 2.147.483.647 … og det er altså MANGE logfiler, over 2 milliarder. Hvis du roterer dine logfiler hver time, varer det således 245.000 år, før MySQL når maximum og giver fejl.
Inkrementel backup
Binære filer er et rigtig godt redskab til at lave inkrementel backup med, og den form for backup er en fordel hvis du arbejder med store databaser. Når du laver et dump af en database, vil hele databasen blive låst, mens der tages backup. Det kan nemt tage mange minutter hvis du har meget data og hvis databasen driver en hjemmeside, vil den være ubrugelig imens. Binær backup låser ikke databasen, da MySQL serveren skriver til den hele tiden. Hver eneste ændring der udføres med INSERT og UPDATE mv, bliver logget til den binære log.
Lad os tage et tænkt scenarie. Backup med mysqldump låser databasen i 30 minutter. Derfor nøjes man med at tage backup een gang i døgnet, om natten hvor der er færrest brugere på hjemmesiden. Men en så stor database har brugere døgnet rundt, og de vil altså opleve 30 minutter hver eneste døgn, hvor data ikke kan tilgås. Det er 2% nedetid. Det lyder ikke af meget, men tilgængelighed er en konkurrenceparameter, og det kan sagtens gøres bedre. Derudover mister vi op til 24 timers data, hvis det skrækkelige indtræffer, at der sker noget med vores MySQL server og vi får brug for backup’en.
Nu beslutter vi derfor at ændre strategi. I stedet for at lave en fuld backup med mysqldump hver nat, gør vi det kun een gang ugentligt. Dvs. på den dag og det tidspunkt hvor der er færrest brugere. Nu har vi reduceret nedetiden til 0,3%. Derudover tager vi inkremental backup vha den binære log hver eneste time døgnet rundt mellem hver fulde backup. Det betyder at vi fra at miste 24 timers data, nu kun kan miste maksimalt 1 times data.
Dit dump kunne se således ud (husk alt på een linie):
|
1 2 3 4 |
mysqldump -u bruger -pkode -h hostnavn --flush-logs --delete-master-logs --master-data=2 --lock-all-tables --routines --flush-privileges --log-error=dumplog --databases db1 db2 db3 > mysqldump.20170304-0443.sql |
Der er flere vigtige parametre i ovenstående, men bemærk --flush-logs og --master-data=2 . Disses 2 i kombination sikrer at du kan indlæse en backup fra det helt rigtige tidspunkt i de binære filer.
--flush-logs: Rotér MySQL servers binære filer før dumpet startes. I kombination med enten –lock-all-tables eller –master-data), foregår roteringen een gang på præcis samme tidspunkt som dumpingen af data.
--master-data=2: Inkludér CHANGE MASTER TO i dit dump. Informationen indeholder de præcise koordinater (fil og position) fra hvilke de binære logs skal indlæses ved gendannelse. Værdien 2 betyder at sætningen er kommenteret og derfor ikke eksekveres ved indlæsning af dit dump.
Bemærk at ovenstående dum ikke inkluderer backup af eventuelle triggers i databasen. Dem bør du lave backup af i en separat fil så du kan indlæse dem EFTER gendannelse af data. Hvis dine triggers er inkluderet og du bagefter begynder at indlæse dine binære logs, kan det have uheldige konsekvenser, som f.eks. “duplicate key”-fejl.
Du dumper alle dine triggers således:
|
1 2 3 |
mysqldump -u bruger -pkode --triggers --no-create-info --no-data --no-create-db --skip-opt --all-databases > triggers.20170304-0443.sql |
Husk også at dumpe privilegier. Det hjælper ikke så meget at gendanne data, hvis de brugere dine applikationer benytter, ikke bliver oprettet:
|
1 2 |
mysql -u bruger -pkode -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user != 'root'" | \ > while read uh; do mysql -u bruger -pkode -BNe "show grants for $uh" | sed 's/$/;/; s/\\\\/\\/g'; done > privileges.sql |
Gendan data fra backup
Når du skal gendanne data, indlæser du først din seneste komplette backup:
|
1 |
# mysql --host=host_navn -u root -p < dumpfile_20170301.sql |
Herefter skal du indlæse de binære log filer der er oprettet efter denne backup blev taget. I den fulde backup vil du se en kommentar der angiver fra hvilken binær fil, og hvilken linie du skal starte indlæsningen. Hvis koordinaterne i dit dump er…
|
1 |
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.001002', MASTER_LOG_POS=27284; |
… skal du starte med log nummer 001002 (og alle efterfølgende) fra position 27284:
|
1 |
# mysqlbinlog --start-position=27284 binlog.001002 binlog.001003 binlog.001004 | mysql --host=host_name -u root -p |
Det er klart, at hvis du har rigtig mange binære logfiler der skal indlæses, kan det være langsommeligt at skulle angive dem alle, istedet kan du gøre følgende:
|
1 |
# mysqlbinlog --start-position=27284 binlog.[0-9]* | mysql --host=host_name -u root -p |
Nu kan du indlæse dine backups af triggers og privilegier:
|
1 |
# mysql --host=host_navn -u root -p < triggers_20170301.sql |
|
1 |
# mysql --host=host_navn -u root -p < privileges.sql |
Voila! 🙂
0% nedetid ved brug af replikering
Faktisk kan du helt undgå nedetid under en backup. Nemlig ved at benytte dig af replikering, og fremover tage backup af den MySQL server du replikerer til. Det er helt klart den mest anbefalelsesværdige metode. Det kræver dog lidt planlægning og nedetid, og er uden for denne artikels emne.