Nota

El servidor MySQL es mysqld. A él se pueden conectar múlti- ples clientes. mysql es el cliente en modo texto que proporcio- na el propio SGBD.

El servidor MySQL es el servicio mysqld, que puede recibir solicitudes de clientes locales o remotos a través TCP/IP, sockets o pipes en forma de ficheros locales a la máquina en que se está ejecutando. En la distribución se incluye un cliente llamado mysql-client, al que en adelante nos referiremos simplemente como mysql (así es como se llama el programa ejecutable). Si se invoca sin parámetros, mysql realiza una conexión al servidor local utilizando el nombre del usuario UNIX que lo ha invocado, y supone que este usuario no requiere contraseña. La conexión a un servidor remoto y un nombre de usuario específicos requiere de al menos dos argumentos:

•    -h para especificar el nombre del servidor.
•    -u para el nombre del usuario.

Para que el programa cliente pregunte la contraseña de conexión al usuario, deberemos proporcionar adicionalmente el parámetro -p.

                        

Nota

Los usuarios del sistema operativo y los de MySQL no son los mismos, aunque el administrador de MySQL (con fines prácticos) pueda utilizar los mismos nombres para las uentas de los usuarios MySQL.

Si se tiene algún problema para realizar la conexión, es necesario consultar con el administrador del sistema, que nos proporcionará un nombre de usuario, contraseña y el nombre del servidor, según sea necesario, y nos informará de las restricciones que tiene nuestra cuenta.

La administración y seguridad de MySQL está diseñada sobre un esquema de usuarios y privilegios. Los usuarios deben ser creados por el administrador con sus respectivos privilegios y restricciones. Es el administrador quien decide si los nombres de los usuarios de MySQL se corresponden o no a los del sistema operativo.

Apariencia de mysql al ingresar en el modo interactivo:

Con el comando help obtenemos una serie de opciones (veremos las más utilizadas).

Para salir del cliente podemos escribir ’\q’ o ‘quit’:

Tanto para el comando quit como para el comando help, el punto y coma al final es opcional.

A continuación presentamos una ejecución de la sentencia select con cuatro columnas de datos:

 

              

En esta consulta se solicita, a través de funciones incorporadas en el SGBD, el nombre del usuario actual de MySQL, el número de conexión al servidor, la versión del servidor y la base de datos en uso. Las funciones se reconocen por los paréntesis al final. mysql entrega sus resultados en tablas, en la que el primer renglón son los encabezados de las columnas. Es importante no dejar espacio entre el nombre de una función y los paréntesis, de otro modo, mysql marcará un mensaje de error.

La última línea entregada por mysql informa sobre el número de filas encontrado como resultado de la consulta y el tiempo estimado que llevó su realización. Esta medida de tiempo no se debe considerar muy precisa para medir el rendimiento del servidor, se trata simplemente de un valor aproximado que puede verse alterado por múltiples factores.

Observamos que la columna con el nombre de la base de datos actual esta vacía. Esto es natural, ya que no hemos creado aún ninguna base de datos ni le hemos indicado al gestor sobre cuál queremos trabajar.

 

Los comandos pueden expandirse en varias líneas por comodidad, sobre todo al escribir largas sentencias SQL. El cliente no enviará la sentencia SQL al servidor hasta encontrar el punto y coma, de este modo, el comando anterior puede escribirse así:

Obsérvese el indicador de mysql que se transforma en ->, signo que significa que el comando aún no está completo. También pueden escribirse varios comandos en una sola línea, cada uno debe llevar su respectivo punto y coma:

Se ejecutarán en el orden que están escritos. Los comandos se pueden cancelar con la combinación \c, con lo que el cliente nos volverá a mostrar el indicador para que escribamos de nuevo la sentencia.

         
       

Indicadores de mysql

Indicador	Significado
mysql>	Espera una nueva sentencia
->	La sentencia aún no se ha terminado con ;
“>	Una cadena en comillas dobles no se ha cerrado
'>	Una cadena en comillas simples no se ha cerrado

Las cadenas de caracteres pueden delimitarse mediante comillas dobles o simples. Evidentemente, deben cerrarse con el mismo delimitador con el que se han abierto.

y pueden escribirse en diversas líneas:

Al principio, es común olvidar el punto y coma al introducir un comando y, también, olvidar cerrar las comillas. Si éste es el caso, hay que recordar que mysql no interpreta lo que está entre comillas, de tal modo que para utilizar el comando de cancelación ’\c’ es preciso antes cerrar las comillas abiertas:

MySQL  dispone  de  variables  de  sesión,  visibles  únicamente  durante  la  conexión actual. Éstas pueden almacenar valores de tipos enteros, flotantes o cadenas, pero no tablas. Se definen como en el siguiente ejemplo:

 

La variable local @x tiene ahora el valor 1 y puede utilizarse en expresiones:

  

Las variables locales permiten almacenar datos entre consultas y, en la práctica, es recomendable utilizarlas exclusivamente con este fin, por ejemplo:

