Daily MySQL backups with xtrabackup

I’ve been using standard MySQL dumps as backup technique on my VPS for few years. It works fine and backups were usable few times when I needed them. But in other places I’m using xtrabackup. It’s faster when crating backups and a lot faster when restoring them – they’re binary so there is no need to reevaluate all SQL create tables/inserts/etc. Backups also include my.cnf config file so restoring on other machine should be easy.

After I switched from MariaDB to Percona I have Percona repos configured, so I will use latest version of xtrabackup.

apt-get install -y percona-xtrabackup

Prerequisities

xtrabackup requires configured user to be able to make backups. One way is to write user and password in plaintext in ~/.my.cnf. Another is using mysql_config_editor to generate ~/.mylogin.cnf file with encrypted credentials. To be honest I didn’t check what kind of security provides this encryption but it feels better than keeping password in plaintext.

I do not want to create new user for this task – I just used debian-sys-maint user. Check password for this user like this:

grep password /etc/mysql/debian.cnf

Now create encrypted file:

mysql_config_editor set --login-path=client --host=localhost --user=debian-sys-maint --password

Hit enter and copy/paste password. File .mylogin.cnf should be created with binary content. We may check this with:

# mysql_config_editor print 
[client]
user = debian-sys-maint
password = *****
host = localhost

Looks OK.

Backuping

Now backup script. I placed it directly in cron.daily dir ex: /etc/cron.daily/zz-percona-backup with content:

#!/bin/bash
DATE=`date +%F-%H%M%S`
DIR=/backup/xtrabackup
DST=$DIR/${DATE}.tar.xz

# this will produce directories with compresses files
# mkdir -p $DST
# xtrabackup --backup --compress --target-dir=$DST

# this will produce tar.xz archives
xtrabackup --backup --stream=tar | xz -9 > $DST

# delete files older than 30 days
find $DIR -type f -mtime +30 -delete

I prefer to have single archive with backup because I’m transferring those files to my NAS (for security). But for local backups directories are more convenient and faster when restoring. Also tar archives have to be decompressed with -ioption.

Restoring

First time I saw it it scared me a little but after all worked fine and without problems…

service mysql stop
rm -rf /var/lib/mysql
mkdir /var/lib/mysql

Now prepare backup, if you used directory backups it’s easy, ex:

xtrabackup --decompress --target-dir=/backup/xtrabackup/2016-03-14-214233
xtrabackup --prepare --target-dir=/backup/xtrabackup/2016-03-14-214233
xtrabackup  --copy-back --target-dir=/backup/xtrabackup/2016-03-14-214233

But if you used tar archives it’s little more messy… You have to create temporary dir and extract archive there:

mkdir /tmp/restore
tar -xvif /backup/xtrabackup/2016-03-14-214233.tar.xz -C /tmp/restore
xtrabackup --prepare --target-dir=/tmp/restore
xtrabackup  --copy-back --target-dir=/tmp/restore

We have to fix ownership of restored files and db may be started:

chown -R mysql:mysql /var/lib/mysql
service mysql start

If your backup is huge you should reorder commands to shutdown database after backup decompression

Source: https://www.percona.com/doc/percona-xtrabackup/2.3/xtrabackup_bin/xtrabackup_binary.html http://dev.mysql.com/doc/refman/5.7/en/mysql-config-editor.html https://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/streaming_backups_innobackupex.html

MySQL – reset root password

It will happen from time to time, that you're on alien machine and have to brutally update things in db without knowing credentials. Example is for root (quite secure candidate to change because it shouldn't be used in app 馃檪 ) but will work for any user.

  • shutdown db
  service mysql stop
  • create text file with command like this (update user accordingly) ex. in /tmp/pwchange.txt
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('HereYourNewPassword');
  • start mysqld with --init-file param
mysqld_safe --init-file=/tmp/pwchange.txt

