Profeeeee! he olvidado la contraseña de MySQL

Cuando uno lleva impartiendo clases de bases de datos bastante tiempo hay algunas situaciones que son recurrentes, no importa el software utilizado, la versión, las máquinas o la procedencia del alumnado, siempre hay algún alumno que en el día menos pensando justo en el momento de acceder a MySQL comenta... "Profeee, no me acuerdo de la contraseña de MySQL". Esta situación, tan habitual a la vuelta de vacaciones la suelo resolver en dos pasos:

• La primera vez ayudo al alumno indicando los pasos para recuperar la contraseña
• La segunda vez le recuerdo que ya le expliqué cómo lo hacía y que como buen informático es el momento de que recupere el acceso a su servidor. Esto que podría ser interpretado como de "mal profesor" suele dar mejores resultados ya que no conozco ningún alumno que una vez que se haya enfrentado al problema y lo haya resuelto por si mismo me haya vuelto a decir eso de "Profeee!, he olvidado la contraseña de MySQL".

Sin embargo la última vez que me ha ocurrido esta situación me di cuenta de que había un pequeño cambio en la sintaxis de la sentencia de establecimiento de contraseña a partir de la versión 5.7.6 de MySQL por lo que a partir de este momento es importante ser consciente de qué versión de MySQL estamos utilizando. Para ello podemos utilizar el siguiente comando desde el prompt de linux:

$ mysql -V

En mi caso ya estoy trabajando con una versión posterior a la 5.7.6. En todo caso la mayoría de pasos para recuperar la contraseña en todas las versiones son similares y solo cambia la sentencia final.

1. Paramos el servicio

$ sudo service mysql stop

2. Lo arrancamos de nuevo sin cargar las tablas de permisos:

$ sudo mysqld_safe --skip-grant-tables &

En el caso de que nuestro servidor permitiese conexiones remotas, opción que viene deshabilitada por defecto con la opción de bind_address en el fichero de configuración de MySQL podría ser interesante la incorporación de la opción --skip-networking en la instrucción anterior.

3. Accedemos al servicio como usuario root, pero ahora no es necesaria la contraseña

$ mysql -u root

4. Y cambiamos la contraseña con una sentencia SQL... Pero claro, en este paso es donde hay que estar atento porque dependiendo de nuestra versión de MySQL el comando será diferente dentro del prompt MySQL.

Para versiones de MySQL anteriores a la 5.7.6 normalmente realizaba los dos pasos siguientes:

Acceder al esquema mysql.

mysql > use mysql;

Y cambiar el valor de la contraseña directamente con una sentencia SQL de actualización:

mysql > UPDATE user SET password=PASSWORD("NuevaContraseña") WHERE user='root';

Otra posibilidad que es la que recomienda el propio manual de MySQL y que no solía usar es:

mysql > SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NuevaContraseña');

Que además parece que hasta el momento funciona en todas las versiones.

Sin embargo, la documentación oficial de MySQL indica que para versiones 5.7.6. y posteriores se debe usar:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'nuevaContraseña';

Esto es debido a que a partir de esta versión de MySQL los comandos de gestión de usuarios han cambiado. Es más, la sentencia UPDATE que solía utilizar ahora genera este error que inicialmente te deja bastante despistado.

mysql> ERROR 1054 (42S22): Unknown column 'password' in 'field list'

En todo caso con las dos otras opciones que indico todo debería funcionar y nuestro alumno despistado podría volver a acceder a su servidor MySQL simplemente  arrancando de nuevo el servicio después de los cambios anteriores.

$ sudo service mysql restart

Backup físico en MySQL de bases de datos con motor de almacenamiento InnoDB

En ocasiones queremos migrar una base de datos MySQL de un servidor a otro de la manera más rápida posible. De las múltiples opciones de copia de seguridad existentes la copia física estará siempre entre las más rápidas. Sin embargo dependiendo del motor de almacenamiento tendremos que aplicar una técnica u otra, en este caso vamos a ver cómo realizar una copia física de base de datos sobre el motor de almacenamiento InnoDB que es uno de los más comunes entre los que cumplen las características ACID de MySQL. 

