MySQL

dbbot provides deployment, backup and common operations playbooks for the MySQL ecosystem. This page is used to explain the applicable scenarios, core variables and execution entry points of each script.

For fuller parameter coverage, see: MySQL Variables and Parameters.

Preparation before execution

Before executing any MySQL Playbook, it is recommended to complete the following preparations:

  1. Refer to Deploy dbbot and configure Ansible to complete the control node preparation.
  2. Refer to dbbot Configuration and Single-Node Deployment Demo to configure inventory/hosts.ini and common_config.yml.
  3. Perform a connectivity check in the playbooks/ directory: ansible dbbot_mysql -m ping.

1. Deploy a Single Node

For a complete example of standalone deployment, see: dbbot Configuration and Single-Node Deployment Demo.

ansible-playbook single_node.yml

2. Deploy Primary-Replica Topology

For a complete example of one primary and two replicas, see: MySQL Deployment Quick Start.

Parameter valueTypeExplanation
master_ipStringMaster IP address
slave_ipsString listReplica IP address list
sub_netsStringMySQL trusted network segment, trusted domain or trusted IP
ansible-playbook master_slave.yml

3. Deploy MHA

For MHA support scope and variable descriptions, see: MySQL MHA Deployment Guide.

Parameter valueTypeExplanation
master_ipStringMaster IP address
slave_ipsString listReplica IP address list
sub_netsStringMySQL trusted network segment, trusted domain or trusted IP
manager_ipStringThe node where the MHA manager is located, cannot be the same as master_ip
net_work_interfaceStringThe name of the network card bound to the VIP
vipstringVIP address
vip_netmaskStringVIP netmask, common value is 32
ansible-playbook mha.yml

dbbot supports both traditional three-machine MHA and dual-machine MHA.

4. Deploy MGR

Parameter valueTypeExplanation
mysql_binlog_formatStringMGR must use row, this variable is used to override the common configuration
mysql_mgr_hostsString listMGR member IP list, by default the first IP is set to primary
sub_netsStringMySQL trusted network segment, trusted domain or trusted IP
greatsql_vipStringVIP of GreatSQL HA, only valid in GreatSQL HA scenario
greatsql_net_work_interfaceStringThe network card name of GreatSQL HA bound VIP
greatsql_netmaskStringThe netmask used by GreatSQL HA, the common value is 32
greatsql_group_replication_arbitratorBooleanWhether to enable the arbitration node
greatsql_group_replication_arbitrator_hostsString listArbitrator node list
ansible-playbook mgr.yml

Description:

  • The deployment result defaults to single-master mode.
  • mgr.yml currently supports MySQL 8.0.x, 8.4.x, and 9.7.x.
  • When mgr_use_random_uuid: 1 is set, the 8.0, 8.4, and 9.7 templates automatically generate and deliver a new group_replication_group_name.
  • Multi-master mode is not recommended as the default deployment mode. If necessary, please switch manually after validation.

5. Deploy InnoDB Cluster

For the full InnoDB Cluster guide, see: MySQL InnoDB Cluster Deployment Guide.

ansible-playbook innodb_cluster.yml

If applications also need a stable access layer, continue with: MySQL Router Deployment Guide.

6. Deploy xtrabackup

install_xtrabackup.yml is only for MySQL 5.7 physical backup.

ansible-playbook install_xtrabackup.yml

Description:

  • dbbot selects built-in RPMs based on the target operating system.
  • EL7 class systems use percona-xtrabackup-24-2.4.26-1.el7.x86_64.rpm.
  • EL8 class systems use percona-xtrabackup-24-2.4.28-1.el8.x86_64.rpm.

7. Deploy mysqlshell

ansible-playbook install_mysqlshell.yml

Suggestions:

  • Prioritize deployment of 8.0+ mysqlshell.
  • The mysqlshell version is backward compatible and can be used to manage MySQL 5.7.

8. Uninstall MySQL

ansible-playbook unsafe_uninstall.yml

unsafe_uninstall.yml removes the target instance directories defined by the active advanced_config.yml values for datadir, tmpdir, binlog_dir, relaylog_dir, and redolog_dir. With the current default layout, that means it removes both the instance data and log directories, but it does not remove the MySQL software base directory, binaries, Linux user, or instance my.cnf. Keeping the configuration file is intentional so the removed instance settings can still be audited later. Because dbbot supports custom data and log paths, validate the exact impact against your own configuration in a test environment before considering any production use.

Before running it, confirm that playbooks/common_config.yml currently points mysql_port at the instance you actually want to remove. unsafe_uninstall.yml does not infer the target instance automatically.

For non-interactive execution in scripts or CI, append -e dbbot_confirmation_input=confirm explicitly. For manual runs, keep using the interactive confirm prompt.

In scripts, CI jobs, or SSH one-liners, use the explicit portable Ansible path, for example:

python3 /usr/local/dbbot/portable-ansible/ansible-playbook unsafe_uninstall.yml -e dbbot_confirmation_input=confirm

If the instance also has legacy MHA or MHA-Go deployed, use the matching uninstall entry first so manager processes, VIPs, systemd units, and MHA configs are not left behind:

ansible-playbook mha_unsafe_uninstall.yml
ansible-playbook mha_go_unsafe_uninstall.yml

9. Deploy the 5.7 Backup Script

ansible-playbook backup_script.yml

Description:

  • Only supports MySQL 5.7.
  • The script will perform both xtrabackup installation and backup script deployment.
  • The backup script is generated by default in {{ mysql_data_dir_base }}/backup{{ mysql_port }}.sh.
  • Scheduled tasks are written to the mysql user crontab, but not to /etc/cron.d/.
  • Default cron is 02:00 every day.
  • Supports physical full backup, binlog archiving, my.cnf archiving and simple retention policy.
  • It is recommended to use the mysql user when executing manually, for example su - mysql -c "sh /database/mysql/backup3310.sh".

10. MySQL 8.4 Backup and Restore

MySQL 8.4 uses a clone-based backup and PITR restore workflow. For details, see: MySQL 8.4 Backup and Restore.

Related playbooks:

  • ansible-playbook backup_script_8.4.yml
  • ansible-playbook restore_pitr_8.4.yml

11. Login

su - mysql
db3306

12. Variables and Parameters

For the complete variable reference, see: MySQL Variables and Parameters.