sometimes you may require to point configuration file ex. --defaults-file=/etc/mysql/my.cnf

  • wait until it loads and kill mysql (ex. Ctrl+C / kill / etc)
  • start mysql
service mysql start
  • delete file with password
rm -f /tmp/pwchange.txt

You should be able to login with updated password.

Source:
https://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html

MySQL – Proste metody optymalizacji

Wcze艣niej czy p贸藕niej zawsze pojawia聽si臋 potrzeba zoptymalizowania naszej bazy MySQL. Przedstawi臋 kilka zmian w konfiguracji, kt贸re powinny zwi臋kszy膰 wydajno艣膰 w wi臋kszo艣ci przypadk贸w.

MyISAM –聽key_buffer_size

Najprostsz膮 optymalizacj膮 baz/tabel z mechanizmem MyISAM jest odpowiednie dobranie bufora na cache dla kluczy i indeks贸w (dane nigdy nie s膮 cachowane). Poni偶sze zapytanie pozwala oszacowa膰 zalecany rozmiar cache’u:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 2 PowerOf1024) B;

Wynik okre艣la zalecany rozmiar bufora (parametr key_buffer_size w pliku /etc/mysql/my.cnf) dla bie偶膮cego stanu bazy – warto ciut doda膰 na zapas. Na systemach 32 bitowych parametr聽key_buffer_size mo偶e przyjmowa膰 maksymalnie 4GB, na 64 bitowych maksymalnie 8GB.

[mysqld]
key_buffer_size=xxxM

InnoDB –聽innodb_buffer_pool_size

W przypadku InnoDB cachowane mog膮 by膰 zar贸wno dane jak i klucze/indeksy a rozmiar bufora okre艣la parametr聽innodb_buffer_pool_size. Zalecan膮 minimaln膮 warto艣膰 tego parametru mo偶emy oszacowa膰 zapytaniem:

SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 2 PowerOf1024) B;

W przypadku InnoDB po zmianie warto艣ci聽innodb_buffer_pool_size konieczne jest r贸wnie偶 ustawienie聽innodb_log_file_size na 25% warto艣ci聽innodb_buffer_pool_size lub 2047M (nale偶y wybra膰 mniejsz膮 z warto艣ci). By wygenerowa膰 pliki log o nowych rozmiarach musimy post臋powa膰 wed艂ug poni偶szej instrukcji:

  1. Dopisujemy w pliku my.cnf parametry:
    [mysqld]
    innodb_buffer_pool_size=xxxM
    innodb_log_file_size=25% xxxM lub 2047M
  2. Wy艂膮czamy baz臋:
    invoke-rc.d mysql stop
  3. Kasujemy obecnie pliki log:
    rm /var/lib/mysql/ib_logfile[01]
  4. Uruchamiamy baz臋:
    invoke-rc.d mysql start
  5. Po starcie bazy zostan膮 wygenerowane nowe pliki log o nowych rozmiarach.

InnoDB – kompaktowanie plik贸w

W domy艣lnej konfiguracji MySQL dla baz InnoDB (na Debianie na bank, przypuszczam 偶e na innych distro jest podobnie) wszystkie tabele, indeksy, metadane tabel i inne dane dotycz膮ce table InnoDB przechowywane s膮 w jednym pliku:聽/var/lib/mysql/ibdata1

Nie jest to optymalne ustawienie szczeg贸lnie gdy mamy du偶o baz i o znacznych rozmiarach – wykonywanie wielu r贸wnoczesnych operacji na jednym gigantycznym pliku potrafi mocno przymuli膰.

Pr贸ba kompaktowania/optymalizowania tabel InnoDB nie powoduje zmniejszenia tego pliku – bo gdy pr贸bujemy optymalizowa膰 tabele InnoDB powoduje to:

  • u艂o偶enie danych i indeks贸w wewn膮trz pliku ibdata1 w spos贸b ci膮g艂y,
  • wzrost rozmiaru pliku ibdata1 poniewa偶 powy偶sze dane dopisywane s膮 na jego ko艅cu.

