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

José Juan Sánchez Hernández

IES Celia Viñas (Almería) - 2019/2020

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

Vamos a crear 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 Configuración de la interfaz de red

Tenga en cuenta que la tarjeta de red de la máquina virtual tiene que estar configurada en modo adaptador puente, que debemos asignarle una dirección IP estática manualmente y que tenemos que configurar la máquina para que use el proxy que tenemos en clase.

2.3 Configuración en Ubuntu 18.04 LTS

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.

Por ejemplo si nuestro archivo de configuración se llama 50-cloud-init.yaml deberíamos editarlo para añadir nuestra configuración de red.

sudo nano /etc/netplan/50-cloud-init.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][15] 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

2.4 Configuración en versiones previas a Ubuntu 18.04 LTS Server

Este paso sólo será necesario realizarlo si hemos instalado una versión anterior a la 18.04.

Para configurar la interfaz de red manualmente lo haremos editando el archivo /etc/network/interfaces.

sudo nano /etc/network/interfaces

Configuramos la interfaz con los siguientes parámetros, teniendo 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.

auto enp0s3
iface enp0s3 inet static
  address 192.168.21.101
  netmask 255.255.255.0
  gateway 192.168.21.99
  dns-nameservers 8.8.8.8 8.8.4.4

Reiniciamos el servicio de red:

sudo /etc/init.d/networking restart

También podemos desactivar la interfaz enp0s3 y volver a activarla para que se apliquen los cambios de la nueva configuración a esa interfaz de red.

sudo ifdown enp0s3
sudo ifup enp0s3

2.5 Configuración del proxy

Este paso sólo será necesario realizarlo si estamos haciendo uso del proxy del aula.

Para poder tener conexión a Internet en nuestra máquina virtual tenemos que configurarla para que use el proxy que tenemos en el aula. Para esto editamos el archivo /etc/environment:

sudo nano /etc/environment

Y añadimos las siguientes variables de entorno al final del archivo:

http_proxy=http://192.168.21.99:3128
https_proxy=https://192.168.21.99:3128

3 MySQL

3.1 Instalación de MySQL server

En primer lugar actualizamos la lista de paquetes del repositorio:

sudo apt-get update

Realizamos la instalación de mysql-server:

sudo apt-get install mysql-server

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. En primer lugar vamos a iniciar 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

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 | mysql_native_password |
| mysql.sys        | localhost | mysql_native_password |
| debian-sys-maint | localhost | mysql_native_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.

Si queremos cambiar la contraseña de root tendremos que cambiar el método de autenticación a mysql_native_password.

UPDATE user SET plugin='mysql_native_password' WHERE User='root';

Una vez que hemos actualizado el valor de la columna plugin a mysql_native_password actualizamos la columna authentication_string para asignar cuál será la nueva contraseña. Tendrá que sustituir nueva_contraseña por la contraseña que desee.

UPDATE user SET authentication_string=password('nueva_contraseña') where user='root';

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

FLUSH PRIVILEGES;

3.4 Configuración de MySQL

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.

[mysqld]
bind-address = 0.0.0.0

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

sudo /etc/init.d/mysql restart

3.5 Lista de usuarios de 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é 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).

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

3.6 Asignando privilegios a un usuario para conectarnos desde una máquina remota

Ahora tenemos que asignar privilegios al usuario de MySQL que vamos a utilizar para conectarnos desde una máquina remota.

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

mysql -u root -p  

Una vez que hemos conectado con MySQL Server vamos a crear un usuario que pueda conectarse desde cualquier máquina. Recuerde que los usuarios en MySQL están identificados por un nombre de usuario y un host, en este caso vamos a utilizar el comodín % como el host, para indicar que este usuario se puede conectar desde cualquier dirección IP.

La sentencia SQL para realizar esta acción sería la siguiente:

mysql> GRANT ALL PRIVILEGES ON DATABASE.* to USERNAME@'%' IDENTIFIED BY 'PASSWORD';
mysql> FLUSH PRIVILEGES;

Tenga en cuenta que tendrá que reemplazar los valores DATABASE, USERNAME y IP-SERVIDOR-HTTP por los valores que necesite.

Por ejemplo, si queremos crear un usuario 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:

mysql> GRANT ALL PRIVILEGES ON *.* to root@'%' IDENTIFIED BY 'root';
mysql> FLUSH PRIVILEGES;

Una vez hecho esto comprobamos cuál es la lista de usuarios que existen actualmente 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    |
| 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'@'%'      |
+------------------------------------------------+

3.7 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.8 Cómo iniciar, parar y consultar el estado de MySQL

3.8.1 Método 1. systemctl

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

3.8.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.9 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.10 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
El contenido de esta web está bajo una licencia de Creative Commons Reconocimiento-NoComercial-CompartirIgual 4.0 Internacional.