Topics

Database

Setup postgreSQL RDS using Ansible

Setting up PostgreSQL on RDS using ansible is a bit tricky because the main user on RDS is not a SUPERUSER and roles membership is not automatically granted for ex: “ERROR: must be member of role ..” is quite common. Here is a working solution:

Use mysqldump to create separate files and directories for databases and tables

The script creates separate directory for every database and bz2 files for every table inside that database.

#!/bin/sh

#edit these
USER=""
PASSWORD=""
MYSQLDIR="/path/to/backupdir"

MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"

echo - Dumping DB structure "$MYSQLDIR"/all.bz2 
$MYSQLDUMP --user=$USER --password=$PASSWORD -d --all-databases | bzip2 > "$MYSQLDIR"/all.bz2 

echo - Dumping tables for each DB
databases=`$MYSQL --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`
for db in $databases; do
    echo - Creating "$db" DB
	mkdir $MYSQLDIR/$db
	chmod -R 777 $MYSQLDIR/$db
	for tb in `$MYSQL  --user=$USER --password=$PASSWORD -N -B -e "use $db ;show tables"`
		do 
			echo -- Creating table $tb
			$MYSQLDUMP --opt  --delayed-insert --insert-ignore --user=$USER --password=$PASSWORD $db $tb | bzip2 -c > $MYSQLDIR/$db/$tb.sql.bz2
	done
	echo
done

Install Sphinx 2.0.4 on CentOS 6.2

– Make sure to check CentOS, PHP-FPM, Nginx, memcached and MYSQL posts.

– Get fresh RPM from sphinxsearch.com

– Install Sphinx

rpm -Uhv http://sphinxsearch.com/files/sphinx-2.0.4-1.rhel6.x86_64.rpm

– Create sample MySQL database