Esta es la versión para imprimir de MySQL.

Si imprimes esta página, o eliges la opción de Vista preliminar de impresión de tu navegador, verás que desaparecen este cuadro y los elementos de navegación de arriba y de la izquierda, pues no son útiles en una versión impresa.
Pulsando antes en Refrescar esta página te asegurarás de obtener los últimos cambios del libro antes de imprimirlo.
Para más información, puedes ver Wikilibros:Versión para imprimir.

Contenido

Manual de MySQL

Base de datos relacional

MySQL es un sistema de gestión de bases de datos relacional desarrollado bajo licencia dual GPL/Licencia comercial por Oracle Corporation y está considerada como la base datos de código abierto más popular del mundo. Está desarrollado en su mayor parte en ANSI C y C++ y es usado por muchos sitios web grandes y populares, como Wikipedia, Google, Facebook, Twitter, Flickr o YouTube. El servidor MySQL opera en un ambiente de red donde interoperan clientes con servidores que funcionan en ordenadores a los cuales se los llama anfitriones, y que en conjunto proporcionan el servicio de una base de datos.▶

Índice • Introducción • Texto completo

Permisos de usuario

Capítulo completo

Enlaces

Wikipedia en español tiene un artículo sobre «SQL».

Wikipedia en español tiene un artículo sobre «MySQL».

Página de edición

Este libro se encuentra en construcción, para ello dispone de una página de edición, donde se coordina su construcción.

Introducción a MySQL

Una vez que hemos aprendido sobre el tipo de licencias por las cuales MySQL puede ser adquirido, pasemos a ver las características básicas de esta poderosa base de datos.

Los componentes principales usados en MySQL

El servidor MySQL opera en un ambiente de red donde interoperan clientes con servidores. Los clientes y servidores funcionan o son hospedados en ordenadores a los cuales se los llama anfitriones. El anfitrión, entonces, es el conjunto de componentes electrónicos que conforman el ordenador lo cuales son controlados por un sistema operativo. Los componentes clientes y servidores son los programas que operan en los anfitriones para darnos un servicio. En este caso, los servidores y clientes nos proporcionan el servicio de una base de datos.

La base de datos MySQL contiene los siguientes componentes:

mysqld:es el corazón de MySQL. Es el programa servidor el cual proporciona las bases de datos que se encuentran en memoria o en el disco duro. En los sistemas operativos Windows NT, 2000 o XP el servidor MySQL son los programas mysqld-nt o mysql-max-nt. Este último provee soporte para tablas BDB las cuales veremos más adelante.

Entre los clientes tenemos:

MySQLCC: Ésta interfaz gráfica permite controlar todos los aspectos del servidor MySQL. Permite crear bases de datos, tablas, usuarios, cambiar permisos, acceder y cambiar la información almacenada, etc., de una forma muy intuitiva y muy fácil. Tiene todas las opciones y tiene mejoras de la interfaz de texto mysql. Ésta aplicación debe ser descargada independientemente.
mysql: Ésta interfaz de texto permite también controlar todos los aspectos del servidor pero la interfaz es solamente texto. Permite leer instrucciones del usuario por medio del teclado como también puede leer archivos que contienen instrucciones. Ésta es la aplicación más usada por los programadores para controlar la base de datos. Ésta y otras aplicaciones que veremos a continuación son aplicaciones de texto que funcionan en la consola. (command prompt o cmd en Windows y un shell sh, bash, etc. en Unix, Linux, MacOS y demás).
mysqlimport permite importar datos a través de archivos de texto. Provee una interfaz de texto para los comandos LOAD, DATA, INFILE.
mysqldump permite hacer copias o respaldos de la información almacenada para restaurarlos en el mismo servidor o para exportarlo a otros servidores.
mysqladmin permite administrar el servidor con una interfaz gráfica y de una forma muy sencilla
mysqlcheck permite revisar la salud de la base de datos. Permite también reparar dichas bases si fuera necesario.

Aplicaciones que trabajan independientemente del servidor MySQL

myisamchk revisa las tablas y realiza operaciones de reparación.
myisampack crea versiones comprimidas de solo-lectura de tablas MyISAM.C

Más adelante en el capítulo 3 cubriremos con más detalle algunos de estos componentes.

Sistemas operativos donde MySQL puede ser usado

El servidor MySQL y los clientes soportan los principales sistemas operativos existentes, entre los cuales se encuentran los siguientes:

La familia de sistemas operativos Windows (Windows 95, 98, Me, NT, 2000 y XP). MySQL no soporta MS-DOS o Windows 3.1
La familia UNIX y derivados entre los cuales se encuentran: Los sistemas BSD (ejemplo: FreeBSD, OpenBSD, NetBSD, etc.), el sistema operativo MacOS X, System V, Solaris, HP-UX, entre otros, la familia Linux (como Fedora, RedHat, SuSE, Debian, Mandrake, Gentoo, Ubuntu, etc.)
y Novell Netware 6.5 y superior.

Para la mayoría de estos sistemas operativos ya se tienen paquetes compilados listos para ser usados. La ventaja que trae el software libre (GPL) es que el código de fuente es distribuido libremente por lo que si deseáis podéis compilar el programa en cualquier sistema operativo que deseeis.

Debido a que MySQL trabaja en un ambiente de Red (es decir entre computadores conectados en Red o al internet), el servidor MySQL y los clientes pueden inter-operar en diferentes sistemas operativos. Por ejemplo, podemos tener instalado el servidor MySQL en un sistema operativo tipo Unix (como Linux) y podemos acceder a la información a través de la red con un ordenador con sistema operativo tipo Windows a través de las aplicaciones clientes.

Diferencias entre las distribuciones de MySQL

MySQL tiene varias versiones y distribuciones. Para crear una nueva versión del programa, las versiones de MySQL siguen el siguiente patrón:

pre-alpha: es cuando se han propuesto nuevas tecnologías a incorporarse y se ha diseñado la base de esa versión
alpha: es cuando se hace público los códigos de fuente para que sea puesto a prueba para corregir cualquier error que tenga.
beta: Los errores encontrados se han corregido y la versión parece estar estable.
gamma: Es cuando no se han encontrado más errores en la versión Beta por al menos un mes.
Produccion: Ésta es la etapa final. Todos los errores conocidos han sido corregidos.