Hay que tener cuidado con el uso de las variables locales por los motivos siguientes:

Nota

El comando do evalúa expresiones sin mostrar los resultados en pantalla. Se puede evaluar cualquier expresión que admite el comando select.

•    Se evalúan en el servidor al ser enviadas por el cliente.
•    Se realizan conversiones de tipo implícitas.

 

 

 

Las  variables  no  requieren  declaración  y,  por  omisión,  contienen  el  valor NULL que significa “ausencia de valor”, observad en la siguiente consulta los resultados de utilizar valores nulos:

Nota

En una expresión donde cualquiera de sus elementos sea NULL, automáticamente entregará como resultado el valor NULL.

 

La razón de este comportamiento es que no es posible realizar ninguna operación cuando se desconoce algún valor. La entrada de valores NULL siempre significará salida de valores NULL.

Los tipos de datos numéricos comprenden dos categorías, los enteros y los números con punto flotante.

Números enteros

La principal diferencia entre cada uno de los tipos de enteros es su tamaño, que va desde 1 byte de almacenamiento hasta los 8 bytes. Las columnas de tipo en- tero pueden recibir dos atributos adicionales, que deben especificarse inmediatamente después del nombre del tipo:

•    unsigned. Indica que el entero no podrá almacenar valores negativos. Es responsabilidad del usuario verificar, en este caso, que los resultados de las restas no sean negativos, porque MySQL los convierte en positivos.

•    zerofill. Indica que la columna, al ser mostrada, rellenará con ceros a la iz- quierda los espacios vacíos. Esto de acuerdo al valor especificado por M en la declaración del tipo. Una columna con el atributo zerofill es al mismo tiempo unsigned aunque no se especifique.

 

Ejemplo

El comando anterior crea una tabla con dos columnas. Ambas ocuparán un espacio de 4 bytes, pero al mostrarse, la columna x ocupará un espacio de 4 dígitos y la columna y, de 5.

 

Tanto zerofill como unsigned deben escribirse siempre antes que cualquier otro atributo de columna.

 

Tipos enteros

Tipo	Espacio de almacenamiento	Significado
tinyint[(M)]	1 byte	Entero muy pequeño
smallint[(M)]	2 bytes	Entero pequeño
mediumint[(M)]	3 bytes	Entero mediano
int[(M)]	4 bytes	Entero
bigint[(M)]	8 bytes	Entero grande

Números con punto flotante

MySQL cuenta con los tipos float y double, de 4 y 8 bytes de almacenamiento. Además incluye el tipo decimal, que se almacena como una cadena de carac- teres y no en formato binario.

 

Números de punto flotante

Tipo	Espacio de almacenamiento	Significado
float	4 bytes	Simple precisión
double	8 bytes	Doble precisión
decimal	M + 2 bytes	Cadena de caracteres representando un número flotante

Cadenas de caracteres

Tipo	Equivalente	Tamaño máximo	Espacio de almacenamiento
char[(M)]		M bytes	M bytes
varchar[(M)]		M bytes	L+1 bytes
tinytext	tinyblob	28-1 bytes	L+1 bytes
text	blob	216-1 bytes	L+2 bytes
mediumtext	mediumblob	224-1 bytes	L+3 bytes
longtext	longblob	232 -1 bytes	L+4 bytes
enum('v1','v2',...)		65535 valores	1 o 2 bytes
set('v1','v2',...)		64 valores	1 a 8 bytes

Si observamos la tabla, vemos que el único tipo de dato que siempre utiliza el tamaño especificado por M es el tipo char. Por este motivo, se ofrece el tipo varchar que ocupa sólo el espacio requerido por el valor de la columna.

Ejemplo

La columna comentario ocupará 250 bytes de espacio de almacenamiento, sin importar el valor almacenado. Por el contrario, la columna recado ocupará sólo el espacio necesario según el valor asignado; por ejemplo, la cadena “Instalar MySQL” tiene 14 bytes de longitud, y el campo recado ocuparía 15 bytes para almacenarla.

Los tipos text y blob son equivalentes, pero text respeta las mayúsculas, mi- núsculas y caracteres acentuados en la ordenación.

Ejemplo del uso de los tipos enumerados o enum

La columna edo_civil de la tabla en la sentencia anterior, solo podrá almacenar los valores ’soltero’, ’casado’, ’viudo’, ’divorciado’, que son especificados por el tipo enum. La columna ocupará el espacio de un byte, ya que los valores enum son representados internamente por números.

Alterar la estructura de una tabla de MySQL es una tarea más frecuente de lo que uno puede imaginar en un principio. La  sentencia alter table permite una amplia gama de formas de modificar una tabla. La siguiente sentencia nos recuerda un poco a la estructura de la sentencia create table, en donde modificamos la tabla personal creada en la sección anterior.

Nota

Siempre es posible consultar la estructura de una tabla con el comando describe tabla.

 

 

 

 

