Administración de MySQL

Toda la información que maneja el servidor se encuentra en un mismo directorio de datos. En Windows esta ubicación suele ser C:\mysql\data.
Si no es así, hay dos formas de encontrarlo:

• Utilizando el comando mysqladmin variables | more y comprobando el directorio que se indica, para la variabla datadir.
• Realizando una búsqueda convencional de los archivos con extensión *.frm, que forman parte de cualquier instalación de mysql.

Dentro el directorio de datos, la información se encuentra organizada en una estructura jerárquica de árbol. Existe una carpeta correspondiente a cada base de datos (con su mismo nombre), y dentro de cada una, se encuentran los archivos correspondientes de cada tabla.

Las tablas pueden estar almacenadas de diferentes formas, incluso dentro de una misma base de datos. Cada tipo de tabla, afecta en el espacio que consumen en memoria y disco, y en la velocidad de ejecución de las sentencias MySQL.

Para pasar tablas de un tipo a otro, se puede hacer sin problema (siempre que se tengan los privilegios adecuados), a través de la sentencia:

ALTER TABLE NombreTabla TYPE=TipoTabla;

A continuación se detallan los diferentes tipos de tablas:

Versión mejorada de estructura ISAM. Cada tabla de la base de datos existe como tres archivos:

• Archivo de formulario, o descripción (.frm): Describe la estructura de la tabla (columnas, tipo de columnas, etc).
• Archivo de datos (.MYD): Contiene las filas de la tabla.
• Archivo de índice (.MYI): Contiene los índices que se han creado sobre el archivo de datos.

Cuando se ejecuta una sentencia CREATE, se crean los tres archivos, aunque algunos estén vacíos.
Existen diferentes opciones de almacenamiento para tablas ISAM:

• fixed: Indica que los datos tendrán longitud fija. Se selecciona de forma automática, si no hay columnas de tipo VARCHAR, TEXT o BLOB. Si se especifica la opción ROW_FORMAT=FIXED, las columnas de longitud variable, pasan a tener longitud fija. Los índices asociados a este tipo de tablas son más pequeños, rápidos y eficientes. Además son tablas que no generan fragmentación.
• dinamic: Indica que los datos tendrán longitud variable, por lo que cada fila tiene una cabecera indicando su longitud. Se selecciona de forma automática, si hay columnas de tipo VARCHAR, TEXT o BLOB, o si se crea con la opción ROW_FORMAT=DYNAMIC. Todas las columnas de cadena son de longitud variable, incluso CHAR cuando pasa de 4 caracteres. Este tipo de almacenamiento ahorra espacio en disco, pero los DELETES y UPDATES generan fragmentación, por lo que cada cierto tiempo conviene pasar un proceso de desfregmentación, a través de la sentencia:

OPTIMIZE TABLE NombreTabla;

O a través de la utilidad myisamchk, que se encuentra como programa ejecutable, dentro de la carpeta /bin.

• Compressed: Formato de tablas de solo lectura, generado por la utilidad myisampack, que se encuentra en la carpeta /bin de la distribución de MySQL. Se caracterizan por el ahorro de espacio en el disco y es conveniente para dispositivos de almacenamiento lentos como los CD-ROM.
  

Ejemplo de uso:

  C:\mysql\bin>myisampack ..\data\NombreBaseDatos\NombreTabla # Para crear la tabla
  C:\mysql\bin>myisamchk -u ..\data\NombreBaseDatos\NombreTabla # Para crear la tabla # Para obtener la tabla original, en formato lectura-escritura

Estas tablas no tienen datos por si mismas, sino que hacen referencia a dos o más tablas tipo MyISAM a través de la cláusula UNION. Podemos realizar las diferentes acciones, bien a través de la tambla tipo MERGE, o de las tablas originales. Generan dos ficheros: uno con extensión .frm y otro con extensión .MRG.\\Las tablas de este tipo, deben ser idénticas en estructura. Borrar una tabla tipo MERGE, no afecta a las tablas que la conforman.

Se caracterizan por almacenar todos los datos en memoria. Si se reinicia el servidor, todos los datos se pierden (no así su estructura). Estas tablas son extremadamente rápidas, y son muy útiles para crear tablas temporales. Sólo guardan un fichero con extensión .frm que almacena la estructura de la tabla.