Hasta el día de hoy tenemos la versión de producción MySQL 4.1 y la versión de prueba BETA 5.0 pero este libro se centrará solamente en las características de la versión estable ya que esa es la versión usada para el examen de certificación.

Como habíamos hablado anteriormente, MySQL también varía de acuerdo con el tipo de licencia. Éstas distribuciones se clasifican en :

Comercial:

MySQL Classic
MySQL Pro

GPL:

MySQL standard
MySQL MAX

Hay algunas diferencias importantes para notar entre estas distribuciones:

MySQL classic no incluye soporte de tablas InnoDB
MySQL MAX soporta Datos Geográficos (GIS) y la máquina de almacenamiento BDB.

Clientes

MySQL AB ofrece varias aplicaciones clientes para comunicarse con el servidor. Entre ellas tenemos:

La Librería en C libmysqlclient: Esta librería es la más usada para crear aplicaciones en el lenguaje C que se comuniquen con el servidor MySQL. De esta librería se han derivado varias aplicaciones para conectar a PHP, Pascal, Pyton y otros con la base de datos.
El conector ODBC : Este conector provee compatibilidad entre las aplicaciones que usan el lenguaje ODBC para que puedan comunicarse con la base de datos MySQL
El conector J : Este conector provee compatibilidad entre las aplicaciones Java que usan el lenguaje JDBC para que puedan comunicarse con la base de datos MySQL

Estos son los únicos clientes / componentes que son oficialmente respaldados por la empresa MySQL AB. Existen otros clientes y componentes que os podréis descargar de la página oficial de MySQL los cuales sirven para conectar otros lenguajes a MySQL. Estos programas, aun cuando han sido diseñados por los miembros del equipo MySQL, no son oficiales ni cuentan con respaldo por parte de la compañía. Si váis a desarrollar alguna aplicación con estos componentes extras, os recomendamos que os informéis bien acerca de ellos.

Tanto el servidor como los clientes y conectores oficiales se los puede obtener de con los dos tipos de licencia, comercial y GPL en distribuciones listas para usarse o en código de fuente para que podáis modificarlas a vuestro gusto.

Preguntas de ejemplo para la certificación

Nombre el tipo de distribuciones de MySQL de acuerdo con su licencia (comercial o GPL):
Nombre 2 aplicaciones que trabajan independientemente del servidor MySQL.
Un servidor MySQL que esté operando en Linux podrá ser accedido por un cliente que esté trabajando bajo la plataforma MacOS (Verdadero/Falso).
Un cliente en Windows solo podrá acceder servidores MySQL que estén operando bajo el sistema Windows (Verdadero/Falso).
El servidor mysqld no dispone del código de fuente, únicamente los componentes o clientes. (Verdadero/Falso).
El nombre de la aplicación que funciona como servidor es (seleccione la mejor respuesta):
1. mysql
2. mysqlCC
3. mysqladmin
4. mysqld
5. mysqlserver

Respuestas

1. Comercial:

MySQL Classic
MySQL Pro

GPL:

MySQL standard
MySQL MAX

De todas estas versiones la única que no posee soporte para tablas InnoDB es la licencia comercial MySQL Classic.

2. myisamchk y myisampack estas dos aplicaciones trabajan directamente con los archivos de tablas MyISAM y no requieren del servidor MySQL mientras que los otros componentes y clientes si requieren del servidor.

3. Verdadero : Debido a que MySQL trabaja en un ambiente de red, tanto el servidor como los clientes pueden operar en diferentes plataformas y podrán comunicarse sin problema.

4. Falso: Debido a que MySQL trabaja en un ambiente de red, tanto el servidor como los clientes pueden operar en diferentes plataformas y podrán comunicarse sin problema.

5. Falso: Tanto el servidor como los clientes y conectores oficiales se los puede obtener de con los dos tipos de licencia, comercial y GPL en distribuciones listas para usarse o en código de fuente para que podáis modificarlas a vuestro gusto.

6. 4. mysqld es el nombre de la aplicación que funciona como servidor. mysql es una interfaz de texto del servidor. mysqlCC es una interfaz gráfica del servidor. mysqladmin permite administrar el servidor con una interfaz gráfica y de una forma muy sencilla. mysqlserver no existe.

Qué es MySQL y MySQL AB

Bienvenidos al Capítulo primero de nuestro curso. En este capítulo aprenderemos las diferencias entre MySQL y MySQL AB y los tipos de licencias bajo las cuales opera esta base de datos.

Diferencias entre MySQL y MySQL AB

El nombre MySQL muchas veces es usado intercambiablemente entre la compañía que produce la base de datos MySQL y la base de datos propiamente dicha. Esto se debe a que la compañía que produce la base de datos MySQL se llama MySQL AB (AB son los distintivos de "compañía de inversión" en Suecia). Recalcamos esta diferencia para conocimiento general. MySQL AB entonces es la compañía que desarrolla y vende la base de datos MySQL y las demás herramientas que le acompañan.

En sus inicios la base de datos MySQL pasó desapercibida sin recibir casi ninguna atención sino hasta hace un par de años en la que la base de datos pasó a ser una de las bases de datos más usada en el internet. Este cambio se debe principalmente a las mejoras que empezó a incorporar: su estabilidad, velocidad y costo la hacen única entre sus competidores. También esta popularidad se debe al crecimiento del movimiento Open Source y a la popularidad del lenguaje gratuito de programación para el Web PHP.

Todos estos factores fueron decisivos para que MySQL sea en nuestros días la base de datos usada por millones de personas, una de las más populares en el mercado.

Licencias de MySQL

Parte de ésta popularidad se debe también al hecho de que MySQL puede ser utilizada o modificada por cualquier persona o empresa sin ningún costo. El programa incorpóra dos tipos de licencias:

1. La licencia GNU GPL: la cual permite a cualquier persona, empresa o entidad usar el programa sin ningúna restricción. También se da la libertad de modificar el producto y nuevamente re-distribuirlo bajo la misma licencia. Esta licencia se caracteríza por ser completamente gratuita. Para leer la licencia puedes visitar: Licencia de documentación libre GNU

