Los dos métodos principales para hacer una copia de seguridad de la base de datos son usar el programa mysqldump o copiar directamente el archivo de la base de datos (como usar cp, cpio o tar, etc.). Este artículo detalla el plan de copia de seguridad de la base de datos de la plataforma MySQL. En caso de que una tabla de base de datos se pierda o se dañe, es importante hacer una copia de seguridad de su base de datos. Si ocurre una falla del sistema, definitivamente querrás poder restaurar tus tablas al estado en el que estaban cuando ocurrió la falla con la menor cantidad de pérdida de datos posible. A veces, es el administrador de MySQL quien causa estragos. El administrador ya sabe que las tablas están corruptas y tratar de editarlas directamente usando un editor como vi o Emacs definitivamente no es algo bueno para las tablas.
Los dos métodos principales para hacer una copia de seguridad de la base de datos son usar el programa mysqldump o copiar directamente el archivo de la base de datos (como usar cp, cpio o tar, etc.). Cada método tiene sus pros y sus contras:
mysqldump funciona con el servidor MySQL. El método de copia directa se realiza fuera del servidor y debe tomar medidas para asegurarse de que ningún cliente modifique la tabla que está copiando. Si desea utilizar la copia de seguridad del sistema de archivos para hacer una copia de seguridad de la base de datos, ocurrirá el mismo problema: si la tabla de la base de datos se modifica durante el proceso de copia de seguridad del sistema de archivos, el asunto del archivo de la tabla respaldada será inconsistente y la tabla será sin sentido para la futura recuperación. La diferencia entre una copia de seguridad del sistema de archivos y una copia directa del archivo es que con esta última usted tiene control total sobre el proceso de copia de seguridad, por lo que puede tomar medidas para asegurarse de que el servidor deje la mesa sin ser molestado.
mysqldump es más lento que la copia directa.
mysqldump genera archivos de texto que son portátiles a otras máquinas, incluso aquellas con diferentes arquitecturas de hardware. Los archivos que se copian directamente no se pueden transferir a otras máquinas a menos que la tabla que está copiando utilice el formato de almacenamiento MyISAM. Las tablas ISAM sólo se pueden copiar en máquinas con estructuras de hardware similares. El formato de almacenamiento de tablas MyISAM introducido en MySQL 3.23 resuelve este problema porque el formato es independiente de la máquina, por lo que la copia directa del archivo se puede trasplantar a máquinas con diferentes estructuras de hardware. Siempre que se cumplan dos condiciones: la otra máquina también debe ejecutar MySQL 3.23 o posterior, y el archivo debe estar representado en formato MyISAM, no en formato ISAM.
Independientemente del método de copia de seguridad que utilice, si necesita restaurar su base de datos, existen varios principios que deben seguirse para garantizar los mejores resultados:
Implemente copias de seguridad periódicas. Establezca un plan y cúmplalo.
Deje que el servidor realice el registro de actualizaciones. El registro de cambios le ayudará cuando necesite recuperar datos después de un fallo. Después de usar el archivo de copia de seguridad para restaurar los datos al estado en el que se encontraban en el momento de la copia de seguridad, puede volver a aplicar los cambios realizados después de la copia de seguridad ejecutando una consulta en el registro de actualización, que restaurará las tablas en la base de datos a el estado en el que se encontraban cuando ocurrió el accidente.
En términos de copia de seguridad del sistema de archivos, el archivo de copia de seguridad de la base de datos representa un volcado completo, mientras que el registro de actualización representa un volcado incremental.
Utilice un esquema de nombres consistente y comprensible para los archivos de respaldo. Cosas como backup1, buckup2, etc. no son particularmente significativas. Al realizar la recuperación, perderá tiempo averiguando qué hay en los archivos. Puede resultarle útil utilizar el nombre de la base de datos y la fecha para formar el nombre del archivo de copia de seguridad. Por ejemplo:
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02
%mysqldump colección de animales >/usr/archives/mysql/menagerie.1999-10-02
Es posible que desees comprimir las copias de seguridad después de generarlas. ¡Las copias de seguridad tienden a ser grandes! También necesita que sus archivos de respaldo caduquen para evitar que llenen su disco, al igual que caducan sus archivos de registro.
Haga una copia de seguridad de sus archivos de respaldo con una copia de seguridad del sistema de archivos. Si experimenta un bloqueo total que no sólo borra su directorio de datos, sino también la unidad de disco que contiene las copias de seguridad de su base de datos, estará en verdaderos problemas.
También haga una copia de seguridad de su registro de cambios.
Coloque sus archivos de respaldo en un sistema de archivos diferente al utilizado para su base de datos. Esto reducirá la posibilidad de llenar el sistema de archivos que contiene el directorio de datos como resultado de generar la copia de seguridad.
Las técnicas utilizadas para crear copias de seguridad también son útiles para copiar una base de datos a otra máquina. Lo más común es que una base de datos se mueva a un servidor que se ejecuta en otro host, pero también se pueden mover los datos a otro servidor en el mismo host.
1 Utilice mysqldump para realizar una copia de seguridad y copiar la base de datos
Cuando utiliza el programa mysqldumo para generar un archivo de copia de seguridad de la base de datos, de forma predeterminada, el contenido del archivo contiene la instrucción CREATE que crea la tabla que se está volcando y la instrucción INSERT que contiene los datos de la fila en la tabla. En otras palabras, la salida producida por mysqldump se puede utilizar posteriormente como entrada a mysql para reconstruir la base de datos.
Puede volcar toda la base de datos en un único archivo de texto de la siguiente manera:
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02
El comienzo del archivo de salida se ve así:
# MySQL Dump 6.0# # Host: localhost Base de datos: samp_db
#---------------------------------#
Versión del servidor 3.23.2-alpha-log## Estructura de tabla para ausencia de tabla
#CREAR TABLA ausencia( Student_id int(10) unsigned DEFAULT 0 NOT NULL, fecha fecha DEFAULT 0000-00-00 NOT NUL L,
PRIMARY KEY (student_id,date));## Volcado de datos para la ausencia de la tabla #INSERT INTO ausencia VALUES (3,1999-09-03);INSERT INTO ausencia VALUE S (5,1999-09-03);INSERT INTO ausencia VALUES (10,1999-09-08);......
El resto del archivo consta de más declaraciones INSERT y CREATE TABLE. Si desea comprimir la copia de seguridad, utilice un comando similar al siguiente:
%mysqldump samp_db | gzip >/usr/archives/mysql/samp_db.1999-10-02.gz
Si tiene una base de datos grande, los archivos de salida también serán grandes y pueden resultar difíciles de administrar. Si lo desea, puede enumerar los nombres de las tablas individuales después del nombre de la base de datos en la línea de comando mysqldump para volcar su contenido, lo que dividirá el archivo de volcado en archivos más pequeños y manejables. El siguiente ejemplo muestra cómo volcar algunas tablas de la base de datos samp_db en archivos separados:
%mysqldump samp_db ausencia del evento de puntuación del estudiante >grapbook.sql
%mysqldump presidente miembro de samp_db >hist-league.sql
Si está generando archivos de respaldo destinados a actualizar periódicamente el contenido de otra base de datos, es posible que desee utilizar la opción --add-drop-table. Esto le indica al servidor que escriba la instrucción DROP TABLE IF EXISTS en el archivo de respaldo y luego, cuando tome el archivo de respaldo y lo cargue en la segunda base de datos, no obtendrá un error si la tabla ya existe.
Si volca una base de datos para poder moverla a otro servidor, ni siquiera tendrá que crear un archivo de respaldo. Asegúrese de que la base de datos exista en otro host y luego use una tubería para volcar la base de datos para que mysql pueda leer directamente la salida de mysqldump. Por ejemplo: desea copiar la base de datos samp_db del host pit-viper.snake.net a boa.snake.net. Puede hacerlo fácilmente de esta manera:
%mysqladmin -h boa.snake.net crea samp_db
%mysqldump samp_db | mysql -h boa.snake.net samp_db
En el futuro, si desea actualizar la base de datos en boa.snake.net nuevamente, omita el comando mysqladmin, pero agregue --add-drop-table a mysqldump para evitar que aparezca el error de que la tabla ya existe: %mysqldump --add- tabla desplegable samp_db | mysql -h boa.snake.net samp_db
Otras opciones útiles de mysqldump incluyen: La combinación --flush-logs y --lock-tables será útil para controlar su base de datos. --lock-tables bloquea todas las tablas que está volcando y --flush-logs cierra y vuelve a abrir el archivo de registro de actualización. El nuevo registro de actualización solo incluirá consultas que modificaron la base de datos desde el punto de respaldo. Esto establecerá el tiempo de copia de seguridad del punto de control del registro de actualización. (Sin embargo, si tiene clientes que necesitan realizar una actualización, bloquear todas las tablas no es una buena idea para el acceso de los clientes durante la copia de seguridad).
Si usa --flush-logs para realizar un punto de control en una copia de seguridad, probablemente sea mejor volcar toda la base de datos.
Si volca archivos separados, es más difícil sincronizar los puntos de control del registro de actualización con los archivos de respaldo. Durante la recuperación, normalmente extrae el contenido del registro de actualizaciones por base de datos. No hay ninguna opción para extraer actualizaciones para tablas individuales, por lo que debe extraerlas usted mismo.
De forma predeterminada, mysqldump lee todo el contenido de una tabla en la memoria antes de escribir. Esto suele ser realmente innecesario y, de hecho, es casi un fracaso si tienes una mesa grande. Puede usar la opción --quick para indicarle a mysqldump que escriba cada fila cada vez que recupere una. Para optimizar aún más el proceso de vertido, utilice --opt en lugar de --quick. La opción --opt activa opciones adicionales para acelerar el volcado de datos y su lectura.
Implementar copias de seguridad con --opt es probablemente el método más común debido a la ventaja de velocidad de las copias de seguridad. Sin embargo, tenga en cuenta que la opción --opt tiene un costo --opt optimiza su proceso de respaldo, no el acceso de otros clientes a la base de datos. La opción --opt evita que alguien actualice cualquier tabla que esté volcando al bloquear todas las tablas a la vez. Puede ver fácilmente el efecto en el acceso general a la base de datos. Cuando su base de datos generalmente se usa con mucha frecuencia, simplemente ajuste la copia de seguridad una vez al día.
Una opción que tiene el efecto opuesto a --opt es --dedayed. Esta opción hace que mysqldump escriba declaraciones INSERT DELAYED en lugar de declaraciones INSERT. --delayed es útil si está cargando un archivo de datos en otra base de datos y desea que esta operación tenga un impacto mínimo en las consultas que puedan aparecer en esa base de datos.
La opción --compress es útil cuando copia la base de datos a otra máquina porque reduce la cantidad de bytes transferidos a través de la red. Aquí hay un ejemplo. Tenga en cuenta que --compress se proporciona para programas que se comunican con un servidor en un host remoto, no para programas que se conectan al host local:
%mysqldump --opt samp_db | mysql --compress -h boa.snake.net samp_db
mysqldump tiene muchas opciones; consulte el "Manual de referencia de MySQL" para obtener más detalles.
2 Método de copia de seguridad y copia utilizando la base de datos de copia directa
Otra forma de hacer una copia de seguridad de la base de datos y las tablas que no involucra mysqldump es copiar los archivos de la tabla de la base de datos directamente. Normalmente, esto se hace mediante utilidades como cp, tar o cpio. Los ejemplos de este artículo utilizan cp.
Cuando utiliza un método de copia de seguridad directa, debe asegurarse de que la tabla ya no esté en uso. Si el servidor cambia una tabla mientras la copia, la copia no tiene sentido.
La mejor manera de garantizar la integridad de su copia es apagar el servidor, copiar los archivos y luego reiniciar el servidor. Si no desea apagar el servidor, bloquéelo mientras realiza la verificación de la tabla. Si el servidor está en ejecución, se aplican las mismas restricciones para copiar archivos y debe usar el mismo protocolo de bloqueo para "silenciar" el servidor.
Suponiendo que el servidor está inactivo o que ha bloqueado la tabla que desea copiar, a continuación se muestra cómo hacer una copia de seguridad de toda la base de datos samp_db en un directorio de respaldo (DATADIR representa el directorio de datos del servidor): %cd DATADIR%cp -r samp_db /usr /archivo/mysql
Se puede realizar una copia de seguridad de una sola tabla de la siguiente manera:
%cd DATADIR/samp_db%cp miembro.* /usr/archive/mysql/samp_db%cp puntuación.* /usr/archive/mysql/samp_db ....
Cuando haya completado la copia de seguridad, puede reiniciar el servidor (si lo apagó) o liberar los bloqueos colocados en la mesa (si dejó el servidor en ejecución).
Para copiar una base de datos de una máquina a otra usando archivos de copia directa, simplemente copie los archivos al directorio de datos apropiado en el otro servidor host. Asegúrese de que el archivo esté en formato MyIASM o que ambas máquinas tengan la misma estructura de hardware; de lo contrario, su base de datos tendrá contenidos extraños en la otra máquina. También debe asegurarse de que el servidor de otra máquina no acceda a las tablas de la base de datos mientras las instala.
3 Base de datos replicante
La replicación es similar a copiar una base de datos a otro servidor, pero su significado exacto es garantizar que las dos bases de datos estén completamente sincronizadas en tiempo real. Esta característica aparecerá en la versión 3.23 y aún no está muy madura, por lo que este artículo no la presentará en detalle.
4 Restaurar datos desde la copia de seguridad
La corrupción de la base de datos puede ocurrir por muchas razones y en diversos grados. Si tiene suerte, es posible que solo dañe una o dos tablas (como un corte de energía); si no tiene suerte, es posible que deba reemplazar todo el directorio de datos (como un disco dañado). La recuperación también es necesaria en determinadas situaciones, como cuando un usuario elimina una base de datos o una tabla por error. Independientemente de la causa de estos desafortunados acontecimientos, será necesario implementar algún tipo de recuperación.
Si las tablas están dañadas pero no se pierden, intente repararlas con myisamchk o isamchk. Si dicho daño puede repararse con un programa de reparación, es posible que no necesite utilizar el archivo de copia de seguridad. Para conocer el proceso de reparación de la tabla, consulte "Mantenimiento y reparación de bases de datos".
El proceso de recuperación implica dos fuentes de información: sus archivos de respaldo y sus registros de cambios. El archivo de copia de seguridad restaura la tabla al estado en que se encontraba cuando se realizó la copia de seguridad. Sin embargo, normalmente la tabla se modificó en el tiempo transcurrido entre la copia de seguridad y el problema, y el registro de actualización contiene las consultas utilizadas para realizar estas modificaciones. Puede utilizar archivos de registro como entrada a mysql para repetir consultas. Es por eso que debe habilitar el registro de cambios.
El proceso de recuperación varía según la cantidad de información que tenga que recuperar. De hecho, es más fácil restaurar toda la base de datos que una sola tabla porque es más fácil aplicar el registro de actualización a la base de datos que a una sola tabla.
4.1 Restaurar toda la base de datos
Primero, si la base de datos que desea restaurar es una base de datos mysql que contiene una tabla de concesión, debe ejecutar el servidor con la opción --skip-grant-table. De lo contrario, se quejará de que no se puede encontrar la tabla de autorización. Después de restaurar la tabla, ejecute mysqladmin Flush-privileges para indicarle al servidor que cargue los tokens de autorización y los use.
Copie el contenido del directorio de la base de datos en otra ubicación si los necesita más adelante.
Reinstale la base de datos con el archivo de copia de seguridad más reciente. Si usa el archivo generado por mysqldump, úselo como entrada para mysql. Si está utilizando archivos copiados directamente de la base de datos, cópielos directamente al directorio de la base de datos. Sin embargo, en este caso deberá cerrar la base de datos y luego reiniciarla antes de copiar los archivos.
Utilice el registro de actualización para repetir consultas que modifiquen las tablas de la base de datos después de la copia de seguridad. Para cualquier registro de cambios aplicable, páselo como entrada a mysql. Especificar la opción --one-database hace que mysql ejecute consultas solo para la base de datos que está interesado en restaurar. Si sabe que necesita aplicar todos los archivos de registro de actualización, puede usar este comando en el directorio que contiene los registros:
% ls -t -r -1 actualización.[0-9]* | xargs cat | mysql --one-database db_name
El comando ls genera una lista de una sola columna de archivos de registro de actualizaciones, ordenados según el orden en que los generó el servidor (Idea: si modifica cualquiera de los archivos, cambiará el orden de clasificación, lo que hace que el registro de actualizaciones sea usado en el orden incorrecto.)
Lo más probable es que utilice ciertos registros de cambios. Por ejemplo, si los registros de actualización generados desde su copia de seguridad se denominan update.392, update.393, etc., puede volver a ejecutarlos de esta manera:
%mysql --one-database nombre_bd <actualización.392
%mysql --one-database nombre_bd <actualización.393
.....
Si está realizando una recuperación y está utilizando el registro de actualización para recuperar información perdida debido a una instrucción DROP DATABASE, DROP TABLE o DELETE recomendada incorrectamente, asegúrese de eliminar estas declaraciones del registro de actualización antes de usarlo.
4.2 Restaurar una sola tabla
Restaurar una sola tabla es más complejo. Si utiliza un archivo de respaldo generado por mysqldump y no contiene datos para las tablas que le interesan, deberá extraerlos de las filas relevantes y usarlos como entrada para mysql. Esta es la parte fácil. La parte difícil es extraer el fragmento del registro de actualización que solo se aplica a esa tabla. Puede que le resulte útil la utilidad mysql_find_rows para esto, que extrae consultas de varias filas del registro de cambios.
Otra posibilidad es utilizar otro servidor para restaurar toda la base de datos y luego copiar los archivos de tabla que desee a la base de datos original. ¡Esto podría ser realmente fácil! Cuando copie los archivos nuevamente al directorio de la base de datos, asegúrese de que el servidor de la base de datos original esté apagado.