MariaDB

• INSTALLATION ET DEBUT
• Installation master/slave

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)