2. MySQL también incorpora una licencia comercial con la cual las empresas pueden re-distribuir el producto bajo sus propios términos. La licencia sin embargo tiene un precio pero no es costosa comparada con licencias de bases de datos comerciales como SQL Server de Microsoft.

Estos dos tipos de licencias se aplican tanto al servidor como a las interfaces y librerias clientes como el C client library, mysqladmin, MySQLCC, mysqldump, libmysqlclient, MySQL Connector/ODBC y J. Y no se aplican a la documentación producida por MySQL AB la cual está bajo la licencia de propiedad intelectual.

Algo importante para recalcar es que si uno utiliza la licencia GPL no obtendrá ningun tipo de asistencia técnica, soporte o garantía por parte de MySQL AB. Si vuestra empresa requiere de alguno de estos servicios es recomendable que se compre la licencia comercial. Sin embargo, en el internet existe un amplio grupo de usuarios que siempre están dispuestos a ayudaros con cualquier pregunta que tengáis. Para acceder a ellos podrás suscribirte a una lista de correo. Para suscribirte podéis ingresar a : http://lists.mysql.com

Preguntas de ejemplo para la certificación

Verdadero o Falso:

1. Cualquier persona puede usar y distribuir la base de datos MySQL de forma gratuita sin tener que pagar ninguna Licencia y sin preocuparse por ningún tipo de reglas.

2. La licencia comercial no se aplica a las utilidades MySQLCC ni mysqladmin

3. La licencia gratuita es solo para empresas pequeñas o para uso personal

4. La documentación de MySQL también puede ser copiada, modificada y redistribuida libremente.

Respuestas:

Falso: El programa MySQL puede ser usado y distribuido gratuitamente solamente bajo las reglas de la licencia GPL.
Falso: Los dos tipos de licencias (GPL y comercial) se aplican para los programas distribuidos por MySQL AB con excepción de la documentación
Falso: Cualquier persona o empresa puede hacer uso de los dos tipos de licencia
Falso: La documentación esta amparada bajo la licencia de propiedad intelectual.

SQL Básico

El lenguaje de manipulación de datos (Data Manipulation Language, o DML) es un lenguaje de programación que se utiliza para la definición del nivel externo y las operaciones de manipulación de la información.

La definición del nivel externo de representación consiste en la definición de vistas de los datos que utilizarán los usuarios de la base de datos.

La manipulación de los datos consiste en la realización de operaciones de inserción, borrado, modificación y consulta de la información almacenada en la base de datos. La inserción y el borrado son el resultado de añadir nueva información a la ya que se encontraba almacenada o eliminarla de nuestra base de datos, tomando en cuenta las restricciones marcadas por el DDL y las relaciones entre la nueva información y la antigua. La modificación nos permite alterar esta información, y la consulta nos permite el acceso a la información almacenada en la base de datos siguiendo criterios específicos.

El DML de un SGBD dependerá del tipo de modelo lógico de datos que se utilice en cada caso: Un SGBD jerárquico o de red, que poseen un modelo lógico basado en registros y sus relaciones, utilizará un DML con un nivel de abstracción menos complejo que el que sería utilizado en un SGBD relacional, y se usarían punteros y registros. Una operación en este SGBD tendrá dos componentes diferenciados: una primera fase de acceso cuyo objeto es el situar los punteros para seleccionar correctamente el registro a manipular, y una segunda fase que consiste en describir la operación deseada. El DML en un SGBD relacional utilizaran tablas y para el acceso a la información se utiliza el cálculo relacional y el álgebra relacional. El cálculo relacional puede estar orientado a tuplas u orientado a dominios:

**Sobre la sintaxis**
Acceso	Sintaxis
Orientado a tuplas	p(t)}
Orientado a dominios	P(X1,X2,...Xn)}

Donde t representa una tabla o relación y p(t) es un predicado sobre ella. Donde X1..Xn represente una serie de variables de dominio y P es un predicado.

El álgebra relacional propone una serie de operadores (Unión, diferencia, selección, proyección y producto cartesiano y adicionales operadores de nivel avanzado) que pueden ser aplicados a una o varias relaciones y devolver una relación con características distintas a las anteriores.

El comando SELECT

Seleccionar Registros

SELECT FROM nombre_de_tabla WHERE condición;

nombre_de_tabla: La tabla que queramos.

condición: La condición que han de cumplir los registros.

Ejemplo: Seleccionar a los alumnos de cuarto.

SELECT nombre,apellidos FROM alumnos WHERE curso=4;

Tablas MyISAM

El servidor de bases de datos MySQL soporta distintos tipos de tablas, tales como ISAM, MyISAM, InnoDB y BDB (Berkeley Database). De éstos, InnoDB es el tipo de tabla más importante (después del tipo predeterminado, MyISAM), y merece una atención especial.

Las tablas del tipo InnoDB están estructuradas de forma distinta que MyISAM, ya que se almacenan en un sólo archivo en lugar de tres, y sus principales características son que permite trabajar con transacciones, y definir reglas de integridad referencial.

Por default, las tablas InnoDB ejecutan un lectura consistente (consistent read). Esto significa que cuando una sentencia SELECT es ejecutada, MySQL regresa los valores presentes en la base de datos hasta la transacción más reciente que ha sido completada. Si alguna transacción está en progreso, los cambios hechos por alguna sentencia INSERT o UPDATE no serán reflejados. Sin embargo, existe una excepción: las transacciones abiertas si pueden ver sus propios cambios. Para demostrar esto, necesitamos establecer dos conexiones al servidor MySQL.

Utilizando los programas clientes de MySQL

Haciendo uso de las aplicaciones clientes

Como habíamos visto en el capítulo 2, existen varias aplicaciones y utilidades que vienen con las distribuciones de MySQL, en esta sección veremos como usar cada una de ellas. La mayoría de ellas requieren que utilicéis la línea de comandos de vuestro sistema operativo (command prompt o cmd en la familia Windows o el shell en la familia Unix -linux, MacOS, etc-). En esta sección veremos con más detalle las funciones principales de la interfáz de texto mysql debido a que es la interfáz más usada, pero también veremos como se usa algunas otras aplicaciones.

