A Clone of the STRING_SPLIT MSSQL 2016 Function

I have recently been developing some stuff using MSSQL 2016 and used the STRING_SPLIT function. This doesn’t exist in earlier versions and I discovered I would be required to deploy to 2008 or 2012. So here’s a my own version of the STRING_SPLIT function I have developed and tested on MSSQL 2008 (may also work on 2005).

	@string VARCHAR(1024),
	@seperator CHAR(1)
		[Value] VARCHAR(1024)
	SELECT @x = CAST('<a>' + REPLACE(@string, @seperator, '</a><a>') + '</a>' AS XML);
	SELECT t.value('.', 'varchar(1024)') as inVal
	FROM @X.nodes('/A') AS x(t)

Usage is as follows;

FROM dbo.STRING_SPLIT_2008('mail1.com;mail2.com;mail3.com;mail4.com;mail5.com;mail6.com;mail7.com;mail8.com;mail9.com', ';')

This would return the following resultset;

string_split_2008 resultset

A simple MariaDB deployment with Ansible

Here’s a simple Ansible Playbook to create a basic MariaDB deployment.

The basic steps the playbook will attempt are:

  • Install a few libraries
  • Setup Repos
  • Install MariaDB packages
  • Install Percona software
  • Create MariaDB directories
  • Copy my.cnf to server (note this is a template file and not supplied here)
  • Run mysql_install_db if needed
  • Start MariaDB
  • Set root password
  • Delete anonymous users
  • Create myapp database and user

Note: some steps will only execute if a root password has not been set. These are identifiable by the following line:

when: is_root_password_set.rc == 0

This is the playbook in full:

- hosts: database
  become: true
    - name: Install Utility software
      apt: name={{item}} state=latest update_cache=yes
        - software-properties-common
        - python-mysqldb
    - name: Add apt key
      command: apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
    - name: Add MariaDB Repo
        filename: MariaDB-10.2
        repo: deb [arch=amd64,i386] http://mirror.rackspeed.de/mariadb.org/repo/10.2/ubuntu trusty main
        state: present
    - name: Get Key for Percona Repo
      command: apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
    - name: Add Percona Tools Repo
        filename: Percona
        repo: deb http://repo.percona.com/apt trusty main
        state: present
    - name: Install MariaDB Packages
      apt: name={{item}} state=installed default_release=trusty update_cache=yes
        - mariadb-client
        - mariadb-common
        - mariadb-server
    - name: Install Percona Software
      apt: name={{item}} state=latest force=yes
        - percona-toolkit
        - percona-xtrabackup
        - percona-nagios-plugins
    - name: Create MariaDB Directories
      file: path=/data/{{item}} state=directory owner=mysql group=mysql recurse=yes
        - db
        - log
    - name: Write new configuration file
        src: /home/vagrant/ansible/templates/mysql/my.cnf
        dest: /etc/mysql/my.cnf
        owner: mysql
        group: mysql
        mode: '0600'
        backup: yes
    - name: Count files in /data/db
      find: path=/data/db patterns='*'
      register: db_files
    - name: Run mysql_install_db only if /data/db is empty
      command: mysql_install_db --datadir=/data/db
      when: db_files.matched|int == 0
    - name: Start MariaDB
      service: name=mysql state=started
    - name: Is root password set?
      command: mysql -u root --execute "SELECT NOW()"
      register: is_root_password_set
      ignore_errors: yes
    - name: Delete anonymous users
      mysql_user: user="" state="absent"
      when: is_root_password_set.rc == 0
    - name: Generate mysql root password
      shell: tr -d -c "a-zA-Z0-9" < /dev/urandom | head -c 10
      register: mysql_root_password
      when: is_root_password_set.rc == 0
    - name: Set root password
      mysql_user: user=root password="{{mysql_root_password.stdout}}" host=localhost
      when: is_root_password_set.rc == 0
    - name: Set root password for other hosts
      mysql_user: user=root password="{{mysql_root_password.stdout}}" host="{{item}}" login_user="root" login_host="localhost" login_password="{{mysql_root_password.stdout}}"
      when: is_root_password_set.rc == 0
        - ""
        - "::1"
    - name: Inform user of mysql root password
        msg: "MariaDB root password was set to {{mysql_root_password.stdout}}"
      when: is_root_password_set.rc == 0
    - name: Create myapp database
        name: myapp
        login_user: root
        login_password: "{{mysql_root_password.stdout}}"
        login_host: localhost
        state: present
      when: is_root_password_set.rc == 0
    - name: Generate myapp_rw password
      shell: tr -d -c "a-zA-Z0-9" < /dev/urandom | head -c 10
      register: mysql_myapp_rw_password
      when: is_root_password_set.rc == 0
    - name: Create user for myapp db
        name: myapp_rw
        password: "{{mysql_myapp_rw_password}}"
        priv: myapp.*:SELECT,INSERT,UPDATE,DELETE
        login_user: root
        login_password: "{{mysql_root_password.stdout}}"
        state: present
      when: is_root_password_set.rc == 0
