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'
donePut 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-pythonExecute: 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: startedOn 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="db_name --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 db_name < ${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