← Índex

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.

💡
Nota

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:

sudo apt update

Instal·la MariaDB:

sudo apt install mariadb-server mariadb-client

Comprova que l’estat del servei:

sudo systemctl status mariadb

Sortida esperada:

● 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:

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:

sudo mariadb-secure-installation

Aquest script demana, entre altres coses:

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:~$
⚠️
Avís

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.

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ó:

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):

-- 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;
✅
Consell

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

-- 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!';
⚠️
Avís

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

-- 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

-- 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

-- 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;
✅
Consell

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:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
bind-address = 0.0.0.0
sudo systemctl restart mariadb

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

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

mysqldump -u root -p botiga > botiga_backup.sql

6.2. Backup de totes les bases de dades

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

6.3. Backup amb compressió

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ó:

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

6.5. Restauració

# 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
💡
Nota

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

sudo crontab -e
# 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
⚠️
Avís

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:

[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

-- 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:

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2
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

Domini Públic (CC0)