Migrace MySQL/MariaDB serveru

Migroval jsem nedávno systém s MySQL 5.6 na MariaDB 10.0 a hrozí, že v budoucnu něco podobného budu ještě párkrát dělat. Jelikož mají oba systémy drobné odlišnosti a ani MySQL ani MariaDB většinou nepatří mezi mé první volby při výběru databázového systému, raději si celou anabázi zapíšu do své externí paměti v podobě tohoto blogu. A kdo ví, třeba se pár řádků bude hodit ještě i někomu jinému.

MySQL vs. MariaDB

Jak mnozí pravděpodobné vědí, MariaDB je fork MySQL vytvořen původními tvůrci MySQL, kteří se obávali, že v rukách Oracle dojde k jeho postupné záhubě. Díky tomu je zatím ještě stále MySQL i MariaDB binárně kompatibilní, takže je jednoduše možné vzít kompletně celý datový adresář obvykle umístěný ve /var/lib/mysql/, překopírovat jej na nový databázový server a tam jednoduše pustit mysql_upgrade a doufat, že to klapne. Tímhle způsobem jsem se ale tak úplně vydat nechtěl. Jednak proto, že soubory mohou být nafouklé a obsahovat různý balast kvůli neustálému pohybu dat, ale hlavně proto, že systém obsahoval několik InnoDB databází a já je v novém umístění chtěl rozkouskovat pomocí innodb_file_per_table, aby lépe pasovaly na tiered storage na cílovém serveru. Další rozdíl mezi MySQL a MariaDB, kvůli kterému mi bylo milejší vydat se cestou dumpu a importu, je nepatrně odlišný způsob práce s oprávněními. Pokud totiž chci, aby uživatel mohl k databázi přistoupit jak lokálně přes unixový socket, tak i vzdáleně po síti, musím v MySQL mít dva záznamy pro uživatele a dva záznamy pro oprávnění. Tedy například

CREATE USER 'myuser'@'%' IDENTIFIED BY 'tajneheslo';
GRANT ALL ON `mydatabase`.* TO 'myuser'@'%';

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'tajneheslo';
GRANT ALL ON `mydatabase`.* TO  'myuser'@'localhost';

Zatímco MariaDB si vystačí pouze s prvním párem. Připadá mi to daleko logičtější. Když řeknu '%', tak tím prostě myslím bez výjimek všechno, protože když pak potřebuju měnit heslo, jsem nucen zjišťovat, kolik že to ten uživatel vlastně má záznamů.

Příprava

Mám v úmyslu exportovat jak uživatele a oprávnění, tak i struktury a data všech databází, takže budu potřebovat všelijakým příkazům pro export předávat uživatelské jméno a heslo databázového roota. Mohl bych použít klasický konstrukt mysql -u root -p<heslo>, ale tak nějak razím názor, že doporučené bezpečností praktiky jsou doporučené z dobrého důvodu a že se při jejich používání minimalizuje riziko, že vám kdejaký albánský virus sežere počítač i s klávesnicí. Doporučená praktika v případě MySQL a MariaDB je užití souboru ~/.mylogin.cnf, ideálně navíc zašifrovaného. Ten vytvoříte následujícím příkazem

mysql_config_editor set --login-path=client --host=localhost --user=root --password

A veškeré následující mysql příkazy směřující se stejným uživatelským jménem na stejný databázový server si budou tahat heslo odtamtud. Stejnou akci je pak pro zjednodušení importu a případných následných operací s databází možno udělat i na cílovém serveru.

Skládka povolena

Dumpnout MySQL databázi je díky příkazu mysqldump vcelku triviální operace. Já bych ale potřeboval vysypat všechny databáze. Ale zase ne úplně všechny, protože třeba takové information_schema a performance_schema obsahují data, která nedává smysl je přenášet, případně nejsou vůbec přenositelná. Databáze mysql pak obsahuje informace o uživatelích a oprávnění, která sice potřebuju, ale kvůli výše zmíněnému rozdílu je budu ještě po cestě chtít upravit. Příkaz mysqldump naštěstí umí pracovat v dávkovém módu, takže se mu v parametrech jen předají názvy databází a on je poslušně vysype do jednoho souboru i včetně všech potřebných CREATE klauzulí. Onen seznam databází si obstarám pomocí SQL dotazu SHOW DATABASES; specifického pro MySQL a MariaDB. Jako třešinku na závěr celý dump rovnou zkomprimuju, protože u textových souborů s opakujícími se hodnotami, kterými SQL dumpy bezesporu jsou, i sebepitomější kompresní algoritmus srazí výslednou velikost na zlomek té původní.

