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)