Varios métodos para realizar copias de seguridad y restaurar bases de datos MySQL

Las tablas MyISAM se guardan en 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. Utilice la copia de seguridad de la base de datos de copia directa. Utilidades típicas como cp, tar o cpio. Cuando utilice el 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 se debe utilizar el mismo protocolo de bloqueo para "silenciar" el servidor. Cuando complete la copia de seguridad de la base de datos MySQL, deberá 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. 2. Utilice la copia de seguridad de la base de datos mysqldump mysqldump es 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. El programa mysqldump tarda en realizar copias de seguridad de las bases de datos, pero los archivos de texto que genera son fáciles de portar. Algunos parámetros principales de mysqldump: 1) --compatible=name Le dice a mysqldump con qué base de datos o versión antigua 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. 2) --complete-insert, -c exporta datos utilizando el método INSERT completo, incluidos los nombres de los campos, es decir, escribiendo todos los valores 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 y provocar un error de inserción. Por lo tanto, este parámetro debe usarse con precaución, al menos no lo recomiendo. 3) --default-character-set=charset especifica 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 la importación. los datos nuevamente. 4) --disable-keys le dice a mysqldump que agregue declaraciones ; y ; al principio y al final de la declaración INSERT, lo que puede mejorar en gran medida la velocidad de la declaración de inserción porque reconstruye el índice después de insertar todos los datos. Esta opción sólo es adecuada para tablas MyISAM. 5) --extended-insert = true|false 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.

6) --hex-blob exporta 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. 7) --lock-all-tables, -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. 8) --lock-tables 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. 9) --no-create-info, -t solo exporta datos sin agregar la instrucción CREATE TABLE. 10) --no-data, -d no exporta ningún dato, solo la estructura de la tabla de la base de datos. 11) --opt Esta es solo una opción rápida, que equivale a agregar --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables - -rápido al mismo tiempo --opción 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á habilitada de forma predeterminada, pero se puede deshabilitar con --skip-opt. Tenga en cuenta que si ejecuta mysqldump sin especificar las opciones --quick o --opt, todo el conjunto de resultados se colocará en la memoria. Pueden surgir problemas si exporta una base de datos grande. 12) --quick, -q Esta opción es útil al exportar tablas grandes. Obliga a mysqldump a generar directamente los registros obtenidos de la consulta del servidor en lugar de almacenarlos en la memoria caché después de obtener todos los registros. 13) --rutinas, -R exporta procedimientos almacenados y funciones personalizadas. 14) --single-transaction Esta opción envía una declaración SQL BEGIN antes de exportar datos. BEGIN no bloqueará ninguna aplicación y puede garantizar el 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. --triggers también exporta desencadenantes. Esta opción está habilitada de forma predeterminada, use --skip-triggers para deshabilitarla. Copia de seguridad: usar mysqldump para hacer una copia de seguridad de la base de datos es en realidad volcar la base de datos en una serie de declaraciones CREATE TABLE e INSERT. A través de estas declaraciones, podemos regenerar la base de datos.

El método para usar mysqldump es el siguiente: mysqldump --opt testdb | gzip gt; /data/backup/testdb.bak#--opt optimizará el proceso de volcado, el archivo de copia de seguridad generado será más pequeño y la operación de canalización posterior. se realizará la compresión de datos mysqldump --opt testdb mytable1, mytable2 | gzip gt; /data/backup/testdb_mytable.bak# El nombre de la tabla de datos puede ir seguido de la base de datos y solo se pueden exportar varias tablas de datos. se pueden separar por comas opción --opt También puede activar la opción --add-drop-table, que agregará una declaración DROP TABLE IF EXISTS antes de cada CREATE TABLE en el archivo de respaldo. Esto facilita la actualización de la tabla de datos sin provocar el error "la tabla de datos ya existe". También puede usar el comando mysqldump para transferir directamente la base de datos a otro servidor sin generar un archivo de respaldo. La ejecución repetida actualiza periódicamente la base de datos remota. mysqladmin -h remote_host create testdb mysqldump --opt testdb | mysql -h remote_host testdb Además, puede llamar remotamente al programa en el servidor a través de ssh, como por ejemplo: ssh remote_host mysql testdb | Copiando directamente el sistema. Haga una copia de seguridad de la base de datos como un archivo. Al realizar la copia de seguridad, asegúrese de que nadie modifique la base de datos. Para hacer esto, es mejor apagar el servidor. Si no se puede cerrar, intente bloquear la tabla de datos relevante en modo de solo lectura. A continuación se muestran algunos ejemplos: cp -r db /backup/db #Haga una copia de seguridad de la base de datos db en el directorio /backup/db cp nombre_tabla.* /backup/db #Haga una copia de seguridad solo de la tabla de datos nombre_tabla scp -r db remotehot:/usr /local/mysql/ data #Use scp para copiar la base de datos directamente al servidor remoto. Al copiar la base de datos directamente al host remoto, debe tenerse en cuenta que las dos máquinas deben tener la misma estructura de hardware, o todas las tablas de datos copiadas. Deben ser tipos de tablas de datos portátiles.

