Saltar al contenido

Bases de Datos

pg_dump – pg_restore (PostgreSQL)

  • admin 

Cuando se trabaja con base de datos, uno de las cosas más importantes de hacer es respaldar los datos en caso de que ocurra cualquier situación que dañe nuestro servidor de base de datos. De nada nos serviría hacer esos respaldos si no los podemos volver a cargar (restaurar) cuando lo necesitemos.
En este post,  trataremos la forma de hacer respaldos y restauración de datos cuando se utiliza postgreSQL como DBMS.

Respaldando los datos: pg_dump

pg_dump es una herramienta de línea de comandos que nos permita hacer un respaldo de alguna de las bases de datos  (o todas) en nuestro servidor postgres. Permite hacer el volcado de datos en diferentes formatos ya sean compresos, texto plano, etc. En resumen, escribe en un archivo (o salida estándar) las instrucciones SQL necesarias para hacer un respaldo de la base de datos.

El formato del comando pg_dump es:
   pg_dump [opciones] [nombre_base]
Entre las opciones que se pueden utilizar están:
dbname: Nombre de la base de datos de la que se desea hacer respaldo.
-a o –data-only: Hace un volcado solo de los datos y no del esquema.
-c o –clean: Crea instrucciones para eliminar los elementos antes de crearlos. Es útil para evitar los errores del tipo ‘la relacion nombre_relación ya existe’ a la hora de restaurar el respaldo.

-C o –create:  Escribe las instrucciones para la creación de la base de datos dentro del script del respaldo.

-f <archivo> o –file=<archivo>:  Escriba la salida (el volcado) en el archivo especificado. En caso de que no se utilice esta opción, el volcado se hace a la salida estándar.

-F <formato_de_archivo> o –format=<formato_de_archivo>: Permite especificar el formato de la salida del dump. El formato de salida puede ser:

p o plain: Texto plano.

c o custom:  Formato de salida customizable. Este tipo de salida siempre se realiza compreso por defecto.

t o tar: Crea la salida en formato tar.

-n <nombre_esquema> o –schema=<nombre_esquema>: Realiza el dumpúnicamente del esquema (y todos los objetos que contengan) que concuerde con el<nombre_esquema>. Si no se especifica, se hará un dump de todos los esquemas que no sean del sistema y que estén en la base destino. Si se quiere incluir en el dump más de un esquema se pueden poner multiples -n <nombre_esquema> como sean necesarios.

-N <nombre_esquema> o –exclude-schema=<nombre_esquema>: Omite los esquemas que concuerden con <nombre_esquema> del dump a realizarse. Se pueden incluir tantos -N como sean necesarios.

-s o –schema-only: Hace un volcado únicamente del esquema, no de los datos.

-t <nombre_tabla> o –table=<nombre_tabla>: Hace un volcado solo de las tablas que se especifiquen. Se pueden utilizar -t <nombre_tabla> tantas veces como se necesite. Se debe tener en cuenta que pg_dump no hace un seguimiento de las tablas de las que pueda depender la tabla que se desee volcar con el dump, así que hay que tener cuidado de incluirlas todas las que sean necesarias (que tengan relación con llaves primarias o foráneas) para garantizar que se puede hacer la restauración de los datos exitosamente.

-T <nombre_tabla> o — exclude-table: <nombre_tabla>: Excluye del dumplas tablas listadas. Esta opción puede ser utilizada más de una vez.

— inserts: Utiliza inserts en lugar de copy en las instrucciones de SQL.

— port = <puerto>: Especifica el puerto TCP o local en el cual el servidor está escuchando.

-U <nombre_de_usuario>: Especifica el nombre de usuario con el que se hará la conexión a la base de datos que se desea respaldar.

Ejemplos:
Si tenemos una base de datos llamada base_de_prueba y queremos respaldarla completamente en un archivo llamado base_de_prueba.sql, podemos utilizar el siguiente comando:
   pg_dump base_de_prueba > base_de_prueba.sql

Para hacer el volcado en un archivo de extensión personalizada (opción -F y c para custom):

   pg_dump -Fc base_de_prueba > base_de_prueba.dump

