MySQL logical backup and restore (mysqldump, MySQL Shell)

Logical backup and restore with mysqldump or MySQL Shell (util.dumpInstance / dumpSchemas / dumpTables + util.loadDump): a table, a schema or the whole instance; structure only or data only; migration to another server; extracting a single table from a full dump; scheduled backups with systemd/cron, passwordless via login-path, verified, with retention. Every path ends with a row count comparison. Use this guide for migrations, single-object recovery and moving between versions; for daily backups of large databases, fast restores and point-in-time recovery (PITR), use the Percona XtraBackup guide.

Topology: 1 server (+ a target server for migration and instance restore)

Scenarios

• Table export / import — Back up one or more tables, copy them to another schema/server, or restore them. The source stays up; no locks are taken on InnoDB tables.
• Back up / restore a schema (database) — Back up an entire database (schema) or load it into another schema/server. The source stays up; tables with the same names on the target are dropped and recreated.
• Whole instance (users + all schemas) — The backup is taken on this server and restored on an empty server: users, privileges and all schemas. For rebuilding a server or changing version/platform.
• Structure only (DDL) — Table, view, trigger, procedure and event definitions; no data. For schema comparison, building an empty environment or putting the structure under version control.
• Data only — Only the rows are transferred while the structure already exists on the target (e.g. refreshing test data). Triggers on the target may fire during the load; the guide checks for this.
• Migration to another server — You work on the target server and connect to the source over the network with a login-path. At cutover the application stops writing; the downtime equals the dump + load time. For changing server, operating system or version.
• Extract a single table from a full dump — You have a full dump and need only one table (deleted rows, dropped table). It is extracted into a separate schema without touching the original schema, or the dropped table is put back.
• Scheduled backup (systemd timer / cron) — Automatic logical backup every day at a set time: a separate backup user, login-path, script, systemd timer or cron, retention period and error notification. The source stays up.

Before you start

• MySQL … is running; the operating system root user can connect without a password using the mysql command (auth_socket or ~/.mylogin.cnf). If it was installed with the DBAtlas MySQL single-server installation guide, the paths are the same.
• The size of the schemas is known and the backup directory … has enough free space (a compressed logical backup is usually 10–30% of the data size; indexes go into the dump only as definitions).
• All tables are InnoDB. --single-transaction (and MySQL Shell's consistent dump) is lock-free and consistent only for InnoDB tables; if there is a MyISAM table, writes to it during the dump cause inconsistency.
• The server where MySQL Shell will be installed can reach the MySQL repository (repo.mysql.com), or an internal mirror is configured.
• The same or a newer MySQL version is installed and running on the target server … (with the DBAtlas installation guide) and it contains no user schemas. Loading into an older version, such as 8.4 to 8.0 for a rollback, is not supported.
• The target server can reach port … on the source.
• The server can send email: the local MTA (postfix) relays to the corporate SMTP relay.
• A maintenance window is planned for a large restore or migration; if the target will be overwritten, the target has been backed up.
• This server is a healthy replica: IO and SQL threads are Yes, lag is low. The binlog position in the dump (--source-data=2) refers to this replica's own binlog; if it will be used for PITR, log_replica_updates must be enabled (the default in 8.x).
• This server is an InnoDB Cluster member and is currently SECONDARY / ONLINE.

Phases of the guide

• Preparation — Create the backup directory · Check table engines and long-running transactions · Install MySQL Shell
• Restore (import) — Create the target schema · Create the table structure on the target · Check the triggers on the target · Load (mysql) · Load (mysql, over the same schema) · Load (util.loadDump) …
• Instance backup — Export users and privileges · Back up all schemas (mysqldump) · Back up the instance (util.dumpInstance) · Copy the backup to the restore server · Load the backup (mysql) · Load the users …
• Migration account on the source server — Create the migration user · Open the port to the target in the firewall · Stop the application (cutover)
• Transfer on the target server — Save the source connection as a login-path · Migrate the users · Transfer the schemas (mysqldump | mysql) · Load the users on the target · Dump the source (util.dumpInstance / dumpSchemas) · Load into the target (util.loadDump) …
• Extract a single table from a dump — Check that the table is in the dump · Extract the table from the dump · Create the target schema · Load the table · Restore the table into the original schema · Compare the row count …
• Backup user and login-path — Create the operating system backup user · Create the MySQL backup account · Save the login-path (mysql_config_editor)
• Backup script — Install the notification tool · Write the backup script · Make the script executable · Take the first backup by hand
• Scheduling (systemd timer) — Write the systemd service · Write the systemd timer · Enable the timer and trigger the service by hand · Check that the scheduled job ran (the next day)
• Scheduling (cron) — Prepare the cron service · Write the cron job · Set the cron file permissions and test in a cron environment · Check that the scheduled job ran (the next day)
• Verification and monitoring — Restore the latest backup into a test schema · Monitor the age of the latest backup
• Next step: physical backup — Cover the limits of a logical backup