Category: Database

Database Tutorials and Tips

Setting up PostGresql Replication with Loadbalancing  with PGPOOL II

Setting up PostGresql Replication with Loadbalancing with PGPOOL II

Installation (on both Master and Slave Nodes): yum install https://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-3.noarch.rpm yum install postgresql95 postgresql95-server postgresql-libs postgresql95-contrib rsync   Initialize the database and configure postgresql /usr/pgsql-9.5/bin/postgresql95-setup initdb cd /var/lib/pgsql/9.5/data cp postgresql.conf postgresql.conf.orig chown postgres postgresql.conf.orig...

Running a query without affecting slave

Running a query without affecting slave

Some times, you might want to run a query without slave updating with that query. In that case, you would have to avoid writing the query to the binary log , to do that,...

MySQL root grant permission denied

MySQL root grant permission denied

There are times, when  you may come across this situation, when you are not able to grant permission to a new user, even when you are root. This seems to happen , either when...

Shell Script to Check the Status of MySQL Replication

Shell Script to Check the Status of MySQL Replication

#!/bin/bash emailAddress="emailtoNotify@domain.com" cmd=`mysql -e ‘show slave status\G’ | grep Slave_SQL_Running | awk ‘{print $2}’` if [ "$cmd" == "No" ];then         MSG="Mysql Replication Broken on `hostname`, please check asap"         echo $MSG | mail -s "Replication...

cannot start session without errors

cannot start session without errors

Web server is not able to access your session directory. Change the permissions. chown -R root:apache /var/lib/php/session

Mysqldump Tips and Tricks

Mysqldump Tips and Tricks

MySQL Database Backup Ignore one or more tables while doing mysqldump : mysqldump -u username -p database –ignore-table=db.table1 –ignore-table=db.table2 > mysqldump.sql Compress the output of mysqldump : mysqldump -u username -p database  | /bin/gzip...

mysql master master replication with ssl encryption

mysql master master replication with ssl encryption

Lets assume the two servers to be serverA and serverB Create the certificates to use with mysql replication: openssl genrsa 2048 > ca-key.pem openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem...

Sample mysql queries

Sample mysql queries

Delete Rows based on output from a query : DELETE FROM Table1 WHERE EXISTS(SELECT 1 FROM Table2 WHERE Table2.Group = Table1.Group) or delete from Table1 where id in (select id from table1 inner join...

Shell Script to Backup and Rotate Mysql Database

Shell Script to Backup and Rotate Mysql Database

#!/bin/bash #Author : Amit K Nepal #Last Modified : Nov 01 2011 BACKUP_DIR=/db_backups DB_NAME=dbname TSTAMP=`date +%Y%m%d` FILENAME=$BACKUP_DIR/$DB_NAME.gz Notify=1 Rotate=8 logfile=$BACKUP_DIR/backup.log echo “………….Backup Script Running on $TSTAMP…………” >> $logfile let i=$Rotate-1 if [ -f “$FILENAME.$Rotate”...