Para volcar solo una tabla de la base:

   pg_dump -t nombre_tabla base_de_prueba > mi_tabla_dump.sql

Restaurando los datos: pg_restore

Para realizar la restauración de los datos volcados con pg_dump, podemos utilizar la herramienta pg_restore. pg_restore restaura una base de datos que ha sido respaldada con pg_dump.

Entre las ventajas de utilizar esta utilidad para la restauración es que se puede seleccionar qué partes del respaldo se quieren restaurar o incluso reordenar los items antes de hacer la restauración

La sintáxis del comando es la siguiente:

   pg_restore [opciones] [fichero_a_restaurar]

Entre las opciones más comunes utilizadas con este comando están:

-a o –data-only:  Restaura solo los datos, no el esquema.

-c o –clean: Elimina los objetos antes de volverlos a crear.

-C o –create: Crea la base de datos especificada con la opción -d, sin embargo, los datos son restaurados a la base de datos que aparece en el script.
-d <nombre_base> o –dbname=<nombre_base>: Conecta a la base de datos<nombre_base> y restaura los datos directamente en ella.

-j <numero> o –jobs=<numero>: Realiza la restauración de los datos utilizando<numero> hilos o procesos (dependiendo del sistema operativo). Cada jobs es un proceso o hilo que utiliza una conexión separada. La utlización de esta opción, permite poder restaurar un dump en una forma más rápida, sin embargo, utiliza más recursos en el servidor. La velocidad de la restauración, por tanto, depende en gran medida de la configuración del hardware del servidor.

-n <nombre_esquema> o –schema=<nombre_esquema>: Realiza la restauración únicamente del esquema llamado <nombre_esquema>.

-s o –schema-only: Restura solo el esquema, no los datos.

-t <nombre_tabla> o –table=<nombre_tabla>:  Restaura únicamente la tabla con el nombre <nombre_tabla>. <nombre_tabla> puede ser una expresión regular.

-U <nombre_usuario> o –username=<nombre_usuario>:  Nombre de usuario con el que se desea hacer la conexión.
Uno de las cosas a tomar en cuenta a la hora de restaurar un respaldo es que si éste ha sido hecho en un formato particular (custom), no podrá ser visible con herramientas como more,cat o algún editor de texto.

Debido a que no se puede ver que usuarios se utilizan o a que base de debe conectar, se pueden correr problemas a la hora de quererlos restaurar en otra máquina que no tenga los usuarios o bases que correspondan con los que están en el archivo creado con pg_dump.
Una forma de solucionar esto es hacer una restauración sin especificar la base de datos a la que se desea restaurar los datos. Esto hace que pg_restore mande la salida a la salida estandar. Esta salida estandar puede ser enviada a un archivo que contendrá las instrucciones de respaldo en texto plano.
Por ejemplo, asumiendo que tenemos un archivo creado con pg_dump en formato .dump, llamado respaldo-base.dump y queremos pasarlo a texto plano en un fichero, podemos hacer lo siguiente:
$ pg_restore respaldo-base.dump > respaldo-base.sql
El operador > redirecciona la salida de la salida estándar (pantalla) al archivo respaldo-base.sql. Después de haberse ejecutado el comando, el fichero puede ser leido con cualquier editor de texto.

Otros ejemplos:

$ pg_restore -C -d postgres db.dump
Crea la base de datos postgres (por la opción -C) pero siempre vuelca los datos en la base que el script especifica.
 $ pg_restore -d mi_base db.dump
Restaura los datos en la base llamada mi_base. Esta base debe haber sido creada previamente.

 

Como habrán notado, las herramientas pg_dump y pg_restore comparten muchas opciones comunes entre sí y representan un par de herramientas bastante útiles a la hora de restaurar y manipular los respaldos hechos de nuestras bases de datos.

Escalabilidad

  • admin 

En telecomunicaciones y en ingeniería informática, la escalabilidad es la propiedad deseable de un sistema, una red o un proceso, que indica su habilidad para reaccionar y adaptarse sin perder calidad, o bien manejar el crecimiento continuo de trabajo de manera fluida, o bien para estar preparado para hacerse más grande sin perder calidad en los servicios ofrecidos.

