Category: MySQL

All Tips Tricks,Solutions and Tutorials about MySQL Database, Backup,Restore,Recovery and Replication.

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...

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”...

Finding and optimizing fragmented tables in mysql

Finding and optimizing fragmented tables in mysql

With increasing data, mysql tables can get defragmented and it is a good idea to optimize tables every once in a while. Getting the list of all tables that need optimization : mysql -p...

Optimize MySQL From my-large.cnf

Optimize MySQL From my-large.cnf

1. Copy the my-large.cnf which usually comes with mysql-server , usually inside the /usr/share/mysql  directory Note: you might want to backup your existing my.cnf before doing this. cp -p /usr/share/mysql/my-large.cnf /etc/my.cnf Note: All values...