# Servidor MariaDB/MySQL

MariaDB és un sistema gestor de bases de dades relacionals (SGBD), fork lliure de MySQL creat després que Oracle adquirís aquest darrer. És totalment compatible pel que fa a protocol i ordres amb MySQL, i és el SGBD per defecte a les distribucions Debian/Ubuntu.

> [!NOTE]
> Tot el que s'explica en aquest document amb `mariadb`/`mysql` és intercanviable: als sistemes moderns el client s'anomena `mariadb`, però es manté `mysql` com a àlies per compatibilitat.

# 1. Instal·lació

Actualitza la llista de paquets:

```bash
sudo apt update
```

Instal·la MariaDB:

```bash
sudo apt install mariadb-server mariadb-client
```

Comprova que l'estat del servei:

```bash
sudo systemctl status mariadb
```

Sortida esperada:

```ini
● mariadb.service - MariaDB 11.8.6 database server
     Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; preset: enabled)
     Active: active (running) since Wed 2026-08-12 07:33:20 UTC; 5s ago
 Invocation: 4a9c17cb52ab424f8837ee2dbc69c856
       Docs: man:mariadbd(8)
             https://mariadb.com/kb/en/library/systemd/
   Main PID: 19597 (mariadbd)
     Status: "Taking your SQL requests now..."
      Tasks: 13 (limit: 11340)
     Memory: 93.1M (peak: 97.6M)
        CPU: 1.419s
     CGroup: /system.slice/mariadb.service
             └─19597 /usr/sbin/mariadbd
```

Habilitat l'engegada del servei a l'arrencada:

```bash
sudo systemctl enable mariadb
```

## 1.1. Configuració inicial segura

MariaDB inclou un script que aplica una sèrie de bones pràctiques bàsiques de seguretat:

```bash
sudo mariadb-secure-installation
```

Aquest script demana, entre altres coses:

- Establir contrasenya per a l'usuari `root` de la base de dades
- Eliminar usuaris anònims
- Desactivar l'accés remot de `root`
- Eliminar la base de dades `test`
- Recarregar les taules de privilegis

```ini
ramon@server:~$ sudo mariadb-secure-installation

NOTE: MariaDB is secure by default in Debian. Running this script is
      useless at best, and misleading at worst. This script will be
      removed in a future MariaDB release in Debian. Please read
      /usr/share/doc/mariadb-server/README.Debian.gz for details.

Enter root user password or leave blank:

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] 
Enabled successfully (or at least no errors was emitted)!
Reloading privilege tables..
 ... Success!


You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] 
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] 
SQL executed without errors!
The operation might have been successful, or it might have not done anything.

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] 
SQL executed without errors!
The operation might have been successful, or it might have not done anything.

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] 
 - Dropping test database...
SQL executed without errors!
The operation might have been successful, or it might have not done anything.
 - Removing privileges on test database...
SQL executed without errors!
The operation might have been successful, or it might have not done anything.

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] 
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
ramon@server:~$ 
```

> [!WARNING]
> L'usuari `root` de MariaDB **no és el mateix** que el `root` del sistema operatiu. Són dos espais de privilegis totalment independents.

## 1.2. Autenticació de root: `unix_socket` vs. contrasenya

A Debian/Ubuntu, per defecte, l'usuari `root` de MariaDB s'autentica mitjançant el connector `unix_socket`: només es pot accedir com a `root` de MariaDB si ets `root` (o uses `sudo`) al sistema operatiu, sense necessitat de contrasenya.

```bash
sudo mysql
```

Si vols que `root` s'autentiqui amb contrasenya (per exemple per accedir des d'una eina gràfica com a `root`), cal canviar el mètode d'autenticació:

```sql
ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password USING PASSWORD('la_teva_contrasenya');
FLUSH PRIVILEGES;
```

# 2. Conceptes bàsics: bases de dades i taules

Un cop dins del client (`sudo mysql` o `mysql -u usuari -p`):

```sql
-- Llistar bases de dades
SHOW DATABASES;

-- Crear una base de dades
CREATE DATABASE botiga CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Seleccionar-la per treballar-hi
USE botiga;

-- Crear una taula
CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    data_alta DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Veure l'estructura d'una taula
DESCRIBE clients;

-- Eliminar una base de dades
DROP DATABASE botiga;
```