En general, también se podría definir como la capacidad del sistema informático de cambiar su tamaño o configuración para adaptarse a las circunstancias cambiantes. Por ejemplo, una Universidad que establece una red de usuarios por Internet para un edificio de docentes y no solamente quiere que su sistema informático tenga capacidad para acoger a los actuales clientes que son todos profesores, sino también a los clientes que pueda tener en el futuro dado que hay profesores visitantes que requieren de la red por algunas aplicaciones académicas, para esto es necesario implementar soluciones que permitan el crecimiento de la red sin que la posibilidad de su uso y reutilización disminuya o que pueda cambiar su configuración si es necesario.

La escalabilidad como propiedad de los sistemas es generalmente difícil de definir en cualquier caso, en particular es necesario definir los requerimientos específicos para la escalabilidad en esas dimensiones donde se crea que son importantes. Es una edición altamente significativa en sistemas electrónicos, bases de datos, ruteadores y redes. A un sistema cuyo rendimiento es mejorado después de haberle añadido más capacidad hardware, proporcionalmente a la capacidad añadida, se dice que pasa a ser un sistema escalable.

 

La escalabilidad debe formar parte del proceso de diseño porque no es una característica separada que se pueda agregar después. Al igual que con otras funciones de aplicación, las decisiones que se tomen durante las primeras fases de diseño y codificación determinarán en gran medida la escalabilidad de la aplicación.

La escalabilidad de una aplicación requiere una pertenencia equilibrada entre dos dominios distintos, software y hardware. Puede avanzar grandes pasos que aumenten la escalabilidad de un dominio sólo para sabotearlos cometiendo errores en el otro. Por ejemplo, la creación de un grupo de servidores Web con equilibrio de carga no beneficiará una aplicación Web que se ha diseñado para ejecutarse un solo equipo. De igual modo, el diseño de una aplicación altamente escalable y su implementación en equipos conectados a una red con poco ancho de bada no controlará bien las cargas pesadas cuando se sature el tráfico en la red.

Puesto que la escalabilidad no es un problema de diseño de las aplicaciones independientes, aquí se tratan las aplicaciones distribuidas. Las aplicaciones distribuidas están también un paso más allá de las tradicionales aplicaciones de cliente-servidor. Las aplicaciones distribuidas son aplicaciones que están diseñadas como aplicaciones de n niveles. La arquitectura de estas aplicaciones distribuidas favorece el diseño de aplicaciones escalables compartiendo recursos, como bases de datos y componentes empresariales.

 

Escalar en vertical
El escalado en vertical es el término que más se utiliza para lograr escalabilidad utilizando software mejor, más rápido y más caro. El escalado incluye agregar más memoria, más procesadores o procesadores más rápidos o, simplemente, migrar la aplicación a un único equipo más potente. Normalmente, este método permite un aumento en la capacidad sin requerir cambios en el código fuente. Desde el punto de vista administrativo, las cosas permanecen igual puesto que sigue habiendo un único equipo que administrar.

escalar en vertical

Actualizar un componente de hardware en un equipo sólo mueve el limite de capacidad de procesamiento de un lado a otro. Por ejemplo, una máquina que está al 100 % de uso de la CPU podría mejorar su capacidad agregando otra CPU. Sin embargo, la limitación puede pasar de la CPU a la memoria del sistema. Agregar CPU no aporta rendimiento en un modelo lineal. En su lugar, el rendimiento va disminuyendo cada vez que se agrega un procesador adicional. Para equipos con configuraciones de varios procesadores simétricos (SMP), cada procesador adicional produce una sobrecarga del sistema. Por tanto, un equipo con cuatro procesadores no obtendrá una mejora del 400% en capacidad sobre una versión con un único procesador. Una vez que haya actualizado todos los componentes de hardware al máximo de su capacidad, llegará el momento en que alcance el límite real de la capacidad de procesamiento del equipo. Llegado ese punto, el siguiente paso es escalar en vertical para moverse a otro equipo.