Es un motor de bases de datos muy completo que ha sido embebido dentro de MySQL. Sus principales características son:

• Control de transacciones.
• Integridad referencial.
• Bloqueo a nivel de filas (en vez de bloquear la tabla entera, como ocurre con MyISAM).
• SELECT sin bloqueo, lo que genera un aumento del rendimiento.

InnoDB, a diferencia que MyISAM, almacena todos los datos e índices de todas las tablas de todas las bases de datos en un mismo tablespace, que consiste en un número reducido de archivos del sistema operativo formado por ficheros de datos (data files) y ficheros de log (log files).

Por defecto, InnoDB crea un archivo de datos llamado ibdata1 de 10MB autoextensible y dos archivos de log llamados ib_logfile0 y ib_logfile1, todos ellos en el directorio de datos de MySQL. Por ello InnoDB sólo crea por cada tabla un fichero de descripción de la tabla con extensión .frm. Los datos e índices quedan en el tablespace definido.

Los programas que componen MySQL (mysqld, mysqladmin, etc) pueden ser configurados, por orden de mayor a menor preferencia:

1. Línea de comandos.
2. Ficheros de opciones.
3. Valores de variables de entorno.

Deben seguir las siguientes reglas:

• Siempre se introducen después del nombre del programa.
• La opción va precedida de un guión si se expresa de la forma corta, o de dos guiones si es la forma larga. Por ejemplo:

  c:\mysql\bin>mysql --help  # Forma larga
  c:\mysql\bin>mysql -?      # Forma corta

• Si la opción va seguida de un valor, si se usa la forma larga se especifica tras el símbolo '='. Si se utiliza la forma corta, no es necesario ningún símbolo, se expresa a continuación de la opción, siendo opcional el incluir un espacio entre ambas (salvo en el caso de la contraseña, que no está permitido el espacio).
• Algunos de los programas tienen variables que son configurables. Para configurar estas variables se utiliza la opción -set-variable. Por ejemplo:

  c:\mysql\bin>mysql --set-variable=max_allowed_packet=16M

Se recomienda visitar el Manual de Referencia de MySQL para localizar toda la información necesaria.

Los ficheros de configuración sirven para que los programas carguen la misma configuración al arrancar.
Listado de programas que aguantan ficheros de configuración: myisamchk, myisampack, mysql, mysql.server, mysqladmin, mysqlbinlog, mysqlcc, mysqlcheck, mysql_safe, mysqldump, mysqld, mysqlhotcopy, mysqlimport, mysqlshow.

Los programas MySQL pueden consultar más de un fichero de configuración.
Bajo Windows, por orden de mayor a menor preferencia:

1. WINDIR\my.ini Opciones globales (WINDIR es la ruta del sistema Windows, típicamente C:\Windows).
2. C:\my.cnf Opciones globales.

Bajo Linux, por orden de mayor a menor preferencia:

1. /etc/my.cnf Opciones globales.
2. DATADIR/my.cnf Opciones específicas del servidor (DATADIR hace referencia al directorio de datos de MySQL).
3. ~/.my.cnf Opciones específicas del usuario.

Ha tener en cuenta:

• La sintaxis de estos ficheros es muy similar a la indicada en el apartado anterior.
  
• Las líneas en blanco se ignoran.
  
• Los comentarios (línea) se indican mediante el carácter '#', ó ';'.
  
• [grupo] Cuando algo aparece entre corchetes, se refiere al nombre del programa MySQL sobre el que tendrán efecto las opciones que se muestran a continuación. El grupo [client] permite especificar opciones que se aplican de forma común a todos los programas cliente.
• En el caso de set-variable = nombre_variable=valor hay que tener en cuenta que los espacios alrededor del símbolo '=' son válidos en la primera igualdad, pero no en la segunda.

El administrador del Sistema MySQL, puede especificar:

• Qué usuarios pueden conectarse al servidor (usuario).
• Desde dónde pueden conectarse (host).
• Qué pueden hacer mientras están conectados (privilegios).

