Cómo hacer un volcado de MySQL

Cómo guardar su base de datos MySQL

MySQL es un servicio de base de datos para almacenar cualquier tipo de datos. Ofrece diferentes tipos de opciones de copia de seguridad, que se pueden elegir con el tamaño de los datos, especificaciones de hardware o capacidad de almacenamiento en mente.

MySQL proporciona:

• Copias de seguridad lógicas, ejecutadas a través de un archivo . sql y crear/insertar instrucciones. Más tarde, puede restaurar una base de datos desde este archivo. Este tipo de copia de seguridad se considera más lento que otros, y adecuado para bases de datos de tamaño mediano.
• Las copias de seguridad físicas se ejecutan copiando archivos en el formato original. El inconveniente de este tipo de copia de seguridad es que la base de datos solo se puede restaurar en un servidor MySQL.
• Copias de seguridad consistentes, ejecutadas a través de copias de seguridad regulares automatizadas de archivos.

También se puede crear una copia de seguridad completa (una copia de toda la base de datos), una copia de seguridad incremental (una copia repetitiva de los cambios realizados en la base de datos), y una copia de seguridad diferencial (una copia de los cambios realizados desde la última copia de seguridad).

La parte del paquete de base de datos de relaciones es mysqldump, que es una utilidad de línea de comandos para copias de seguridad de bases de datos MySQL. Se puede usar para recrear datos en un solo archivo que contenga instrucciones SQL y luego se puede usar para recrear datos en su formato original. Con este comando, se pueden volcar tablas, varias bases de datos o todos los datos del servidor para una posterior migración a otro servidor.

En general, se puede usar fácilmente un comando. Sin embargo, muchos usuarios enfrentan complicaciones al restaurar la base de datos desde un archivo de copia de seguridad usando comandos SQL. El proceso de recreación de la base de datos puede tomar un tiempo si tiene una base de datos grande ya que mysqldump ejecuta todas las instrucciones SQL para insertar los datos.

Tenga en cuenta que mysqldump no puede volcar ciertas tablas, incluidas las tablas temporales, las tablas del sistema, las tablas de esquemas de rendimiento (de la base de datos de esquemas de rendimiento) y las tablas de esquemas de información (de la base de datos de esquemas de información). Sin embargo, el comando ofrece opciones de personalización: puede especificar excluir ciertos tipos de tablas o datos del volcado con el comando --ignore-table o usar la opción -no-data para mantener la estructura de la tabla mientras se deshace de los datos.

¿Qué tareas facilita Mysqldump?

La utilidad mysqldump facilita:

• Copias de seguridad de la base de datos;
• Migración de bases de datos;
• Versionamiento de base de datos (puede crear diferentes versiones de su base de datos, que puede volver a más adelante);
• Replicación del entorno (este comando puede ayudar a replicar los datos de producción para crear un entorno estable para el desarrollo o las pruebas);

Estructura de los datos exportados

Los datos exportados consistirán en:

• Archivos comprimidos en formatos gz (gzip) o . bz2 (bzip2);
• Instrucciones SQL en forma de script SQL utilizado para recrear la estructura y los datos de la base de datos;
• Datos con formato XML para facilitar la integración de datos con otros sistemas;
• Archivos de texto delimitados por pestañas utilizados para la importación y exportación de datos;
• Valores separados por comas o archivos CSV utilizados para el intercambio de datos;
• Los formatos personalizados definidos por el usuario se utilizan para personalizar el formato de salida;

Privilegios para el uso de Mysqldump

Para comenzar a usar el comando mysqldump, se requiere un mínimo de privilegios de acceso de lectura completa. Para usar la funcionalidad extendida, se necesitan los siguientes privilegios:

• SHOW VIEW (para acceder a las definiciones de vista);
• SELECT (para la lectura de datos);
• LOCK TABLES (para el bloqueo de la mesa);
• RELOAD (para usar la opción --flush-privileges);

Ejemplos de volcado de MySQL

Como ya hemos mencionado, con el comando mysqldump puede realizar copias de seguridad de tablas específicas, una base de datos única, múltiples bases de datos y todas las bases de datos desde el servidor MySQL. Echemos un vistazo a cada ejemplo.

Copia de seguridad de tablas MySQL específicas

Para hacer copias de seguridad de tablas específicas desde una base de datos MySQL, ejecute el siguiente comando:

mysqldump -u my_user -p database_name table1 table2 > tables.sql

Aquí:

• my_user - es para su nombre de usuario MySQL;
• -p - es para la contraseña del usuario MySQL;
• database_name - es el nombre de la base de datos que contiene las tablas necesarias;
• table1 table2 - es para los nombres de las tablas que desea respaldar;

Si desea hacer una copia de seguridad de más tablas, puede usar el mismo principio y agregar los nombres de las bases de datos que desea realizar una copia de seguridad adicional.

Al finalizar, el archivo "tables.sql" creado por mysqldump contendrá los comandos SQL necesarios para recrear las tablas y los datos dentro de ellas.