mysql> alter table personal add (
-> mascota char(30) default ’perro’,
-> pasatiempo char (20) not null
-> );

Después de ejecutar la sentencia anterior, aparecen dos nuevas columnas en la tabla. Si queremos agregar una sola columna, podemos usar la sintaxis siguiente:

mysql> alter table personal add capital int not null
-> after nom;

 

Este formato de alter  table permite, además, insertar las columnas antes (before) o después (after) de una columna en cuestión.

Las columnas no deseadas pueden eliminarse con la opción drop.

mysql> alter table personal drop pasatiempo;

La modificación de una columna con la opción modify es parecida a volver a definirla.

Después de la sentencia anterior, los atributos y tipo de la columna han cambiado por los especificados. Lo que no se puede cambiar con esta sintaxis es el nombre de la columna. Para ello, se debe utilizar la opción change:

m

La columna que se llamaba nom cambia a nombre.

Nota

En general, una tabla no puede durar mucho tiempo con un order respecto a una columna, ya que las inserciones no se realizarán respetando el orden establecido. Solamente en tablas que no van a ser actualizadas es útil aplicar este comando.

Con el mismo comando alter table podemos incluso realizar la ordenación física de una tabla bajo una columna específica:

 

 

 

 

 

Finalmente, podemos cambiar de nombre la

mysql> alter table personal rename gente;

 

 

rename table

El comando rename table viejo_nombre to nuevo_nombre es una forma alternativa de cambiar el nombre a una tabla.

 

 

 

 

Aunque no existe un comando explícito para copiar tablas de una base de datos a otra, es posible utilizar el comando rename table para este propósito; basta con especificar la base de datos a la que pertenece una tabla:

También es posible crear una tabla nueva con el contenido de otra ya existente (copiando los datos):

La siguiente sentencia es equivalente, pero no copia los datos de la tabla origen:

MySQL permite la creación de tablas temporales, visibles exclusivamente en la sesión abierta, y guardar datos entre consultas. La creación de una tabla temporal sólo requiere la utilización de la palabra temporary en cualquier formato del comando create table. La utilidad de las tablas temporales se limita a consultas complejas que deben generar resultados intermedios que debemos consultar (hacer 'join' con ellas) varias veces o en consultas separadas. Internamente, MySQL genera también tablas temporales para resolver determinadas consultas:

Las funciones auxiliares que podemos utilizar en nuestras consultas (tanto en la proyección de las columnas como en condiciones en su selección) se pueden clasificar según el tipo de datos con el que trabajan.

Ejemplo

<img src="/files/uploads/32image/funcuones_auxiliares1.png" alt="" />

Algunos ejemplos de las funciones más usadas:

Operadores lógicos

Comparación. Aparte de los estándares =, !=, <, >, IS NULL, IS NOT NULL, BETWEN, IN, destacan COALESCE, INTERVAL, LEAST, GREATEST para trabajar con listas de valores.

Control del flujo de ejecución

•    CASE .. WHEN .. THEN .. ELSE .. END: Similar a la estructura que crearíamos mediante cualquier lenguaje de programación:

•    IF(expr1,expr2,expr3): Típica estructura condicional, si la expr1 es cierta, devuelve la expr2, en caso contrario, la expr3:

Funciones para trabajar con cadenas de caracteres (sólo algunos ejemplos)

•    CONCAT, INSTR (encontrar en una cadena), SUBSTRING, LCASE/RCASE, LENGTH, REPLACE, TRIM, entre otras, son funciones similares a las que podemos encontrar en lenguajes de programación para manipular cadenas de caracteres.

•    QUOTE: delimita una cadena de texto correctamente para evitar problemas al usarla en sentencias SQL. La cadena resultante estará delimitada por comillas simples. Las comillas, el valor ASCII NUL y otros potencialmente conflictivos serán devueltos precedidos del carácter '\'.

•    ENCODE/DECODE,  CRYPT,  COMPRESS/UNCOMPRESS,  MD5,  etc.  son funciones que nos pueden ayudar mucho en el almacenamiento de datos sensibles como contraseñas, etc.

Funciones numéricas

•    Los operadores aritméticos clásicos para realizar todo tipo de operaciones, suma, resta, división, producto, división entera, etc.

•    Funciones matemáticas de todo tipo, trigonométricas, logarítmicas, etc.

Funciones para trabajar con fechas y horas

•    Obtención de fechas en cualquier formato: DATE_FORMAT, DATE, NOW, CURRDATE, etc.

•    Manipulación y cálculos con fechas: ADDDATE, ADDTIME, CONVERT_TZ, DATE_DIFF, etc.

MySQL nos ofrece también facilidades a la hora de evaluar las sentencias SQL, gracias a la sentencia EXPLAIN.

Presentamos primero la ejecución de una sentencia SQL más o menos compleja:

<img src="/files/uploads/32image/explain1.png" alt="" />

Ahora utilizamos la sentencia EXPLAIN para que MySQL nos explique cómo ha realizado esta consulta:

