Shantanu's Blog

Database Consultant

November 29, 2010

 

Script extract to warn user

There are times when you want to make sure that the user has verified certain parameters. Here is an extract that can be placed at the top of any script.

# cat mycheck.sh
#!/bin/sh
# Warn and force user to check certain settings before starting the execution of script
echo "
********

Make sure the following and continue :

1. There is enough disk space available
2. general and binary logging is disabled in my.cnf
3. you have taken the required permissions from your boss, parents and girl friend

********

Press 'y' to continue 'n' to abort "
read answer
if [ "$answer" = "n" ] || [ "$answer" = "N" ]
then
echo "Aborting"
exit 160
fi

echo "welcome and let us start the process"

Labels:


 

MySQL alert system

#!/bin/sh
# filename myalert.sh
# a cron that will record the mysql status to a text file every minute
# record status of both masters
# * * * * * mysqladmin -h111.222.333.101 -uroot -pPassWord status >> /home/ricie/status_success_101.txt 2>> /home/develop/status_err_101.txt
# * * * * * sh -xv /home/develop/myalert.sh 101 1> /home/develop/alert_succ_101.txt 2> /home/develop/alert_err.txt

# analyse the change in the status figures and prompt the user when MySQL is restarted, receives less than 3 queries or more than 10 slow queries
# myalert email

if [ -z "$1" ]
then
filename='/home/develop/status_success.txt'
else
filename="/home/develop/status_success_$1.txt"
fi

path='/home/shantanu'
User='root'
PassWord='root'
mydate=`date '+%d %B %H:%M'`
myhostname=`hostname`
ADMIN="shantanu.oak+$myhostname@gmail.com"

uptime=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -1)

threads=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -2 | tail -1)

questions=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -3 | tail -1)

slow=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -4 | tail -1)

opens=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -5 | tail -1 )

flush=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -6 | tail -1 )

otables=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -7 | tail -1)

aqueries=$(tail -2 $filename | awk ' {
gsub(/[a-zA-Z: ]+/," ")
m=split($0,a," ");
for (i=1;i<=m;i++)
if (NR==1) b[i]=a[i]; else print a[i] - b[i]
} ' | head -8 | tail -1)

echo "differnce in uptime is " $uptime
echo "differnce in Threads are " $threads
echo "difference in Questions are " $questions
echo "difference in Slow queries are " $slow
echo "difference in Opens " $opens
echo "difference in Flush tables " $flush
echo "difference in Open tables " $otables
echo "difference in Queries per second " $aqueries


if [[ $uptime -lt 0 ]];then

# the message that will be written to a file, mail and SMS
# the word space is black listed by SMS gateway
mymessage="MySQL restarted on $1 at $mydate"

# write to a file, email and SMS
echo "$mymessage" >> $path/messages.txt 2>> $path/messages_err.txt
echo "$mymessage" | mail -s "mysql restarted on $1" $ADMIN

# sms alert add as many numbers as you want to the list
while read mnumber
do
# one of the gateway not working
# curl -Ld'username=soak&password=43417010&source=oksoft&dmobile='$mnumber'&message='"'$mymessage'"'' http://67.23.229.95/smsclient//api.php
curl -Ld'user=shantanu.oak@gmail.com:PassWord&state=4&senderID=TEST SMS&receipientno='$mnumber'&msgtxt='"'$mymessage'"'' http://api.mVaayoo.com/mvaayooapi/MessageCompose
done << mnumber_list
09702977470
mnumber_list

fi



if [[ $questions -lt 3 ]];then

# the message that will be written to a file, mail and SMS
# the word space is black listed by SMS gateway
mymessage="Queries $questions on $1 too few at $mydate"

# write to a file, email and SMS
echo "$mymessage" >> $path/messages.txt 2>> $path/messages_err.txt
echo "$mymessage" | mail -s "Few questions on $1" $ADMIN

# sms alert add as many numbers as you want to the list
while read mnumber
do
# one of the gateway not working
# curl -Ld'username=soak&password=43417010&source=oksoft&dmobile='$mnumber'&message='"'$mymessage'"'' http://67.23.229.95/smsclient//api.php
#curl -Ld'user=shantanu.oak@gmail.com:PassWord&state=4&senderID=TEST SMS&receipientno='$mnumber'&msgtxt='"'$mymessage'"'' http://api.mVaayoo.com/mvaayooapi/MessageCompose
echo "dummy text"
done << mnumber_list
09702977470
mnumber_list

