Topics

Create

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

Limit requests per IP on Nginx using HttpLimitZoneModule and HttpLimitReqModule except whitelist

– Make sure to check Nginx, PHP posts for information on Nginx and PHP setup and configuration.

Nginx offers two modules, HttpLimitReqModule and HttpLimitZoneModule, to limit simultaneous connections for the assigned session and the number of requests for a given session from one IP address. Basically these modules are built to protect the web server from possible DDos attacks; For example, this configuration limits remote clients to no more than 20 concurrently “open” connections per remote ip address:

http{
    limit_conn_zone  $binary_remote_addr zone=concurrent:10m;
    limit_conn_log_level warn;
    limit_conn  concurrent  20;

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

install memcached 1.4.13 on CentOS 6.2

– Make sure to check PHP-FPM and MYSQL posts.

– One of the dependencies of memcache is libevent, get latest version from libevent.org

mkdir libevent
cd libevent
wget https://github.com/downloads/libevent/libevent/libevent-2.0.17-stable.tar.gz
tar xfz libevent-2.0.17-stable.tar.gz
cd libevent-2.0.17-stable

– get latest version from memcached.org