Programa Mysql Manual Del Estudiante-2012
-
Upload
edgardo-jose-pinto-hernandez -
Category
Documents
-
view
59 -
download
3
Transcript of 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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
[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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
| 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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
| 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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
-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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
| 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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
* 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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
UNIDAD 4: Transacciones
Objetivo de la Unidad: Utilizar las funciones especificas para realizar
transacciones en MySQL de forma eficiente.
Pág. 81 de 142
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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);
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO innotest VALUES(2);
Query OK, 1 row affected (0.01 sec)
Pág. 83 de 142
Programa de formación :BASE DE DATOS MySQL
mysql> INSERT INTO innotest VALUES(3);
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM innotest;
+-------+
| campo |
+-------+
| 1 |
| 2 |
| 3 |
+-------+
3 rows in set (0.00 sec)
Al utilizar las transacciones.
mysql> BEGIN;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO innotest VALUES(4);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM innotest;
+-------+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
Programa de formación :BASE DE DATOS MySQL
| 1 |
| 2 |
| 3 |
| 4 |
+-------+
4 rows in set (0.00 sec)
Ahora, al ejecutar un ROLLBACK, no se completará la transacción y los
cambios realizados sobre la tabla no tendrán efecto.
mysql> ROLLBACK;
Query OK, 0 rows affected (0.06 sec)
mysql> SELECT * FROM innotest;
+-------+
| campo |
+-------+
| 1 |
| 2 |
| 3 |
+-------+
3 rows in set (0.00 sec)
Entonces, ¿qué sucede si se pierde la conexión al servidor antes de que la
transacción sea completada?.
Pág. 85 de 142
Programa de formación :BASE DE DATOS MySQL
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO innotest VALUES(4);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM innotest;
+-------+
| campo |
+-------+
| 1 |
| 2 |
| 3 |
| 4 |
+-------+
4 rows in set (0.00 sec)
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
Programa de formación :BASE DE DATOS MySQL
mysql> SELECT * FROM innotest;
+-------+
| campo |
+-------+
| 1 |
| 2 |
| 3 |
+-------+
3 rows in set (0.00 sec)
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;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO innotest VALUES(4);
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.02 sec)
mysql> EXIT;
Bye
Pág. 87 de 142
Programa de formación :BASE DE DATOS MySQL
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.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 450 to server version: 4.0.13
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> SELECT * FROM innotest;
+-------+
| campo |
+-------+
| 1 |
| 2 |
| 3 |
| 4 |
+-------+
4 rows in set (0.00 sec)
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | .\ |
| innodb_mirrored_log_groups | 1 |
| innodb_max_dirty_pages_pct | 90 |
+---------------------------------+------------+
20 rows in set (0.00 sec)
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
[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
Welcome to the MySQL monitor. Commands end with ; or \g.
Pág. 103 de 142
Programa de formación :BASE DE DATOS MySQL
Your MySQL connection id is 4 to server version: 3.23.41
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
$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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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';
Query OK, 5 rows affected (0.00 sec)
mysql>
El archivo de salida se verá de la siguiente forma:
"Fulano";"1974-04-12"
Pág. 131 de 142
Programa de formación :BASE DE DATOS MySQL
"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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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
Programa de formación :BASE DE DATOS MySQL
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);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0
mysql>
Pág. 141 de 142
Programa de formación :BASE DE DATOS MySQL
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 |
+-----------+------------+
8 rows in set (0.00 sec)
mysql>
Pág. 142 de 142