-
Copia de seguridad y recuperación de MySQL Sábado, 30/09/2006 - 14:21 — yejr
Copia de seguridad y recuperación de MySQL
Autor/Traductor: Ye Jinrong (Correo electrónico:), Fuente: http://imysql.cn . Indique el autor/traductor y la fuente al reimprimir. No se puede utilizar con fines comerciales. Los infractores serán procesados.
Fecha: 2006/10/01
Este artículo analiza el mecanismo de copia de seguridad y recuperación de MySQL y cómo mantener las tablas de datos, incluidos los dos tipos de tablas principales: MyISAM e Innodb. La versión de MySQL diseñada en este artículo es 5.0.22.
Las herramientas de copia de seguridad gratuitas actualmente admitidas por MySQL incluyen: mysqldump, mysqlhotcopy. También puede utilizar la sintaxis SQL para realizar copias de seguridad: BACKUP TABLE o SELECT INTO OUTFILE, o hacer copias de seguridad de registros binarios (binlog), o copiar directamente archivos de datos y archivos de configuración relacionados. Las tablas MyISAM se guardan como archivos, por lo que es relativamente fácil realizar copias de seguridad de ellas. Se pueden utilizar varios de los métodos mencionados anteriormente. Todas las tablas en Innodb se almacenan en el mismo archivo de datos ibdata1 (también pueden ser varios archivos o archivos de espacio de tabla independientes), lo cual es relativamente difícil de realizar una copia de seguridad. Las soluciones gratuitas pueden ser copiar archivos de datos y hacer una copia de seguridad de binlog. .
1.mysqldump
1.1 Copia de seguridad
mysqldump utiliza un mecanismo de copia de seguridad a nivel de SQL. Exporta tablas de datos a archivos de script SQL. Es relativamente adecuado para actualizar entre diferentes versiones de MySQL. Este es también el método de copia de seguridad más utilizado.
Ahora hablemos de algunos de los parámetros principales de mysqldump:
--compatible=nombre
Le dice a mysqldump con qué base de datos o versión anterior del servidor MySQL serán compatibles los datos exportados. Los valores pueden ser ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options, etc. Para utilizar varios valores, sepárelos con comas. Por supuesto, no garantiza una compatibilidad completa, pero sí intenta ser compatible.
--inserción-completa, -c
Los datos exportados utilizan el método INSERT completo incluyendo los nombres de los campos, es decir, todos los valores se escriben en una línea. Hacerlo puede mejorar la eficiencia de la inserción, pero puede verse afectado por el parámetro max_allowed_packet y provocar un error de inserción. Por lo tanto, este parámetro debe usarse con precaución, al menos no lo recomiendo.
--default-character-set=juego de caracteres
Especifique qué juego de caracteres usar al exportar datos. Si la tabla de datos no usa el juego de caracteres latin1 predeterminado, entonces se debe especificar esta opción al exportar; de lo contrario, se producirán caracteres confusos después de importar los datos nuevamente.
--disable-keys
Dígale a mysqldump que agregue declaraciones /*!40000 ALTER TABLE table DISABLE KEYS */; y /*!40000 ALTER TABLE table ENABLE KEYS */ al principio y al final de la declaración INSERT. porque es El índice se reconstruye después de que se hayan insertado todos los datos. Esta opción sólo es adecuada para tablas MyISAM.
--extended-insert = verdadero|falso
De forma predeterminada, mysqldump activa el modo --complete-insert, por lo que si no desea usarlo, simplemente use esta opción y establezca su valor en falso.
--blob-hexadecimal
Exporte campos de cadenas binarias usando formato hexadecimal. Esta opción debe usarse si hay datos binarios. Los tipos de campos afectados son BINARIO, VARBINARIO y BLOB.
--bloquear-todas-las-tablas,-x
Antes de comenzar la exportación, envíe una solicitud para bloquear todas las tablas en todas las bases de datos para garantizar la coherencia de los datos. Este es un bloqueo de lectura global y se desactiva automáticamente con las opciones --single-transaction y --lock-tables.
--lock-mesas
Es similar a --lock-all-tables, pero bloquea la tabla de datos actualmente exportada en lugar de bloquear todas las tablas de la base de datos a la vez. Esta opción solo es aplicable a tablas MyISAM. Si es una tabla Innodb, puede usar la opción --single-transaction.
--no-crear-información, -t
Exporte solo los datos sin agregar una declaración CREATE TABLE.
--sin-datos,-d
No se exporta ningún dato, solo se exporta la estructura de la tabla de la base de datos.
--optar
Esta es solo una opción rápida, equivalente a agregar también --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set- opciones de juego de caracteres. Esta opción permite a mysqldump exportar datos rápidamente y los datos exportados se pueden volver a importar rápidamente. Esta opción está habilitada de forma predeterminada, pero se puede deshabilitar con --skip-opt. Tenga en cuenta que si ejecuta mysqldump sin especificar la opción --quick o --opt, todo el conjunto de resultados se colocará en la memoria. Pueden surgir problemas si exporta una base de datos grande.
--rápido,-q
Esta opción es útil al exportar tablas grandes. Obliga a mysqldump a generar los registros obtenidos de la consulta del servidor directamente en lugar de recuperar todos los registros y almacenarlos en la memoria caché.
--rutinas, -R
Exporte procedimientos almacenados y funciones personalizadas.
--transacción única
Esta opción envía una declaración BEGIN SQL antes de exportar datos. BEGIN no bloquea ninguna aplicación y garantiza un estado consistente de la base de datos durante la exportación. Sólo funciona con tablas transaccionales como InnoDB y BDB.
Esta opción y la opción --lock-tables son mutuamente excluyentes porque LOCK TABLES hace que cualquier transacción pendiente se confirme implícitamente.
Para exportar tablas grandes, se debe utilizar la opción --quick en combinación.
--desencadenantes
También exporta desencadenantes. Esta opción está habilitada de forma predeterminada, use --skip-triggers para deshabilitarla.
Consulte el manual para obtener detalles sobre otros parámetros. Normalmente uso el siguiente SQL para realizar copias de seguridad de las tablas MyISAM:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob -x nombre_bd > nombre_bd.sql
Utilice el siguiente SQL para realizar una copia de seguridad de las tablas Innodb:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --extended-insert=false
--triggers -R --hex-blob --transacción única nombre_bd > nombre_bd.sql
Además, si desea implementar una copia de seguridad en línea, también puede utilizar el parámetro --master-data, de la siguiente manera:
/usr/local/mysql/bin/mysqldump -uyejr -pyejr
--default-character-set=utf8 --opt --master-data=1
--transacción única --flush-logs nombre_bd > nombre_bd.sql
Solo solicita la tabla de bloqueo al principio, luego actualiza el binlog y luego agrega la instrucción CHANGE MASTER al archivo exportado para especificar la ubicación del binlog de la copia de seguridad actual. Si desea restaurar este archivo en el esclavo, puede usarlo. esta manera de hacerlo.
1.2 Restaurar El archivo respaldado con mysqldump es un script SQL que se puede importar directamente. Hay dos formas de importar los datos.
Utilice el cliente mysql directamente, por ejemplo:
/usr/local/mysql/bin/mysql -uyejr -pyejr nombre_bd < nombre_bd.sql
El uso de la sintaxis SOURCE en realidad no es una sintaxis SQL estándar, sino una función proporcionada por el cliente mysql, por ejemplo:
FUENTE /tmp/db_name.sql;
Aquí debe especificar la ruta absoluta del archivo, y debe ser un archivo que el usuario que ejecuta mysqld (por ejemplo, nadie) tenga permiso para leer.
2. mysqlhotcopy
2.1 Copia de seguridad
mysqlhotcopy es un programa PERL escrito originalmente por Tim Bunce. Utiliza LOCK TABLES, FLUSH TABLES y cp o scp para realizar una copia de seguridad rápida de la base de datos. Es la forma más rápida de realizar una copia de seguridad de una base de datos o de una sola tabla, pero solo puede ejecutarse en la máquina donde se encuentran los archivos de la base de datos (incluidos los archivos de definición de tablas de datos, los archivos de datos y los archivos de índice). mysqlhotcopy sólo se puede utilizar para realizar copias de seguridad de MyISAM y sólo se ejecutará en sistemas tipo Unix y NetWare.
mysqlhotcopy admite la copia de varias bases de datos a la vez y también admite expresiones regulares. A continuación se muestran algunos ejemplos:
raíz#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
db_name /tmp (cambiar el directorio de la base de datos db_name
Copiar a /tmp
Abajo)
raíz#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
nombre_bd_1 ... nombre_bd_n /tmp
raíz#/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=yejr -p=yejr
nombre_bd./regex/ /tmp
Consulte el manual para obtener un uso más detallado o llame al siguiente comando para ver la ayuda de mysqlhotcopy:
perldoc /usr/local/mysql/bin/mysqlhotcopy
Tenga en cuenta que si desea utilizar mysqlhotcopy, debe tener permisos SELECT y RELOAD (para ejecutar FLUSH TABLES), y también debe tener permiso para leer el directorio datadir/db_name.
2.2 Restaurar
Mysqlhotcopy realiza una copia de seguridad de todo el directorio de la base de datos. Cuando se usa, se puede copiar directamente al directorio de datos especificado por mysqld (aquí está /usr/local/mysql/data/). Al mismo tiempo, se debe prestar atención a los problemas de permisos. como en el siguiente ejemplo:
raíz#cp -rf nombre_bd /usr/local/mysql/data/
root#chown -R nadie:nadie /usr/local/mysql/data/ (cambie el propietario del directorio db_name a mysqld
usuario en ejecución)
3. Copia de seguridad de sintaxis SQL
3.1 Copia de seguridad
La sintaxis de BACKUP TABLE es en realidad similar al principio de funcionamiento de mysqlhotcopy. Ambos bloquean la tabla y luego copian el archivo de datos. Puede lograr una copia de seguridad en línea, pero el efecto no es ideal, por lo que no se recomienda. Solo copia archivos de estructura de tablas y archivos de datos, pero no copia archivos de índice al mismo tiempo, por lo que la recuperación es más lenta.
ejemplo:
VOLVER TABLA tbl_name TO '/tmp/db_name/';
Tenga en cuenta que debe tener permiso de ARCHIVO para ejecutar este SQL y que el usuario mysqld debe poder escribir en el directorio /tmp/db_name. El archivo exportado no puede sobrescribir el archivo existente para evitar problemas de seguridad.
SELECT INTO OUTFILE exporta los datos a un archivo de texto normal. Puede personalizar el intervalo de campo para facilitar el procesamiento de estos datos.
ejemplo:
SELECCIONE * EN OUTFILE '/tmp/db_name/tbl_name.txt' FROM tbl_name;
Tenga en cuenta que debe tener permiso de ARCHIVO para ejecutar este SQL y que el usuario mysqld debe poder escribir en el archivo /tmp/db_name/tbl_name.txt. El archivo exportado no puede sobrescribir el archivo existente para evitar problemas de seguridad.
3.2 Para restaurar archivos respaldados usando el método BACKUP TABLE, puede ejecutar la instrucción RESTORE TABLE para restaurar la tabla de datos.
ejemplo:
RESTAURAR TABLA DESDE '/tmp/db_name/';
Los requisitos de permiso son similares a los descritos anteriormente.
Para los archivos respaldados mediante el método SELECT INTO OUTFILE, puede ejecutar la instrucción LOAD DATA INFILE para restaurar la tabla de datos.
ejemplo:
CARGAR ARCHIVO DE DATOS '/tmp/db_name/tbl_name.txt' EN LA TABLA tbl_name;
Los requisitos de permiso son similares a los descritos anteriormente. Antes de importar datos, la tabla de datos ya debe existir. Si le preocupa la duplicación de datos, puede agregar la palabra clave REPLACE para reemplazar los registros existentes o usar la palabra clave IGNORE para ignorarlos.
4. Habilite el registro binario (binlog)
El método de uso de binlog es relativamente más flexible, ahorra preocupaciones y esfuerzos y también puede admitir copias de seguridad incrementales.
Mysqld debe reiniciarse cuando binlog está habilitado. Primero, cierre mysqld, abra my.cnf y agregue las siguientes líneas:
ID del servidor = 1
log-bin = binlog
log-bin-index = binlog.index
Luego inicie mysqld. Binlog.000001 y binlog.index se generarán durante la operación. El primer archivo es mysqld que registra todas las operaciones de actualización de datos, y el último archivo es el índice de todos los binlogs, que no se pueden eliminar fácilmente. Consulte el manual para obtener información sobre binlog.
Cuando necesite realizar una copia de seguridad, primero puede ejecutar la instrucción SQL para permitir que mysqld finalice la escritura en el binlog actual y luego hacer una copia de seguridad del archivo directamente. De esta manera, se puede lograr el propósito de la copia de seguridad incremental:
FLUSH LOGS; si está haciendo una copia de seguridad del servidor esclavo en el sistema de replicación, también debe hacer una copia de seguridad de los archivos master.info y Relay-log.info.
El archivo binlog respaldado se puede ver usando la herramienta mysqlbinlog proporcionada por MySQL, como por ejemplo:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001
Esta herramienta le permite mostrar todas las declaraciones SQL en una base de datos específica y también puede limitar el rango de tiempo, lo cual es bastante conveniente. Consulte el manual para obtener más detalles.
Al restaurar, puede utilizar declaraciones similares a las siguientes:
/usr/local/mysql/bin/mysqlbinlog /tmp/binlog.000001 mysql -uyejr -pyejr nombre_bd |
Utilice las declaraciones SQL generadas por mysqlbinlog directamente como entrada para ejecutarlo.
Si tiene una máquina inactiva, también puede utilizar este método para hacer una copia de seguridad. Dado que los requisitos de rendimiento de las máquinas esclavas son relativamente bajos, el costo es bajo. Se puede lograr una copia de seguridad incremental a bajo costo y se puede compartir parte de la presión de la consulta de datos.
5. Copia de seguridad directa de archivos de datos En comparación con los métodos anteriores, la copia de seguridad de archivos de datos es la más directa, rápida y conveniente. La desventaja es que la copia de seguridad incremental es básicamente imposible. Para garantizar la coherencia de los datos, se debe ejecutar la siguiente instrucción SQL antes de realizar una copia de seguridad del archivo:
FLUSH TABLES CON READ LOCK; es decir, vaciar todos los datos de la memoria en el disco y bloquear la tabla de datos para garantizar que no se escriban datos nuevos durante el proceso de copia. Los datos respaldados por este método también son muy simples de restaurar, simplemente cópielos nuevamente al directorio de la base de datos original.
Tenga en cuenta que para las tablas de tipo Innodb, también necesita hacer una copia de seguridad de sus archivos de registro, es decir, los archivos ib_logfile*. Porque cuando la tabla Innodb está dañada, puede confiar en estos archivos de registro para recuperarse.
6. Estrategia de respaldo Para sistemas con volumen de negocios de nivel medio, la estrategia de respaldo se puede determinar de la siguiente manera: respaldo completo por primera vez, respaldo incremental una vez al día, respaldo completo una vez a la semana, etc. Para sistemas importantes y ocupados, es posible que necesite una copia de seguridad completa una vez al día, una copia de seguridad incremental una vez cada hora o incluso con mayor frecuencia. Para lograr copias de seguridad en línea y copias de seguridad incrementales sin afectar el negocio en línea, la mejor manera es utilizar el mecanismo de replicación maestro-esclavo (replicación) para realizar copias de seguridad en la máquina esclava.
7. Mantenimiento de datos y recuperación ante desastres Como DBA (todavía no lo soy, jaja), una de las tareas más importantes es garantizar que las tablas de datos se puedan utilizar de forma segura, estable y a alta velocidad. Por lo tanto, sus tablas de datos deben mantenerse con regularidad. La siguiente declaración SQL es útil:
VERIFICAR TABLA o REPARAR TABLA, verificar o mantener tablas MyISAM
OPTIMIZAR TABLA, optimizar la tabla MyISAM
ANALIZAR TABLA, analizar la tabla MyISAM Por supuesto, todos los comandos anteriores se pueden completar a través de la herramienta myisamchk y no se describirán en detalle aquí.
Las tablas de Innodb se pueden desfragmentar y mejorar la velocidad de indexación ejecutando las siguientes instrucciones:
ALTER TABLE tbl_name MOTOR = Innodb;
En realidad, esta es una operación NULL. En la superficie, no hace nada, pero en realidad reorganiza los fragmentos.
Las tablas MyISAM de uso común se pueden restaurar utilizando los métodos mencionados anteriormente. Si el índice está roto, puede utilizar la herramienta myisamchk para reconstruirlo. Para las tablas Innodb, no es tan sencillo porque almacena todas las tablas en un espacio de tablas. Sin embargo, Innodb tiene un mecanismo de verificación llamado punto de control difuso. Siempre que se guarde el archivo de registro, los errores se pueden reparar en función del archivo de registro. Puede agregar los siguientes parámetros en el archivo my.cnf para permitir que mysqld verifique automáticamente el archivo de registro cuando se inicie:
innodb_force_recovery = 4
Consulte el manual para obtener información sobre este parámetro.
8. Resumir la copia de seguridad de los datos y determinar la estrategia de copia de seguridad adecuada. Esto es una pequeña parte de lo que hace un DBA. Todo es difícil al principio.