PLAY [database] *********************************************************************************************************************************************

TASK [Gathering Facts] **************************************************************************************************************************************
ok: [db01]

TASK [Install Utility software] *****************************************************************************************************************************
changed: [db01] => (item=[u'software-properties-common', u'python-mysqldb'])

TASK [Add apt key] ******************************************************************************************************************************************
changed: [db01]

TASK [Add MariaDB Repo] *************************************************************************************************************************************
changed: [db01]

TASK [Get Key for Percona Repo] *****************************************************************************************************************************
changed: [db01]

TASK [Add Percona Tools Repo] *******************************************************************************************************************************
changed: [db01]

TASK [Install MariaDB Packages] *****************************************************************************************************************************
changed: [db01] => (item=[u'mariadb-client', u'mariadb-common', u'mariadb-server'])

TASK [Install Percona Software] *****************************************************************************************************************************
changed: [db01] => (item=[u'percona-toolkit', u'percona-xtrabackup', u'percona-nagios-plugins'])

TASK [Create MariaDB Directories] ***************************************************************************************************************************
changed: [db01] => (item=db)
changed: [db01] => (item=log)

TASK [Write new configuration file] *************************************************************************************************************************
changed: [db01]

TASK [Count files in /data/db] ******************************************************************************************************************************
ok: [db01]

TASK [Run mysql_install_db only if /data/db is empty] *******************************************************************************************************
changed: [db01]

TASK [Start MariaDB] ****************************************************************************************************************************************
ok: [db01]

TASK [Is root password set?] ********************************************************************************************************************************
changed: [db01]

TASK [Delete anonymous users] *******************************************************************************************************************************
ok: [db01]

TASK [Generate mysql root password] *************************************************************************************************************************
changed: [db01]

TASK [Set root password] ************************************************************************************************************************************
changed: [db01]