fi



if [[ $slow -gt 10 ]];then

# the message that will be written to a file, mail and SMS
# the word space is black listed by SMS gateway
mymessage="Slow queries $slow on $1 are inordinately high as on $mydate"

# write to a file, email and SMS
echo "$mymessage" >> $path/messages.txt 2>> $path/messages_err.txt
echo "$mymessage" | mail -s "slow queries high on $1" $ADMIN

# sms alert add as many numbers as you want to the list
while read mnumber
do
# one of the gateway not working
# curl -Ld'username=soak&password=43417010&source=oksoft&dmobile='$mnumber'&message='"'$mymessage'"'' http://67.23.229.95/smsclient//api.php
#curl -Ld'user=shantanu.oak@gmail.com:PassWord&state=4&senderID=TEST SMS&receipientno='$mnumber'&msgtxt='"'$mymessage'"'' http://api.mVaayoo.com/mvaayooapi/MessageCompose
echo "dummy text"
done << mnumber_list
09702977470
mnumber_list

fi



# count the processes and alert the user if the processes are more than 230
myvar2=$(mysqladmin -u$User -p$PassWord processlist | wc -l | awk '{print int($1)}')
if [[ $myvar2 -gt 230 ]];then
mymessage="processes $myvar2 on $1 are inordinately high as on $mydate"

echo "$mymessage" >> $path/messages.txt 2>> $path/messages_err.txt
echo "$mymessage" | mail -s "slow queries high on $1" $ADMIN

# sms alert add as many numbers as you want to the list
while read mnumber
do
# one of the gateway not working
# curl -Ld'username=soak&password=43417010&source=oksoft&dmobile='$mnumber'&message='"'$mymessage'"'' http://67.23.229.95/smsclient//api.php
#curl -Ld'user=shantanu.oak@gmail.com:PassWord&state=4&senderID=TEST SMS&receipientno='$mnumber'&msgtxt='"'$mymessage'"'' http://api.mVaayoo.com/mvaayooapi/MessageCompose
echo "dummy text"
done << mnumber_list
09702977470
mnumber_list

fi

Labels:


November 24, 2010

 

common my.cnf file

[mysqld]
##### make sure /data/ /mnt/ /var/lib/mysql and /var/run/mysqld exist with proper permissions and disk space
## data dir should point to the partition where there is enough free disk space
datadir=/data/jul20
## always use default socket
socket=/var/lib/mysql/mysql.sock
## always start the mysql service as root user
user=root
## unique server id can be generated using IP address or timestamp '+%y%m%d%H%M%S' or ‘+%s’
server-id=192168413
## tmpdir should have a lot of free disk space
tmpdir=/mnt/
## max allowed packet should be big enough
max_allowed_packet=512M
## For partitioned tables
open_files_limit=80000
## to allow more simultaneous threads
max_connections=1024
## max connections allowed before mysql stops accepting requests suspecting DOS attack
max_connect_errors= 4294967295

## tuning
## better performance – use ip addresses when connecting
# skip-name-resolve
## slave as well as master if php application using super privilege
# read-only
### Troubleshooting ###
## if password is lost or to solve access issues
#init_file=sql_commands.sql
#skip-grant-tables
#skip-networking
## change to strict sql mode or use traditional more more constraints
# sql-mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE"
#sql-mode=NO_ENGINE_SUBSTITUTION
## values 1 to 6 with varying severity
#innodb_force_recovery=3


## logging
## slow qury log
#slow_query_log=1
#slow_query_log_file=/tmp/slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#log-slow-slave-statements
## binary logging with distinct file name. Ensure enough disk space
#log-bin=/mnt/mis-bin-log
#log_slave_updates
#binlog_format=mixed
#expire_logs_days=7
# max_binlog_size=1024M
# log_bin_trust_function_creators=TRUE
#relay-log=/var/run/mysqld/mis-relay-log  
## default of binlog cache is 32K
##binlog_cache_size=4M
# General log can be enabled whenever required
# log=/tmp/general.log # for 5.0
# general_log=1 # for 5.1
# general_log_file=/var/log/mysql/general.log