Comenzando...

Para comenzar hemos creado una instalación de MySQL 5.7.17. en una máquina virtual de Ubuntu 16.04 Desktop de nombre "Antares", en esa instalación hemos creado una pequeña base de datos denominada portable que nos servirá para comprobar que la migración se ha realizado correctamente.

Imagen 1. Estado de la base de datos original (máquina Antares)

Apagado lento del servidor

En primer lugar, se hace necesario pasar el servidor para realizar una copia de archivos en un estado seguro, para asegurarnos que la parada el servidor se hace de la mejor manera posible debemos consultar el valor de la variable de servidor innodb_fast_shutdown que especifica la manera en la que se va a realizar la parada del sistema:

• Si el valor es 0 InnoDB realiza un apagado lento limpiando y fusionando el buffer de cambios antes de apagar.
• Si el valor es 1(por defecto) el servidor se apaga pero no realiza esas operaciones (proceso conocido como apagado rápido)
• Si el valor es 2 InnoDB aplica sus logs y se apaga en frío de manera similar a una caída de la aplicación. No se perderán las transacciones aplicadas pero las operaciones de recuperación al iniciar su servidor pueden tomarse su tiempo.

En nuestro caso cambiamos el valor a 0.

Imagen 2. Cambiando el valor de innodb_fast_shutdown

A continuación nos aseguramos que conocemos la ruta del directorio de datos de MySQL (habitualmente /var/lib/mysql), en todo caso lo podemos consultar en la variable de servidor datadir.

Una vez que estamos seguros de la ubicación de los datos detenemos el servidor.

Haciendo el backup de los archivos

Ahora simplemente tenemos que hacer una copia de los archivos originales, para ello basta mirar el manual de MySQL que indica para estos casos cuáles son los archivos importantes:

• Archivos de datos InnoDB (archivos ibdata y .ibd).
• Archivos .frm correspondientes a las tablas InnoDB.
• Archivos de registro Innodb (archivos ib_logfine).
• Archivo de configuración my.cnf. Muy útil en el caso de que el servidor destino tenga una configuración distinta.

Imagen 3. Generando un archivo comprimido con los datos necesarios para el backup

Restaurando en el servidor de destino

A continuación y con el archivo de backup listo nos movemos a nuestro servidor de destino (hostname Andromeda) que tiene una configuración similar al original. En este caso trabajaremos a partir de una instalación limpia en otra máquina:

Imagen 4. Estado de la base de datos MySQL en el equipo destino antes de restaurar

En primer lugar y para evitar problemas realizamos un apagado lento del servidor, tal y como hicimos en el servidor original. Esto realmente puede no ser necesario pero uno ha visto ya muchas cosas raras cuando en informática no se toman todas las precauciones...

Imagen 5. Apagado lento del servidor

Accedemos al directorio de datos donde restauraremos el archivo de backup:

Imagen 6. Directorio de datos equipo de destino antes de restaurar

Descomprimimos el archivo:

Imagen 7. Descomprimimos el archivo de backup

Después borramos nuestra copia de seguridad del directorio de datos de MySQL, cerramos sesión con el usuario root y lanzamos de nuevo el servicio MySQL, si hemos hecho todo correcto veremos que en la máquina en la que hemos realizado la restauración aparece la base de datos original y podemos realizar operaciones sobre ella sin problemas. No hay que olvidar que puede que queramos volver a establecer el apagado lento a valor 1, para ello basta modificar el valor de la variable como se hizo para establecerlo a 0.

Imagen 8. Base de datos restaurada en otro equipo y funcionando

Configurando AppArmor y MySQL en Ubuntu 16 para exportar e importar datos desde archivos externos

Una de las opciones para importar y exportar datos de manera rápida desde MySQL son los comandos SELECT ... INTO OUTFILE Y LOAD DATA INFILE, sin embargo, configurar el sistema de manera adecuada para utilizarlos se ha convertido en algo no demasiado sencillo a partir de la versión 5.7.6 de MySQL. Como he estado pegándome bastante con el asunto ya que hasta esta versión no había tenido demasiados problemas os dejo aquí los pasos por si estáis hasta los .... de los errores de MySQL:

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Y su amigo....

