bennojoy / mysql Goto Github PK

ansible role for mysql

mysql's Introduction

MySQL Server

This roles helps to install MySQL Server across RHEL and Ubuntu variants. Apart from installing the MySQL Server, it applies basic hardening, like securing the root account with password, and removing test databases. The role can also be used to add databases to the MySQL server and create users in the database. It also supports configuring the databases for replication--both master and slave can be configured via this role.

Requirements

This role requires Ansible 1.4 or higher, and platform requirements are listed in the metadata file.

Role Variables

The variables that can be passed to this role and a brief description about them are as follows:

  mysql_port: 3306                 # The port for mysql server to listen
  mysql_bind_address: "0.0.0.0"    # The bind address for mysql server
  mysql_root_db_pass: foobar       # The root DB password

  # A list that has all the databases to be
  # created and their replication status:
  mysql_db:                                 
       - name: foo
         replicate: yes
       - name: bar
         replicate: no

  # A list of the mysql users to be created
  # and their password and privileges:
  mysql_users:                              
       - name: benz
         pass: foobar
         priv: "*.*:ALL"

  # If the database is replicated the users
  # to be used for replication:
  mysql_repl_user:                          
    - name: repl
      pass: foobar

  # The role of this server in replication:
  mysql_repl_role: master

  # A unique id for the mysql server (used in replication):
  mysql_db_id: 7

Examples

Install MySQL Server and set the root password, but don't create any database or users.
- hosts: all roles:
  - {role: mysql, mysql_root_db_pass: foobar, mysql_db: none, mysql_users: none }
Install MySQL Server and create 2 databases and 2 users.
- hosts: all roles:
  - {role: mysql, mysql_db: [{name: benz}, {name: benz2}], mysql_users: [{name: ben3, pass: foobar, priv: ".:ALL"}, {name: ben2, pass: foo}] }

Note: If users are specified and password/privileges are not specified, then default values are set.

Install MySQL Server and create 2 databases and 2 users and configure the database as replication master with one database configured for replication.
- hosts: all roles:
  - {role: mysql, mysql_db: [{name: benz, replicate: yes }, { name: benz2, replicate: no}], mysql_users: [{name: ben3, pass: foobar, priv: ".:ALL"}, {name: ben2, pass: foo}], mysql_repl_user: [{name: repl, pass: foobar}] }
A fully installed/configured MySQL Server with master and slave replication.
- hosts: master roles:
  - {role: mysql, mysql_db: [{name: benz}, {name: benz2}], mysql_users: [{name: ben3, pass: foobar, priv: ".:ALL"}, {name: ben2, pass: foo}], mysql_db_id: 8 }
- hosts: slave roles:
  - {role: mysql, mysql_db: none, mysql_users: none, mysql_repl_role: slave, mysql_repl_master: vm2, mysql_db_id: 9, mysql_repl_user: [{name: repl, pass: foobar}] }

Note: When configuring the full replication please make sure the master is configured via this role and the master is available in inventory and facts have been gathered for master. The replication tasks assume the database is new and has no data.

Dependencies

None

License

BSD

Author Information

Benno Joy

mysql's People

Contributors

Stargazers

Watchers

Forkers

tgerla stwind hounddog pinterb fabianmet monofone gr360ry innodrift ggillies cascaya shamanime pierrepaul pturczyk carlitux linkux-it dlip star26bsd raebo collegeappz kinoppyd contegix baldwinkoo rr326 geogdog electrichummingbird carlos22 clovify outboundexplorer certifiedloud bitheater showkasepad neuweb sinicis russell-io matterstech milamberspace dhimanpuneet max-b ichtar davidmcclure floedesigntechnologies ginsys dimdung tompscanlan rslnk damko wzur sheriffac antoniohs marcusdb simonbaird ashishjain14 staticfloat cscfi willthames einarc billsworld javyzheng valentinfunk chillisystems xpxstar rajughoshdev exygy kanghaiyang jaymell dmitrym0 mdautry mohiuddinopu mosoriob ptahdunbar ragb hrandy j0lly goldmund84 lyvivian0077 eswar-github sxlstevengit kzap msoranno deathowl slgevens cilangovan sd2017 hopeua wissalmanai erilbeth mrdxiangmu ramjive tmas0 kserge2001 numediaweb tsusilkumar gakaya saivanianthati lalkrishnak elmozgo optionalg pleemans igorlefand anasme

