How to handle heavy changes in MySQL replication with ansible

This can be useful for you if you have applications which critically depend on database response time and your MySQL databases are still on 5.5 or older.

If you need to add column into some really big table (or remove column) then MySQL starts heavy operation which can fully overload disk or CPU or both. Which will cause much longer response time and it can ruin your service.

We decided to use simple manual approach using ansible with these steps:

• stop replication on all replicas using ansible
• start migration on master and wait until this is done (I recommend monitoring with telegraf + influxDB)
• separately on each replica do following steps:
  • if connection cause heavy load on database, redirect them to other replica and wait until processlist is empty
    • but if load is not big and you use SSD then it is safe to migrate even with connections
  • start replication on this freed replica and monitor it until it is done
  • redirect connections back

ansible playbook for stopping replication on all replicas (playbook “stop_mysql_replicas.yaml”):

# stop mysql replication on replicas based on config file
#
# requires mysql root password as parameter !!!
#
# ansible-playbook "stop_mysql_replicas.yaml" -i ./replicas -e "mysql_root_password=......" 
#

- name: stop mysql replication
  hosts: all
  become: yes
  become_method: sudo
  gather_facts: yes

  tasks:
  - name: show hosts
    debug: msg="play_hosts={{play_hosts}}"
    
  - name: run stop slave command
    command: >
      mysql -u root -p{{ mysql_root_password }}  --execute="stop slave"
    register: mysqlresult

requires list of replicas in config file

ansible playbook for starting one replica (playbook “start_mysql_slave.yaml”):

# stop mysql replication on replicas based on config file
#
# requires mysql root password as parameter !!!
#
# ansible-playbook "start_mysql_slave.yaml" -i ./replicas -e "mysql_root_password=......" -e "slave=...."
#

- name: stop mysql replication
  hosts: "{{slave}}"
  become: yes
  become_method: sudo
  gather_facts: yes

  tasks:
  - name: show hosts
    debug: msg="play_hosts={{play_hosts}}"
    
  - name: run stop slave command
    command: >
      mysql -u root -p{{ mysql_root_password }}  --execute="start slave"
    register: mysqlresult