mysql> explain select productos.clave, concat(parte,' ',tipo,' ', especificación) as producto, proveedores.empresa , precio , pago from productos natural join precios natural join proveedores;

En cada fila del resultado, nos explica cómo ha utilizado los índices de cada tabla involucrada en la consulta. La columna 'type' nos indica el tipo de “join” que ha podido hacer. En nuestro caso, 'eq_ref', 'ref' o 'ref_or_null' indica que se ha consultado una fila de esta tabla para cada combinación de filas de las otras. Es una buena señal, se están utilizando los índices, tal como indican el resto de columnas (en concreto el atributo 'clave' que es su clave primaria).

Vemos que en las otras dos tablas, el tipo de 'join' es ALL, esto indica que el gestor ha tenido que leer toda la tabla para comprobar las condiciones que le hemos exigido en la consulta. En el caso de la tabla proveedores, habría podido utilizar la clave primaria ('possible_keys'), pero no lo ha hecho.

Vamos a intentar mejorar esta consulta. Vemos que en la tabla precios no se ha definido ningún índice, lo que facilitaría la labor al SGBD:

Veremos más sobre los índices en el subapartado 5.5 “Análisis y optimización” de esta unidad didáctica.

Las cosas han cambiado sustancialmente. El gestor ha pasado de leer 24 filas de datos, a leer 11. También ha cambiado el orden de lectura de las tablas, haciendo primero una lectura total de la tabla proveedores (que es inevitable ya que no hemos puesto ninguna condición en el SELECT) y, después, ha aprovechado los índices definidos en 'precios' y en 'productos'.

La sintaxis simplificada de grant consta de tres secciones. No puede omitirse ninguna, y es importante el orden de las mismas:

•    grant lista de privilegios
•    on base de datos.tabla
•    to usuario

Ejemplo

Creación de un nuevo usuario al que se otorga algunos privilegios

En la primera línea se especifican los privilegios que serán otorgados, en este caso se permite actualizar (update), insertar (insert) y consultar (select). La segunda línea especifica que los privilegios se aplican a la tabla precios de la base de datos demo. En la última línea se encuentra el nombre del usuario y el equipo desde el que se va a permitir la conexión.

El comando grant crea la cuenta si no existe y, si existe, agrega los privilegios especificados. Es posible asignar una contraseña a la cuenta al mismo tiempo que se crea y se le otorgan privilegios:

En la misma sentencia es posible también otorgar permisos a más de un usuario y asignarles, o no, contraseña:

MySQL proporciona mecanismos para permitir que el usuario realice su conexión desde diferentes equipos dentro de una red específica, sólo desde un equipo, o únicamente desde el propio servidor.

El carácter % se utiliza de la misma forma que en el comando like: sustituye a cualquier cadena de caracteres. En este caso, se permitiría el acceso del usuario 'visitante' (con contraseña, si la tuviese definida) desde cualquier equipo del dominio 'empresa.com'. Obsérvese que es necesario entrecomillar el nombre del equipo origen con el fin de que sea aceptado por MySQL. Al igual que en like, puede utilizarse el carácter ’_’.

Entonces, para permitir la entrada desde cualquier equipo en Internet, escribiríamos:

Obtendríamos el mismo resultado omitiendo el nombre del equipo origen y escribiendo simplemente el nombre del usuario:

Los anfitriones válidos también se pueden especificar con sus direcciones IP.

Los caracteres ’ %’ y ’_’ no se permiten en los nombres de los usuarios.

Después de analizar las opciones referentes a los lugares de conexión permitidos, veamos ahora cómo podemos limitar los privilegios a bases de datos, tablas y columnas.

En el siguiente ejemplo otorgamos privilegios sobre todas las tablas de la base de datos demo.

Podemos obtener el mismo resultado de esta forma:

De igual modo, al especificar sólo el nombre de una tabla se interpretará quepertenece a la base de datos en uso:

 

Opciones para la clausula on del comando grant

Opción	Significado
*.*	Todas las bases de datos y todas las tablas
base.*	Todas las tablas de la base de datos especificada
tabla	Tabla especificada de la base de datos en uso
*	Todas las tablas de la base de datos en uso

 

A continuación presentamos un ejemplo donde se especifican las columnas sobre las que se otorgan privilegios con el comando grant:

Podemos especificar privilegios diferentes para cada columna o grupos de columnas:

MySQL proporciona una gran variedad de tipos de privilegios.

•    Privilegios relacionados con tablas: alter, create, delete, drop, index, insert, select, update

•    Algunos privilegios administrativos: file, proccess, super reload, replication client, grant option, shutdown

•    Algunos privilegios para fines diversos: lock tables, show databases, create temporary tables.

El privilegio all otorga todos los privilegios exceptuando el privilegio grant option. Y el privilegio usage no otorga ninguno, lo cual es útil cuando se desea, por ejemplo, simplemente cambiar la contraseña:

 

 

