MariaDB

Sissejuhatus

TODO

Tarkvara paigaldamine

Paigaldamine tavalisest paketihaldusest

# apt-get install mariadb-server

Seadistamine

Debian v. 11 operatsioonisüsteemi puhul kasutatakse vaikimisi utf8mb4, vt

/etc/mysql/mariadb.conf.d/50-server.cnf

st andmebaasi ja kasutajat on loomulik moodustada selliselt, nt wordpress jaoks sobib väga hästi

MariaDB [(none)]> CREATE DATABASE projektinimi CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
MariaDB [(none)]> grant all privileges on projektinimi.* to 'projektinimi'@'%' identified by 'parool';
MariaDB [(none)]> grant all privileges on projektinimi.* to 'projektinimi'@'localhost' identified by 'parool';

Collation jms

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'collation%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+
3 rows in set (0.001 sec)

ning

MariaDB [(none)]> SHOW COLLATION WHERE Sortlen LIKE '8' AND Charset LIKE 'utf8mb4';
+------------------------------+---------+------+---------+----------+---------+
| Collation                    | Charset | Id   | Default | Compiled | Sortlen |
+------------------------------+---------+------+---------+----------+---------+
| utf8mb4_unicode_ci           | utf8mb4 |  224 |         | Yes      |       8 |
| utf8mb4_icelandic_ci         | utf8mb4 |  225 |         | Yes      |       8 |
| utf8mb4_latvian_ci           | utf8mb4 |  226 |         | Yes      |       8 |
| utf8mb4_romanian_ci          | utf8mb4 |  227 |         | Yes      |       8 |
| utf8mb4_slovenian_ci         | utf8mb4 |  228 |         | Yes      |       8 |
| utf8mb4_polish_ci            | utf8mb4 |  229 |         | Yes      |       8 |
| utf8mb4_estonian_ci          | utf8mb4 |  230 |         | Yes      |       8 |
| utf8mb4_spanish_ci           | utf8mb4 |  231 |         | Yes      |       8 |
...

character set

MariaDB [(none)]> SHOW CHARACTER SET LIKE 'utf8%';
+---------+---------------+--------------------+--------+
| Charset | Description   | Default collation  | Maxlen |
+---------+---------------+--------------------+--------+
| utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
+---------+---------------+--------------------+--------+
2 rows in set (0.000 sec)
<pre>

ning

<pre>
MariaDB [(none)]> SHOW COLLATION WHERE Sortlen LIKE '8' AND Charset LIKE 'utf8';
+---------------------------+---------+------+---------+----------+---------+
| Collation                 | Charset | Id   | Default | Compiled | Sortlen |
+---------------------------+---------+------+---------+----------+---------+
| utf8_unicode_ci           | utf8    |  192 |         | Yes      |       8 |
| utf8_icelandic_ci         | utf8    |  193 |         | Yes      |       8 |
| utf8_latvian_ci           | utf8    |  194 |         | Yes      |       8 |
| utf8_romanian_ci          | utf8    |  195 |         | Yes      |       8 |
| utf8_slovenian_ci         | utf8    |  196 |         | Yes      |       8 |
| utf8_polish_ci            | utf8    |  197 |         | Yes      |       8 |
...

Kasulikud lisamaterjalid

• https://database.guide/show-the-collation-in-mariadb/
• https://mariadb.com/kb/en/show-collation/

Misc

MariaDB [(none)]> SHOW VARIABLES LIKE "%version%";
+-----------------------------------+------------------------------------------+
| Variable_name                     | Value                                    |
+-----------------------------------+------------------------------------------+
| in_predicate_conversion_threshold | 1000                                     |
| innodb_version                    | 10.5.28                                  |
| protocol_version                  | 10                                       |
| slave_type_conversions            |                                          |
| system_versioning_alter_history   | ERROR                                    |
| system_versioning_asof            | DEFAULT                                  |
| tls_version                       | TLSv1.2,TLSv1.3                          |
| version                           | 10.5.28-MariaDB-0+deb11u1                |
| version_comment                   | Debian 11                                |
| version_compile_machine           | x86_64                                   |
| version_compile_os                | debian-linux-gnu                         |
| version_malloc_library            | system                                   |
| version_source_revision           | 7eded23be6597b4c485e8cad1538f2ae14541f91 |
| version_ssl_library               | OpenSSL 1.1.1w  11 Sep 2023              |
| wsrep_patch_version               | wsrep_26.22                              |
+-----------------------------------+------------------------------------------+
15 rows in set (0.001 sec)

