En engine BLACKHOLE de MySQL se puede usar para hacer desparecer los datos de algunas tablas que no nos interesen. Por ejemplo, en un entorno master-slave podemos eliminar una tabla grande que no nos cabe en disco. O también, si tenemos una aplicación que no podemos modificar, descartar los datos que se introducen en dicha tabla.

Primero deberemos mirar si la instalación de MySQL soporta el engine BLACKHOLE, lo podemos ver con SHOW ENGINES:

mysql> show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   |

Al arrancar un servidor MySQL nos podmeos encontrar en los logs un error similar al siguiente:

110510  9:29:06 [Warning] 'user' entry 'root@systemadmin.es' ignored in --skip-name-resolve mode.

Esto se debe a la contradicción que existe entre usar nombres DNS y decirle en la configuración que no los resuelva:

# grep resol /etc/my.cnf 
skip-name-resolve

Las entradas que deban resolver nombres simplemente se ignoraran en los permisos, por lo que podemos pasar a usar IPs o rangos de IPs en lugar de nombres. O bien, eliminar la opción skip-name-resolve del arranque del MySQL.

Relacionados

Tal como vimos en la instalación de MySQL Cluster, el nodo de gestión (ndb_mgmd) se encarga de arbitrar el cluster. Vamos a ver cómo redundar dicho servicio.

Para añadir más de un nodo de gestión, deberemos modificar su fichero config.ini, añadiendo en la definición otro nodo, por ejemplo:

[ndb_mgmd]
hostname=10.10.88.15
PortNumber=1186
datadir=/var/mysql/mgm
nodeid=1

[ndb_mgmd]
hostname=10.10.88.14
PortNumber=1186
datadir=/var/mysql/mgm
nodeid=4

Deberemos recargar la configuración, si tenemos un nodo ya activo, con la opción –reload:

/opt/mysql-cluster/bin/ndb_mgmd --skip-daemon \
  -f /usr/local/etc/mysql/config.ini --reload \

Hace un tiempo descubrí una característica (tal vez sea un bug) sobre los campos timestamp de MySQL. Es probable que este documentado en alguna parte que todavía no he leído:

Cuando se añade un campo timestamp a una tabla, MySQL agrega mágicamente algunas características al nuevo campo creado como un “trigger” y la fecha actual como valor por defecto.

Aquí esta el script donde se produce el caso:

-- CREANDO UNA TABLA CUALQUIERA E INSERTANDO DATOS 
mysql> CREATE TABLE t(
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> val VARCHAR(50)
    -> );
Query OK, 0 ROWS affected (0.15 sec)
 
mysql> INSERT INTO t (val) VALUES ("foo") ,("var");
Query OK, 2 ROWS affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings:

Algunos datos que pueden ser interesantes mostrar en un gráfico son las queries en la cache y la memoria libre de la query cache:

mysql> show status like 'Qca%';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| Qcache_free_blocks      | 2          |
| Qcache_free_memory      | 400954376  |
| Qcache_hits             | 1014709429 |
| Qcache_inserts          | 149894868  |
| Qcache_lowmem_prunes    | 1785432    |
| Qcache_not_cached       | 57337894   |
| Qcache_queries_in_cache | 26915      |
| Qcache_total_blocks     | 53982      |
+-------------------------+------------+
8 rows in set (0.00 sec)

Si mostramos las queries en cache en gráfico podemos obtener algo similar a:

En un post anterior comenté que no es suficiente mirar el Seconds_Behind_Master para saber si se esta replicando correctamente un MySQL, por lo que las herramientas de monitorización se deben adaptar a esto.

El siguiente script comprueba no sólo Seconds_Behind_Master, sino también Last_Errno para dar por buena la sincronización entre master y slave. Los dos únicos parámetros que debemos definir en el script son:

• SBMLIMIT: Número de segundos de retraso máximo que permitimos, no nos interesa que por una pequeña desincronización puntual nos este avisando
• AUTOSTARTSLAVE: Si debe intentar arrancar el slave en caso que se lo encuentre parado