Niewiele os贸b spodziewa si臋 takiego rezultatu.聽Mo偶na zmniejszy膰 rozmiar tego pliku wy艂膮czaj膮c dane tabel i ich indeksy do osobnych plik贸w ale proces ten wymaga pe艂nego backupu bazy i jej odtworzenia, wi膮偶e si臋 wi臋c z chwilowym (a w przypadku du偶ych baz – d艂u偶szym) przestojem.

  1. Robimy pe艂ny backup bazy, np. poleceniem:
    mysqldump --all-databases --single-transaction -uroot -p > my-dump.sql

    (dump’a mo偶na dodatkowo skompresowa膰 gzipem dodaj膮c pipe’a – przyspieszy to odzyskiwanie przez zmniejszenie ilo艣ci danych potrzebnych do odczytania z dysku)

  2. Kasujemy wszystkie bazy z wyj膮tkiem schematu mysql (de facto powinno wystarczy膰 skasowanie i odzyskanie tylko baz korzystaj膮cych z tabel InnoDB)
  3. Wy艂膮czamy us艂ug臋:
    invoke-rc.d mysql stop
  4. Dodajemy w pliku /etc/mysql/my.cnf聽parametry:
    [mysqld]
    innodb_file_per_table
    innodb_log_file_size=25% xG
    innodb_buffer_pool_size=xG

    – pierwsza opcja powoduje w艂a艣ciwe rozdzielenie tabel InnoDB do r贸偶nych plik贸w (dodanie tej opcji na serwerze na kt贸rym znajduj膮 si臋 bazy InnoDB spowoduje ich uszkodzenie – odradzam),
    – drug膮 i trzeci膮 linijk臋 konfigurujemy wed艂ug wcze艣niejszej instrukcji o innodb_buffer_pool_size,
    – mo偶emy te偶 na czas przywracania danych doda膰 opcj臋 bulk_insert_buffer_size=256M, skr贸ci to czas potrzebny na przywr贸cenie bazy.

  5. Kasujemy pliki: ibdata1, ib_logfile0 and ib_logfile1:
    rm /var/lib/mysql/ibdata1
    rm /var/lib/mysql/ib_logfile[01]
  6. Uruchamiamy serwer MySQL:
    invoke-rc.d mysql start
  7. Przywracamy wszystkie bazy z dump’a:
    cat聽my-dump.sql | mysql -uroot -p

Plik ibdata1 uro艣nie ale od tej pory b臋dzie zawiera膰 wy艂膮cznie metadane tabel a poszczeg贸lne tabele i indeksy b臋d膮 przechowywane w osobnych plikach, np. tabela.ibd. Teraz optymalizowanie tabel InnoDB b臋dzie powodowa膰 zmniejszenie rozmiar贸w plik贸w *.ibd a plik ibdata1 nie b臋dzie tak obci膮偶ony.

Zmiana metody flush’a

W niekt贸rych przypadkach ustawienie opcji聽innodb_flush_method na warto艣膰 O_DIRECT mo偶e poprawi膰 wydajno艣膰, cho膰 w innych wydajno艣膰 mo偶e si臋 pogorszy膰聽(na forach sugerowano wyst臋powanie problemu na dedykowanych macierzach). Mo偶na bezpiecznie w艂膮czy膰 t臋 opcj臋 i wykona膰 kilka bechmark贸w:

[mysqld]
innodb_flush_method=O_DIRECT

Cache wynik贸w zapyta艅

Sprawd藕my najpierw czy cachowanie jest w艂膮czone (u mnie by艂o to domy艣lne ustawienie), wydajemy zapytanie:

SHOW VARIABLES LIKE 'query_cache_type';

Mo偶liwe s膮 3 ustawienia:

  • ON (query_cache_type = 1) – cachowanie wszystkich zapyta艅,
  • OFF聽(query_cache_type = 0) – cachowanie na 偶膮danie,
  • DEMAND聽(query_cache_type = 2) – cachowanie wy艂膮czone.