mysqldump -B $(mysql -BNe 'SHOW DATABASES' | grep -Ev '^information_schema|mysql|performance_schema$')| gzip -c9 >databases.sql.gz

Kredence

Jak jsem již zmínil, veškerá oprávnění jsou uložena v databázi s názvem mysql. Tentokrát ale nebudu potřebovat mysqldump, protože milé MySQL je natolik přívětivé, že mi dotazem SHOW GRANTS FOR 'user'@'host'; umožní vypsat jaká že to oprávnění dotyčný uživatel má. Seznam všech uživatelů pak jednoduše vysypu proběhnutím tabulky mysql.users. Bohužel není možné nějak rozumně tyto dva dotazy spojit, takže se budu muset uchýlit k potenciálně nebezpečné praktice. Nechám si totiž tu hromádku SHOW GRANTS FOR dotazů vyrobit pomocí funkce CONCAT() rovnou při probíhání oné tabulky uživatelů a tyto předkousané dotazy pak předám do dalšího procesu MySQL klienta, z nějž mi teprve konečně vypadne celá sada oprávnění všech uživatelů. Na závěr si na výpis sednu (tj. spustím nad ním sed) a trochu jej učešu, aby se mi v něm lépe orientovalo, protože mám v úmyslu jej před importem na cílový server ručně editovat.

mysql -BNe "SELECT DISTINCT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') FROM mysql.user" | mysql | sed -e 's/\(GRANT.*\)/\1;/' -e 's/^\(Grants.*\)/-- \1/' -e '/--/{x;p;x;}' >privileges.sql

Soubor privileges.sql, který je výstupem tohoto one-lineru pak vypadá třeba takto:

-- Grants for someuser@%
GRANT USAGE ON *.* TO 'someuser'@'%' IDENTIFIED BY PASSWORD '*38409EE19B8CDA8CD292007AE92F0436F8733E5F';
GRANT ALL PRIVILEGES ON `somedb`.* TO 'someuser'@'%';

-- Grants for otheruser@%
GRANT USAGE ON *.* TO 'otheruser'@'%' IDENTIFIED BY PASSWORD '*ACE854840EE8C90EE702B8783A8121D5FABC38DD';
GRANT ALL PRIVILEGES ON `otherdb`.* TO 'otheruser'@'%';
GRANT ALL PRIVILEGES ON `otherdb2`.* TO 'otheruser'@'%';
GRANT ALL PRIVILEGES ON `otherdb3`.* TO 'otheruser'@'%';

-- Grants for root@127.0.0.1
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*92E8E446A298EC98F48798AA0E60A099393370CF' WITH GRANT OPTION;

-- Grants for root@::1
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' IDENTIFIED BY PASSWORD '*92E8E446A298EC98F48798AA0E60A099393370CF' WITH GRANT OPTION;

-- Grants for root@host.example.com
GRANT ALL PRIVILEGES ON *.* TO 'root'@'host.example.com' IDENTIFIED BY PASSWORD '*92E8E446A298EC98F48798AA0E60A099393370CF' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'host.example.com' WITH GRANT OPTION;

-- Grants for root@localhost
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*92E8E446A298EC98F48798AA0E60A099393370CF' WITH GRANT OPTION;
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION;

Z čehož pak hezky vyplyne důvod mého zájmu o revalidaci těchto oprávnění.

Jak je vidno, všechna hesla jsou zahashována funkcí PASSWORD(), takže bude možno je v nezměněné podobě nahrát na cílový server. Dbejte však při manipulaci s tímto souborem zvýšené opatrnosti, protože PASSWORD() je pouze prachsprostý dvojitý SHA1 hash, takže jsem si celkem jistý, že rainbow tabulkáři v útrobách dark webu s tím počítají.

A zase zpátky

Na závěr přesunu na cílový server jak soubor s komprimovaným dumpem databází, tak i skriptík s nastavením oprávnění a data nasypu do doposud neposkvrněného databázového systému.

zcat databases.sql.gz | mysql
cat privileges.sql | mysql