You are here

Ansible Basics Continued. And example mysql dbserver role

We continue are ansible basics post. check out the previous post to get the full config..

Here is the git repo with all the files that we work with:

https://github.com/likid0/AnsibleSeries.git


We are going to continue and work a little on the db mysql role.

So we create the structure like allways:

[liquid@liquid-ibm:ansible/ansible]$ tree roles/dbserver                                                                                                                                 (10-15 21:25)
roles/dbserver
├── dump
│   └── dump.sql
├── handlers
│   └── main.yaml
├── tasks
│   └── main.yaml
├── templates
│   ├── my.cnf.j2
│   └── root.my.cnf
└── vars
    └── main.yaml

5 directories, 6 files

First lets go to our tasks file, we have some new things here that we will explain:

[liquid@liquid-ibm:ansible/ansible]$ cat roles/dbserver/tasks/main.yaml                                                                                                                  (10-15 21:25)
---
- name: Install Mysql package
  yum: name={{ item }} state=present
  with_items:
   - mariadb-server
   - MySQL-python

- name: Check if needed dirs for mariadb exist
  stat: path={{ mysql_log }}
  register: check_path

- name: Create logs dir for mariadb if it doesn't exist
  file: path={{ mysql_log }} state=directory owner=mysql
  when: check_path.stat.exists == false

- name: Check if needed dirs for mariadb exist
  stat: path={{ mysql_run }}
  register: check_path

- name: Create run dir for mariadb if it doesn't exist
  file: path={{ mysql_run }} state=directory owner=mysql
  when: check_path.stat.exists == false

- name: Check if needed dirs for mariadb exist
  stat: path={{ mysql_var }}
  register: check_path

- name: Create run var/lib for mariadb if it doesn't exist
  file: path={{ mysql_var }} state=directory owner=mysql
  when: check_path.stat.exists == false

- name: Configure Mysql template
  template: src=my.cnf.j2 dest=/etc/my.cnf
  notify: MySQL Restart

- name: Start Mysql and enable on boot
  service: name=mariadb enabled=yes state=started

- name: ensure anonymous users are not in the database
  mysql_user: name='' host={{ item }} state=absent
  with_items:
     - localhost
     - '{{ inventory_hostname }}'

- name: remove the test database
  mysql_db: name=test state=absent

- name: update mysql root password for all root accounts
  mysql_user: name=root host={{ item }} password={{ root_db_pass }}
  with_items:
    - '{{ ansible_hostname }}'
    - 127.0.0.1
    - ::1
    - localhost

- name: copy .my.cnf file with root password credentials
  template: src=root.my.cnf dest=/root/.my.cnf owner=root mode=0600

- name: open iptables 3306 port for mysql
  iptables: action=insert chain=INPUT source=0.0.0.0/0 ctstate=NEW state=present protocol=tcp match=tcp destination_port=3306 jump=ACCEPT

- name: Test that port 3306 is open, and the continue
  wait_for: port=3306 timeout=10

- name: Drop Application Database just in case it existed before
  mysql_db: name={{ db_test }} state=absent

- name: Create Application Database
  mysql_db: name={{ db_test }} state=present

- name: copy mysql test dump to userdb database
  copy: src=dump/dump.sql dest=/tmp

- name: import test dump to userdb database
  mysql_db: name={{ db_test }} state=import target=/tmp/dump.sql

- name: Create Application DB User
  mysql_user: name={{ dbuser }} password={{ dbupass }} priv={{ db_test }}.*:ALL host='%' state=present

- name: Query the DB to check the import worked ok
  command: mysql -u {{ dbuser }} -p{{ dbupass }} -e "SELECT * FROM test" {{ db_test }} 

- name: truncate the test table in userdb database
  command: mysql -u {{ dbuser }} -p{{ dbupass }} -e "drop TABLE test" {{ db_test }}


So what let's check out what we have, the mysql_user module, to create,delete,change permision for mysql users:

Here we are doing a little bit of hardening and we remove any anonymous users that exist.

- name: ensure anonymous users are not in the database
  mysql_user: name='' host={{ item }} state=absent
  with_items:
     - localhost
     - '{{ inventory_hostname }}'

We set the passwd for the mysql root user:

- name: update mysql root password for all root accounts
  mysql_user: name=root host={{ item }} password={{ root_db_pass }}
  with_items:
    - '{{ ansible_hostname }}'
    - 127.0.0.1
    - ::1
    - localhost

Using the mysql_db module, we remove the test database:

- name: remove the test database
  mysql_db: name=test state=absent

We copy a template file with the root mysql passwd

- name: copy .my.cnf file with root password credentials
  template: src=root.my.cnf dest=/root/.my.cnf owner=root mode=0600

We then use the wait_for module, this module waits for certain events to happen before it continues, here it checks if tcp port 3306 is open/responding:

- name: Test that port 3306 is open, and the continue
  wait_for: port=3306 timeout=10

This part is overkill,it tests if mysql is working oL with our user, just for practice, it create a database, imports a dump, does a select and then removes the db:

- name: Drop test Database just in case it existed before
  mysql_db: name={{ db_test }} state=absent

- name: Create test Database
  mysql_db: name={{ db_test }} state=present

- name: copy mysql test dump to userdb database
  copy: src=dump/dump.sql dest=/tmp

- name: import test dump to userdb test database
  mysql_db: name={{ db_test }} state=import target=/tmp/dump.sql

- name: Create Application DB User 
  mysql_user: name={{ dbuser }} password={{ dbupass }} priv={{ db_test }}.*:ALL host='%' state=present

- name: Query the test DB to check the import worked ok
  command: mysql -u {{ dbuser }} -p{{ dbupass }} -e "SELECT * FROM test" {{ db_test }}

- name: delete the test database
  mysql_db: name={{ db_test }} state=absent

Finally if all went well we create the application database and give the user ALL priviledges over the DB:

- name: Create Application Database
  mysql_db: name={{ db }} state=present

- name: Add access to dbuser to lamp database
  mysql_user: name={{ dbuser }} password={{ dbupass }} priv={{ db }}.*:ALL host='%' state=present

So that is our tasks file.

Handler:

[liquid@liquid-ibm:ansible/ansible]$ cat roles/dbserver/handlers/main.yaml                                                                                                               (10-15 22:45)
---
- name: MySQL Restart
  service: name=mariadb state=restarted

Here are the templates, we have the myslqd config template:

[liquid@liquid-ibm:ansible/ansible]$ cat roles/dbserver/templates/my.cnf.j2                                                                                                              (10-15 22:51)
[mysqld]
datadir={{ mysql_var }}
socket={{ mysql_var }}/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
port={{ mysql_port }}

[mysqld_safe]
log-error={{ mysql_log }}/mysqld.log
pid-file={{ mysql_run }}/mysqld.pid

And the credentials file for the mysql root user:

[liquid@liquid-ibm:ansible/ansible]$ cat roles/dbserver/templates/root.my.cnf                                                                                                            (10-15 22:51)
[client]
user=root
password={{ root_db_pass }}

[mysql]
user=root
password={{ root_db_pass }}

[mysqldump]
user=root
password={{ root_db_pass }}

[mysqldiff]
user=root
password={{ root_db_pass }}

Now to out vars file:

[liquid@liquid-ibm:ansible/ansible]$ cat roles/dbserver/vars/main.yaml                                                                                                                   (10-15 22:50)
mysql_port: '3306'
root_db_pass: 'rootpass'
db: 'lampdb'
db_test: 'testdb'
dbuser: 'lampuser'
dbupass: 'userpass'
mysql_log: '/var/log/mysql'
mysql_run: '/var/run/mysqld'
mysql_var: '/var/lib/mysql'

And finally our dumps dir, with our test db dump:

[liquid@liquid-ibm:ansible/ansible]$ cat roles/dbserver/dump/dump.sql                                                                                                                    (10-15 22:53)
CREATE TABLE IF NOT EXISTS `test` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `user` varchar(255) NOT NULL DEFAULT '',
  `pass` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`aid`, `user`, `pass`) VALUES
('1', 'test', 'test');

With this we have our DB role ready.
Unix Systems: 

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
By submitting this form, you accept the Mollom privacy policy.
Error | HP-UX Tips & Tricks Site

Error

Error message

  • Warning: Cannot modify header information - headers already sent by (output started at /homepages/37/d228974590/htdocs/includes/common.inc:2567) in drupal_send_headers() (line 1207 of /homepages/37/d228974590/htdocs/includes/bootstrap.inc).
  • PDOException: SQLSTATE[42000]: Syntax error or access violation: 1142 INSERT command denied to user 'dbo229817041'@'217.160.155.192' for table 'watchdog': INSERT INTO {watchdog} (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8, :db_insert_placeholder_9); Array ( [:db_insert_placeholder_0] => 0 [:db_insert_placeholder_1] => cron [:db_insert_placeholder_2] => Attempting to re-run cron while it is already running. [:db_insert_placeholder_3] => a:0:{} [:db_insert_placeholder_4] => 4 [:db_insert_placeholder_5] => [:db_insert_placeholder_6] => http://www.hpuxtips.es/?q=content/ansible-basics-continued-and-example-mysql-dbserver-role [:db_insert_placeholder_7] => [:db_insert_placeholder_8] => 54.198.71.184 [:db_insert_placeholder_9] => 1503243017 ) in dblog_watchdog() (line 157 of /homepages/37/d228974590/htdocs/modules/dblog/dblog.module).
The website encountered an unexpected error. Please try again later.