Tipos de privilegios en MySQL

Tipo de privilegio	Operación que permite
all [privileges]	Otorga todos los privilegios excepto grant option
usage	No otorga ningún privilegio
alter	Privilegio para alterar la estructura de una tabla
create	Permite el uso de create table
delete	Permite el uso de delete
drop	Permite el uso de drop table
index	Permite el uso de index y drop index
insert	Permite el uso de insert
select	Permite el uso de select
update	Permite el uso de update
file	Permite le uso de select . . . into outfile y load data infile
process	Permite el uso de show full procces list
super	Permite la ejecución de comandos de supervisión
reload	Permite el uso de flush
replication client	Permite preguntar la localización de maestro y esclavo
replication slave	Permite leer los binlog del maestro
grant option	Permite el uso de grant y revoke
shutdown	Permite dar de baja al servidor
lock tables	Permite el uso de lock tables
show tables	Permite el uso de show tables
create temporary tables	Permite el uso de create temporary table

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

En entornos grandes, es frecuente encontrarse en la necesidad de delegar el trabajo de administrar un servidor de bases de datos para que otros usuarios, además del administrador, puedan responsabilizarse de otorgar privilegios sobre una base de datos particular. Esto se puede hacer en MySQL con el privilegio grant option:

El mismo resultado se puede obtener con la siguiente sintaxis alternativa:

 

De este modo el usuario operador podrá disponer de todos los privilegios sobre la base de datos demo, incluido el de controlar el acceso a otros usuarios.

* Secure Sockets Layer

MySQL puede establecer conexiones seguras encriptándolas mediante el protocolo SSL*; de esta manera, los datos que se transmiten (tanto la consulta, en un sentido, como el resultado, en el otro) entre el cliente y el servidor estarán protegidos contra intrusos. Para especificar que un usuario debe conectarse obligatoriamente con este protocolo, se utiliza la cláusula require:

Las conexiones encriptadas ofrecen protección contra el robo de información, pero suponen una carga adicional para el servicio, que debe desencriptar la petición del cliente y encriptar la respuesta (además de un proceso más largo de negociación al conectar), por ello, merman el rendimiento del SGBD.

s

 

Los recursos físicos del servidor siempre son limitados: si se conectan muchos usuarios al mismo tiempo al servidor y realizan consultas o manipulaciones de datos complejas, es probable que pueda decaer el rendimiento notablemente. Una posible solución a este problema es limitar a los usuarios el trabajo que pueden pedir al servidor con tres parámetros:

•    Máximo número de conexiones por hora.

•    Máximo número de consultas por hora.

•    Máximo número de actualizaciones por hora.

La sintaxis de estas limitaciones es como se muestra a continuación:

El comando  revoke  permite eliminar privilegios otorgados con  grant a los usuarios. Veamos un ejemplo representativo:

Al ejecutar este comando se le retiran al usuario visitante todos sus privilegios sobre todas las bases de datos, cuando se conecta desde localhost.

El comando anterior no retira todos los privilegios del usuario visitante, sólo se los retira cuando se conecta desde localhost. Si el usuario se conecta desde otra localidad (y tenía permiso para hacerlo) sus privilegios permanecen intactos.

Antes de proceder a la eliminación de un usuario, es necesario asegurarse de que se le han quitado primero todos sus privilegios. Una vez asegurado este detalle, se procede a eliminarlo mediante el comando drop user:

MySQL almacena la información sobre los usuarios y sus privilegios en una base de datos como cualquier otra, cuyo nombre es mysql. Si exploramos su estructura, entenderemos la manera como MySQL almacena la información de sus usuarios y privilegios:

Es posible realizar modificaciones directamente sobre estas tablas y obtener los mismos resultados que si utilizáramos los comandos grant, revoke, set password o drop user:

El comando flush privileges solicita a MySQL que vuelva a leer las tablas de privilegios. En el momento de ejecutarse, el servidor lee la información de estas tablas sobre privilegios. Pero si se han alterado las tablas manualmente, no se enterará de los cambios hasta que utilicemos el comando flush privileges.

 

 

Tablas de la base de datos mysql

Tabla	Contenido
user	Cuentas de usuario y sus privilegios globales
db	Privilegios sobres bases de datos
tables_priv	Privilegios sobre tablas
columns_priv	Privilegios sobre columnas
host	Privilegios de otros equipos anfitriones sobre bases de datos

El acceso directo a las tablas de privilegios es útil en varios casos; por ejemplo, para borrar un usuario del sistema en las versiones de MySQL anteriores a la 4.1.1:

No es posible eliminar mediante un solo comando revoke todos los privilegios de un usuario.

Ejemplo

Se otorgan derechos a un usuario con dos comandos grant.

Observando el contenido de la base de datos de privilegios, podemos entender el comportamiento de los comandos grant y revoke. Primero asignamos privilegios para usar el comando select al usuario visitante con dos comandos grant: el primero de ellos le permite el ingreso desde el servidor nuestra-ong.org y el segundo le otorga el mismo tipo de privilegio, pero desde cualquier equipo en Internet.