Para conocer cuales son las funciones que soporta cierta aplicación, podemos escribir el nombre de la aplicación seguido por el comando --help, el cual desplegará una pantalla de ayuda indicando que funciones soporta dicha aplicación. Por ejemplo para ver de que funciones disponemos en la interfáz mysql podemos escribir:

mysql --help

el resultado se lo puede ver en este link : Pantalla: mysql --help

Para ver que versión de la aplicación se está usando, se puede escribir el nombre de la aplicación seguido por el comando --version:

mysql --version

el resultado en mi caso es es siguiente:

mysql  Ver 14.7 Distrib 4.1.11, for Win32 (ia32)

Esta pantalla nos dice que este cliente está funcionando con la versión 4.1.11 del servidor MySQL.

Formas de las opciones

En casi todos los casos, las opciones están disponibles en dos formas: una forma larga y otra forma corta. Por ejemplo si deseamos saber que versión del servidor estamos usando podemos hacerlo usando el comando --version como ya lo habíamos visto o también usando el comando -V de la siguiente forma:

mysql -V

lo cual nos dará el mismo resultado que con --version.

Debéis tener cuidado que las opciones, eso si, son de caracteres sensitivos, por lo que -v es diferente a -V o --version es diferente a --Version. Muchas veces las opciones son válidas pero tienen funciones distintas. -V muestra la versión del programa y -v dice a mysql que debe desplegar información extra cuando se realicen las operaciones.

Algunas opciones requieren información adicional. En el caso que deseéis conectaros a un servidor externo, podéis utilizar la opcion --host o -h pero debéis acompañar esta opción con el nombre del servidor. Por ejemplo:

mysql --host=host.ejemplo.com
mysql -h host.ejemplo.com

Las dos sentencias anteriores son equivalentes. La diferencia entre las opciones largas y las opciones cortas cuando requieren de otros argumentos es que con las opciones largas debéis poner un signo = entre la opción y el argumento, mientra que con las opciones cortas simplemente debéis separalas con un espacio.

En otros casos, cuando una opción requiere de parámetros, si no los especificamos, la aplicación asumirá valores pre-determinados o preguntará que valores se desean en el caso de claves. Por ejemplo, si deseamos ingresar a la interfáz mysql con el usario root, usamos la opcion -u seguido por el nombre del usuario. La opción -p requiere del parámetro "password" o clave pero si no lo especificamos, mysql lo pedirá:

mysql -u root -p

mysql desplegará :

Enter password:

Este comportamiento de pedir el argumento faltante solamente sucede con claves, en el resto de casos, mysql tomará los valores pre-determinados o por defecto.

Opciones de la conexión

Para poder conectarse a un servidor MySQL a travéz de un cliente debemos especificar donde se encuentra el servidor y tenemos que dar las credenciales correspondientes (nombre de usuario y contraseña) para poder acceder a el.

Véase también

Ayuda en línea

Chuletario de trucos y recetas

INTRODUCCIÓN

En esta sección se incluyen trucos, recetas y consejos para el trabajo diario con MySQL.

TIMEOUT EN LAS CONEXIONES TCP A MYSQL

mysql wait_timeout: Por defecto mysql cierra una conexión después de 8 horas sin recibir ninguna petición del cliente y sin que el cliente sea notificado de las mismas. En una aplicación web puede ser normal abrir un pool de conexiones desde un servidor (Tomcat p.ej) de forma que siempre haya conexiones de sobra sin usar. Esto en la práctica se traduce en que debemos hacer una de dos: - bien reconfigurar mysql (/etc/mysql/my.cnf) para aumentar el wait_timeout - o bien "refrescar" periodicamente las conexiones del pool para resetear el contador interno de timeout de mysql.

Los servidores de aplicaciones suelen tener también parámetros de configuración que nos ayudan: P.ej, Tomcat incluye también un sistema automático de pool de conexiones que en principio soluciona el problema. En la práctica lo minimiza pero aleatoriamente cada 2 o 3 semanas, por experiencia del que escribe estas líneas, vuelve a fallar (Tomcat versión 5).

Por tanto puede ser una buena opción el desabilitar el wait_timeout editando /etc/mysql/my.conf añadiendo la linea wait_timeout=0.

PROBLEMAS CON CORTAFUEGOS

En alguna extraña ocasión al activar el cortafuegos (IPtables) impidiendo la conexión de un cliente al puerto TCP 3306 de mysql, la conexión cliente queda en estado SYNC_SEND y el driver (JDBC) de mysql se bloquea por completo teniendo que reiniciar el servidor (tomcat). (*Pendiente: Ver con que versión del driver se produce).

PROBLEMAS CON DNS

La opción skip-name-resolve evita que el servidor mysql realice una resolución inversa de DNS (obtener el nombre simbólico del cliente a partir de su IP) de forma que es conveniente activarla si sólo vamos a trabajar con IPs ya que si falla el DNS la conexión se retarda y puede generar "timeouts" en el cliente de forma aleatoria.

RESUMEN DE GESTIÓN DE ÍNDICES EN MYSQL

Destaco lo que la documentación de MySQL dice al respecto:

"Una PRIMARY KEY es una KEY única donde todas las columnas de la key deben ser definidas como NO NULAS. Si no se declaran explicitamente como NOT NULL, MySQL lo hace implicitamente (y en silencio). Una tabla puede tener solo una PRIMARY KEY. Si no tiene tal PRIMARY KEY y una aplicación pregunta por la misma, MySQL devuelve el primer índice ÚNICO que no tiene columnas NULL como la PRIMARY KEY."

Ejemplo de creación de índice típico (entero, llave primaria y autoincrementado):

... idx INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

Otras fórmulas comunes:

CREATE TABLE test (
   idx INT NOT NULL,
   last_name CHAR (30) NOT NULL,
   first_name CAHR (30) NOT NULL,
   PRIMARY KEY (idx),
   INDEX full_name (last_name, first_name)
);

ALTER TABLE test ADD INDEX USING HASH|BTREE (idx)

Conviene repasar también la documentación para ver las optimizaciones de velocidad que soporta MySQL a la hora de gestionar índices (Mysql Sección Optimizando MySQL - DELAYED_KEY_WRITE )

Backup MySQL

