Cómo hacer una copia de seguridad y restaurar la base de datos en mysql
1. mysqldump
1.1 Copia de seguridad
Mysqldump es un mecanismo de copia de seguridad a nivel de SQL que convierte tablas de datos en archivos de script SQL. Es relativamente adecuado para diferentes situaciones. La actualización entre versiones de MySQL es también el método de copia de seguridad más utilizado. Ahora hablemos de algunos de los parámetros principales de mysqldump:
-Compatibility=name
Le dice a mysqldump con qué base de datos o con qué servidor MySQL antiguo 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. Utilice varios valores, separados por comas. Por supuesto, no se garantiza una compatibilidad total, pero se pretende que sea lo más compatible posible.
-Full-Insert, -c
Los datos exportados utilizan inserción completa con nombres de campos, es decir, todos los valores se escriben en una línea. Hacer esto puede mejorar la eficiencia de la inserción, pero puede verse afectado por el parámetro max_allowed_packet, lo que provoca un error de inserción. Por lo tanto, debes usar este parámetro con precaución, al menos yo no lo recomiendo.
-default-character-set=Juego de caracteres
Especifique el juego de caracteres que se utilizará al exportar datos. Si la tabla de datos no utiliza el juego de caracteres latino1 predeterminado, se debe especificar esta opción al exportar; de lo contrario, aparecerán caracteres confusos después de importar los datos nuevamente.
-Tecla deshabilitada
¡Dile a mysqldump que agregue /* al principio y al final de la declaración INSERT! 40000 ALTER TABLE clave deshabilitada de la tabla */; 40000 La tabla ALTER TABLE habilita la instrucción clave */;, que puede mejorar en gran medida la velocidad de las instrucciones de inserción porque reconstruye el índice después de que se hayan insertado todos los datos. Esta opción sólo está disponible para tablas MyISAM.
- extended-insert = true|false
De forma predeterminada, mysqldump activa el modo de inserción completa, por lo que si no desea usarlo, simplemente use esta opción y establezca su valor Establecido en falso.
-hex blob
Exporta un campo de cadena binaria en formato hexadecimal. Esta opción debe usarse si hay datos binarios. Los tipos de campos afectados son BINARY, VARBINARY y BLOB.
-Bloquear todas las tablas, -x
Antes de iniciar 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 las opciones -single-transaction y -lock-tables se desactivan automáticamente.
-Bloquear tabla
Es similar a -lock-all-tables, pero bloquea la tabla de datos actualmente exportada en lugar de bloquear todas las tablas de la biblioteca a la vez. Esta opción solo está disponible para tablas MyISAM, si es una tabla Innodb, puede usar la opción -single-transaction.
-No crear información, -t
Solo exportar datos, no agregar instrucción CREATE TABLE.
-Sin datos-d
No exporta ningún dato, sólo la estructura de la tabla de la base de datos.
- opt
Esta es solo una opción de acceso directo, equivalente a agregar-agregar-eliminar-tabla-agregar-bloqueo-crear-opción-disable-key-extend-insert-lock al mismo tiempo, opción -table-quick-set-charset. 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á activada de forma predeterminada, pero se puede desactivar usando -skip-opt. Tenga en cuenta que si ejecuta mysqldump sin especificar las opciones -quick u -opt, guardará todo el conjunto de resultados en la memoria. Pueden surgir problemas si exporta bases de datos grandes.
-Quick-Ask
Esta opción es útil al exportar tablas grandes. Obliga a mysqldump a recuperar registros directamente de la consulta del servidor en lugar de almacenar en caché todos los registros en la memoria.
-Rutinas,-R
Exportar procedimientos almacenados y funciones personalizadas.
-Transacción única
Esta opción envía la instrucción BEGIN SQL antes de exportar los datos. Begin no bloquea ninguna aplicación y garantiza un estado consistente de la base de datos al exportar. Sólo funciona con tablas transaccionales como InnoDB y BDB. Esta opción y la opción -lock-tables son mutuamente excluyentes, porque bloquear tablas hará que cualquier transacción pendiente se confirme implícitamente. Si desea exportar una tabla grande, debe usarla junto con la opción -quick.
-Triggers
Al mismo tiempo, exporta triggers. Esta opción está habilitada de forma predeterminada y 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/MySQL dump-uyejr-pye Jr-default-character-set = utf8-opt-extended-insert = false \
-triggers-R-hex-blob-x db_name>database name.sql
Utilice el siguiente SQL para realizar una copia de seguridad de la tabla Innodb:
/ usr/local/MySQL/bin/MySQL dump-uyejr-pye Jr-default-character-set = utf8-opt-extended-insert = false \
- disparadores -R - hex-blob -transacción única Nombre de la base de datos y nombre de la base de datos gt. sql
Reducción 1.2
Los archivos respaldados por mysqldump son scripts SQL y se pueden ingresar directamente. Hay dos formas de importar datos.
Utilice el cliente mysql directamente
Por ejemplo:
/usr/local/MySQL/bin/MySQL-uyejr-pye Jr db_name <nombre de la base de datos sql.
Usar sintaxis fuente
De hecho, esta no es una sintaxis SQL estándar, sino una función proporcionada por el cliente mysql, como:
FUENTE /tmp /db _ nombre . SQL;
Aquí debe especificar la ruta absoluta del archivo, y debe ser un archivo que el usuario que ejecuta mysqld (como nadie) tenga permiso para leer.
2. mysqlhotcopy
2.1 Copia de seguridad
Mysqlhotcopy es un programa PERL escrito originalmente por Tim Bunce. Utiliza tabla de bloqueo, tabla de actualización y cp o scp para realizar una copia de seguridad rápida de la base de datos. Esta es la forma más rápida de hacer una copia de seguridad de una base de datos o de una sola tabla, pero solo se puede ejecutar en la computadora 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 múltiples bases de datos a la vez y también admite expresiones regulares. Aquí hay algunos ejemplos:
root #/usr/local/MySQL/bin/mysqlhotcopy-h = localhost-u = yejr-p = yejrdb_name/tmp (copie el directorio de la base de datos db_name a /tmp).
root #/usr/local/MySQL/bin/mysqlhotcopy-h = localhost-u = yejr-p = yejr db _ nombre _ 1... nombre de la base de datos n /tmp
root #/usr/local/MySQL/bin/mysqlhotcopy -h=localhost-u=yejr -p=yejr db_name. /regex/ /tmp
Para un uso más detallado, consulte el manual 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, y también debe tener permiso para leer el directorio datadir/db_name.
2.2 Reducción
Mysqlhotcopy realiza una copia de seguridad de todo el directorio de la base de datos. Se puede copiar directamente al directorio datadir (aquí está /usr/local/mysql/data/) especificado por Myqld. Preste atención al problema de permisos, como se muestra en el siguiente ejemplo:
root # CP-RF db _ name/usr/local/MySQL/data/
root # chown-. rnobody:nobody/usr/local/ MySQL/data/ (cambie el propietario del directorio db_name al usuario que ejecuta mysqld).
3.Copia de seguridad de sintaxis SQL
3.1 Copia de seguridad
De hecho, la sintaxis de BACKUP TABLE es similar al principio de funcionamiento de mysqlhotcopy, bloqueando la tabla y luego copiando el archivo de datos. Se puede realizar una copia de seguridad en línea, pero el efecto no es ideal y no se recomienda. Solo copia archivos de estructura de tablas y archivos de datos, y no copia archivos de índice al mismo tiempo, por lo que la recuperación es más lenta. Ejemplo:
Devuelva la tabla tbl_name a "/tmp/db_name/";
Tenga en cuenta que para ejecutar este SQL, debe tener permisos de archivo y el directorio /tmp/db_name/ debe El usuario de mysqld puede escribirlo y el archivo exportado no puede sobrescribir los archivos existentes para evitar problemas de seguridad.
SELECT INTO OUTFILE exporta datos a un archivo de texto normal y los intervalos de campo se pueden personalizar para facilitar el procesamiento de estos datos. Ejemplo:
SELECT INTO OUTFILE '/tmp/db _ nombre/TBL _ nombre . txt ' FROM TBL _ nombre;
Tenga en cuenta que para ejecutar este SQL, debe tener permisos de archivo. Y el usuario mysqld debe poder escribir en el archivo /tmp/db_name/tbl_name.txt, y el archivo exportado no puede sobrescribir el archivo existente para evitar problemas de seguridad.
3.2 Recuperación
Para los archivos respaldados mediante el método de tabla de respaldo, puede ejecutar la instrucción RESTORE TABLE para restaurar la tabla de datos. Ejemplo:
Restaurar una tabla desde "/tmp/db_name/";
Los requisitos de permisos son similares a los descritos anteriormente.
Los archivos respaldados por el método SELECT INTO OUTFILE pueden ejecutar la instrucción LOAD DATA INFILE para restaurar la tabla de datos.
Ejemplo:
Cargar datos del archivo "/tmp/db_name/tbl_name.txt" en la tabla tbl_name
Los requisitos de permisos son similares a los descritos anteriormente. La tabla de datos ya debe existir antes de inyectar datos. Si le preocupan los datos duplicados, puede agregar la palabra clave REPLACE para reemplazar los registros existentes o usar la palabra clave IGNORE para ignorarlos.
Suplemento:
shell & gtmysqldump-quick db_name | gzip & gt;Db_name.contents.gz (el archivo creado en este ejemplo está en formato comprimido).
Los comandos para restaurar/transferir a otro sitio son los siguientes:
shell & gtgunzip & ltdb _ name.contents.gz _ name Los comandos anteriores son adecuados para máquinas con *sistemas operativos nix.
4. Habilite el registro binario.
El método Binlog es relativamente más flexible, sin preocupaciones y ahorra trabajo, 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:
server-id = 1
log-bin = bin log
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 y registra todas las operaciones de actualización de datos, y el último archivo es el índice de todos los binlogs y no se puede eliminar fácilmente. Para obtener información sobre binlog, consulte el manual.
Cuando necesite realizar una copia de seguridad, primero puede ejecutar la instrucción SQL para evitar que mysqld escriba en el binlog actual, de modo que pueda realizar una copia de seguridad directamente del archivo, logrando así el propósito de la copia de seguridad incremental:
Actualizar registros; si está realizando una copia de seguridad de servidores esclavos en un sistema de replicación, también debe realizar una copia de seguridad de los archivos master.info y Relay-log.info.
Puede utilizar la herramienta mysqlbinlog proporcionada por MySQL para ver el archivo binlog de respaldo, por ejemplo:
/usr/local/MySQL/bin/mysqlbinlog/tmp/bin log 000001.
Esta herramienta le permite mostrar todas las declaraciones SQL en una base de datos específica y limitar el rango de tiempo, lo cual es muy conveniente. Consulte el manual para obtener más detalles.
Al restaurar, puede utilizar declaraciones similares a las siguientes:
/usr/local/MySQL/bin/mysqlbinlog/tmp/bin log 000001 | MySQL-uyejr-pye Jr. db_name
Ejecute directamente la salida de la declaración SQL de mysqlbinlog como entrada.
Si tienes una máquina de respaldo, también puedes hacer una copia de seguridad de esta manera. Como máquina esclava, los requisitos de rendimiento de la máquina son relativamente bajos, por lo que el costo es bajo. El costo es bajo, se puede lograr una copia de seguridad incremental y se puede compartir parte de la presión de la consulta de datos. ¿por qué no?
5. Realizar una copia de seguridad directa de los archivos de datos
En comparación con el método anterior, la copia de seguridad de los archivos de datos es la más directa, rápida y conveniente, pero la desventaja es que la copia de seguridad incremental es básicamente una copia de seguridad. imposible. . Para garantizar la coherencia de los datos, se debe ejecutar la siguiente instrucción SQL antes del archivo anterior:
Actualice la tabla con un bloqueo de lectura, es decir, actualice todos los datos en la memoria al; disco y bloquee la tabla de datos al mismo tiempo para garantizar que durante el proceso de copia no se escriban datos nuevos. Los datos respaldados de esta manera también son muy simples de restaurar y se pueden copiar directamente 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 una tabla Innodb se daña, puede confiar en estos archivos de registro para recuperarse.
6. Estrategia de copia de seguridad
Para sistemas con un volumen de negocio medio, se puede determinar la siguiente estrategia de copia de seguridad: copia de seguridad completa por primera vez, copia de seguridad incremental una vez al día, copia de seguridad completa una vez al día. semana, y así sucesivamente. Para un sistema crítico y ocupado, es posible que desee realizar una copia de seguridad completa todos los días, una copia de seguridad incremental 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 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 la tabla de datos se pueda utilizar de forma segura. , estable y a alta velocidad. Por lo tanto, necesita mantener sus tablas de datos con regularidad. Las siguientes sentencias SQL son muy útiles:
Verificar tabla o Reparar tabla para verificar o mantener tablas MyISAM.
Optimizar tablas, optimizar tablas MyISAM
Analizar tablas, analizar tablas MyISAM
Por supuesto, todos los comandos anteriores se pueden iniciar utilizando la herramienta myisamchk, que No se menciona aquí.
Las tablas de Innodb se pueden desfragmentar y mejorar la velocidad de indexación ejecutando la siguiente instrucción:
ALTER TABLE TBL _ name ENGINE = Innodb;
Esto en realidad es un archivo vacío Las operaciones, que parecen no hacer nada, en realidad reorganizan los fragmentos.
Las tablas MyISAM de uso común se pueden restaurar mediante los métodos de recuperación mencionados anteriormente. Si el índice está dañado, puede utilizar la herramienta myisamchk para reconstruirlo. Para las tablas Innodb, esto no es sencillo ya que mantiene 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 corregir en función del archivo de registro. Puede agregar el siguiente parámetro al archivo my.cnf para que mysqld verifique automáticamente el archivo de registro al inicio:
innodb_force_recovery = 4
Para obtener información sobre este parámetro, consulte el Manual de referencia.
Es un poco largo. Tome su tiempo. Debería ayudarte.