Consultando la tabla user de la base de datos de privilegios, podemos observar los valores ’Y’ en la columna del privilegio select.

Ahora solicitamos eliminar el privilegio select de todas las bases de datos y de todos los equipos en Internet.

En la tabla user observamos que, efectivamente, se ha eliminado el privilegio para visitante@’%’ pero no para 'visitante@nuestra-ong.org'. MySQL considera que son direcciones diferentes y respeta los privilegios otorgados a uno cuando se modifica otro.

Un mecanismo que permite realizar la copia de los archivos del servidor sin necesidad de detener el servicio es el script 'mysqlhotcopy'. El script está escrito en Perl y bloquea las tablas mientras realiza el respaldo para evitar su modificación. Se usa de la siguiente manera:

En este caso, creará un directorio /algun_directorio/demo con todos los archivos de la base de datos.

El comando mysqlhotcopy puede recibir sólo el nombre de una base de datos como parámetro:

En este caso, creará un directorio /var/lib/mysql/demo_copy.

Este método no funciona para tablas con el mecanismo de almacenamiento tipo InnoDB.

Las dos opciones anteriores representan copias binarias de la base de datos. El comando mysqldump, en cambio, realiza un volcado de las bases de datos pero traduciéndolas a SQL; es decir, entrega un archivo de texto con todos los comandos necesarios para volver a reconstruir las bases de datos, sus tablas y sus datos. Es el método más útil para copiar o distribuir una base de datos que deberá almacenarse en otros servidores.

El comando mysqldump ofrece multitud de parámetros para modificar su comportamiento  o  el  tipo  de  volcado  generado:  por  defecto,  genera  sentencias SQL, pero puede generar ficheros de datos tipo CSV u otros formatos. También podemos especificarle que haga el volcado de todas las bases de datos o que sólo vuelque los datos y no la creación de las tablas, etc.

Las primeras líneas del archivo demo.sql según el ejemplo anterior tendrían el siguiente aspecto:

La ventaja de utilizar mysqldump es que permite que los archivos puedan ser leídos (y modificados) en un simple editor de textos, y pueden ser utilizados para migrar la información a otro SGBD que soporte SQL. Además soporta todos los tipos de tablas. La desventaja es que su procesamiento es lento y los archivos que se obtienen son muy grandes.

En algún momento, sea por el motivo que sea, necesitaremos realizar la restauración de nuestras bases de datos.

Si tenemos una copia binaria del directorio de datos, bastará con copiarla al directorio original y reiniciar el servidor:

 

Es importante restaurar también el dueño y el grupo de los archivos de datos, para tener los accesos correctamente establecidos. En este ejemplo se adopta el supuesto que el usuario mysql es el que ejecuta el servidor mysqld.

La restauración de un archivo SQL obtenido con mysqldump, se realiza desde el cliente mysql, la base de datos debe existir, ya que el archivo demo.sql no la crea por defecto.

El programa myisamchk es una utilidad en línea de comandos que se incluye con la distribución de MySQL y sirve para reparar tablas tipo MyISAM. Para utilizarlo con seguridad el servidor no debe estar ejecutándose y se recomienda realizar un respaldo del directorio de datos antes de su utilización.

Recibe como parámetro principal los archivos .MYI correspondientes a las tablas que hay que revisar; es decir, myisamchk no conoce la ubicación del directorio de datos. Por ejemplo, si el directorio de datos está ubicado en /val/ lib/mysql, las siguientes serían dos maneras de realizar una comprobación de los archivos de la base de datos demo:

Se pueden revisar todas las bases de datos utilizando ’*’ para denominar el directorio de la base de datos:

Para realizar una comprobación rápida, el manual sugiere utilizar el siguiente comando:

Y para realizar la corrección de las tablas corruptas, el manual sugiere la sintaxis siguiente:

Nota

En la práctica con estas opciones se logran corregir los errores más comunes. Para conocer otras opciones de recuperación con myisamchk, podéis consultar el manual que acompaña a la distribu- ción de MySQL.

 

 

 

 

 

 

Las opciones dadas por -O se refieren al uso de memoria, que permiten acelerar de forma notoria el proceso de reparación.

 

--force reinicia myisamchk con el parámetro  --recover cuando encuentra algún error.

--updatestate almacena información sobre el resultado del análisis en la tabla MYI.

La indexación es la principal herramienta para optimizar el rendimiento general de cualquier base de datos. Es también la más conocida por los usuarios de servidores MySQL y, paradójicamente, su no utilización es una de las principales causas de bajo rendimiento en servidores de bases de datos.

Muchos administradores y diseñadores simplemente parecen olvidar usar índices para optimizar los accesos a las bases de datos. Por otro lado, algunas personas tienden a indexar todo, esperando que de esta manera el servidor acelere cualquier tipo de consulta que se le solicite. En realidad, esta práctica puede causar una disminución en el rendimiento, sobre todo en lo que respecta a inserciones y modificaciones.

