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.

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
replica_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
replica_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 and 8.4.x.
  • When mgr_use_random_uuid: 1 is set, the 8.0 and 8.4 templates will 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

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 will only delete the data directory, log directory and systemd service of the target instance, but will not clean up the entire software base directory.

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. Deploy keepalived + Primary-Replica

keepalived_master_slave.yml is currently not recommended for production use. It should not be used as a recommended architecture for official releases.

12. Login

su - mysql
db3306