Shantanu's Blog

Database Consultant

January 21, 2010

 

Email new offers to your customers

# if the customer emails are saved in the emailid column of UserMaster table
# send the text of file mbox.txt to all of them with the subject and From email address specified
#!/bin/sh 
for customerMail in `mysql -uroot -pPassWord dbName -BNe"select emailid from UserMaster limit 1"`
do
mail -s "New discounts offered" $customerMail < mbox.txt -- -f customercare@CompanyName.com -F 'Customer Care'
done

Labels:


 

Archive and other engines

If you need Blackhole, Archive, CSV and federated engines you need to install MySQL from rpm. I will first have to remove the current MySQL version of 5.0.45 in order to install the latest version 5.0.77

-rw-r--r-- 1 root root 3.8M Jan 21 16:36 mysql-test-5.0.77-3.i386.rpm
-rw-r--r-- 1 root root 2.5M Jan 21 16:36 mysql-devel-5.0.77-3.i386.rpm
-rw-r--r-- 1 root root 9.9M Jan 21 16:36 mysql-server-5.0.77-3.i386.rpm
-rw-r--r-- 1 root root 20M Jan 21 16:36 mysql-debuginfo-5.0.77-3.i386.rpm
-rw-r--r-- 1 root root 4.8M Jan 21 16:36 mysql-5.0.77-3.i386.rpm
-rw-r--r-- 1 root root 509K Jan 21 16:36 mysql-bench-5.0.77-3.i386.rpm


# rpm -Uvh mysql-5.0.77-3.i386.rpm
error: Failed dependencies:
mysql = 5.0.45-7.el5 is needed by (installed) mysql-devel-5.0.45-7.el5.i386
mysql = 5.0.45-7.el5 is needed by (installed) mysql-server-5.0.45-7.el5.i386
mysql = 5.0.45-7.el5 is needed by (installed) mysql-bench-5.0.45-7.el5.i386
[root@localhost ~]# rpm -e mysql-devel
[root@localhost ~]# rpm -e mysql-server
warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave
[root@localhost ~]# rpm -e mysql-bench

[root@localhost ~]# rpm -Uvh mysql-5.0.77-3.i386.rpm
Preparing... ########################################### [100%]
1:mysql ########################################### [100%]
[root@localhost ~]# rpm -Uvh mysql-server-5.0.77-3.i386.rpm
Preparing... ########################################### [100%]
1:mysql-server ########################################### [100%]
[root@localhost ~]# rpm -Uvh mysql-devel-5.0.77-3.i386.rpm
Preparing... ########################################### [100%]
1:mysql-devel ########################################### [100%]
[root@localhost ~]# rpm -Uvh mysql-bench-5.0.77-3.i386.rpm
Preparing... ########################################### [100%]
1:mysql-bench ########################################### [100%]


mysql> show engines;
+------------+---------+----------------------------------------------------------------+
| Engine | Support | Comment |
+------------+---------+----------------------------------------------------------------+
| MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
| InnoDB | YES | Supports transactions, row-level locking, and foreign keys |
| BerkeleyDB | YES | Supports transactions and page-level locking |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE | NO | Example storage engine |
| ARCHIVE | YES | Archive storage engine |
| CSV | YES | CSV storage engine |
| ndbcluster | NO | Clustered, fault-tolerant, memory-based tables |
| FEDERATED | YES | Federated MySQL storage engine |
| MRG_MYISAM | YES | Collection of identical MyISAM tables |
| ISAM | NO | Obsolete storage engine |
+------------+---------+----------------------------------------------------------------+
12 rows in set (0.05 sec)

Labels:


January 17, 2010

 

Browser access to remote host

SSH will allow you command line access to the remote host. But if you want browser base access to remote server from your Linux desktop, use the following command to connect.
# ssh -L 8888:localhost:80 root@remoteserverip

Then go to mozilla browser and type
http://localhost:8888/

Labels:


January 07, 2010

 

finding exit code from PHP

If you use exec to execute the UNIX command or shell script then you do not get the exit status.
passthru command will allow you to save and use the exit code.

$synarray='myfile';
passthru("gunzip ".$synarray.".sql.gz > /dev/null 2>&1", $returnvalue);


if ($returnvalue != 0){
echo "no success";
}else{
echo "success";
}

Labels: ,


January 06, 2010

 

Replication "how-to"

1) Check if the server-id variable is set in my.cnf on both master and slave. If the IP is 192.178.90.51 then the id can be

server-id=1789051
# config on master server to start binary logs
log-bin=/var/log/mysql/mysql-bin.log
max_binlog_size=1024M
expire_logs_days=40

2) Take backup of master server using the following command or set it in a cron:

50 13 * * * /usr/bin/mysqldump -uroot -pPassWord dbName --routines --single-transaction --master-data=2 > /backup/dumpmysqldaily/ACCNT`date +'%d-%b-%Y-%H-%M'`.sql 2> /backup/dumpmysqldaily/ACCNT_err.txt

3) Create a "slave_user" user for replication purpose on the slave.
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'slave_user';

4) Copy the dump file from master to slave. Restore the dump on slave server
mysql -uroot -pPassWord myDBname < /root/ACCNT07-Jan-2010-11-30.sql
Do not forget to turn off binary/ slow/ general logs of slave server. The logging may slow down the restoration process.

5) Open the dump file and check the log position
$ more /root/ACCNT07-Jan-2010-11-30.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000030', MASTER_LOG_POS=110656468;

6) On the slave server point to the master:
CHANGE MASTER TO MASTER_HOST='111.222.333.444', MASTER_USER='slave_user', MASTER_PASSWORD='slave_user', MASTER_LOG_FILE='mysql-bin.000030', MASTER_LOG_POS=110656468

7) If you want to skip a line, use the following set of commands.
stop slave sql_thread;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
start slave;
show slave status\G

8) If you want a command to be executed only on the master and not on the slave then...
set sql_log_bin = 0;
#A command that you don't want to be passed on to the slave.
set sql_log_bin = 1;
_____

When you want to drop the table from master, keep it on the slave for backup purpose.
set sql_log_bin = 0;
drop table if exists temp_table;

If there are no issues, the next day execute the drop table statement again on the master and the slaves too will drop it.

9) You can replicate to different DB on the slave using the following:
replicate-rewrite-db=from_name->to_name

10) You can check the binary file size on master and remove older files
show binary logs ;
PURGE BINARY LOGS TO mysql-bin.103;


11) If you shut the slave server without stopping the slave first, the slave will loose the track of the master position. Open the error log to check the binary file and it's position.

tail -100 /var/log/mysqld.log

091222 11:15:35 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000092', position 965379003
091222 11:15:35 [Note] Error reading relay log event: slave SQL thread was killed
091222 11:15:41 [Note] /usr/libexec/mysqld: Normal shutdown

091222 11:15:41 InnoDB: Starting shutdown...
091222 11:15:44 InnoDB: Shutdown completed; log sequence number 1 453745375
091222 11:15:44 [Note] /usr/libexec/mysqld: Shutdown complete

091222 11:15:44 mysqld ended

091222 11:15:45 mysqld started

Now change the master pointer as shown below:

CHANGE MASTER TO MASTER_HOST='111.222.333.444', MASTER_USER='slave_user', MASTER_PASSWORD='slave_user', MASTER_LOG_FILE='mysql-bin.000092', MASTER_LOG_POS=965379003

12) tune up the slave:
#If you do not want a table or database to be replicated, add it to my.cnf of the slave.
replicate-wild-ignore-table=test.%
# where xxx is 'all' or a comma separated list of error codes
slave-skip-errors=all
# no user other than super privileged can write
read-only
# you can alter hostname without affecting replication
relay-log = /var/log/mysql/
# Data transferred without any problem
max_allowed_packet = 500M
skip-slave-start
# not to start the slave threads when the server starts


13) Delayed slave

Delayed slave is the slave that is stopped for some purpose.

mysql> stop slave sql_thread;

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.50.211
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000040
Read_Master_Log_Pos: 562693548
Relay_Log_File: mysqld-relay-bin.000025
Relay_Log_Pos: 107211908
Relay_Master_Log_File: mysql-bin.000040
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 559403550
Relay_Log_Space: 110501906
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
1 row in set (0.00 sec)


If a table or database is dropped by mistake, it can be retrieved by "playing" the binary logs until the position earlier the one in question.
304002 in this case.

# mysqlbinlog /var/log/mysql/mysql-bin.000040 > tostudy.txt

# grep -iB15 'DROP TABLE if EXISTS asdf' tostudy.txt | more

# at 303857
#091222 23:56:51 server id 50211 end_log_pos 304002 Query thread_id=12 exec_time=0 error_code=0
SET TIMESTAMP=1261506411/*!*/;
insert into allocated_luggage(etim_no,crc) values('MCT00043','135676505')/*!*/;
# at 304002
#091222 23:58:28 server id 50211 end_log_pos 304099 Query thread_id=13 exec_time=0 error_code=0
SET TIMESTAMP=1261506508/*!*/;
DROP TABLE if EXISTS asdf/*!*/;

mysql> start slave until master_log_file = 'mysql-bin.000040' master_log_pos = '304002'

--short-form
will truncate comments from mysqlbinlog output
--read-from-remote-server
will allow you to read binary files from other servers.

skip-slave-start
# parameter in my.cnf file tells the slave server not to start the slave threads when the server starts.

