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