## innodb
innodb_buffer_pool_size=256M
innodb_log_file_size=64M
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
#innodb_log_buffer_size = 16M
#innodb_flush_log_at_trx_commit=2
#innodb_lock_wait_timeout = 50
#innodb_additional_mem_pool_size=16M
# cache tables while using file per table
# innodb_open_files=1024
# number of cpus * number of disks * 2
# innodb_thread_concurrency=16
## myisam # du -sch /var/lib/mysql/*/*.MYI
#key_buffer_size=512M

### performance
## full text and grouping
#group_concat_max_len=500000
#ft_min_word_len=2
## query cache
#query_cache_type=2
#query_cache_size=64M
#query_cache_limit=1M
#query_cache_min_res_unit=2097152

## join queries use this buffer #default for read buffer is .1 M
#read_buffer_size=1M
## default for rnd buffer is .2M
#read_rnd_buffer_size=1M
## default for sort buffer is 2M
#sort_buffer_size=2M
#join_buffer_size=2M
## default bulk insert is 8M
# bulk_insert_buffer_size=16M
##default table cache is 64
#table_cache=256
## default thread cache size is 0
#thread_cache_size=16
## cat /proc/sys/net/core/somaxconn
# back_log=128
## default interactive_timeout 28800 seconds
#interactive_timeout=600
##default wait timeout 28800
#wait_timeout=600
#connect_timeout=15
## default is 0 for max user connections that means no limit
#max_user_connections=800
## temporary tables optimization
#tmp_table_size=256M
#max_heap_table_size=256M
## sort, alter and repair functions uses this buffer
#myisam_sort_buffer_size=64M
#myisam_max_sort_file_size=64M

### replication
#skip-slave-start
#replicate-do-db=vserv
#replicate-ignore-db=vserv_raw
#replicate-ignore-table=vserv.ox_session
#replicate-wild-ignore-table=vserv_raw.%
#slave-skip-errors=1062,1032,all
#slave-skip-errors=all
#slave_net_timeout=60

### master-maser replication
#auto_increment_increment=2
#auto_increment_offset=1

[mysqld_multi]
mysqld = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user = multi_admin
password = multipass

[mysqld2]
socket = /tmp/mysql.sock2
port = 3307
pid-file = /ext-hdd4/data/mysql2/hostname.pid2
datadir = /ext-hdd4/data/mysql2

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Labels:


November 23, 2010

 

ACID compliant engine

As all of us know that MyISAM is not ACID compliant as is InnoDB. In the following example, the first record is updated while the second one fails due to duplicate key error. This will not happen if you specify the engine as InnoDB.

mysql> create table test (a INT UNIQUE, b INT );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into test VALUES (NULL, 2);
Query OK, 1 row affected (0.03 sec)

mysql> insert into test VALUES (NULL, 2);
Query OK, 1 row affected (0.00 sec)

mysql> update test set a = 1 where b = 2;
ERROR 1062 (23000): Duplicate entry '1' for key 'a'
mysql> select * from test;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| NULL | 2 |
+------+------+
2 rows in set (0.00 sec)

Labels:


November 17, 2010

 

Unix Case Study 26

Left outer join SQL like query from shell

basically i have two files:

frequency.txt: (multiple lines, space separated file containing words and a frequency)

de 1711
a 936
et 762
la 530
les 482
pour 439
le 425
...

and i have a file containing "prohibited" words:

stopwords.txt: (one single line, space separated file)

au aux avec le ces dans ...

so i want to delete from frequency.txt all the lines containing a word found on stopwords.txt

how could i do that? i'm thinking that it could be done with awk.. something like

awk 'match($0,SOMETHING_MAGICAL_HERE) == 0 {print $0}' frequency.txt > new.txt

but i'm not really sure... any ideas?? thxs in advance


http://stackoverflow.com/questions/3978626/shell-to-filter-prohibited-words-on-a-file

Labels:


November 15, 2010

 

Creating a filler table

If I need to create a table with a million auto generated id's then I can use the filler table as shown below.

CREATE TABLE filler (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT) ENGINE=Memory;

DELIMITER $$

CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
DECLARE _cnt INT;
SET _cnt = 1;
WHILE _cnt <= cnt DO
INSERT
INTO filler
SELECT _cnt;
SET _cnt = _cnt + 1;
END WHILE;
END
$$

DELIMITER ;

CALL prc_filler(500000);

Labels:


 

MySQL strict mode

If you want mysql to care for the data-integrity then you need to add the following line to the my.cnf file.

sql-mode="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE"

NO_ENGINE_SUBSTITUTION
If innodb engine is not available, the create table statement will throw a warning and create a MyISAM table. This mode will avoid this and throw an error instead of using default engine.

STRICT_TRANS_TABLES
Enable strict mode for InnoDB. Instead of truncating 20 characters to fit into varchar(10), it will throw an error. If you want MyISAM tables to behave the same way, use STRICT_ALL_TABLES

NO_ZERO_IN_DATE
Do not accept all zeros in the date or month field. (for example, '0000-00-00' is legal but '2010-00-01' and '2010-01-00' are not). If you do not want zero dates like 0000-00-00 then you need to use NO_ZERO_DATE

The following is the default behavior of MySQL

create table sqlmode(id varchar(2), birth_date date);
insert into sqlmode values ('abc', '1970-00-16');
insert into sqlmode values ('ab', '1970-00-16');
insert into sqlmode values ('abc', '1970-11-16');

mysql> select * from sqlmode;
+------+------------+
| id | birth_date |
+------+------------+
| ab | 1970-00-16 |
| ab | 1970-00-16 |
| ab | 1970-11-16 |
+------+------------+
3 rows in set (0.00 sec)

When the sql-mode is turned on, all the above insert statements will fail. It will allow the record to enter only when the varchar and date columns are valid.

mysql> insert into sqlmode values ('abc', '1970-00-16');
ERROR 1406 (22001): Data too long for column 'id' at row 1

mysql> insert into sqlmode values ('ab', '1970-00-16');
ERROR 1292 (22007): Incorrect date value: '1970-00-16' for column 'birth_date' at row 1

mysql> insert into sqlmode values ('abc', '1970-11-16');
ERROR 1406 (22001): Data too long for column 'id' at row 1

mysql> insert into sqlmode values ('ab', '1970-11-16');
Query OK, 1 row affected (0.00 sec)

mysql> select * from sqlmode;
+------+------------+
| id | birth_date |
+------+------------+
| ab | 1970-11-16 |
+------+------------+
1 row in set (0.00 sec)

Labels:


November 10, 2010

 

Upgrading to 5.1 version from 5.0

1) Download Server from...
http://mysql.com/downloads/mirror.php?id=395761

MySQL-server-community-5.1.52-1.rhel5.x86_64.rpm

Download client from ...
http://mysql.com/downloads/mirror.php?id=395683

MySQL-client-community-5.1.52-1.rhel5.x86_64.rpm

Stop current MySQL deamon before installing the new version.

2) I have to uninstall 5.0 before installing 5.1 or else I get an error as shown below:

# rpm -ivh MySQL-client-community-5.1.52-1.rhel5.x86_64.rpm MySQL-server-community-5.1.52-1.rhel5.x86_64.rpm
error: Failed dependencies:
MySQL conflicts with mysql-5.0.77-3.el5.x86_64
MySQL-server conflicts with mysql-server-5.0.77-3.el5.x86_64


3) While uninstalling 5.0 I was informed that the dependencies too needs to be removed.

# rpm -e mysql-5.0.77-3.el5
error: Failed dependencies:
libmysqlclient.so.15()(64bit) is needed by (installed) perl-DBD-mysql-4.014-1.el5.rf.x86_64
libmysqlclient.so.15(libmysqlclient_15)(64bit) is needed by (installed) perl-DBD-mysql-4.014-1.el5.rf.x86_64
mysql is needed by (installed) perl-DBD-mysql-4.014-1.el5.rf.x86_64

4) SO I remove everything that is getting in my way.

# rpm -e mysql-5.0.77-3.el5 perl-DBD-mysql-4.014-1.el5.rf.x86_64 mysql-mmm-agent-2.2.1-1.el5.noarch

5) And now I am all set to install the shiny new MySQL version

