Etiqueta: MySQL

Script para automatizar backups de MySQL en servidores Windows

en por Donnie Rocken Informática, sistemas y redes, SQL8 comentarios

Aunque en servidores Windows lo más habitual es trabajar con el SQL-Server de Microsoft como SGBD a veces puede tocarnos instalar un MySQL. Entonces nos asalta la duda ¿cómo automatizo un backup de la base de datos? Una de las tareas más habituales de todo administrador.

En la instalación de la base de datos se os instalará el programa mysqldump.exe para la gestión de copias de seguridad. Este programa nos permite, por medio de la línea de comandos, realizar un backup de la base de datos. Con la siguiente sintaxis, a grosso modo, haréis la copia de seguridad:

mysqldump –u (TuUsuario) –p(password) (nombre_base_datos) > (nombreArchivo).sql

Dato importante: entre -u y el nombre de vuestro usuario hay un espacio, entre -p y el password NO. Para más información sobre este comando, podéis visitar el artículo pertinente en el manual de MySQL.

Podéis abrir con CMD un terminal de comandos, situaroes en vuestra carpeta de MySQL y, situándoos allí en la carpeta bin, ejecutar el comando con los parámetros que correspondan para comprobar su funcionamiento. Una vez visto que mysqldump funciona el siguiente paso es crear un script de batch (con la extensión .bat) que ejecute el comando incluyendo la fecha. Los backups los guardaremos en una carpeta llamada C:\backups, y a la hora de escribir el script os vale cualquier editor, mismo el Notepad (aunque yo suelo usar en Windows Notepad++):

echo off                                        
set FECHA= %date%
set FECHA=%FECHA:/=%
mysqldump -e -u root –p7j91lt0y articulos > C:\backups\backuparticulos%FECHA%.sql

NOTA:Edito porque esta semana me he topado con que en el PowerShell de Windows 2k12 no me estaba funcionado esa instrucción, y también me han comentado hoy que el script no funcionaba. Lo mejor es que probéis primero en el intérprete de comandos si funciona, y si os falla la de arriba podéis cambiar la sintaxis por esta:

mysqldump --user=root --password=7j91lt0y articulos > C:\backups\backuparticulos%FECHA%.sql

Con la primera instrucción desactivamos la salida por pantalla de la consola, luego recogemos la fecha actual, la formateamos y finalmente creamos el backup anexando a su nombre la fecha de la copia. En este ejemplo he usado el usario root, recordad siempre que el usuario que utilicéis debe tener permisos en la base de datos para realizar la copia de seguridad. Guardad este script en la carpeta bin de vuestra instalación de MySQL.

Pues ya tenemos el script listo, ejecutadlo con permisos de administrador para ver que funciona. Si os crea un archivo vacío es que algo falla, repasadlo (la sintaxis, la contraseña, el nombre, los permisos tanto en la bd como en el sistema operativo…). Ahora lo que nos queda es automatizar la ejecución del script para programar, por ejemplo, una copia diaria de la base de datos. Para eso os váis al menú de administración del servidor y creáis una tarea básica que ejecute el batch a diario a una hora predeterminada (a poder ser una hora con poca actividad para la base de datos). Para más ayuda con el tema de la automatización podéis visitar la web de Microsoft.

Stored Procedure para guardar y devolver datos encriptados con AES (MySQL)

en por Donnie Rocken Informática, SQL1 comentario

Esto llevaba un tiempo en el TODO list del blog, y a raíz de unos comentarios en el artículo sobre encriptación AES en MySQL y MariaDB he decidido hacerlo al fin. La cosa es que queremos guardar un dato encriptado en la base de datos pero de forma que sea transparente para el usuario, que cuando pide el dato lo recibe en texto plano.

El primer punto a la hora de guardar datos encriptados en AES es que debemos almacenarlo en un campo de tipo BLOB, ya que este algoritmo nos devolverá un resultado binario, no texto plano.

Por resumir vamos a imaginar una tabla que sólo tiene un Id autonumérico y un campo con un texto encriptado. Habrá dos procedimientos: uno para insertar los datos y otro para recuperarlos.

