Práctica 1. Instalación y configuración de MySQL Server en Ubuntu Server

Apuntes de BD para DAW, DAM y ASIR

Curso 2024/2025

1 SGBDRs más usados actualmente

Un Sistema de Gestión de Bases de Datos Relacionales (SGBDRs) es un programa que nos permite crear, actualizar y administrar una base de datos relacional. La mayoría de los sistemas de gestión de bases de datos relacionales utilizan SQL para acceder a la base de datos.

Hay que tener en cuenta que pueden existir diferencias en la sintaxis SQL utilizada por los distintos SGBDRs.

Los SGBDRs más usados actualmente son:

2 MySQL Server

2.1 Instalación y configuración de MySQL Server en Ubuntu Server

En esta práctica vamos a crear (1) una instancia en [OpenStack][17_p1 o (2) una máquina virtual en VirtualBox con la última versión de Ubuntu Server donde vamos a instalar todo el software necesario para realizar nuestras primeras prácticas con el sistema gestor de bases de datos MySQL.

2.2 Opción 1. Instancia en OpenStack

Para crear una instancia en la infraestructura de OpenStack del centro, se recomienda seguir los pasos de la guía de uso:

Al crear la instancia podemos hacer uso de cloud-init para realizar la configuración inicial.

La configuración se define en unos archivos de texto plano con formato YAML que contienen las instrucciones de configuración inicial de la instancia. Por ejemplo, el siguiente archivo de configuración va a realizar las siguientes acciones:

#cloud-config
password: nueva_password
chpasswd: { expire: False }
ssh_pwauth: True
package_update: true
package_upgrade: true
packages:
  - mysql-server

runcmd:
  # Configuramos el parámetro bind-address para permitir conexiones remotas
  - sed -i 's/^bind-address.*/bind-address = 0.0.0.0/' /etc/mysql/mysql.conf.d/mysqld.cnf
  
  # Reiniciamos el servicio de MySQL
  - systemctl restart mysql

  # Creamos un usuario para conectarnos a MySQL desde cualquier host
  - |
    mysql -e "CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'root';"
    mysql -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';"
    mysql -e "FLUSH PRIVILEGES;"

Nota:Recuerde que tendrá que sustituir los valores de las contraseñas del ejemplo por los valores que desee.

2.3 Opción 2. Máquina virtual en VirtualBox

Si decide crear una máquina virtual en VirtualBox, tenga en cuenta que la tarjeta de red de la máquina virtual tiene que estar configurada en modo adaptador puente, y que debemos asignarle una dirección IP estática manualmente.

Configuración de la interfaz de red en Ubuntu

La configuración de las interfaces de red se encuentra en el directorio /etc/netplan. En este directorio encontraremos un archivo de configuración con extensión .yaml.

Los archivos de configuración de las interfaces de red utilizados en las versiones de Ubuntu 18.04 y 20.04, tienen los siguientes nombres:

Si nuestro archivo de configuración se llama 00-installer-config.yaml deberíamos editarlo para añadir nuestra configuración de red.

sudo nano /etc/netplan/00-installer-config.yaml

El archivo deberá tener un contenido similar al este:

# This file is generated from information provided by
# the datasource.  Changes to it will not persist across an instance.
# To disable cloud-init's network configuration capabilities, write a file
# /etc/cloud/cloud.cfg.d/99-disable-network-config.cfg with the following:
# network: {config: disabled}
network:
    ethernets:
        enp0s3:
            addresses: []
            dhcp4: true
    version: 2

Ahora deberemos añadir nuestra configuración de red. Tenga en cuenta que en los archivos YAML no está permitido el uso de caracteres de tabulación para indentar, para indentar sólo podemos usar espacios en blanco.

A continuación se muestra un ejemplo de cómo sería la configuración de red para una máquina con la dirección IP 192.168.21.101.

# This file is generated from information provided by
# the datasource.  Changes to it will not persist across an instance.
# To disable cloud-init's network configuration capabilities, write a file
# /etc/cloud/cloud.cfg.d/99-disable-network-config.cfg with the following:
# network: {config: disabled}
network:
    ethernets:
        enp0s3:
            addresses: [192.168.21.101/24]
            gateway4: 192.168.21.99
            nameservers:
              addresses: [8.8.8.8,8.8.4.4]
            dhcp4: no
    version: 2

Tenga en cuenta que en su caso tendrá que sustituir la dirección IP 192.168.21.101 por la dirección IP que le haya asignado el profesor.

Una vez hecho esto aplicamos la nueva configuración de red.

sudo netplan apply

3 MySQL

3.1 Instalación de MySQL server

En primer lugar actualizamos la lista de paquetes del repositorio:

sudo apt update

Realizamos la instalación de mysql-server:

sudo apt install mysql-server -y

3.2 Cómo acceder a MySQL Server desde consola con el usuario root

Una vez que hemos instalado MySQL Server en nuestro sistema vamos a acceder a la consola de MySQL. Esto podemos hacerlo de dos formas.

Opción 1

Ejecutamos el siguiente comando:

sudo mysql

Una vez hecho esto ya tendríamos acceso a la consola de MySQL como root.

Opción 2

Iniciamos una sesión como root:

sudo su

Una vez que hemos iniciado una sesión como root vamos a iniciar la consola de MySQL también como root sin necesidad de indicarle ningún password (no es necesario usar el modificador -p).

mysql -u root

Una vez hecho esto ya tendríamos acceso a la consola de MySQL como root.

3.3 Cómo cambiar la contraseña del usuario root (Método 1)

En primer lugar accedemos a la consola de MySQL como root y seleccionamos la base de datos mysql.

USE mysql;

Vamos a revisar los usuarios que existen en MySQL y qué método tienen establecido para autenticar.

SELECT User, Host, plugin FROM user;

+------------------+-----------+-----------------------+
| User             | Host      | plugin                |
+------------------+-----------+-----------------------+
| root             | localhost | auth_socket           |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| debian-sys-maint | localhost | caching_sha2_password |
+------------------+-----------+-----------------------+
4 rows in set (0.00 sec)

Podemos ver que para el usuario root@localhost el método de autenticación es auth_socket. Esto quiere decir que el usuario root usará las mismas credenciales que tiene en el sistema operativo.

En la columna plugin también podemos encontrar estas dos opciones:

Por lo tanto, si queremos cambiar la contraseña de root tendremos que cambiar el método de autenticación a mysql_native_password o caching_sha2_password. Podemos hacerlo ejecutando alguna de las siguientes sentencias SQL, teniendo en cuenta que tendrá que sustituir nueva_contraseña por la contraseña que desee.

Opción mysql_native_password (Antigua)

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'nueva_contraseña';

Con esta sentencia SQL estamos indicando que la nueva contraseña del usuario root estará cifrada con el plugin mysql_native_password.

Opción caching_sha2_password (Nueva)

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'nueva_contraseña';

Con esta sentencia SQL estamos indicando que la nueva contraseña del usuario root estará cifrada con el plugin caching_sha2_password.

La documentación oficial de MySQL indica que la nueva opción caching_sha2_password es más segura que la antigua opción mysql_native_password y que por lo tanto es la opción recomendada.

Después habrá que ejecutar el siguiente comando para que las actualizaciones realizadas tengan efecto.

FLUSH PRIVILEGES;

3.4 Cómo cambiar la contraseña del usuario root (Método 2 --skip-grant-tables)

Otra opción para modificar la contraseña del usuario root en MySQL es iniciar el servicio con la opción --skip-grant-tables, que permite que cualquier usuario se pueda conectar sin necesidad de realizar el proceso de autenticación.

En primer lugar detenemos el servicio de MySQL.

sudo systemctl stop mysql

Como vamos a iniciar el proceso de MySQL de forma manual vamos a necesitar crear de forma manual el directorio /var/run/mysqld y asignarle que el propietario de este directorio es mysql del grupo mysql.

sudo mkdir -p /var/run/mysqld
sudo chown mysql:mysql /var/run/mysqld

Una vez que hemos detenido el servicio de MySQL, lo volvemos a iniciar pero haciendo uso del comando que está en la ruta /usr/sbin/mysqld y pasándole como parámetro la opción --skip-grant-tables que permite conectarnos sin contraseña y con todos los privilegios. Esta opción deshabilita la gestión de usuarios y por lo tanto, no es posible utilizar sentencias como ALTER USER y SET PASSWORD. Al utilizar la opción --skip-grant-tables se habilita automáticamente la opción --skip-networking para desactivar las conexiones remotas como mecanismo de seguridad. Observe que hemos añadido & al final del comando para que el proceso se ejecute en segundo plano.

sudo /usr/sbin/mysqld --skip-grant-tables &

Podemos comprobar que le proceso se está ejectuando de forma correcta haciendo un listado de todos los procesos que están en ejecución con ps aux y buscando en la lista el nombre del proceso con grep mysqld.

ps aux | grep mysqld

Una vez que hemos comprobado que el proceso está en ejecución, podemos conectarnos al servidor de MySQL. No es necesario indicar ningún usuario ni contraseña, nos conectaremos automáticamente con todos los privilegios como root.

mysql

Ahora tendremos que indicarle al servidor que tiene que recargar las tablas encargadas de la autenticación de los usuarios para poder activar la gestión de usuarios. Recuerda que la opción --skip-grant-tables deshabilita esta funcionalidad.

FLUSH PRIVILEGES;

Modificamos la contraseña del usuario ‘root’@‘localhost’, reemplazando nueva_contraseña por la contraseña que queramos asignar.

ALTER USER 'root'@'localhost' IDENTIFIED BY 'nueva_contraseña';

Salimos del cliente de MySQL.

exit;

Detenemos el proceso mysqld.

sudo pkill mysqld

Reiniciamos el servicio de MySQL.

sudo systemctl start mysql

3.5 Configuración de MySQL para permitir conexiones remotas

En la instalación por defecto, MySQL Server sólo acepta conexiones que vengan de localhost. Por lo tanto, vamos a ver cómo tenemos que configurar MySQL Server para que acepte conexiones remotas.

En primer lugar, edita el siguiente archivo de configuración:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Busca la directiva de configuración bind-address dentro del bloque de [mysqld]:

[mysqld]
bind-address = 127.0.0.1

En la configuración por defecto, MySQL sólo permite conexiones desde localhost (127.0.0.1). Habrá que modificar este valor por la dirección IP de la máquina donde se está ejecutando el servicio de MySQL.

[mysqld]
bind-address = IP_SERVIDOR_MYSQL

Si nuestra máquina dispone más de una interfaz de red podemos poner la dirección IP 0.0.0.0 para permitir que se puedan conectar a MySQL desde cualquiera de las interfaces de red disponibles de la máquina.

[mysqld]
bind-address = 0.0.0.0

Una vez hecho esto tenemos que reiniciar el servicio de MySQL:

sudo systemctl restart mysql

3.6 Consultar los usuarios creados en MySQL Server

Los usuarios de MySQL se almacenan en la tabla mysql.user. La clave primaria de esta tabla está formada por los valores user y host, de modo que cada fila vendrá identificada por un nombre de usuario y el host desde el que puede conectarse.

La siguiente consulta nos devuelve el listado de usuarios que tenemos en MySQL y desde qué interfaz de red del servidor MySQL pueden conectarse:

SELECT user,host FROM mysql.user;

En nuestra caso la consulta anterior devuelve el siguiente resultado:

+------------------+--------------+
| user             | host         |
+------------------+--------------+
| root             | localhost    |
| debian-sys-maint | localhost    |
| mysql.session    | localhost    |
| mysql.sys        | localhost    |
+------------------+--------------+

Todos los usuarios que existen actualmente en la base de datos sólo pueden conectarse desde la propia máquina donde está instalado MySQL Server (localhost).

El siguiente diagrama muestra un ejemplo de dos usuarios que se están conectando a una máquina con MySQL Server. El usuario root@localhost es un usuario que sólo puede conectarse desde la máquina local y el usuario root@'%' es un usuario que se puede conectar desde una máquina remota.

Veamos cómo podemos crear un nuevo usuario que pueda conectarse desde una máquina remota.

3.7 Creamos un usuario para conectarnos desde una máquina remota

Ahora vamos a crear un usuario root@% para conectarnos a MySQL Server desde una máquina remota.

En primer lugar, nos conectamos a la consola de MySQL Server con el usuario root@localhost desde la misma máquina.

sudo mysql

A partir de la versión 8.0 de MySQL no es posible crear un usuario con la sentencia GRANT de SQL. Por lo tanto, sólo usaremos GRANT para aquellos usuarios que ya han sido creados previamente. Puede encontrar más información en la documentación oficial.

En este caso tendremos que crear un usuario con la sentencia CREATE USER y luego asignarle los privilegios que queramos con la sentencia GRANT.

Paso 1. Creación de un usuario

CREATE USER 'nombre_usuario'@'IP_MÁQUINA_CLIENTE' IDENTIFIED BY 'contraseña';

Donde el valor de IP_MÁQUINA_CLIENTE indica que el usuario nombre_usuario sólo podrá conectarse al servidor de MySQL desde esa dirección IP. Los valores que podemos utilizar para IP_MÁQUINA_CLIENTE son los siguientes:

Tenga en cuenta que tendrá que reemplazar los valores nombre_usuario, contraseña y IP_MÁQUINA_CLIENTE por los valores que necesite.

Por lo tanto, para crear el usuario root@% con la contraseña contraseña ejecutaremos la sentencia SQL:

CREATE USER 'root'@'%' IDENTIFIED BY 'contraseña';

Paso 2. Asignación de privilegios

Una vez que hemos creado el usuario le asignamos los privilegios que sean necesarios sobre la base de datos de la aplicación. En este caso le vamos a asignar todos los privilegios con ALL PRIVILEGES.

GRANT ALL PRIVILEGES ON 'base_de_datos'.* TO 'nombre_usuario'@'IP_MÁQUINA_CLIENTE';

Tenga en cuenta que tendrá que reemplazar los valores base_de_datos, nombre_usuario y IP_MÁQUINA_CLIENTE por los valores que necesite.

Los diferentes tipos de permisos que podemos aplicar son los siguientes:

Por lo tanto, para asignarle todos los privilegios al usuario root@% sobre todas las bases de datos ejecutaremos la siguiente sentencia SQL:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

Paso 3. Actualizamos las tablas de privilegios

Para que los cambios que hemos realizado sobre los privilegios de los usuarios se apliquen de forma inmediata, tendremos que ejecutar el siguiente comando.

FLUSH PRIVILEGES;

Ejemplo 1

Por ejemplo, si queremos crear un usuario root con contraseña root, que se puede conectar desde cualquier máquina, que tiene privilegios sobre todas las bases de datos que existen y cuya contraseña es root tendríamos que ejecutar la siguiente sentencia SQL:

CREATE USER 'root'@'%' IDENTIFIED BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

Ejemplo 2

En este ejemplo vamos a indicar el plugin que se utilizará para cifrar la contraseña del usuario. En este caso se ha seleccionado el plugin mysql_native_password.

CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH PRIVILEGES;

3.8 Comprobamos que podemos conectarnos a MySQL

Ahora vamos a comprobar que podemos conectarnos con MySQL desde la máquina donde está corriendo el servicio de Apache HTTP. Podemos comprobarlo conectando con el shell de mysql:

mysql -u USERNAME -p -h IP-SERVIDOR-MYSQL

O haciendo un telnet al puerto donde está corriendo el servicio de MySQL:

telnet IP-SERVIDOR-MYSQL 3306

Si no podemos conectarnos a MySQL revisaremos que el servicio está activo y que no tenemos ningún firewall que nos esté filtrando el puerto del servicio donde se ejecuta MySQL.

3.9 Configuración de MySQL Workbench

En nuestro caso, el cliente que vamos a utilizar para conectarnos a MySQL Server será MySQL Workbench.

MySQL Workbench es una aplicación con interfaz gráfica que nos permite administrar y gestionar bases de datos MySQL de una forma sencilla.

Una vez que tengamos instalado MySQL Workbench en nuestra máquina tendremos que configurar una nueva conexión.

Los parámetros de conexión que tenemos que configurar son los siguientes:

La siguiente imagen muestra los valores de la conexión que tenemos que configurar en la pestaña Parameters.

En la pestaña SSL tendremos que indicar que no queremos utilizar SSL.

Después de configurar estos parámetros podemos pulsar sobre el botón Test Connection para comprobar que la conexión se realiza de forma correcta.

En las instalaciones de MySQL Workbench que tenemos en el aula, podemos encontrarnos con el siguiente error cuando intentamos conectarnos a MySQL Server.

Plugin caching_sha2_password cannot be loaded

Este error ocurre porque la versión de MySQL Workbench que tenemos instalada no tiene soporte para la autenticación caching_sha2_password.

Para solucionarlo, podemos actualizar la contraseña de nuestro usuario y modificar el plugin que se utilizará para cifrarla. En este caso utilizaremos el plugin mysql_native_password que sí es compatible con nuestra versión, aunque debe tener en cuenta que es menos seguro que caching_sha2_password.

Desde el terminal de la máquina remota donde se está ejecutando MySQL Server ejecutamos el siguiente comando:

sudo mysql

Una vez que hemos accedido a la consola de MySQL, modificamos la contraseña utilizando el plugin mysql_native_password.

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root';

Observe que estamos actualizando la contraseña del usuario root@'%' y que como contraseña le hemos asignado el valor root.

3.10 Cómo iniciar, parar y consultar el estado de MySQL

3.10.1 Método 1. systemctl

sudo systemctl start mysql
sudo systemctl stop mysql
sudo systemctl restart mysql
sudo systemctl status mysql

3.10.2 Método 2. /etc/init.d/mysql

sudo /etc/init.d/mysql start
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql restart
sudo /etc/init.d/mysql reload
sudo /etc/init.d/mysql force-reload
sudo /etc/init.d/mysql status

3.11 Archivos de configuración de MySQL

/etc/mysql/mysql.cnf

También podemos encontrar archivos de configuración en los directorios:

/etc/mysql/conf.d/
/etc/mysql/mysql.conf.d/

3.12 Archivos de log de MySQL

/var/log/mysql/error.log

4 Intérprete de comandos de MySQL

4.1 Conexión con un MySQL local

Para conectarnos a un MySQL que se está ejecutando en nuestra máquina local (localhost). Con el parámetro -u indicamos el nombre del usuario con el que queremos conectar y con el parámetro -p nos pedirá el password del usuario.

mysql -u root -p

4.2 Conexión con un MySQL remoto

Para conectarnos a un MySQL remoto que se está ejecutando en otra máquina, utilizamos el parámetro -h con la IP de la máquina donde nos queremos conectar.

mysql -u root -p -h 192.168.20.102

Cuando hayamos conectado nos aparecerá la siguiente consola de comandos SQL.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.19-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

4.3 Algunos comandos útiles para MySQL desde consola

4.3.1 Listar todas las bases de datos disponibles

SHOW DATABASES;

4.3.2 Crear una nueva base de datos

CREATE DATABASE <database>;

4.3.3 Borrar una base de datos

DROP DATABASE <database>;

4.3.4 Seleccionar una base de datos

USE <database>;

4.3.5 Listar las tablas que contiene una base de datos

SHOW TABLES;

4.3.6 Mostrar la estructura de una tabla

DESCRIBE <table>;

4.3.7 Cerrar la sesión y salir

exit
quit

4.4 Ejecutar un script .sql desde la consola

Desde la consola de Linux:

mysql -u root -p < script.sql

Desde la consola de MySQL:

mysql> source script.sql

4.5 Usuarios y permisos en MySQL desde consola

4.5.1 Tipos de permisos que podemos aplicar

4.5.2 Crear un nuevo usuario

Habrá que reemplazar nombre_usuario y contraseña por los datos del nuevo usuario que desea crear:

CREATE USER 'nombre_usuario'@'localhost' IDENTIFIED BY 'contraseña';

Una vez que hemos creado el usuario hay que asignarle permisos para que pueda acceder a la/s base/s de datos que queramos.

4.5.3 Eliminar un usuario

DROP USER 'nombre_usuario'@'localhost';

4.5.4 Asignar permisos a un usuario

GRANT [permiso] ON [nombre_base_de_datos].[nombre_tabla] TO 'nombre_usuario'@'localhost';

Por ejemplo:

GRANT ALL PRIVILEGES ON *.* TO 'nombre_usuario'@'localhost';

En este comando, los asteriscos indican que estamos aplicando el permiso ALL PRIVILEGES al usuario nombre_usuario para todas las tablas de cada una de las bases de datos. bases de datos y tablas.

Después de este comando habrá que ejecutar el siguiente comando para refrescar todos los privilegios a los usuarios.

FLUSH PRIVILEGES;

4.5.5 Eliminar permisos a un usuario

REVOKE [permiso] ON [nombre_base_de_datos].[nombre_tabla] FROM 'nombre_usuario'@'localhost';

4.5.6 Consultar los usuarios creados en MySQL

Los usuarios de MySQL se almacenan en la tabla mysql.user. La clave primaria de esta tabla está formada por los valores user y host, de modo que cada fila vendrá identificada por un nombre de usuario y el host desde el que puede conectarse.

La siguiente consulta nos devuelve el listado de usuarios que tenemos en MySQL y desde qué host pueden conectarse:

SELECT user,host FROM mysql.user;

En nuestra caso la consulta anterior devuelve el siguiente resultado:

+------------------+--------------+
| user             | host         |
+------------------+--------------+
| root             | %            |
| root             | localhost    |
| debian-sys-maint | localhost    |
| phpmyadmin       | localhost    |
| mysql.session    | localhost    |
| mysql.sys        | localhost    |
+------------------+--------------+

También podemos consultar qué permisos específicos tiene un determinado usuario. La siguiente consulta nos devuelve los permisos que tiene el usuario root:

SHOW GRANTS FOR root;
+------------------------------------------------+
| Grants for root@%                              |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'      |
+------------------------------------------------+

5 Licencia

Licencia de Creative Commons
Esta página forma parte del curso Bases de Datos de José Juan Sánchez Hernández y su contenido se distribuye bajo una licencia Creative Commons Reconocimiento-NoComercial-CompartirIgual 4.0 Internacional.