Si por algún motivo se nos queda nuestro sitio de Drupal en modo offline, y no podemos acceder para recuperar el estado normal, ejecutando la siguiente consulta SQL conseguiremos que nuestro sitio vuelva a estar disponible.
UPDATE variable SET value = 's:1:"0";' WHERE name= 'site_offline'; DELETE FROM cache WHERE cid = 'variables';
Siguiendo con las entradas relacionadas con mis notas mentales, hoy me he vuelto a cruzar con una situación que se me plantea de vez en cuando y siempre tengo que tirar de la documentación.
El escenario es el siguiente, tengo un programa en php que se ejecuta en terminal que me realiza una consulta e extracción de datos de una base de datos paradox que esta en un servidor, si he dicho paradox, y me genera un fichero csv el cual a continuación por medio de una consulta de load data infile se me vuelca a una base de datos mysql que tengo en otro servidor. Resulta que la consulta load data infile no he conseguido que se me ejecute, en cuando lo solucione publicare la solución así que lo que hago es una vez terminado el proceso de volcado me conecto al servidor vía terminal y ejecuto la consulta load data infile desde la terminal. Si esto lo hago de esta manera y no ejecutando la consulta directamente es debido a lo lento que funciona el paradox. El proceso de volcado de los datos de paradox al fichero oscila entre los 8-10 minutos, realizando la operación directa el proceso duraba entre 45 minutos a 1 hora.
Primero me conecto al servidor remoto mysql con un usuario y contraseña que tenga permisos para acceso remoto, añadiendo al final la opción –local-infile=1 para poder subir ficheros
#mysql -u [usuario] -p -h [host remoto] --local-infile=1
A continuación ejecuto la consulta para que me vuelque el fichero
LOAD DATA LOCAL INFILE [fichero] REPLACE INTO TABLE [db.table] CHARACTER SET [tipo caracteres] FIELDS TERMINATED BY [terminación campos] ([columna1], [columna2], ..);
En [fichero] ponemos entre comillas la ruta completa del fichero en nuestro ordenador, en [db.table] el nombre de la base de datos y la tabla en la que vamos a volcar los datos, en tipo carácter ponemos entre comillas el tipo de codificación p.e cp1250, utf8, en [terminación campos] como están delimitados los campos, p.e ; y finalmente entre paréntesis las columnas del la tabla.
Una vez ejecutado puedo decir que en mi caso se volcaron 42673 registros en 0.53 segundos, esta misma operación la he realizado generando un fichero sql para la inserción de los datos y el tiempo era infinitamente superior a este.
Con esta entrada voy a inaugurar una serie de minientradas que iré creando, que voy a llamar notas mentales, serán cosas muy cortas y que si no las usamos con frecuencia, siempre que tenemos que tirar de ellas nos toca googlear.
La primera es la consulta para crear una clave foránea en MySQL en una tabla ya creada
ALTER TABLE `miTabla` ADD FOREIGN KEY (campodemiTabla) REFERENCES `otraTabla` (`Id`) ;
Seguramente en alguna ocasión nos habremos visto, o nos podemos ver en la situación de borrar de dos tablas un dato, un ejemplo clásico seria el de creo una factura y esta contiene productos, con lo que podríamos tener una tabla de Facturas y otra tabla que fueran detallesFacturas, en la primera guardaríamos los datos generales de la factura y en el la segunda los artículos o lineas que comprondría la factura, pues bien, si borro la factura tengo que borrar también los artículos que estaban en esa factura, y eso tendría que poder hacerlo con una sola consulta.
| Tabla Facturas | Tabla detallesFacturas |
| Id | Id |
| (Resto campos) | IdFactura |
| IdProducto | |
| (Resto campos) |
Imaginemos que tenemos la factura 123 y tenemos que borrarla y queremos borrar también todos los registros referentes a esa factura en la tabla detallesFactura.
La consulta de borrado quedaría así
DELETE a1, a2 FROM Facturas AS a1 INNER JOIN detallesFacturas AS a2 WHERE a1.Id=a2.IdFactura AND a1.Id LIKE 123;
Otra opción podria ser
DELETE FROM a1, a2 USING Facturas AS a1 INNER JOIN detallesFacturas AS a2 WHERE a1.Id=a2.IdFactura AND a1.Id LIKE 123;
Y con esta consulta borraríamos la factura de la tabla Facturas y los datos de la factura que hubiera en la tabla de detallesFacturas.
Mas info aqui: http://dev.mysql.com/doc/refman/5.1/en/delete.html
Una de las cosas con las que toca lidiar practicamente siempre que trabajamos con fechas contra una base de datos MySQL es que la base va a almacenar las fechas de la manera ISO «año-mes-dia» y nosotros a la hora de trabajar trabajos con el formato Europeo, ya sea «dia-mes-año» o «dia/mes/año».
Durante muchos años trabajaba con la tecnica de la función bidireccional, es decir, me creaba una función la cual cuando le llegaba la fecha la convertia en un sentido u otro.
function cambiaFormatoFecha($fechaOriginal)
{
//Dividimos la fecha de la hora si existe
$partesStamp = explode(" ", $fechaOriginal);
// La primera parte es la fecha, la segunda la hora
$partesFecha = explode ( "-", $partesStamp[0] );
// La fecha final
return $partesFecha[2] ."-".$partesFecha[1]."-".$partesFecha[0];
}
MySQL guarda las fechas separadas por guiones, así que si nosotros definimos el formato de fecha separado con guiones, pues eso que tenemos ganado y esta función nos convierte tanto nuestras fechas cuando las introducimos como las fechas que devuelve el MySQL.
Trabajar con esto puede parecer que es una gran ventaja, pero la verdad que a nivel de rendimiento no lo es ya que supone ejecutar la función cada vez que aparezca un registro de fecha.
Una solución bastante mas eficiente es la de procesar las fechas en la consulta. Para eso utilizaremos las funciones incorporadas de MySQL DATE_FORMAT y STR_TO_DATE.
Si queremos unos registros de una determinada fecha podemos hacer:
SELECT * FROM `tabla` WHERE fecha LIKE STR_TO_DATE('mifecha','%d-%m-%Y);
En esta consulta mifecha esta en el formato dia-mes-año
Si queremos ver las fechas en el formato dia-mes-año
SELECT DATE_FORMAT(fecha,'%d-%m-%Y) FROM `tabla`;
Para mas información
STR_TO_DATE http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date
DATE_FORMAT http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_date-format
Dejo aquí lo que seria los parámetros básicos para conectarnos a una base de datos MySQL con Python y que nos devuelva los resultados de una consulta
import MySQLdb as dbapi
bbdd = dbapi.Connect(host='localhost',user='myuser',passwd='mypass',db='mydb')
cursor = bbdd.cursor()
sql = "SELECT * FROM table WHERE field like %s"
params = 'myparam'
cursor.execute(sql, params)
for datos in cursor.fetchall():
print datos
Por defecto el paramstyle en la conexión con MySQL es «format»
en el cual los especificadores se poner de la misma manera que lo haríamos con el printf en C
Si quisieramos establecer otro tipo de especificador lo hariamos de la siguiente manera
bbdd.paramstyle = "qmark" # los especificadores con ? "select * from table where valor=?" bbdd.paramstyle = "numeric" # numero indicando la posicion "select * from table where :1" bbdd.paramstyle = "named" #el nombre del valor :nombre "select * from table where :nombre"
Esta es una clase la cual la uso muchísimas veces para establecer la conexión a una base de datos con PHP. Se establece la conexión via PDO y esta basada en el patrón Singleton. Una de las ventajas que tiene esta clase es que solo establece la conexión una vez, y por muchas veces que sea instanciada no duplica la conexión.
final class DBConnection {
private static $_handle = null;
private static $_dsn = "mysql:dbname=mydb;host=my.host.com;port=3306";
private static $_user = "myUserName";
private static $_password = "myPassword";
private static $_options = array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"); // SET UTF-8
/**
* Deny Construct
*/
private function __construct()
{
}
/**
* Deny Clone
*/
private function __clone()
{
}
/**
* Function to connect width database
*/
function connect()
{
if ( is_null( self::$_handle ) ) {
try {
self::$_handle = new PDO(
self::$_dsn, self::$_user, self::$_password, self::$_options
);
} catch ( PDOException $error ){
die ( $error->getMessage() );
}
}
return self::$_handle;
}
}
La tengo disponible en http://gist.github.com/1771941 o para clonar con Git en git://gist.github.com/1771941.git
Para saber si tenemos activada la query cache en nuestro servidor, usaremos el siguiente comando:
mysql> SHOW VARIABLES LIKE 'have_query_cache';
Para visualizar que uso estamos realizando de la Query cache:
mysql> SHOW STATUS LIKE 'Qcache%';
Podemos desfragmentar la ‘query cache’ para un mejor uso:
mysql> FLUSH QUERY CACHE;
Con esta sentencia no borramos ninguna consulta de la cache
En cambio si usamos:
mysql> RESET QUERY CACHE;
Se borran todos los resultados almacenados en la query cache al igual que si usamos:
mysql> FLUSH TABLES;
Ambos comandos cierran todas las tablas abiertas, fuerza a todas las tablas en uso a ser cerradas y vacia la query cache.
MySQL :: MySQL 5.1 Reference Manual :: 7.6.3.4 Query Cache Status and Maintenance.
Todos nos acordamos de las copias de seguridad o bien cuando no la hemos hecho, y sobre todo cuando el problema es grande. Una manera de automatizar las copias de nuestras bases de datos es por medio de este script el cual es muy fácil de configurar y muy fácil de instalar, y seguro que de alguna nos salvara.
Si tenemos un sistema basado en debian ( como ubuntu ) con el apt-get install se nos bajara y se nos instalara en el sistema.
sudo apt-get install automysqlbackup
Una vez instalado vamos a configurar los 4 parámetros básicos para que funcione en el fichero automysqlbackup que generalmente esta en /etc/default
USERNAME=myUser #Usuario con permisos en la base de datos PASSWORD=myPass #Contraseña del usuario DBHOST=localhost #En la mayoría de los casos estará en localhost DBNAMES="myDb" #Esto si queremos una en particular si son mas bases las ponemos separadas por coma
Ahora ya solo queda añadir la tarea al crontab y ya estarán programadas las copias de seguridad
crontab -e
Con esto abrimos la edición de la crontab
# m h dom mon dow command 0 0 * * * automysqlbackup > /dev/null 2>&1
La parte del /dev/null 2 >&1 la pongo por si se producen errores en el comando estos no salgan por pantalla.
+Info: AutoMySQLBackup | Free software downloads at SourceForge.net.
vía AutoMySQLBackup | Free software downloads at SourceForge.net.
Hasta la version 5.1 a la hora de establecer los parametros para la replicación en el servidor slave podiamos especificarlos en el my.cnf con los parametros
master-host=10.0.0.1 #ip del servidor maestro master-port=3306 # puerto del servidor maestro master-user=rpl # usuario con permisos para la replicación master-password=password #contraseña del usuario
A partir de esta version estas opciones han sido marcadas como deprecated y hay que establecer los valores de otra manera, ya que desapareceran en la version 5.5.
Una manera que tenemos de configurar estos parametros es con la consola de MySQL y el comando CHANGE MASTER TO
CHANGE MASTER TO MASTER_USER = 'user_name'; CHANGE MASTER TO MASTER_PASSWORD = 'password';
rubenlacasa.es 