W przypadku opcji DEMAND cachowanie jest w艂膮czane je偶eli po SELECT’cie dodamy聽SQL_CACHE. Mi osobi艣cie najbardziej odpowiada opcja z cachowaniem wszystkiego.

Nast臋pnie nale偶y ustawi膰 w pliku my.cnf poni偶sze zmienne wed艂ug potrzeb:

query_cache_limit = 2M
query_cache_size = 32M

Pierwsza opcja ustala maksymalny rozmiar pojedynczego zapytanie, kt贸re b臋dzie cachowane – zapytania wi臋ksze nie b臋d膮 cachowane. Druga opcja ustala rozmiar ca艂ego bufora na cache (przewa偶nie wi臋cej dzia艂a lepiej).

MyISAM – unikanie repair with keycache

Gdy nasza baza uro艣nie i b臋dziemy mie膰 w niej tabele o rozmiarze przekraczaj膮cym 2GB to da si臋 zauwa偶y膰 偶e pewne operacja jak np. zak艂adanie indeksu, optymalizacja, naprawa – trwaj膮 cholernie d艂ugo. Mo偶na to szczeg贸lnie odczu膰 w艂a艣nie w momencie przekraczania rozmiaru 2GB i tak utworzenie indeksu na tabeli o rozmiarze 1,9GB trwa powiedzmy kilkana艣cie/kilkadziesi膮t minut, a ta sama operacja na bazie o rozmiarze 2,1GB mo偶e zaj膮膰 nawet kilka godzin.

Przyczyn臋 艂atwo namierzy膰 obserwuj膮c wynik polecenia:

SHOW PROCESSLIST;

w trakcie operacji na “ma艂ej” i “du偶ej” tabeli. “Ma艂a” zatrzymuje si臋 na d艂u偶ej na operacji Repair By Sorting, a “du偶a” kona godzinami na Repair With Keycache. W艂a艣nie r贸偶nica w dzia艂aniu obu mechanizm贸w sortowania daje w ko艣膰:

  • repair by sorting – wykorzystuje do sortowania wiele plik贸w tymczasowych i wymaga sporo wolnego miejsca w katalogu ustawionym w opcji tmpdir (domy艣lnie ustawionej na /tmp) – je偶eli miejsca b臋dzie za ma艂o to wybierany b臋dzie mechanizm “repair with keycache”,
  • repair with keycache – wykorzystuje do sortowania bardzo ma艂y bufor (u mnie 8MB), jest ok 10~20 krotnie wolniejszy ni偶 “repair by sorting” a do tego tworzy mniej optymalne indeksy.

O tym kt贸ry z mechanizm贸w zostanie wybrany decyduje opcja myisam_max_sort_file_size – zmienna ta ma domy艣lnie warto艣膰 2GB i w艂a艣nie dlatego problemy pojawiaj膮 si臋 po przekroczeniu tego rozmiaru. Proponuj臋 ustawi膰 j膮 sporo powy偶ej rozmiaru najwi臋kszych tablic – oczywi艣cie je艣li miejsce w temp’ie pozwoli na to, np:

聽myisam_max_sort_file_size=8GB

Przy takim ustawieniu warto mie膰 w /tmp minimum drugie tyle wolnego miejsca.

殴r贸d艂o

http://dba.stackexchange.com/questions/3163/mysql-5-1-innodb-configuration-24gb-ram-bi-xeon-high-load

MySQL – dost臋p zdalny na szybko

Instalacja serwera聽MySQL聽na Debianie jest niezwykle prosta i聽sprowadza si臋 do jednego polecenia:

sudo apt-get install mysql-server