El “diccionario de datos” de MySQL se encuentra en el “directorio de datos” en una base de datos llamada mysql. En esta base de datos aparecen cuatro tablas que disponen de toda la información necesaria acerca de los usuarios y sus privilegios. Las tablas son las siguientes:

• user: Usuarios que pueden conectar con el servidor.
• db: Privilegios a nivel base de datos.
• tables_priv: Privilegios a nivel de tabla.
• columns_priv: Privilegios a nivel de columna.

Aunque se puden manejar los privilegios con la manipulación de estas cuatro tablas, es más cómodo usar las sentencias GRANT (crear usuarios y especificar privilegios) y REVOKE (eliminar privilegios).

Sintaxis de la sentencia GRANT:

  GRANT privilegios (columnas)
  ON nivelPrivilegio
  TO usuario@host IDENTIFIED BY "contraseña"
  [WITH GRANT OPTION];

Vamos a ver qué significa cada cláusula de esta sentencia:

• Privilegios: Se muestran a continuación los diferentes privilegios que pueden asignarse a los usuarios.
  • Privilegios aplicados a bases de datos, tablas y columnas:

• Privilegios administrativos:

• Privilegios especiales:

• Nivel de privilegio: Puede aplicarse a cuatro niveles, de mayores privilegios a menor:
  1. Nivel global: Se aplican privilegios a todas las bases de datos y tablas del servidor. La forma de indicarlo será: GRANT privilegios ON *.* …
  2. Nivel base de datos: Se aplican privilegios sobre todas las tablas de la base de datos nombrada: GRANT privilegios ON NombreBaseDatos.* …
  3. Nivel de tabla: Se aplican privilegios sobre todas las columnas de la tabla especificada: GRANT privilegios ON NombreBaseDatos.NombreTabla …
  4. Nivel de columna: Se aplican privilegios sobre las/s columna/s seleccionadas de una tabla determinada.

• Usuario@host: El usuario de la base de datos se especifica mediante el usuario y el host desde el que se conecta. De esta manera es posible diferenciar a los usuarios según de dónde se conecten, y aplicarles en consecuencia diferentes privilegios.
  Lo nombre de usuarios son independientes de los nombres de usuario del Sistema Operativo.
  Como nombre de host, pueden utilizarse direcciones IP o nombres DNS. También está permitido el uso de caracteres comodín, lo que nos va a permitir especificar más de un host, o incluso cualquier host, por ejemplo: alberto.gargoles@“%”.
  Si queremos especificar cualquier nombre de usuario, podemos hacerlo dejando en blanco éste, por ejemplo: ''@uah.es
• Contraseña: Si no se especifica la clausula IDENTIFIED BY cuando se crea un usuario, no se establece contraseña para ese usuario, lo que puede suponer un problema de seguridad. Si el usuario ya tenía contraseña, ésta es sobreescrita. Si el usuario tenía contraseña y no se especifica la cláusula IDENTIFIED BY, la contraseña antigua se mantiene.
• Cláusula WITH GRANT OPTION: Es opcional, y significa que el usuario tiene la capacidad de transmitir sus privilegios a otros usuarios.

Sintaxis de la sentencia REVOKE:

  REVOKE privilegios (columnas)
  ON nivelPrivilegio
  FROM usuario@host

Los privilegios no tienen porqué coincidir con todos los que tiene el usuario, ya que es posible eliminar sólo la parte que interese.
Son los mismos privilegios y nivel de privilegio que se especificaron en el apartado anterior.

Para eliminar de golpe todos los privilegios de uno o varios usuarios, se puede usar la forma:

  REVOKE ALL PRIVILEGES, GRANT OPTION FROM usuario@host [,usuario@host ...]

Eliminar todos los privilegios no supone eliminar al usuario, ya que éste aún permanece en un registro de la tabla mysql.user, lo que significa que aún puede establecer conexión con el servidor.

  DROP USER 'usuario'@'host' [,'usuario'@'host' ...]

Para poder eliminar a un usuario, anteriormente le han tenido que haber eliminado todos los privilegios.

Pueden verse los privilegios de un usuario con la sentencia SHOW GRANTS:

  SHOW GRANTS FOR 'usuario'@'host' [,'usuario'@'host' ...]