El procemiento que inserta sería así:

CREATE PROCEDURE sp_insertDatoEncriptado (IN `texto` varchar(128), IN `password` varchar(25))
DETERMINISTIC
BEGIN
    Insert Into 
      tabla (`TextoSecreto`)
    Values
      (AES_ENCRYPT(texto,password))
END

Y para recuperar los datos la cosa iría por este camino:

CREATE PROCEDURE sp_getDatoEncriptado (IN `password` varchar(25))
DETERMINISTIC
BEGIN
    Select
      Id,
      AES_DECRYPT(TextoSecreto, password) AS 'TextoSecreto'
    From
      tabla
END

Consulta SQL para conocer el número de campos de una tabla

en por Donnie Rocken SQLDeja un comentario

Por avatares del destino hoy estaba modificando una página en ASP clásico y necesitaba saber el número de campos que tenía una tabla. Basta con una consulta para tener estos datos:

SELECT Table_Name, COUNT(*) As NumeroCampos
FROM Information_Schema.Columns
WHERE Table_Name = 'nombre_de_la_tabla'
GROUP BY Table_Name;

Si queréis saber el de todas las tablas de la base de datos podéis hacer lo siguiente:

SELECT Table_Name, COUNT(*) As NumeroCampos
FROM Information_Schema.Columns
GROUP BY Table_Name
ORDER BY Table_Name

Hacer un UPSERT en MySQL

en por Donnie Rocken Informática, SQLDeja un comentario

Si el otro día os contaba cómo hacer un INSERT IF NOT EXISTS en MySQL, hoy vamos a ver cómo hacer un UPSERT. La idea es intentar un INSERT, pero en caso de que se de una duplicidad de un campo único (una clave primaria, un campo UNIQUE…) en lugar de insertar actualizará el campo en cuestión. Hasta esta mañana desconocía esta forma de insertar, pero me lo comentaron en StackOverflow por un problemilla que tenía con un procedimiento almacenado.

La sintaxis sería más o menos:

INSERT INTO tabla (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE c=c+1, b=2;

Os pongo un ejemplo práctico, metido dentro de un procedimiento almacenado. Recibe el id de un usurio y una variable bit que representa si se ha conectado o ha cerrado sesión, guarda estos datos junto con el momento en que se ha realizado dicha acción en una tabla usada para llevar el control de lo mismo (bueno, es más o menos lo mismo que me pusieron en SO):

DELIMITER $$
CREATE PROCEDURE `sp_UltimoLogin`(id_in int, accion_in bit)
BEGIN
  INSERT INTO `login` (`idusuario`, `fecha`, `accion`) VALUES (id_in, now(), accion_in)
    ON DUPLICATE KEY UPDATE `fecha` = now(), `accion` = accion_in;
END $$

Un truquito que os ahorrará realizar comprobaciones en muchos supuestos, aligerando la carga de trabajo y reduciendo líneas de código.

Optimización de consultas en MySQL

en por Donnie Rocken Informática, SQL1 comentario

Vamos con una serie de consejos a la hora de optimizar consultas en MySQL:

  • No uses el comodín * si no quieres recuperar todos los datos. Estarás aumentando innecesariamente el volumen de datos.
  • En selects, updates y deletes utiliza LIMIT 1 cuando sólo esperes un resultado. De esa forma cuando lo encuentre parará, y no recorrerá toda la tabla. Esto además te valdrá como medida de seguridad, ya que un pudate o delete mal hecho puede joderte una tabla y empantanarte en una recuperación de datos.
  • En las cláusulas where no utilices paréntesis innecesarios.
  • Si al hacer una consulta con GROUP BY o DISTINCT sabes que el resultado va a ser reducido usa la opción SQL_SMALL_RESULT, que usará tablas temporales dándote mayor velocidad en el acceso a datos.
  • COUNT(*) sólo se debe utilizar en consultas sin WHERE y que afecten a sólo una tabla.
  • En los selects que impliquen varias tablas utiliza alias y ponlos antes de los campos. Ahorrarás tiempo al no tener que buscar la tabla a la que pertenece.
  • Para unir tablas no uses un where. Funciona, pero ahí el motor hace el producto cartesiano de todas las tablas implicadas y luego lo filtra. INNER JOIN, LEFT JOIN o RIGHT JOIN son tus fieles amigos. Si los usas las tablas se irán uniendo a medida que las declaras
  • Usa columnas NOT NULL si es posible. Las columnas NULL requieren de un bit extra.
  • Para ordenación aleatoria no uses ORDER BY RAND(). En ese mismo blog ya dimos opciones alternativas
  • Analiza tus consultas con EXPLAIN. Si pones EXPLAIN antes de tu consulta recibirás una explicación de cómo se realiza esta en el servidor. Esto te permitirá ver formas de optimizarla.
  • A la hora de relacionar tablas usa siempre campos que estén indizados, ganarás en velocidad.
  • Optimiza tus índices. Ten en cuenta que suelen agilizar las lecturas pero ralentizar las inserciones (bueno, lo de ralentizar es en Microsoft SQL-Server, debería mirar sin en MySQL también ocurre eso). Aquí tienes un buen artículo sobre el tema (aunque puede que dentro de poco haga yo uno)

En fin, una serie de consejillos para mejorar el rendimiento de vuestra base de datos. Por cierto, y a modo anecdótico. Hace años un ingenieron me dijo que no usara LIMIT 1 porque era «meter más consultas que no van a hacer nada porque estás buscando sobre un ID»… todavía no he encontrado a nadie más que defienda esa teoría (igual era ingeniero agrónomo y no informático, eso explicaría mucho).

Insert IF NOT EXISTS en MySQL

en por Donnie Rocken SQL4 comentarios

Muchas veces necesitamos crear una entrada en una tabla sólo en el caso de que no exista ya. Por desgracia no existe una sintaxis de INSERT IF NOT EXISTS, como sí existe por ejemplo con CREATE. Os estaréis cagando en mi porque el título os dio esperanzas… Tranquilos, existe una simple alternativa, haciendo uso de SELECT FROM dual con una condición WHERE NOT EXISTS:

Veamos un ejemplo, y debajo lo explico. Vamos a insertar un pueblo en una tabla sólo si no ha sido insertado ya:

INSERT INTO poblaciones(Nombre)
SELECT 'Bergantiños'
FROM dual
WHERE NOT EXISTS (SELECT Nombre FROM poblaciones WHERE Nombre = ‘Bergantiños’)LIMIT 1

Como veis usamos SELECT FROM dual, un nombre de tabla que se especifica en MySQL, Oracle o PostgreSQL como nombre de tabla falsa en asignaciones en las que no usamos tablas realmente. De esta forma podemos usar la condición WHERE NOT EXISTS para comprobar con una búsqueda que el campo no está en la tabla. En la búsqueda uso LIMIT 1 para que pare en cuanto encuentre un resultado y reducir la carga de trabajo.

(Editado, y gracias a infocat20, que me avisó de que tenía el limit mal colocado)

Bucles y condicionales en procedimientos almacenados de MySQL

en por Donnie Rocken SQL7 comentarios

A la hora de crear procedimientos almacenados en MySQL tenemos la opción de utilizar distintos tipos de bucles y de condicionales, lo que nos dotará de la capacidad de llevar a cabo acciones más complejas dentro de nuestros procedimientos.

Para esto disponemos de las estructuras de control IF THEN ELSE, CASE, WHILE y REPEAT, además de poder definir y declarar variables locales dentro de los procedimientos.

Variables:
Para declarar una variable la definimos con DEFINE y la inicializamos con SET:

DEFINE varprueba INT; #definimos la variable y su tipo
SET varpruebra = 10; #le damos un valor concordante con el tipo

IF THEN ELSE:
El condicional simple IF que tenemos en la mayoría de los lenguajes de programación, que puede ser complementado o no con un ELSE para el caso de que no se cumpla. La sintaxis es IF condición THEN (el ELSE si hace falta) END IF. Podéis ver un ejemplo:

delimiter $$
CREATE procedure ej(IN val int)     /* Parámetro de entrada */
  begin    
    IF val = 0 then
      INSERT INTO tbejemplo VALUES('No disponible');
    else
      INSERT INTO tbejemplo VALUES(val);
    end IF;
  end$$
delimiter ;

CASE:
Condicional que nos permite evaluar el valor de una variable y realizar diversas acciones según varias posibilidades. Es una forma de abreviar un montón de IF anidados. En este caso el ELSE nos permite definir un valor por defecto (como el default en un switch/case de C, Java o PHP):

delimiter $$
CREATE procedure ej(IN val int)     /* Parámetro de entrada */
  begin
    case val
     when 5 then INSERT INTO prueba VALUES('Suficientes');
     when 4 then INSERT INTO prueba VALUES('Reserva');
     else INSERT INTO prueba VALUES ('Insuficientes);
    end case;
  end$$
delimiter;

WHILE:
Un bucle que hace que un procedimiento se repita mientras se de una condición. Si la condición ya no se da antes de entrar al bucle, no entra.

delimiter $$
CREATE procedure ej(IN val int)     /* Parámetro de entrada */
  begin
    define i int;
    set i = 0;
    while i<5 do
      INSERT INTO prueba VALUES (i);
      set i=i+1;
    end while;
  end$$
delimiter ;

REPEAT:
Lo contratio que WHILE, repeat es un bucle UNTIL, se realiza hasta que se de una condición. Si bien también comparte con el DO…WHILE de C (y Java, y PHP…) que siempre se ejecuta al menos una vez.

delimiter $$
CREATE procedure ej(IN val int)     /* Parámetro de entrada */
  begin
    define i int;
    set i = 0;
    repeat
      INSERT INTO prueba VALUES (i);
      set i=i+1;
      until i <= 5;
    end repeat;
  end$$
delimiter ;

Con esto ya podéis empezar a programar un poco más en serio dentro de MySQL.

Scripts para parar, arrancar y reiniciar Apache y MySQL en Ubuntu

en por Donnie Rocken Informática, sistemas y redesDeja un comentario

Muchos desarrolladores tenemos en nuestro equipo un entorno LAMPP para testear nuestros trabajos web. Como generalmente uso el ordeador para trabajar tengo configurado que arranquen con el equipo por defecto tanto MySQL como Apache, ya que rara vez estoy ON en mi equipo y no estoy trasteando con algo de código.

Pero a veces, para virtualizar o para trabajar con algún editor de vídeo me veo obligado a tener toda la memoria posible para que la cosa no se torne inusable. Cierto que son sólo dos líneas en el terminal… pero con cada script las dejo en una sola:

Script para parar Apache 2 y MySQL:

#!/bin/bash/

sudo apache2ctl -k stop
sudo /etc/init.d/mysql stop

Script para arrancar Apache2 y MySQL

#!/bin/bash/

sudo apache2ctl -k start
sudo /etc/init.d/mysql start

Y finalmente, script para reiniciarlos

#!/bin/bash/

sudo apache2ctl -k restart
sudo /etc/init.d/mysql restart

Te dará una serie de warnings, pero ni caso, funciona (compruébalo intentando conectar con MySQL o intentando ver algunha página en local).

En fin, por hoy nada más.

Usar codificación utf-8 con MySQLi

en por Donnie Rocken Informática, Lenguajes de programación3 comentarios

Como estos días estoy hablando de MySQLi voy a seguir un poco más con el tema y comentar una peculiaridad: si quieres recuperar datos de una BD donde se hayan guardado en utf-8 y manipularlos e imprimirlos sin problemas de codificación tienes que especificarlo.

Es decir, no basta con que los datos estén en la base codificados como utf-8 y que, además, en la web también esté definido que usaremos esta codificación. No, además debemos indicar a MySQLi que queremos trabajar con utf-8 específicamente. Esto no es para nada complicado porque ya hay una función que te permite elegir la codificación. Un ejemplillo de código simplón os lo aclarará:

$con = new mysqli("server", "user", "password");
if (mysqli_connect_errno($con)) { //comprobamos que no haya fallo en la conexión
	die("Fallo al conenctar a MySQL: " . mysqli_connect_error());
}
				
if (!$con->set_charset("utf8")) {//asignamos la codificación comprobando que no falle
       die("Error cargando el conjunto de caracteres utf8");
}

Como podéis ver, es simple: creamos la conexión y luego simplemente definimos un juego de caracteres. En el ejemplo he usado utf-8, pero vamos, que podéis usar cualquiera de los soportados por MySQL.

PHP prepared statement (consulta parametrizada) con MySQLi

en por Donnie Rocken Informática, Lenguajes de programación1 comentario

Si bien en el pasado ya vimos como hacer una consulta parametrizada en este blog de una forma primitiva (fue un artículo antiguo y a día de hoy no lo haría así ni lo recomiendo, por lo que quedaos con este) o haciendo uso de PDO, toca ahora ver cómo hacerlo con MySQLi.

¿Por qué usar MySQLi y no PDO? En fin, todo depende del caso. PDO nos permite abstraernos del SGBD, con sólo cambiar un parámetro en la creación nuestro código funcionará en Postgres, MySQL, Oracle… MySQLi, en cambio, sólo funciona con MySQL. En principio parece que no hay ningún motivo para usar MySQLi, pero si nos metemos en temas de rendimiento veremos que MySQLi va a penalizar menos el rendimiento de la página que PDO. Es decir, trabajando con MySQL nuestra web requerirá menos recursos usando MySQLi que PDO. Si por lo que sea estás con un proyecto que estás seguro de que no a tener que migrarse de SGBD, si sabes positivamente que se va a usar MySQL durante mucho tiempo, entonces MySQLi es tu opción.

Entonces ¿cómo hacemos una consulta parametrizada con MySQLi? Bueno, veamos como se hace una SELECT que recogería unos datos de la BD vinculados a un id del que disponemos en nuestro PHP?


$mysqli = new mysqli ("servidor", "usuario", "password", "nombre_base_de_datos"); //creamos el objeto MySQLi<code>
</code>

if($mysqli->connect_errno){ //comprobamos que no haya errores de conexión

die( 'La cagaste, esto no va'); //mensaje de error

}

if(!$prepared_st = $mysqli->prepare("SELECT * FROM tabla WHERE id = ?")){//creamos el statment y controlamos los errores

die("La cagaste again"); //mensaje de error

}

&nbsp;

//ahora toca vincular el parámetro id a la consulta

$id=5;

if(!$prepared_st->bind_param("i",$id)){ //vinculamos el parámetro

die("Fallito en la vinculación");

}

if(!$prepared_st->execute()){//ejecutamos la consulta

die("Fallo en la ejecución");

}

$resultado = $prepared_st->get_result(); //recogemos resultado

$prepared_st->close(); //cerramos el prepare

$mysqli->close(); //cerramos la conexión a la base de datos

Bueno, toca una pequeña explicación de bind_param: como veis en este caso recibe dos parámetros. El primer parámetro de bind param siempre es una cadena donde le pasamos una serie de letras que identificarán los tipos de datos que le vamos a proporcionar luego con las variables. Es decir, en el ejemplo le decimos primero que le va a llegar un entero, y luego pasamos la variable de ese entero. Si quisiéramos pasar un entero y una cadena (en se orden) el primer parámetro sería «is» y luego irían la variable entera y la variable cadena. El orden es fundamental.

Finalmente recogemos el resultado (para usar get_result() tienes que tener instalada la extensión mysqlnd, si no tendrás que usar bind_result()), que podemos convertir a array con las funciones fetch_array o fetch_assoc según convenga. En cualquier caso en la web de php.net tenéis más información con la que trabajar. Para el resto de operaciones CRUD la cosa va siendo similar: consulta, vinculamos parámetros, ejecutamos, recogemos resultado si procede.