TASK [Set root password for other hosts] ********************************************************************************************************************
changed: [db01] => (item=
changed: [db01] => (item=::1)

TASK [Inform user of mysql root password] *******************************************************************************************************************
ok: [db01] => {
    "changed": false,
    "msg": "MariaDB root password was set to zr2MuEXUBD"

TASK [Create myapp database] ********************************************************************************************************************************
changed: [db01]

TASK [Generate myapp_rw password] ***************************************************************************************************************************
changed: [db01]

TASK [Create user for myapp db] *****************************************************************************************************************************
changed: [db01]

PLAY RECAP **************************************************************************************************************************************************
db01                       : ok=22   changed=17   unreachable=0    failed=0

A dockerized mongod instance with authentication enabled

Here’s just a quick walkthrough showing how to create a dockerized instance of a standalone MongoDB instance.

First, from within a terminal, create a folder to hold the Dockerfile…

mkdir Docker_MongoDB_Image
cd Docker_MongoDB_Image
touch Dockerfile

Edit the Dockerfile…

vi Dockerfile

Enter the following text. You may wish to modify the file slightly. For example; if you need to set any of the proxy values or the MongoDB admin password.

FROM centos

MAINTAINER Rhys Campbell no_mail@no_mail.cc

RUN echo $'[mongodb-org-3.4] \n\
name=MongoDB Repository \n\
baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/3.4/x86_64/ \n\
gpgcheck=1 \n\
enabled=1 \n\
gpgkey=https://www.mongodb.org/static/pgp/server-3.4.asc ' > /etc/yum.repos.d/mongodb-org-3.4.repo

RUN yum clean all && yum install -y mongodb-org-server mongodb-org-shell mongodb-org-tools
RUN mkdir -p /data/db && chown -R mongod:mongod /data/db
RUN /usr/bin/mongod -f /etc/mongod.conf && sleep 5 && mongo admin --eval "db.createUser({user:\"admin\",pwd:\"secret\",roles:[{role:\"root\",db:\"admin\"}]}); db.shutdownServer()"
RUN echo $'security: \n\
  authorization: enabled \n ' >> /etc/mongod.conf
RUN sed -i 's/^  bindIp: 127\.0\.0\.1/  bindIp: \[127\.0\.0\.1,0\.0\.0\.0\]/' /etc/mongod.conf
RUN sed -i 's/^  fork: true/  fork: false/' /etc/mongod.conf
RUN chown mongod:mongod /etc/mongod.conf
RUN cat /etc/mongod.conf

EXPOSE 27017

ENTRYPOINT /usr/bin/mongod -f /etc/mongod.conf

Build the image from within the current dirfectory…

docker build -t mongod-instance . --no-cache

Run the image and map to the 27017 port…

docker run  -p 27017:27017 --name mongod-instance -t mongod-instance

Inspect the mapped port with…

docker ps

The output should look something like this…

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                      NAMES
5e7e4a069f4a        mongod-instance     "/bin/sh -c '/usr/..."   2 hours ago         Up 2 hours>27017/tcp   mongod-instance

We can view the docker ip address with this command…

docker-machine ls

Output looks like this…

NAME      ACTIVE   DRIVER       STATE     URL                         SWARM   DOCKER        ERRORS
default   *        virtualbox   Running   tcp://           v17.05.0-ce

You can connect to the dockerized mongod instance with this command…

mongo admin -u admin -p --port 27017 --host

When you are done with the instance it can be destroyed with…

docker stop mongod-instance
docker rm mongod-instance

Update: I’ve added this to my Docker Hub account so you can grab the image directly from there.

Check MariaDB replication status inside Ansible

I needed a method to check replication status inside Ansible. The method I came up with uses the shell module

- hosts: mariadb
      - name: "mariadb_user"
        prompt: "Enter MariaDB user"
      - name: "mariadb_password"
        prompt: "Enter MariaDB user password"
    - name: "Check MariaDB replication state"
      shell: "test 2 -eq $(mysql -u '{{ mariadb_user }}' -p'{{ mariadb_password }}' -e 'SHOW SLAVE STATUS' --auto-vertical-output | grep -E 'Slave_IO_Running|Slave_SQL_Running' | grep Yes | wc -l)"
      register: replication_status
    - name: "Print replication status var"
        var: replication_status

This is executed like so…

ansible-playbook mariadb_check_repl.yml -i inventories/my_mariadb_hosts

The playbook will prompt for a MariaDB user and password and will output something like below…

PLAY [mariadb] *****************************************************************

TASK [setup] *******************************************************************
ok: [slave2]
ok: [master2]
ok: [slave1]
ok: [master1]

TASK [Check MariaDB replication state] *****************************************
changed: [master1]
changed: [master2]
changed: [slave2]
changed: [slave1]

TASK [Print replication status var] ********************************************
ok: [master1] => {
    "replication_status": {
        "changed": true,
        "cmd": "test 2 -eq $(mysql -u 'mariadb_user' -p'secret' -e 'SHOW SLAVE STATUS' --auto-vertical-output | grep -E 'Slave_IO_Running|Slave_SQL_Running' | grep Yes | wc -l)",
        "delta": "0:00:00.009477",
        "end": "2017-06-02 16:52:51.293609",
        "rc": 0,
        "start": "2017-06-02 16:52:51.284132",
        "stderr": "",
        "stdout": "",
        "stdout_lines": [],
        "warnings": []
ok: [slave1] => {
    "replication_status": {
        "changed": true,
        "cmd": "test 2 -eq $(mysql -u 'mariadb_user' -p'secret' -e 'SHOW SLAVE STATUS' --auto-vertical-output | grep -E 'Slave_IO_Running|Slave_SQL_Running' | grep Yes | wc -l)",
        "delta": "0:00:00.017658",
        "end": "2017-06-02 16:52:51.325027",
        "rc": 0,
        "start": "2017-06-02 16:52:51.307369",
        "stderr": "",
        "stdout": "",
        "stdout_lines": [],
        "warnings": []
ok: [master2] => {
    "replication_status": {
        "changed": true,
        "cmd": "test 2 -eq $(mysql -u 'mariadb_user' -p'secret' -e 'SHOW SLAVE STATUS' --auto-vertical-output | grep -E 'Slave_IO_Running|Slave_SQL_Running' | grep Yes | wc -l)",
        "delta": "0:00:00.015469",
        "end": "2017-06-02 16:52:51.292966",
        "rc": 0,
        "start": "2017-06-02 16:52:51.277497",
        "stderr": "",
        "stdout": "",
        "stdout_lines": [],
        "warnings": []
ok: [slave2] => {
    "replication_status": {
        "changed": true,
        "cmd": "test 2 -eq $(mysql -u 'mariadb_user' -p'secret' -e 'SHOW SLAVE STATUS' --auto-vertical-output | grep -E 'Slave_IO_Running|Slave_SQL_Running' | grep Yes | wc -l)",
        "delta": "0:00:00.014586",
        "end": "2017-06-02 16:52:51.291047",
        "rc": 0,
        "start": "2017-06-02 16:52:51.276461",
        "stderr": "",
        "stdout": "",
        "stdout_lines": [],
        "warnings": []

PLAY RECAP *********************************************************************
master1   : ok=3    changed=1    unreachable=0    failed=0
master2   : ok=3    changed=1    unreachable=0    failed=0
slave1   : ok=3    changed=1    unreachable=0    failed=0
slave2   : ok=3    changed=1    unreachable=0    failed=0

N.B. There is the mysql_replication Ansible module that some may prefer to use but it requires the MySQLdb Python package to be present on the remote host.

my: a command-line tool for MariaDB Clusters

I’ve posted the code for my MariaDB Cluster command-line tool called my. It does a bunch of stuff but the main purpose is to allow you to easily monitor replication cluster-wide while working in the shell.

Here’s an example of this screen…

hostname port  cons  u_cons  role  repl_detail                                       lag  gtid    read_only
master1  3306  7     0       ms    master2.ucid.local:3306 mysql-bin.000046 7296621  0    0-2-4715491  OFF
master2  3306  33    20      ms    master1.ucid.local:3306 mysql-bin.000052 1031424  0    0-2-4715491  OFF
slave1   3306  5     0       ms    master1.ucid.local:3306 mysql-bin.000052 1031424  0    0-2-4715491  ON
slave2   3306  29    19      ms    master2.ucid.local:3306 mysql-bin.000046 7296621  0    0-2-4715491  ON
backup   3306  5     0       ms    master2.ucid.local:3306 mysql-bin.000046 7296621  0    0-2-4715491  ON

This screen will handle hosts that are down, identify ones where MariaDB isn’t running, highlight replication lag or errors, as well as multi-master setups. See the README for more details for how to get started.