# MariaDB # INSTALLATION ET DEBUT #### Prèrequis - Un serveur Linux - 500 MB de RAM - 1 CPU - 10 GB de disque #### Installation - Mettez à jour les repos ```bash apt update && apt upgrade -y ``` - Installez le paquet mariadb-server ```bash apt install mariadb-server ``` - Testez si le serveur est bien installé ```bash 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 ```bash 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 ```mysql 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 ```mysql MariaDB [(none)]> create database TEST; Query OK, 1 row affected (0.001 sec) ``` - Supprimer une base de donnée ```mysql MariaDB [(none)]> drop database TEST; Query OK, 0 rows affected (0.003 sec) ``` - Sélectionner une base de donnée ```mysql 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 ```mysql 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 ```mysql MariaDB [(none)]> create user 'test'@'localhost'; Query OK, 0 rows affected (0.002 sec) ``` - Créer un utilisateur avec mot de passe ```mysql 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 ```mysql 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 ```mysql MariaDB [(none)]> grant usage on test.* to 'test1'@'localhost'; Query OK, 0 rows affected (0.001 sec) ``` - Supprimer un utilisateur ```mysql 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* ```bash [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 ```sql 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 ```bash [mysqld] bind-address = 0.0.0.0 server-id = 2 log_bin = mysql-bin ``` - Redémarrer mariadb - Mise en place de la réplication ```sql 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 ```sql 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é ```sql show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.001 sec) ```