El siguiente script sirve de ejemplo para realizar backups de varios servidores MySQL de forma cómoda . Funciona en sistemas GNU (Linus, BSD,...), Solaris y Windows+Cygwin. Los servidores en este caso vienen definidos por sus IPs (IP1 IP2 IP3).

backupMySQL.sh

#!/bin/sh
LOGFILE=/var/log/backupMySQL.$(whoami).$(date +%Y%m%d).log
exec 1>>${LOGFILE} 2>&1
echo -n "Iniciando ${0} " ; date +%Y-%m-%d\ %H:%M:%S
cd /var/backupMysql
USUARIO="XXXX"
PASSWRD="YYYY"
for server in IP1 IP2 IP3; do
    echo ""
    echo "$(date +%Y/%m/%d-%H:%M:%S) Comenzando mysqldump del servidor ${server}"
    ddbbNames=$(mysql -h${server} -u${USUARIO} -p${PASSWRD} -e "show databases;" | grep -v Database | grep -v nombreBBDDNoBackup)
    for ddbb in $ddbbNames; do
        tables=$(mysql -h${server} -u${USUARIO} -p${PASSWRD} $ddbb -e "show tables;" | grep -v -i backup | \
            grep -v -i Tables_in_ | while read line ; do echo -en "${line} " ; done ;)
        mysqldumpOpts="--add-locks --disable-keys --extended-insert --delayed-insert"
        cmd="mysqldump ${mysqldumpOpts} -h${server} -u${USUARIO} -p${PASSWRD} ${ddbb} ${tables}"
        tiempoEmpleado=$((time ${cmd} > ${server}_mysqldump_${ddbb}.sql) 2>&1 | grep real | cut -f 2)
        echo "$(date +%Y/%m/%d-%H:%M:%S) Servidor:${server}, BBDD:${ddbb}, Comando:${cmd},  Tiempo Empleado: ${tiempoEmpleado}";
    done;
done
echo -n "Finalizando ${0} " ; date +%Y-%m-%d\ %H:%M:%S

La salida del comando es similar a:

2009/06/01-12:09:45 Comenzando mysqldump del servidor IP1
2009/06/01-12:09:46 Servidor:IP1, BBDD:nombre1, ...nombreN  \
      nombre1, ...nombreN  Tiempo Empleado: 1m0.036s
 ...
2009/06/01-12:09:46 Comenzando mysqldump del servidor IP2
2009/06/01-12:09:47 Servidor:192.168.0.215, BBDD:nombre1, ...nombreN  \
      nombre1, ...nombreN  Tiempo Empleado: 3m0.568s
 ...

Es importante revisar de vez en cuando la salida comprobando que el tiempo empleado no es excesivo. Normalmente no suele tardarse más de un segundo para las tablas administrativas con metadatos y un par de minutos para las tablas "grandes" (10 millones de registros) en un PC moderno.

MOTORES SOPORTADOS POR MySQL

Utilizamos el comando show engines

mysql> show engines;
+------------+----------+------------------------------------------------------------+
| Engine     | Support  | Comment                                                    |
+------------+----------+------------------------------------------------------------+
| MyISAM     | DEFAULT  | Default engine as of MySQL 3.23 with great performance     |
| HEAP       | YES      | Alias for MEMORY                                           |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables  |
| MERGE      | YES      | Collection of identical MyISAM tables                      |
| MRG_MYISAM | YES      | Alias for MERGE                                            |
| ISAM       | YES      | Obsolete storage engine, now replaced by MyISAM            |
| MRG_ISAM   | YES      | Obsolete storage engine, now replaced by MERGE             |
| InnoDB     | YES      | Supports transactions, row-level locking, and foreign keys |
| INNOBASE   | YES      | Alias for INNODB                                           |
| BDB        | DISABLED | Supports transactions and page-level locking               |
| BERKELEYDB | DISABLED | Alias for BDB                                              |
| NDBCLUSTER | DISABLED | Clustered, fault-tolerant, memory-based tables             |
| NDB        | DISABLED | Alias for NDBCLUSTER                                       |
| EXAMPLE    | NO       | Example storage engine                                     |
| ARCHIVE    | YES      | Archive storage engine                                     |
| CSV        | YES      | CSV storage engine                                         |
| BLACKHOLE  | NO       | Storage engine designed to act as null storage             |
+------------+----------+------------------------------------------------------------+

Optimizando MySQL

Extraido de MySQL Query Optimization tiene la respuesta.:

   """For MyISAM tables, another strategy for reducing index flushing is to use the DELAYED_KEY_WRITE table option. With this option, data rows are written to the data file immediately as usual, but the key cache is flushed only occasionally rather than after each insert. To use delayed index flushing on a server-wide basis, start mysqld with the --delay-key-write=on option. In this case, index block writes for a table are delayed until blocks must be flushed to make room for other index values, until a FLUSH TABLES command has been executed, or until the table is closed.
   If you choose to use delayed key writes for MyISAM tables, abnormal server shutdowns can cause loss of index values. This is not a fatal problem because MyISAM indexes can be repaired based on the data rows, but to make sure that the repairs happen, you should start the server with the --myisam-recover=FORCE option. This option causes the server to check MyISAM tables when it opens them and repair them automatically if necessary."""

En el manual de referencia de Mysql:

   --delay-key-write[= OFF | ON | ALL]

   Specify how to use delayed key writes. Delayed key writing causes key buffers not to be flushed between writes for MyISAM tables. OFF disables delayed key writes. ON enables delayed key writes for those tables that were created with the DELAY_KEY_WRITE option. ALL delays key writes for all MyISAM tables. Available as of MySQL 4.0.3. See Section 7.5.2, “Tuning Server Parameters”, and Section 14.1.1, “MyISAM Startup Options”.

En la sección 7.5.2 se ve entre otros que podemos definir el "delay" máximo por número de inserts o por un timeout:

http://dev.mysql.com/doc/refman/4.1/en/server-parameters.html

delayed_insert_limit     current value: 100
delayed_insert_timeout   current value: 300

Chequeo/Reparación automática de tablas Mysql

Palabras relacionadas: pérdida de datos, recuperación de datos, backup, disaster recovery, fichero corrupto.