> [!TIP]
> Fes servir sempre `utf8mb4` (no `utf8`) com a joc de caràcters per defecte: `utf8` a MySQL/MariaDB és una implementació incompleta que no admet emojis ni certs caràcters Unicode de 4 bytes.

# 3. Gestió d'usuaris

## 3.1. Crear usuaris

```sql
-- Usuari que només pot connectar-se des de localhost
CREATE USER 'app_botiga'@'localhost' IDENTIFIED BY 'ContrasenyaSegura123!';

-- Usuari que pot connectar-se des de qualsevol màquina
CREATE USER 'admin_remot'@'%' IDENTIFIED BY 'AltraContrasenya456!';

-- Usuari que només pot connectar-se des d'una IP concreta
CREATE USER 'servidor_web'@'10.0.2.20' IDENTIFIED BY 'Contrasenya789!';
```

> [!WARNING]
> L'usuari `'nom'@'%'` pot connectar-se des de qualsevol origen. Combinat amb l'obertura del port 3306 a la xarxa, és un risc de seguretat important. Limita sempre l'origen (`localhost` o una IP/subxarxa concreta) al mínim necessari.

## 3.2. Llistar i eliminar usuaris

```sql
-- Llistar usuaris i el seu origen d'accés
SELECT User, Host FROM mysql.user;

-- Canviar la contrasenya d'un usuari existent
ALTER USER 'app_botiga'@'localhost' IDENTIFIED BY 'NovaContrasenya!';

-- Eliminar un usuari
DROP USER 'admin_remot'@'%';
```

# 4. Permisos (`GRANT` / `REVOKE`)

Els permisos a MariaDB s'assignen combinant un **privilegi**, un **àmbit** (base de dades, taula o global) i un **usuari**.

## 4.1. Assignar permisos

```sql
-- Tots els privilegis sobre una base de dades concreta
GRANT ALL PRIVILEGES ON botiga.* TO 'app_botiga'@'localhost';

-- Només lectura sobre una base de dades
GRANT SELECT ON botiga.* TO 'lector'@'localhost';

-- Permisos concrets sobre una taula
GRANT SELECT, INSERT, UPDATE ON botiga.clients TO 'servidor_web'@'10.0.2.20';

-- Privilegis globals (administració)
GRANT ALL PRIVILEGES ON *.* TO 'superadmin'@'localhost' WITH GRANT OPTION;

-- Aplicar els canvis
FLUSH PRIVILEGES;
```

| Privilegi | Descripció |
|---|------|
| `SELECT` | Llegir dades |
| `INSERT` | Inserir files noves |
| `UPDATE` | Modificar files existents |
| `DELETE` | Eliminar files |
| `CREATE` | Crear bases de dades/taules |
| `DROP` | Eliminar bases de dades/taules |
| `ALTER` | Modificar l'estructura de taules |
| `GRANT OPTION` | Poder concedir els mateixos privilegis a altres usuaris |
| `ALL PRIVILEGES` | Tots els privilegis anteriors |

## 4.2. Consultar i revocar permisos

```sql
-- Veure els permisos d'un usuari
SHOW GRANTS FOR 'app_botiga'@'localhost';

-- Revocar un permís concret
REVOKE INSERT ON botiga.* FROM 'app_botiga'@'localhost';

-- Revocar tots els permisos
REVOKE ALL PRIVILEGES ON botiga.* FROM 'app_botiga'@'localhost';
FLUSH PRIVILEGES;
```

> [!TIP]
> Aplica el principi de mínim privilegi: crea un usuari diferent per a cada aplicació/servei i concedeix-li només els permisos estrictament necessaris sobre la base de dades que utilitza, mai `ALL PRIVILEGES ON *.*`.

# 5. Accés remot

Per defecte, MariaDB només escolta a `127.0.0.1`. Per permetre connexions remotes:

```bash
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
```

```ini
[mysqld]
bind-address = 0.0.0.0
```

```bash
sudo systemctl restart mariadb
```

Si tens tallafocs actiu, obre el port 3306 només per als orígens necessaris:

```bash
sudo ufw allow from 10.0.2.0/24 to any port 3306
```

# 6. Còpies de seguretat amb `mysqldump`

## 6.1. Backup d'una base de dades

```bash
mysqldump -u root -p botiga > botiga_backup.sql
```