Para ver las ventajas de utilizar índices, analizaremos en primer término una simple búsqueda en una tabla sin índice alguno:

•    El constante acceso de escritura de una tabla la mantiene desordenada.

•    La ordenación de una tabla es una operación costosa: el servidor tendría que detenerse un tiempo considerable para ordenar sus tablas.

•    Muchas tablas tienen más de un criterio de ordenación: ordenar según una columna implica desordenar otra.

•    La inserción y eliminación de datos sin alterar el orden en una tabla es costosa: la inserción de un registro en una tabla grande implicaría una larga espera en la actualización de la misma.

•    Si se opta por mantener la tabla desordenada (que es la opción más viable), una búsqueda implicaría forzosamente un recorrido secuencial (también denominado full scan), registro por registro.

El uso de índices en la ordenación de las bases de datos ofrece las ventajas siguientes:

•    Permite ordenar las tablas por varios criterios simultáneamente.

•    Es menos costoso ordenar un archivo índice, porque incluye sólo referencias a la información y no la información en sí.

•    El coste de inserción y eliminación es menor.

•    Con los registros siempre ordenados se utilizaran algoritmos mucho más eficientes que el simple recorrido secuencial en las consultas.

El uso de índices también comporta alguna desventaja:

•    Los índices ocupan espacio en disco.

•    Aún teniendo registros pequeños, el mantener en orden un índice disminuye la velocidad de las operaciones de escritura sobre la tabla.

A pesar de estos inconvenientes, la utilización de índices ofrece mayores ventajas que desventajas, sobre todo en la consulta de múltiples tablas, y el aumento de rendimiento es mayor cuanto mayor es la tabla.

Consideremos por ejemplo una consulta sobre las tablas A, B, y C, independientemente del contenido de la cláusula where, las tres tablas se deben de combinar para hacer posible posteriormente el filtrado según las condiciones dadas:

Consideremos que no son tablas grandes, que no sobrepasan los 1.000 registros. Si A tiene 500 registros, B tiene 600 y C 700, la tabla resultante de la consulta  anterior  tendrá  210  millones  de  registros.  MySQL  haría  el  producto cartesiano de las tres tablas y, posteriormente, se recorrería la relación resultante para buscar los registros que satisfacen las condiciones dadas, aunque al final el resultado incluya solamente 1.000 registros.

Si utilizamos índices MySQL los utilizaría de una forma parecida a la siguiente:

•    Tomaría cada uno de los registros de A.

•    Por cada registro de A, buscaría los registros en B que cumpliesen con la condición A.a = B.b. Como B está indexado por el atributo 'b', no necesitaría hacer el recorrido de todos los registros, simplemente accedería directamente al registro que cumpliese la condición.

•    Por cada registro de A y B encontrado en el paso anterior, buscaría los registros de C que cumpliesen la condición B.b = C.c. Es el mismo caso que en el paso anterior.

Comparando las dos alternativas de búsqueda, la segunda ocuparía cerca del 0,000005% del tiempo original. Por supuesto que sólo se trata de una aproximación teórica, pero adecuada para comprender el efecto de los índices en las consultas sobre bases de datos.

El índice ideal debería tener las siguientes características:

•    Los registros deberían ser lo más pequeños posible.
•    Sólo se debe indexar valores únicos.

Analicemos cada recomendación:
•    Cuanto más pequeños sean los registros, más rápidamente se podrán cambiar de lugar (al insertar, modificar o borrar filas), además, en un momento dado, el índice puede permanecer en memoria. Consideremos las dos definiciones posibles:

En esta tabla el índice se realiza sobre nombre, que es un campo de 30 caracteres, y se utiliza como clave para hacer los 'joins' con otras tablas.

 

Ahora considérese la siguiente alternativa:

Se agrega una columna que servirá como identificador de la empresa. Desde el punto de vista de rendimiento implica una mejora, ya que el índice se realiza sobre números enteros, por lo tanto, ocupará menos espacio y funcionará más rápido.

•    Consideremos el índice siguiente, creado para disminuir la necesidad de efectuar accesos a la tabla:

Si consideramos que un índice se crea para evitar la necesidad de recorrer la tabla, veremos que el índice creado es prácticamente inútil, ya que alguno de los valores ocurre el 50% o más de las veces: para encontrar todos los resultados hay que recorrer gran parte de la tabla. MySQL no utiliza los índices que implican un 30% de ocurrencias en una tabla.

Aun así, y exceptuando casos exagerados como este último, puede ser interesante indexar una tabla por algún atributo que no sea único, si ese atributo se utiliza para ordenar los resultados. También puede ser conveniente crear un índice por varios atributos simultáneamente si se usan todos en alguna consulta en la cláusula ORDER BY.

* Memoria intermedia de acceso rápido.

