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
- if connection cause heavy load on database, redirect them to other replica and wait until processlist is empty
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