MariaDB
INSTALLATION ET DEBUT
Prèrequis
- Un serveur Linux
- 500 MB de RAM
- 1 CPU
- 10 GB de disque
Installation
- Mettez à jour les repos
apt update && apt upgrade -y - Installez le paquet mariadb-server
apt install mariadb-server - Testez si le serveur est bien installé
root@sql-front-01:~# ss -lntp |grep 3306 LISTEN 0 80 127.0.0.1:3306 0.0.0.0:* users:(("mariadbd",pid=4326,fd=21)) root@sql-front-01:~# systemctl status mysql * mariadb.service - MariaDB 10.11.4 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; preset: enabled) Active: active (running) since Wed 2023-11-08 13:29:24 UTC; 2min 20s ago Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ Main PID: 4326 (mariadbd) Status: "Taking your SQL requests now..." Tasks: 11 (limit: 9395) Memory: 81.6M CPU: 813ms CGroup: /system.slice/mariadb.service `-4326 /usr/sbin/mariadbd Nov 08 13:29:24 sql-front-01 mariadbd[4326]: 2023-11-08 13:29:24 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool Nov 08 13:29:24 sql-front-01 mariadbd[4326]: 2023-11-08 13:29:24 0 [Note] InnoDB: Buffer pool(s) load completed at 231108 13:29:24 Nov 08 13:29:24 sql-front-01 mariadbd[4326]: 2023-11-08 13:29:24 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work. Nov 08 13:29:24 sql-front-01 mariadbd[4326]: 2023-11-08 13:29:24 0 [Note] Server socket created on IP: '127.0.0.1'. Nov 08 13:29:24 sql-front-01 mariadbd[4326]: 2023-11-08 13:29:24 0 [Note] /usr/sbin/mariadbd: ready for connections. Nov 08 13:29:24 sql-front-01 mariadbd[4326]: Version: '10.11.4-MariaDB-1~deb12u1' socket: '/run/mysqld/mysqld.sock' port: 3306 Debian 12 Nov 08 13:29:24 sql-front-01 systemd[1]: Started mariadb.service - MariaDB 10.11.4 database server. Nov 08 13:29:24 sql-front-01 /etc/mysql/debian-start[4341]: Upgrading MySQL tables if necessary. Nov 08 13:29:24 sql-front-01 /etc/mysql/debian-start[4352]: Checking for insecure root accounts. Nov 08 13:29:24 sql-front-01 /etc/mysql/debian-start[4356]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables
Gestion des bases de donnée
- Se connecter à MariaDB
root@sql-front-01:~# mysql Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 31 Server version: 10.11.4-MariaDB-1~deb12u1 Debian 12 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> - Lister les bases de donnée
MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.001 sec) - Créer une base de donnée
MariaDB [(none)]> create database TEST; Query OK, 1 row affected (0.001 sec) - Supprimer une base de donnée
MariaDB [(none)]> drop database TEST; Query OK, 0 rows affected (0.003 sec) - Sélectionner une base de donnée
MariaDB [(none)]> use mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed MariaDB [mysql]> - Lister les tables
MariaDB [mysql]> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | column_stats | | columns_priv | | db | | event | | func | | general_log | | global_priv | | gtid_slave_pos | | help_category | | help_keyword | | help_relation | | help_topic | | index_stats | | innodb_index_stats | | innodb_table_stats | | plugin | | proc | | procs_priv | | proxies_priv | | roles_mapping | | servers | | slow_log | | table_stats | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | transaction_registry | | user | +---------------------------+ 31 rows in set (0.001 sec)
Gestion des utilisateurs
- Créer un utilisateur
MariaDB [(none)]> create user 'test'@'localhost'; Query OK, 0 rows affected (0.002 sec) - Créer un utilisateur avec mot de passe
MariaDB [(none)]> create user 'test1'@'localhost' identified by 'password'; Query OK, 0 rows affected (0.001 sec) - Mettre à jour le mot de passe d'un utilisateur
MariaDB [(none)]> set password for 'test'@'localhost' = password ('password'); Query OK, 0 rows affected (0.002 sec) - Donner les droits a un utilisateur sur une base de donnée
MariaDB [(none)]> grant usage on test.* to 'test1'@'localhost'; Query OK, 0 rows affected (0.001 sec) - Supprimer un utilisateur
MariaDB [(none)]> drop user 'test1'@'localhost'; Query OK, 0 rows affected (0.002 sec)
Installation master/slave
Prérequis
- Avoir deux serveurs mariadb
Master
- Ajouter ou modifier ce qui suit dans le fichier /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld] bind-address = 0.0.0.0 server-id = 1 log_bin = mysql-bin - Redémarrer mariadb
- Création de l'utilisateur de réplication
CREATE USER 'repli'@'%' IDENTIFIED BY 'STRONGPASSWORD'; GRANT REPLICATION SLAVE ON *.* TO 'repli'@'%'; MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 663 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.000 sec)
SLAVE
- Ajouter ou modifier ce qui suit dans le fichier /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld] bind-address = 0.0.0.0 server-id = 2 log_bin = mysql-bin - Redémarrer mariadb
- Mise en place de la réplication
CHANGE MASTER TO -> MASTER_HOST='172.16.0.5', -> MASTER_USER='repli', -> MASTER_PASSWORD='STRONGPASSWORD', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=663; start slave; SHOW SLAVE STATUS\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.0.5 Master_User: repli Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 663 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 555 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Rewrite_DB: Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 663 Relay_Log_Space: 865 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 0 1 row in set (0.000 sec)
TEST
- Sur le Master céer une BDD
create database test; show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.001 sec) - Vérifier que la base de donné créée sur le master est bien répliqué
show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.001 sec)