ERROR 1 (HY000): Can't create/write to file '/ruta/archivo.txt' (Errcode: 13)

Vamos a ver cómo se configura...

Configurando --secure-file-priv para evitar el mensaje "ERROR 1290 (HY000) The MySQL server is running with the --secure-file-priv option so it cannot execute this statement"

--secure-file-priv es una variable de sistema de MySQL no dinámica usada para limitar el efecto de las operaciones de importación y exportación de datos, puede configurarse de tres maneras:

1. Vacía. La variable no tiene efecto y no se considera una configuración segura. Si la estableces así tus volcados estarán en el directorio de datos de MySQL (normalmente /var/lib/mysql) o aquel apuntado en la variable de servidor datadir.
2. Establecida a un nombre de directorio. Solo se permitirán operaciones de importación/exportación desde/hacia el directorio especificado.
3. NULL. Las operaciones de importación y exportación quedan deshabilitadas.

Antes de la versión 5.7.6 de MySQL estaba vacía por defecto pero a partir de esa versión depende de la plataforma. Su valor es comprobado al arranque y se escribe un aviso en el log de errores del sistema si el valor no es seguro por una de las siguientes causas:

1. Variable vacía.
2. Ruta es la misma que el directorio de datos de MySQL o un subdirectorio.
3. Directorio accesible a todos los usuarios
4. Apunta a una ruta inexistente (el servidor no la creará)

¿Y porqué os cuento todo esto? pues sencillo, porque lo más normal si estáis con una versión igual o posterior a la 5.7.6 os vais a encontrar este mensaje más de una vez:

Y cómo se soluciona, pues sencillo:

1º Establecemos un directorio para los volcados

Editamos el fichero de configuración de MySQL (/etc/mysql/mysql.conf.d/mysqld.cnf) y añadimos un valor de inicio para la variable, en mi caso he añadido un directorio que he creado para ello.

2º Cambiamos el propietario de la carpeta y damos los permisos adecuados

Usando los comandos chmod y chown establecemos como propietario de la carpeta el usuario mysql del grupo mysql con permisos 750. Los comandos son:

Para el propietario de la carpeta:

Para los permisos de acceso:

Si todo es correcto el directorio debería aparecer así definido:

Ahora simplemente tenemos que reiniciar el servidor MySQL para que se apliquen los cambios del fichero de configuración y esperar que todo funcione ( o no....)

3º Y sigue sin funcionar, pero ahora el error es otro....

Pues sí, después de reiniciar el servidor e intentar la exportación de nuevo el tema parece que no acaba de funcionar...

Pero en este caso hemos cambiado el error, por lo que siendo optimistas algo hemos hecho bien.

Configurando AppArmor para evitar el mensaje "ERROR 1 (HY000): Can't create/write to file '/ruta/archivo.txt' (Errcode: 13)"

AppArmor es un sistema de control de acceso que viene instalado en Ubuntu por defecto y que controla el acceso a los recursos del sistema por parte de los programas que tenemos instalados, tiene muchas posibles configuraciones y perfiles diferentes para asignar a las aplicaciones y precisamente asigna uno a MySQL y por lo tanto, si necesitas cambiar dónde va a leer y escribir MySQL en disco te va a tocar configurarlo en AppArmor o utilizar otras opciones menos ortodoxas como deshabilitarlo, pero nosotros somos elegantes y vamos a configurarlo.

1º Editamos el fichero de configuración de AppArmor. 

El fichero de configuración se encuentra en la ruta "/etc/apparmor.d/usr.sbin.mysqld", simplemente tenemos que editarlo y dar permisos a MySQL para escribir y leer en el directorio configurado en la variable --secure-file-priv.

Como se puede ver en la imagen no me he complicado mucho, simplemente he copiado la estructura de permisos del directorio de datos de MySQL y lo he aplicado a la ruta especificada en la variable. 

2º Ahora simplemente recargamos los perfiles en AppArmor.

Y volvemos a entrar a MySQL donde veremos que ya podemos escribir en la carpeta indicada.

Y eso es todo ;-)