Ver el código de «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:

<syntaxhighlight lang="bash">
dnf install -y mariadb-server mariadb
</syntaxhighlight>

Habilitar y arrancar el servicio:

<syntaxhighlight lang="bash">
systemctl enable --now mariadb
</syntaxhighlight>

Comprobar estado:

<syntaxhighlight lang="bash">
systemctl status mariadb
</syntaxhighlight>



== Configuración inicial ==

Ejecutar el asistente de seguridad inicial:

<syntaxhighlight lang="bash">
mysql_secure_installation
</syntaxhighlight>

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:


<syntaxhighlight lang="bash">

[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!
</syntaxhighlight>

== Acceso al servidor ==

Acceder como root local:

<syntaxhighlight lang="bash">
mysql -u root -p
</syntaxhighlight>

Salir:

<syntaxhighlight lang="sql">
EXIT;
</syntaxhighlight>


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

Listar bases de datos:

<syntaxhighlight lang="sql">
MariaDB [(none)]> show DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.003 sec)

MariaDB [(none)]>

</syntaxhighlight>

Seleccionar una base de datos:

<syntaxhighlight lang="sql">
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]>
</syntaxhighlight>

Listar tablas:

<syntaxhighlight lang="sql">
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)
</syntaxhighlight>

Ver estructura de una tabla:

<syntaxhighlight lang="sql">
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)
</syntaxhighlight>

Mostrar contenido (limitado):

<syntaxhighlight lang="sql">

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

Ver usuarios existentes:

<syntaxhighlight lang="sql">
SELECT user, host FROM mysql.user;
</syntaxhighlight>


== Crear base de datos ==

Ejemplo de creación de base de datos:

<syntaxhighlight lang="sql">
MariaDB [mysql]> CREATE DATABASE appdb;
Query OK, 1 row affected (0.000 sec)
</syntaxhighlight>

Comprobar:

<syntaxhighlight lang="sql">
MariaDB [appdb]> SHOW DATABASES LIKE 'appdb';
+------------------+
| Database (appdb) |
+------------------+
| appdb            |
+------------------+
1 row in set (0.000 sec)
</syntaxhighlight>


== Crear usuario local y conexión localhost ==

Crear un usuario que solo pueda conectarse desde localhost:

<syntaxhighlight lang="sql">
MariaDB [appdb]> CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'tiovivo998877';
Query OK, 0 rows affected (0.002 sec)
</syntaxhighlight>

Conceder privilegios sobre una base de datos concreta:

<syntaxhighlight lang="sql">
MariaDB [appdb]> GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
Query OK, 0 rows affected (0.003 sec)
</syntaxhighlight>

Aplicar cambios:

<syntaxhighlight lang="sql">
MariaDB [appdb]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)
</syntaxhighlight>

Verificar permisos:

<syntaxhighlight lang="sql">
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)
</syntaxhighlight>


== Probar conexión con el nuevo usuario ==

Salir del cliente root y probar:

<syntaxhighlight lang="bash">
[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]>
</syntaxhighlight>

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:

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

Archivo de configuración principal:

<syntaxhighlight lang="bash">
/etc/my.cnf.d/mariadb-server.cnf
</syntaxhighlight>

Parámetro relevante:

<syntaxhighlight lang="ini">
[mysqld]
bind-address = 127.0.0.1
</syntaxhighlight>


== Verificación y diagnóstico ==

Ver logs del servicio:

<syntaxhighlight lang="bash">
journalctl -u mariadb
</syntaxhighlight>

Comprobar bases y tablas rápidamente:

<syntaxhighlight lang="bash">
mysql -u root -p -e "SHOW DATABASES;"
</syntaxhighlight>


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