Mysql / MariaDB

MySQL / MariaDB

Introducción

MySQL y MariaDB son sistemas de gestión de bases de datos relacionales (RDBMS) ampliamente utilizados en entornos Linux para aplicaciones web, servicios internos y sistemas empresariales.

MariaDB es un fork de MySQL y es la opción por defecto en la mayoría de distribuciones Linux modernas. Ambos utilizan SQL como lenguaje de consulta y comparten una sintaxis y funcionamiento muy similares.

Usos comunes:

• Aplicaciones web (LAMP / LEMP)
• Almacenamiento de datos estructurados
• Backends de aplicaciones y servicios
• Entornos de pruebas y producción

Instalación

En sistemas basados en RHEL:

dnf install -y mariadb-server mariadb

Habilitar y arrancar el servicio:

systemctl enable --now mariadb

Comprobar estado:

systemctl status mariadb

Configuración inicial

Ejecutar el asistente de seguridad inicial:

mysql_secure_installation

Acciones recomendadas:

• Establecer contraseña para root
• Eliminar usuarios anónimos
• Deshabilitar acceso remoto de root
• Eliminar base de datos de prueba
• Recargar tablas de privilegios

Ejemplo:

[root@icecube ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

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!
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]
 ... Success!

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]
 ... Success!

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...
 ... Success!
 - Removing privileges on test database...
 ... Success!

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!

Acceso al servidor

Acceder como root local:

mysql -u root -p

Salir:

EXIT;

Revisión básica de bases de datos y tablas

Listar bases de datos:

MariaDB [(none)]> show DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.003 sec)

MariaDB [(none)]>

Seleccionar una base de datos:

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

Listar tablas:

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.000 sec)

Ver estructura de una tabla:

MariaDB [mysql]> DESCRIBE servers;
+-------------+---------------+------+-----+---------+-------+
| Field       | Type          | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| Server_name | char(64)      | NO   | PRI |         |       |
| Host        | varchar(2048) | NO   |     |         |       |
| Db          | char(64)      | NO   |     |         |       |
| Username    | char(80)      | NO   |     |         |       |
| Password    | char(64)      | NO   |     |         |       |
| Port        | int(4)        | NO   |     | 0       |       |
| Socket      | char(108)     | NO   |     |         |       |
| Wrapper     | char(64)      | NO   |     |         |       |
| Owner       | varchar(512)  | NO   |     |         |       |
+-------------+---------------+------+-----+---------+-------+
9 rows in set (0.002 sec)

Mostrar contenido (limitado):

MariaDB [mysql]> SELECT User FROM user LIMIT 10;
+-------------+
| User        |
+-------------+
| mariadb.sys |
| mysql       |
| root        |
+-------------+
3 rows in set (0.001 sec)

Ver usuarios existentes:

SELECT user, host FROM mysql.user;

Hacer un backup de la base de datos:

mysqldump -u "$DB_USER" -p"$DB_PASS" "$DB_NAME" > "$BK_FOLDER/bck_mediawiki.sql

Crear base de datos

Ejemplo de creación de base de datos:

MariaDB [mysql]> CREATE DATABASE appdb;
Query OK, 1 row affected (0.000 sec)

Comprobar:

MariaDB [appdb]> SHOW DATABASES LIKE 'appdb';
+------------------+
| Database (appdb) |
+------------------+
| appdb            |
+------------------+
1 row in set (0.000 sec)

Crear usuario local y conexión localhost

Crear un usuario que solo pueda conectarse desde localhost:

MariaDB [appdb]> CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'tiovivo998877';
Query OK, 0 rows affected (0.002 sec)

Conceder privilegios sobre una base de datos concreta:

MariaDB [appdb]> GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
Query OK, 0 rows affected (0.003 sec)

Aplicar cambios:

MariaDB [appdb]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

Verificar permisos:

MariaDB [appdb]> SHOW GRANTS FOR 'appuser'@'localhost';
+----------------------------------------------------------------------------------------------------------------+
| Grants for appuser@localhost                                                                                   |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `appuser`@`localhost` IDENTIFIED BY PASSWORD '*63E643376E062C7625FE9E6CF075C1565DAC0759' |
| GRANT ALL PRIVILEGES ON `appdb`.* TO `appuser`@`localhost`                                                     |
+----------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

Probar conexión con el nuevo usuario

Salir del cliente root y probar:

[root@icecube ~]# mysql -u appuser -p appdb
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 10.5.29-MariaDB MariaDB Server

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 [appdb]>

Si el acceso es correcto, el usuario quedará conectado únicamente desde localhost y con permisos limitados a su base de datos.

Configuración de red (opcional)

Por defecto MariaDB escucha solo en localhost.

Comprobar:

[root@icecube ~]# ss -tulnp | grep 3306
tcp   LISTEN 0      80                 *:3306             *:*    users:(("mariadbd",pid=33155,fd=19))

Archivo de configuración principal:

/etc/my.cnf.d/mariadb-server.cnf

Parámetro relevante:

[mysqld]
bind-address = 127.0.0.1

Verificación y diagnóstico

Ver logs del servicio:

journalctl -u mariadb

Comprobar bases y tablas rápidamente:

mysql -u root -p -e "SHOW DATABASES;"

Buenas prácticas

• No usar root para aplicaciones
• Limitar usuarios por host (localhost)
• Asignar permisos mínimos necesarios
• Proteger copias de seguridad
• Mantener MariaDB actualizado