# time rpm -ivh MySQL-client-community-5.1.52-1.rhel5.x86_64.rpm MySQL-server-community-5.1.52-1.rhel5.x86_64.rpm

6) Make sure it is installed
# rpm -qa | grep -i mysql
MySQL-server-community-5.1.52-1.rhel5
mysql-mmm-2.2.1-1.el5
MySQL-client-community-5.1.52-1.rhel5

7) Use the mysql_upgrade command to update the user tables. If your mysql.proc table gets corrupted due to any reason (for e.g. while trying to install 5.0 dump), use the following:

mysql_upgrade -f

This will forcefully fix the privilege tables. The 5.0 dump can be imported using the same -f (force) option as well. For e.g.

mysql -uroot -proot@123 -f < /home/today.sql

_____

Installing 5.0 version of mysql is easy.

# check what is installed
# rpm -qa | grep mysql-*
mysql-5.0.77-4.el5_4.2
mysql-5.0.77-4.el5_4.2
mod_auth_mysql-3.0.0-3.2.el5_3

# remove any conflicting version of mysql
# rpm -e mysql-5.0.77-4.el5_5.3.x86_64 --nodeps

# install using yum
# yum install mysql-*

Setting up Install Process
Package mysql-5.0.77-4.el5_4.2.x86_64 already installed and latest version
Package mysql-5.0.77-4.el5_4.2.i386 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package mysql-bench.x86_64 0:5.0.77-4.el5_4.2 set to be updated
=
Install 16 Package(s)
Update 5 Package(s)
Remove 0 Package(s)

Total size: 27 M
Is this ok [y/N]: y
Downloading Packages:
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Updating : krb5-libs 1/26
Installing : perl-DBD-MySQL 2/26
Installing : mysql-server 3/26
Updating : libsepol 4/26
Cleanup : libsepol 24/26
Cleanup : krb5-workstation 25/26
Cleanup : krb5-libs 26/26

Installed:
mysql-bench.x86_64 0:5.0.77-4.el5_4.2 mysql-connector-odbc.x86_64 0:3.51.26r1127-1.el5 mysql-devel.i386 0:5.0.77-4.el5_4.2 mysql-devel.x86_64 0:5.0.77-4.el5_4.2
mysql-server.x86_64 0:5.0.77-4.el5_4.2 mysql-test.x86_64 0:5.0.77-4.el5_4.2

Dependency Installed:
e2fsprogs-devel.x86_64 0:1.39-23.el5 keyutils-libs-devel.x86_64 0:1.2-1.el5 krb5-devel.x86_64 0:1.6.1-36.el5_4.1 libselinux-devel.x86_64 0:1.33.4-5.5.el5
libsepol-devel.x86_64 0:1.15.2-3.el5 libtool-ltdl.x86_64 0:1.5.22-7.el5_4 openssl-devel.x86_64 0:0.9.8e-12.el5_4.6 perl-DBD-MySQL.x86_64 0:3.0007-2.el5
unixODBC.x86_64 0:2.2.11-7.1 zlib-devel.x86_64 0:1.2.3-3

Dependency Updated:
krb5-libs.i386 0:1.6.1-36.el5_4.1 krb5-libs.x86_64 0:1.6.1-36.el5_4.1 krb5-workstation.x86_64 0:1.6.1-36.el5_4.1 libsepol.i386 0:1.15.2-3.el5
libsepol.x86_64 0:1.15.2-3.el5

Complete!


# start mysql
# /etc/init.d/mysqld start
Initializing MySQL database: Installing MySQL system tables...
OK
Filling help tables...
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h vishnuvm7.hyd.wc password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
[ OK ]
Starting MySQL: [ OK ]
[root@vishnuvm7 shantanu]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)



# rpm -qa | grep -i mysql-*
mysql-5.0.77-4.el5_4.2
mysql-server-5.0.77-4.el5_4.2
mysql-test-5.0.77-4.el5_4.2
mysql-bench-5.0.77-4.el5_4.2
mysql-5.0.77-4.el5_4.2
perl-DBD-MySQL-3.0007-2.el5
mod_auth_mysql-3.0.0-3.2.el5_3
mysql-connector-odbc-3.51.26r1127-1.el5
mysql-devel-5.0.77-4.el5_4.2
mysql-devel-5.0.77-4.el5_4.2