El siguiente script repara de forma automática fallos en Mysql. ¡¡¡Antes de ejecutarlo hay que asegurar que hemos hecho copias de los ficheros correspondientes *MYD (Datos), *MYI(Indices) y *frm(esquema)!!!

mysqlcheck es el commando encargado de realizar la tarea ya permite recuperar "en caliente" -con mysqld en marcha-.

#!/bin/sh
commonOpts="-uABC -p???"  # <- Sustituir con usuario y contraseña reales.
ddbbList=`mysql $commonOpts -e "show databases;" | grep -v Database | grep -v information_schema`
for ddbbItem in $ddbbList; do
    mysqlcheck $commonOpts --auto-repair --extended $ddbbItem ;   # Ver 'man mysqlcheck' para más detalles.
done

Algunas notas importantes a tener en cuenta.

--auto-repair trata de reperar automáticamente en caso de error.
--extended puede reemplazarse por --fast( chequea sólo tablas no cerradas correctamente) ,
--medium-check (recomendado, encuentra el 99,9% de los errores) o
--quick (chequea sólo índices, que es la tabla que más problemas suele dar).

Cuando trabajamos con tablas MyISAM (lo normal) existen 3 ficheros por tabla. MYD (Datos), MYI (Índices) y frm (esquema/definición de la tabla). Si MYI está corrupto podemos recuperar MYD apoyandonos solamente en frm (el esquema/definición es un fichero de sólo lectura una vez creada la tabla y por tanto no debería estar "corrupto" salvo por fallos físicos del disco duro mientras que MYI contiene índices y puede corromperse con mayor facilidad debido a cortes de alimentación, ...). De todas formas se recomienda no utilizar frm salvo que MYI no pueda ser usado (en realidad mysqlcheck -myisamchk - no permiter la opción frm y hay que utilizar 'REPAIR TABLE USER_FRM' una vez conectados a mysql para poder hacerlo).

Extraido de la doc. oficial de MySQL:

http://dev.mysql.com/doc/refman/4.1/en/repair-table.html
As of MySQL 4.0.2, there is a USE_FRM mode for REPAIR TABLE. Use this if the
.MYI index file is missing or if its header is corrupted. In this mode, MySQL
re-creates the .MYI file using information from the .frm file. This kind of
repair cannot be done with myisamchk.
Note: Use this mode only if you cannot use regular REPAIR modes. The .MYI
header contains important table metadata (in particular, current AUTO_INCREMENT
value and Delete link) that are lost in REPAIR ... USE_FRM. Don't use
USE_FRM if the table is compressed because this information is also stored in the
.MYI file.

En ocasiones mysqlcheck no es capaz de reparar la BBDD. En tal caso podemos intentar parar el servidor Mysql y ejecutar a mano el comando myisam sobre el fichero físico afectado (normalmente /var/lib/mysql/NombreBBDD/tabla.myd).

Monitorización myTop

Equivale a un top de Linux, pudiendo acceder a bbdd. remotas.Ej:

mytop -h servidorA -uABC -p???

Stress Testing

Utilizamos SysBench. Ej. Suponemos 2 nodos ndb, uno en localhost y otro en IP2.

Preparamos la BBDD de prueba. Para ello primero ejecutamos un "CREATE DATABASE sbtest;" en los dos host con mysqld. A continuación:

./sysbench --test=oltp --mysql-table-type=ndbcluster --oltp-table-siz e=100000 \
    --mysql-socket=/var/run/mysqld/mysqld.sock --mysql-user=ABC --mysql-password=?????? prepare

Con esto queda preparada la bb.dd. en modo cluster.

Ejecutamos un primer sysbench contra IP2:

./sysbench --num-threads=16 --max-requests=100000 \
    --test=oltp --oltp-table-size=100000 --oltp-read-only \
    --mysql-user=ABC --mysql-password=ABC --mysql-host=IP2 run

En localhost:

./sysbench --num-threads=16 --max-requests=100000 \
    --test=oltp --oltp -table-size=100000  --oltp-read-only \ 
    --mysql-user=ABC --mys ql-password=ABC --mysql-socket=/var/run/mysqld/mysqld.sock run

VARIOS SIN ORDENAR

Google mMAIM es una herramienta desarrollada por Google. mMAIM es la abreviatura de MySQL Monitoring And Investigation Module. El objetivo de mMAIM es facilitar la monitorización y análisis de servidores MySQL e integrarse fácilmente en el entorno. Permite mostrar estadísticas de conexión Maestro/Esclavo, estadísticas de eficiencia y estadísticas de la mayoría de los comandos "show".

Gestión gráfica de MySQL mediante SQLYog

SQLYog ofrece una versión gratuita de cliente gráfico para Mysql que corre sobre Windows y Wine (soportado este último de forma "oficial"). Así mismo ofrece una versión de pago con características avanzadas como posibilidad de ver diferencias entre bases de datos, sincronizar entre servidores, etc...

Licencia

Version 1.2, November 2002


Copyright (C) 2000,2001,2002  Free Software Foundation, Inc.
51 Franklin St, Fifth Floor, Boston, MA  02110-1301  USA
Everyone is permitted to copy and distribute verbatim copies
of this license document, but changing it is not allowed.

0. PREAMBLE

The purpose of this License is to make a manual, textbook, or other functional and useful document "free" in the sense of freedom: to assure everyone the effective freedom to copy and redistribute it, with or without modifying it, either commercially or noncommercially. Secondarily, this License preserves for the author and publisher a way to get credit for their work, while not being considered responsible for modifications made by others.

This License is a kind of "copyleft", which means that derivative works of the document must themselves be free in the same sense. It complements the GNU General Public License, which is a copyleft license designed for free software.

We have designed this License in order to use it for manuals for free software, because free software needs free documentation: a free program should come with manuals providing the same freedoms that the software does. But this License is not limited to software manuals; it can be used for any textual work, regardless of subject matter or whether it is published as a printed book. We recommend this License principally for works whose purpose is instruction or reference.

1. APPLICABILITY AND DEFINITIONS

This License applies to any manual or other work, in any medium, that contains a notice placed by the copyright holder saying it can be distributed under the terms of this License. Such a notice grants a world-wide, royalty-free license, unlimited in duration, to use that work under the conditions stated herein. The "Document", below, refers to any such manual or work. Any member of the public is a licensee, and is addressed as "you". You accept the license if you copy, modify or distribute the work in a way requiring permission under copyright law.

