Setting up Ansible for MySQL

In this how to we’re going to manage in total 16 different LXC nodes on two different host machines. One of the two host machines will have the “master” nodes in the MySQL cluster, the other host machine will run the slave nodes we will replicate to from the master nodes.

Since Ansible is based on SSH the below implies that you have already got the public key of the hosts already added to the nodes.

Put the following in /etc/ansible/hosts on the master host if you already have node1-8 in your hosts file on the master host:

[dbs]
node1
node2
node3
node4
node5
node6
node7
node8

Put this in loop_nodes.sh and run it:

for i in {1..8}
do
  ssh root@node$i 'apt-get -y install python python-apt'
done

Put this in setup-mysql.yml:

---
- hosts: dbs  
  remote_user: root
  tasks:
  - name: Install pip
    apt: pkg={{ item }} state=present update_cache=yes cache_valid_time=604800
    with_items:
      - mariadb-server
      - python-pip
      - libmariadbclient-dev
      - python-dev
      - libssl-dev
  - name: Install Python mysql module
  pip: name=MySQL-python

Execute: ansible-playbook setup-mysql.yml -f 4

The -f 4 will execute 4 in parallel, doing all 8 at the same time might be a bit too hard on the networking.

Then put this in ini-mysql-setup.yml:

---
- hosts: dbs  
  remote_user: root
  tasks:
  - name: ensure mysql is not running
    service:
      name: mysql
      state: stopped
  - name: remove mysql log file 0
    command: rm /var/lib/mysql/ib_logfile0 removes=/var/lib/mysql/ib_logfile0
  - name: remove mysql log file 1
    command: rm /var/lib/mysql/ib_logfile1 removes=/var/lib/mysql/ib_logfile1
  - name: write bashrc file
    template: src=bashrc.j2 dest=/root/.bashrc  
  - name: write the mysql config file
    template: src=my.cnf.j2 dest=/etc/mysql/my.cnf
  - name: start mysql
    service:
      name: mysql
      state: started

On the host running the slaves

Redo all of the above on the host running the slave nodes and make sure that the server-id is different in the my.cnf!

Then put this in the /etc/ansible/hosts file:

[dbs]
lxc1 master_ip=10.40.10.65
lxc2 master_ip=10.40.10.66
lxc3 master_ip=10.40.10.67
lxc4 master_ip=10.40.10.68
lxc5 master_ip=10.40.10.69
lxc6 master_ip=10.40.10.70
lxc7 master_ip=10.40.10.71
lxc8 master_ip=10.40.10.72

Put this in ini-replication.sh.j2

MYSQL_MASTER_HOST={{ master_ip }}
MYSQL_SLAVE_HOST=localhost
MYSQL_USER=slavemanager
MYSQL_PASS=1234
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
MYSQL_MASTER_CONN="-h${MYSQL_MASTER_HOST} ${MYSQL_CONN}"
MYSQL_SLAVE_CONN="-h${MYSQL_SLAVE_HOST} ${MYSQL_CONN}"
MYSQLDUMP_OPTIONS="videoslots --master-data=1"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --single-transaction"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --skip-tz-utc"
RELOAD_FILE=/root/MySQLData.sql
echo "STOP SLAVE;" > ${RELOAD_FILE}
echo "CHANGE MASTER TO master_host='${MYSQL_MASTER_HOST}'," >> ${RELOAD_FILE}
echo "master_port=3306," >> ${RELOAD_FILE}
echo "master_user='${MYSQL_USER}'," >> ${RELOAD_FILE}
echo "master_password='${MYSQL_PASS}'," >> ${RELOAD_FILE}
echo "master_log_file='dummy-file'," >> ${RELOAD_FILE}
echo "master_log_pos=1;" >> ${RELOAD_FILE}
mysqldump ${MYSQL_MASTER_CONN} ${MYSQLDUMP_OPTIONS} >> ${RELOAD_FILE}
echo "START SLAVE;" >> ${RELOAD_FILE}
mysql ${MYSQL_SLAVE_CONN} --default_character_set utf8 videoslots < ${RELOAD_FILE}

And run this to execute the above on all slave nodes to start the replication:

---
- hosts: dbs  
  remote_user: root
  tasks:
  - name: write the script
    template: src=ini-replication.sh.j2 dest=/root/ini-replication.sh mode=0777
  - name: executing the replication start script
    command: sh /root/ini-replication.sh

Related Posts

Tags: , , ,