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:
- 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.
- Establecida a un nombre de directorio. Solo se permitirán operaciones de importación/exportación desde/hacia el directorio especificado.
- 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:
- Variable vacía.
- Ruta es la misma que el directorio de datos de MySQL o un subdirectorio.
- Directorio accesible a todos los usuarios
- 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 ;-)
Muchísimas gracias por la ayuda. Has conseguido resolver mi problema casi al completo, solo que queda una duda:
ResponderEliminarAl importar el archivo no hay forma de que me aparezca con los caracteres normales, me sale de una forma totalmente ininteligible.
La única forma en la que he conseguido hacerlo bien es importando un texto plano, pero desde una hoja de cálculo no se importa bien.
Esta hoja de cálculo, si la importo desde el phpmyadmin no me da ningún problema, ¿sabes a que puede ser debido?
Por cierto, el comando que uso desde el terminal es:
LOAD DATA INFILE '/home/borja/Escritorio/importacionesSQL/productiii.ods' INTO TABLE productos_corto;
Muchas gracias y un saludo.
Hola!
ResponderEliminarPara importar desde hoja de cálculo lo que hago habitualmente es exportar los datos desde la hoja de cálculo a un archivo CSV y luego importo desde ese archivo, dependiendo de la hoja de cálculo que uses hay diferentes maneras de hacerlo pero es bastante sencillo. La única precaución que debes tener es la página de códigos del archivo, normalmente debería coincidir con la que usas en la base de datos.
Saludos
Jorge
¡Muchísimas gracias! Y mas por la rápida respuesta.
ResponderEliminarSi no es mucha molestia tengo una última consulta, la última de verdad :-)
¿Sabes si es posible importar una tabla mediante la línea de comandos SQL desde 0 tal y como se hace desde el phpmyadmin?
Por si no me explico bien, me refiero a que lo que yo se es crear la tabla con el CREATE TABLE, para después poder añadir la línea LOAD DATA INFILE...
y quizá haya una forma de hacerlo en un solo paso en vez de es dos.
Lo dicho, muchísimas gracias por tu aporte. Un saludo Jorge.
Hola de nuevo!
ResponderEliminarPues en este caso no te puedo dar una respuesta, yo también lo hago del mismo modo que tu. Quizás en un script pero realmente serían dos sentencias diferentes. En este caso me has pillado, siento no poder darte una respuesta concreta ;-)
Saludos
Jorge
¡Hola Jorge!
ResponderEliminarNo pasa nada, creo que he llegado a la conclusión de que no se puede, bueno si se puede, pero no es nada práctico. Me explico:
He visto que cuando se exporta una tabla desde MYSQL, y después de importa en otra base de datos desde phpmyadmin, el comando que usa phpmyadmin es:
CREATE TABLE XXX; para en líneas sucesivas meter el comando:
INTERT INTO XXX (ZZZ,ZZZ,ZZZ) VALUES (YYY, YYY,YYY);
INTERT INTO XXX (ZZZ,ZZZ,ZZZ) VALUES (YYY, YYY,YYY);
y así con todas las líneas que tenga la susodicha tabla.
Vamos, que para importar una tabla, parece que lo mejor es usar phpmyadmin en vez de la consola.
Si alguien que lea esto cree que estoy equivocado, me encantaría que me corrigieran :-)
¡Un saludo Jorge!
Muchas gracias Borja! :-) Un último apunte, el propio manual de mysql(Manual de Mysql - Optimización de sentencias INSERT) nos recomienda cargar datos masivos siempre mediante LOAD DATA INFILE, según indica la documentación aproximadamente es 20 veces más rápido que hacerlo mediante INSERT por lo que para tablas con muchos datos puede merecer la pena hacer primero la creación y luego la carga con LOAD DATA. En todo caso yo también espero que alguien nos pueda ampliar información!
ResponderEliminarSaludos!! :-)
Muchas gracias. Has resuelto mi problema.
ResponderEliminar