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)