## 6.2. Backup de totes les bases de dades

```bash
mysqldump -u root -p --all-databases > backup_complet.sql
```

## 6.3. Backup amb compressió

```bash
mysqldump -u root -p botiga | gzip > botiga_backup.sql.gz
```

## 6.4. Opcions habituals

| Opció | Descripció |
|---|------|
| `--single-transaction` | Backup consistent sense bloquejar taules (recomanat per InnoDB) |
| `--routines` | Inclou procediments emmagatzemats i funcions |
| `--triggers` | Inclou triggers (per defecte ja s'inclouen) |
| `--events` | Inclou esdeveniments programats |
| `--no-data` | Exporta només l'estructura, sense dades |
| `--add-drop-database` | Afegeix `DROP DATABASE` abans de crear-la, útil per restauracions netes |

Exemple de backup complet i consistent per a producció:

```bash
mysqldump -u root -p --single-transaction --routines --triggers --events \
  botiga | gzip > botiga_$(date +%Y%m%d).sql.gz
```

## 6.5. Restauració

```bash
# Des d'un fitxer sense comprimir
mysql -u root -p botiga < botiga_backup.sql

# Des d'un fitxer comprimit
gunzip < botiga_backup.sql.gz | mysql -u root -p botiga
```

> [!NOTE]
> Si la base de dades de destí no existeix encara, cal crear-la abans (`CREATE DATABASE botiga;`) tret que el dump inclogui `CREATE DATABASE` (opció `--databases` o `--all-databases`).

## 6.6. Automatitzar backups amb cron

```bash
sudo crontab -e
```

```nano
# Backup diari a les 2:00 amb rotació de 7 dies
0 2 * * * mysqldump -u root -p'contrasenya' --single-transaction botiga | gzip > /var/backups/mysql/botiga_$(date +\%Y\%m\%d).sql.gz
0 3 * * * find /var/backups/mysql/ -name "*.sql.gz" -mtime +7 -delete
```

> [!WARNING]
> Escriure la contrasenya directament a la línia d'ordres o al crontab és insegur (queda visible amb `ps` i a l'historial). L'alternativa és fer servir un fitxer `~/.my.cnf` amb permisos `600`:
> ```ini
> [client]
> user=root
> password=contrasenya
> ```
> D'aquesta manera n'hi ha prou amb `mysqldump botiga | gzip > ...` sense passar credencials explícites.

# 7. Ordres de manteniment útils

```sql
-- Mida de cada base de dades
SELECT table_schema AS "Base de dades",
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Mida (MB)"
FROM information_schema.tables
GROUP BY table_schema;

-- Processos actius
SHOW PROCESSLIST;

-- Estat i variables del servidor
SHOW STATUS;
SHOW VARIABLES;

-- Optimitzar una taula (recuperar espai fragmentat)
OPTIMIZE TABLE clients;

-- Comprovar i reparar una taula (MyISAM)
CHECK TABLE clients;
REPAIR TABLE clients;
```

# 8. Registre de connexions i consultes lentes

Per depurar rendiment, és útil activar el registre de consultes lentes:

```bash
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
```

```ini
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2
```

```bash
sudo systemctl restart mariadb
sudo tail -f /var/log/mysql/mariadb-slow.log
```

# Resum

| Tasca | Ordre |
|---|---|
| Accedir com a root | `sudo mysql` |
| Crear base de dades | `CREATE DATABASE nom;` |
| Crear usuari | `CREATE USER 'u'@'host' IDENTIFIED BY 'pass';` |
| Assignar permisos | `GRANT privilegis ON bd.* TO 'u'@'host';` |
| Veure permisos | `SHOW GRANTS FOR 'u'@'host';` |
| Backup d'una BD | `mysqldump -u root -p bd > fitxer.sql` |
| Restaurar | `mysql -u root -p bd < fitxer.sql` |

#### Versions d'aquest document

> + [HTML](https://proferamon.com/tic/mariadb.html)
> + [PDF](https://proferamon.com/tic/pdf/mariadb.pdf)
> + [ODT](https://proferamon.com/tic/odt/mariadb.odt)
> + [MD](https://proferamon.com/tic/md/mariadb.md)

[Domini Públic (CC0)](https://creativecommons.org/publicdomain/zero/1.0/deed.ca)