Escalar en vertical conlleva también otros posibles problemas. El uso de un único equipo en el que basar una aplicación crea un único punto de error, lo que disminuye enormemente la tolerancia de errores del sistema. Si bien algunos métodos, como varias fuentes de alimentación, pueden implementar redundancia en un sistema de un único equipo, pueden resultar costosas.

 

Escalar en horizontal

Una alternativa a escalar en vertical es escalar en horizontal. Escalar en horizontal aprovecha el ahorro que supone utilizar el hardware de PC activo para distribuir la carga de procesamiento en más de un servidor. Aunque el escalado en horizontal se logra utilizando muchos equipos, la colección funciona esencialmente como un único equipo. Al dedicar varios equipos a una tarea común, mejora la tolerancia de errores de la aplicación. Por supuesto, desde el punto de vista del administrador, escalar en horizontal presenta un desafío mayor de administración debido al mayor número de equipos.

 

escalar en horizontal

Los desarrolladores y administradores utilizan una gran variedad de técnicas de equilibrio de carga para escalar en horizontal con la plataforma Windows. El equilibrio de carga permite escalar un sitio en horizontal a través de un clúster de servidores, facilitando la adición de capacidad agregando más servidores duplicados. También proporciona redundancia, concediendo al sitio capacidad de recuperación de conmutación por error, de manera que permanece disponible para los usuarios incluso si uno o más servidores fallan (o si es preciso retirarlos del servicio para realizar labores de mantenimiento). El escalado en horizontal proporciona un método de escalabilidad que no se ve mermado por limitaciones de hardware. Cada servidor adicional proporciona una mejora casi lineal de la escalabilidad.

La clave para escalar horizontalmente una aplicación con éxito es la transparencia de ubicación. Si alguna parte del código de la aplicación depende de saber qué servidor está ejecutando el código, no se ha logrado la transparencia de ubicación y será difícil el escalado en horizontal. Esta situación se denomina afinidad de ubicación. La afinidad de ubicación requiere cambios de código para escalar una aplicación en horizontal de un servidor a varios, lo que, en pocas ocasiones, constituye una opción económica. Si diseña la aplicación con transparencia de ubicación en mente, resulta más fácil escalarla en horizontal.

Big Data

  • admin 

Quizás el aspecto más novedoso del nuevo paradigma se encuentra en el volumen de la materia prima de todo análisis: los datos. Aquí sí estamos presenciando en cierto modo una revolución, por el crecimiento de la magnitud de información que el ser humano es capaz de generar y almacenar. En 2010 se han generado unos 800exabytes de nueva información. Para poner dicha cifra en perspectiva, desde el inicio de los tiempos hasta 2005 el ser humano había almacenado unos 5 exabytes, cantidad que hoy se genera cada dos días. Y, en este aspecto, desde el más acérrimo fan de Hayek hasta el más recalcitrante comunista están de acuerdo en el valor de la información -los primeros argumentan que el sistema de precios es la institución espontánea más importante que ha creado el hombre, pues permite agregar información dispersa sin necesidad de una intención explícita; los segundos pretenden optimizar una función de utilidad individual para dar “a cada cual según su necesidad”-. ¿Dónde está entonces el problema con el Big Data?

Primer problema: nuestra capacidad de modelización es muy reducida

Una característica distintiva del Big Data es que se refiere generalmente a la posibilidad de modelizar y predecir ciertos comportamientos del ser humano. La nueva información que cada día se genera no se trata de nuevos datos de observaciones astronómicas o reacciones químicas, sino del rastro que deja en la red la acción humana, el cual puede ser fácilmente recolectado para su posterior análisis. Pero las novelas de Asimov y el cine de Hollywood se han encargado de transmitir una idea excesivamente optimista de nuestra capacidad real de modelización de fenómenos sociales o conductuales complejos. Una cosa es un experimento de laboratorio con 50 estudiantes de una misma universidad, del cual se puede extraer un patrón medio de comportamiento generando grupos de forma aleatoria, y otra muy distinta es que los investigadores puedan, a partir de información de la vida real, generar un modelo estructural de comportamiento que permita predecir la reacción de ciudadanos o clientes a determinadas medidas.