El servidor MySQL incluye la posibilidad de utilizar una cache* con los resultados de las últimas consultas para acelerar la velocidad de respuesta. Esta solución  es  útil  cuando  las  tablas  tienen  relativamente  pocos  cambios  y  se realizan los mismos tipos de consultas. El funcionamiento de la cache se basa en las premisas siguientes:

•    La primera vez que se recibe una consulta se almacena en la cache.

•    Las siguientes veces la consulta se realiza primero en la cache; si tiene éxito, el resultado se envía inmediatamente.

La cache tiene las siguientes características:

•    El servidor compara el texto de la consulta; aunque técnicamente sea igual si difiere en uso de mayúsculas-minúsculas o cualquier otro cambio, no se considera la solicitud idéntica y no será tratada por la cache.

•    Si alguna tabla incluida en alguna consulta cambia, el contenido de la consulta es eliminado de la cache.

La configuración de la cache se realiza a través de variables globales:

•    query_cache_limit. No almacena resultados que sobrepasen dicho tamaño. Por omisión es de 1M.

•    query_cache_size. Tamaño de la memoria cache expresada en bytes. Por omisión es 0; es decir, no hay cache.

•    query_cache_type. Puede tener tres valores: ON , OFF o DEMAND 

tipos de cache

Valor	Tipo	Significado
0	OFF	Cache desactivado
1	ON	Cache activado
2	DEMAND	Sólo bajo solicitud explicita

 

 

 

 

 

Cuando la cache del servidor esta en modo DEMAND, se debe solicitar explícitamente que la consulta utilice o no la cache:

El equipo maestro debe tener acceso por red. Antes de realizar la configuración de los servidores maestro y esclavo es necesario realizar las siguientes tareas:

•    Asegurarse de que en ambos está instalada la misma versión de MySQL.

•    Asegurarse de que ninguno de los servidores atenderá peticiones durante el proceso de configuración.

•    Asegurarse de que las bases de datos del servidor maestro han sido copiadas manualmente en el servidor esclavo, de manera que en ambos se encuentre exactamente la misma información.

•    Asegurarse de que ambos atienden conexiones vía TCP/IP. Por seguridad, esta opción está desactivada por omisión. Para activarla se debe comentar la línea skip_networking en el archivo de configuración /etc/my.cnf

En el servidor maestro creamos una cuenta de usuario con permisos de replicación para autorizar, en el servidor maestro, al nuevo usuario para realizar réplicas:

Replicador es el nombre del nuevo usuario.

Esclavo.empresa.com es la dirección del servidor esclavo.

’Secreto’ es la contraseña.

 

El servidor maestro llevará un archivo de registro 'binlog' donde se registrarán todas las solicitudes de actualización que se realicen en las bases de datos. Para activar la creación de este archivo debemos editar el archivo /etc/my.cnf y agregar las siguientes líneas en la sección [mysqld]:

El servidor maestro debe identificarse con un id, en este caso será el número 1. a continuación, reiniciamos el servidor:

Finalmente, consultamos el nombre del archivo 'binlog' y la posición de compensación (estos datos son necesarios para configurar el esclavo):

 

En el servidor esclavo, editamos el archivo /etc/my.cnf y agregamos, al igual que en el maestro, la activación del archivo 'binlog' y un identificador del servidor (que debe ser distinto del identificador del servidor maestro):

Reiniciamos el servidor esclavo:

Configuramos los datos del maestro en el servidor esclavo:.

El último paso es iniciar el servidor esclavo:

Y ya tendremos el servidor esclavo funcionando.

La utilidad mysqlimport que se incluye en la distribución puede realizar el mismo trabajo que load data. Estos son algunos de sus parámetros:

Estos son algunos de los argumentos de mysqlimport para realizar las tareas equivalentes a la sentencia load data:

La forma más simple para exportar datos es redireccionando la salida del cliente mysql. El parámetro -e permite ejecutar un comando en modo de procesamiento por lotes. MySQL detecta si la salida es en pantalla o está redireccionada a un archivo y elige la presentación adecuada: con encabezados y líneas de separación para la salida en pantalla, y sin encabezados y con tabuladores para un archivo:

La sentencia select también cuenta con una opción para realizar la tarea inversa de la sentencia load data:

La utilidad mysqldump realiza el volcado de bases de datos y puede utilizarse para transportar datos de una base a otra que también entienda SQL. Sin embargo, el archivo debe ser editado antes de utilizarse, ya que algunas opciones son exclusivas de MySQL. Por lo general, basta con eliminar el tipo de tabla que se especifica al final de un comando create table.

El siguiente comando realiza el vaciado completo de la base de datos demo:

En algunos casos, los comandos insert son suficientes y no necesitamos las definiciones de las tablas.

El siguiente comando realiza un vaciado de la tabla proveedores de la base de datos demo filtrando la salida con el comando grep de UNIX que selecciona sólo las líneas que contienen la palabra INSERT. De este modo, el archivo proveedores-insert.txt contiene exclusivamente comandos insert: