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

Mass replace in WordPress posts via MySQL query

I was doing a lot of changes to my old posts, switched to HTTPS, etc. Sometimes it was useful to change some particular text in all my old posts at a time, but there is no such feature in WordPress. But WordPress runs on MySQL and I could use SQL query to update such posts.

Make backup – it's not required but strongly advised 🙂

Now use this query as template to replace in place whatever you need:

mysql> UPDATE wp_posts SET post_content = REPLACE(post_content, '<!--more-->', '');
Query OK, 157 rows affected (0.04 sec)
Rows matched: 455  Changed: 157  Warnings: 0

This will remove <!--more-->from all posts (it's used by WordPress to span article when showed on tag/category pages).

Another example to update all URLs to HTTPS:

UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://gagor.pl', 'https://gagor.pl');

Be careful with that and make DB backup before you start.

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

Debian – Upgrade MySQL to MariaDB

After reading some good opinions about MariaDB I wanted to give it a try. Upgrade looks quite straight forward but I found some issues a little tricky.

Installation

Add repo and key:

cat > /etc/apt/sources.list <<SRC
deb http://mirrors.supportex.net/mariadb/repo/5.5/debian wheezy main
deb-src http://mirrors.supportex.net/mariadb/repo/5.5/debian wheezy main
SRC

(find more repositories here)

Now install MariaDB:

sudo apt-get update
sudo apt-get install mariadb-server

It could be better to install mariadb-server-5.5 and mariadb-client-5.5 package instead, because of this error.

MariaDB repo pinning

Some time after installation I have problem with newer packages from Debian repositories that upgraded my MariaDB installation back to MySQL – it’s described here, so I used pinning to resolve that.

cat > /etc/apt/preferences.d/ <<PIN
Package: *
Pin: origin mirrors.supportex.net
Pin-Priority: 1000
PIN

Results

Before migration to MariaDB, front page of my blog needs about 650 ms to generate. After switch, it was only about 550ms. So it’s about 15% – absolutely for free 🙂

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