# Actualitzar el sistema
sudo apt update && sudo apt upgrade -y

# Instal·lar MariaDB Server
sudo apt install mariadb-server mariadb-client -y

# Verificar l'estat del servei
sudo systemctl status mariadb

# Habilitar l'inici automàtic
sudo systemctl enable mariadb

# Executar l'assistent de seguretat inicial (imprescindible en producció)
sudo mysql_secure_installation

# Connectar-se al servidor
sudo mysql -u root -p

# Des de la consola MariaDB
MariaDB [(none)]> SELECT VERSION();
MariaDB [(none)]> SHOW DATABASES;
MariaDB [(none)]> STATUS;

[mysqld]

# --- Identificació ---
user            = mysql
pid-file        = /run/mysqld/mysqld.pid

# --- Xarxa ---
bind-address    = 127.0.0.1      # Canviar a 0.0.0.0 per a accés extern
port            = 3306
socket          = /run/mysqld/mysqld.sock

# --- Directoris ---
datadir         = /var/lib/mysql
tmpdir          = /tmp

# --- Joc de caràcters ---
character-set-server  = utf8mb4
collation-server      = utf8mb4_unicode_ci

# --- Connexions ---
max_connections         = 100
connect_timeout         = 10
wait_timeout            = 600
interactive_timeout     = 600

# --- Motor per defecte ---
default_storage_engine  = InnoDB

# --- Mida de la caché de consultes ---
query_cache_type        = 1
query_cache_size        = 16M

# --- InnoDB ---
innodb_buffer_pool_size = 256M   # ~70% de la RAM disponible per a BD
innodb_log_file_size    = 64M

# --- Logs ---
log_error               = /var/log/mysql/error.log
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow.log
long_query_time         = 2
log_bin                 = /var/log/mysql/mariadb-bin

sudo systemctl restart mariadb

# Comprovar que no hi ha errors
sudo journalctl -u mariadb -n 50

-- Llistar totes les bases de dades
SELECT schema_name FROM information_schema.schemata;

-- Llistar les taules d'una BD
SELECT table_name, table_type, engine, table_rows
FROM   information_schema.tables
WHERE  table_schema = 'institut';

-- Informació de les columnes d'una taula
SELECT column_name, data_type, is_nullable, column_default, column_key
FROM   information_schema.columns
WHERE  table_schema = 'institut' AND table_name = 'alumne';

-- Restriccions d'una taula
SELECT constraint_name, constraint_type
FROM   information_schema.table_constraints
WHERE  table_schema = 'institut' AND table_name = 'matricula';

-- Claus foranes
SELECT constraint_name, column_name, referenced_table_name, referenced_column_name
FROM   information_schema.key_column_usage
WHERE  table_schema = 'institut' AND referenced_table_name IS NOT NULL;

-- Crear una vista
CREATE VIEW v_alumnes_actius AS
    SELECT id_alumne, nom, cognoms, email
    FROM   alumne
    WHERE  actiu = TRUE;

-- Consultar la vista
SELECT * FROM v_alumnes_actius WHERE cognoms LIKE 'Mart%';

-- Crear una vista de resum per a professors
CREATE VIEW v_resum_notes AS
    SELECT mo.nom_modul,
           COUNT(m.id_matricula)          AS total_alumnes,
           ROUND(AVG(m.nota_final), 2)    AS nota_mitjana,
           SUM(m.nota_final >= 5)         AS aprovats,
           SUM(m.nota_final < 5)          AS suspesos
    FROM   matricula m
           INNER JOIN modul mo ON m.id_modul = mo.id_modul
    WHERE  m.nota_final IS NOT NULL
    GROUP  BY mo.id_modul, mo.nom_modul;

-- Modificar una vista
CREATE OR REPLACE VIEW v_alumnes_actius AS
    SELECT id_alumne, nom, cognoms, email, data_naix
    FROM   alumne
    WHERE  actiu = TRUE;

-- Eliminar una vista
DROP VIEW IF EXISTS v_alumnes_actius;

-- Vista actualitzable
CREATE VIEW v_contacte_alumne AS
    SELECT id_alumne, nom, cognoms, email, telefon
    FROM   alumne;

-- Actualitzar a través de la vista
UPDATE v_contacte_alumne
SET    email = 'nou@exemple.cat'
WHERE  id_alumne = 7;