Allí donde una empresa o gobierno pueda generar experimentos, asignando clientes o ciudadanos aleatoriamente a distintos grupos, podrá tener una cierta certeza del efecto de una medida concreta. Por ejemplo, una cadena de supermercados puede experimentar con la respuesta del consumidor a cambios en los precios de algún producto seleccionando aleatoriamente la mitad de los supermercados de una ciudad y aplicando en ellos una rebaja. La selección aleatoria garantiza en este caso que el cambio de comportamiento se debe exclusivamente al efecto de la medida.

Pero este tipo de experimentos ya son fáciles de realizar desde hace décadas. Lo distintivo del Big Data es la recolección de datos de comportamiento real, y el problema aquí se encuentra en que las personas actúan por motivos muy complejos y en que ninguna “asignación a un grupo u otro” es aleatoria. ¿Está extraída de la misma muestra la gente que compra el pan en El Corte Inglés y quien lo compra en un supermercado Dia? Aquí es donde nuestra capacidad de modelización se topa con un muro de indescriptible complejidad, en el que un tratamiento ingenuo de la realidad puede llevar a conclusiones tremendamente erróneas.

Piensen en el clásico ejemplo de la mayor longevidad de los bebedores de vino sobre los de cerveza. La interpretación ingenua lleva a concluir que el vino aumenta la longevidad (o que la cerveza la acorta), pero bastan unos segundos para darse cuenta de que el comprador medio de vino es muy distinto del comprador de cerveza, y que su renta y hábitos de salud también difieren mucho. Si un ejemplo tan sencillo puede llevar a conclusiones erróneas, ¿qué no sucederá con fenómenos complejos en los que actúan simultáneamente varios factores? Imagine una enorme base de datos de impagos crediticios con información sobre renta, historial crediticio, localización, patrimonio, formación, sexo, etc. Arrojar todas las variables a un modelo múltiple para intentar predecir la probabilidad de un futuro impago devolverá medias condicionales, pero no habrá abordado la cuestión clave de todo análisis basado en datos no experimentales: la “endogeneidad”, esto es, el hecho de que la causalidad corre en varios sentidos y que las variables explicativas dependen unas de otras. Solo un modelo estructural explícito puede resolver este problema, pero ¿cuánta gente hay en el mundo capaz de plantearlo y contrastarlo? No pierdan tiempo buscando: realmente muy poca, y ellos mismos les dirán que aún tienen un amplio camino por recorrer.

En la medida en que los datos estén obtenidos de comportamientos reales, la complejidad del proceso será un muro difícil de franquear. Así, la capacidad de modelización a partir de bases de datos masivas, por prometedora que sea, es por ahora solo una quimera para el 99% del tejido productivo. Salvo algunas grandes compañías -solo Google o Facebook saben qué uso pueden estar haciendo con toda la información que recolectan-, el resto ha de conformarse con aprender gradualmente, aunque sí podrán beneficiarse de los incrementos de productividad que, por ejemplo, Google les pueda aportar.

Segundo problema: el Big Data solo es complementario con un tipo de capital humano muy específico

La intuición de este problema es sencilla: así como la revolución del motor de explosión fue posible por su complementariedad con cualquier tipo de trabajo –hasta el trabajador menos formado es mucho más productivo cavando una zanja con un martillo neumático-, los datos solo se complementan bien con una combinación de habilidades terriblemente escasa en la sociedad. Un “trabajador de los datos” necesita combinar tres tipos de conocimientos:

 1. Estadística pura y dura. Modelos lineales, generalizados, máxima verosimilitud, contraste de hipótesis, inferencia, machine learning… Casi todo el mundo puede aprender las matemáticas necesarias, pero lo cierto es que, a día de hoy, menos del 1% de la población tiene el conocimiento suficiente para comprender un modelo estadístico y sus implicaciones.

