MariaDB: erinevus redaktsioonide vahel

Allikas: Imre kasutab arvutit
Mine navigeerimisribaleMine otsikasti
Resümee puudub
 
(ei näidata sama kasutaja 12 vahepealset redaktsiooni)
8. rida: 8. rida:
   
 
# apt-get install mariadb-server
  
# apt-get install mariadb-server
  +
  +
===Seadistamine===
  +
  +
Debian v. 11 operatsioonisüsteemi puhul kasutatakse vaikimisi utf8mb4, vt
  +
  +
<pre>
  +
/etc/mysql/mariadb.conf.d/50-server.cnf
  +
</pre>
  +
  +
st andmebaasi ja kasutajat on loomulik moodustada selliselt, nt wordpress jaoks sobib väga hästi
  +
  +
<pre>
  +
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';
  +
</pre>
  +
  +
====Collation jms====
  +
  +
<pre>
  +
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)
  +
</pre>
  +
  +
ning
  +
  +
<pre>
  +
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 |
  +
...
  +
</pre>
  +
  +
character set
  +
  +
<pre>
  +
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 |
  +
...
  +
</pre>
  +
  +
Kasulikud lisamaterjalid
  +
  +
* https://database.guide/show-the-collation-in-mariadb/
  +
* https://mariadb.com/kb/en/show-collation/
  +
  +
====Misc====
  +
  +
<pre>
  +
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)
  +
</pre>
   
 
====Paigaldamine tootja repost====
  
====Paigaldamine tootja repost====
   
 
TODO
  
TODO
  +
  +
===mariadb-backup kasutamine===
  +
  +
====Tööpõhimõte====
  +
  +
TODO
  +
  +
====Kasutaja tekitamine====
  +
  +
<pre>
  +
CREATE USER 'mariadb-backup'@'localhost' IDENTIFIED BY 'parool';
  +
GRANT RELOAD, PROCESS, LOCK TABLES, BINLOG MONITOR ON *.* TO 'mariadb-backup'@'localhost';
  +
</pre>
  +
  +
====Varundamine====
  +
  +
TODO
  +
  +
<pre>
  +
root@pwrk-02:~# systemctl start mariadb
  +
root@pwrk-02:~# mariadb-backup --backup --target-dir=/root/mariadb-backup-2 --user=mariadb-backup --password=parool
  +
</pre>
  +
  +
====Taastamine====
  +
  +
TODO
  +
  +
<pre>
  +
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
  +
</pre>
  +
  +
====Kasulikud lisamaterjalid====
  +
  +
* https://mariadb.com/docs/server/server-usage/backup-and-restore/mariadb-backup/mariadb-backup-overview
  +
* https://www.youtube.com/watch?v=xB4ImmmzXqU
   
 
===Kasulikud lisamaterjalid===
  
===Kasulikud lisamaterjalid===

Viimane redaktsioon: 15. jaanuar 2026, kell 10:31

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

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

Kasulikud lisamaterjalid

  • TODO
Pärit leheküljelt "https://www.auul.pri.ee/wiki/index.php?title=MariaDB&oldid=2847"