Labels:


November 08, 2010

 

Full text search

MySQL's full text search is not the only option to search for a word anywhere in the table. For e.g. I have a table called tblName in the database "DbName" and I want to search for a word "denmyr".
I am not sure about which column to search for. I can use the following shell script to generate XML file of the records those contain the word.

# vi fulltext.sh

#!/bin/sh
mydata=`mysqldump DbName tblName --skip-extended-insert | grep -i 'denmyr' | awk '{print $5}' | awk -F',' '{print $1}' | replace '(' ''`
for record in $mydata
do
mysql DbName -Xe"select * from tblName where ofac_key=$record"
done

Labels: ,


November 01, 2010

 

Logging status of MMM

You can check the Multi Master status and log it to a text file whenever an issue is detected.

#!/bin/sh
qsec=`/usr/sbin/mmm_control show | grep ONLINE | grep -v "^$" | wc -l`
SEC=0
if [ $qsec -eq $SEC ]; then

curl -Ld"user=shantanu.oak@gmail.com:password&senderID=TEST SMS&receipientno=9702977470&msgtxt=problem with MMM detected as on `date`&state=4" http://api.mVaayoo.com/mvaayooapi/MessageCompose

echo "problem with MMM detected as on `date`" >> /home/shantanu/mmstatus.txt
/usr/sbin/mmm_control show >> /home/shantanu/mmstatus.txt

fi

Labels: ,


 

Master - Maser Replication Manager

MMM (Master-Master Replication Manager) is a set of flexible scripts to perform monitoring/failover and management of MySQL Master-Master replication configurations (with only one node writable at any time). The toolset also has the ability to read balance standard master/slave configurations with any number of slaves, so you can use it to move virtual IP addresses around a group of servers depending on whether they are behind in replication.

The main functionality is provided through the following three scripts:

mmm_mond: monitoring daemon which does all monitoring work and makes all decisions about roles moving and so on.
mmm_agentd: agent daemon which runs on each MySQL server and provides monitoring node with simple set of remote services.
mmm_control: simple script dedicated to management of the mmm_mond processes by commands.


# start and stop MMM Monitor
/etc/init.d/mysql-mmm-monitor start
/etc/init.d/mysql-mmm-monitor status
/etc/init.d/mysql-mmm-monitor stop

# Show MMM details
/usr/sbin/mmm_control show
/usr/sbin/mmm_control show | grep -v 'ONLINE'
/usr/sbin/useradd --comment "MMM Script owner" --shell /sbin/nologin mmmd

# Config files
vi /etc/default/mysql-mmm-monitor
vi /etc/mysql-mmm/mmm_common.conf
vi /etc/mysql-mmm/mmm_mon.conf

A typical MMM setup looks like this...
/usr/sbin/mmm_control show
db1(192.168.100.101) master/ONLINE. Roles: reader(192.168.100.122)
db2(192.168.100.102) master/ONLINE. Roles: reader(192.168.100.121), writer(192.168.100.123)

And in order to check the logs on monitor and DB servers you can use the command...
tail /var/log/mysql-mmm/mmm_mond.log
tail /var/log/mysql-mmm/mmm_agentd.log

Here are the permissions those need to be created in order to monitor the replication.

GRANT REPLICATION CLIENT ON *.* TO 'mmm_monitor'@'%' IDENTIFIED BY 'monitor_password';
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *.* TO 'mmm_agent'@'%' IDENTIFIED BY 'agent_password';

GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 'replication_password';

grant replication slave on *.* to 'slave_user'@'%' IDENTIFIED BY 'slave_user';
# One of the last 2 statements can be used

And do not forget to check if the following setting is there in my.cnf file
log_slave_updates = 1

More info can be found here...
http://mysql-mmm.org/mmm2:guide

Labels:


 

MySQL Case Study - 180

Finding records in a given date range

I'm very new to MySQL and I'm trying to query between 4 specific times each day;

1. Where >= PREVIOUS DAY 15:00:00 and < TODAY 11:00:00
OR
2. Where >= TODAY 11:00:00 and < TODAY 15:00:00

