{"id":8409,"date":"2015-12-23T14:08:30","date_gmt":"2015-12-23T13:08:30","guid":{"rendered":"http:\/\/www.palentino.es\/blog\/?p=8409"},"modified":"2015-12-23T15:21:43","modified_gmt":"2015-12-23T14:21:43","slug":"monografico-sobre-mysql-curiosidades-trucos-y-funcionamiento-parte-i","status":"publish","type":"post","link":"https:\/\/www.palentino.es\/blog\/monografico-sobre-mysql-curiosidades-trucos-y-funcionamiento-parte-i\/","title":{"rendered":"Monogr\u00e1fico sobre Mysql, curiosidades, trucos y funcionamiento. Parte I"},"content":{"rendered":"

Mysql es un sistema gestor de bases de datos relacionales bastante extendido<\/strong> actualmente. Posee un excelente rendimiento y facilidad de uso, con una licencia dual.<\/p>\n

Es multiusuario, multihilo, adquirido por la empresa Oracle (posee otro gestor de bases de datos objeto relacional<\/strong> que domina el mercado alto rendimiento a nivel empresarial).<\/p>\n

El \u00e9xito de Mysql en parte radica por su sencillez, facilidad, y su uso en aplicaciones filosof\u00eda LAMP.<\/p>\n

Como he comentado muchos usuarios confunden<\/strong> la libertad<\/strong> de usarlo como gestor de nuestras bases, con la gratuidad. De hecho, solamente usaremos MySQL GPL, si la aplicaci\u00f3n que lo emplea tambi\u00e9n 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\u00f3digo es privativo (no quiero enfadar\u00a0a Stallman<\/a>)<\/p>\n

Aunque inicialmente no ofrec\u00eda motor para soporte transaccional, ni integridad referencial autom\u00e1tica (si a nivel de especificaci\u00f3n mediante sentencia DDL)(esto sonar\u00e1 curioso a los amantes de Microsoft Access), actualmente si ofrece ambas caracter\u00edsticas en funci\u00f3n del tipo de motor de almacenamiento seleccionado.<\/p>\n

Otra gran ventaja es su disponibilidad de uso en cualquiera de las plataformas disponibles.<\/p>\n

Seg\u00fan datos de fabricante, existir\u00edan m\u00e1s 6.000.000 de copias de MySQL<\/strong> funcionando en la actualidad, lo que supera la base instalada de cualquier otra herramienta de gesti\u00f3n de bases de datos.<\/p>\n

Es empleado en las herramientas m\u00e1s extendidas de Internet, como puede ser WordPress<\/strong> (1 de cada 4 sitios de internet se realiza con este CMS), Drupal, Moodle, Prestashop, etc \u2026<\/p>\n

Aunque tambi\u00e9n es empleado por empresas grandes Wikipedia<\/strong>,\u00a0Google\u00a0<\/strong>(aunque no para realizar b\u00fasquedas),\u00a0Facebook<\/strong>,\u00a0Twitter<\/strong>,\u00a0Flickr<\/strong>, YouTube<\/strong>\u2026<\/p>\n

El tr\u00e1fico del sitio web de MySQL AB<\/strong> (empresa) super\u00f3 en el a\u00f1o 2004 al del sitio de\u00a0IBM.<\/p>\n

<\/p>\n

Algunos trucos a nivel de comando.<\/strong><\/p>\n

Para conectarnos desde la l\u00ednea de comandos, tendremos que tenerlo l\u00f3gicamente instalado y teclear desde consola:
\nmysql \u2013u usuario \u2013p<\/strong><\/p>\n

Nos pedir\u00e1 la password.
\nUn dato muy importante es que al ser multiusuario, en funci\u00f3n del usuario con el que conectemos tendremos determinados permisos.<\/p>\n

Tambi\u00e9n nos podremos conectar a un gestor remoto:<\/p>\n

Mysql \u2013h elservidor.com \u2013u suario \u2013p<\/strong><\/p>\n

Para desconectar tendremos que teclear quit; (importante siempre para finalizar punto y coma).<\/p>\n

Podemos procesar por lotes de la siguiente forma (una de ellas).<\/p>\n

Mysql \u2013u miusuario \u2013h servidor.com \u2013p <archivo_con_instrucciones.sql<\/strong><\/p>\n

Se pueden almacenar todos los comandos tecleados en el int\u00e9rprete Mysql ejecutando la orden tee.<\/p>\n

Por ejemplo: tee archivo_de_comandos.txt<\/strong><\/p>\n

Para cancelar la captura ejecutar notee.<\/p>\n

Otras Consultas interesantes en l\u00ednea de comandos.<\/p>\n

Show databases;\u00a0<\/strong> \/\/ Muestra las bases de datos existentes.
\nselect database();<\/strong> \/\/ Informa sobre la base de datos actualmente en uso.<\/p>\n

use la_base_de_datos;<\/strong> \/\/ Permite trabajar con la base de datos que seleccionemos.
\nmysql base_de_Datos \u2013u usuario \u2013p<\/strong> \/\/ Permite seleccionar desde l\u00ednea de comandos la base de datos con la que vamos a trabajar.
\nshow tables;<\/strong> \/\/ Muestra las tablas
\ndescribe nombre_tabla;<\/strong> \/\/ Muestra la definici\u00f3n de los campos de una tabla.
\ncreate database nombre_de_la_base;<\/strong> \/\/ Crea una base de datos
\ndrop database nombre_de_la_base;<\/strong> \/\/ borra una base de datos.<\/p>\n

Ejemplo de c\u00f3mo crear una tabla desde el int\u00e9rprete:<\/strong><\/p>\n

 <\/p>\n

mysql> create table personas (\r\n\r\n> nombre varchar(40) not null,\r\n-> direcci\u00f3n varchar(50) null,\r\n-> estado_civil char(13) default \u2019casado\u2019,\r\n-> num_registro int primary key auto_increment,\r\n-> );<\/pre>\n
 <\/p>\n
Cuando se almacena un datos en char, si sobrepasan esos l\u00edmites ser\u00e1n truncados para poderlos almacenar. Es por eso que se suele usar bastante el varchar, con la salvedad\u00a0 de que el valor se ajusta al tama\u00f1o, permitiendo ahorrar espacio.<\/p>\n
Para borrar una tabla.
\nDrop table nombre_de_la_tabla;<\/strong><\/p>\n
Este apartado es interesante para analizar:<\/p>\n
 <\/p>\n
mysql> create table personas (\r\n\r\n-> nombre varchar(40) not null,\r\n-> nacimiento date not null, ->\r\npareja varchar(40),\r\n-> proveedor int not null,\r\n->\r\n-> primary key (nombre,nacimiento),\r\n-> unique (pareja),\r\n-> foreign key (proveedor) references proveedores -> );<\/pre>\n
Primary key<\/strong> define las columnas que ser\u00e1n clave primaria, son por lo tanto no nulas.
\nUnique<\/strong> establece las columnas que no admite duplicados. Son claves candidatas del modelo ER
\nForeign key<\/strong>, define los valores de proveedor que solo existan en proveedores, es una forma de relacionar tablas.<\/p>\n
Las 3 son conocidas como restricciones en la tabla, tambi\u00e9n se les puede dar un nombre a cada restricci\u00f3n mediante constraint<\/strong>.<\/p>\n
Ejemplo<\/strong>:<\/p>\n
Constrain clave primary key (nombre, nacimiento);<\/strong><\/p>\n
Otro aspecto a considerar son los \u00edndices. Pueden establecerse en el momento de definici\u00f3n de la creaci\u00f3n de la tabla o a posteri. Se suele realizar con la palabra key, pueden ser sencillos o compuestos dependiendo de nuestras necesidades.<\/p>\n
Respecto a los tipos de datos, mysql cuenta con muchas alternativas.
\nExisten 3 tipos de clasificaci\u00f3n<\/strong>, los num\u00e9ricos, cadenas de caracteres y tipo fecha-hora.<\/p>\n
El valor null es un caso especial, implica la ausencia de valor.<\/p>\n
Num\u00e9ricos<\/strong>
\nunsigned (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.<\/p>\n
Cadenas de caracteres<\/strong> (de mejor a mayor espacio)<\/p>\n
Char, varchar, tinytext, text(blob), mediumtext, longtext. Dos tipos especiales.
\nTipo enumerado (enum) y tipo set.El car\u00e1cter varchar s\u00f3lo ocupa el espacio requerido por el valor de la columna.<\/p>\n
Fecha y hora<\/strong>
\ndate (3 bytes), time(3), datetime (8),timestamp(4), year(1)<\/p>\n
 <\/p>\n
Para modificar una tabla y columna<\/strong><\/p>\n
Para tablas:
\nalter table<\/strong> mi_tabla add<\/strong> (campo tipo, campo tipo);
\nalter table mi_tabla drop campo; \/\/ Elimina un campo de una tabla.<\/p>\n
Para columnas:
\nalter table<\/strong> mi_tabla modify<\/strong> campo tipo;
\nalter table<\/strong> mi_tabla change<\/strong> nombre_antiguo nombre_nuevo;
\nalter table<\/strong> mi_tabla\u00a0 order by<\/strong> el_campo_para_ordenar;<\/p>\n
Cambiar nombre de la tabla:
\nalter<\/strong> table mi_tabla rename<\/strong> nuevo\u00ad_nombre_tabla;<\/p>\n
Copiar tablas:
\nrename<\/strong> tabla mibase.tabla to mibase_dos.tabla; \/\/ Copia una tabla de una base a otra<\/p>\n
Crear tabla con el contenido de otra:
\ncreate table<\/strong> mi_nueva_tabla_destino\u00a0 select <\/strong>* from tabla_origen;<\/p>\n
Crear tabla con campos o estructura pero si datos:
\ncreate table<\/strong> mi_nueva_tabla_destino like tabla_origen;<\/p>\n
Crear una tabla temporal, solo visible en la sesi\u00f3n activa, \u00fatil para trabajar con resultados intermedios<\/p>\n
Create temporaly table<\/strong> la_nueva_tabla;<\/p>\n
Podemos importar datos de una aplicaci\u00f3n a otra, mediante la creaci\u00f3n de archivos de texto mediante ordenes silimares a esta<\/p>\n
mysql> load data local infile \u2019clientes.txt\u2019 -> into table clientes;<\/strong><\/p>\n
o detallando m\u00e1s el tipo de importaci\u00f3n:<\/p>\n
mysql> load data local infile \u2018clientes.txt\u2019 -> fields terminated by \u2019,\u2019 -> enclosed by \u2019\u201d\u2019 -> lines terminated by \u2019\\r\\n<\/pre>\n
Estas acciones tambi\u00e9n se pueden realizados con mysqlimport<\/strong><\/p>\n
CURIOSIDADES<\/strong><\/span><\/p>\n
Si usamos la expresi\u00f3n explain delate de una consulta select, Mysql nos explica como ha realizado la consulta, si ha empleado \u00edndices, el tipo de join realizado, las claves primarias, etc.<\/p>\n
Su usamos la cl\u00e1usula 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.<\/p>\n
Podemos asignar prioridades a las operaciones LOW_PRIORITY<\/strong> o HIGH_PRIORITY<\/strong> para evitar bloqueos cuando varios usuarios realizan operaciones sobre la base.<\/p>\n
Mediante LAST_INSERT_ID()<\/strong>, nos devuelve el id\u00a0 asignado a una columna auto num\u00e9rica despu\u00e9s de realizar un insert.<\/p>\n
Es preferible realizar instalaciones fuente y luego compilarlas, que instalar los binarios directamente. Aunque la segunda posibilidad es m\u00e1s sencilla, el motor seguramente sea m\u00e1s lento.<\/p>\n
El archivo de configuraci\u00f3n de Mysql suele residir en la carpeta etc\/my.cnf<\/strong><\/p>\n
Podemos acelerar las consultas a la base, mediante la incorporaci\u00f3n de una cache<\/strong>. Si no vamos a trabajar con una base de datos sometida a cambios, podremos usarla y notar mucha mejor\u00eda en el rendimiento. Esta cache se activa mediante variables globales o tocando el archivo de configuraci\u00f3n anterior.<\/p>\n
Recuerda tambi\u00e9n que el empleo de \u00edndices<\/strong> en las tablas, aumenta la velocidad pero necesitan estar activos bajo unos criterios y consejos adecuados. Pero principalmente que los registros de los \u00edndices sean lo m\u00e1s peque\u00f1os posibles y que solo se indexen valores \u00fanicos.<\/p>\n
Otra caracter\u00edstica poco conocida de Mysql es que permite la replicaci\u00f3n<\/strong>. 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\u00e9n de ganar velocidad, al igual que con la cache y los \u00edndices.<\/p>\n
Si queremos que los mensajes del servidor aparezcan el castellano, se debe ejecutar el demonio mysqld con el comando<\/p>\n
mysqld –language=spanish <\/strong><\/p>\n
o agregar la l\u00ednea dentro del etc\/my.cnf<\/strong> con un editor de texto (vi, joe,emac) en la secci\u00f3n<\/p>\n
[mysqld]<\/strong>
\n language = \/usr\/share\/mysql\/spanish<\/strong><\/p>\n

\nCopias de seguridad<\/strong><\/p>\n
Mysql ofrece varios mecanismos crear copias de seguridad<\/strong>.<\/p>\n
Una de ellas, la m\u00e1s directa es copiar los archivos f\u00edsicos binarios con los datos. Para ello es necesario saber d\u00f3nde se almacenan las bases. La ruta puede variar en funci\u00f3n de la distribuci\u00f3n (si es Linux) y el sistema operativo.<\/p>\n
En Linux suelen estar ubicadas en \/var\/lib\/mysql<\/strong><\/p>\n
Podemos usar el comando desde dentro > show variables like \u2019datadir\u2019; para saber d\u00f3nde se almacenar las bases.<\/p>\n
Es importante parar el servidor antes re realizar la copia binaria.
\nmysqladmin -u root -p shutdown<\/strong><\/p>\n
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 \u00edndices. Se diferencian por la extensi\u00f3n.<\/p>\n
Este m\u00e9todo obliga para evitar problemas, a parar el servidor para realizar la copia.<\/p>\n
Mysqlhotcopy <\/strong>posibilita realizar la copia sin parar el servidor, es un script escrito en perl<\/p>\n
Ejemplo \u00a0mysqlhotcopy base_de_datos<\/p>\n
Esto creara una carpeta \u00a0\/var\/lib\/mysql\/copy_base_de_datos<\/strong><\/p>\n
Pero importante, no funciona para bases con motor de almacenamiento INNODB<\/strong><\/p>\n
Otro mecanismo sencillo es mediante<\/p>\n
Mysqldump<\/strong><\/p>\n
Esta copia no es binaria, y genera un archivo de texto con las sentencias DDL y DML SQL. El \u00fanico inconveniente es que los archivos generados son grandes y es un mecanismo lento. Pero no es necesario parar el servidor.<\/p>\n
mysqldump mi_base > mi_base.sql<\/strong><\/p>\n
Es necesario observar el s\u00edmbolo mayor, que a modo de tuber\u00eda t\u00edpica de los interpretes env\u00eda la base al archivo .sql<\/p>\n
Restauraci\u00f3n de las copias (administradores de sistemas)<\/strong><\/p>\n
Si la restauraci\u00f3n es binaria, tendremos que parar el servidor.
\nmysqladmin -u root -p shutdown<\/strong><\/p>\n
Y copiar los archivos a su destino.
\n\/var\/lib\/mysql<\/strong> (en la mayor\u00eda de los casos en sistemas GNU\/Linux)<\/p>\n
Pero ojo, es necesario restaurar el propietario y el grupo de los archivos copiados.<\/p>\n
chown -R mysql.mysql \/var\/lib\/mysql<\/strong><\/p>\n
Y si la copia ha sido mediante un archivo .sql<\/p>\n
mysql mi_base -u admin -p < mi_base.sql<\/strong><\/p>\n
 <\/p>\n
Reparaci\u00f3n de las tablas en caso de problemas.<\/strong><\/p>\n
En algunas ocasiones, y es m\u00e1s 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\u00faan bloqueos, accesos, escrituras y es posible que en un momento determinado, los \u00edndices de la tabla queden da\u00f1ados. Tambi\u00e9n condiciones f\u00edsicas, apagones, fallos de hard, etc.<\/p>\n
Vamos a explicar el equivalente a compactar y reparar datos en una base de datos Access.<\/p>\n
Para ello, desde la l\u00ednea de comandos.<\/p>\n
Ckeck table tabla_a_analizar;<\/strong><\/p>\n
Si nos devuelve un OK, la tabla est\u00e1 bien. Si la tabla se encuentra da\u00f1ada nos dar\u00e1 errores.
\nEs importante saber que Mysql no puede realizar consultas sobre una tabla da\u00f1ada, desatando el error.<\/p>\n
Con este otro comando
\ncheck table tabla_a_analizar extended;<\/strong><\/p>\n
Muestra m\u00e1s informaci\u00f3n sobre el error que tenemos.<\/p>\n
Es el momento de ejecutar el comando repair<\/p>\n
Repair table tabla_a reparar;<\/strong><\/p>\n
Es preciso aclarar, que esto no es la panacea, ya que al reparar se suelen perder en ocasiones registros, y que es una acci\u00f3n no deseada l\u00f3gicamente, pero necesaria para poder seguir trabajando. Por lo que es aconsejable tirar de las copias de seguridad.<\/p>\n
El comando optimize table tambi\u00e9n se encarga de realizar algunas correcciones.<\/p>\n
Existe una utilidad myisamchk, que permite reparar las tablas, pero ojo, solo de tipo MyISAM<\/strong>. Para ejecutarlo, el servidor no debe estar ejecut\u00e1ndose.<\/p>\n
Ejemplo: myisamchk *.MYI<\/strong><\/p>\n
No obstante, antes de esto, es preferible repito disponer de copia de seguridad.<\/p>\n
SEGURIDAD<\/strong><\/p>\n
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.<\/p>\n
Mysql almacena toda la informaci\u00f3n de los usuarios y el sistema de privilegios en una base de datos cuyo nombre es mysql<\/strong>. Su estructura podemos verla mediante use mysql; show tables<\/strong>;<\/p>\n
El comando show privileges es tambi\u00e9n importante, ya que si realizamos cambios en informaci\u00f3n que afecta a privilegios de usuarios, para que el gestor se entere de los cambios es necesario ejecuitar un<\/p>\n
mysql> flush privileges;<\/strong>\u00a0 \/\/ Es como un refresh<\/p>\n
Aqu\u00ed os dejo un ejemplo de creaci\u00f3n de un usuario:<\/p>\n
GRANT ALL PRIVILEGES ON nombre_base.* TO \u2018nombre_usuario\u2019@\u2019%\u2019 IDENTIFIED BY \u2018contrase\u00f1a\u2019;<\/strong><\/p>\n
Cada usuario dispondr\u00e1 de sus privilegios.<\/p>\n
La administraci\u00f3n de privilegios y usuarios en MySQL se realiza a trav\u00e9s de las sentencias:<\/p>\n