Cansado de tener que averiguar manualmente  cuanto espacio ocupan mis bases de datos, acabo de crear un procedimiento almacenado para tener una vista global de las bases de datos que tenemos en nuestro servidor MySQL.

En MySQL no hay disponible  un comando que nos permita tener una resumen global de las bases de datos, algo que se parezca a SHOW TABLE STATUS pero para todas las bases de datos. El comando SHOW DATABASES sólo lista las bases actuales pero no nos dice cuanto espacio ocupan o cuantas tablas hay, etc.

El procedimiento que escribí esta basado en la base de datos INFORMATION_SCHEMA, que contiene mucha información de todas las bases de datos existentes.

Ya antes en un post anterior mencioné que podemos tener todas estas

El MySQL a veces tiene comportamientos sorprendentes o acabas descubriendo porque stopped no es lo mismo que not running. Por ejemplo, comparando una cadena con un int puedes obtener resultados.

Vamos a suponer la siguiente tabla de ejemplo con datos:

mysql> create table test(id int, txt text);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test values(1,"uno");
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(2,"dos");
Query OK, 1 row affected (0.00 sec)

mysql> insert into test values(3,"tres");
Query OK, 1 row affected (0.00 sec)

Cualquier podría suponer que si comparamos el id con un string no obtendremos ningún resultado:

En los cambios de MySQL 5.5 a MySQL 5.6 podemos encontrar el siguiente comentario que puede sonar raro:

Important Change: Replication: Formerly, the value of the Seconds_Behind_Master column in the output of SHOW SLAVE STATUS was always set to NULL whenever the SQL thread or the I/O thread was stopped. Now, this column is set to NULL only if the SQL thread is not running, or if the I/O thread is not running following a check to determine whether or not the SQL thread has processed all of the relay log. (If the SQL thread has finished processing and the I/O thread is running, Seconds_Behind_Master is 0.) (Bug #12946333)

Suena raro que se diferencie entre “stopped” y “not running”, pero tiene su explicación.

En MySQL, para dar privilegios a un cierto rango de IPs, si es un /24 podemos hacerlo con el siguiente comando:

mysql> GRANT ALL PRIVILEGES ON `systemadmin_blog`.* 
       TO 'systemadmin_blog'@'192.168.100.%'  
       IDENTIFIED BY 'systemadmin.es';
Query OK, 0 rows affected (0.00 sec)

Pero si lo que queremos es restringirlo a un rango más pequeño (o uno más grande que tampoco sea redondo) no nos sirve este método.

En el manual de MySQL podemos encontrar que también se puede hacer por IP y máscara de red:

mysql> GRANT ALL PRIVILEGES ON `systemadmin_blog`.* 
       TO 'systemadmin_blog'@'192.168.100.128/255.255.255.240'  
       IDENTIFIED BY

Para crecer en slaves de MySQL podemos apuntar el slave al master o bien utilizar el slave como master para el nuevo slave:

master  slave1  slave2

En el caso que usemos el slave anterior como master para el nuevo slave deberemos tener en cuenta varias cosas. A parte de las opciones de configuración como slave:

# Replicacion
server-id               = 3
relay-log               = mysqld-relay-bin
max-relay-log-size      = 500M
relay_log_purge         = 1

Deberemos añadir las que usamos para el master, los binlogs:

log-bin 			= /var/mysql-extra/binlog/mysql-bin
sync_binlog                     = 1
max-binlog-size                 = 500M
expire_logs_days                = 4
innodb_flush_log_at_trx_commit  = 1

Pero también deberemos indicar

En MySQL existe la directiva tmpdir que indicamos el directorio que debe usar para crear ficheros temporales. Aún así, existen otros ficheros temporales que no se crean el dicho directorio.

Si definimos la directiva tmpdir veremos que allí crea ficheros con los datos de queries que no le caben en memoria (HEAP) y hace la operación (por ejemplo un sort) en disco:

# grep tmp /etc/my.cnf
tmpdir				= /var/mysql/tmp
# ls /var/mysql/tmp
#sql_22c8_0.MYD  #sql_22c8_0.MYI  #sql_22c8_1.MYD  #sql_22c8_1.MYI

Pero en el datadir también podemos ver en alguna ocasión ficheros temporales que ignoran la directiva tmpdir. En el caso de MyISAM ficheros TMM:

-rw-rw---- 1 mysql mysql 7684096 Nov 16 08:56

Mediante snapshots LVM y mylvmbackup podemos realizar backups en caliente, pero en el caso que necesitemos mantener el snapshot sin realizar ninguna copia y a la vez conocer la posición del master para montar un slave de MySQL, deberemos parchear el script mylvmbackup.

En el caso que necesitemos crear un snapshot pero sin realizar ningún backup, en la sección misc del fichero de configuración deberemos definir las opciones backuptype y keep_snapshot:

(...)
[misc]
backuptype=none
(...)
keep_snapshot=1
(...)

El problema es que

Mediante el comando LOAD DATA podemos cargar datos desde un fichero a una tabla MySQL, y según la própia documentación de MySQL:

The LOAD DATA INFILE statement reads rows from a text file 
into a table at a very high speed.

El problema es que se nos quede la importación encallada sin razón aparente:

| 1 | datos | 1.1.1.1:26014 | lol | Killed | 9564 | NULL | LOAD DATA  LOCAL INFILE '/home/jordi/cosa1.data' REPLACE INTO TABLE tbl1
| 2 | datos | 1.1.1.1:22813 | lol | Killed | 3372 | NULL | LOAD DATA  LOCAL INFILE '/home/jordi/cosa2.data' REPLACE INTO TABLE tbl2

Si buscamos en el listado de bugs, podemos enconcrar el Bug #51840, con el título

En MySQL, podemos ver el estado de las tablas mediante el comando SHOW TABLE STATUS y en general, de un vistazo podemos ver las que están corruptas por si existe un campo con datos o no (la última columna llamada Comment):

Ejemplo SHOW TABLE STATUS

El problema surge cuando este es compartido con la opción COMMENT de la creación de tablas:

mysql> create table g(x int) engine=innodb comment="soy un comentario";
Query OK, 0 rows affected (0.77 sec)

Por lo que en el SHOW TABLE STATUS veremos los dos campos indistintamente llenos:

Haciendo unas pruebas con la tabla information_schema.tables y el campo table_comment me fijé que haciendo este SELECT no se indicaba que la tabla estaba corrupta:

mysql> select table_schema,table_name,table_comment,TABLE_COMMENT from information_schema.tables where table_name="a";
+--------------+------------+---------------+---------------+
| table_schema | table_name | table_comment | TABLE_COMMENT |
+--------------+------------+---------------+---------------+
| systemadmin  | a          |               |               |
+--------------+------------+---------------+---------------+
1 row in set (0.00 sec)

Añadí tanto con mayúsculas como minúsculas para ver si era ese el problema.

La única forma que encontré para ver el mensaje de

En MySQL tenemos cuatro variables para identificar la versión y arquitectura de un servidor MySQL. Vamos a ver cuales son:

Las variables las podemos ver todas juntas con SHOW VARIABLES:

mysql> show variables like 'ver%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| version                 | 5.5.22-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
4 rows in set (0.00 sec)

Su significado es:

• version: Versión del servidor con indicación de características. Si acaba con:

Este fin de semana, Oracle ha anunciado MySQL 5.6 Release Candidate

Podemos encontrar más información en un articulo que se ha publicado en la web de MySQL con las nuevas características de MySQL 5.6. En resumen, destacan las mejoras en el funcionamiento de master-slave:

• GTID (Global Transactions Identifiers): Identificador único por transacción que permite si seguimiento en una topologia compleja de master-slaves
• Nuevas herramientas para alta disponibilidad (de serie)
• Slaves multi-threads: Hasta ahora, los slaves aplicaban los cambios uno a uno (en serie), por lo que podía causar retrasos en la replicación. Ahora

En MySQL podemos habilitar un log de queries ejecutadas en el servidor llamado “general log“. Aunque no es muy recomendable tenerlo habilitado normalmente, puede resultar útil en momentos puntuales. Vamos a ver su funcionamiento:

Las variables que controlan el general log son las siguientes:

• general_log: Indicamos si queremos habilitar o no dicho log
• general_log_file: Indicamos el path del fichero dónde queremos el log

Mediante SHOW VARIABLES podemos verlos:

mysql> show variables like 'general_log%';
+------------------+--------------------------+
| Variable_name    | Value                    |
+------------------+--------------------------+
| general_log      | OFF

Cuando habilitamos un slave en read_only esto no quita que un usuario con privilegio SUPER pueda realizar modificaciones, por lo que puede ocurrir algún desastre que tengamos que recuperar manualmente mediante comandos SQL tanto en el master como en el slave.

Si nos interesa ejecutar algún conjunto de comandos en el master que no queremos que se ejecuten en el slave, podemos deshabilitar únicamente para la sesión actual los binary logs, sin afectar al resto de conexiones, mediante la variable sql_log_bin:

mysql> set sql_log_bin = 0;
Query OK, 0 rows affected (0.00 sec)

A partir de este comando cualquier sentencia que ejecutemos no quedará

Las tablas de MySQL debemos hacerles mantenimiento periódicamente ya que cuando eliminamos un dato, el espacio se queda marcado como libre pero no se libera. Mediante el comando OPTIMIZE podemos liberar dicho espacio recreando la tabla sin dicho espacio.

Tanto mediante SHOW TABLE STATUS:

mysql> show table status\G
(...)
*************************** 13. row ***************************
           Name: ejemplo_systemadmin.es
         Engine: MyISAM
        Version: 10
     Row_format: Fixed
           Rows: 467
 Avg_row_length: 25
    Data_length: 11675
Max_data_length: 7036874417766399
   Index_length: 62464
      Data_free: 54300
 Auto_increment: NULL
    Create_time: 2011-11-04

Al ejecutar comandos SQL en MySQL veremos que nos devuelve el resultado con una bonita tabla ASCII tanto en el modo interactivo:

mysql> select 1,2;
+---+---+
| 1 | 2 |
+---+---+
| 1 | 2 |
+---+---+
1 row in set (0.00 sec)

mysql> 

Como usando la opción -e para pasar el comando a ejecutar:

$ mysql -e "select count(1)"
+----------+
| count(1) |
+----------+
|        1 | 
+----------+

Vamos a ver las opciones que tenemos para poder modificar el tipo de salida por pantalla.

En el caso que no queramos que nos muestre el nombre de la columna por pantalla, tenemos la opción -N:

  -N, --skip-column-names 
                      Don't write column names in results.

En MySQL nos podemos encontrar con errores que no son muy explicativos pero que nos dan un código. Son similares a:

algo ... (errno: <CODIGO>)
algo ... (Errcode: <CODIGO>)

Mediante perror podemos descodificar dicho error:

Únicamente deberemos ejecutar el comando perror con el código para que nos devuelva una mejor descripción del error:

$ perror 31
OS error code  31:  Too many links
$ perror 32
OS error code  32:  Broken pipe

Dicho binario se encuentra con el resto de ficheros dónde tengamos instalado el MySQL:

# ls -la /usr/local/mysql/bin/perror 
-rwxr-xr-x 1 mysql mysql 197433 Feb 15  2010 /usr/local/mysql/bin/perror

En MySQL, el comando CREATE TABLE le podemos pasar otra tabla con un SELECT o con un LIKE para que cree una tabla con el resultado de la SELECT o copie la estructura de datos.

Los dos comandos son parecidos pero tenemos que tener en cuenta un detalle. Cuando hacemos un CREATE TABLE LIKE estamos diciendo que queremos copiar la estructura de tabla, por lo que incluye todas las definiciones:

mysql> show create table a;
+-------+-------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                          |

La herramienta mysqladmin permite realizar las operaciones más comunes que un administrador de este motor de base de datos necesita realizar con comandos más cortos. Vamos a ver unos ejemplos:

Si ejecutamos mysqladmin sin opciones veremos el conjunto de operaciones que podemos hacer:

# mysqladmin 
mysqladmin  Ver 8.42 Distrib 5.5.8, for Linux on x86_64
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
(...)
Where command is a one or more of: (Commands may be shortened)
  create databasename	Create a new database
  debug			Instruct server to write debug information to log
  drop databasename	Delete a database and all its tables
  extended-status       Gives an extended status message from the server
  flush-hosts           Flush all cached hosts
  flush-logs            Flush all

En tablas MyISAM nos podemos encontrar que al insertar un valor nos salga el siguiente error:

Duplicate entry '201830' for key 'PRIMARY'

En este caso, debería ser imposible que en una columna con una clave primaria exista una entrada duplicada. Se trata de una corrupción de datos debido posiblemente a algún bug o un cierre abrupto de la base de datos.

Para solucionar el problema deberemos usar el comando REPAIR TABLE:

REPAIR TABLE ejemplo;

Dichos errores no son especialmente comunes, pero lo único que podemos hacer es introducir un trozo de código en la aplicación que realice la operación en caso de encontrar el error para minimizar el problema.

Uno de los grandes problemas del log de slow queries de MySQL es la gran cantidad de ruido que se acaba introduciendo limitando mucho la utilidad de revisar dicho log, especialmente si se revisa cada mucho tiempo. Una opción para reducir dicho ruido es min_examined_row_limit

La variable min_examined_row_limit permite indicar un valor mínimo de filas examinadas para que una query (aunque tarde más que el tiempo que tengamos definido en long_query_time) sea incluida en el log o no. Dicho valor por defecto esta a 0.

mysql> show variables like 'min_examined_row_limit';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| min_examined_row_limit | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

Teniendo uno o varios MySQL slave, lo lógico es repartir las lecturas entre los varios servidores, por lo que necesitamos un SELECT que nos identifique el servidor con el fin de verificar que realmente estamos repartiendo las lecturas entre servidores.

A partir de la versión 5 de MySQL se introdujo la base de datos information_schema dónde podemos encontrar mediante tablas mucha información sobre el estado del MySQL.

Mediante la tabla global_variables, podemos obtener el hostname del servidor que esta ejecutando la query, por lo que identificaremos el servidor que estamos usando:

mysql> select * from information_schema.global_variables where variable_name="HOSTNAME";
+---------------+----------------------+
| VARIABLE_NAME |

Anteriormente vimos cómo hacer backups de un MySQL con una clave simétrica que luego ciframos con una clave pública. Vamos a ver cómo hacer la operación inversa para descifrar el backup y recuperar los datos.

Primero de todo deberemos recuperar la clave simétrica del fichero cifrado, para ello deberemos usar la clave privada con la opción rsautl -decrypt:

openssl rsautl -decrypt \
        -in backup_key_20120704.secret \
        -out backup_key_20120704.key \
        -inkey privkey.pem 

Este comando nos genera el fichero backup_key_20120704.key que contiene la clave simetrica descrifrada. A continuación deberemos descifrar el backup con dicha clave. Para ello deberemos

En el caso que intentemos cifrar unos datos directamente con una clave pública, obtendremos el siguiente mensaje de error:

RSA operation error
140654231455560:error:0406D06E:rsa routines:RSA_padding_add_PKCS1_type_2:data too large for key size:rsa_pk1.c:151:

Vamos a ver cómo cifrar unos backups con clave pública.

No podemos cifrar backups directamente con la clave pública, pero sí que podemos emular el intercambio de claves simétricas del protocolo TLS. Por lo tanto, para cifrar un backup podemos crear una clave aleatoria:

openssl rand 128 -out $BACKUP/backup_key_$(date +%Y%m%d)

Con dicha clave de 128 bytes ciframos los backups:

openssl enc -aes256 -in $BACKUP/backup_${db}_${taula}_$(date +%Y%m%d).sql.gz \
        -out