Polecenie to zainstaluje i聽uruchomi us艂ug臋 serwerow膮聽MySQL. W聽czasie instalacji b臋dziemy proszeni o聽podanie has艂a dla root’a (kt贸re oczywi艣cie dobrze jest zapami臋ta膰 b膮d藕 zapisa膰).

Tak zainstalowana baza nas艂uchuje na lokalnym porcie (localhost:3306) umo偶liwiaj膮膰 dost臋p wy艂膮cznie root’owi. Jest to bardzo bezpieczna konfiguracja… Ale je艣li nie mamy zamiaru na tej samej maszynie instalowa膰 oprogramowania zarz膮dzaj膮cego to nie zawsze jest to wygodne, tym bardziej gdy przyk艂adowo mamy dzia艂aj膮cego phpmyadmin’a na jakim艣 serwerze聽www. W聽takim przypadku pierwsz膮 rzecz膮, kt贸r膮 robi臋 jest udost臋pnienie dost臋pu zdalnego dla root’a. Warto zaznaczy膰 偶e uprawnienia root’a mo偶na nada膰 dowolnemu u偶ytkownikowi (np. romanowi) co jest du偶o bezpieczniejsz膮 konfiguracj膮 ni偶 dzia艂anie bezpo艣rednio na koncie root’a (kt贸rego nazwa jest powszechnie znana).

Dost臋p zdalny dla root’a

Aby umo偶liwi膰 zdalne zalogowanie si臋 do bazy z聽uprawnieniami root’a trzeba ustawi膰 odpowiednie GRANT’y, robimy to tak:

mysql -u root -p

mysql> GRANT ALL PRIVILEGES ON *.* TO 'roman'@'%'
> IDENTIFIED BY 'haslo dla zdalnego roota' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> exit

Pierwsze polecenie po艂膮czy nas z聽baz膮 prosz膮c o聽has艂o podane w聽czasie instalacji.

Kolejne to polecenia聽SQL’owe, kt贸re pozwalaj膮 u偶ytkownikowi ‘roman’ 艂膮cz膮cemu si臋 z聽hosta ‘%’ (dowolnego) identyfikuj膮cemu si臋 has艂em ‘haslo dla zdalnego roota’. Je偶eli chcemy ograniczy膰 dost臋p do tylko jednego zdalnego adresu to zamiast znaku procenta wpisujemy ten adres IP. Tak dodany u偶ytkownik ma te偶 prawo nadawania uprawnie艅 (GRANT OPTION). Polecenie聽FLUSH PRIVILEGES聽prze艂adowuje uprawnienia – umo偶liwiaj膮c logowanie z聽podanymi wcze艣niej uprawnieniami.

Pozosta艂o nam zmieni膰 ustawienia serwera tak aby nas艂uchiwa艂 nie tylko na localho艣cie. W聽tym celu edytujemy plik聽/etc/mysql/my.cnf:

sudo vim /etc/mysql/my.cnf

Odszukujemy nast臋puj膮c膮 lini臋:

bind-address = 127.0.0.1

Lini臋 t臋 mo偶emy zakomentowa膰 co b臋dzie skutkowa膰 nas艂uchiwaniem przez serwer na wszystkich skonfigurowanych adresach IP (taki sam efekt da wpisanie w聽polu adresu 0.0.0.0). Mo偶na te偶 wpisa膰 tylko jeden adres IP w聽przypadku gdy na serwerze jest ich kilka i聽nie chcemy aby serwer by艂 dost臋pny na wszystkich.

Ostatnim krokiem jest zrestartowania serwera聽MySQL聽aby zadzia艂a艂y wprowadzone w聽pliku konfiguracyjnym zmiany. Mo偶na to zrobi膰 tak:

invoke-rc.d mysql restart

Je偶eli w艂a艣nie za艂o偶y艂e艣 i udost臋pni艂e艣 nowy serwer bazodanowy MySQL to oszcz臋d藕 sobie pracy w przysz艂o艣ci i od razu ustaw przechowywanie tabel InnoDB w osobnych plikach.