Copia de seguridad de una única base de datos

Si desea realizar una copia de seguridad de una sola base de datos, deberá usar el siguiente comando:

mysqldump -u my_user -p database_name > backup.sql

Aquí:

• my_user - es para su nombre de usuario MySQL;
• -p - es para la contraseña del usuario MySQL;
• database_name - es para el nombre de la base de datos que desea respaldar;

Después de ejecutar el comando, el archivo "backup.sql" tendrá los datos necesarios para restaurar la base de datos en su forma original.

Copia de seguridad de múltiples bases de datos

Para realizar una copia de seguridad de varias bases de datos, debe agregar el indicador -database al comando mysqldump, como sigue:

mysqldump -u my_user -p --databases db1 db2 db3 > backup.sql

Aquí:

• my_user - es para su nombre de usuario MySQL;
• -p - es para la contraseña del usuario MySQL;
• db1 db2 db3 - es para los nombres de las bases de datos que desea respaldar;

Copia de seguridad de todas las bases de datos del servidor MySQL

Si desea realizar una copia de seguridad de todas las bases de datos desde un servidor MySQL, debe agregar el indicador --all-database cuando use el comando mysqldump:

mysqldump -u my_user -p --all-databases > all_databases.sql

El archivo final all_databases.sql contendrá los comandos SQL necesarios para recrear las bases de datos respaldadas.

Cómo restaurar una base de datos en un servidor remoto

Para replicar las bases de datos respaldadas en un nuevo servidor, primero tenemos que asegurarnos de que el servidor mencionado tenga una base de datos en blanco o su esquema.

Para esto, puede comprobar si un servidor ya tiene una base de datos o crear una si no:

mysql -u root -pYOUR_PASSWORD -e "CREATE DATABASE destination_db

Este comando va a crear una base de datos en un nuevo servidor.

Luego, puede restaurar fácilmente tablas o bases de datos desde las copias de seguridad que haya creado. Usando una sola base de datos como ejemplo, puede usar el siguiente comando para restaurar una base de datos desde los archivos de copia de seguridad:

mysql -u root -pYOUR_PASSWORD destination_db < single_table_dump.sql

Resumen

Mysqldump es una utilidad versátil que puede ayudar a hacer copias de seguridad de bases de datos de una manera que sea accesible incluso para principiantes. Este comando puede empaquetar toda la base de datos en un solo archivo, desde el cual puede recrear la base de datos. Mysqldump es lo suficientemente flexible como para que un usuario pueda elegir qué parte de la base de datos quiere que se respalde: ciertas tablas, toda la base de datos, varias bases de datos o todas las bases de datos en el servidor. En general, esta herramienta puede ser útil para aquellos que tratan regularmente con bases de datos.

FAQ

¿Cuál es el efecto del indicador --single-transaction en mysqldump?

En resumen, --single-transaction agiliza la copia de seguridad de la base de datos mediante una sola transacción. Significa que mysqldump se puede realizar con una sola transacción, siempre que la base de datos permanezca coherente durante todo el proceso de copia de seguridad.

¿Qué métodos se pueden emplear para volcar tablas grandes de forma eficaz?

Puede combinar --single-transaction y -quick para descargar bases de datos grandes más rápido.

Este enfoque es más adecuado para tablas InnoDB ya que utiliza menos RAM y garantiza la coherencia del proceso de volcado.

¿Cómo se pueden ignorar las tablas durante el proceso mysqldump?

Puede ignorar ciertas tablas con la opción --ignore-table. Puede verse algo como esto:

mysqldump -u root -pmypassword my_db –-ignore-table=my_db.table_to_ignore > my_db.sql

Por lo tanto, las tablas que usted dice que deben ignorarse se notifican con  –-ignore-table=DATABASE_NAME.TABLE_TO_IGNORE.

También puede ignorar todas las tablas de una base de datos o toda la base de datos, tendrá que repetir la parte de notificación para que incluya todas las tablas:

mysqldump -u root -pmypassword –-ignore-table="my_db.table1" –-ignore-table="my_db.table2" –-ignore-table="my_db.table3" > all_databases.sql

¿Qué enfoque se debe adoptar para volcar datos BLOB binarios con mysqldump?

Si una base de datos tiene datos binarios, podría tener algunos problemas. Si necesita volcar una base de datos MySQL que tenga datos binarios, use la opción --hex-blob. Esta opción ordena cadenas binarias y las pone en un formato distinguible. Así es como desea volcar datos binarios:

mysqldump -u root -pmypassword my_bin_db --hex-blob > my_bin_db.sql

¿Es posible utilizar la cláusula "dónde" con mysqldump?

Puede usar la cláusula "where" con mysqldump para facilitar la tarea de ordenar los datos que desea respaldar desde la base de datos. La cláusula "where" inicia la cadena de condiciones y toma los datos que cumplen con los criterios:

mysqldump -u root -pmypassword wpdb --tables thetable --where="date_created > '2017-04-27'" > wpdb_myrecord.sql