Afin d'automatiser l'installation de MySQL sous Debian, Ansible est l'outils qu'il nous faut.
Les pre requis du script ansible :
---
# Mise en place de la replication MySQL
- hosts: mysql_replication
vars:
replication_master: mysql01
replication_replica: mysql02
replication_user: repl
replication_pass: repl
remote_user: root
tasks:
- name: Verification log_bin = ON
shell: mysql -s -N -e "show global variables like 'log_bin';" | awk '{ print $2 }' | grep "ON"
- name: Verification log_slave_updates = OFF
shell: mysql -s -N -e "show global variables like 'log_slave_updates';" | awk '{ print $2 }' | grep "OFF"
- name: Verification gtid_mode = ON
shell: mysql -s -N -e "show global variables like 'gtid_mode';" | awk '{ print $2 }' | grep "ON"
- name: Verification enforce_gtid_consistency = ON
shell: mysql -s -N -e "show global variables like 'enforce_gtid_consistency';" | awk '{ print $2 }' | grep "ON"
- name: Verification server_id sur {{replication_master}}
shell: mysql -s -N -e "show global variables like 'server_id';" | awk '{ print $2 }' | grep "1"
when: inventory_hostname_short == replication_master
- name: Verification server_id sur {{replication_replica}}
shell: mysql -s -N -e "show global variables like 'server_id';" | awk '{ print $2 }' | grep "2"
when: inventory_hostname_short == replication_replica
- name: Stop replication
shell: mysql -s -N -e "stop replica;"
- name: Drop replication User
shell: mysql -s -N -e "drop user if exists '{{replication_user}}'@'%';"
- name: Create replication User
shell: mysql -s -N -e "create user '{{replication_user}}'@'%' identified with mysql_native_password by '{{replication_pass}}';"
- name: Grant replication User
shell: mysql -s -N -e "grant replication slave on *.* to '{{replication_user}}'@'%';"
- name: Reset master
shell: mysql -s -N -e "reset master;"
- name: Reset replica
shell: mysql -s -N -e "reset replica;"
- name: Create replication source on {{replication_replica}}
shell: mysql -s -N -e "change replication source to source_host='{{replication_master}}', source_port=3306, source_user='{{replication_user}}', source_password='{{replication_pass}}', source_auto_position=1;"
when: inventory_hostname_short == replication_replica
- name: Start replica on {{replication_replica}}
shell: mysql -s -N -e "start replica;"
when: inventory_hostname_short == replication_replica
# ansible-playbook mysql_replication_8.0_setup.yml PLAY [mysql_replication] ****************************************************************************************************************************** TASK [Gathering Facts] ******************************************************************************************************************************** ok: [mysql02] ok: [mysql01] TASK [Verification log_bin = ON] ********************************************************************************************************************** changed: [mysql01] changed: [mysql02] TASK [Verification log_slave_updates = OFF] *********************************************************************************************************** changed: [mysql01] changed: [mysql02] TASK [Verification gtid_mode = ON] ******************************************************************************************************************** changed: [mysql01] changed: [mysql02] TASK [Verification enforce_gtid_consistency = ON] ***************************************************************************************************** changed: [mysql01] changed: [mysql02] TASK [Verification server_id sur mysql01] ************************************************************************************************************* skipping: [mysql02] changed: [mysql01] TASK [Verification server_id sur mysql02] ************************************************************************************************************* skipping: [mysql01] changed: [mysql02] TASK [Stop replication] ******************************************************************************************************************************* changed: [mysql01] changed: [mysql02] TASK [Drop replication User] ************************************************************************************************************************** changed: [mysql01] changed: [mysql02] TASK [Create replication User] ************************************************************************************************************************ changed: [mysql01] changed: [mysql02] TASK [Grant replication User] ************************************************************************************************************************* changed: [mysql01] changed: [mysql02] TASK [Reset master] *********************************************************************************************************************************** changed: [mysql01] changed: [mysql02] TASK [Reset replica] ********************************************************************************************************************************** changed: [mysql01] changed: [mysql02] TASK [Create replication source on mysql02] *********************************************************************************************************** skipping: [mysql01] changed: [mysql02] TASK [Start replica on mysql02] *********************************************************************************************************************** skipping: [mysql01] changed: [mysql02] PLAY RECAP ******************************************************************************************************************************************** mysql01 : ok=12 changed=11 unreachable=0 failed=0 skipped=3 rescued=0 ignored=0 mysql02 : ok=14 changed=13 unreachable=0 failed=0 skipped=1 rescued=0 ignored=0