MySQL 8.4 Backup and Restore

This article explains how to use backup_script_8.4.yml and restore_pitr_8.4.yml.

Scope of application:

  • MySQL 8.4.x
  • Single instance, primary-replica, MGR, InnoDB Cluster metadata scenarios
  • First do clone backup, and then combine it with binlog to do point-in-time recovery (PITR)
/usr/local/dbbot/mysql_ansible/playbooks/backup_script_8.4.yml
/usr/local/dbbot/mysql_ansible/playbooks/restore_pitr_8.4.yml

2. 8.4 Backup script

2.1 Configuration entry

File: playbooks/vars/var_backup_script_8.4.yml

Key parameters:

  • backup_topology
  • backup_scope
  • backup_preferred_host
  • backup_base_dir
  • backup_keep_full
  • backup_keep_binlog_days
  • backup_cron

Default configuration example:

backup_topology: auto
backup_scope: primary_only
backup_preferred_host: ""
backup_base_dir: "/backup"
backup_cron: "0 2 * * *"

2.2 backup_scope Description

  • primary_only: current default value. Suitable for single instances, test environments and first time validation, out of the box.
  • replica_one: Recommended for primary-replica or MGR scenarios in production environments, allowing only one replica database to bear the backup pressure.
  • replica_all: Backup is performed on all replicas.
  • all: Perform backup on all nodes.
  • selected: only executed on the node specified by backup_preferred_host.

Recommendations for production environments:

  • If there are replicas of your schema, it is recommended to change backup_scope to replica_one.
  • The reason is that both clone and binlog archiving consume disk IO, network and instance resources.
  • Putting the backup on the replica can reduce the impact on the primary write path and business response time.
  • Retaining primary_only is more suitable for single instances or scenarios where you explicitly want to retain backups on the current primary node.

2.3 Perform backup deployment

cd /usr/local/dbbot/mysql_ansible/playbooks
ansible-playbook backup_script_8.4.yml

After deployment is completed, it will generate:

  • {{ mysql_data_dir_base }}/scripts/backup_clone{{ mysql_port }}.sh
  • {{ mysql_data_dir_base }}/scripts/backup_schedule{{ mysql_port }}.sh
  • {{ mysql_data_dir_base }}/scripts/backup_purge{{ mysql_port }}.sh
  • {{ mysql_data_dir_base }}/scripts/backup_clone{{ mysql_port }}.conf
  • Scheduled tasks in mysql user crontab

2.4 Backup product structure

Take 3309 as an example:

/backup/mysql3309/
├── 20260307_002415/
│   ├── clone/
│   └── meta/
├── binlog/
├── binlog.txt
└── backup_3309.log

Among them:

  • clone/: clone full snapshot
  • meta/: snapshot metadata, my.cnf backup, clone details
  • binlog/: binlog files required for incremental playback
  • binlog.txt: binlog list
  • backup_3309.log: backup log

2.5 Perform a backup manually

su - mysql
sh /database/mysql/scripts/backup_schedule3309.sh

3. 8.4 Restore script

3.1 Configuration entry

File: playbooks/vars/var_restore_pitr_8.4.yml

Key parameters:

  • restore_mode
  • backup_snapshot_id
  • backup_source_mysql_port
  • restore_target_mysql_port
  • backup_root_dir
  • enable_pitr
  • pitr_target_time
  • pitr_target_gtid
  • pitr_target_gtid_inclusive

Typical example:

restore_mode: "new_instance"
backup_snapshot_id: "20260307_002415"
backup_source_mysql_port: 3309
restore_target_mysql_port: 3388
backup_root_dir: "/backup"
enable_pitr: true
pitr_target_time: ""
pitr_target_gtid: "7318dfcb-1978-11f1-a779-000c292d7170:10"
pitr_target_gtid_inclusive: true

Rules:

  • When enable_pitr: true is set, pitr_target_time and pitr_target_gtid are selected.
  • restore_mode: new_instance will first deploy a new instance and then perform recovery.
  • mysql_port will automatically align to restore_target_mysql_port.

3.2 Perform recovery

If it is executed only on the recovery node, it is recommended to add --limit:

cd /usr/local/dbbot/mysql_ansible/playbooks
ansible-playbook restore_pitr_8.4.yml --limit 192.0.2.131

3.3 Validation after recovery

mysql -uadmin -h127.0.0.1 -P3388 -pDbbot_admin@8888 -e "show databases"
mysql -uadmin -h127.0.0.1 -P3388 -pDbbot_admin@8888 -e "select * from dbbot_pitr.t order by id"

4. Usage suggestions

  • Deploy the backup script first, and then do a manual backup drill.
  • When restoring, give priority to selecting a new target port and do not directly overwrite the production instance.
  • Complete at least one “backup + recovery + validation” closed-loop drill before the production environment goes online.