A "Modified Version" of the Document means any work containing the Document or a portion of it, either copied verbatim, or with modifications and/or translated into another language.

A "Secondary Section" is a named appendix or a front-matter section of the Document that deals exclusively with the relationship of the publishers or authors of the Document to the Document's overall subject (or to related matters) and contains nothing that could fall directly within that overall subject. (Thus, if the Document is in part a textbook of mathematics, a Secondary Section may not explain any mathematics.) The relationship could be a matter of historical connection with the subject or with related matters, or of legal, commercial, philosophical, ethical or political position regarding them.

The "Invariant Sections" are certain Secondary Sections whose titles are designated, as being those of Invariant Sections, in the notice that says that the Document is released under this License. If a section does not fit the above definition of Secondary then it is not allowed to be designated as Invariant. The Document may contain zero Invariant Sections. If the Document does not identify any Invariant Sections then there are none.

The "Cover Texts" are certain short passages of text that are listed, as Front-Cover Texts or Back-Cover Texts, in the notice that says that the Document is released under this License. A Front-Cover Text may be at most 5 words, and a Back-Cover Text may be at most 25 words.

A "Transparent" copy of the Document means a machine-readable copy, represented in a format whose specification is available to the general public, that is suitable for revising the document straightforwardly with generic text editors or (for images composed of pixels) generic paint programs or (for drawings) some widely available drawing editor, and that is suitable for input to text formatters or for automatic translation to a variety of formats suitable for input to text formatters. A copy made in an otherwise Transparent file format whose markup, or absence of markup, has been arranged to thwart or discourage subsequent modification by readers is not Transparent. An image format is not Transparent if used for any substantial amount of text. A copy that is not "Transparent" is called "Opaque".

Examples of suitable formats for Transparent copies include plain ASCII without markup, Texinfo input format, LaTeX input format, SGML or XML using a publicly available DTD, and standard-conforming simple HTML, PostScript or PDF designed for human modification. Examples of transparent image formats include PNG, XCF and JPG. Opaque formats include proprietary formats that can be read and edited only by proprietary word processors, SGML or XML for which the DTD and/or processing tools are not generally available, and the machine-generated HTML, PostScript or PDF produced by some word processors for output purposes only.

The "Title Page" means, for a printed book, the title page itself, plus such following pages as are needed to hold, legibly, the material this License requires to appear in the title page. For works in formats which do not have any title page as such, "Title Page" means the text near the most prominent appearance of the work's title, preceding the beginning of the body of the text.

A section "Entitled XYZ" means a named subunit of the Document whose title either is precisely XYZ or contains XYZ in parentheses following text that translates XYZ in another language. (Here XYZ stands for a specific section name mentioned below, such as "Acknowledgements", "Dedications", "Endorsements", or "History".) To "Preserve the Title" of such a section when you modify the Document means that it remains a section "Entitled XYZ" according to this definition.

The Document may include Warranty Disclaimers next to the notice which states that this License applies to the Document. These Warranty Disclaimers are considered to be included by reference in this License, but only as regards disclaiming warranties: any other implication that these Warranty Disclaimers may have is void and has no effect on the meaning of this License.

2. VERBATIM COPYING

You may copy and distribute the Document in any medium, either commercially or noncommercially, provided that this License, the copyright notices, and the license notice saying this License applies to the Document are reproduced in all copies, and that you add no other conditions whatsoever to those of this License. You may not use technical measures to obstruct or control the reading or further copying of the copies you make or distribute. However, you may accept compensation in exchange for copies. If you distribute a large enough number of copies you must also follow the conditions in section 3.

You may also lend copies, under the same conditions stated above, and you may publicly display copies.

3. COPYING IN QUANTITY

If you publish printed copies (or copies in media that commonly have printed covers) of the Document, numbering more than 100, and the Document's license notice requires Cover Texts, you must enclose the copies in covers that carry, clearly and legibly, all these Cover Texts: Front-Cover Texts on the front cover, and Back-Cover Texts on the back cover. Both covers must also clearly and legibly identify you as the publisher of these copies. The front cover must present the full title with all words of the title equally prominent and visible. You may add other material on the covers in addition. Copying with changes limited to the covers, as long as they preserve the title of the Document and satisfy these conditions, can be treated as verbatim copying in other respects.

If the required texts for either cover are too voluminous to fit legibly, you should put the first ones listed (as many as fit reasonably) on the actual cover, and continue the rest onto adjacent pages.

If you publish or distribute Opaque copies of the Document numbering more than 100, you must either include a machine-readable Transparent copy along with each Opaque copy, or state in or with each Opaque copy a computer-network location from which the general network-using public has access to download using public-standard network protocols a complete Transparent copy of the Document, free of added material. If you use the latter option, you must take reasonably prudent steps, when you begin distribution of Opaque copies in quantity, to ensure that this Transparent copy will remain thus accessible at the stated location until at least one year after the last time you distribute an Opaque copy (directly or through your agents or retailers) of that edition to the public.

It is requested, but not required, that you contact the authors of the Document well before redistributing any large number of copies, to give them a chance to provide you with an updated version of the Document.

4. MODIFICATIONS

You may copy and distribute a Modified Version of the Document under the conditions of sections 2 and 3 above, provided that you release the Modified Version under precisely this License, with the Modified Version filling the role of the Document, thus licensing distribution and modification of the Modified Version to whoever possesses a copy of it. In addition, you must do these things in the Modified Version:

A. Use in the Title Page (and on the covers, if any) a title distinct from that of the Document, and from those of previous versions (which should, if there were any, be listed in the History section of the Document). You may use the same title as a previous version if the original publisher of that version gives permission.

B. List on the Title Page, as authors, one or more persons or entities responsible for authorship of the modifications in the Modified Version, together with at least five of the principal authors of the Document (all of its principal authors, if it has fewer than five), unless they release you from this requirement.

C. State on the Title page the name of the publisher of the Modified Version, as the publisher.

D. Preserve all the copyright notices of the Document.

E. Add an appropriate copyright notice for your modifications adjacent to the other copyright notices.