14) Use matkit utility on the master to know if the master and slave data is really in sync
mk-table-checksum h=localhost,u=root,p=PassWord,P=3306 --databases dbName h=666.777.888.999,u=root,p=SlavePassWord,P=3306 --databases dbName --nocrc --count | mk-checksum-filter

15) If the slave has stopped, you need to open the error log file and find out the position from where it needs to be restored.

# cat slave_start.sh
#!/bin/sh
# finding the slave position where it stopped and restart from there
binfile=`grep "Slave I/O thread exiting, read up to log" /var/log/mysqld.log | tail -1 | awk '{print $12}'`
position=`grep "Slave I/O thread exiting, read up to log" /var/log/mysqld.log | tail -1 | awk '{print $14}'`
echo "CHANGE MASTER TO MASTER_HOST='111.222.333.444', MASTER_USER='slave_user', MASTER_PASSWORD='slave_user', MASTER_LOG_FILE=$binfile MASTER_LOG_POS=$position;"

_____

# Show slave status will display the correct master position and not the mysql error log
# http://code.openark.org/blog/wp-content/uploads/2014/03/slave_status_explained_lagging.png

#!/bin/sh

Master_Pass='slave_user'

Relay_Master_Log_File=`mysql -e"show slave status\G" | grep 'Relay_Master_Log_File' | awk '{print $2}'`
Exec_Master_Log_Pos=`mysql -e"show slave status\G" | grep 'Exec_Master_Log_Pos' | awk '{print $2}'`
Master_Host=`mysql -e"show slave status\G" | grep 'Master_Host' | awk '{print $2}'`
Master_User=`mysql -e"show slave status\G" | grep 'Master_User' | awk '{print $2}'`

echo "CHANGE MASTER TO MASTER_LOG_FILE='"$Exec_Master_Log_File"', MASTER_LOG_POS="$Exec_Master_Log_Pos";"

echo "CHANGE MASTER TO MASTER_HOST='"$Master_Host"', MASTER_USER='"$Master_User"', MASTER_PASSWORD='"$Master_Pass"', MASTER_LOG_FILE='"$Exec_Master_Log_File"', MASTER_LOG_POS="$Exec_Master_Log_Pos";"


16) log-slave-updates is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:
A -> B -> C

17) log-slow-slave-statements In order to log slow queries in replicated thread

18) --dump-slave[=value]
This option is similar to --master-data except that it is used to dump a replication slave server to produce a dump file that can be used to set up another server as a slave that has the same master as the dumped server. This option was added in MySQL 5.5.3.

Labels:


 

Starting mysql deamon automatically

When you restart the system, mysql service will start automatically. If you do not want it to start on reboot,
chkconfig mysqld off

Now once you start the server, you have to start the mysqld using
service mysqld start

Labels:


January 05, 2010

 

UNIX case study - 23

How do I copy the entire directory structure (without files)?
For e.g. I want to copy the directory names below /home directory and paste it in the /test directory.

// copy directory structure
find /home/ -type d -print | sed 's;/home/;/test/;' | sed 's/^/mkdir /' | sh -x

Labels:


January 01, 2010

 

troubleshooting using logger

The logger command can be used anywhere in the script or PHP code that will log messages to... guess where? /var/log/messages

mysql_query("show databases") or die(exec('logger "show db query error on line 87 of index.php"'));
_____

mysql_query('update mytest set id = 12 where id = 103;');
$mycount = mysql_affected_rows();
if( "$mycount" == 0) {
exec('logger "update mytest failed for the value 12 the old value 103 was retained at `date`"');
}

Labels:


 

IP addresses from access log

If you need to analyse the access log file in order to know who is accessing your website, use the following shell script.

# cat mywho.sh
#!/bin/sh
> mycount.txt
# type your access_log path here
myfile='/var/log/httpd/access_log'

mystart=`head -1 $myfile | cut -f4 -d" " | replace '[' ''`
myend=`tail -1 $myfile | cut -f4 -d" " | replace '[' ''`
echo "$mystart to $myend" >> mycount.txt

cat $myfile | cut -f1-3 -d. | sort | uniq -c | sort -nr | head -10 |
while read count net; do
mywho=`whois "$net.0" | sed -ne '/^OrgName: */{s/^OrgName: *//;p;}'`
mynet=`whois "$net.0" | sed -ne '/^netname: */{s/^netname: *//;p;}'`
echo $count "$net.*" $mywho $mynet >> mycount.txt
done

# cat mycount.txt
30/Dec/2009:13:13:58 to 01/Jan/2010:13:43:02
19239 111.222.333.* Internet Assigned Numbers Authority
297 59.183.54.* MTNLISP
204 112.110.190.* ICL-PNQ-NET-IN
156 124.124.13.* RCOM
61 115.118.220.* TATACOMM-IN

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?