2.  Habilidades computacionales. Trabajar con datos masivos requiere en primer lugar la capacidad para manejarlos, e implementar modelos y algoritmos requiere soltura al menos en un lenguaje. Los paquetes estadísticos clásicos parecen estar perdiendo fuelle ante el empuje de herramientas open-source como R o Python, en las cuales la propia comunidad implementa en cuestión de días cualquier nueva funcionalidad. La buena noticia es que nunca fue tan fácil aprender a programar, gracias a la explosión de los cursos online (aquí una maravillosa introducción a Python, aquí una introducción a bases de datos y aquí otra para R).

3. Profundo conocimiento del tema a tratar. Este problema es, en esencia, el mismo al que con anterioridad nos referimos con el término “endogeneidad”, y es precisamente el mismo motivo por el que las suites de Business Intelligence han dado mucho menos de lo que prometían. Ningún modelo ni algoritmo puede garantizar predicciones fiables sin un conocimiento profundo del tema, es decir, sin un marco teórico sobre el que plantear el mismo. No existe una caja negra a la que podamos arrojar una tonelada de datos de la cual saldrá un patrón de comportamiento fiable. Además, las habilidades de modelización suelen adquirirse en el mundo académico, y allí donde el trasvase entre la academia y la empresa es fluido –siendo el ejemplo paradigmático el conglomerado Silicon Valley + Stanford + Caltech– los resultados son maravillosos. Pero allí donde la Universidad vive en un mundo paralelo y alejado de la excelencia, los resultados son nulos.

La conjunción de estas habilidades es hoy, y más en España, muy escasa (en nuestro caso habría que añadir las dificultades con el inglés). ¿Quién se beneficiará entonces de las nuevas posibilidades que traen los datos? ¿En qué medida esta nueva oportunidad puede dar a luz a un nuevo paradigma económico? Estas reflexiones nos llevan a las dos conclusiones que cierran este artículo.

Conclusión 1

Como anunciaba Hal Varian, el Big Data será muy provechoso para los trabajadores que reúnan las capacidades necesarias…, pero no necesariamente para el resto. Cuando una nueva materia prima (datos) solo es complementaria con un tipo escaso de habilidades, son los dueños de dichas habilidades los que se quedan la mayor parte del premio. Para que un país como España aprovechase las oportunidades de este nuevo mundo, habría de conseguir mejoras radicales y generalizadas en la formación en matemáticas, programación e inglés; de momento, España sigue en el vagón de cola de la educación en la OCDE.

Conclusión 2

Para que los avances del Big Data penetren de forma generalizada en la empresa, habrá de sortearse un problema fundamental: ¿quién será en cada empresa el encargado de decidir qué decisiones se pueden dejar a un modelo y cuáles no? Un modelo estadístico es algo muy opaco para el que no tiene la formación necesaria. En la anterior revolución, cualquier persona podía apreciar la facilidad con la que un martillo neumático perforaba el asfalto, cuantificar la ganancia de productividad y comprar la nueva maquinaria. El ejecutivo moderno, sin embargo, ha de decidir si confiar en los resultados de un modelo que probablemente no comprenda a la hora de tomar una decisión.

Fuente: http://www.sintetia.com/big-data-la-no-revolucion-requerira-sangre-sudor-y-lagrimas/

MySQL Engines: InnoDB vs. MyISAM – A Comparison of Pros and Cons

  • admin 

The 2 major types of table storage engines for MySQL databases are InnoDB and MyISAM. To summarize the differences of features and performance,

  1. InnoDB is newer while MyISAM is older.
  2. InnoDB is more complex while MyISAM is simpler.
  3. InnoDB is more strict in data integrity while MyISAM is loose.
  4. InnoDB implements row-level lock for inserting and updating while MyISAM implementstable-level lock.
  5. InnoDB has transactions while MyISAM does not.
  6. InnoDB has foreign keys and relationship contraints while MyISAM does not.
  7. InnoDB has better crash recovery while MyISAM is poor at recovering data integrity at system crashes.
  8. MyISAM has full-text search index while InnoDB has not.

In light of these differences, InnoDB and MyISAM have their unique advantages and disadvantages against each other. They each are more suitable in some scenarios than the other.

