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.