Copias de seguridad de MySQL sin phpMyAdmin

Como humildes desarrolladores web sabemos que la mayoría de los proyectos web que usan php corren en entornos LAMP utilizando MySQL como base de datos, aunque sabemos también que hay muchos otros sistemas como PostgreSQL, Oracle o incluso soluciones noSQL. Igualmente en muchos servidores de alojamiento y paquetes para desarrollo local (WAMP, XAMPP en Windows) se instala phpMyAdmin para gestionar de manera gráfica nuestras bases de datos MySQL, algo que ha provocado que muchos nos olvidemos de gestionar la base de datos mediante la línea de comandos, otros incluso jamás lo habrán intentado.

Hacer una copia de seguridad de las bases de datos es algo vital para evitar desastres, y necesario cuando tenemos que migrar desarrollos de un servidor a otro, ya sea desde nuestro entorno local hacia internet o cuando un proyecto cambia de hosting.

¿Y qué pasa cuando no disponemos de phpMyAdmin o queremos automatizar el proceso? Es entonces cuando esta micro-guía resulta útil.

Motores de MySQL

En primer lugar es recomendable saber que MySQL dispone de varios motores de almacenamiento, los más conocidos son MyISAM, InnoDB y HEAP. Lo más común es dejar MyISAM, tal y como viene configurado por defecto.

Conviene saber que cuando queramos aplicar restricciones de claves foráneas a nivel de base de datos y no de aplicación, debemos usar InnoDB en lugar de MyISAM porque éste no las soporta, al igual que las transacciones.

MySQL en nuestro disco

Es importante también conocer cómo almacena físicamente MySQL nuestros datos. En entornos unix MySQL se instala normalmente en

/var/lib/mysql/

aunque podemos preguntarle por el directorio de datos directamente al intérprete con

SHOW VARIABLES LIKE 'datadir';

En esta ruta encontraremos una carpeta para cada base de datos que hayamos creado,  y para cada tabla hay tres archivos:  nombre_tabla.frm (formato), nombre_tabla.myd (datos) y nombre_tabla.myi (índices).

Dicho esto, toca hacer las copias de seguridad. Hay muchas formas, aquí vamos a comentar solo dos. Copiando los archivos directamente o mediante la herramienta mysqldump.

Copias de seguridad directas (a-pelo-method)

Si queremos usar el método de copia directa, primero debemos parar MySQL para desbloquear los archivos, usando (en Ubuntu)

sudo /etc/init.d/mysql stop

o desde dentro del intérprete

mysqladmin -u usuario -p shutdown;

Hecho esto, podemos copiar los ficheros de muchas formas, mostramos tres:

1) Mandándolo todo en un zip al escritorio

sudo zip -r /home/usuario/Desktop/mysql-backup.zip /var/lib/mysql/

2) Usando rsync (útil para lanzar desde un script):

sudo rsync -r /var/lib/mysql/ /tmp/mysql-backup

3) Copiando simplemente los archivos a otro directorio:

sudo cp -r /var/lib/mysql /tmp/mysql-backup

Una vez completada la copia, hay que asegurarse de que el propietario de los archivos sigue siendo el usuario mysql

sudo chown -R mysql.mysql /var/lib/mysql/

y ya podemos reiniciar el servidor MySQL

sudo chown -R mysql.mysql /var/lib/mysql/

Exportando e importando con mysqldump

Esta herramienta, a diferencia del método anterior, genera archivos que no contienen los datos directamente sino las instrucciones SQL que replican la estructura y contenido de nuestras bases de datos.

1) Si queremos hacer una copia de seguridad completa, usaremos

mysqldump -u urusario -p --all-databases > backup.sql

Esto nos generará un archivo backup.sql en la carpeta de mysqldump.

2) Exportar sólo una base de datos

mysqldump -u usuario -p base_de_datos > base_de_datos.sql

3) Exportar sólo algunas tablas

mysqldump -u usuario -p base_de_datos tabla1 tabla2 > tabla1_y_tabla2.sql

4)Restaurando la copia

mysqldump -u usuario -p base_de_datos tabla1 tabla2 > tabla1_y_tabla2.sql

Más información

Podemos encontrar esta información y mucha más en la guía de administración de MySQL escrita por Alejandro Castán.