Paigaldamine tootja repost

TODO

mariadb-backup kasutamine

Tööpõhimõte

TODO

Kasutaja tekitamine

CREATE USER 'mariadb-backup'@'localhost' IDENTIFIED BY 'parool';
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'mariadb-backup'@'localhost';

Varundamine

TODO

root@pwrk-02:~# systemctl start mariadb
root@pwrk-02:~# mariadb-backup --backup --target-dir=/root/mariadb-backup-2 --user=mariadb-backup --password=parool

Taastamine

TODO

root@pwrk-02:~# systemctl stop mariadb
root@pwrk-02:~# ps U mysql
root@pwrk-02:~#

root@pwrk-02:~# mv /var/lib/mysql/ /var/lib/mysql-tootanud
root@pwrk-02:~# mariadb-backup --prepare --target-dir=/root/mariadb-backup-2
root@pwrk-02:~# mariadb-backup --copy-back --target-dir=/root/mariadb-backup-2
root@pwrk-02:~# chown -R mysql:mysql /var/lib/mysql
root@pwrk-02:~# systemctl start mariadb

Kasulikud lisamaterjalid

• https://mariadb.com/docs/server/server-usage/backup-and-restore/mariadb-backup/mariadb-backup-overview
• https://www.youtube.com/watch?v=xB4ImmmzXqU

2026 sügise alguse märkmed

Väited, mariadb v 11.8 jms

• mariadb puhul on oluline charset ja collation, nt vastavalt 'utf8mb4' ja 'utf8mb4_uca1400_ai_ci'
• serveri puhul on olemas nö 'server default' komplekt, tavaliselt 'utf8mb4' ja 'utf8mb4_uca1400_ai_ci'
• mariadb andmebaasi iseloom on erinevalt postgresql tarkvarast saab kasutada mitmesuguseid charset ja collate väärtusi samaaegselt, nt sama andmebaasi erinevate tablite puhul (utf8, latin1 jne)
• suvalised charset ja collation kombinatsioonid ei ole võimalikud, tavaliselt on charset jaoks üks kõige nö naturaalsem collation
• tavaliselt rakenduse paigaldamisel rakendus ise täpsustab oma tabelite charset ja collation väärtused - st see on installeri vastutus
• kui andmebaasi, tabeli jms tekitamisel ei täpsustata charset ega collation väärtusi, siis rakendab server vaikeväärtusi

character-set-server = utf8mb4
collation-server     = utf8mb4_uca1400_ai_ci

• kui andmebaasi ja tabeli jms tekitamisel täpsustatakse ainult charset, siis võimalik kirjeldada charset jaoks default collation väärtus

character_set_collations = utf8mb3=utf8mb3_general_ci,ucs2=ucs2_uca1400_ai_ci,utf8mb4=utf8mb4_uca1400_ai_ci,utf16=utf16_uca1400_ai_ci,utf32=utf32_uca1400_ai_ci

mis tähendab, et nt

• utf8mb3 charset puhul on default collation utf8mb3_general_ci
• utf8mb4 charset puhul on default collation utf8mb4_uca1400_ai_ci

Väited

• mariadb v. 9.4 puhul oli charset nimetus utf8 - tänapäeval nimetatakse seda utf8mb3 (mariadb oli multibyte pioneer ja alguses läks natuke nö viltu baitide arv)
• v. 9.4 ja utf8 puhul oli nö loomulik collation utf8_general_ci - täna tuntud utf8mb3_general_ci
• kui andmebaasi migratsiooni puhul andmebaasi serverisse loetakse sisse sql skripti kujul dump siis võib see sisaldada selgeid juhisied charset ja collation kohta ja iga tabeli puhul - kui mitte, siis võib rakenduda serveri nö default - sellises olukorras võib olla abi character_set_collations kasutamisest määratletud tulemuse saavutamisel

Kasulikud lisamaterjalid

• TODO