Mysql es un sistema gestor de bases de datos relacionales bastante extendido actualmente. Posee un excelente rendimiento y facilidad de uso, con una licencia dual.
Es multiusuario, multihilo, adquirido por la empresa Oracle (posee otro gestor de bases de datos objeto relacional que domina el mercado alto rendimiento a nivel empresarial).
El éxito de Mysql en parte radica por su sencillez, facilidad, y su uso en aplicaciones filosofía LAMP.
Como he comentado muchos usuarios confunden la libertad de usarlo como gestor de nuestras bases, con la gratuidad. De hecho, solamente usaremos MySQL GPL, si la aplicación que lo emplea también lo es. En caso contrario, tendremos que pagar una licencia de uso. Este aspecto es curioso, puesto que existen empresas que basan el funcionamiento de sus aplicaciones sobre este gestor sin pagar licencia, pero su código es privativo (no quiero enfadar a Stallman)
Aunque inicialmente no ofrecía motor para soporte transaccional, ni integridad referencial automática (si a nivel de especificación mediante sentencia DDL)(esto sonará curioso a los amantes de Microsoft Access), actualmente si ofrece ambas características en función del tipo de motor de almacenamiento seleccionado.
Otra gran ventaja es su disponibilidad de uso en cualquiera de las plataformas disponibles.
Según datos de fabricante, existirían más 6.000.000 de copias de MySQL funcionando en la actualidad, lo que supera la base instalada de cualquier otra herramienta de gestión de bases de datos.
Es empleado en las herramientas más extendidas de Internet, como puede ser WordPress (1 de cada 4 sitios de internet se realiza con este CMS), Drupal, Moodle, Prestashop, etc …
Aunque también es empleado por empresas grandes Wikipedia, Google (aunque no para realizar búsquedas), Facebook, Twitter, Flickr, YouTube…
El tráfico del sitio web de MySQL AB (empresa) superó en el año 2004 al del sitio de IBM.
Algunos trucos a nivel de comando.
Para conectarnos desde la línea de comandos, tendremos que tenerlo lógicamente instalado y teclear desde consola:
mysql –u usuario –p
Nos pedirá la password.
Un dato muy importante es que al ser multiusuario, en función del usuario con el que conectemos tendremos determinados permisos.
También nos podremos conectar a un gestor remoto:
Mysql –h elservidor.com –u suario –p
Para desconectar tendremos que teclear quit; (importante siempre para finalizar punto y coma).
Podemos procesar por lotes de la siguiente forma (una de ellas).
Mysql –u miusuario –h servidor.com –p <archivo_con_instrucciones.sql
Se pueden almacenar todos los comandos tecleados en el intérprete Mysql ejecutando la orden tee.
Por ejemplo: tee archivo_de_comandos.txt
Para cancelar la captura ejecutar notee.
Otras Consultas interesantes en línea de comandos.
Show databases; // Muestra las bases de datos existentes.
select database(); // Informa sobre la base de datos actualmente en uso.
use la_base_de_datos; // Permite trabajar con la base de datos que seleccionemos.
mysql base_de_Datos –u usuario –p // Permite seleccionar desde línea de comandos la base de datos con la que vamos a trabajar.
show tables; // Muestra las tablas
describe nombre_tabla; // Muestra la definición de los campos de una tabla.
create database nombre_de_la_base; // Crea una base de datos
drop database nombre_de_la_base; // borra una base de datos.
Ejemplo de cómo crear una tabla desde el intérprete:
mysql> create table personas ( > nombre varchar(40) not null, -> dirección varchar(50) null, -> estado_civil char(13) default ’casado’, -> num_registro int primary key auto_increment, -> );
Cuando se almacena un datos en char, si sobrepasan esos límites serán truncados para poderlos almacenar. Es por eso que se suele usar bastante el varchar, con la salvedad de que el valor se ajusta al tamaño, permitiendo ahorrar espacio.
Para borrar una tabla.
Drop table nombre_de_la_tabla;
Este apartado es interesante para analizar:
mysql> create table personas ( -> nombre varchar(40) not null, -> nacimiento date not null, -> pareja varchar(40), -> proveedor int not null, -> -> primary key (nombre,nacimiento), -> unique (pareja), -> foreign key (proveedor) references proveedores -> );
Primary key define las columnas que serán clave primaria, son por lo tanto no nulas.
Unique establece las columnas que no admite duplicados. Son claves candidatas del modelo ER
Foreign key, define los valores de proveedor que solo existan en proveedores, es una forma de relacionar tablas.
Las 3 son conocidas como restricciones en la tabla, también se les puede dar un nombre a cada restricción mediante constraint.
Ejemplo:
Constrain clave primary key (nombre, nacimiento);
Otro aspecto a considerar son los índices. Pueden establecerse en el momento de definición de la creación de la tabla o a posteri. Se suele realizar con la palabra key, pueden ser sencillos o compuestos dependiendo de nuestras necesidades.
Respecto a los tipos de datos, mysql cuenta con muchas alternativas.
Existen 3 tipos de clasificación, los numéricos, cadenas de caracteres y tipo fecha-hora.
El valor null es un caso especial, implica la ausencia de valor.
Numéricos
unsigned (sin valor negativo),zerofill(rellena con 0 espacios a la izquierda vacios), tinyint(1 byte),smallint(2), mediumint(3),int(4), bigint(8), float(4), double(8),decimal.
Cadenas de caracteres (de mejor a mayor espacio)
Char, varchar, tinytext, text(blob), mediumtext, longtext. Dos tipos especiales.
Tipo enumerado (enum) y tipo set.El carácter varchar sólo ocupa el espacio requerido por el valor de la columna.
Fecha y hora
date (3 bytes), time(3), datetime (8),timestamp(4), year(1)
Para modificar una tabla y columna
Para tablas:
alter table mi_tabla add (campo tipo, campo tipo);
alter table mi_tabla drop campo; // Elimina un campo de una tabla.
Para columnas:
alter table mi_tabla modify campo tipo;
alter table mi_tabla change nombre_antiguo nombre_nuevo;
alter table mi_tabla order by el_campo_para_ordenar;
Cambiar nombre de la tabla:
alter table mi_tabla rename nuevo_nombre_tabla;
Copiar tablas:
rename tabla mibase.tabla to mibase_dos.tabla; // Copia una tabla de una base a otra
Crear tabla con el contenido de otra:
create table mi_nueva_tabla_destino select * from tabla_origen;
Crear tabla con campos o estructura pero si datos:
create table mi_nueva_tabla_destino like tabla_origen;
Crear una tabla temporal, solo visible en la sesión activa, útil para trabajar con resultados intermedios
Create temporaly table la_nueva_tabla;
Podemos importar datos de una aplicación a otra, mediante la creación de archivos de texto mediante ordenes silimares a esta
mysql> load data local infile ’clientes.txt’ -> into table clientes;
o detallando más el tipo de importación:
mysql> load data local infile ‘clientes.txt’ -> fields terminated by ’,’ -> enclosed by ’”’ -> lines terminated by ’\r\n
Estas acciones también se pueden realizados con mysqlimport
CURIOSIDADES
Si usamos la expresión explain delate de una consulta select, Mysql nos explica como ha realizado la consulta, si ha empleado índices, el tipo de join realizado, las claves primarias, etc.
Su usamos la cláusula truncate en vez de delete para borrar registros de una tabla, ganaremos en velocidad debido a que no hace una cuenta de los registros borrados.
Podemos asignar prioridades a las operaciones LOW_PRIORITY o HIGH_PRIORITY para evitar bloqueos cuando varios usuarios realizan operaciones sobre la base.
Mediante LAST_INSERT_ID(), nos devuelve el id asignado a una columna auto numérica después de realizar un insert.
Es preferible realizar instalaciones fuente y luego compilarlas, que instalar los binarios directamente. Aunque la segunda posibilidad es más sencilla, el motor seguramente sea más lento.
El archivo de configuración de Mysql suele residir en la carpeta etc/my.cnf
Podemos acelerar las consultas a la base, mediante la incorporación de una cache. Si no vamos a trabajar con una base de datos sometida a cambios, podremos usarla y notar mucha mejoría en el rendimiento. Esta cache se activa mediante variables globales o tocando el archivo de configuración anterior.
Recuerda también que el empleo de índices en las tablas, aumenta la velocidad pero necesitan estar activos bajo unos criterios y consejos adecuados. Pero principalmente que los registros de los índices sean lo más pequeños posibles y que solo se indexen valores únicos.
Otra característica poco conocida de Mysql es que permite la replicación. Esto permite realizar una copia de los datos de forma sincronizada entre servidores de bases de datos, para que cualquiera de ellos, pueda ofrecer registros a sus clientes. El esquema de funcionamiento es maestro-esclavo, donde existe un servidor central o uno o varios esclavos que sincronizan con el maestro. Los servidores esclavos solo sirven para realizar consultas. Con el objeto también de ganar velocidad, al igual que con la cache y los índices.
Si queremos que los mensajes del servidor aparezcan el castellano, se debe ejecutar el demonio mysqld con el comando
mysqld –language=spanish
o agregar la línea dentro del etc/my.cnf con un editor de texto (vi, joe,emac) en la sección
[mysqld]
language = /usr/share/mysql/spanish
Copias de seguridad
Mysql ofrece varios mecanismos crear copias de seguridad.
Una de ellas, la más directa es copiar los archivos físicos binarios con los datos. Para ello es necesario saber dónde se almacenan las bases. La ruta puede variar en función de la distribución (si es Linux) y el sistema operativo.
En Linux suelen estar ubicadas en /var/lib/mysql
Podemos usar el comando desde dentro > show variables like ’datadir’; para saber dónde se almacenar las bases.
Es importante parar el servidor antes re realizar la copia binaria.
mysqladmin -u root -p shutdown
Se pueden copiar solo tablas si deseamos dentro de cada carpeta de cada base. Cada base de datos posee una carpeta y dentro de ella es donde se ubican las tablas e índices. Se diferencian por la extensión.
Este método obliga para evitar problemas, a parar el servidor para realizar la copia.
Mysqlhotcopy posibilita realizar la copia sin parar el servidor, es un script escrito en perl
Ejemplo mysqlhotcopy base_de_datos
Esto creara una carpeta /var/lib/mysql/copy_base_de_datos
Pero importante, no funciona para bases con motor de almacenamiento INNODB
Otro mecanismo sencillo es mediante
Mysqldump
Esta copia no es binaria, y genera un archivo de texto con las sentencias DDL y DML SQL. El único inconveniente es que los archivos generados son grandes y es un mecanismo lento. Pero no es necesario parar el servidor.
mysqldump mi_base > mi_base.sql
Es necesario observar el símbolo mayor, que a modo de tubería típica de los interpretes envía la base al archivo .sql
Restauración de las copias (administradores de sistemas)
Si la restauración es binaria, tendremos que parar el servidor.
mysqladmin -u root -p shutdown
Y copiar los archivos a su destino.
/var/lib/mysql (en la mayoría de los casos en sistemas GNU/Linux)
Pero ojo, es necesario restaurar el propietario y el grupo de los archivos copiados.
chown -R mysql.mysql /var/lib/mysql
Y si la copia ha sido mediante un archivo .sql
mysql mi_base -u admin -p < mi_base.sql
Reparación de las tablas en caso de problemas.
En algunas ocasiones, y es más frecuente de lo que pensamos, cuando se realizan muchas operaciones sobre la base de datos, por ejemplo, usuarios en una plataforma formativa moodle, se efectúan bloqueos, accesos, escrituras y es posible que en un momento determinado, los índices de la tabla queden dañados. También condiciones físicas, apagones, fallos de hard, etc.
Vamos a explicar el equivalente a compactar y reparar datos en una base de datos Access.
Para ello, desde la línea de comandos.
Ckeck table tabla_a_analizar;
Si nos devuelve un OK, la tabla está bien. Si la tabla se encuentra dañada nos dará errores.
Es importante saber que Mysql no puede realizar consultas sobre una tabla dañada, desatando el error.
Con este otro comando
check table tabla_a_analizar extended;
Muestra más información sobre el error que tenemos.
Es el momento de ejecutar el comando repair
Repair table tabla_a reparar;
Es preciso aclarar, que esto no es la panacea, ya que al reparar se suelen perder en ocasiones registros, y que es una acción no deseada lógicamente, pero necesaria para poder seguir trabajando. Por lo que es aconsejable tirar de las copias de seguridad.
El comando optimize table también se encarga de realizar algunas correcciones.
Existe una utilidad myisamchk, que permite reparar las tablas, pero ojo, solo de tipo MyISAM. Para ejecutarlo, el servidor no debe estar ejecutándose.
Ejemplo: myisamchk *.MYI
No obstante, antes de esto, es preferible repito disponer de copia de seguridad.
SEGURIDAD
En Mysql la seguridad de acceso a los datos, queda principalmente marcada por los permisos atribuidos a los diversos tipos de usuarios. Estos permisos pueden ser en principio complejos. Existe un usuario admin generalmente que posee acceso a todos los recursos del sistema.
Mysql almacena toda la información de los usuarios y el sistema de privilegios en una base de datos cuyo nombre es mysql. Su estructura podemos verla mediante use mysql; show tables;
El comando show privileges es también importante, ya que si realizamos cambios en información que afecta a privilegios de usuarios, para que el gestor se entere de los cambios es necesario ejecuitar un
mysql> flush privileges; // Es como un refresh
Aquí os dejo un ejemplo de creación de un usuario:
GRANT ALL PRIVILEGES ON nombre_base.* TO ‘nombre_usuario’@’%’ IDENTIFIED BY ‘contraseña’;
Cada usuario dispondrá de sus privilegios.
La administración de privilegios y usuarios en MySQL se realiza a través de las sentencias:
- GRANT. Otorga privilegios a un usuario, en caso de no existir, se creará el usuario.
- REVOKE. Elimina los privilegios de un usuario existente.
- SET PASSWORD. Asigna una contraseña.
- DROP USER. Elimina un usuario.
Es importante aclarar que podemos establecer diferentes accesos en función del tipo de red donde estemos, limitando accesos por la red, id o local.
MySQL proporciona una gran variedad de tipos de privilegios.
- Privilegios relacionados con tablas: alter, create, delete, drop, index, insert, select, update
- Algunos privilegios administrativos: file, proccess, super reload, replication client, grant option, shutdown
- Algunos privilegios para fines diversos: lock tables, show databases, create temporary tables.
MySQL puede establecer conexiones seguras encriptándolas mediante el protocolo SSL*;
mysql> grant all -> on *.* -> to usuario@localhost -> require ssl;
También podemos limitar los recursos del servidor, a conexiones por hora, consultas, actualizaciones.
mysql> grant all -> on *.* -> to -> with MAX_CONECTIONS_PER_HOUR 5 -> MAX_QUERIES_PER_HOUR 600 -> MAX_UPDATES_PER_HOUR 5
Para borrar un usuario
mysql> drop user usuario_a_borrar;
Si deseas simplificar todos estos comandos anteriores, es posible y recomendable usar un gestor GUI, ya que la memoria nos puede fallar …
PARTE II
Anexo – Los Oscar a las mejores Herramientas de Administración GUI para Mysql.
