Programa Mysql Manual Del Estudiante-2012

Programa de formación :BASE DE DATOS MySQL

OBJETIVOS DEL CURSO

OBJETIVO GENERAL

Capacitar al participante para utilizar el manejador de base de datos MySql

y su entorno de administración.

OBJETIVOS ESPECÍFICOS

Al finalizar el curso de MySQL el participante estará en capacidad de:

Identificar la historia, características y modalidad de trabajo de MySQL

como servidor de bases de datos

Describir las instrucciones básicas para la administración de bases de

datos en de MySQL.

Emplear las instrucciones necesarias para procesar la información y los

registros de las tablas en una base de datos de MySQL

Utilizar la metodología de trabajo de transacciones en MySQL

Manipular métodos de acceso remoto a MySQL

Emplear algunas técnicas para mejorar el rendimiento de un servidor

MySQL

Pág. 1 de 142


Construir los pasos necesarios para realizar migraciones a MySQL desde

otras plataformas de manejo de datos

Ejecutar algunos clientes gráficos alternativos para la administración de un

servidor MySQL

Pág. 2 de 142


CONTENIDO PROGRAMÁTICO

Unidad 1: Introducción MySQL

Unidad 2: Administración básica de bases de datos MySQL.

Unidad 3: Operaciones sobre tablas.

Unidad 4: Transacciones.

Unidad 5: Acceso remoto a MySQL.

Unidad 6: Administración avanzada de MySQL.

Unidad 7: Migración a MySQL.

Pág. 3 de 142


UNIDAD 1: Introducción

Objetivo de la Unidad: Utilizar MySQL como servidor de bases de datos,

instalándolo y activando su funcionamiento.

Temas:

Definición MySQL

Historia

Descripción básica

Instalación y actualizaciones

Puesta en marcha de un servidor MySQL

Definición de MySQL

El gestor de bases de datos conocido MySQL es un sistema de gestión de

bases de datos relacional, licenciado bajo la GPL de la GNU. Su diseño multihilo

permite soportar una gran carga de forma muy eficiente. La compañía sueca

MySQL AB es la creadora de MySQL, la cual mantiene el copyright del código

fuente del servidor SQL, así como también de la marca.

Pág. 4 de 142


MySQL es un software libre, pero MySQL AB distribuye también una

versión comercial de MySQL, la cual sólo se diferencia de la versión libre en el

soporte técnico que se ofrece, y la posibilidad de integrar este gestor a un

software propietario, de lo contrario, se vulneraría la licencia GPL.

Este gestor de bases es considerado uno de los más utilizados en todo el

mundo del software libre, gracias a su gran rapidez y facilidad de uso. Esto se

debe, en parte, a que existen infinidad de librerías y otras herramientas que

permiten su uso a través de gran cantidad de lenguajes de programación,

además de su fácil instalación y configuración.

Historia

MySQL nace como un intento de conectar el gestor mSQL a las tablas

propias de MySQL AB, utilizando sus propias rutinas a bajo nivel. Después de

llevar a cabo las pruebas iniciales, se vio que mSQL no era lo suficientemente

flexible para lo que se necesitaba, por lo que se tuvo que desarrollar nuevas

funciones. Esto dio como resultado una interfaz SQL a su base de datos, con una

interfaz totalmente compatible a mSQL.

No se sabe con certeza de donde proviene su nombre; se dice, por un lado,

que sus librerías llevaron el prefijo 'my' durante los diez últimos años. Por otro

lado, la hija de uno de los desarrolladores se llama My, pero no se sabe con

exactitud cuál de estas dos razones le dio su nombre a este conocido gestor de

bases de datos.

Pág. 5 de 142


Descripción básica

Las características presentadas a continuación son implementadas

únicamente por MySQL:

Aprovecha la potencia de sistemas multiprocesador, gracias a su

implementación multihilo.

Soporta gran cantidad de tipos de datos para las columnas.

Dispone de API's en gran cantidad de lenguajes (C, C++, Java, PHP, etc.)

Cuenta con una gran portabilidad entre sistemas.

Soporta hasta 32 índices por tabla.

Gestión de usuarios y passwords, manteniendo un muy buen nivel de

seguridad en los datos.

Múltiples motores de almacenamiento (MyISAM, Merge, InnoDB, BDB,

Memory/heap, MySQL Cluster, Federated, Archive, CSV, Blackhole y Example en

5.x), permitiendo al usuario escoger la que sea más adecuada para cada tabla

de la base de datos.

Agrupación de transacciones, reuniendo múltiples transacciones de varias

conexiones para incrementar el número de transacciones por segundo.

Existen tres tipos de compilación del servidor MySQL:

Pág. 6 de 142


el tipo estándar: los binarios estándar de MySQL son los recomendados para

la mayoría de los usuarios, e incluyen el motor de almacenamiento InnoDB.

Max (No se trata de MaxDB, que es una cooperación con SAP): los binarios

incluyen características adicionales que no han sido lo bastante probadas o que

normalmente no son necesarias.

MySQL-Debug: son binarios que han sido compilados con información de

depuración extra. No debe ser usada en sistemas en producción porqué el

código de depuración puede reducir el rendimiento.

Instalación y actualización

Lo primero que se debe hacer es conseguir los paquetes necesarios en el

sitio Web oficial de MySQL: http://www.mysql.com

El archivo se encuentra comprimido con el formato TAR.GZ e indica en su

nombre la versión del MySQL que se ha descargado, por ejemplo: mysql-

3_22_22_tar.gz

Para poder realizar todo el proceso de instalación se debe tener acceso

como root a la estación Linux.

Lo primero que se debe hacer es un directorio de instalación, aunque lo

normal sería que se creara en /usr/local, /urs/src, o bien en /opt. Para el caso que

sirve de ejemplo se va a trabajar con /usr/local, aunque el proceso sería para los

otros.

Pág. 7 de 142


Luego, se descomprimen los paquetes en el directorio seleccionado para

instalar:

cd /usr/local

tar zxvf /root/instal/mysql-3.22.x.tar.gz

Se crean enlaces sencillos a código fuente

ln -s /usr/local/mysql-3.22.x /usr/local/mysql

Se compila e instala MySQL

cd /usr/local/mysql

./configure --witout-debug --prefix=/usr/local/mysql

make

make install

cp /usr/local/support-files/mysql.server /etc/rc.d/init.d/mysql

chmod 755 /etc/rc.d/init.d/mysql

Se crea la bases del datos del sistema MySQL

/usr/local/mysql/bin/mysql_install_db

Pág. 8 de 142


Se arranca el servidor MySQL

/etc/rc.d/init.d/mysql start/etc/rc.d/init.d/mysql start

Se asigna el password del administrador (root ) de MySQL

/usr/local/mysql/bin/mysqladmin -u root password "clave"

Puesta en marcha de un servidor MySQL

Primero se arranca MySQL:

./mysqld start

Mysql almacena en una tabla llamada mysql todo el sistema de permisos

de acceso al servidor, a las bases de datos y sus tablas, que como todas se

encontrará en el directorio /data, a menos que se especifique otro directorio, en

caso de que no existan se ejecuta el comando:

/usr/local/mysql/bin/mysql_install_db

Pág. 9 de 142


Todos los permisos y restricciones a los datos de nuestras bases de datos

se guardan en la base de datos mysql. La herramienta principal de MySQL es

mysqladmin, la cual, como indica su nombre, es la encargada de la

administración.

El usuario root se crea por defecto por MySQL, se puede utilizar este

usuario como administrador o crear otro, por ejemplo mysqladmi, con todos los

permisos posibles habilitados. Como el usuario root se crea sin clave de acceso,

lo primero que se debe hacer es asignarle una:

mysqladmin -u root password "miclave"

De ahora en adelante, para cualquier operación que se realice como root,

se debe especificar la clave. Hay que destacar que entre el modificador -p y la

clave no debe haber espacios.

mysqladmin -u root -pmiclave

Ahora bien, Mysql se esta preparado para crear una base de datos

mysqladmin -u root -pmiclave create mibasededatos

Pág. 10 de 142


Si se desea borrarla

mysqladmin -u root -pmiclave drop mibasededatos

La estructura de MySQL

En el directorio /bin se pueden encontrar ejemplos de script y SQL.

En el directorio /share se encuentran los mensajes de error del servidor

para los distintos idiomas. Los directorios /include y /lib contiene los archivos *.h

y las librerías necesarias, en /bin se encuentran los archivos ejecutables, y en

/data se encuentran, como subdirectorio, cada una de las bases de datos que se

hayan creado.

MySQL crea un directorio con el nombre que le hemos asignado a la base

de datos, para cada base de datos que se crea. Dentro de este directorio, por

cada tabla que se defina como MySQL se crean tres archivos: mitabla.ISD,

mitabla.ISM, mitabla.frm

El archivo con extensión ISD, contiene los datos de la tabla, el ISM contiene

la información sobre las claves y otros datos que MySQL utiliza para buscar

datos en el fichero ISD. Y el archivo frm contiene la estructura de la propia tabla.

Pág. 11 de 142


Ya que las bases de datos de MySQL son simples archivos de un directorio,

para realizar copias de seguridad, se pueden utilizar las herramientas de

compresión que habitualmente se utilizan en el sistema, y luego copiarlo a otro

lugar, o simplemente esto último.

Seguridad

Como se mencionó antes, Mysql guarda todo el sistema de permisos en

una base de datos llamada mysql, la cuál se componen de cinco tablas: host,

user, db, tables_priv, colums_priv.

La tabla user contiene la información sobre los usuarios, desde que

máquinas se puede acceder a nuestro servidor MySQL, su clave y de sus

diferentes permisos. La tabla host ofrece información sobre que máquinas

podrán tener acceso a nuestro sistema, así como a las bases de datos que

tendrán acesso y sus diferentes permisos. Finalmente, las tablas db, tables_priv,

columns_priv ofrecen un control individual sobre las bases de datos, las tablas y

las columnas (campos).

Pág. 12 de 142


CAMPO TIPO POR

DEFECTO

Host char(60)

User char(16)

Password char(16)

Select_pri

v