Advantages of InnoDB

  1. InnoDB should be used where data integrity comes a priority because it inherently takes care of them by the help of relationship constraints and transactions.
  2. Faster in write-intensive (inserts, updates) tables because it utilizes row-level locking and only hold up changes to the same row that’s being inserted or updated.

Disadvantages of InnoDB

  1. Because InnoDB has to take care of the different relationships between tables, database administrator and scheme creators have to take more time in designing the data models which are more complex than those of MyISAM.
  2. Consumes more system resources such as RAM. As a matter of fact, it is recommended by many that InnoDB engine be turned off if there’s no substantial need for it after installation of MySQL.
  3. No full-text indexing.

Advantages of MyISAM

  1. Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
  2. Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
  3. Full-text indexing.
  4. Especially good for read-intensive (select) tables.

Disadvantages of MyISAM

  1. No data integrity (e.g. relationship constraints) check, which then comes a responsibility and overhead of the database administrators and application developers.
  2. Doesn’t support transactions which is essential in critical data applications such as that of banking.
  3. Slower than InnoDB for tables that are frequently being inserted to or updated, because the entire table is locked for any insert or update.

The comparison is pretty straightforward. InnoDB is more suitable for data critical situations that require frequent inserts and updates. MyISAM, on the other hand, performs better with applications that don’t quite depend on the data integrity and mostly just select and display the data.

How do I import delimited data into MySQL?

  • admin 

If you have data that you need to bring into your MySQL database, there are a few ways to do it. Exporting data out of mysql is another topic, described here.

1. Using the LOAD DATA INFILE SQL statement

For security reasons, no one has the mysql FILE priv, which means you cannot «LOAD DATA INFILE». You can, however, use a «LOAD DATA LOCAL INFILE» statement as long as you have amysql prompt on our system and have uploaded the data file to your account here first.

The «LOAD DATA LOCAL INFILE» statement will only work from a MySQL prompt on our local system. It will not work from any web-based tool such as phpMyAdmin, and will never pull a file in directly off your own computer.

To import a file this way, first upload your data file to your home directory on our system with FTP or SCP. Then get a shell prompt on our system, and then a MySQL Monitor prompt so that you can issue the SQL that will import your file.

For example, suppose you have a data file named importfile.csv that contains 3 comma separated columns of data on each line. You want to import this textfile into your MySQL table named test_table, which has 3 columns that are named field1, field2 and field3.

To import the datafile, first upload it to your home directory, so that the file is now located at /importfile.csv on our local system. Then you type the following SQL at the mysql prompt:

LOAD DATA LOCAL INFILE ‘/importfile.csv‘
INTO TABLE test_table
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
(field1, filed2, field3);

The above SQL statement tells the MySQL server to find your INFILE on the LOCAL filesystem, to read each line of the file as a separate row, to treat any comma character as a column delimiter, and to put it into your MySQL test_table as columns field1, field2, and field3 respectively. Many of the above SQL clauses are optional and you should read the MySQL documentation on the proper use of this statement.

2. Using a script to parse and import the file

You can also write a script in any programming language that can connect to MySQL (such as PHP) to open your data file, break it up into an array of lines that each represent a row of data, split each line up by the delimiter character (such as a comma ‘,’, tab ‘\t’, semicolon ‘;’, space ‘ ‘, etc.), and then perform invididual MySQL INSERT queries (one INSERT for each line) to insert all your data from the file into the appropriate table fields.

Such scripts are not difficult to write in less than 15 lines and can import data from text files just as effectively as a LOAD DATA LOCAL INFILE command. A working example script written in PHP appears below in the Annotations.

3. Importing a mysqldump

If your data file actually comes from another MySQL database, and not from Excel or any other source, then the most direct way to export and import your data would be to dump out your table or entire MySQLdatabase on the original database server using the mysqldump command, FTP the resulting dump file to your account here, and then import the dump file at a shell prompt.

For instructions on creating the dumpfile using the mysqldump command, see this FAQ. For instructions on how to import a dump made with mysqldump, see this FAQ.