3 MySQL
3.1 Instalación de MySQL server
En primer lugar actualizamos la lista de paquetes del repositorio:
sudo apt updateRealizamos la instalación de mysql-server:
sudo apt install mysql-server -y3.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:
Una vez hecho esto ya tendríamos acceso a la consola de MySQL como
root.
Opción 2
Iniciamos una sesión como root:
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).
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.
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:
mysql_native_password: Este plugin cifra la contraseña utilizando una función hash basada en SHA-1. Actualmente no se considera segura pero se puede seguir utilizando para mantener la compatibilidad con con clientes antiguos que siguen utilizando este método de cifrado.caching_sha2_password: Este plugin cifra la contraseña utilizando una función hash basada en SHA-256. Es más seguro que el anterior y es la opción que se utiliza por defecto a partir de MySQL 8.0
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)
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)
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.
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 mysqlComo 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/mysqldUna 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 mysqldUna 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.
mysqlAhora 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.
Modificamos la contraseña del usuario ‘root’@‘localhost’,
reemplazando nueva_contraseña por la contraseña que
queramos asignar.
Salimos del cliente de MySQL.
exit;Detenemos el proceso mysqld.
sudo pkill mysqldReiniciamos el servicio de MySQL.
sudo systemctl start mysql3.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:
Busca la directiva de configuración bind-address dentro
del bloque de [mysqld]:
[mysqld]
bind-address = 127.0.0.1En 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_MYSQLSi 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.0Una vez hecho esto tenemos que reiniciar el servicio de 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:
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.
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
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:
localhost: No permitimos conexiones remotas, sólo se permiten conexiones dentro de la misma máquina.%: Este comodín indica que permitimos conexiones desde cualquier máquina.- Una dirección IP desde la máquina que ese usuario puede conectarse al servidor de MySQL.
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:
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.
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:
ALL PRIVILEGES: permite a un usuario de MySQL acceder a todas las bases de datos asignadas en el sistema.CREATE: permite crear nuevas tablas o bases de datos.DROP: permite eliminar tablas o bases de datosDELETE: permite eliminar registros de tablas.INSERT: permite insertar registros en tablas.SELECT: permite leer registros en las tablas.UPDATE: permite actualizar registros seleccionados en tablas.GRANT OPTION: permite asignar privilegios a otros usuarios.
Por lo tanto, para asignarle todos los privilegios al usuario
root@% sobre todas las bases de datos
ejecutaremos la siguiente sentencia SQL:
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.
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:
O haciendo un telnet al puerto donde está corriendo el servicio de MySQL:
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:
- Connection name: Nombre que queramos asignarle a la conexión.
- Connection method: Usaremos
Standard (TCP/IP). - Hostname: Será la dirección IP de la máquina remota.
- Port: El puerto de MySQL Server por defecto es el
3306. - Username: Nombre de usuario.
- Password: Contraseña del usuario.
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 loadedEste 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:
Una vez que hemos accedido a la consola de MySQL, modificamos la
contraseña utilizando el plugin mysql_native_password.
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 mysql3.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 status3.11 Archivos de configuración de MySQL
/etc/mysql/mysql.cnfTambié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