ansible/ansible — pull request #5765 by kustodian (2014-01-25 13:42:00+00:00)
Title: mysql_replication should not connect to the 'mysql' database

All the actions by the `mysql_replication` plugin can be done by connecting to the `NULL` database. There is no need to connect to the `mysql` db, since there are permissions problems when connecting to the database on a remote host, e.g. when you want to run `getmaster` on a remote host.

This will make it much easier to deploy a master-slave (or master-master) MySQL cluster and everything could be done with the user which will be used for replication. If you create a replication user on both servers with only replication permissions like this:
```GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO replication@'%' IDENTIFIED BY 'PASSWORD';```

You will be able to connect on a remote host with that same user and run `SHOW MASTER STATUS` which you need if you want to configure replication. The problem is that if you create a user `replication` like this it doesn't have access to the `mysql` database.

Here a part of the mysql role which automatically sets replication master-master replication:
```yml
- name: Create MySQL user for replication
mysql_user: name=replication host=% priv='*.*:REPLICATION SLAVE,REPLICATION CLIENT' password={{ mysql_replication_pass }} state=present

# This doesn't work if the mysql_replication plugin connects to the 'mysql' db
- name: Get master information from the master MySQL server
mysql_replication:
mode=getmaster
login_host={{ hostvars[mysql_replication_master_hostname]['ansible_' + mysql_replication_master_interface]['ipv4']['address'] }}
login_user=replication
login_password='{{ mysql_replication_pass }}'
register: master_status

- name: Configure master
mysql_replication:
mode=changemaster
master_host={{ hostvars[mysql_replication_master_hostname]['ansible_' + mysql_replication_master_interface]['ipv4']['address'] }}
master_user=replication
master_password={{ mysql_replication_pass }}
master_log_file={{ master_status.File }}
master_log_pos={{ master_status.Position }}

- mysql_replication: mode=startslave
```