MySQL/Texto completo
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.▶
| ||||||||
|
| |||||||
| ||||||||
 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):
- mysql
- mysqlCC
- mysqladmin
- mysqld
- 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:
| 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
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...