CREATE VIEW clients AS SELECT * FROM alumne;

-- Usuari local (només des de localhost)
CREATE USER 'professor'@'localhost' IDENTIFIED BY 'contrasenya_segura';

-- Usuari remot (des de qualsevol IP)
CREATE USER 'app_user'@'%' IDENTIFIED BY 'contrasenya_app';

-- Usuari des d'una IP específica
CREATE USER 'dba'@'192.168.1.100' IDENTIFIED BY 'contrasenya_dba';

-- Canviar la contrasenya
ALTER USER 'professor'@'localhost' IDENTIFIED BY 'nova_contrasenya';

-- Forçar canvi de contrasenya en el pròxim accés
ALTER USER 'professor'@'localhost' PASSWORD EXPIRE;

-- Eliminar un usuari
DROP USER IF EXISTS 'professor'@'localhost';

SELECT user, host, password_expired, account_locked
FROM   mysql.user;

-- Tots els privilegis sobre una BD (usuari administrador d'una BD)
GRANT ALL PRIVILEGES ON institut.* TO 'admin_inst'@'localhost';

-- Privilegis de lectura sobre tota una BD
GRANT SELECT ON institut.* TO 'professor'@'localhost';

-- Privilegis de lectura i escriptura sobre una taula
GRANT SELECT, INSERT, UPDATE ON institut.matricula TO 'secretaria'@'localhost';

-- Privilegis a nivell de columna
GRANT SELECT (nom, cognoms, email) ON institut.alumne TO 'consultor'@'localhost';

-- Permetre que un usuari pugui atorgar privilegis a altres
GRANT SELECT ON institut.* TO 'cap_estudi'@'localhost' WITH GRANT OPTION;

-- Privilegi global per crear usuaris (DBA)
GRANT CREATE USER ON *.* TO 'dba'@'localhost';

-- Aplicar els canvis immediatament
FLUSH PRIVILEGES;

-- Veure els privilegis d'un usuari
SHOW GRANTS FOR 'professor'@'localhost';

-- Privilegis de l'usuari actual
SHOW GRANTS;

-- Revocar un privilegi concret
REVOKE INSERT ON institut.matricula FROM 'secretaria'@'localhost';

-- Revocar tots els privilegis sobre una BD
REVOKE ALL PRIVILEGES ON institut.* FROM 'professor'@'localhost';

FLUSH PRIVILEGES;

-- Crear rols
CREATE ROLE rol_lectura;
CREATE ROLE rol_secretaria;
CREATE ROLE rol_professor;

-- Assignar privilegis als rols
GRANT SELECT ON institut.* TO rol_lectura;
GRANT SELECT, INSERT, UPDATE ON institut.matricula TO rol_secretaria;
GRANT SELECT ON institut.* TO rol_professor;
GRANT SELECT ON v_resum_notes TO rol_professor;

-- Assignar rols a usuaris
GRANT rol_lectura    TO 'consultor'@'localhost';
GRANT rol_secretaria TO 'secretaria'@'localhost';
GRANT rol_professor  TO 'professor'@'localhost';

-- Activar el rol per defecte en iniciar sessió
SET DEFAULT ROLE rol_professor FOR 'professor'@'localhost';

-- Eliminar un rol
DROP ROLE IF EXISTS rol_lectura;

DELIMITER //

-- Procediment per matricular un alumne
CREATE PROCEDURE sp_matricular(
    IN  p_id_alumne INT,
    IN  p_id_modul  INT,
    IN  p_curs      YEAR,
    OUT p_resultat  VARCHAR(100)
)
BEGIN
    DECLARE v_existeix INT DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_resultat = 'Error: no s''ha pogut realitzar la matrícula.';
    END;

    -- Comprovar si ja existeix la matrícula
    SELECT COUNT(*) INTO v_existeix
    FROM   matricula
    WHERE  id_alumne = p_id_alumne
      AND  id_modul  = p_id_modul
      AND  curs      = p_curs;

    IF v_existeix > 0 THEN
        SET p_resultat = 'Ja existeix aquesta matrícula.';
    ELSE
        START TRANSACTION;
        INSERT INTO matricula (id_alumne, id_modul, curs)
        VALUES (p_id_alumne, p_id_modul, p_curs);
        COMMIT;
        SET p_resultat = CONCAT('Matrícula registrada. ID: ', LAST_INSERT_ID());
    END IF;
END //

DELIMITER ;

-- Cridar el procediment
CALL sp_matricular(5, 2, 2024, @res);
SELECT @res;

DELIMITER //

CREATE FUNCTION fn_nota_literal(p_nota DECIMAL(4,2))
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    RETURN CASE
        WHEN p_nota >= 9   THEN 'Excel·lent'
        WHEN p_nota >= 7   THEN 'Notable'
        WHEN p_nota >= 6   THEN 'Bé'
        WHEN p_nota >= 5   THEN 'Suficient'
        WHEN p_nota IS NULL THEN 'Pendent'
        ELSE 'Suspès'
    END;
END //

DELIMITER ;

-- Ús de la funció
SELECT a.nom, a.cognoms, m.nota_final, fn_nota_literal(m.nota_final) AS qualificació
FROM   matricula m
       INNER JOIN alumne a ON m.id_alumne = a.id_alumne
WHERE  m.id_modul = 1;

-- Taula d'auditoria
CREATE TABLE audit_notes (
    id_audit        INT AUTO_INCREMENT PRIMARY KEY,
    id_matricula    INT,
    nota_anterior   DECIMAL(4,2),
    nota_nova       DECIMAL(4,2),
    usuari          VARCHAR(100),
    data_canvi      DATETIME DEFAULT NOW()
);

DELIMITER //

-- Trigger BEFORE UPDATE: validar la nota
CREATE TRIGGER trg_valida_nota
BEFORE UPDATE ON matricula
FOR EACH ROW
BEGIN
    IF NEW.nota_final < 0 OR NEW.nota_final > 10 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'La nota ha de ser entre 0 i 10.';
    END IF;
END //

-- Trigger AFTER UPDATE: registrar l'historial de canvis
CREATE TRIGGER trg_audit_nota
AFTER UPDATE ON matricula
FOR EACH ROW
BEGIN
    IF OLD.nota_final <> NEW.nota_final OR
       (OLD.nota_final IS NULL AND NEW.nota_final IS NOT NULL) THEN
        INSERT INTO audit_notes (id_matricula, nota_anterior, nota_nova, usuari)
        VALUES (NEW.id_matricula, OLD.nota_final, NEW.nota_final, USER());
    END IF;
END //

-- Trigger BEFORE DELETE: impedir l'eliminació de matrícula amb nota
CREATE TRIGGER trg_protegeix_nota
BEFORE DELETE ON matricula
FOR EACH ROW
BEGIN
    IF OLD.nota_final IS NOT NULL THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'No es pot eliminar una matrícula amb nota registrada.';
    END IF;
END //

DELIMITER ;

-- Veure els triggers d'una BD
SHOW TRIGGERS FROM institut;
SHOW TRIGGERS FROM institut LIKE 'matricula';

-- Eliminar un trigger
DROP TRIGGER IF EXISTS trg_audit_nota;

-- Activar l'Event Scheduler
SET GLOBAL event_scheduler = ON;

-- Verificar
SHOW VARIABLES LIKE 'event_scheduler';

-- Esdeveniment que s'executa una vegada en una data futura
CREATE EVENT ev_netejar_proves
ON SCHEDULE AT '2025-07-01 03:00:00'
DO
    DELETE FROM alumne WHERE nom LIKE 'Nom_%' AND cognoms LIKE 'Cognom_%';

-- Esdeveniment recurrent: neteja diari dels logs antics
CREATE EVENT ev_neteja_audit
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
    DELETE FROM audit_notes WHERE data_canvi < DATE_SUB(NOW(), INTERVAL 90 DAY);

-- Esdeveniment recurrent que s'atura en una data
CREATE EVENT ev_backup_semanal
ON SCHEDULE EVERY 1 WEEK
STARTS '2025-01-06 02:00:00'
ENDS   '2025-12-31 02:00:00'
DO
    -- Aquí normalment es crida un procediment emmagatzemat
    CALL sp_backup_logic();

-- Veure els esdeveniments
SHOW EVENTS FROM institut;

-- Desactivar/activar un esdeveniment
ALTER EVENT ev_neteja_audit DISABLE;
ALTER EVENT ev_neteja_audit ENABLE;

-- Eliminar un esdeveniment
DROP EVENT IF EXISTS ev_netejar_proves;

DELIMITER //

CREATE PROCEDURE sp_exemple_errors(IN p_id INT)
BEGIN
    -- Gestió de l'error 1062 (entrada duplicada)
    DECLARE EXIT HANDLER FOR 1062
    BEGIN
        SELECT 'Error: ja existeix un registre amb aquest identificador.' AS missatge;
    END;

    -- Gestió de qualsevol error SQL
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
            @codi = MYSQL_ERRNO,
            @msg  = MESSAGE_TEXT;
        SELECT CONCAT('Error ', @codi, ': ', @msg) AS missatge;
        ROLLBACK;
    END;

    START TRANSACTION;
    INSERT INTO alumne (id_alumne, nom, cognoms) VALUES (p_id, 'Test', 'Test');
    COMMIT;
    SELECT 'Registre inserit correctament.' AS missatge;
END //

DELIMITER ;

-- Veure els processos actuals
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

-- Matar un procés bloquejat
KILL QUERY 42;  -- mata la consulta però manté la connexió
KILL 42;        -- tanca la connexió

-- Variables de sistema
SHOW GLOBAL STATUS;
SHOW GLOBAL STATUS LIKE 'Conn%';
SHOW GLOBAL STATUS LIKE 'Queries';
SHOW GLOBAL STATUS LIKE 'Innodb%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- Variables de configuració
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

-- Estadístiques de les taules
SHOW TABLE STATUS FROM institut;

-- Canviar paràmetres en calent (sense reiniciar)
SET GLOBAL max_connections = 200;
SET GLOBAL slow_query_log = ON;

# Instal·lar i executar mysqltuner
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl --user root --pass secret

-- Crear un índex en crear la taula
CREATE TABLE producte (
    id_producte  INT         PRIMARY KEY AUTO_INCREMENT,
    nom          VARCHAR(100) NOT NULL,
    categoria    VARCHAR(50),
    preu         DECIMAL(8,2),
    INDEX idx_categoria (categoria),
    INDEX idx_preu      (preu),
    UNIQUE INDEX uq_nom  (nom)
);

-- Afegir un índex a una taula existent
CREATE INDEX idx_cognom ON alumne (cognoms);

-- Índex compost (per a consultes que filtren per múltiples columnes)
CREATE INDEX idx_modul_curs ON matricula (id_modul, curs);

-- Índex FULLTEXT (per a cerques de text)
ALTER TABLE producte ADD FULLTEXT INDEX ft_nom (nom);

-- Veure els índexs d'una taula
SHOW INDEX FROM alumne;
SHOW INDEX FROM matricula;

-- Eliminar un índex
DROP INDEX idx_cognom ON alumne;
ALTER TABLE alumne DROP INDEX idx_cognom;

EXPLAIN
SELECT a.nom, a.cognoms, m.nota_final
FROM   matricula m
       INNER JOIN alumne a ON m.id_alumne = a.id_alumne
WHERE  m.id_modul = 3 AND m.nota_final >= 5;

-- Versió estesa
EXPLAIN FORMAT=JSON
SELECT * FROM alumne WHERE cognoms LIKE 'Puig%';

-- Analitzar i actualitzar estadístiques (per a l'optimitzador)
ANALYZE TABLE alumne, matricula;

-- Comprovar i reparar taules MyISAM
CHECK TABLE alumne;
REPAIR TABLE alumne;

-- Defragmentar una taula InnoDB (reconstrueix l'espai lliure)
OPTIMIZE TABLE matricula;

-- Compactar el log d'InnoDB (allibera espai)
SET GLOBAL innodb_fast_shutdown = 0;

-- Taula per emmagatzemar alertes
CREATE TABLE alerta_rendiment (
    id_alerta    INT AUTO_INCREMENT PRIMARY KEY,
    tipus        VARCHAR(50),
    valor        DECIMAL(10,2),
    descripcio   VARCHAR(255),
    data_alerta  DATETIME DEFAULT NOW()
);

DELIMITER //

CREATE PROCEDURE sp_comprova_connexions()
BEGIN
    DECLARE v_connexions INT;
    DECLARE v_max_conn   INT;

    SELECT VARIABLE_VALUE INTO v_connexions
    FROM information_schema.GLOBAL_STATUS
    WHERE VARIABLE_NAME = 'Threads_connected';

    SELECT VARIABLE_VALUE INTO v_max_conn
    FROM information_schema.GLOBAL_VARIABLES
    WHERE VARIABLE_NAME = 'max_connections';

    IF v_connexions > (v_max_conn * 0.8) THEN
        INSERT INTO alerta_rendiment (tipus, valor, descripcio)
        VALUES ('CONNEXIONS', v_connexions,
                CONCAT('Connexions actives (', v_connexions, ') superen el 80% del màxim (', v_max_conn, ')'));
    END IF;
END //

DELIMITER ;

-- Esdeveniment que executa la comprovació cada 5 minuts
CREATE EVENT ev_monitoratge
ON SCHEDULE EVERY 5 MINUTE
DO CALL sp_comprova_connexions();

# Verificar la mida del buffer pool d'InnoDB en calent
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# Ajustar el límit de fitxers oberts (important per a servidors amb moltes taules)
# /etc/security/limits.conf
mysql soft nofile 65536
mysql hard nofile 65536

# Desactivar l'accés a l'hora als fitxers (millora el rendiment d'E/S)
# /etc/fstab: afegir l'opció noatime a la partició de dades
# /dev/sdb1 /var/lib/mysql ext4 defaults,noatime 0 2

# Veure l'ús de memòria de MariaDB
ps aux | grep mysql
cat /proc/$(pidof mysqld)/status | grep VmRSS

Node 1: alumnes amb id_alumne 1 a 10.000
Node 2: alumnes amb id_alumne 10.001 a 20.000
Node 3: alumnes amb id_alumne > 20.000

Node 1: id_alumne, nom, cognoms, data_naix  (dades personals)
Node 2: id_alumne, email, telefon           (dades de contacte)

sudo systemctl restart mariadb

-- Crear un usuari de replicació al mestre
CREATE USER 'replicacio'@'%' IDENTIFIED BY 'repl_password_segur';
GRANT REPLICATION SLAVE ON *.* TO 'replicacio'@'%';
FLUSH PRIVILEGES;

-- Obtenir la posició actual del log binari
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- Anotar: File i Position
-- Exemple: mariadb-bin.000003 | 456

# Fer una còpia de la BD per inicialitzar l'esclau
mysqldump -u root -p --all-databases --master-data > /tmp/dump_master.sql
# Alliberar el bloqueig
mysql -u root -p -e "UNLOCK TABLES;"

# Transferir el dump a l'esclau
scp /tmp/dump_master.sql usuari@esclau:/tmp/

sudo systemctl restart mariadb

# Importar el dump del mestre
mysql -u root -p < /tmp/dump_master.sql

-- Configurar la connexió al mestre
CHANGE MASTER TO
    MASTER_HOST     = '192.168.1.10',
    MASTER_USER     = 'replicacio',
    MASTER_PASSWORD = 'repl_password_segur',
    MASTER_LOG_FILE = 'mariadb-bin.000003',
    MASTER_LOG_POS  = 456;

-- Iniciar la replicació
START SLAVE;

-- Verificar l'estat
SHOW SLAVE STATUS\G

Mestre (server-id=1)
    └── Esclau/Mestre intermediari (server-id=2)
            ├── Esclau final (server-id=3)
            └── Esclau final (server-id=4)

sudo apt install mariadb-server galera-4 -y

# Inicialitzar el clúster (només al primer node, una única vegada)
sudo galera_new_cluster

# Als altres nodes, iniciar normalment
sudo systemctl start mariadb

-- Verificar l'estat del clúster
SHOW STATUS LIKE 'wsrep%';
-- wsrep_cluster_size: nombre de nodes
-- wsrep_local_state_comment: Synced = OK
-- wsrep_connected: ON
-- wsrep_ready: ON

# Simular la caiguda d'un node
sudo systemctl stop mariadb   # En un dels nodes esclaus

# Verificar des del mestre que la replicació detecta la fallada
mysql -u root -p -e "SHOW SLAVE HOSTS;"
mysql -u root -p -e "SHOW MASTER STATUS;"

# Reiniciar el node aturat
sudo systemctl start mariadb

# Verificar que l'esclau es re-sincronitza
mysql -u root -p -e "SHOW SLAVE STATUS\G"
# Seconds_Behind_Master hauria de tornar a 0 progressivament

# En Galera: comprovar que el node tornat a unir-se es re-sincronitza (SST/IST)
mysql -u root -p -e "SHOW STATUS LIKE 'wsrep_local_state_comment';"
# Ha de passar de Joiner → Joined → Synced