mysql's Issues

Got "host is not a legal parameter in an Ansible task or handler" with ansible 1.4.4

See #2

Got "host is not a legal parameter in an Ansible task or handler" with ansible 1.4.3

$ ansible-playbook ansible/site.yml -vvvv
ERROR: host is not a legal parameter in an Ansible task or handler

$ cat ansible/site.yml

---
- hosts: mysql-servers
  sudo: true
  roles:
  - bennojoy.mysql

$ ansible --version
ansible 1.4.3

when commented out main.yml:77, it is working fine

  #host: ip={{ hostvars[mysql_repl_master].ansible_default_ipv4.address }} hostname={{ mysql_repl_master }} state=present

docs mysql >> bennojoy.mysql ?

When installing and using your role via ansible-galaxy. Nowhere in the ansible-galaxy nor your page documentation could I find that I needed to specify bennojoy.mysql rather than mysql as your examples specify.

I understand there are other deployment options where mysql would be correct, but could you add a note clarifying the difference?

Deprecation warnings

Hi,

If you have some time, could you fix the deprecated syntax (I'm running Ansible 2.6.1) :

TASK [mysql : Install the mysql packages in Debian derivatives] ***********************************************************************************************************************************************************************************************
[DEPRECATION WARNING]: State 'installed' is deprecated. Using state 'present' instead.. This feature will be removed in version 2.9. Deprecation warnings can be disabled by setting deprecation_warnings=False in ansible.cfg.
TASK [mysql : Ensure the hostname entry for master is available for the client.] ******************************************************************************************************************************************************************************
[DEPRECATION WARNING]: Using tests as filters is deprecated. Instead of using `result|failed` use `result is failed`. This feature will be removed in version 2.9. Deprecation warnings can be disabled by setting deprecation_warnings=False in ansible.cfg.
TASK [mysql : Get the current master servers replication status] **********************************************************************************************************************************************************************************************
[DEPRECATION WARNING]: Using tests as filters is deprecated. Instead of using `result|failed` use `result is failed`. This feature will be removed in version 2.9. Deprecation warnings can be disabled by setting deprecation_warnings=False in ansible.cfg.
TASK [mysql : Change the master in slave to start the replication] ********************************************************************************************************************************************************************************************
[DEPRECATION WARNING]: Using tests as filters is deprecated. Instead of using `result|failed` use `result is failed`. This feature will be removed in version 2.9. Deprecation warnings can be disabled by setting deprecation_warnings=False in ansible.cfg.

No hurry though, syntax will be dropped in Ansible 2.9.

Thank you for the this clean role.

Add the python-mysqldb package

The python-mysqld package ( on ubuntu ) is needed to do some task like : Get the current master servers replication status

It can be good to check before if this package is present or not.

Remote access denied

Hi. I am new to ansible. I user your role to install and configure MYSQL. Its working fine but when I try to login remotely using MYSQL work bench or any other software it does not connect. Secondly it donot prompt for password which I created using this module.

MySQL-python package required on remote host

All my hosts are on CentOS 7.6. I needed to add this to make the mysql_user module work:

- name: install MySQL-python package
  yum:
    name: MySQL-python
    state: present

One or more undefined variables: 'str object' has no attribute 'name'", 'failed': True} for Copy my.cnf task

I keep receiving the following error for the Copy my.cnf file task:

This output is with the -v flag, but I'm still not sure what the issue is.

PLAY [all] ******************************************************************** 

GATHERING FACTS *************************************************************** 
ok: [192.241.139.34]

TASK: [mysql | Add the OS specific variables] ********************************* 
ok: [192.241.139.34] => {"ansible_facts": {"mysql_conf_dir": "/etc/mysql/", "mysql_pkgs": ["python-selinux", "mysql-server", "python-mysqldb"], "mysql_service": "mysql"}, "item": ""}

TASK: [mysql | Install the mysql packages in Redhat derivatives] ************** 
skipping: [192.241.139.34]

TASK: [mysql | Install the mysql packages in Debian derivatives] ************** 
ok: [192.241.139.34] => (item=python-selinux,mysql-server,python-mysqldb) => {"changed": false, "item": "python-selinux,mysql-server,python-mysqldb"}

TASK: [mysql | Copy the my.cnf file] ****************************************** 
fatal: [192.241.139.34] => {'msg': "One or more undefined variables: 'str object' has no attribute 'name'", 'failed': True}
fatal: [192.241.139.34] => {'msg': "One or more undefined variables: 'str object' has no attribute 'name'", 'failed': True}

FATAL: all hosts have already failed -- aborting

One user multiple databases

Hi,
Wanted to ask how can I achieve this with your role.
If I have:

mysql_db:
     - name: foo
       replicate: yes
     - name: bar
       replicate: no
     - name: foobar
       replicate: no

mysql_users:
     - name: benz
       pass: foobar
       priv: "*.*:ALL"
     - name: marko
       pass: foobar
       priv: "foo.*:ALL"
     - name: marko
       pass: foobar
       priv: "bar.*:ALL"

User marko will be granted only last DB in the list. In this case bar.

Invalid privileges specified: frozenset(['\"REPLICATION SLAVE\"'])

This error is due to a typo in tasks/main.yml at line 80.
Proposed fix:
Change
priv=*.*:"REPLICATION SLAVE" state=present
to
priv="*.*:REPLICATION SLAVE" state=present

Sensitive default variables

Thank you for sharing this role, it's a real timesaver, though I have a suggestion about default variables.

If I add this role with default variables, it:

opens up the server for all incoming connections (mysql_bind_address: "0.0.0.0")
sets a password foobar to the root user
creates standard user and repl user, also with insecure password foobar

Though the related variables are mentioned in a basic usage in README, I was still surprised when the role created a repl user.

It is my understanding that the default variables should be used for sharing variables with included and dependent roles, but not as an example usage.

I would like you to consider a bit more sensitive default behaviour, for example:

bind to 127.0.0.1 by default
omit a default root password – the mysql_root_db_pass could be either mandatory, or related tasks would be skipped when the password is missing
do not create a database or users by default

The omitted variables should be either explained in documentation or commented out in defaults/main.yml as an example usage.

I'd be happy to send a PR if you agree with this proposal.

error with ansible 2.0

seeing this error:
ERROR! this task 'lineinfile' has extra params, which is only allowed in the following modules: command, shell, script, include, include_vars, add_host, group_by, set_fact, raw, meta

my proposed fix:

in tasks/main.yml replace the double quotes in the "Ensure the hostname entry for master is available for the client." task to single quotes in the address interpolation.

from:
lineinfile: dest=/etc/hosts regexp="{{ mysql_repl_master }}" line="{{ hostvars[mysql_repl_master].ansible_default_ipv4.address + " " + mysql_repl_master }}" state=present

to:
lineinfile: dest=/etc/hosts regexp="{{ mysql_repl_master }}" line="{{ hostvars[mysql_repl_master].ansible_default_ipv4.address + ' ' + mysql_repl_master }}" state=present

New to anisible

Hi ,

I wanted to use your repo to install mysql using ansible. Where do i start with after cloning repo? Do you have commands to run?

Error with ansible 2.0 stable

ERROR! The tasks/main.yml file for role 'mysql' must contain a list of tasks

The error appears to have been in 'roles/mysql/tasks/main.yml': line 2, column 1, but may
be elsewhere in the file depending on the exact syntax problem.

The offending line appears to be:

---
- name: Add the OS specific variables
^ here

One example for multiple grants ?

Here the command line I'm willing to do with ansible

mysql -u root -p -e "grant all on centreon.* to 'centreon'@localhost identified by 'centreon'; grant all on centreon_storage.* to 'centreon'@localhost identified by 'centreon'; grant all on centreon_status.* to 'centreon'@localhost identified by 'centreon';"

I've a meta/main.yml like this


---
dependencies:
  - { role: httpd }
  - { role: mysql,  mysql_root_db_pass: centreon, mysql_users: [{name: centreon,  pass: centreon, priv: "centreon.*:ALL"}] }
# Need centreon_storage et centreon_status

How would you do that? That could make one more example (or a tweak in a existing one) in the README.md

Create .my.cnf file before changing all root passwords

The mysql_user module requires the .my.cnf file to have the new root credentials stored before it can be used to update the passwords of the other root users. Otherwise the task fails with error msg: Using password: NO

The .my.cnf template task should be before the mysql_user task

From the mysql_user docs

Both login_password and login_user are required when you are passing credentials. If none are present, the module will attempt to read the credentials from ~/.my.cnf

Add selinux and iptable rules

Hello

This is merely a suggestion, add a couple of task that'll deal with selinux and iptables.
Here is the example: https://github.com/ansible/ansible-examples/blob/master/lamp_simple/roles/db/tasks/main.yml.

With you role's visibility in galaxy.ansibleworks, that would be a good inspiration for anyone using it.

Cheers,

Default behavior

I just tried to use your role as a requirement for another, without any parameters. Meaning, it's all about installing mysql, cleaning anonymous users, etc.

Then I saw this

TASK: [mysql | update mysql root password for all root accounts] **************
changed: [centos] => (item=centos)
ok: [centos] => (item=127.0.0.1)
ok: [centos] => (item=::1)
ok: [centos] => (item=localhost)

TASK: [mysql | update mysql root password for all root accounts] **************
skipping: [centos] => (item=127.0.0.1)
skipping: [centos] => (item=::1)
skipping: [centos] => (item=localhost)

TASK: [mysql | copy .my.cnf file with root password credentials] **************
ok: [centos]

TASK: [mysql | ensure anonymous users are not in the database] ****************
ok: [centos] => (item=localhost)
ok: [centos] => (item=centos)

TASK: [mysql | remove the test database] **************************************
ok: [centos]

TASK: [mysql | Create the databases] ******************************************
ok: [centos] => (item={'replicate': False, 'name': 'ansible'})

TASK: [mysql | Create the database users] *************************************
ok: [centos] => (item={'pass': 'ansible', 'name': 'ansible', 'priv': 'ansible.*:ALL'})

TASK: [mysql | Create the replication users] **********************************
ok: [centos] => (item={'name': 'ansible_repl', 'pass': 'ansible'})

TASK: [mysql | Check if slave is already configured for replication] **********
skipping: [centos]

TASK: [mysql | Ensure the hostname entry for master is available for the client.] ***
skipping: [centos]

TASK: [mysql | Get the current master servers replication status] *************
skipping: [centos]

TASK: [mysql | Change the master in slave to start the replication] ***********
skipping: [centos]

The thing that's bothering me is the default behavior, creating ansible database, users, replication user. I specified nothing, I expect it to create nothing.

But that's not such a big deal of course, if you've no time to deal with that.

miss create db in slave

MySQL Version: 5.5.38

I follow this configuration to create a new Replication environment, then loads data from an existing MySQL Server. Since slave didn't create databases, sync db from master to slave will be failed, told db not exist when create tables;

The solution is set replicate_do_db = dbname in slave

  - hosts: master
    roles:
     - {role: mysql, mysql_db: [{name: benz}, {name: benz2}],
                     mysql_users: [{name: ben3, pass: foobar, priv: "*.*:ALL"},
                                   {name: ben2, pass: foo}],
                     mysql_db_id: 8 

  - hosts: slave
    roles:
     - {role: mysql, mysql_db: none, mysql_users: none,
              mysql_repl_role: slave, mysql_repl_master: vm2,
              mysql_db_id: 9, mysql_repl_user: [{name: repl, pass: foobar}] }

Allow to specify collation/encoding for DBs

Subj. mysql_db offers that parameters, so the role can use them.