O /usr/local/mysql/bin/mysqldump -uroot -proot \--default-character-set=utf8 --opt --extended-insert=false \--triggers -R --hex-blob -x testdb gt testdb.sql utiliza el siguiente SQL para realizar una copia de seguridad de la tabla Innodb: /usr/local/mysql/bin/mysqldump -uroot -proot \--default-character-set=utf8 --opt --extended-insert=false \; --triggers -R --hex-blob --single-transaction testdb gt; testdb.sql Además, si desea implementar una copia de seguridad en línea, también puede usar el parámetro --master-data para lograrlo, de la siguiente manera: /usr/local/mysql/bin/ mysqldump -uroot -proot \--default-character-set=utf8 --opt --master-data=1 \--single-transaction --flush-logs testdb gt; sql solo se solicita al principio Bloquee la tabla, luego actualice el binlog y luego agregue 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, debe. puede utilizar este método. Restaurar: el archivo respaldado con mysqldump es un script SQL que se puede importar directamente. Hay dos formas de importar datos. Utilice el cliente mysql directamente, por ejemplo: /usr/local/mysql/bin/mysql -uroot -proot testdb lt; testdb.sql usa la sintaxis SOURCE. De hecho, esta no es la sintaxis SQL estándar, sino la función proporcionada por. Cliente mysql, por ejemplo: SOURCE /tmp/testdb.sql; La ruta absoluta del archivo debe especificarse aquí y debe ser un archivo que el usuario que ejecuta mysqld (como nadie) tenga permiso para leer. 3. Utilice la copia de seguridad de la base de datos mysqlhotcopy. Utilice la herramienta mysqlhotcopy, que es un script Perl DBI que puede realizar una copia de seguridad de la base de datos sin apagar el servidor. 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. Su principal ventaja es que copia archivos directamente, por lo que es más rápido que mysqldump. El bloqueo de datos se puede completar automáticamente sin apagar el servidor durante la copia de seguridad. El registro se puede actualizar para que se puedan sincronizar los puntos de control de los archivos de respaldo y los archivos de registro. Copia de seguridad: mysqlhotcopy admite la copia de varias bases de datos a la vez y también admite expresiones regulares.

Los siguientes son algunos ejemplos: /usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root -p=root \testdb /tmp (copia el directorio de la base de datos testdb a /tmp) /usr/local/mysql/ bin/mysqlhotcopy -h=localhost -u=root -p=root \testdb_1 testdb_2 testdb_n /tmp/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root -p=root \testdb./regex/ / tmp recovery: 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/). problemas de permisos, como en el siguiente ejemplo: cp -rf testdb /usr/local/mysql/data/chown -R nadie: nadie /usr/local/mysql/data/ (Cambie el propietario del directorio testdb al usuario que ejecuta mysqld ) 4. Utilice la copia de seguridad de la base de datos con instrucciones SQL BACKUP La sintaxis de la TABLA 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. Copia de seguridad: BACK TABLE tbl_test TO '/tmp/testdb/'; #Haga una copia de seguridad de la base de datos tbl_test en el directorio /tmp/testdb/, se creará automáticamente un directorio testdb. Para ejecutar esta declaración, debe tener permisos de ARCHIVO y. SELECCIONE los permisos en esas tablas, el servidor también debe poder escribir en el directorio de respaldo. Cuando se ejecuta esta declaración, los datos en la memoria primero se escribirán en el disco y luego los archivos .frm (archivo de definición de estructura de tabla) y .MYD (datos) de cada tabla de datos se copiarán del directorio de datos al directorio de respaldo. No copia el archivo .MYI (índice) porque se puede reconstruir con los otros dos archivos. Al realizar una copia de seguridad de la instrucción BACKUP TABLE, las tablas de datos se bloquean en secuencia. Al realizar una copia de seguridad de varias tablas de datos al mismo tiempo, las tablas de datos pueden modificarse. Por lo tanto, cuando se completa la copia de seguridad 0, los datos en el archivo de copia de seguridad y el archivo de copia de seguridad se modifican. Los datos en la tabla de datos actual pueden ser diferentes. Para eliminar esta diferencia, podemos bloquear la tabla de datos en modo de solo lectura y desbloquearla una vez completada la copia de seguridad. Por ejemplo: mysqlgt; LOCK TABLES tb1 READ, tb2 READ; mysqlgt; BACKUP TABLE tb1, tb2 TO 'backup/db'; UNLOCK TABLES; . 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/testdb/. Los archivos exportados no pueden sobrescribir los archivos existentes para evitar problemas de seguridad. SELECT INTO OUTFILE exporta los datos a un archivo de texto normal. Puede personalizar el espacio entre campos para facilitar el procesamiento de estos datos.

Ejemplo: SELECT * INTO OUTFILE '/tmp/testdb/tbl_test.txt' FROM tbl_test; tenga en cuenta que debe tener permiso de ARCHIVO para ejecutar este SQL y que mysqld debe poder escribir y exportar el archivo /tmp/testdb/tbl_test.txt Los archivos de usuario no pueden sobrescribir archivos ya existentes para evitar problemas de seguridad. Restaurar: para los archivos respaldados mediante el método BACKUP TABLE, puede ejecutar la instrucción RESTORE TABLE para restaurar la tabla de datos. Ejemplo: RESTORE TABLE FROM '/tmp/testdb/'; los requisitos de permisos 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: LOAD DATA INFILE '/tmp/testdb/tbl_name.txt' INTO TABLE tbl_name los requisitos de permisos son similares a los descritos anteriormente. Antes de importar datos, la tabla de datos ya debe existir.