MySQL MGR Deployment Guide

This article explains how to use mgr.yml to deploy MySQL Group Replication.

1. Scope

  • Target architecture: 3 to 9 MGR nodes
  • Database version: MySQL 8.0.x, 8.4.x, or 9.7.x
  • Host group: [dbbot_mysql]
  • Automatic validation: MySQL instance checks plus MGR member count, member state, and primary count

Use a fresh environment whenever possible. mgr.yml installs MySQL instances first and then configures Group Replication.

MySQL 9.7.x uses glibc2.28 packages and is not supported on the CentOS/RHEL 7 family; use MySQL 8.4.x on those systems.

2. Inventory

[dbbot_mysql]
192.0.2.131 ansible_user=root ansible_ssh_pass="'<your_password>'"
192.0.2.132 ansible_user=root ansible_ssh_pass="'<your_password>'"
192.0.2.133 ansible_user=root ansible_ssh_pass="'<your_password>'"

3. Key variables

Edit mysql_ansible/playbooks/common_config.yml:

db_type: mysql
mysql_version: "8.4.9"
mysql_port: 3306
fcs_allow_dbbot_default_passwd: true

Edit mysql_ansible/playbooks/vars/var_mgr.yml:

mysql_binlog_format: row
mysql_mgr_port: "{{ (mysql_port | int) * 10 + 1 }}"
sub_nets: "192.0.2.%"
mysql_mgr_hosts:
  - "192.0.2.131"
  - "192.0.2.132"
  - "192.0.2.133"

Notes:

  • mysql_mgr_hosts must match the hosts in [dbbot_mysql].
  • mysql_mgr_hosts supports 3 to 9 nodes.
  • sub_nets is the source subnet allowed for the MGR replication account.
  • mysql_mgr_port is calculated as mysql_port * 10 + 1 by default, for example 33061 when mysql_port is 3306.

4. Execute deployment

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

During execution, enter confirm as prompted. For non-interactive execution, append:

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

5. Post-deployment validation

mgr.yml runs MGR validation automatically in post_tasks by default. You can also inspect it manually:

mysql -uadmin -h127.0.0.1 -P3306 -pDbbot_admin@8888 \
  -e "SELECT MEMBER_HOST, MEMBER_STATE, MEMBER_ROLE FROM performance_schema.replication_group_members ORDER BY MEMBER_HOST;"

Validation points:

  • Member count matches mysql_mgr_hosts.
  • Every member has MEMBER_STATE = ONLINE.
  • Exactly one member is PRIMARY.

6. Startup protection and restarts

mgr.yml renders this startup policy for MGR nodes:

super_read_only=ON
group_replication_start_on_boot=ON
group_replication_bootstrap_group=OFF

During initial deployment, the playbook temporarily disables read-only mode so it can create the administration and replication accounts and install the Group Replication plugin. After the group is created, Group Replication manages the runtime role state: the primary is writable and secondaries remain in super_read_only=ON.

After a member crash or host restart, mysqld starts Group Replication automatically and attempts to rejoin the existing majority. Do not return the member to application traffic until its local MEMBER_STATE is ONLINE. The upstream load balancer must use live group membership for health checks instead of checking only the port, SELECT 1, or @@read_only.

group_replication_start_on_boot=ON does not safely recover a complete outage where every member stopped. After a complete outage, compare gtid_executed and certified transactions on every member, bootstrap the member with the most complete transaction set exactly once, and then join the remaining members. Keep group_replication_bootstrap_group=OFF in the static configuration; never persist it as ON for automatic recovery.

7. Uninstall

Uninstall reads the effective port and directory settings from the current common_config.yml / advanced_config.yml. After confirming mysql_port points to the instance you want to remove, run:

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

During execution, enter confirm as prompted. This entry removes the target MySQL instance data, log, and runtime directories. It does not remove the MySQL software base directory, Linux user, or my.cnf.