Is it even possible to use the NOW() plus a specific time? This would query each day. If anyone can helps, that would be great!

http://forums.mysql.com/read.php?22,388833,388833#msg-388833

Labels:


 

MySQL Data Directory

MySQL Data directory should have it's own partition. The main adtantage is that you can simplify the backup process. So assuming that the sda and sdb both have equal size, we can copy the data from one partition to another.

Backup Entire Harddisk
To backup an entire copy of a hard disk to another hard disk.
Device name of the source hard disk is /dev/hda, and device name of the target hard disk is /dev/hdb.
# dd if=/dev/sda of=/dev/sdb

* “if” represents inputfile, and “of” represents output file. So the exact copy of /dev/sda will be available in /dev/sdb.
* If there are any errors, the above command will fail. If you give the parameter “conv=noerror” then it will continue to copy if there are read errors.
* Input file and output file should be mentioned very carefully, if you mention source device in the target and vice versa, you might loss all your data.

In the copy of hard drive to hard drive using dd command given below, sync option allows you to copy everything using synchronized I/O.
# dd if=/dev/sda of=/dev/sdb conv=noerror,sync

Create an Image of a Hard Disk
# dd if=/dev/hda of=~/hdadisk.img

Restore using Hard Disk Image
# dd if=hdadisk.img of=/dev/hdb

Labels:


 

Aspersa tools

The Aspersa tool does tell you almost everything that I need to know about MySQL variables.

http://aspersa.googlecode.com/svn/html/mysql-summary.html

It seems to be one of the most important tool any DBA will like to have in his toolbox.

Labels:


Archives

June 2001   July 2001   January 2003   May 2003   September 2003   October 2003   December 2003   January 2004   February 2004   March 2004   April 2004   May 2004   June 2004   July 2004   August 2004   September 2004   October 2004   November 2004   December 2004   January 2005   February 2005   March 2005   April 2005   May 2005   June 2005   July 2005   August 2005   September 2005   October 2005   November 2005   December 2005   January 2006   February 2006   March 2006   April 2006   May 2006   June 2006   July 2006   August 2006   September 2006   October 2006   November 2006   December 2006   January 2007   February 2007   March 2007   April 2007   June 2007   July 2007   August 2007   September 2007   October 2007   November 2007   December 2007   January 2008   February 2008   March 2008   April 2008   July 2008   August 2008   September 2008   October 2008   November 2008   December 2008   January 2009   February 2009   March 2009   April 2009   May 2009   June 2009   July 2009   August 2009   September 2009   October 2009   November 2009   December 2009   January 2010   February 2010   March 2010   April 2010   May 2010   June 2010   July 2010   August 2010   September 2010   October 2010   November 2010   December 2010   January 2011   February 2011   March 2011   April 2011   May 2011   June 2011   July 2011   August 2011   September 2011   October 2011   November 2011   December 2011   January 2012   February 2012   March 2012   April 2012   May 2012   June 2012   July 2012   August 2012   October 2012   November 2012   December 2012   January 2013   February 2013   March 2013   April 2013   May 2013   June 2013   July 2013   September 2013   October 2013   January 2014   March 2014   April 2014   May 2014   July 2014   August 2014   September 2014   October 2014   November 2014   December 2014   January 2015   February 2015   March 2015   April 2015   May 2015   June 2015   July 2015   August 2015   September 2015   January 2016   February 2016   March 2016   April 2016   May 2016   June 2016   July 2016   August 2016   September 2016   October 2016   November 2016   December 2016   January 2017   February 2017   April 2017   May 2017   June 2017   July 2017   August 2017   September 2017   October 2017   November 2017   December 2017   February 2018   March 2018   April 2018   May 2018   June 2018   July 2018   August 2018   September 2018   October 2018   November 2018   December 2018   January 2019   February 2019   March 2019   April 2019   May 2019   July 2019   August 2019   September 2019   October 2019   November 2019   December 2019   January 2020   February 2020   March 2020   April 2020   May 2020   July 2020   August 2020   September 2020   October 2020   December 2020   January 2021   April 2021   May 2021   July 2021   September 2021   March 2022   October 2022   November 2022   March 2023   April 2023   July 2023   September 2023   October 2023   November 2023  

This page is powered by Blogger. Isn't yours?