enum('N

','Y')

N

Insert_pri

v

enum('N

','Y')

N

Update_pr

iv

enum('N

','Y')

N

Delete_pri

v

enum('N

','Y')

N

Create_pri

v

enum('N

','Y')

N

Drop_priv enum('N

','Y')

N

Reload_pr

iv

enum('N

','Y')

N

Shutdown

_priv

enum('N

','Y')

N

Process_p

riv

enum('N

','Y')

N

File_priv enum('N

','Y')

N

Pág. 13 de 142


CAMPO TIPO POR

DEFECTO

Grant_priv enum('N

','Y')

N

Reference

s_priv

enum('N

','Y')

N

Index_priv enum('N

','Y')

N

Alter_priv enum('N

','Y')

N

Tabla 1.1 Tabla User

CAMPO TIPO POR

DEFECTO

Host char(60)

Db char(32)

Select_pri

v

enum('N

','Y')

N

Insert_priv enum('N

','Y')

N

Pág. 14 de 142


CAMPO TIPO POR

DEFECTO

Update_pr

iv

enum('N

','Y')

N

Delete_pri

v

enum('N

','Y')

N

Create_pri

v

enum('N

','Y')

N

Drop_priv enum('N

','Y')

N

Grant_priv enum('N

','Y')

N

Reference

s_priv

enum('N

','Y')

N

Index_priv enum('N

','Y')

N

Alter_priv enum('N

','Y')

N

Tabla 1.2 Tabla host

CAMPO TIPO POR

DEFECTO

Host char(60)

Pág. 15 de 142


CAMPO TIPO POR

DEFECTO

Db char(32)

User char(16)

Select_pri

v

enum('N

','Y')

N

Insert_priv enum('N

','Y')

N

Update_pr

iv

enum('N

','Y')

N

Delete_pri

v

enum('N

','Y')

N

Create_pri

v

enum('N

','Y')

N

Drop_priv enum('N

','Y')

N

Reference

s_priv

enum('N

','Y')

N

Index_priv enum('N

','Y')

N

Alter_priv enum('N

','Y')

N

Tabla 1.3 Tabla db

Pág. 16 de 142


A continuación se describen los diferentes permisos:

Select_priv Utiliza la sentencia SELECT

Insert_priv Utiliza la sentencia INSERT

Update_pri

v

Utiliza la sentencia UPDATE

Delete_pri

v

Utiliza la sentencia DELETE

Create_pri

v

Utiliza la sentencia CREATE o crear bases de

datos

Drop_priv Utiliza la sentencia DROP o eliminar bases de

datos

Reload_pri

v

Recarga el sistema mediante mysqladmin

reload

Shutdown_

priv

Permite parar el servidor mediante

mysqladminPermite parar el servidor mediante

mysqladmin shutdown

Process_pr

iv

Manejar procesos del servidor

File_priv Permite leer y escribir ficheros usando

comando como SELECT INTO OUTFILE y LOAD

DATA INFILE

Grant_priv Otorga permisos a otros usuarios

Pág. 17 de 142


Select_priv Utiliza la sentencia SELECT

Index_priv Crear o borrar índices

Alter_priv Utiliza la sentencia ALTER TABLE

Nota:

Se debe mencionar que si se dejan en blanco los campos user, host o db,

se hará referencia a cualquier usuario, servidor o base de datos. El mismo efecto

lo crea colocar el símbolo % en el campo.

Pág. 18 de 142


UNIDAD 2: Administración básica de bases de datos MySQL

Objetivo de la Unidad: Aplicar las instrucciones para administrar un

servidor MySQL de forma básica.

Temas:

Crear y administrar usuarios

Permisos de usuarios

Crear y Eliminar bases de datos

Pág. 19 de 142


Ver la información sobre el servidor y su funcionamiento

Crear y administrar usuarios

Comandos GRANT y REVOKE

Los Comandos GRANT y REVOKE son utilizados para conceder y retirar los

derechos de los usuarios de MySQL. Hay cuatro niveles de privilegio, estos

niveles son:

Global

Base de datos

Tabla

Columna

Comando GRANT

Este comando se utiliza para crear usuarios y concederle privilegios. A

continuación se presenta la sintaxis general del comando GRANT es la siguiente:

Pág. 20 de 142


GRANT privilegios (columnas)

ON elemento

TO nombre_usuario IDENTIFIED BY 'contraseña'

(whith grant option);

En el ejemplo presentado antes se puede observar que lo que se

encuentra escrito entre paréntesis son opcionales y que los paréntesis pueden o

no ser colocados. Es muy importante que al final se utilice el ";" y pulsar "intro",

ya que si se presenta algún error mysql indicará en que línea se encuentra éste.

Primero, se verá privilegios, que equivale a una lista de privilegios separados por

comas.

Comando REVOKE

Es lo contrario del comando opuesto al GRANT. Se utiliza para retirar

privilegios de un usuario.

Su sintaxis es muy similar a la sintaxis de GRANT:

REVOKE privilegios [(columnas)]

ON elemento

FROM nopmbre_de_usuario

Los privilegios concedidos con la cláusula WITH GRANT OPTION, pueden ser

revocados de la siguiente forma:

Pág. 21 de 142


REVOKE GRANT OPTION

ON elemento

FROMnombre_de_usuario

Ejemplos de GRANT y REVOKE.

Si se desea configurar un administrador, se puede escribir:

mysql > grant all

-> on *

-> to julia identified by 'Qe4w'

-> with grant option;

Es importante tener en cuenta que el punto y coma se dejará en la última

línea, de esta forma si se comete algún error nos dirá en que línea se encuentra

el mismo.

En este ejemplo se puede observar que a julia se le han otorgado todos los

privilegios sobre todas las Bases de Datos existentes, con la contraseña Qe4w.

Este tipo de privilegio sólo es aconsejable para los administradores, y no para el

usuario; por lo que conviene quitárselos por mayor seguridad.

Pág. 22 de 142


mysql > revoke all

-> on *

-> from julia;

Configuración de un usuario normal

mysql > grant usage

-> on peliculas .*

-> to julia identified by 'Qe4w';

De ahora en adelante, los privilegios serán concedidos en función de los

que se ha pedido hacer, colocando los privilegios adecuados

mysq > grant select, insert, update, delete, index, alter, create, drop

-> on peliculas .*

-> to julia;

Como ya se ha creado la contraseña al crear un usuario sin privilegios, no

hace falta colocarla, es suficiente con sólo colocar los privilegios. Pero resulta

que julia no ha respetado las normas que se le dijo y se desea quitarle

privilegios:

mysql > revoke alter, create, drop

-> on peliculas.*

-> from julia;

Pág. 23 de 142


Si en definitiva nos comunica que no quiere más ser usuario de la BD se

coloca revocar :

mysql > revoke all

-> on peliculas.*

-> from julia;

Niveles de privilegios y tipos

Existen tres tipos básicos de privilegios en MySQL

Privilegios apropiados para su concesión a los usuarios habituales.

Privilegios apropiados para su concesión a los administradores.

Dos privilegios especiales.

Para contar con más seguridad en la Bases de Datos (BD) se deben

considerar los diferentes privilegios que se le conceden a los usuarios o a los

administradores, ya que de ellos dependerá que las Base de Datos sean

hackeadas.

Se aconseja que los administradores tengan todos los privilegios, y que los

usuarios tengan el nivel más bajo de los mismos.

Si se entienden claramente estos principios de los comandos GRANT y

REVOKE; se puede entender cómo crear un usuario y que privilegios darle en

nuestra Base de Datos.

Pág. 24 de 142


Cambios de password de root con el programa mysqladmin:

[shell]# mysqladmin -u root password

nuevo_password

Ahora se agregarán algunos usuarios para ver como funciona este sistema:

Si se desean añadir usuarios, se utilizará el comando GRANT, el cual

cuenta con la sintaxis que se presenta a continuación:

GRANT priv_type [(column list)]

[, priv_tipe[(column_lis)] ...]

ON {table_name | * | *.* | db_name.*}

TO user_name [IDENTIFIED BY 'password']

[, user_name [IDENTIFIED BY 'password'] ...]

[WITH GRANT OPTION]

Esto puede parecer más difícil de lo que es en realidad, imagínese que se

desea crear un usuario llamado Pedro con todos los permisos sobre su base de

datos llamada pedro_db, y que solo pueda conectarse desde cualquier máquina

de dominio.com, para lograrlo se hace algo como lo presentado a continuación:

[shell]# mysql -p

Enter password:

mysql> GRANT insert,select,update,delete,create,drop,alter ON

Pág. 25 de 142


pedro_db.* TO pedro@"%.dominio.com" IDENTIFIED BY 'p3dr0';

Para explicar lo anterior, a un usuario llamado pedro que se conectará

desde cualquier máquina de dominio.com, cuyo password será p3dr0 se le

otorga permiso para hacer ( insert, select, update ... ) sobre cualquier tabla de

pedro_db.

Ahora, se creará otro superusuario llamado ferdy que tendrá todos los

privilegios, y que podrá conectarse desde cualquier sitio:

mysql> GRANT ALL PRIVILEGES

ON *.* TO ferdy@"%" IDENTIFIED BY 'gh4limones25tf' WITH

GRANT OPTION;

mysql> GRANT ALL PRIVILEGES ON *.* TO ferdy@localhost IDENTIFIED

BY 'gh4limones25tf' WITH GRANT OPTION;

Al realizar esto se tendrá el nuevo superusuario.

Supóngase por último que se desea borrar a Pedro, ya que los usuarios se

guardan en la base de datos mysql en la tabla user:

mysql> use mysql;

mysql> DELETE FROM user WHERE user = 'pedro';

Con esto Pedro habrá desaparecido de nuestro sistema MySQL.

Pág. 26 de 142


Y al darse cuenta que Ferdy no debería tener tantos permisos, se le

quitarán los mismos sobre la base de datos usuarios:

mysql> REVOKE ALL PRIVILEGES

ON usuarios.* FROM ferdy;

Ferdy no tendrá más permisos sobre la base de datos usuarios. A

continuación se presenta la sintaxis de REVOKE:

REVOKE priv_type [(column_list)]

[, priv_type [(column_list)] ...]

ON {table_name | * | *.* | db_name.*}

FROM user_name [, user_name ...]

Permisos de usuarios

Se deberían tomar en cuenta seriamente las siguientes sugerencias, para

convertir un sistema MySQL en seguro,:

Pág. 27 de 142


Se deben utilizar las claves para todos los usuarios MySQL. Un programa

cliente no necesariamente reconoce la identidad de la persona que lo utilizan. Es

común en las aplicaciones cliente/servidor que el usuario pueda especificar

cualquier nombre de usuario al programa cliente. Por ejemplo, cualquiera puede

utilizar el programa mysql para conectarse como cualquier otra persona,

simplemente invocándolo de la siguiente manera: mysql -u otro_usuario

nombre_bd cuando otro_usuario no tiene clave. Si todos los usuarios tienen una

clave, es mucho más difícil conectarse si se utiliza la cuenta de otro usuario.

Si se desea cambiar la clave de un usuario, se debe utilizar la sentencia SET

PASSWORD. También es posible alterar la tabla user en la base de datos mysql

de forma directa. Por ejemplo, haga lo siguiente si se desea cambiar la clave de

todas las cuentas MySQL que tienen por nombre de usuario root:

shell> mysql -u root

mysql> UPDATE mysql.user SET

Password=PASSWORD('newpwd')

-> WHERE User='root';

mysql> FLUSH PRIVILEGES;

Nunca se debe ejecutar el servidor MySQL con el usuario root de Unix. Esto

es extremadamente peligroso porque cualquier usuario con el privilegio FILE es

capaz de crear archivos como root (por ejemplo, ~root/.bashrc). Para evitar esto,

mysqld rechaza ejecutarse como root a menos que se utilice explícitamente la

opción --user=root.

Pág. 28 de 142


En cambio, mysqld puede, y debe, ser ejecutado por medio de un usuario

normal sin privilegios. Se puede crear una cuenta de Unix específica llamada

mysql para que todo sea aún más seguro. Se debe utilizar esta cuenta sólo para

administrar MySQL. Si se desea ejecutar el mysqld por medio de un usuario de

Unix diferente, agregue la opción user la cual especifica el nombre de usuario al

grupo [mysqld] del archivo de opciones /etc/my.cnf o al archivo de opciones

my.cnf que se encuentra en el directorio de datos del servidor. Por ejemplo:

[mysqld]

user=mysql

Al hacer esto se provoca que el servidor se inicie mediante el usuario

designado, ya sea ejecutado de forma manual o por medio del mysqld_safe o

mysql.server.

Al ejecutar mysqld como un usuario Unix diferente de root no significa

que se necesite cambiar el usuario root de la tabla user. Los usuarios de las

cuentas MySQL no tienen nada que ver con los usuarios de las cuentas Unix.

No permita el uso de enlaces simbólicos a tablas. Esto puede ser

desactivado con la opción --skip-symbolic-links.) Esto es muy importante si se

ejecuta mysqld como root, ya que cualquier persona que tenga acceso de

escritura al directorio de datos del servidor podría borrar cualquier archivo en el

sistema!.

Asegurarse de que el único usuario Unix con permisos de lectura o escritura

en los directorios de la base de datos sea el usuario que ejecuta mysqld.

No otorgar los privilegios PROCESS o SUPER a usuarios no-administrativos.

La salida del de mysqladmin processlist muestra el texto de cualquier

Pág. 29 de 142


sentencia que se esté ejecutando, así que cualquier usuario al que se permita

ejecutar dicho comando puede ser capaz de ver si otro usuario ejecuta una

sentencia UPDATE user SET password=PASSWORD('not_secure').

mysqld se reserva una conexión extra para usuarios que tengan el

privilegio SUPER, de esta forma un usuario root puede conectarse y comprobar

la actividad del servidor aún cuando todas las conexiones normales se

encuentren en uso.

El privilegio SUPER puede ser utilizado para cerrar conexiones de cliente,

cambiar el funcionamiento del servidor modificando el valor de variables del

sistema, y controlar servidores de replicación.

No otorgarle el privilegio FILE a usuarios no-administrativos. Cualquier

usuario que posea este privilegio puede escribir un archivo en cualquier de lugar

del sistema de archivos con los privilegios del demonio mysqld. Si se desea que

esto sea poco más seguro, los archivos generados con SELECT ... INTO OUTFILE

no sobreescriben los archivos existentes, y pueden ser escritos por cualquiera.

El privilegio FILE también puede ser utilizado para leer cualquier archivo

que sea legible por cualquiera o accesible para el usuario Unix que ejecuta el

servidor. Con este privilegio, se podría, por ejemplo, leer cualquier archivo e

insertarlo en una tabla de la base de datos. Esto podría ser utilizado, por

ejemplo, al usar LOAD DATA para cargar /etc/passwd en una tabla, la cual podría

mostrarse luego un SELECT.

Si no se confía en sus DNS, se podrían utilizar los números IP en lugar de los

nombres en las tablas de permisos (tablas grant). En cualquier caso, debería se

Pág. 30 de 142


debe ser muy cuidadoso al crear registros en las tablas de permiso utilizando

nombres que contengan caracteres comodín.

Si se desea restringir el número de conexiones permitidas para una misma

cuenta, se puede lograr al establecer la variable max_user_connections de

mysqld. La sentencia GRANT también soporta opciones de control de recursos

para limitar la extensión de uso de servidor permitido a una cuenta.

Crear/Eliminar/Modificar Bases de Datos

Una vez se tengan los usuarios definidos, y dependiendo de los permisos

asignados a cada uno de ellos, se pueden utilizar algunos comandos para la

creación, eliminación y modificación de las bases de datos existentes en el

servidor.

Crear Bases de Datos

CREATE DATABASE

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification [, create_specification] ...]

create_specification:

Pág. 31 de 142


[DEFAULT] CHARACTER SET charset_name

| [DEFAULT] COLLATE collation_name

CREATE DATABASE crea una base de datos con el nombre dado. Para usar

CREATE DATABASE se necesita el privilegio CREATE en la base de datos.

También existen reglas para los nombres permitidos de bases de datos,

tablas, índices, y columnas. Si la base de datos ya existe y no se ha especificado

IF NOT EXISTS, se producirá un error

Se pueden usar las opciones create_specification para especificar

características de las base de datos desde MySQL 4.1.1. Estas características se

almacenan en el archivo 'db.opt' en el directorio de la base de datos. La cláusula

CHARACTER SET especifica el conjunto de caracteres por defecto para la base de

datos. La clausula COLLATE específica el conjunto de reglas de comparación de

caracteres (collation) por defecto para la base de datos. Para obtener más

detalles sobre los juegos de caracteres y las reglas de comparación de

caracteres ver Caracteres y Reglas.

Las bases de datos se implementan como directorios que contienen los

ficheros correspondientes a las tablas de la base de datos en MySQL . Como no

existen tablas en una base de datos cuando esta se crea, la sentencia CREATE

Pág. 32 de 142


DATABASE sólo crea un directorio bajo el directorio "data" de MySQL (y el

archivo 'db.opt' para MySQL 4.1.1 y siguientes).

Eliminar Bases de datos

Si se desea eliminar una base de datos que ya no es necesaria en el

sistema se cuenta, al menos, con dos métodos. Se ejecuta en un ventana de

Terminal.

$ mysql -h servidor -u usuario -p

Enter password:

mysql> DROP DATABASE nombre_de_la_base_de_datos;

quit

El comando DROP DATABASE borrar todas las tablas en la base de datos y

borrar la base de datos. Se debe ser muy cuidadoso con este comando! Para

usar DROP DATABASE, se necesita el permiso DROP en la base de datos.

Si se utiliza DROP DATABASE en una base de datos enlazada

simbólicamente, tanto el enlace como la base de datos se borran.

El comando DROP DATABASE retorna el número de tablas que se eliminan.

Se corresponde con el número de archivos .frm borrados.

Pág. 33 de 142


El comando DROP DATABASE borrar del directorio de base de datos los

archivos y los directorios que MySQL puede crear durante las operaciones

normales:

Todos los archivos con estas extensiones:

.

BAK

.

DAT

.

HSH

.

MRG

.

MYD

.

ISD

.

MYI

.

db

.

frm

Todos los subdirectorios con nombres que tienen dos dígitos hexadecimales

00-ff. son subdirectorios utilizados por las tablas RAID. Estos directorios no se

borran desde MySQL 5.0, cuando se elimina el soporte para tablas RAID. Las

tablas RAID deben ser convertidas y antes de actualizar MySQL 5.0 se deben

eliminar estos directorios manualmente.

El archivo db.opt, de existir.

El directorio de base de datos no puede ser borrado, si otros archivos o

directorios permanecen en el directorio de la base de datos después de que

MySQL halla borrado los archivos listados. En este caso, se debe borrar cualquier

archivos restante de froma manual y se debe realizar el comando DROP

DATABASE una vez más.

Pág. 34 de 142


http://dev.mysql.com/doc/refman/5.0/es/drop-database.html

UNIDAD 3: Operaciones sobre tablas

Objetivo de la Unidad: Emplear las instrucciones necesarias para

administrar tablas y registros dentro de MySQL.

Temas:

Crear tablas

Tipos de campos

Modificar la estructura de una tabla, agregar o eliminar nuevos campos

Eliminar tablas

Listar el contenido de una tabla

Crear tablas

CREAR UNA TABLA

Pág. 35 de 142


CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(definición_create,...)]

[opciones_tabla] [sentencia_select]

O

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

[(] LIKE viejo_tbl_name [)];

Sintaxis de definición_create:

definición_columnas

| [CONSTRAINT [símbolo]] PRIMARY KEY (index_nombre_col,...)

| KEY [nombre_index] (nombre_col_index,...)

| INDEX [nombre_index] (nombre_col_index,...)

| [CONSTRAINT [símbolo]] UNIQUE [INDEX]

[nombre_index] [tipo_index] (nombre_col_index,...)

| [FULLTEXT|SPATIAL] [INDEX] [nombre_index] (nombre_col_index,...)

| [CONSTRAINT [símbolo]] FOREIGN KEY

[nombre_index] (nombre_col_index,...) [definición_referencia]

| CHECK (expr)

Pág. 36 de 142


Sintaxis de definición_columnas:

nombre_col tipo [NOT NULL | NULL] [DEFAULT valor_por_defecto]

[AUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'string']

[definición_referencia]

Sintaxis de tipo:

TINYINT[(longitud)] [UNSIGNED] [ZEROFILL]

| SMALLINT[(longitud)] [UNSIGNED] [ZEROFILL]

| MEDIUMINT[(longitud)] [UNSIGNED] [ZEROFILL]

| INT[(longitud)] [UNSIGNED] [ZEROFILL]

| INTEGER[(longitud)] [UNSIGNED] [ZEROFILL]

| BIGINT[(longitud)] [UNSIGNED] [ZEROFILL]

| REAL[(longitud,decimales)] [UNSIGNED] [ZEROFILL]

| DOUBLE[(longitud,decimales)] [UNSIGNED] [ZEROFILL]

| FLOAT[(longitud,decimales)] [UNSIGNED] [ZEROFILL]

| DECIMAL(longitud,decimales) [UNSIGNED] [ZEROFILL]

| NUMERIC(longitud,decimales) [UNSIGNED] [ZEROFILL]

Pág. 37 de 142


| DATE

| TIME

| TIMESTAMP

| DATETIME

| CHAR(longitud) [BINARY | ASCII | UNICODE]

| VARCHAR(longitud) [BINARY]

| TINYBLOB

| BLOB

| MEDIUMBLOB

| LONGBLOB

| TINYTEXT

| TEXT

| MEDIUMTEXT

| LONGTEXT

| ENUM(valor1,valor2,valor3,...)

| SET(valor1,valor2,valor3,...)

| tipo_spatial

Sintaxis de nombre_col_index:

Pág. 38 de 142


nombre_col [(longitud)] [ASC | DESC]

Sintaxis de definición_referencia:

REFERENCES nombre_tbl [(nombre_col_index,...)]

[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]

[ON DELETE opción_referencia]

[ON UPDATE opción_referencia]

Sintaxis de opción_referencia:

RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Sintaxis de opciones_tabla:

opción_tabla [opción_tabla] ...

Sintaxis de opción_tabla:

{ENGINE|TYPE} = {BDB|HEAP|ISAM|InnoDB|MERGE|MRG_MYISAM|MYISAM

}

Pág. 39 de 142


| AUTO_INCREMENT = valor

| AVG_ROW_LENGTH = valor

| CHECKSUM = {0 | 1}

| COMMENT = 'cadena'

| MAX_ROWS = valor

| MIN_ROWS = valor

| PACK_KEYS = {0 | 1 | DEFAULT}

| PASSWORD = 'cadena'

| DELAY_KEY_WRITE = {0 | 1}

| ROW_FORMAT = { DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNTANT|

COMPACT}

| RAID_TYPE = { 1 | STRIPED | RAID0 }

RAID_CHUNKS=valor

RAID_CHUNKSIZE=valor

| UNION = (nombre_tabla,[nombre_tabla...])

| INSERT_METHOD = { NO | FIRST | LAST }

| DATA DIRECTORY = 'camino de directorio absoluto'

| INDEX DIRECTORY = 'camino de directorio absoluto'

| [DEFAULT] CHARACTER SET nombre_conjunto_caracteres [COLLATE

nombre_cotejo]

Pág. 40 de 142


Sintaxis de sentencia_select:

[IGNORE | REPLACE] [AS] SELECT ... (Alguna sentencia select legal)

Por medio del comando CREATE TABLE se crea una tabla con el nombre

especificado. Se debe contar con el privilegio CREATE para la tabla.

La tabla se crea en la base de datos actúa por defecto. Si la tabla ya existe

se producirá un error, si no hay una base de datos actual o si la base de datos

no existe.

El nombre de la tabla se puede especificar, en versiones de MySQL 3.22 y

posteriores, como db_name.tbl_name para la creación de la tabla en una base

de datos específica. Esto funciona aunque no exista una base de datos

seleccionada. Si se escribe el nombre de la tabla entre comillas, el nombre de la

base de datos y el de la tabla se deben entrecomillar por separado. Por ejemplo,

`midb`.`mitabla` es legal, pero `midb.mitabla` no lo es.

Se puede usar la palabra clave TEMPORARY cuando se quiere crear una

tabla desde la versión 3.23 de MySQL. La tabla temporal sólo se encuentra

visible para la conexión actual, y será borrada de forma automática cuando al

cerrar la conexión. Esto significa que dos conexiones diferentes pueden usar, de

forma simultánea, el mismo nombre para una tabla temporal sin conflictos entre

Pág. 41 de 142


ellas o con una tabla existente con el mismo nombre. Por otro lado, la tabla

existente se ocultará hasta que la tabla temporal sea borrada. Para crear las

tablas temporales desde MySQL 4.0.2 se debe tener el privilegio CREATE

TEMPORARY TABLES.

Se puede usar IF NOT EXISTS de modo que no se obtiene un error si la

tabla ya existe desde la versión 3.23 de MySQL. No se verifica si la tabla

existente tiene una estructura idéntica a la indicada por la sentencia CREATE

TABLE.

Al no especificar ni NULL ni NOT NULL, la columna se trata como si se

hubiese especificado NULL.

Una columna entera puede tener el atributo adicional AUTO_INCREMENT.

Cuando se inserta un valor NULL, que es lo que se recomienda, o 0 en una

columna indexada AUTO_INCREMENT, el siguiente valor secuencial se utiliza

como valor para dicha columna. Normalmente, ese valor es valor+1, donde valor

es el mayor valor en la columna actual en la tabla. Las secuencias

AUTO_INCREMENT empiezan en 1.

Nota: sólo puede existir una columna AUTO_INCREMENT por tabla, y debe

estar indexada y no puede tener un valor DEFAULT.

Pág. 42 de 142


En la versión 3.23 de MySQL una columna AUTO_INCREMENT sólo

funcionará de forma correcta si contiene valores positivos. La inserción de un

número negativo se considera como un número positivo muy grande. Esto se

hace para evitar que por problemas de precisión los números pasen de positivo

a negativo, y también para asegurar que una columa AUTO_INCREMENT

contenga un cero de forma accidental. En las tablas MyISAM y BDB se puede

especificar AUTO_INCREMENT en una columna secundaria dentro de una clave

multi-columna. Para hacer MySQL compatible con algunas aplicaciones ODBC, se

puede encontrar el valor AUTO_INCREMENT para la última fila insertada

mediante la siguiente consulta:

SELECT * FROM tbl_name WHERE auto_col IS NULL

Las definiciones de columnas de caracteres pueden incluir un atributo

CHARACTER SET para especificar el conjunto de caracteres y, opcionalmente, un

conjunto de reglas de comparación para la columna desde la versión 4.1 de

MySQL. Ver apéndice C.

CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);

Se puede especificar un comentario para una columna con la opción

COMMENT. El comentario se muestra mediante la sentencia SHOW CREATE

TABLE, y por SHOW FULL COLUMNS. Esta opción se encuentra disponible desde

MySQL 4.1. En versiones anteriores está permitida pero se ignora.

Pág. 43 de 142


KEY normalmente es sinónimo de INDEX. Desde la versión 4.1, el atributo

de clave PRIMARY KEY puede especificarse también como KEY. Esto se ha

implementado para compatibilidad con otras base de datos.

Una clave UNIQUE sólo puede contener valores diferentes en MySQL. Si se

intenta insertar una fila nueva con una clave que coincida con la de una fila

existente se genera un error. Se considera una excepción si una columna en el

índice puede tomar valores NULL, entonces puede contener múltiples valores

NULL. Esta excepción no se aplica a tablas BDB, para las que una columna

indexada permite sólo un valor NULL.

Una PRIMARY KEY es una KEY única donde todas las columnas clave deben

encontrase definidas como NOT NULL. Esto debe hacerse implícitamente, y

discretamente, si no se han declarado específicamente como NOT NULL. Una

tabla sólo puede contener una PRIMARY KEY. Si no se tiene una y alguna

aplicación solicita una, MySQL devolverá el primer índice UNIQUE que no tenga

columnas NULL, como PRIMARY KEY.

Se coloca la PRIMARY KEY, seguida por todos los índices UNIQUE, y

después los índices no únicos en la tabla creada. Esto ayuda al optimizador de

MySQL para dar prioridad sobre cuál índice se debe utilizar y para detectar de

forma más rápida las claves UNIQUE duplicadas.

Pág. 44 de 142


Una PRIMARY KEY puede ser un índice multicolumna. Sin embargo, no se

puede crear un índice multicolumna utilizando el atributo PRIMARY KEY en la

especificación de columna. Al hacer esto sólo se marca esa columna como

primaria. Se debe utilizar una cláusula adicional PRIMARY KEY(nombre_col_index,

...).

Si la clave PRIMARY o UNIQUE consta sólo de una columna y es de tipo

entero, se también se puede referenciar como _rowid en una sentencia SELECT,

desde la Versión 3.23.11.

En MySQL, el nombre de un PRIMARY KEY es PRIMARY. Para otros índices,

si no se asigna un nombre, se le asigna el mismo nombre que la primera

columna indexada, con un sufijo opcional (_2, _3. ...) para hacerlo único. Se

pueden ver los nombres de los índices usando la sentencia SHOW INDEX FROM

nombre_tabla.

Algunos motores de almacenamiento permiten especificar un tipo de

índice cuando este es creado desde MySQL 4.1.0,. La sintaxis para el

especificador de tipo_indice es USING nombre_tipo. Por ejemplo:

CREATE TABLE lookup

(id INT, INDEX USING BTREE (id))

ENGINE = MEMORY;

Pág. 45 de 142


Sólo las tablas de tipos MyISAM, InnoDB, BDB y, desde MySQL 4.0.2,

MEMORY soportan índices en columnas que contengan valors NULL. En otros

casos se deben declarar esas columnas como NOT NULL o se producirá un error.

Se puede crear un índice que utilice sólo los primeros 'longitud' bytes de

una columna CHAR o VARCHAR con al sintaxis col_name(longitud) en una

especificación de un índice; esto puede causar que el archivo de índices sea

mucho más pequeño. Las tablas de tipos MyISAM y, desde MySQL 4.0.14, InnoDB

soportan la indexación en columnas BLOB y TEXT. Cuando se utiliza un índice en

una columna BLOB o TEXT se debe especificar siempre la longitud de los índices.

Por ejemplo:

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

Los prefijos pueden tener hasta 255 bytes de longitud, o 1000 bytes para

tablas MyISAM y InnoDB desde MySQL 4.1.2. Nótese que los límites de los

prefijos se miden en bytes, aunque la longitud del prefijo en sentencias CREATE

INDEX se interpretan por número de caracteres. Se debe tener esto en cuenta al

especificar una longitud de prefijo para una columna que utiliza un conjunto de

caracteres multi-byte.

Pág. 46 de 142


Una especificación de nombre_col_index puede terminar con ASC o DESC.

Estas palabras clave se encuentran permitidas para futuras extensiones para la

especificación de almacenamiento de índices ascendentes o descendentes. En la

actualidad, se verifica su sintaxis, pero se ignoran. Los valores de índices

siempre se almacenan en orden ascendente.

El servidor ordena los valores usando sólo los bytes iniciales indicados por

la variable del servidor max_sort_length, al utilizar ORDER BY o GROUP BY con

una columna TEXT o BLOB,.

Se pueden crear índices especiales FULLTEXT en versiones de MySQL

3.23.23 o posteriores, éstos se utilizan para realizar búsquerdas de texto

completo. Sólo las tablas de tipo MyISAM soportan índices FULLTEXT, éstos sólo

pueden ser creados desde columnas CHAR, VARCHAR y TEXT. La indexación

siempre se hace sobre la columna completa; la indexación parcial no se

encuentra soportada y cualquier longitud de prefijo es ignorada si se especifica.

Se pueden crear índices SPATIAL en columnas de tipo espacial a partir de

MySQL 4.1. Los tipos especiales se soportan sólo para tablas MyISAM y las

columnas indexadas deben declararse como NOT NULL.

Las tablas InnoDB soportan la verificación para restricciones de claves

foráneas a partir de la versión 3.23.44 de MySQL. Se deben tener en cuenta que

la sintaxis para FOREIGN KEY en InnoDB es mucho más restrictiva que la sintaxis

Pág. 47 de 142


presentada antes: las columnas en la tabla referenciada deben ser nombradas

de forma explícita. InnoDB soporta las acciones ON DELETE y ON UPDATE para

las claves ajenas, tanto para MySQL 3.23.50 como para 4.0.8, respectivamente.

Para otros tipos de tablas, el servidor MySQL verifica la sintaxis de FOREIGN KEY,

CHECK y REFERENCES en comandos CREATE TABLE, pero no se toma ninguna

acción.

Cada columna NULL requiere un bit extra, redondeando hacia arriba al

siguiente byte, para las tablas MyISAM e ISAM. El tamaño máximo para un

registro, en bytes, puede calcularse de la siguiente forma:

row length = 1

+ (sum of column lengths)

+ (number of NULL columns + delete_flag + 7)/8

+ (number of variable-length columns)

El comando delete_flag es 1 para las tablas con formato de registro

estático. Las tablas estáticas utilizan un bit en una fila de registro como un

banderín que indica si la fila ha sido borrada. El comando delete_flag es 0 para

tablas dinámicas ya que el banderín se almacena en una fila de cabecera

dinámica. Estos cálculos no se aplican a tablas InnoDB, para las que el tamaño

de almacenamiento no es diferente que para las columnas NULL si se compara

Pág. 48 de 142


con las NOT NULL. La parte opciones_tabla de CREATE TABLE sólo se encuentran

disponibles desde MySQL 3.23.

Las opciones ENGINE y TYPE especifican el motor de almacenamiento para

la tabla. ENGINE se añadió en MySQL 4.0.18, para 4.0, y 4.1.2, para 4.1. Esta es

la opción sugerida desde esas versiones, y TYPE queda desaconsejada. TYPE

obtendrá soporte a lo largo de la serie 4.x series, pero se eliminará en MySQL

5.1.

Las opciones ENGINE y TYPE pueden tomar los valores presentados a

continuación:

BDB: Tablas de transacción segura con bloqueo de página.

BerkeleyDB: Alias para BDB.

HEAP: los datos para esta tabla sólo se almacenan en memoria.

ISAM: es el motor de almacenamiento original de MySQL.

InnoDB: son las tablas de transacción segura con bloqueo de fila y claves

foráneas.

MEMORY: es el alias para HEAP.

MERGE: es una colección de tablas MyISAM utilizadas como una tabla.

MRG_MyISAM : es un alias para MERGE.

MyISAM: es el nuevo motor binario de almacenamiento portable que

reemplaza a ISAM.

Pág. 49 de 142


MySQL utilizará el tipo MyISAM, si se especifica un tipo de tabla, y ese tipo

particular no se encuentra disponible. Por ejemplo, si la definición de la tabla

incluye la opción ENGINE=BDB pero el servidor MySQL no soporta las tablas

BDB, la tabla se creará como una tabla MyISAM. Esto hace posible tener un

sistema de réplica donde se tienen tablas operativas en el maestro pero las

tablas creadas en el esclavo no son operativas, con el fin de obtener mayor

velocidad. En MySQL 4.1.1 se obtiene un aviso si el tipo de tabla especificado no

es aceptable.

Se puede crear una tabla a partir de otra añadiendo la sentencia SELECT al

final de la sentencia CREATE TABLE desde MySQL 3.23,:

CREATE TABLE new_tbl SELECT * FROM orig_tbl;

MySQL creará nuevos campos para todos los elementos del SELECT. Por

ejemplo:

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,

-> PRIMARY KEY (a), KEY(b))

-> TYPE=MyISAM SELECT b,c FROM test2;

Pág. 50 de 142


Con esto se creará una tabla MyISAM con tres columnas, a, b y c. Se debe

tener presente que dichas tres columnas de la sentencia SELECT se añaden al

lado derecho de la tabla, no superpuestos. Véase el siguiente ejemplo:

mysql> SELECT * FROM foo;

+---+

| n |

+---+

| 1 |

+---+

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;

Query OK, 1 row affected (0.02 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM bar;

+------+---+

| m | n |

+------+---+

| NULL | 1 |

+------+---+

1 row in set (0.00 sec)

Pág. 51 de 142


Se inserta una fila en bar con los valores de foo y los valores por defecto

para las nuevas columnas para cada una de la filas en la tabla foo.

Ésta se eliminará de forma automática y no se creará si se produce

cualquier error mientras se copian los datos a la tabla.

Al utilizar CREATE TABLE ... SELECT no se crearán los indices de forma

automática. Esto se ha hecho de forma intencionada para hacer el comando tan

flexible como sea posible. Si se quiere tener índices en la tabla creada, se puede

especificar después de la sentencias SELECT:

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

También, se pueden producir algunas conversiones de tipos de columna.

Por ejemplo, el atributo AUTO_INCREMENT no se preserva, y las columnas

VARCHAR se pueden convertir en CHAR.

Al crear una tabla con CREATE ... SELECT, hay que asegurarse de crear un

alias para cualquier llamada a función o expresión en la consulta. Al no hacerlo,

la sentencia CREATE podrá fallar o puede dar como resultado nombres de

columna no deseados.

Pág. 52 de 142


CREATE TABLE artists_and_works

SELECT artist.name, COUNT(work.artist_id) AS number_of_works

FROM artist LEFT JOIN work ON artist.id = work.artist_id

GROUP BY artist.id;

se puede especificar explícitamente el tipo para una columna generada

desde MySQL 4.1:

CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;

También, se puede usar LIKE para crear una tabla vacía basada en la

definición de otra tabla, incluyendo cualquier atributo de columna e indices que

tenga la tabla original en MySQL 4.1:

CREATE TABLE new_tbl LIKE orig_tbl;

El comando CREATE TABLE ... LIKE no copia ninguna opción de tabla DATA

DIRECTORY o INDEX DIRECTORY que se haya especificado en la tabla original, o

cualquier definición de clave foránea.

Se puede preceder el SELECT por IGNORE o REPLACE para indicar como

manipular los registros que dupliquen las claves únicas. Con IGNORE, los nuevos

Pág. 53 de 142


registros que dupliquen la clave única de un registro existente serán

descartados. Con REPLACE, los nuevos registros reemplazan a los que tengan el

mismo valor de clave única. Si no se especifica IGNORE ni REPLACE, la

repetición de claves únicas producirá un error.

MySQL no permite la inserción concurrente durante CREATE TABLE ...

SELECT para asegurar la utilización del diario de modificación para recrear las

tablas originales.

Tipos de campos

Con MySQL se tienen habilitados diversos tipos de campos que en una

primera aproximación podrían clasificarse en tres grupos:

Campos numéricos

Campos de fecha

Campos de cadenas de caracteres

Campos numéricos

MySQL ofrece soporte para los tipos numéricos exactos (INTEGER,

NUMERIC, DECIMAL, y SMALLINT) y los tipos numéricos aproximados (FLOAT,

DOUBLE precision y REAL).

Los campos que contienen números enteros admiten el parámetro

Pág. 54 de 142


UNSIGNED que implica que no admita signos por lo que solo aceptaría

enteros positivos.

Todos los campos numéricos admiten el parámetro ZEROFILL cuya

función es completar el campo con ceros a la izquierda hasta su longitud

máxima.

Tipos de campos numéricos enteros

Estos son los distintos tipos de campos numéricos enteros que admite

MySQL. Los parámetros señalados entre corchetes son opcionales.

TINYINT [(M)] [UNSIGNED] [ZEROFILL]

Es número entero muy pequeño. Con la opción UNSIGNED puede tomar

valores entre 0 y 255. En caso contrario, puede estar comprendido entre -128 y

127.

Si el parámetro ZEROFILL solo tiene sentido junto con la opción UNSIGNED ya

que no tiene ningún sentido tratar de rellenar con ceros a la izquierda de un

número negativo.

El valor por defecto de parámetro M, o número de cifras es 4 si la opción

UNSIGNED no se encuentra activada. De encontrarse activada, el valor por

defecto sería M=3. Para valores de M >valor por defecto reajusta el tamaño al

valor por defecto.

Pág. 55 de 142


Al asignarle a M un valor menor que cuatro se limita el número de caracteres

al tamaño especificado tomando en cuenta el signo sólo en los números

negativos.

Por ejemplo, si M=3 admitiría 148 pero si intentamos insertar -148 recortaría

por la izquierda y solo insertaría -14.

Si se trata de insertar un valor fuera de rango se registraría el valor dentro

del rango más próximo a éste.

Por ejemplo: al tratar de insertar el valor 437 se escribiría 127 ó 255, este

último en el caso de tener la opción UNSIGNED.

Si se desease insertar -837 con la opción UNSIGNED se escribiría 0 y sin ella

pondría -128.

El tamaño de un campo TINYINT es de 1 byte.

SMALLINT [(M)] [UNSIGNED] [ZEROFILL]

Es un número entero pequeño. Con la opción UNSIGNED se pueden tomar

valores entre 0 y 65 535. De otra forma, éste puede estar comprendido entre

-32 768 y 32 767. Los comentarios hechos para TINYINT son válidos, con la

excepción de los relativos a los valores por defecto de M que en este caso

serían 6 ó 5. Su tamaño es de 2 bytes.

Pág. 56 de 142


MEDIUMINT [(M)] [UNSIGNED] [ZEROFILL]

Es un número entero mediano. Con la opción UNSIGNED puede tomar

valores entre 0 y 16 777 215. En caso contrario, éste puede estar comprendido

entre -8 388 608 y 8 388 607. También son válidos los comentarios hechos

para TINYINT excepto los relativos al valor por defecto de M que en este caso

serían 8. Su tamaño es de 3 bytes.

INT [(M)] [UNSIGNED] [ZEROFILL]

Es un número entero. Con la opción UNSIGNED se pueden tomar valores

entre 0 y 4 294 967 295. De otra forma, éste puede estar comprendido entre

-2 147 483 648 y 2 147 483 647. Son válidos todos los comentarios de los

casos anteriores. Su tamaño es de 4 bytes.

INTEGER [(M)] [UNSIGNED] [ZEROFILL]

Es un sinónimo de INT

BIGINT [(M)] [UNSIGNED] [ZEROFILL]

Pág. 57 de 142


Son números enteros grandes. Con la opción UNSIGNED se pueden tomar

valores entre 0 y 18 446 744 073 709 551 615. En caso contrario, éste puede

estar comprendido entre -9 223 372 036 854 775 808 y 21 474 839 223 372

036 854 775 807 647, pero al usarlo desde PHP se encontrará sujeto a las

limitaciones máximas de los valores numéricos de este. Son válidos todos los

comentarios de los casos anteriores. Su tamaño es de 8 bytes.

Números de coma flotante

Debido a la estructura binaria de los microprocesadores y ya que algunos

números no enteros, sin ir más lejos el 0.1, se requerirían infinitos caracteres

binarios para su representación exacta, se hace necesario introducir un

redondeo en su tratamiento informático y como consecuencia de ello asumir

que se generan errores de medida.

Esta circunstancia obligó al tratamiento de los números decimales

mediante el llamado Estándar de Aritmética de Punto Flotante, un

algoritmo definido por la IEEE (Instituto de Ingenieros Eléctricos y Electrónicos)

que unificó los procesos de representación de números en ordenadores por

medio del cual los errores introducidos son uniformemente controlables.

El Estándar de Aritmética de Punto Flotante estableció dos niveles de

precisión:

Pág. 58 de 142


Precisión Simple en la que todo número debe ser almacenado en 32

bits

(4 bytes)

Doble precisión en la que los números se almacenan en 64 bits (8 bytes

).

El MySQL admite los siguientes tipos de números de coma flotante:

FLOAT(x) [ZEROFILL]

Es un número de coma flotante. Éste ignora la opción UNSIGNED pero si

acepta ZEROFILL por lo que se debe prestar atención a estas opciones ya que

no sería demasiado habitual una presentación como esta: 000-3.47

El valor de x especifica la precisión. Si x<=24 será de precisión simple.

Cuando 24 <x <=53 lo convertirá automáticamente a doble precisión. Al no

especificar el valor de x el campo se considera como de precisión simple. Su

tamaño es de 4 bytes si x<=24 y de 8 bytes cuando 24 <x <=53

FLOAT [(M,D)] [ZEROFILL]

Es un número de coma flotante de precisión simple. Son válidos los

comentarios relativos a las opciones UNSIGNED y ZEROFILL del caso anterior.

Toma valores en los intervalos siguientes:

Pág. 59 de 142


-3.402823466E+38 a -1.175494351E-38

0 y

1.175494351E-38 a 3.402823466E+38.

M es la anchura máxima de visualización y D es el número de

decimales. Si M > 24 se convierte a doble precisión FLOAT de forma

automática sin argumentos, representa un número de coma flotante y

precisión simple.

DOUBLE [(M,D)] [ZEROFILL]

Es un número de coma flotante de doble precisión. Siguen siendo

válidos los comentarios relativos a las opciones UNSIGNED y ZEROFILL del

caso anterior.

Toma valores en los intervalos siguientes:

-1.7976931348623157E+308 a -2.2250738585072014E-308

0 y

2.2250738585072014E-308 a 1.7976931348623157E+308

M es la anchura máxima de visualización y D es el número de

decimales.

FLOAT

Pág. 60 de 142


Sin argumentos, representa un número de coma flotante y precisión

doble.

REAL [(M,D)] [ZEROFILL]

Es sinónimo de DOUBLE.

DECIMAL [(M[,D])] [ZEROFILL]

Es un número de coma flotante y doble precisión que se almacena

como un campo de tipo CHAR.

Se guarda el valor como una cadena donde cada caracter representa una

cifra. En el valor de M -anchura máxima de visualización, la coma y el signo

menos de los números negativos no se toman en cuenta, aunque si se reserva

un espacio en campo para ellos de forma automática.

Si D vale 0 no tendrá parte decimal. Los números toman valores en el

mismo intervalo especificado para DOUBLE. Los valores por defecto de M y D

son respectivamente 10 y 0. Ocupan M+2 bytes si D > 0; M+1 bytes si D = 0 ó

D+2 bytes si M < D

Pág. 61 de 142


NUMERIC(M,D) [ZEROFILL]

Se comporta de forma idéntica a DECIMAL

Campos de fecha

Con MySQL se cuenta con campos específicos para el almacenamiento de

fechas. Éstos son los siguientes:

DATE

Con DATE se recoge una fecha dentro del intervalo 01-01-1000 a 31-12-

9999. MySQL guarda los valores DATE con formato AAAA-MM-DD (año-mes-día

). Su tamaño es de 3 bytes.

DATETIME

Con DATETIME se recoge una combinación de fecha y hora dentro del

intervalo 00:00:00 del día 01-01-1000 y las23:59:59 del día 31-12-9999.

MySQL guarda los valores DATETIME con formato AAAA-MM-DD HH:MM:SS

(año-mes-día hora:minutos:segundos) . Su tamaño es de 8 bytes.

TIME

Pág. 62 de 142


Con TIME se recoge una hora dentro del intervalo -838:59:59 a 838:59:59.

MySQL guarda los valores TIME con formato HH:MM:SS

(horas:minutos:segundos) . Su tamaño es de 3 bytes.

YEAR ó YEAR(2) ó YEAR(4)

Con éstos se recoge un año en formato de cuatro cifras (YEAR ó YEAR(4))

o en formato de dos cifras (YEAR(2))dentro del intervalo 1901 a 2155 en el

caso de cuatro cifras ó de 1970 a 2069 si se trata de dos cifras. Su tamaño es

de 1 byte.

TIMESTAMP [(M)]

Con éste se recoge un tiempo UNIX. El intervalo válido va desde 01-01-

1970 00:00:00 a cualquier fecha del año 2037.

El parámetro M puede tomar los valores: 14 (valor por defecto), 12, 8, o 6 que

se corresponden con los formatos AAAAMMDDHHMMSS, AAMMDDHHMMSS,

AAAAMMDD, o AAMMDD. Si se le asigna la opción NUL guardará la hora

actual. Cuando se asigna 8 ó 14 como parámetros es considerado como un

número y para las demás opciones como una cadena. Independientemente del

valor del parámetro, un campo TIMESTAMP siempre ocupa 4 bytes.

Campos tipo cadena de caracteres

CHAR (M) [BINARY]

Pág. 63 de 142


Es una cadena de tamaño fijo que se completa a la derecha por

espacios si es necesario.

El parámetro M puede valer de 1 a 255 caracteres. Los espacios finales son

suprimidos cuando al insertar la cadena en el registro.

Los valores de tipo CHAR son elegidos y comparados sin tener en cuenta ni

Mayúsculas ni Minúsculas y utilizan el juego de caracteres por defecto.

Se puede utilizar el operador BINARY para hacer la cadena sensible a

Mayúsculas / Minúsculas. Se puede utilizar un campo tipo CHAR(0) con el

atributo NULL para almacenar una valor booleano. En este caso ocupará un solo

byte y podrá tener únicamente dos valores: NUL ó "". Su tamaño es de M

bytes siendo 1 <= M <= 255 .

VARCHAR(M) [BINARY]

Es una cadena de caracteres de longitud variable. Su tamaño

máximo, especificado en el parámetro M, puede estar comprendido entre 1 y

255 caracteres. Con la opción BINARY es capaz de discriminar entre Mayúsculas

/ minúsculas.

TINYBLOB y TINYTEXT

Pág. 64 de 142


Son cadenas de caracteres de longitud variable con un tamaño

máximo de 255 (28 - 1) caracteres. La diferencia entre ambas es que TINYBLOB

si discrimina entre Mayúsculas y Minúsculas, mientras que TINYTEXT no lo

hace. Ninguno de los campos: BLOB y TEXT admite valores por DEFECTO

Las versiones de MySQL anteriores a 3.23.2 permiten utilizar estos campos

para indexar.

Si se intenta guardar en un campo de este tipo una cadena de mayor

longitud que la especificada sólo se guardarán los M primeros caracteres de

la cadena.

BLOB o TEXT

Estas son cadenas de caracteres de longitud variable con un tamaño

máximo de 65535 (216 - 1) caracteres. La diferencia entre ambas es que BLOB

si discrimina entre Mayúsculas y Minúsculas, mientras que TEXT no lo hace.

Ninguno de los campos: BLOB y TEXT admite valores por DEFECTO .

MEDIUMBLOB o MEDIUMTEXT

Éstas son cadenas de caracteres de longitud variable con una longitud

máxima de 16.777.215 (224 - 1) caracteres. Las especificaciones hechas en el

apartado anterior son válidas. El tamaño máximo de los campos de este tipo se

Pág. 65 de 142


encuentra sujeto a las limitaciones externas tales como la memoria disponible y

el tamaño del buffer de comunicación servidor/cliente.

LONGBLOB o LONGTEXT

La única diferencia con la anterior es el tamaño máximo de la cadena, que

para este caso es de 4.294.967.295 (232 - 1) caracteres.

ENUM('valor1','valor2',...)

Es una cadena de caracteres que contiene uno solo de los valores de la

lista (valor1, valor2, etc. etc.). Cuando se desea insertar un nuevo registro en

una tabla, el valor a especificar para un campo de este tipo debe ser una

cadena que contenga uno de los valores especificados en la tabla. Si se

tratara de insertar un valor distinto de ellos insertaría una cadena vacía.

SET('valor1','valor2','valor3'...)

Es una cadena de caracteres formados por la unión uno varios de los

valores de una lista. El máximo de elementos es 64. Los valores que deben

escribirse en los registros de la tabla que contiene este campo deben ser

numéricos expresados en forma binaria o en forma decimal.

Pág. 66 de 142


Modificar la estructura de una tabla, agregar o eiminar nuevos

campos

ALTER TABLE

ALTER [IGNORE] TABLE tbl_name alter_specification [, alter_specification ...]

Sintaxis para alter_specification:

ADD [COLUMN] create_definition [FIRST | AFTER column_name ]

| ADD [COLUMN] (create_definition, create_definition,...)

| ADD INDEX [index_name] (index_col_name,...)

| ADD [CONSTRAINT [symbol]] PRIMARY KEY (index_col_name,...)

| ADD [CONSTRAINT [symbol]] UNIQUE [index_name] (index_col_name,...)

| ADD FULLTEXT [index_name] (index_col_name,...)

Pág. 67 de 142


| ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name]

(index_col_name,...)

[reference_definition]

| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

| CHANGE [COLUMN] old_col_name create_definition

[FIRST | AFTER column_name]

| MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]

| DROP [COLUMN] col_name

| DROP PRIMARY KEY

| DROP INDEX index_name

| DISABLE KEYS

| ENABLE KEYS

| RENAME [TO] new_tbl_name

| ORDER BY col

| CHARACTER SET character_set_name [COLLATE collation_name]

| table_options

El ALTER TABLE permite modificar la estructura de una tabla existente. Por

ejemplo, se pueden añadir o eliminar columnas, crear y destruir índices, cambiar

el tipo de una columna existente o renombrar columnas o la propia tabla.

También es posible modificar el comentario y el tipo de la tabla.

Pág. 68 de 142


Al utilizar ALTER TABLE para cambiar la especificación de una columna

pero DESCRIBE tbl_name se indica que la columna no ha cambiado, es posible

que MySQL haya ignorado la modificación por alguna razón. Por ejemplo, si se ha

intentado cambiar una columna VARCHAR a CHAR, MySQL seguirá usando

VARCHAR si la tabla contiene otras columnas de longitud variable.

ALTER TABLE trabaja creando una copia temporal de la tabla original. La

modificación se lleva a cabo durante la copia, luego la tabla original se borra y la

nueva se renombra. Esto se lleva acabo para hacer que todas las actualizaciones

se dirijan a la nueva tabla sin ningún fallo de actualización. Mientras ALTER

TABLE se ejecuta, la tabla original permanece accesible en lectura para otros

clientes. Las actualizaciones y escrituras en la tabla se retrasan hasta que la

nueva tabla se encuentre preparada.

Se debe tener en cuenta que si se usa otra opción para ALTER TABLE como

RENAME, MySQL siempre se creará una tabla temporal, aunque no sea

estrictamente necesario copiarla, como cuando se cambia el nombre de una

columna. Está previsto corregir esto en el futuro, pero como no es corriente usar

ALTER TABLE para hacer esto, no es algo urgente de hacer. Para tablas MyISAM,

se puede aumentar la velocidad de la recreación de índices, que es la parte más

lenta del proceso, asignando un valor alto a la variable myisam_sort_buffer_size.

Si se desea utilizar ALTER TABLE, es necesario tener los privilegios ALTER,

INSERT y CREATE en la tabla. IGNORE es una extensión MySQL a SQL-92. Ésta

Pág. 69 de 142


controla el modo de trabajar de ALTER TABLE si hay claves duplicadas o únicas

en la nueva tabla. Si no se especifica IGNORE, la copia se aborta y se deshacen

los cambios. Si se especifica IGNORE, en las filas duplicadas en una clave única

sólo se copia la primera fila; el resto se eliminan.

Se pueden usar múltiples cláusulas ADD, ALTER, DROP y CHANGE en una

sentencia sencilla ALTER TABLE. Esto es una extensión MySQL aSQL-92, que

permite sólo una aparición de cada cláusula en una sentencia ALTER TABLE.

CHANGE col_name, DROP col_name y DROP INDEX también son extensiones

MySQL a SQL-92. MODIFY es una extensión Oracle a ALTER TABLE.

La palabra opcional COLUMN es una palabra ruidosa y puede ser omitida.

Si se utiliza ALTER TABLE tbl_name RENAME TO new_name sin ninguna otra

opción, MySQL sencillamente renombra los archivos que corresponden con la

tabla tbl_name. No hay necesidad de crear una tabla temporal.

Las cláusulas create_definition utilizan la misma sintaxis para ADD y

CHANGE que CREATE TABLE. Esta sintaxis incluye sólo el nombre de columna, no

el tipo. Se puede renombrar una columna utilizando una cláusula CHANGE

old_col_name create_definition. Para hacerlo, se deben especificar los nombres

antiguo y nuevo de la columna y el tipo que la columna tiene actualmente. Por

ejemplo, para renombrar una columna INTEGER desde a a b, se puede hacer

esto:

Pág. 70 de 142


mysql> ALTER TABLE t1 CHANGE a b INTEGER;

Si se desea cambiar el tipo de una columna, pero no su nombre, la sintaxis

de CHANGE sigue necesitando un nombre de columna antiguo y nuevo, aunque

mantenga en mismo nombre. Por ejemplo:

mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

Sin embargo, desde la versión 3.22.16a de MySQL, se puede usar también

MODIFY para modificar el tipo de una columna sin renombrarla:

mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

Si se utiliza CHANGE o MODIFY para acortar una columna para la cual

existe un índice en parte de la columna, por ejemplo, si se tiene un índice en los

primeros 10 caracteres de una columna VARCHAR, no será posible acortar la

columna a un número de caracteres menos que los indexados.

Al cambiar un tipo de columna usando CHANGE o MODIFY, MySQL intenta

convertir datos al nuevo tipo lo mejor posible. En las versiones 3.22 o siguientes

de MySQL, se puede usar FIRST o ADD ... AFTER col_name para añadir una

columna en una posición específica dentro de una fila de la tabla. Por defecto se

Pág. 71 de 142


añade la columna al final. Desde MySQL 4.0.1, se pueden usar las palabras FIRST

y AFTER en un CHANGE o MODIFY.

Al utilizar ALTER COLUMN se especifica un nuevo valor por defecto para

una columna o elimina el valor por defecto anterior. Si se elimina el anterior

valor por defecto y la columna puede ser NULL, el nuevo valor por defecto es

NULL. Si la columna no puede ser NULL, MySQL asigna un nuevo valor por

defecto, como se describe en CREATE TABLE.

DROP INDEX elimina un índice. Esto es una extensión MySQL para SQL-92.

Ver la sintaxis de DROP INDEX.

Si se quitan columnas de una tabla, también se eliminan de cualquier

índice de las que formen parte. Si todas las columnas que forman parte de un

índice se eliminan, el índice se elimina también.

Si la tabla contiene sólo una columna, ésta no puede ser eliminada. Si lo

que se pretende es eliminar la tabla, se debe usar DROP TABLE.

Con DROP PRIMARY KEY se elimina el índice primario. Si no existiera ese

índice, se eliminará el primer índice UNIQUE de la tabla. El MySQL marca la

primera clave UNIQUE como la PRIMARY KEY si no se ha especificado una

PRIMARY KEY de forma explícita. Si se añade una UNIQUE INDEX o una PRIMARY

Pág. 72 de 142


KEY a la tabla, se almacena antes de cualquier índice UNIQUE de modo que

MySQL pueda detectar claves duplicadas lo más pronto posible.

El comando ORDER BY permite crear una nueva tabla con un orden

específico para las filas. La tabla no permanecerá en ese orden después de

nuevas inserciones o borrados. En algunos casos, es más fácil hacer que MySQL

ordene, si la tabla se encuentra indexada por la columna por la que se desea

ordenarla más adelante. Esta opción es corriente, principalmente, cuando se

sabe que se va a consultar la tabla en un orden determinado; es posible obtener

un mejor rendimiento al utilizar esta opción después de grandes campos en la

tabla.

Si se utiliza ALTER TABLE en una tabla MyISAM, todos los índices no únicos

serán creados en un proceso separado (como en REPAIR). Esto hace ALTER

TABLE mucho más rápido al existir muchos índices.

la característica anterior puede ser activada explícitamente desde MySQL

4.0. ALTER TABLE ... DISABLE KEYS hace que MySQL detenga la actualización de

índices no únicos para tablas MyISAM. ALTER TABLE ... ENABLE KEYS debe ser

usado para recrear índices perdidos. Como MySQL hace esto con un algoritmo

especial que es mucho más rápido cuando se insertan claves una a una,

desactivar las claves puede proporcionar una considerable mejora de tiempo

cuando se inserta gran cantidad de filas. Con la función del API de C mysql_info,

se puede obtener todos los registros que han sido copiados, y, si se ha utilizado

Pág. 73 de 142


IGNORE, todos los registros que fueron borrados por la duplicación de valores de

clave.

Las cláusulas FOREIGN KEY, CHECK y REFERENCES no hacen nada en la

actualidad, excepto para tablas del tipo InnoDB que soportant ... ADD

[CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...) y ... DROP

FOREIGN KEY .... Ver restricciones FOREIGN KEY. La sintaxis para otros tipos de

tabla se proporciona sólo por compatibilidad, para hacer más fácil portar código

desde otros servidores SQL y para ejecutar aplicaciones que crean tablas con

referencias.

Con ALTER TABLE se ignoran las opciones de tabla DATA DIRECTORY y

INDEX DIRECTORY. Si se desea cambiar todas las columnas

CHAR/VARCHAR/TEXT a un nuevo juego de caracteres, por ejemplo después de

actualizar desde MySQL 4.0.x a 4.1.1, se puede hacer:

ALTER TABLE table_name CHARACTER SET character_set_name;

El comando presentado a continuación sólo cambia el juego de caracteres

por defecto para la tabla:

ALTER TABLE table_name DEFAULT CHARACTER SET

character_set_name;

Pág. 74 de 142


El juego de caracteres por defecto es el que se utiliza si no se especifica un

juego de caracteres para una nueva columna que se añada a la tabla (por

ejemplo con ALTER TABLE ... ADD column).

Luego, se muestra un ejemplo que demuestra como utilizar ALTER TABLE.

Se puede comenzar creando una tabla t1 como:

mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));

Si se desea renombrar la tabla de t1 a t2:

mysql> ALTER TABLE t1 RENAME t2;

Si se desea cambiar la columna a de INTEGER a TINYINT NOT NULL

(dejando el mismo nombre), y cambiar la columna b de CHAR(10) a CHAR(20) y

además renombrando de b a c:

mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20

);

Si se desea añadir una columna TIMESTAMP llamada d:

Pág. 75 de 142


mysql> ALTER TABLE t2 ADD d TIMESTAMP;

Si se desea añadir un índice en la columna d, y hacer la columna a la clave

primaria:

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

Si se desea eliminar la columna c:

mysql> ALTER TABLE t2 DROP COLUMN c;

Si se desea añadir una nueva columna entera AUTO_INCREMENT llamada c:

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,

ADD INDEX (c);

Se debe anotar que se debe indexar en c, porque las columnas

AUTO_INCREMENT deben estar indexadas, y también que hemos declarado c

como NOT NULL, porque las columnas indexadas no pueden ser NULL.

Pág. 76 de 142


Al añadir una columna AUTO_INCREMENT, los calores de columna se

llenan con una secuencia numérica de forma automática. Se puede elegir el

primer número de la secuencia ejecutando SET INSERT_ID=value antes de

ALTER TABLE o usando la opción de tabla AUTO_INCREMENT=value.

Al utilizar las tablas MyISAM, si no se modifica la columna

AUTO_INCREMENT, la secuencia de numérica no resultará afectada. Si se elimina

una columna AUTO_INCREMENT y después se añade otra columna

AUTO_INCREMENT, los números empezarán a partir de 1 otra vez.

Eliminar tablas

Con DROP TABLE se elimina una o más tablas. Se debe poseer el privilegi

DROP para cada una de las tablas. Se eliminan tanto los datos que contengan y

las definiciones de las tablas, así que hay que tener cuidado con esta sentencia.

DROP [TEMPORARY] TABLE [IF EXISTS]

tbl_name [, tbl_name] ...

Se pueden utilizar las palabras clave IF EXISTS para evitar el error que

ocurriría al intentar eliminar tablas que no existan. Desde MySQL 4.1, se genera

una nota para cada tabla inexistente cuando se usa IF EXISTS, en MySQL 3.22 y

posteriores.

La palabra clave TEMPORARY se ignora en MySQL 4.0. Desde la versión

4.1, tiene el efecto siguiente:

Pág. 77 de 142


* La sentencia elimina sólo tablas temporales (TEMPORARY).

* La sentencia no termina ninguna transacción en curso.

* No se verifican derechos de acceso. (Una tabla temporal sólo es visible

para el cliente que la ha creado, de modo que no es necesaria la verificación).

Utilizar TEMPORARY es una buena forma de asegurar que no se eliminará

una tabla no temporal de forma accidental.

Listar el contenido de una tabla

SHOW COLUMNS

SHOW FIELDS

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'patrón']

Con SHOW COLUMNS se listan las columnas de una tabla especificada. Si

los tipos de columna difieren de los que se esperaba a partir de la sentencia

CREATE TABLE utilizada, hay que tener en cuenta que, algunas veces, MySQL

cambia los tipos de columnas cuando se crea o se altera una tabla. Las

condiciones para que esto ocurra se describen al final de CREATE TABLE.

Pág. 78 de 142


A partir de MySQL 3.23.32 se puede utilizar la palabra clave FULL. Ésta

causa que la salida incluya los privilegios que se poseen para cada columna. A

partir de MySQL 4.1, FULL también causa que se muestre cualquier comentario

por columna que exista.

También, se puede utilizar db_name.tbl_name como una alternativa a la

sintaxis tbl_name FROM db_name syntax, ya que estas dos sentencias son

equivalentes:

mysql> SHOW COLUMNS FROM mytable FROM mydb;

mysql> SHOW COLUMNS FROM mydb.mytable;

El SHOW FIELDS es un sinónimo de SHOW COLUMNS. Con éste también se

pueden listar las columnas de una tabla con el comando mysqlshow db_name

tbl_name.

La sentencia DESCRIBE proporciona información similar a SHOW COLUMNS.

SHOW TABLES

SHOW [FULL|OPEN] TABLES [FROM nombre_db] [LIKE 'patrón']

Pág. 79 de 142


Con SHOW TABLES se listan las tablas no temporales en una base de datos

especificada. También, se puede obtener esta lista utilizando el comando

mysqlshow db_name. Antes de MySQL 5.0.1, la salida de SHOW TABLES contenía

una única columna con los nombres de las tablas. A partir de MySQL 5.0.1,

también se listan las vistas de la base de datos. A partir de MySQL 5.0.2, se

soporta el modificador FULL de modo que SHOW FULL TABLES muestra una

segunda columna. Los valores de esta segunda columna son BASE TABLE para

una tabla y VIEW para una vista.

Nota: si no se dispone de privilegios para una tabla, la tabla no será

mostrada en la salida de SHOW TABLES o mysqlshow db_name.

Con SHOW OPEN TABLES se listan las tablas que se encuentren actualmente

abiertas en la caché de tablas. El campo de comentario en la salida indica las

veces que la tabla está en el caché y en uso. OPEN puede usarse a partir de

MySQL 3.23.33.

Pág. 80 de 142


UNIDAD 4: Transacciones

Objetivo de la Unidad: Utilizar las funciones especificas para realizar

transacciones en MySQL de forma eficiente.

Pág. 81 de 142


Temas:

Transacciones

Estructuras básicas de una transacción

Ejecución de transacciones en un servidor MySQL

Transacciones

Con las transacciones se añade una fiabilidad superior a las bases de

datos. Con el uso de transacciones se puede tener la seguridad de que nunca se

quedará a medio camino de su ejecución, si se dispone de una serie de consultas

SQL que deben ejecutarse en conjunto. De hecho, se puede decir que las

transacciones aportan una característica de "deshacer" a las aplicaciones de

bases de datos.

Teniendo esto en mente, las tablas que soportan transacciones, como es el

caso de InnoDB, son mucho más seguras y fáciles de recuperar si se produce

algún fallo en el servidor, ya que las consultas se ejecutan o no en su totalidad.

Por otra parte, las transacciones pueden hacer que las consultas tarden más

tiempo en ejecutarse.

Estructuras básicas de una transacción

Los pasos para utilizar las transacciones en MySQL son:

Pág. 82 de 142


Iniciar una transacción con el uso de la sentencia BEGIN.

Actualizar, insertar o eliminar registros en la base de datos.

Si se desea realizar cambios a la base de datos, se completa la transacción

con el uso de la sentencia COMMIT. Sólo cuando se procesa un COMMIT, los

cambios hechos por las consultas serán permanentes.

En caso de que ocurra algún problema, se puede hacer uso de la sentencia

ROLLBACK para cancelar los cambios que han sido realizados por las consultas

que han sido ejecutadas hasta el momento.

Los ejemplos presentados a continuación ejecutan algunas consultas para

ver como trabajan las transacciones. Lo primero que se debe hacer es crear una

tabla del tipo InnoDB e insertar algunos datos.

Si se desea crear una tabla InnoDB se utiliza el SQL estándar CREATE

TABLE, pero se debe especificar que se trata de una tabla del tipo InnoDB

(TYPE= InnoDB). Esto es aplicable a cualquier tipo de tabla, pero cuando no se

especifica nada, MySQL supone que se trata de una tabla MyISAM.

mysql> CREATE TABLE innotest (campo INT NOT NULL PRIMARY KEY) TYPE

= InnoDB;

Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO innotest VALUES(1);




Pág. 83 de 142




mysql> SELECT * FROM innotest;

+-------+

| campo |

+-------+

| 1 |

| 2 |

| 3 |

+-------+

3 rows in set (0.00 sec)

Al utilizar las transacciones.

mysql> BEGIN;





+-------+Esto es aplicable a cualquier tipo de tabla, pero cuando no se

especifica nada, MySQL supone que se trata de una tabla MyISAM.

| campo |

+-------+

Pág. 84 de 142


| 1 |

| 2 |

| 3 |

| 4 |

+-------+


Ahora, al ejecutar un ROLLBACK, no se completará la transacción y los

cambios realizados sobre la tabla no tendrán efecto.

mysql> ROLLBACK;



+-------+

| campo |

+-------+

| 1 |

| 2 |

| 3 |

+-------+


Entonces, ¿qué sucede si se pierde la conexión al servidor antes de que la

transacción sea completada?.

Pág. 85 de 142


mysql> BEGIN;





+-------+

| campo |

+-------+

| 1 |

| 2 |

| 3 |

| 4 |

+-------+


mysql> EXIT;

Bye

Al obtener de nuevo la conexión, se puede verificar que el registro no se

insertó ya que la transacción no fue completada.

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 449 to server version: 4.0.13

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Pág. 86 de 142



+-------+

| campo |

+-------+

| 1 |

| 2 |

| 3 |

+-------+


Luego, se repite la sentencia INSERT ejecutada antes, pero con un

COMMIT antes de perder la conexión al servidor al salir del monitor de MySQL.

mysql> BEGIN;




mysql> COMMIT;


mysql> EXIT;

Bye

Pág. 87 de 142


Al realizar un COMMIT, la transacción se completa, y todas las sentencias

SQL que se ejecutaron antes afectan de manera permanente las tablas de la

base de datos.





+-------+

| campo |

+-------+

| 1 |

| 2 |

| 3 |

| 4 |

+-------+


Otras consideraciones

MySQL se ejecuta en modo autocommit por defecto. Esto significa que tan

pronto como se ejecuta una sentencia se actualiza o modifica la tabla, entonces,

MySQL almacenará la actualización en disco.

Pág. 88 de 142


Al utilizar las tablas de transacción segura, como InnoDB o BDB, se puede

poner MySQL en modo no-autocommit utilizando el siguiente comando:

SET AUTOCOMMIT=0

Al desconectar el modo autocommit asignando cero a la variable

AUTOCOMMIT, se debe usar COMMIT para almacenar los cambios en disco o

ROLLBACK, si se quieren ignorar los cambios hechos desde el principio de la

transacción.

Si se desea desactivar el modo autocommit para una serie de sentencias,

se puede usar una sentencia START TRANSACTION:

START TRANSACTION;

SELECT @A:=SUM(salary) FROM table1 WHERE type=1;

UPDATE table2 SET summmary=@A WHERE type=1;

COMMIT;

Se puede usar BEGIN y BEGIN WORK en lugar de START TRANSACTION

para iniciar una transacción. START TRANSACTION fue añadido en MySQL 4.0.11;

es la sintaxis SQL-99 y es el modo recomendado para empezar una transacción.

Pág. 89 de 142


BEGIN y BEGIN WORK están disponibles desde MySQL 3.23.17 y 3.23.19,

respectivamente.

Si no se están usando tablas de transacción segura, cualquier cambio será

almacenado inmediatamente, independientemente del estado del modo

autocommit.

Al utilizar una sentencia ROLLBACK después de actualizar una tabla no

transaccional, se obtendrá un error (ER_WARNING_NOT_COMPLETE_ROLLBACK)

en forma de aviso. Todas las tablas de transacción segura serán restauradas,

pero cualquier tabla de transacción no segura no cambiará.

Si se quiere utilizar START TRANSACTION o SET AUTOCOMMIT=0, se debe

utilizar el diario binario MySQL para copias de seguridad en lugar del diario de

actualización antiguo. Las transacciones se almacenan en el diario binario de

una sola vez, después de COMMIT, para asegurar que las transacciones que se

han rebobinado no se almacenen.

Ejecución de transacciones en un servidor

Si se desean utilizar las transacciones se requiere un servidor MySQL con

soporte para el tipo de tablas InnoDB. En nuestro caso se hace uso de un

servidor MySQL 4.013 ejecutándose en un sistema.

Pág. 90 de 142


Para asegurarse que tenemos soporte para el tipo de tablas InnoDB se

puede ejecutar la sentencia presentada a continuación:

mysql> SHOW VARIABLES LIKE '%innodb%';

+---------------------------------+------------+

| Variable_name |ValueEn nuestro caso se hace uso de un servidor MySQL 4.013

ejecutándose en un sistema. |

+---------------------------------+------------+

| have_innodb | YES |

| innodb_additional_mem_pool_size | 1048576 |

| innodb_buffer_pool_size | 8388608 |

| innodb_data_file_path | ibdata:30M |

| innodb_data_home_dir | |

| innodb_file_io_threads | 4 |

| innodb_force_recovery | 0 |

| innodb_thread_concurrency | 8 |

| innodb_flush_log_at_trx_commit | 1 |

| innodb_fast_shutdown | ON |

| innodb_flush_method | |

| innodb_lock_wait_timeout | 50 |

| innodb_log_arch_dir | .\ |

| innodb_log_archive | OFF |

| innodb_log_buffer_size | 1048576 |

| innodb_log_file_size | 5242880 |

Pág. 91 de 142


| innodb_log_files_in_group | 2 |

| innodb_log_group_home_dir | .\ |

| innodb_mirrored_log_groups | 1 |

| innodb_max_dirty_pages_pct | 90 |

+---------------------------------+------------+


La variable más importante es por supuesto have_innodb que tiene el

valor YES.

De hecho, una de las principales características de las tablas del tipo

InnoDB es que pueden trabajar con transacciones, o sentencias SQL que son

agrupadas como una sola. Un ejemplo típico de esto es una transacción

bancaria. Por ejemplo, si una cantidad de dinero es transferida de la cuenta de

una persona a otra, se requerirán por lo menos dos consultas:

UPDATE cuentas SET balance = balance - cantidad_transferida WHERE

cliente = persona1;

UPDATE cuentas SET balance = balance + cantidad_transferida WHERE cliente =

persona2

Las dos consultas mencionadas antes deben trabajar bien, ¿pero qué

sucede si ocurre algún imprevisto y "se cae" el sistema después de que se

ejecuta la primer consulta, pero la segunda aún no se ha completado?. La

Pág. 92 de 142


persona1 tendrá una cantidad de dinero removida de su cuenta, y creerá que ha

realizado su pago, sin embargo, la persona2 estará enfadada puesto que

pensará que no se le ha depositado el dinero que le deben. En este ejemplo tan

sencillo se ilustra la necesidad de que las consultas sean ejecutadas de manera

conjunta, o en su caso, que no se ejecute ninguna de ellas. Debido a esto es que

transacciones toman un papel muy importante.

Welcome to the MySQL monitor.

Pág. 93 de 142


UNIDAD 5: Acceso remoto a MySQL

Objetivo de la Unidad: Utilizar las opciones disponibles para administrar

un servidor MySQL de forma remota

Temas:

Acceso remoto a MySQL

Usando telnet/SSH

TCP/IP

Acceso Remoto

Ya que MySQL utiliza conexiones no encriptadas, o inseguras, entre el

cliente y el servidor de manera predeterminada, significa que cualquier individuo

mal intencionado puede ver, y aún modificar los datos que son transmitidos

entre sí. Esta situación puede ser peligrosa, dependiendo del tipo de información

Pág. 94 de 142


que se está manejando, por lo tanto, puede ser necesario buscar un método o

mecanismo que permita asegurar que los datos que son transmitidos a través de

una red son confiables.

El SSL es un protocolo que utiliza diferentes algoritmos de encriptación

para asegurarse de que los datos que viajan a través de una red pública, por

ejemplo la Internet, pueden ser confiables. En la versión 4.0, MySQL se ofrecerá

soporte nativo para el SSL, por los momentos, en las versiones 3.23.x de MySQL

se puede utilizar el SSH para crear un canal de comunicación seguro, es decir, al

utilizar el SSH se puede establecer una conexión encriptada entre un cliente y

un servidor MySQL. El SSH es un protocolo que proporciona autenticación,

encriptación e integridad de datos para garantizar las comunicaciones en una

red. En principio es una solución bastante efectiva para resolver el problema de

la inseguridad de los datos que viajan sobre una red.

Lo que se pretende es dar a conocer cuáles son los pasos a seguir para

poder crear un canal de comunicación seguro entre un cliente y un servidor

MySQL utilizando el protocolo SSH.

Port Forwarding

El SSH ofrece una herramienta muy poderosa llamada Port Forwarding,

que permite asegurar prácticamente cualquier tipo de servicio basado en el

Pág. 95 de 142


protocolo TCP/IP. Esto significa que, por ejemplo, se pueden encriptar los datos

que manejan los protocolos de correo electrónico más utilizados, como IMAP,

POP3 y SMTP. De hecho, con el Port Forwarding se podría encriptar la

comunicación entre una computadora local, y un servidor de correo POP3 el cual

con seguridad se encontraría ejecutando un servidor SSH al mismo tiempo.

En la actualidad existen dos tipos de Port Forwarding, el local y el remoto.

En el Port Forwarding local, del que se hablará en esta documentación, los datos

que llegan a un puerto en un host local se reenvían a un puerto en un host

remoto sobre un canal seguro, o "túnel". En este tipo de Port Forwarding el túnel

lo crea, o lo inicia, el cliente.

Si se desea utilizar el Port Forwarding, la aplicación cliente debe ser

configurada para conectarse a hostlocal:puertolocal en lugar de

hostremoto:puertoremoto. La información enviada por un cliente a

hostlocal:puertlocal es interceptada por el SSH, y enviada a través del túnel al

hostremoto:puertoremoto.

En la mayoría de los casos, el puerto local puede ser cualquier puerto que

no esté siendo usado en el host local, sin embargo, el puerto remoto

generalmente es un puerto "bien conocido" (3306 para MySQL).

Entorno seguro para MySQL

Pág. 96 de 142


Si se presenta una situación bastante común y real, en la cuál una

aplicación Web requiere acceso a una base de datos MySQL para su

funcionamiento, el Port Forwarding puede ser bastante útil.

Desde el servidor Web, o el cliente, se envía y se recibe la información de

alguna base de datos en un servidor MySQL. Como se explicó antes, está

información que se transmite no se encuentra protegida, y por lo tanto no es

confiable, ya que MySQL no cuenta con mecanismos para encriptar los datos.

Para solucionar este problema, se puede hacer uso del Secure Shell para

crear un túnel entre el servidor Web y el servidor MySQL, en caso de que se tu

tuviera un servidor de bases de datos MySQL ejecutándose localmente en el

servidor Web. En realidad lo que sucede es que los datos que llegan al puerto

3306 en el servidor Web son enviados de forma segura al puerto 3306 en el

servidor de bases de datos a través del túnel.

Mecanismos de autenticación en SSH

Antes de la creación propiamente dicha del túnel se hará referencia a la

autenticación de llave pública. Éste es uno de los métodos más seguros para

autenticar cuando se utiliza el Secure Shell. En este tipo de autenticación utiliza

Pág. 97 de 142


un par de llaves generadas por computadora, una llave pública y una privada.

Cada llave normalmente posee entre 1024 y 2048 bits de longitud.

1. Ejemplo de una llave privada del tipo RSA.

-----BEGIN RSA PRIVATE KEY-----

MIICWQIBAAKBgQC63MxNWlknrELe6leK2ETGNKbaM2z0bSlEan7LgL+DA5lkZ

x2g

1M/sc3ix42+mSVz6qvhFsNiXQWXU98lM8R9CNb67lxHD5mdcQoaiOoIC6NTzC

tvV

vrUaUm+p5bvdSh28VF2wD/WVboufUhLoSDh7G/BPqv5s2ZVwSs2MtKRG5QI

BIwKB

gCAImA1CsDKv0PMDmfqLeYEQVx4XccN/ve54qAWhCuNCfhE2P5fp98IibHZEP

waQ

eTWh1+6re/hWQcU4uadofeWYW8Oz7lTeyMVrP+BvnZIjakEA5ba8//4LJtWR+4

4s

dvExzvHK5hhG7d0NTxzXq9V7V+CqTEuE65hatJgmrhd/m4fX3m/8b3ANPUj0C

+lt

tT/

+GwJBANA+xK19paFuv4IJAhCHGGcEMuJNkcdgjgCBvFP/gpOkroKmkDwKEhge

ei5rGCB85+9pCEygGzNj/KKaLHRNHv8CQFVSgLbas69Wo+

+4dupZlihLLhrzEwfk

Pág. 98 de 142


ZAdxHOgNdvTBKU+Jw6f2wprZbXPc16ArJEtOKo/KiJMbGNE5ceQ8VxECQE1ZJH

r0

J5O7a7P0t5+9JlImIYdBYgg5zlf1hDx02Lp+/v1Tzyw+QTwok8gZJjfz4R5o1zJn

Wo9uR+SfqhyRrG0CQCKgbvVgBkXr+aeg3X1nhjwzQl00gmWm53cUNTm/HW

mq3v6F

Yyz+8fnkEuB+jDozOFeV6cTlXpVkLSDxGwjH2R8=

-----END RSA PRIVATE KEY-----

Una llave pública es inútil si no se cuenta con la correspondiente llave

privada.

Para poder accesar a un servidor vía SSH, use debe poner en el servidor

una copia de la llave pública. Cuando el cliente se conecta a éster, el servidor

debe ofrecer su llave privada para que el acceso al mismo le sea permitido.

Como es de imaginarse, la llave privada debe ser bien protegida y debe

conservarse en un lugar seguro. En este tipo de autenticación nunca se le

permite el acceso a un cliente a menos de que proporcione la "clave secreta", es

decir su llave privada.

Pág. 99 de 142


Lo que se hará a continuación es crear un par de llaves RSA sin

passphrase para permitir que el túnel SSH pueda iniciar de forma automática.

Para hacerlo, se debe hacer lo siguiente:Welcome to the MySQL monitor.

[bingo@webserver]$ ssh-keygen -b 1024 -t rsa

Generating public/private rsa key pair.

Enter file in which to save the key (/home/bingo/.ssh/id_rsa):

Enter passphrase (empty for no passphrase):

Enter same passphrase again:

Your identification has been saved in /home/bingo/.ssh/id_rsa.

Your public key has been saved in /home/bingo/.ssh/id_rsa.pub.

[bingo@webserver]$ scp .ssh/id_rsa.pub dbserver:

[bingo@dbserver]$ cd ~

[bingo@dbserver]$ cat id_rsa.pub >> .ssh/authorized_keys2

[bingo@dbserver]$ chmod -R go-rwx .ssh

[bingo@dbserver]$ rm id_rsa.pub

Pág. 100 de 142


Se verifica que se pueda acceder al servidor de bases de datos sin

proporcionar un password.

[bingo@webserver]$ ssh dbserver

Last login: Wed Nov 12 10:20:41 2002

bash-2.05$

No se necesita que esta conexión se mantenga abierta para que funcione

el Port Forwarding, es sólo para probar que nos podemos conectar vía SSH al

servidor de bases de datos sin proporcionar un password. Una llave privada es el

medio de entrada al servidor.

bash-2.05$ exit

logout

Connection to dbserver closed.

[bingo@webserver]$

Configuración del Port Forwarding

Pág. 101 de 142


En este momento se va a crear el túnel. El comando para abrir el túnel

SSH se presenta de la siguiente forma:

[bingo@webserver]$ ssh -f -q -N -L3306:dbserver:3306 dbserver

En la tabla presentada a continuación se presenta la descripción de las

opciones utilizadas con el comando ssh.

-f: le indica a ssh que se ejecute en segundo plano (background).

-q: se utiliza para que no se muestren las advertencias y mensajes.

-N: se utiliza para que ssh no permita ejecutar comandos remotos a través

de la conexión. Es útil cuando se está utilizando el Port Forwarding.

-L: ésta es la opción principal que hace posible el Port Forwarding local.

Indica que el puerto especificado en el host local, el cliente, tiene que ser

"forwardeado" (reenviado) al puerto remoto en el host remoto.

Entonces, se puede probar el acceso a alguna base de datos MySQL

desde el servidor Web utilizando el túnel SSH. Podría ser necesario tener los

permisos ajustados de forma correcta en MySQL para que el nombre de usuario

y la contraseña que se utilicen permitan establecer la conexión a MySQL.

Pág. 102 de 142


[bingo@webserver]$ mysqlshow -h 127.0.0.1 -P 3306 -u bingo -p

Enter password: ********

+--------------+

| Databases |

+--------------+

| agendita |

| apache |

| multimail |

| mysql |

| zoologico |

| test |

+--------------+

[bingo@webserver]$ mysql -h 127.0.0.1 -P 3306 -u bingo -p zoologico

Enter password: ********

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A


Pág. 103 de 142




mysql> show tables;

+---------------------+

| Tables_in_zoologico |

+---------------------+

| mascotas |

+---------------------+

1 row in set (0.00 sec)

mysql> exit

Bye

[bingo@webserver]$

Es muy importante resaltar que se tiene que modificar el código de las

aplicaciones que hacen uso de MySQL desde el servidor Web, para realizar la

conexión por medio del túnel SSH, en lugar de conectarse directamente al

servidor de bases de datos por un canal inseguro.

Pág. 104 de 142


Por ejemplo, en PHP se tendría lo siguiente:

$host = "dbserver";

$user = "bingo";

$passwd = "holahola";

$dbname = "mibase";

$conn = mysql_connect("$host","$user","$passwd");

mysql_select_db("$dbname");

....

En principio, lo único que se debería cambiar es la variable $host

$host = "127.0.0.1:3306";

$user = "bingo";

$passwd = "holahola";

$dbname = "mibase";

Pág. 105 de 142


$conn = mysql_connect("$host","$user","$passwd");

mysql_select_db("$dbname");

En este momento, los datos que se envían y se reciben de MySQL

viajarán por el túnel SSH, por lo tanto, se puede estar seguro de la confiabilidad

de los datos que se trasmiten entre el servidor Web y el servidor de bases de

datos MySQL.

Para terminar, es importante recordar que si el túnel SSH se cae, las

aplicaciones Web que lo estén utilizando no podrán acceder a las base de datos

en el servidor MySQL.

Observaciones finales

No hay ningún problema si la seguridad, o la confiabilidad ofrecidas por el

Port Forwarding no son suficiente, y se requiere todavía hacer uso de algún

firewall para proteger el servidor de bases de datos. Con esto se quiere decir

que aunque el servidor MySQL se encuentre detrás de un firewall, todavía puede

seguir utilizándose el Port Forwarding.

Con lo único que se debe cumplir es que en el firewall se abra el puerto

22, el puerto de Secure Shell, y de esta forma crear el túnel entre el servidor

Web y el servidor de bases de datos.

Pág. 106 de 142


En concreto, esta es una de las formas más seguras con las que se puede

tener trabajando una aplicación Web distribuida. Por una lado, no se

compromete la seguridad del servidor de bases de datos al ponerlo detrás de un

firewall, y de esta manera protegerlo, y por otro lado, se garantiza la

confiabilidad de los datos enviados desde y hacía el servidor MySQL ya que éstos

son transmitidos a través del túnel SSH.

UNIDAD 6: Administración avanzada de MySQL

Objetivo de la Unidad: Emplear las instrucciones disponibles para mejorar

y optimizar el rendimiento de un servidor MySQL

Pág. 107 de 142


Temas:

Optimización de Tablas y Base de datos

Mostrar y administrar procesos

Exportar las tablas y bases de datos a otros formatos

Optimización de tablas y Base de datos

Ya que el papel de MySQL ha venido creciendo en las grandes

organizaciones, su uso se está convirtiendo más y más de alto rendimiento. Esto

quiere decir que MySQL necesita poder ofrecer una mayor capacidad de

respuesta, alto rendimiento, y fiabilidad. Mysql es muy conocido en la industria

por ser un servidor de bases de datos increíblemente rápido, es normal que

MySQL se encuentre preparado para desempeñar su trabajo justo después de

instalarlo. Sin embargo, existen un par de aspectos que pueden fácilmente

hacerlo lento, aunque no es extraño que esto se deba también a un pobre

diseño de la aplicación. En algunos casos la configuración por defecto de MySQL

no es lo suficientemente buena como para desempeñar la tarea que se desea

llevar a cabo. Y otras veces se necesita tener un poco más de hardware.

Al momento de intentar hacer más rápida una aplicación de bases de

datos, se debe comenzar con la aplicación en sí y asegurarse de que las tablas

Pág. 108 de 142


se encuentren normalizadas de forma adecuada, y las columnas se encuentren

indexadas, esto es siempre un buen comienzo. Pero si ya se ha hecho todo lo

anterior y las cosas siguen siendo lentas, llega el momento de echarle un ojo al

servidor MySQL en sí.

Aunque parezca intimidante, el ajuste del rendimiento sólo trata de sacar

el mayor rendimiento posible de un sistema. Para lograrlo es necesario entender

cuales son las variables que se encuentran involucradas y como éstas pueden

afectar el buen funcionamiento del servidor.

Antes de entrar en detalle, vale la pena recordar un hecho importante: las

técnicas que se van a mostrar no arreglarán búsquedas mal escritas o sin

optimizar, o un mal diseño de la base de datos, u otros problemas del diseño de

la aplicación. Puede que sirvan para aliviar el esfuerzo de un servidor ocupado,

pero simplemente se está posponiendo lo inevitable. La única solución a una

aplicación mal escrita o un diseño pobre de la base de datos es irse al código y

arreglarlo. En realidad, al arreglar las consultas lentas y/o una aplicación

pobremente diseñada se conseguirán mejores resultados, en lugar de perder el

tiempo con el ajuste del servidor.

Si no se está seguro de por donde se debe comenzar, se tiene que habilitar

el archivo de registro para las búsquedas lentas (slow query log) tal y como se

explica en el manual de MySQL, y luego sólo se debe observar y revisar

cualquier búsqueda que no se esperaba que fuese lenta. También es posible que

Pág. 109 de 142


se encuentren algunas búsquedas rápidas en el archivo de las búsquedas lentas.

Esto se debe a que MySQL considera cualquier búsqueda como lenta si no se

utiliza un índice.

Uso de memoria

Del lado del servidor, el único factor y el más importante, para determinar

cómo rendirá MySQL, es la memoria. MySQL es capaz de ejecutar varios

subprocesos a la vez. Esto significa que cada vez que se realiza una conexión,

MySQL crea un subproceso, y cada subproceso consume memoria. El

almacenamiento en la caché de los resultados también consume memoria. Se

puede pensar entonces, que entre más memoria tengamos en el servidor, éste

mejorará su desempeño. Sin embargo no es suficiente con tener mucha

memoria disponible, es necesario indicarle a MySQL como queremos que utilice

la memoria.

Las configuraciones por defecto de MySQL son bastante conservadoras

para el hardware de hoy en día, sin embargo, si se tiene un servidor MySQL

dedicado con varios cientos de mega bytes de RAM, se debe ser capaz de darle a

MySQL una porción bastante grande de ella para trabajar. Por defecto, sólo se

utilizará una pequeña porción de lo que se encuentre disponible; esto se debe a

que no hay ninguna forma de saber si se está ejecutando en un servidor

dedicado donde será utilizado de forma continua, o si se está ejecutando en un

esforzado portátil donde sólo se utiliza para almacenar una pequeña aplicación.

Pág. 110 de 142


Gran parte de la información presentada a continuación se centrará en el

uso de la memoria y se asume que se está utilizando el tipo de tabla por defecto

de MySQL, MyISAM. Hoy en día existen otros tipos de tablas transaccionales más

avanzadas, tales como InnoDB o Gemini.

Ya que MySQL utiliza la memoria para una gran variedad de búfferes

internos y cachés que influyen en el número de veces que se ha de acceder a

archivos que residen en el disco, cuanto más a menudo tenga que esperar a que

responda un disco, más lento será. Aún los discos duros más modernos siguen

siendo un orden de magnitud más lento que la memoria RAM, y dado la reciente

baja en los precios de la memoria, es muy factible que se pueda añadir más

memoria al servidor y así acelerar los procesos. La última opción a considerar

sería actualizar el equipo con discos duros más rápidos.

Los búfferes y cachés de MySQL son de dos tipos: globales, y por hilo.

Los globales, tal y como lo indica su nombre, son áreas de memoria

reservadas una vez y son compartidas a través de todos los hilos de MySQL. Dos

de los más importantes son el búffer de claves y la caché de tablas. El objetivo

es que sean lo más grandes posibles ya que son búfferes compartidos.

Pág. 111 de 142


En el caso por hilo, son búfferes que reservan memoria de forma individual

a medida que necesitan realizar operaciones particulares, tales como ordenar o

agrupar datos. Con respecto lo antes mencionado, la mayoría de los búfferes

MySQL se reservan en esta forma.

Primero se examinará que función tienen cada uno de los búfEn nuestro

caso se hace uso de un servidor MySQL 4.013 ejecutándose en un sistema.feres

y como configurar e inspeccionar sus valores, luego, se mostrará como examinar

los contadores de rendimiento de MySQL y juzgar si los cambios que se realizan

tienen implicaciones o no.

Búffer de claves

Este tipo de búffer es donde MySQL realiza la caché de los bloques de

índices para las tablas MyISAM. MySQL se fijará antes que nada si el índice

relevante se encuentra o no en la memoria, cada vez que una búsqueda utiliza

un índice. El parámetro key_buffer en el archivo my.cnf determina que tan

grande puede ser dicho búffer. Una vez que el búffer se encuentre lleno, MySQL

creará espacio para los nuevos datos reemplazando los datos antiguos que no

hayan sido utilizados recientemente.

El tamaño del búffer de claves aparece como key_buffer_size en la salida

de SHOW VARIABLES. Con un búffer de claves 384 Mega Bytes, se vería algo

como:

Pág. 112 de 142


key_buffer_size 402649088

Generalmente se recomienda que en un servidor MySQL dedicado se

debería reservar entre el 20 y el 50 por ciento de la memoria RAM para el búffer

de claves de MySQL. Si se tiene un giga byte de memoria se puede empezar con

algo como:

set-variable= key_buffer= 128M

ó incluso:

set-variable= key_buffer= 256M

Lo primero a tomar en cuenta sería el búffer de claves, en caso de que sólo

se permitiera modificar un parámetro en el servidor MySQL. También, los índices

son muy importantes para el rendimiento global de cualquier servidor de bases

de datos, por lo que es difícil equivocarse al hacer más espacio en su memoria

para ellos.

MySQL usará su tamaño por defecto que está cerca de los 8MB, si no se

especifica un tamaño al búffer de claves. Pero en realidad tiene muy poco

Pág. 113 de 142


sentido configurar el valor del búffer de claves tan alto, al hacerlo se podría

reducir el desempeño del sistema operativo con respecto a la memoria que

necesita para escrituras de disco y otras tareas.

Caché de tablas

Las tablas MyISAM se componen de tres archivos en disco:

Estos son, el archivo de datos nombredetabla.MYD, el archivo índice

nombredetabla.MYI, y finalmente, el archivo de definición de la tabla llamado

nombredetabla.FRM. MySQL necesita de hecho abrir los tres archivos si desea

utilizar una única tabla. El archivo .FRM se cerrará después de que lea el

esquema, pero los demás permanecerán abiertos, MySQL no los cerrará hasta

que lo necesite. Si la tabla se utiliza frecuentemente, esto evita una sobrecarga

asociada con la apertura y cierre de los archivos. Los archivos normalmente no

se suelen cerrar hasta que ocurre uno de los siguientes eventos:

1. La tabla se ha cerrado de forma explícita mediante FLUSH TABLES.

2. La tabla se ha desechado

3. El servidor está siendo reiniciadoWelcome to the MySQL monitor.

4. El número total de tablas abiertas ha alcanzado el valor del parámetro

table_cache

Pág. 114 de 142


Este último evento es importante en particular si se tienen muchas tablas

que son utilizadas a menudo entre todas las bases de datos. El valor por defecto

de table_cache es de 64, MySQL va a desperdiciar mucho tiempo y esfuerzo

abriendo y cerrando innecesariamente estos archivos, si se tienen unos cientos

de tablas que se usen de forma activa,

Al Incrementar el tamaño de la caché de tablas en realidad ayudaría en

este caso, pero se debe tener cuidado de no hacer el valor demasiado grande,

ya que todos los sistemas operativos tienen un límite en el número de los

archivos abiertos por un mismo proceso. De hecho, algunos también tienen

limitado el número total de archivos abiertos que puede tener un único usuario.

Si MySQL intenta abrir demasiados archivos, el sistema operativo se negará a

permitirlo y MySQL generará un mensaje de error en el archivo de registro de

errores. Ante la duda, se tienen que comprobar las limitaciones del sistema

operativo.

En los casos más extremos, se puede incrementar el número de

descriptores de archivos disponibles por medio de las opciones de configuración

del kernel. Los descriptores de archivos abiertos se encuentran reservados por

un único proceso, y compartidos por todos sus hilos. A diferencia de los muchos

otros parámetros, la caché de tablas se aplica a todos los tipos de tablas

basadas en disco de MySQL.

Búffer de registro

Pág. 115 de 142


Siempre que MySQL deba escanear una tabla, el hilo que realiza el

escaneo reservará un búffer de registro para cada tabla que se deba escanear.

Esto sucede normalmente cuando MySQL decide que es más eficiente escanear

la tabla que utilizar un índice para una búsqueda. También ocurre cuando

simplemente no hay un índice que se pueda utilizar.

Al incrementar el valor de record_buffer en el archivo my.cnf, se permite

que MySQL lea las tablas en pedazos más grandes. Es probable que esto reduzca

el número de búsquedas en el disco y haga que el escaneo sea

significativamente más rápido en un servidor muy atareado.

Sin embargo, se debe que ser muy cuidadoso con el búffer de registro si se

tienen muchos clientes que realizan búsquedas completas sobre tablas. Debido

a que el búffer de registro se reserva por cada hilo, se puede acabar en una

situación donde clientes individuales hagan que se reserven búfferes de registro

al mismo tiempo. Si el resto de la memoria está limitada es probable que se

empiece a hacer uso de la memoria de intercambio y el rendimiento se verá

dramáticamente reducido. En la versión 3.23.41 se introdujo un parámetro

relacionado denominado record_rnd_buffer.

Al igual que record_buffer, se utiliza para escanear un gran número de

filas. El record_rnd_buffer se utiliza para realizar las búsquedas que resultan en

una ordenación intermedia del archivo, además de algunas lecturas de registro

Pág. 116 de 142


no secuenciales. Afortunadamente, si no se fija el valor de record_rnd_buffer se

establecerá por defecto el valor de record_buffer.

Búffer de ordenación

Tal y como lo indica su nombre, el búffer de ordenación se utiliza para

responder a las búsquedas que involucran el ordenamiento de los datos,

aquellas que contengan una sentencia ORDER BY. Además, el búffer de

ordenación se se utiliza para las búsquedas que involucran el agrupamiento de

datos, aquellas con una sentencia GROUP BY. Como con los demás tipos de

búfferes vistos hasta ahora, el búffer de ordenación es relativamente pequeño

por defecto. Al ajustar la entrada de sort_buffer en el archivo my.cnf:

set-variable= sort_buffer= 8M

Se puede reducir la cantidad de tiempo que se consume para ordenar

grandes grupos de resultados. El búffer de ordenación aparece como sort_buffer

en la salida de SHOW VARIABLES, por ejemplo:

sort_buffer 8388600

El tipo de aviso que se aplica al búffer de ordenación se aplica al búffer de

registros. Es un búffer que MySQL reserva normalmente por hilo. Así que, hay

Pág. 117 de 142


que incrementarlo con cuidado en un servidor que ejecute muchas búsquedas

concurrentes.

Pautas generales de ajuste

Antes de entrar en la discusión sobre como medir o juzgar los efectos de

cualquier cambio que se lleve a cabo, se debe considerar brevemente un

acercamiento a la afinación del rendimiento. Existen varios aspectos que se

deben tener en cuenta cuando se empiezan hacer y probar cambios:

1. Sólo se debe cambiar un parámetro cada vez. Puede que los cambios

no resulten siempre en el comportamiento esperado. Si se cambian demasiados

parámetros a la vez, se corre el riesgo de asignar un cambio en el

comportamiento al parámetro equivocado.

2. No hacer cambios en sistemas en producción. Si es del todo posible, se

debe tener un servidor de pruebas disponible que sea parecido en naturaleza al

servidor de bases de datos de producción. Si se hacen cambios en la

configuración de MySQL seguramente requerirá que se pare y reinicie el

servidor, lo que hará que los usuarios experimenten interrupciones en el mismo.

3. Utilizar datos reales. El tipo de datos que se estén utilizando afecta la

respuesta de MySQL con respecto a las búsquedas. Sería ideal que se utilice una

copia de las bases de datos de producción. Si no es posible hacerlo, entonces se

debería intentar construir un subconjunto representativo de datos.

Pág. 118 de 142


4. Realizar pruebas realistas. Es fácil asumir que se sabe que pruebas

aplicar sólo por el hecho de que se sabe cuales son las áreas problemáticas. Sin

embargo, algunos cambios de la configuración aceleran las partes lentas de una

aplicación al mismo tiempo que disminuyen la velocidad de las cosas que antes

eran bastante rápidas.

5. Ser sistemático y registrar descubrimientos. Es importante que se

mantenga la pista de los cambios que se realizan y como afectan al rendimiento.

Después de varias horas, o incluso días de pruebas, es más que probable que no

se recuerde exactamente que es lo que se ha cambiado y si los cambios fueron

positivos o negativos.

Observando los números de rendimiento de la base de datos

Al contar con pocos puntos de partida y un concepto de cómo hacer

pruebas, se debe ahora considerar cómo darle seguimiento al progreso.

Afortunadamente, MySQL cuenta con más de 50 contadores internos, o variables

de estado, que mantienen el seguimiento sobre cuántas veces ocurren

diferentes tipos de eventos.

En el manual de MySQL se describen todas y cada de las variables de

estado de MySQL en mayor detalle. Para ver estos números, se puede utilizar la

sentencia SHOW STATUS. En este caso se mencionan sólo las variables

relacionadas con el búffer de claves:

Pág. 119 de 142


SHOW STATUS LIKE 'Key%'

Key_read_requests 3844786889

key_reads 16525182

Key_write_requests 303516563

Key_writes 152315649

Gracias a estas cuatro variables se puede ver el rendimiento del búffer de

claves de MySQL. Cada vez que MySQL sea capaz de leer una clave, o índice, del

búffer de claves, en lugar de ir al disco, se incrementará el valor de

key_read_requests de forma automática. Si MySQL debe leer la clave del disco

porque no se encontraba en la caché, se incrementará key_reads. La misma

lógica se aplica para las escrituras de disco. Teniendo esto en cuenta, se puede

calcular la eficiencia, o hit rate, para el búffer de claves.

Al utilizar una fórmula como:

100 - ((Key_reads / Key_read_requests) * 100)

se puede obtener un porcentaje que representa cómo Mysql es capaz de

leer las claves directamente de la caché, en vez de irse a disco. Cuanto más

cerca ese encuentre el valor a 100, mucho mejor. Al utilizar los números antes

presentados, se tiene un hit rate de cerca del 99.57 por ciento. En general, suele

ser una buena idea mantener este porcentaje por encima del 90 por ciento. A fin

Pág. 120 de 142


de cuentas, de lo que se trata, es de tener una mejora medible del rendimiento

de MySQL.

Al observar los números de rendimiento del sistema, monitorear los

cambios de rendimiento en MySQL se vuelve sólo una parte del trabajo, también

es necesario ver qué es lo que está pasando desde el punto de vista del sistema

operativo, ya que como cualquier otra aplicación, se encuentra sometida a lo

que el sistema operativo quiera permitirle hacer, así que es importante que se

mantenga una vista global sobre toda la actividad del sistema operativo.

Antes de comenzar a realizar las pruebas, se debe tener una idea de la

actividad actual del sistema y de las características del rendimiento de MySQL.

Si no se cuenta con una base para la comparación, en realidad no se sabrá como

ha cambiado el impacto de MySQL en el sistema. Finalmente, cabe mencionar

que únicamente se ha descrito una mínima parte de lo que representa el

rendimiento en el lado del servidor para MySQL. El manual de MySQL contiene

muchas otras ideas sobre cómo incrementar el rendimiento de MySQL y darle

seguimiento los progresos.

Mostrar y administrar procesos

Pág. 121 de 142


Con SHOW PROCESSLIST se muestra qué procesos se encuentran en curso.

También se puede obtener esta información al utilizar el comando mysqladmin

processlist. Si se cuenta con el privilegio SUPER, se podrán ver todos los

procesos. De lo contrario, sólo será posible ver los propios procesos, es decir, los

procesos asociados con la cuenta MySQL que se está utilizando. Ver la sintaxis

de KILL. Al no utilizar la palabra clave FULL, sólo se muestran los 100 primeros

caracteres de cada consulta.

Desde la versión de MySQL 4.0.12, la sentencia informa sobre el nombre

de la máquina para conexiones TCP/IP que utiliza el formato

host_name:client_port para hacer más fácil poder determinar qué cliente está

haciendo cada cosa.

Esta sentencia es muy práctica si se obtiene el mensaje de error "too

many connections" y se quiere averiguar qué está ocurriendo. MySQL se reserva

una conexión extra para ser utilizada por las cuentas que tengan el privilegio

SUPER, para de esta forma asegurar que los administradores siempre tendrán la

posibilidad de conectar y verificar el sistema, asumiendo que no se ha dado tal

privilegio a todos los usuarios.

Algunos estados frecuentes en la salida de SHOW PROCESSLIST son:

Checking table: el proceso está realizando una comprobación, automática,

de la tabla.

Pág. 122 de 142


Closing tables: significa que el proceso está enviando los datos modificados

de la tabla al disco y cerrando las tablas utilizadas. Esto debe ser una operación

rápida, en caso contrario, se debe verificar que el disco no se encuentre lleno y

que el disco no está siendo muy utilizado.

Connect Out: es el esclavo conectando al maestro.

Copying to tmp table on disk: es el conjunto de resultados temporales que

son más grandes que tmp_table_size y el proceso está cambiando la tabla

temporal del formato en memoria al basado en disco, para ahorrar memoria.

Creating tmp table: el proceso está creando una tabla temporal para

almacenar una parte del resultado de una consulta.

deleting from main table: el servidor está ejecutando la primera parte de un

borrado multitabla y borrando sólo desde la primera tabla.

deleting from reference tables: el servidor se encuentra ejecutando la

segunda parte de un borrado multitabla y eliminando las filas coincidentes de

otras tablas.

Flushing tables: el proceso se encuentra ejecutando FLUSH TABLES y

esperando a que todos los procesos cierren sus tablas.

Killed: alguien ha enviado un 'kill' al proceso y se abortará la próxima vez

que verifique la marca de 'kill'. La marca se verifica en cada bucle exterior en

MySQL, pero en algunos casos podrá requerir un pequeño tiempo para que el

proceso se elimine. Si el proceso está bloqueado por otro, la finalización tendrá

efecto tan pronto como el otro proceso retire el bloqueo.

Locked: la consulta está bloqueada por otra consulta.

Pág. 123 de 142


Sending data: esta procesando filas para una sentencia SELECT y también

está enviando datos al cliente.

Sorting for group: el proceso está haciendo un ordenamiento para satisfacer

un GROUP BY.

Sorting for order: el proceso está haciendo un ordenamiento para satisfacer

un ORDER BY.

Opening tables: el proceso está intentado abrir una tabla. Este debe ser un

procedimiento muy rápido, a no ser que algo impida la apertura. Por ejemplo,

una sentencia ALTER TABLE o una sentencia LOCK TABLES pueden impedir la

apertura de una tabla hasta que la sentencia haya terminado.

Removing duplicates: la consulta ha usado SELECT DISTINCT de tal modo

que MySQL no puede optimizar la operación de distinción en una primera etapa.

Debido a esto, MySQL necesita una etapa extra para eliminar todas las filas

duplicadas antes de enviar el resultado al cliente.

Reopen table: el proceso ha obtenido un bloqueo para la tabla, pero se ha

notificado que después de obtenerlo la estructura de la tabla ha cambiado. Ha

liberado el bloqueo, cerrado la tabla y ahora está intentando reabrirla.

Repair by sorting: el código de reparado está utilizando un ordenamiento

para crear los índices.

Repair with keycache: el código de reparado se encuentra utilizando la

creación de claves una a una a través del caché de claves. Esto es mucho más

lento que 'Repair by sorting'.

Searching rows for update: el proceso se encuentra ejecutando una primera

fase para encontrar todas las filas coincidentes antes de actualizarlas. Esto tiene

Pág. 124 de 142


que hacerse si el UPDATE está modificando el índice que se utiliza para

encontrar las filas involucradas.

Sleeping: el proceso está esperando a que el cliente le envíe una nueva

sentencia.

System lock: el proceso se encuentra esperando obtener un bloque de

sistema externo para la tabla. Si no se están utilizando varios servidores mysqld

que estén accediendo a las mismas tablas, se pueden desactivar los bloqueos de

sistema con la opción --skip-external-locking.

Upgrading lock: el manipulador de INSERT DELAYED está intentando

obtener un bloqueo para la tabla para insertar filas.

Updating: el proceso está buscando filas para actualizar.

User Lock: el proceso está esperando un GET_LOCK().

Waiting for tables: el proceso tiene una notificación de que la estructura

para una tabla subyacente ha cambiado y necesita reabrir la tabla para obtener

la nueva estructura. Sin embargo, para que pueda reabrir la tabla, se debe

esperar hasta que otros procesos hayan cerrado la tabla en cuestión. Esta

notificación se produce si otro proceso ha usado FLUSH TABLES o una de las

siguientes sentencias para la tabla en cuestión: FLUSH TABLES tbl_name, ALTER

TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE u OPTIMIZE TABLE.

waiting for handler insert: El manipulador de INSERT DELAYED ha procesado

todas las inserciones pendientes y está esperando por más.

Pág. 125 de 142


Gran parte de estos estados se corresponden con operaciones muy

rápidas. Si un proceso permanece en cualquiera de esos estados por muchos

segundos, probablemente existe un problema que necesita ser investigado.

Existen otros estados que no se mencionan en la lista anterior, pero

muchos de ellos son útiles sólo para encontrar bugs (errores) en el servidor.

Si se desea cancelar algún proceso, se debe usar la instrucción KILL

KILL thread_id

Cada conexión a mysqld se ejecuta en un hilo separado. Se puede ver qué

hilo se está ejecutando con el comando SHOW PROCESSLIST y eliminarlo con el

comando KILL thread_id.

Si se cuenta con un privilegio PROCESS, se pueden ver todos los hilos. Si

se posee el privilegio SUPER, se pueden matar todos los hilos. En otro caso, sólo

es posible ver y eliminar los hilos propios.

También se pueden usar los comandos mysqladmin processlist y

mysqladmin kill par examinar y matar hilos.

Pág. 126 de 142


Nota: en la actualidad no se puede utilizar el comando KILL con la librería

del Servidor MySQL embebido, porque el servidor embebido simplemente se

ejecuta dentro de los hilos en el ordenador de la aplicación, no crea hilos de

conexiones por sí mismo. Al realizar un KILL, se activa un banderín de 'kill'

específico para el hilo.

En la mayoría de los casos, eliminar el hilo puede tomar un tiempo ya que

el flag 'kill' se verifica sólo cada cierto tiempo.

El bucle ORDER BY y GROUP BY del comando SELECT, el banderín se

comprueba después de leer un bloque de filas. Si el banderín de 'kill' está

activot, la sentencia se aborta.

Al realizar un ALTER TABLE, el banderín 'kill' se comprueba antes de que

cada bloque de filas se lea desde la tabla original. Si está activo el comando se

aborta y la tabla temporal se borra.

Al ejecutar las sentencias UPDATE o DELETE, el banderín se comprueba

después de cada lectura de bloque y después de cada actualización o borrado de

fila. Si se encuentra activo, la sentencia se aborta. Si no se están utilizando las

transacciones, los cambios no podrán ser rebobinados.

GET_LOCK() abortará con NULL.

Un hilo INSERT DELAYED almacenará rápidamente todas las filas que tenga

en memoria y morirá.

Pág. 127 de 142


Si el hilo se encuentra en la tabla de manipuladores de bloqueo (estado:

Locked), el bloqueo de la tabla será rápidamente abortado.

Si el hilo se encuentra esperando a que haya espacio libre en disco en una

llamada de escritura, la escritura se aborta con un mensaje de error de disco

lleno.

Exportar las tablas y base de datos a otros formatos

Con MySQL se pueden copiar las tablas en diferentes formatos de texto,

así como importar datos a partir de un archivo de texto en diferentes formatos.

Esto se puede utilizar para exportar los datos de nuestras bases de datos a otras

aplicaciones, o bien para importar datos desde otras fuentes a nuestras tablas.

También se puede utilizar para hacer copias de seguridad y restaurarlas

posteriormente.

Exportar a otros archivos.

Para extraer los datos desde una base de datos a un archivo se usa la

sentencia SELECT ... INTO OUTFILE.

El resto de las cláusulas de SELECT siguen siendo aplicables, la única

diferencia es que la salida de la selección se envía a un archivo en lugar de

hacerlo a la consola.

Pág. 128 de 142


La sintaxis de la parte INTO OUTFILE es:

[INTO OUTFILE 'file_name' export_options]

file_name es el nombre del archivo de salida. Éste no debe existir, ya que

en caso contrario la sentencia fallará.

En lo que respecta a las opciones de exportación, son las mismas que para

las cláusulas FIELDS y LINES de LOAD DATA. Su sintaxis es:

[FIELDS

[TERMINATED BY '\t']

[[OPTIONALLY] ENCLOSED BY '']

[ESCAPED BY '\\' ]

]

[LINES

[STARTING BY '']

[TERMINATED BY '\n']

]

Pág. 129 de 142


Estas cláusulas permiten la creación de diferentes formatos de archivos de

salida.

La cláusula FIELDS se refiere a las opciones de cada columna:

TERMINATED BY 'caracter': nos permite elegir el caracter delimitador que se

utilizará para seleccionar cada columna. Por defecto, el valor que se usa es el

tabulador, pero podemos usar ';', ',', etc.

[OPTIONALLY] ENCLOSED BY 'caracter': sirve para elegir el caracter utilizado

para entrecomillar cada columna. Por defecto no se entrecomilla ninguna

columna, pero se puede elegir cualquier caracter. Si se añade la palabra

OPTIONALLY sólo se entrecomillarán las columnas de texto y fecha.

ESCAPED BY 'caracter': sirve para indicar el caracter que se utilizará para

escapar de aquellos caracteres que pueden dificultar la lectura posterior del

archivo. Por ejemplo, si se terminan las columnas con ',' y no se entrecomillan,

un caracter ',' dentro de una columna de texto se interpretará como un

separador de columnas. Para evitar que esto suceda se puede sustituir esa coma

con otro caracter. Por defecto se usa el caracter '\'.

La cláusula LINES se refiere a las opciones para cada fila:

STARTING BY 'caracter': permite seleccionar el caracter para comenzar cada

línea. Por defecto no se usa ningún caracter para ello.

Pág. 130 de 142


TERMINATED BY 'caracter': permite elegir el caracter para terminar cada

línea. Por defecto es el retorno de línea, pero se puede usar cualquier otro

caracter o caracteres, por ejemplo '\r\n'.

Si se desea obtener un fichero de texto a partir de la tabla 'gente', con las

columnas delimitadas por ';', entrecomillando las columnas de texto con '"' y

separando cada fila por la secuencia '\r\n', se utilizará la sentencia presentada a

continuación:

mysql> SELECT * FROM gente

-> INTO OUTFILE "gente.txt"

-> FIELDS TERMINATED BY ';'

-> OPTIONALLY ENCLOSED BY '\"'

-> LINES TERMINATED BY '\n\r';


mysql>

El archivo de salida se verá de la siguiente forma:

"Fulano";"1974-04-12"

Pág. 131 de 142


"Mengano";"1978-06-15"

"Tulano";"2000-12-02"

"Pegano";"1993-02-10"

"Mengano";\N

La fecha para "Mengano" era NULL, para indicarlo se muestra el valor \N.

Pág. 132 de 142


UNIDAD 7: Migración a MySQL

Objetivo de la Unidad: Utilizar los métodos a seguir para importar

información de otros gestores de bases de datos a MySQL.

Temas:

Importación desde Microsoft SQL Server

Importación desde Microsoft Access

Pág. 133 de 142


Otras bases de datos u otras técnicas

Importación desde archivos CSV, TXT y otros formatos de texto plano

El caso más extremo con el que se puede encontrar a la hora de subir una

base de datos a nuestro proveedor de alojamiento, es que la base de datos la

tengamos creada de forma local, pero en un sistema gestor distinto del que

vamos a utilizar en remoto. Si se piensa en remoto, se supone siempre que se va

a utilizar la base de datos MySQL. En Si se piensa en local, se puede disponer de

una base de datos Access, SQL Server o de otro sistema de base de datos.

El proceso de migración puede ser bastante complejo y, ya que existen

tantas bases de datos distintas, es difícil de encontrar una receta que funcione

en todos los casos. Por otro lado, aparte de la dificultad de transferir la

información entre los dos sistemas gestores de base de datos, también influye

mucho en la complejidad del problema, el tipo de los datos de las tablas que se

están utilizando. Por ejemplo, las fechas, los campos numéricos con decimales o

los boleanos pueden dar problemas al pasar de un sistema a otro, porque

pueden almacenarse de maneras distintas o, en el caso de los números, con una

precisión distinta.

Recomendaciones para migrar desde SQL Server a MySQL

Pág. 134 de 142


En realidad es muy difícil encontrarse con este caso, pero se debe decir

que Access también puede ayudar con respecto a este asunto. Access permite

seleccionar una base de datos SQL Server y trabajar desde la propia interfaz del

programa. La idea es que Access también permite trabajar con MySQL y

posiblemente al crear un puente entre estos dos sistemas gestores se pueden

exportar los datos de SQL Server a MySQL.

Lo que si es cierto es que al utilizar el propio Access como puente se

podría realizar el trabajo. Primero exportando de SQL Server a Acess y luego

desde Access a MySQL.

Recomendaciones para migrar de Access a MySQL

Si la base de datos anterior se encontraba construida en Access el proceso

resulta muy fácil, debido a que MySQL dispone de un driver ODBC para sistemas

Windows, que permite conectar Access con el propio MySQL y pasar información

de forma fácil.

Este tema se trata en más detalle el artículo sobe Exportar datos de

MySQL a Access, aunque se debe señalar que si se desea hacer una exportación

desde Access en local hacia MySQL en remoto, pueden surgir problemas ya que

Pág. 135 de 142


no todos los alojadores permiten las conexiones en remoto con la base de datos.

Si no se dispone de una conexión en remoto con nuestro servidor de bases de

datos se debe cambiar la estrategia un poco.

En este último caso se podría instalar MySQL en local y realizar la

migración desde Access en local a MySQL en local, y luego se podría hacer un

backup de la base de datos local y subirla a remoto, tal y como se mencionó

antes.

Otras bases de datos u otras técnicas

Si la base de datos origen cuanta con un driver ODBC, en teoría, no se

tendría ningún problema para conectarla con Access, igual a como se conecta

con MySQL. Entonces se podría utilizar Access para exportar los datos, ya que

desde allí se podrían acceder a los dos sistemas gestores de bases de datos.

Si no se cuenta con Access, o la base de datos original no tiene driver

ODBC, o bien el proceso no funciona de la forma correcta y no se sabe cómo

arreglarlo, otra posibilidad es exportar los datos a archivos de texto, separados

por comas o algo parecido. Muchas bases de datos tienen herramientas para

exportar los datos de las tablas hacia archivos de texto, los cuales luego pueden

ser introducidos en el sistema gestor destino (MySQL) con la ayuda de alguna

herramienta como PhpMyAdmin.

Pág. 136 de 142


Para lograrlo, en la página de propiedades de la tabla se encuentra una

opción para hacer el backup de la tabla y para introducir los archivos de texto

dentro de una tabla (Insert textfiles into table en inglés).

Al acceder a ese enlace se puede ver un formulario donde introducir las

características del archivo de texto, como el caracter utilizado como separador

de campos, o el terminador de líneas, etc., junto con el propio archivo con los

datos, y el PhpMyAdmin se encargará del trabajo de incluir esos datos en la

tabla.

Como es de suponerse, es necesario tener creada la tabla en remoto para

poder introducirle los datos del archivo de texto.

En especial, toda la migración tiene que tener en cuenta, como se

mencionó antes, las formas que cada base de datos tenga para guardar la

información, es decir, del formato de sus tipos de datos. Siempre se debe contar

con la posible necesidad de transformar algunos datos como pueden ser los

campos boleanos, fechas, campos memo, un texto con longitud indeterminada,

etc., los cuales pueden ser almacenados de maneras distintas en cada uno de

los sistemas gestores, origen y destino.

Pág. 137 de 142


En algunos casos es posible que se tenga que realizar algún script que

lleve a cabo los cambios necesarios en los datos. Por ejemplo, se puede llevar a

cabo para localizar los valores boleanos guardados como true / false a valores

enteros 0 / 1, que es como se guardan en MySQL. También, las fechas pueden

sufrir cambios de formato, mientras que en Access aparecen en castellano

(dd/mm/aaaa) en MySQL aparecen en el formato aaaa-mm-dd. PHP puede

ayudar en la tarea de hacer este script, también Visual Basic Script para Access

puede hacer estas tareas complejas y el propio lenguaje SQL, a base de

sentencias dirigidas contra la base de datos, puede servir para algunas acciones

sencillas.

Importación desde archivos CSV, TXT y otros formatos de texto

plano

Se tiene la posibilidad de leer el contenido de un archivo de texto en una

tabla. El archivo origen puede haber sido creado mediante una sentencia

SELECT ... INTO OUTFILE, o mediante cualquier otro medio.

Para hacerlo se cuenta con la sentencia LOAD DATA, cuya sintaxis más

simple es:

LOAD DATA [LOCAL] INFILE 'file_name.txt'

[REPLACE | IGNORE]

Pág. 138 de 142


INTO TABLE tbl_name

[FIELDS

[TERMINATED BY '\t']

[[OPTIONALLY] ENCLOSED BY '']

[ESCAPED BY '\\' ]

]

[LINES

[STARTING BY '']

[TERMINATED BY '\n']

]

[IGNORE number LINES]

[(col_name,...)]

La cláusula LOCAL indica, si aparece, que el archivo se encuentra en el

ordenador del cliente. Al no especificar el archivo de texto se buscará en el

servidor, concretamente en el mismo directorio donde se encuentre la base de

datos. Esto nos permite importar datos desde el ordenador, en un sistema en

que el servidor de MySQL se encuentra en otra máquina.

Las cláusulas REPLACE e IGNORE afectan la forma en que se tratan las filas

leídas que contengan el mismo valor para una clave principal o única para una

Pág. 139 de 142


fila existente en la tabla. Si se especifica REPLACE se sustituirá la fila actual por

la leída. Al especificar IGNORE el valor leído será ignorado.

La parte INTO TABLA tbl_name indica en las tablas donde se insertarán los

valores leídos. No se comenta mucho sobre las cláusulas FIELDS y LINES ya que

su significado es el mismo que vimos para la sentencia SELECT ... INTO OUTFILE.

Estas sentencias nos permiten interpretar correctamente cada fila y cada

columna, adaptándose al formato del archivo de texto de entrada.

La cláusula IGNORE número LINES tiene el mismo uso, el cual nos permite

que las primeras líneas no se interpreten como datos a importar. Es frecuente

que los archivos de texto que se utilizarán como fuente de datos contengan

algunas cabeceras que expliquen el contenido del archivo, o que contengan los

nombres de cada columna. Al utilizar esta cláusula se pueden ignorar.

Esta última parte permite indicar la columna a la que será asignada cada

una de las columnas leídas, esto será útil si el orden de las columnas en la tabla

no es el mismo que en el archivo de texto, o si el número de columnas es

diferente en ambos casos.

Por ejemplo, supóngase que se desea añadir el contenido de este archivo

a la tabla "gente":

Pág. 140 de 142


Archivo de datos de "gente"

fecha,nombre

2004-03-15,Xulana

2000-09-09,Con Clase

1998-04-15,Pingrana

Como se puede observar, existen dos filas al principio que no contienen

datos válidos, las columnas se encuentran separadas por comas y, como se ha

editado el archivo con el "notepad", las líneas terminan con "\n\r". A

continaución se presenta la sentencia adecuada para leer los datos:

mysql> LOAD DATA INFILE "gente.txt"

-> INTO TABLE gente

-> FIELDS TERMINATED BY ','

-> LINES TERMINATED BY '\r\n'

-> IGNORE 2 LINES

-> (fecha,nombre);


Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql>

Pág. 141 de 142


El nuevo contenido de la tabla es:

mysql> SELECT * FROM gente;

+-----------+------------+

| nombre | fecha |

+-----------+------------+

| Fulano | 1974-04-12 |

| Mengano | 1978-06-15 |

| Tulano | 2000-12-02 |

| Pegano | 1993-02-10 |

| Mengano | NULL |

| Xulana | 2004-03-15 |

| Con Clase | 2000-09-09 |

| Pingrana | 1998-04-15 |

+-----------+------------+


mysql>

Pág. 142 de 142

Documents

Programa Mysql Manual Del Estudiante-2012