F. Include, immediately after the copyright notices, a license notice giving the public permission to use the Modified Version under the terms of this License, in the form shown in the Addendum below.

G. Preserve in that license notice the full lists of Invariant Sections and required Cover Texts given in the Document's license notice.

H. Include an unaltered copy of this License.

I. Preserve the section Entitled "History", Preserve its Title, and add to it an item stating at least the title, year, new authors, and publisher of the Modified Version as given on the Title Page. If there is no section Entitled "History" in the Document, create one stating the title, year, authors, and publisher of the Document as given on its Title Page, then add an item describing the Modified Version as stated in the previous sentence.

J. Preserve the network location, if any, given in the Document for public access to a Transparent copy of the Document, and likewise the network locations given in the Document for previous versions it was based on. These may be placed in the "History" section. You may omit a network location for a work that was published at least four years before the Document itself, or if the original publisher of the version it refers to gives permission.

K. For any section Entitled "Acknowledgements" or "Dedications", Preserve the Title of the section, and preserve in the section all the substance and tone of each of the contributor acknowledgements and/or dedications given therein.

L. Preserve all the Invariant Sections of the Document, unaltered in their text and in their titles. Section numbers or the equivalent are not considered part of the section titles.

M. Delete any section Entitled "Endorsements". Such a section may not be included in the Modified Version.

N. Do not retitle any existing section to be Entitled "Endorsements" or to conflict in title with any Invariant Section.

O. Preserve any Warranty Disclaimers.

If the Modified Version includes new front-matter sections or appendices that qualify as Secondary Sections and contain no material copied from the Document, you may at your option designate some or all of these sections as invariant. To do this, add their titles to the list of Invariant Sections in the Modified Version's license notice. These titles must be distinct from any other section titles.

You may add a section Entitled "Endorsements", provided it contains nothing but endorsements of your Modified Version by various parties--for example, statements of peer review or that the text has been approved by an organization as the authoritative definition of a standard.

You may add a passage of up to five words as a Front-Cover Text, and a passage of up to 25 words as a Back-Cover Text, to the end of the list of Cover Texts in the Modified Version. Only one passage of Front-Cover Text and one of Back-Cover Text may be added by (or through arrangements made by) any one entity. If the Document already includes a cover text for the same cover, previously added by you or by arrangement made by the same entity you are acting on behalf of, you may not add another; but you may replace the old one, on explicit permission from the previous publisher that added the old one.

The author(s) and publisher(s) of the Document do not by this License give permission to use their names for publicity for or to assert or imply endorsement of any Modified Version.

5. COMBINING DOCUMENTS

You may combine the Document with other documents released under this License, under the terms defined in section 4 above for modified versions, provided that you include in the combination all of the Invariant Sections of all of the original documents, unmodified, and list them all as Invariant Sections of your combined work in its license notice, and that you preserve all their Warranty Disclaimers.

The combined work need only contain one copy of this License, and multiple identical Invariant Sections may be replaced with a single copy. If there are multiple Invariant Sections with the same name but different contents, make the title of each such section unique by adding at the end of it, in parentheses, the name of the original author or publisher of that section if known, or else a unique number. Make the same adjustment to the section titles in the list of Invariant Sections in the license notice of the combined work.

In the combination, you must combine any sections Entitled "History" in the various original documents, forming one section Entitled "History"; likewise combine any sections Entitled "Acknowledgements", and any sections Entitled "Dedications". You must delete all sections Entitled "Endorsements."

6. COLLECTIONS OF DOCUMENTS

You may make a collection consisting of the Document and other documents released under this License, and replace the individual copies of this License in the various documents with a single copy that is included in the collection, provided that you follow the rules of this License for verbatim copying of each of the documents in all other respects.

You may extract a single document from such a collection, and distribute it individually under this License, provided you insert a copy of this License into the extracted document, and follow this License in all other respects regarding verbatim copying of that document.

7. AGGREGATION WITH INDEPENDENT WORKS

A compilation of the Document or its derivatives with other separate and independent documents or works, in or on a volume of a storage or distribution medium, is called an "aggregate" if the copyright resulting from the compilation is not used to limit the legal rights of the compilation's users beyond what the individual works permit. When the Document is included in an aggregate, this License does not apply to the other works in the aggregate which are not themselves derivative works of the Document.

If the Cover Text requirement of section 3 is applicable to these copies of the Document, then if the Document is less than one half of the entire aggregate, the Document's Cover Texts may be placed on covers that bracket the Document within the aggregate, or the electronic equivalent of covers if the Document is in electronic form. Otherwise they must appear on printed covers that bracket the whole aggregate.

8. TRANSLATION

Translation is considered a kind of modification, so you may distribute translations of the Document under the terms of section 4. Replacing Invariant Sections with translations requires special permission from their copyright holders, but you may include translations of some or all Invariant Sections in addition to the original versions of these Invariant Sections. You may include a translation of this License, and all the license notices in the Document, and any Warranty Disclaimers, provided that you also include the original English version of this License and the original versions of those notices and disclaimers. In case of a disagreement between the translation and the original version of this License or a notice or disclaimer, the original version will prevail.

If a section in the Document is Entitled "Acknowledgements", "Dedications", or "History", the requirement (section 4) to Preserve its Title (section 1) will typically require changing the actual title.

9. TERMINATION

You may not copy, modify, sublicense, or distribute the Document except as expressly provided for under this License. Any other attempt to copy, modify, sublicense or distribute the Document is void, and will automatically terminate your rights under this License. However, parties who have received copies, or rights, from you under this License will not have their licenses terminated so long as such parties remain in full compliance.

10. FUTURE REVISIONS OF THIS LICENSE

The Free Software Foundation may publish new, revised versions of the GNU Free Documentation License from time to time. Such new versions will be similar in spirit to the present version, but may differ in detail to address new problems or concerns. See http://www.gnu.org/copyleft/.

Each version of the License is given a distinguishing version number. If the Document specifies that a particular numbered version of this License "or any later version" applies to it, you have the option of following the terms and conditions either of that specified version or of any later version that has been published (not as a draft) by the Free Software Foundation. If the Document does not specify a version number of this License, you may choose any version ever published (not as a draft) by the Free Software Foundation.