Gheek.net

August 11, 2011

Poor man’s query logging (from http://www.mysqlperformanceblog.com)

Filed under: database, linux, mysql, perl — lancevermilion @ 5:51 pm

Occasionally there is a need to see what queries reach MySQL. The database provides several ways to share that information with you. One is called general log activated with

--log

(or

--general-log

in MySQL 5.1+) start-up parameter. The log writes any query being executed by MySQL to a file with limited amount of additional information. The other is slow log enabled by

--log-slow-queries

parameter (MySQL 5.1 requires also

--slow-query-log

), which was designed to store poorly performing queries that run at least 2 seconds. Percona actually extended the slow log to, among others, include any query regardless of the execution time.

The problem is that for both you need to prepare earlier either by enabling the logging before starting the database instance or, even more work, by applying the patch and rebuilding the entire database from sources.

I know that many databases out there run with none of these and it would require a restart to get the logging in place and possibly another restart to disable it when no longer necessary (though actually slow log can be disabled by simply setting

long_query_time

MySQL variable vale high enough).

So what can be done when you really need to see the queries, but can’t afford any downtime?

If you are a privileged user (i.e. root), you can use tcpdump on a database server to take a peek into a network stream and filter for packets that go to MySQL. Those packets contain queries. Here’s my quick one-liner which I will write in multiple lines:

garfield ~ # sudo /usr/sbin/tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'

The output may contain little garbage, but it can be easily filtered out.

Obviously this method works only when applications communicate with MySQL through TCP sockets. When localhost (not to be confused with 127.0.0.1) is used as a MySQL host, this will not work since all traffic goes through a unix socket file.

It’s most definitely not a MySQL log replacement, but can be very useful if you need just a few minute dump.

Maciek

Here is sample output

tcpdump: listening on bond0, link-type EN10MB (Ethernet), capture size 65535 bytes
110811 17:47:32     325 Connect     bill@10.168.192.90 on
                    325 Query       select @@version_comment limit 1
110811 17:47:38     325 Query       SELECT DATABASE()
                    325 Init DB     accounting
                    325 Query       show databases
                    325 Query       show tables
                    325 Field List  records
110811 17:48:15     325 Quit
Advertisements

mysqlsniffer :: MySQL network protocol sniffer

Filed under: centos, database, linux, mysql, perl — lancevermilion @ 5:49 pm

You’ve got a production database server, and you can’t enable query logging… so how do you see the queries being executed against the database?

The answer: use a modified network sniffer to parse out the MySQL packets and decode them. You’ll have to do a little compiling, but it’ll be worth it. Note that this will not usually work for local connections, although you are welcome to try.

mysqlsniffer is a tcpdump clone specifically for dumping/sniffing/watching MySQL network protocol traffic over TCP/IP networks. mysqlsniffer is coded in C using the pcap library and works with MySQL version 4.0 and newer. mysqlsniffer is the only MySQL-specific network sniffer.

mk-query-digest also understands the MySQL protocol. It’s not a sniffer, though. It reads packet dumps from tcpdump like a slowlog. If you want to analyze queries from the wire (i.e. from network traffic), mk-query-digest is what you want.

For more information about the MySQL protocol read MySQL Internals ClientServer Protocol.

mysqlsniffer latest version Aug 18 2006 v1.2 (what’s new) via mysqlsniffer.tgz if you don’t want to follow the instructions below.

First, you need to install libpcap-dev (libpcap-devel for Redhat/Centos), which is the development library that allows an application to sniff network packets.

Ubuntu/Debian

sudo apt-get install libpcap-dev

Redhat/CentOS

sudo yum install libpcap-devel

Now let’s make a directory, download the source code and compile it

mkdir mysqlsniffer
cd mysqlsniffer
wget http://hackmysql.com/code/mysqlsniffer.tgz
tar xvfz mysqlsniffer.tgz
gcc -O2 -lpcap -o mysqlsniffer mysqlsniffer.c packet_handlers.c misc.c

At this point, we have a shiny new executable named mysqlsniffer in our source directory. You can copy it wherever you like (somewhere in the path would be useful)

To run mysqlsniffer, you need to specify the network interface that MySQL is listening on. For me, it’s eth0.

sudo /path/to/mysqlsniffer eth0

Ah, now there we are… all sorts of query information, without having to restart MySQL.

Here are the full options for the command:

Usage: mysqlsniffer [OPTIONS] INTERFACE

OPTIONS:
–port N Listen for MySQL on port number N (default 3306)
–verbose Show extra packet information
–tcp-ctrl Show TCP control packets (SYN, FIN, RST, ACK)
–net-hdrs Show major IP and TCP header values
–no-mysql-hdrs Do not show MySQL header (packet ID and length)
–state Show state
–v40 MySQL server is version 4.0
–dump Dump all packets in hex
–help Print this

mysqlsniffer Capture Example

MySQL network protocol traffic from the command “mysqladmin -u root -h 127.0.0.1 processlist”:

Note: The capture interface here was lo instead of eth0.

mysqlsniffer listening for MySQL on interface lo port 3306
server > 127.0.0.1.3051: ID 0 len 70 Handshake  (Caps: Get all column flags, Connect w/DB, Compression, )
127.0.0.1.3051 > server: ID 1 len 38 Handshake (new auth) <user root db (null) max pkt 1073741824> (Caps: Long password, Get all column flags, LOAD DATA LOCAL, 4.1 protocol, Transactions, 4.1 authentication, )
server > 127.0.0.1.3051: ID 2 len 7 OK  (Status: Auto-commit, )
127.0.0.1.3051 > server: ID 0 len 17 COM_QUERY: show processlist
server > 127.0.0.1.3051: ID 1 len 1 8 Fields
        ID 2 len 24 Field: ..Id
        ID 3 len 26 Field: ..User
        ID 4 len 26 Field: ..Host
        ID 5 len 24 Field: ..db
        ID 6 len 29 Field: ..Command
        ID 7 len 26 Field: ..Time
        ID 8 len 27 Field: ..State
        ID 9 len 26 Field: ..Info
        ID 10 len 5 End  (Status: Auto-commit, )
        ID 11 len 49 || 1 | root | localhost:3051 | NULL | Query | 0 | NULL | show processlist ||
        ID 12 len 5 End  (Status: Auto-commit, )
127.0.0.1.3051 > server: ID 0 len 1 COM_QUIT
mysqlsniffer exited cleanly.
17 MySQL packets captured (469 bytes)

Loads of stuff starts flying by… let’s filter it out a little more so we can just get the queries and not all the excess data.

$ sudo /path/to/mysqlsniffer –no-mysql-hdrs eth0 | grep COM_QUERY

192.168.73.1.2622 > server: COM_QUERY: SELECT @@sql_mode
192.168.73.1.2622 > server: COM_QUERY: SET SESSION sql_mode=”
192.168.73.1.2622 > server: COM_QUERY: SET NAMES utf8
192.168.73.1.1636 > server: COM_QUERY: SELECT @@SQL_MODE
192.168.73.1.1636 > server: COM_QUERY: SHOW FULL COLUMNS FROM `db2842_howto`.`wp_users`

Original source code and more information at:

http://hackmysql.com/mysqlsniffer

If you are running on a development server, it would be easier to just turn on query logging.

MySQL Tools (from http://mysqlhack.com).

Filed under: database, mysql, perl — lancevermilion @ 5:31 pm

MySQL Tools

The MySQL distros come with extra tools/scripts: mysqldumpslow, mysqldump, mysql_zap, etc. I’ve never known a DBA for whom these tools sufficed. The open-source community has created a sizable number of MySQL and MySQL-related tools which fill pretty much every niche and need. As a full-time developer of such tools, these projects are my speciality. All code listed here is free and open-source.

MySQL Forge is a repository for MySQL projects, tools, code snippets and other odds and ends. In my opinion, it’s not focused enough to be useful which is why I maintain this page. The tools here are ones that I know top MySQL professionals use regularly and recommend.

Hack MySQL Scripts

These are the scripts I created many years ago when no such (better) scripts existed. They have since been retired and replaced by Maatkit tools. I leave them here for historicity and because they are mentioned in several books (one of which was published in early 2010).

Tools

Profile

  • Project: none, archived here
  • Status: retired, replaced by Maatkit tools
  • Tested: no
  • Documented: yes, online and included with releases
  • Language: Perl
  • Design: procedural, partly modular

Maatkit (mk)

Maatkit is a collection of 20+ scripts originally created by Baron Schwartz. These scripts include mk-table-sync, mk-query-digest, mk-archiver, mk-heart, etc. I am the full-time developer and maintainer for all the scripts. Baron and myself are employed by Percona which sponsors most Maatkit development.

Tools

  • mk-table-checksum – Perform an online replication consistency check, or checksum MySQL tables efficiently on one or many servers.
  • mk-table-sync – Synchronize MySQL tables efficiently.
  • mk-query-digest – Parses logs and more. Analyze, transform, filter, review and report on queries.
  • mk-archiver, mk-find, mk-heartbeat, mk-kill, mk-parallel-dump, mk-parallel-restore, mk-slave-delay, mk-upgrade, …

Profile

  • Project: Maatkit
  • Status: active, full-time, professional development
  • Tested: yes, over 2,000 tests
  • Documented: yes, POD in each tool, POD reproduced online at each release
  • Language: Perl
  • Design: procedural, highly modular

boots

Boots is an extensible command line database client for Drizzle and MySQL. It looks and feels almost like the old, venerable mysql cli, but it has modern features like “lingos” and pipes. In my opinion, it’s the MySQL-esque cli of the future for the MySQL-esque RDMS of the future, Drizzle. It still works with MySQL, though.

Profile

  • Project: boots
  • Status: active, professional development
  • Tested: yes
  • Documented: in-program help, not really documented yet since it’s still in devel
  • Language: Python
  • Design: object-oriented, extensible

Percona Projects

Percona develops and sponsors various MySQL tools, programs, patches, etc.–including Maatkit. These range from backup programs to data recovery tools. There’s hardly a way to summarize them all, so just check out the project page.

Profile

  • Project: Percona Projects
  • Status: active, full-time, professional development
  • Tested: unknown
  • Documented: somewhat online, larger documentation effort currently in progress
  • Language: C, C++, Perl
  • Design: various

Aspersa

Maatkit once had a masse of “utilities” which didn’t really belong in Maatkit proper, so Baron moved them to separted project: aspersa. Most of the tools are not directly MySQL-related, but they’re in the ecosystem so I’ve included them here. (If nothing else, they were born from a pure MySQL project: Maatkit.)

Tools

  • bt-aggregate – Aggregate and print GDB backtraces.
  • iodump – Compute per-PID I/O stats for Linux when iotop/pidstat/iopp are not available.
  • mext2 – Columnize output of mysqladmin.
  • snoop-to-tcpdump – Formats ‘snoop’ output the same way as ‘tcpdump’.
  • More tools added as needed…

Profile

  • Project: aspersa
  • Status: developed as needed
  • Tested: no
  • Documented: minimal
  • Language: Perl, bash
  • Deisgn: whatever works

Kontrollkit

Kontrollkit is a collection of scripts to simplify daily MySQL DBA responsibilities. Like Maatkit, it is a suite of many tools backed by a business: Kontrollsoft.

Tools

  • kt-backup-parallel – runs mysql backups in parallel super fast, has lots of reporting features.
  • kt-backup-ai – runs mysql backups standard mysqldump method but adds nice features like email reporting and backup completion checking as well as compression, also adds disk space checking and size analysis before running the backup to ensure you don’t use up 100% partition space with a backup.
  • kt-check-replication – script to report on replication status for slave servers.
  • kt-connections-log – logs connections to mysql to disk, reports on threshold overages.
  • kt-flush-tables-sequence – runs through schema.tables to flush in sequence before global flush.
  • kt-mysql-systemcheck – generates a report for point-in-time system status that is useful for troubleshooting MySQL servers.
  • Several more tools…

Profile

  • Project: kontrollkit
  • Status: active
  • Tested: no
  • Documented: no
  • Language: Perl, Python
  • Design: procedural

MMM

MMM does monitoring/failover and management of MySQL master-master replication configurations.

Profile

  • Project: MMM 2.x
  • Status: semi-active, professional and volunteer development
  • Tested: no
  • Documented: yes, POD and online
  • Language: Perl
  • Design: procedural, highly modular

innotop

innotop is a ‘top’ clone for MySQL. It was created by Baron Schwartz.

Profile

  • Project: innotop
  • Status: no longer in development
  • Tested: no
  • Documented: yes, POD
  • Language: Perl
  • Design: procedural, partly modular

Schema Sync

Schema Sync will generate the SQL necessary to migrate the schema of a source database to a target database (patch script), as well as a the SQL necessary to undo the changes after you apply them (revert script).

Profile

  • Project: SchemaSync
  • Status: active development
  • Tested: yes
  • Documented: basics
  • Language: Python
  • Design: procedural, modular

rtime

rtime is a program that records and reports query response times by sniffing MySQL protocol traffic. It’s coded by another Percona employee, Ignacio Nin. It’s meant to be small and light-weight, filling a role somewhere between mysqlsniffer and mk-query-digest –type tcdpump.

Profile

  • Project: rtime
  • Status: active development
  • Tested: no
  • Documented: yes
  • Language: C
  • Design: procedural

openark kit

The openark kit is a set of utilities for MySQL. They solve everyday maintenance tasks, which may be complicated or time consuming to work by hand. The kit was created and is maintained by Shlomi Noach.

Tools

  • oak-apply-ri – apply referential integrity on two columns with parent-child relationship.
  • oak-block-account – block or release MySQL users accounts, disabling them or enabling them to login.
  • oak-chunk-update – Perform long, non-blocking UPDATE/DELETE operation in auto managed small chunks.
  • oak-kill-slow-queries – terminate long running queries.
  • oak-modify-charset – change the character set (and collation) of a textual column.
  • oak-online-alter-table – Perform a non-blocking ALTER TABLE operation.
  • oak-purge-master-logs – purge master logs, depending on the state of replicating slaves.
  • oak-security-audit – audit accounts, passwords, privileges and other security settings.
  • oak-show-limits – show AUTO_INCREMENT free space.
  • oak-show-replication-status – show how far behind are replicating slaves on a given master.

Profile

  • Project: openarkkit
  • Status: active, professional development
  • Tested: no
  • Documented: yes, online
  • Language: Python
  • Design: procedural

mycheckpoint

Shlomi’s other tool is mycheckpoint. mycheckpoint is an open source monitoring utility for MySQL, with strong emphasis on user accessibility to monitored data.

Profile

  • Project: mycheckpoint
  • Status: active, professional development
  • Tested: no
  • Documented: yes, online
  • Language: Python
  • Design: procedural

MySQL Sandbox

MySQL Sandbox, created by Giuseppe Maxia, is a set of tools (and Perl modules) that allow you to create isolated instances (sandboxes) of MySQL servers in various configurations (standalone, replication, etc.). MySQL sandboxes are useful for testing because they setup and tear down quickly and are self-contained, not interfering with a default MySQL server and its data, port, socket, etc.

Profile

  • Project: MySQL Sandbox
  • Status: active, professional development
  • Tested: yes
  • Documented: yes
  • Language: Perl
  • Design: procedural, modular

MySQL Cacti Templates

Although I’ve never used and know nothing about Cacti, I know that these MySQL Cacti templates are used extensively. Based on questions and issues I see related to MySQL monitoring with Cacti graphs, it seems that Cacti templates can be difficult to config correctly/well so these templates help make it easier.

Profile

  • Project: mysql-cacti-templates
  • Status: active, professional development
  • Tested: yes
  • Documented: yes
  • Language: Perl, PHP
  • Design: procedural

April 15, 2011

How to properly setup Innodb in MySQL to shrink when optimized.

Filed under: mysql — lancevermilion @ 4:22 pm

Taken from: http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine

There is a more complete answer with regard to InnoDB

Keep in mind the busiest file in the InnoDB infrastructure

/var/lib/mysql/ibdata1

This file normally houses four types of information

1) Table Data

2) Table Indexes

3) MVCC (Multiversioning Concurrency Control) Data

4) Table Metadata

Many people create multiple ibdata files hoping for better diskspace management and performance. SORRY, NOT HAPPENING !!!

Unfortunately, OPTIMIZE TABLE against an InnoDB table stored ibdata1 does two things: 1) Makes the table’s data and indexes contiguous inside ibdata1 2) It makes ibdata1 grow because the contiguous data is appended to ibdata1

You can segragate Table Data and Table Indexes from ibdata1 and manage them independently. To shrink ibdata1 once and for all you must do the following

1) MySQLDump all databases into a SQL text file (call it SQLData.sql)

2) Drop all databases (except mysql schema)

3) Shutdown mysql

4) Add the following lines to /etc/my.cnf

innodb_file_per_table

innodb_flush_method=O_DIRECT

innodb_log_file_size=1G

innodb_buffer_pool_size=4G

Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

5) Delete ibdata1, ib_logfile0 and ib_logfile1

At this point, there should only be the mysql schema in /var/lib/mysql

6) Restart mysql

This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

7) Reload SQLData.sql into mysql

ibdata1 will grow but only contain table metadata

Each InnoDB table will exist outside of ibdata1

Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table

mytable.frm (Storage Engine Header) mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)

ibdata1 will never contain InnoDB data and Indexes anymore.

With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many time in my career as a MYSQL DBA

In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 50MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!

http://www.linkedin.com/in/rolandoedwards

www . logicworks . net

February 28, 2011

Liquibase…A RCS for SQL

Filed under: database, java, Liquibase, mysql — lancevermilion @ 6:14 pm

OS: CentOS 5.4

This is a short tutorial on how to get Liquibase to diff 2 database schemas.

Download Liquibase and Extract it

HOME="/home/testuser"
cd $HOME
mkdir liquibase
cd liquibase
wget http://liquibase.org/liquibase-2.0.1-bin.tar.gz
tar zxvf liquibase-2.0.1-bin.tar.gz
chmod +x liquibase

Download a MySQL connector jar file and place it in the Liquibase lib directory.

wget http://www.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.15.tar.gz/from/http://mysql.he.net/
tar zxvf mysql-connector-java-5.1.15.tar.gz
cp mysql-connector-java-5.1.15/mysql-connector-java-5.1.15-bin.jar /home/testuser/liquibase/lib/

How to diff 2 databases schemas.
Let Liquibase diff the two database schemas for you. It will tell you if you have 2 different tables/columns.
Both databases (testdb1 and testdb2) are located on a mysql-server running on my localhost.
I access them with the username of “root” and the password of “mypassword”.

{testuser@localhost /home/testuser/liquibase/}#  ./liquibase --driver=com.mysql.jdbc.Driver \ 
--url=jdbc:mysql://localhost/testdb1 \ 
--username=root \ 
--password=mypassword \ 
diff \ 
--referenceUrl=jdbc:mysql://localhost/testdb2 \ 
--referenceusername=root \ 
--referencepassword=mypassword

Here is a sample output with differences.
testdb2 I changed the Column user_id to user1_id.

INFO 2/28/11 10:58 AM:liquibase: Reading tables for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading views for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading foreign keys for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading primary keys for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading columns for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading unique constraints for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading indexes for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Sequences not supported for root@localhost @ jdbc:mysql://localhost/testdb2 ...
INFO 2/28/11 10:58 AM:liquibase: Reading tables for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Reading views for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Reading foreign keys for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Reading primary keys for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Reading columns for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Reading unique constraints for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Reading indexes for root@localhost @ jdbc:mysql://localhost/testdb1 ...
INFO 2/28/11 10:58 AM:liquibase: Sequences not supported for root@localhost @ jdbc:mysql://localhost/testdb1 ...

Diff Results:
Reference Database: root@localhost @ jdbc:mysql://localhost/testdb1
Target Database: root@localhost @ jdbc:mysql://localhost/testdb2
Product Name: EQUAL
Product Version: EQUAL
Missing Tables: NONE
Unexpected Tables: NONE
Missing Views: NONE
Unexpected Views: NONE
Changed Views: NONE
Missing Columns: 
     accounting.user_id
Unexpected Columns: 
     accounting.user1_id
Changed Columns: NONE
Missing Foreign Keys: NONE
Unexpected Foreign Keys: NONE
Missing Primary Keys: NONE
Unexpected Primary Keys: NONE
Unexpected Unique Constraints: NONE
Missing Unique Constraints: NONE
Missing Indexes: NONE
Unexpected Indexes: NONE
Missing Sequences: NONE
Unexpected Sequences: NONE
Liquibase 'diff' Successful

November 7, 2008

iSCSI init script supporting MySQL checking

Filed under: centos, iscsi, linux, mysql, shell scripts — Tags: , , , — lancevermilion @ 10:17 am

Here is a iSCSI init script for CentOS 5.x. I presume it will work on all flavors of Linux, but haven’t tested. This init script is the original init script but modified to support checking for MySQL databases that might be using the iSCSI mounted disk. So we do not want to umount the iSCSI disk while MySQL is running. I also added checks to see if iscsid is actually running, if it is then make sure you are not already logged in. If you are then don’t try to login again. Also once iSCSI is successfully started we want to mount the iSCSI disk so it is available.

*NOTE: Make sure to adjust your boot init setup. Normally mysql will start before iscsi and iscsid EXCEPT for init 5. Which by default init 5 is the only level which will start mysqld. If you have changed this or yours if not the same make sure you account for this.


chkconfig --list | egrep "iscsi|mysql"
iscsi 0:off 1:off 2:off 3:on 4:on 5:on 6:off
iscsid 0:off 1:off 2:off 3:on 4:on 5:on 6:off
mysqld 0:off 1:off 2:off 3:off 4:off 5:on 6:off

init level 3

ls -l /etc/rc.d/rc3.d/ | egrep -i "mysql|iscsi" | awk '{print $9, $10, $11, $12;}'
K36mysqld -> ../init.d/mysqld
S07iscsid -> ../init.d/iscsid
S13iscsi -> ../init.d/iscsi

init level 5

ls -l /etc/rc.d/rc5.d/ | egrep -i "mysql|iscsi" | awk '{print $9, $10, $11, $12;}'
S07iscsid -> ../init.d/iscsid
S13iscsi -> ../init.d/iscsi
S64mysqld -> ../init.d/mysqld

/etc/init.d/iscsi

#!/bin/sh
#
# chkconfig: 345 13 89
# description: Logs into iSCSI targets needed at system startup
#
# Source function library.
. /etc/init.d/functions

PATH=/sbin:/bin:/usr/sbin:/usr/bin

RETVAL=0

start()
{

        status iscsid > /dev/null 2>&1
        RETVAL=$?

        if [ $RETVAL -ne 0 ]; then
                /etc/init.d/iscsid start
        fi


        status iscsid > /dev/null 2>&1
        RETVAL=$?

        #
        # Verify iscsi is logged in to the iscsi disk
        # and iscsid is actually running.
        #
        iscsiadm -m session -R > /dev/null 2>&1
        iscsiadmcheck=$?
        if [ $RETVAL -eq  0 ]; then
           if [ $iscsiadmcheck -ne "0" ]; then
             echo -n $"Setting up iSCSI targets: "
             # this script is normally called from startup so log into
             # nodes marked node.startup=automatic
             iscsiadm -m node --loginall=automatic
             touch /var/lock/subsys/iscsi
             success
             echo
             sleep 5
             mount -a -O _netdev
           else
             action $"iscsid: " /bin/true
             echo "iscsi:   [ is running ]"
           fi
        else
           action $"Starting iscsid: " /bin/false
           RETVAL=1
        fi
}

stop()
{
        rm -f /var/lock/subsys/iscsi

        # If this is a final shutdown/halt, do nothing since
        # lvm/dm, md, power path, etc do not always handle this
        if [ "$RUNLEVEL" = "6" -o "$RUNLEVEL" = "0" -o "$RUNLEVEL" = "1" ]; then
                success
                return
        fi

        # Do NOT turn off iscsi if root is possibly on a iscsi disk
        rootopts=$(awk '{ if ($1 !~ /^[ \t]*#/ && $2 == "/") { print $4; }}' /etc/mtab)
        if [[ "$rootopts" =~ "_netdev" ]] ; then
                action $"Stopping iscsid: " /bin/false
                echo $"Can not shutdown iSCSI. Root is on a iSCSI disk."
                exit 1
        fi

        #
        # MySQL checking. If MySQL loads a DB from an iscsi disk we need to make sure
        # MySQL is NOT running before we shutdown iscsi and umount the iscsi disk.
        #
        mysqlopts=$(awk '{  if ( $1 ~ /^datadir/ ) { gsub("datadir=", "", $1); print $1; } }' /etc/my.cnf)
        iscsiopts=$(awk -v mysqlopts=$mysqlopts '{ if ($1 !~ /^[ \t]*#/ && $2 != "/" && mysqlopts ~ $2 ) { print $4; }}' /etc/mtab)
        /etc/init.d/mysqld status > /dev/null 2>&1
        mysqlcheck=$?
        if [[ "$iscsiopts" =~ "_netdev" ]] ; then
                if [ "$mysqlcheck" -eq 0 ]; then
                  action $"Stopping iscsid: " /bin/false
                  echo "Can not shutdown iSCSI. MySQL is mounted on a iSCSI disk."
                  exit 1
                fi
        fi
        iscsiadm -m node --logoutall=all
        /etc/init.d/iscsid stop
        success

        status iscsid > /dev/null 2>&1
        RETVAL=$?
        iscsiadm -m session -R > /dev/null 2>&1
        iscsiadmcheck=$?
        if [[ "$iscsiadmcheck" -ne 0 && $RETVAL -ne 0 ]]; then
          umount -a -O _netdev
        else
           action $"Stopping iscsid: " /bin/false
           echo "iSCSI did NOT logout successfully"
           RETVAL=1
        fi
}

case "$1" in
        start)
                start
                ;;
        stop)
                stop
                ;;
        restart)
                stop
                start
                ;;
        status)
                status iscsid
                RETVAL=$?
                ;;
        condrestart)
                [ -f /var/lock/subsys/iscsi ] && restart
                ;;
        *)
                echo $"Usage: $0 {start|stop|restart|status|condrestart}"
                exit 1
esac
exit $RETVAL

/etc/init.d/iscsid

#!/bin/sh
#
# chkconfig: 345 7 89
# description: Starts and stops the iSCSI daemon.
#
# processname: iscsid
# pidfile: /var/run/iscsid.pid
# config:  /etc/iscsi/iscsid.conf

# Source function library.
. /etc/init.d/functions

PATH=/sbin:/bin:/usr/sbin:/usr/bin

RETVAL=0

start()
{
        echo -n $"Turning off network shutdown. "
        # we do not want iscsi or network to run during system shutdown
        # incase there are RAID or multipath devices using
        # iscsi disks
        chkconfig --level 06 network off
        rm /etc/rc0.d/*network
        rm /etc/rc6.d/*network

        echo -n $"Starting iSCSI daemon: "
        modprobe -q iscsi_tcp
        modprobe -q ib_iser
        daemon iscsid
        RETVAL=$?
        echo
        [ $RETVAL -eq 0 ] || return

        touch /var/lock/subsys/iscsid

        success
        echo
        sleep 5
}

stop()
{
        rm -f /var/lock/subsys/iscsid

        # If this is a final shutdown/halt, do nothing since
        # we may need iscsid for as long as possible (halt script kills
        # us at the last second)
        if [ "$RUNLEVEL" = "6" -o "$RUNLEVEL" = "0" -o "$RUNLEVEL" = "1" ]; then
                success
                return
        fi

        # don't turn off iscsi if root is possibly on a iscsi disk
        rootopts=$(awk '{ if ($1 !~ /^[ \t]*#/ && $2 == "/") { print $4; }}' /etc/mtab)
        if [[ "$rootopts" =~ "_netdev" ]] ; then
                echo $"Can not shutdown iSCSI. Root is on a iSCSI disk."
                exit 1
        fi

        mysqlopts=$(awk '{  if ( $1 ~ /^datadir/ ) { gsub("datadir=", "", $1); print $1; } }' /etc/my.cnf)
        iscsiopts=$(awk -v mysqlopts=$mysqlopts '{ if ($1 !~ /^[ \t]*#/ && $2 != "/" && mysqlopts ~ $2 ) { print $4; }}' /etc/mtab)
        /etc/init.d/mysqld status > /dev/null 2>&1
        mysqlcheck=$?
        if [[ "$iscsiopts" = "rw,_netdev" ]] ; then
                if [ "$mysqlcheck" -eq 0 ]; then
                  echo "Can not shutdown iSCSI. MySQL is on a iSCSI disk."
                  exit 1
                fi
        fi


        echo -n $"Stopping iSCSI daemon: "

        # iscsid does not have a nice shutdown process.
        # It really should never be stopped
        #pkill -KILL iscsid
        killproc iscsid
        echo

        modprobe -r ib_iser 2>/dev/null
        modprobe -r iscsi_tcp 2>/dev/null
}

restart()
{
        stop
        start
}

case "$1" in
        start)
                start
                ;;
        stop)
                stop
                ;;
        restart)
                stop
                start
                ;;
        status)
                status iscsid
                RETVAL=$?
                ;;
        condrestart)
                [ -f /var/lock/subsys/iscsid ] && restart
                ;;
        *)
                echo $"Usage: $0 {start|stop|restart|status|condrestart}"
                exit 1
esac

exit $RETVAL

MySQL init script supporting iSCSI disk mount checking

Filed under: centos, iscsi, linux, mysql, shell scripts — Tags: , , — lancevermilion @ 9:45 am

Here is a MySQLd init script for CentOS 5.x. I presume it will work on all flavors of Linux, but haven’t tested. The init script is the original init script but modified to support checking for iSCSI disk support. Basically if the my.cnf specifies a directory that is on an iSCSI mounted disk it will make sure iscsi/iscsid are running and the mount is present in /etc/mtab. I also added a check to see if MySQL is already running and if so do NOT try to restart mysql when a user issues “/etc/init.d/mysqld start”. Basically The user must first issue “/etc/init.d/mysqld restart” or “/etc/init.d/mysqld stop” then “/etc/init.d/mysqld start”. I also added a chkconfig bit. MySQLd needs to start after iSCSI has started just in case it needs to the iSCSI disk. Multipath should be enabled with “/sbin/chkconfig multipathd on” which by default sets init levels 2, 3, 4, and 5 to on. So you would have the services start up as so: multipathd, iscsid, iscsi, mysqld. They will shut down in reverse order: mysqld, iscsi, iscsid, multipathd.


#!/bin/bash
#
# mysqld This shell script takes care of starting and stopping
# the MySQL subsystem (mysqld).
#
# chkconfig: 345 64 36
# description: MySQL database server.
# processname: mysqld
# config: /etc/my.cnf
# pidfile: /var/run/mysqld/mysqld.pid

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

prog="MySQL"

# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
result=`/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}

get_mysql_option mysqld datadir "/var/lib/mysql"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld.pid"
mypidfile="$result"

start(){
#
# iscsi checking. If we need mysql to create/load a DB that is one an iscsi
# disk we ned to make sure iscsi is running, we can successfully login,
# and verify the iscsi disk is moutned before starting mysql.
#
status mysqld > /dev/null 2>&1
mysqlcheck=$?
mysqlopts=$(awk '{ if ( $1 ~ /^datadir/ ) { gsub("datadir=", "", $1); print $1; } }' /etc/my.cnf)
iscsiopts=$(awk -v mysqlopts=$mysqlopts '{ if ($1 !~ /^[ \t]*#/ && $2 != "/" && mysqlopts ~ $2 ) { print $4; }}' /etc/mtab)
status iscsid > /dev/null 2>&1
iscsicheck=$?
if [ "$mysqlcheck" -eq 0 ]; then
echo "MySQL [ already running ]"
exit 1
else
if [ "$iscsicheck" -ne 0 ] ; then
if [[ "$iscsiopts" =~ "_netdev" ]]; then
action $"Checking iscsid: " /bin/true
action $"Starting $prog: " /bin/false
echo "Can not start MySQL, iSCSI disk is not mounted. MySQL is on a iSCSI disk."
exit 1
fi
elif [ "$iscsicheck" -eq 0 ]; then
if [[ "$iscsiopts" =~ "_netdev" ]]; then
iscsiadm -m session -R > /dev/null 2>&1
iscsiadmcheck=$?
if [ "$iscsiadmcheck" -ne 0 ]; then
action $"Checking iscsid: " /bin/true
action $"Starting $prog: " /bin/false
echo "iSCSI login check failed. QUITING!!!"
exit 1
fi
fi
else
action $"Checking iscsid: " /bin/false
action $"Starting $prog: " /bin/false
echo "Can not start MySQL, iSCSI is not running. MySQL is on a iSCSI disk."
exit 1
fi
fi
touch "$errlogfile"
chown mysql:mysql "$errlogfile"
chmod 0640 "$errlogfile"
[ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"
if [ ! -d "$datadir/mysql" ] ; then
action $"Initializing MySQL database: " /usr/bin/mysql_install_db
ret=$?
chown -R mysql:mysql "$datadir"
if [ $ret -ne 0 ] ; then
return $ret
fi
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
# Pass all the options determined above, to ensure consistent behavior.
# In many cases mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure.
/usr/bin/mysqld_safe --datadir="$datadir" --socket="$socketfile" \
--log-error="$errlogfile" --pid-file="$mypidfile" \
>/dev/null 2>&1 &
ret=$?
# Spin for a maximum of N seconds waiting for the server to come up.
# Rather than assuming we know a valid username, accept an "access
# denied" response as meaning the server is functioning.
if [ $ret -eq 0 ]; then
STARTTIMEOUT=30
while [ $STARTTIMEOUT -gt 0 ]; do
RESPONSE=`/usr/bin/mysqladmin -uUNKNOWN_MYSQL_USER ping 2>&1` && break
echo "$RESPONSE" | grep -q "Access denied for user" && break
sleep 1
let STARTTIMEOUT=${STARTTIMEOUT}-1
done
if [ $STARTTIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to start MySQL Daemon."
action $"Starting $prog: " /bin/false
ret=1
else
action $"Starting $prog: " /bin/true
fi
else
action $"Starting $prog: " /bin/false
fi
[ $ret -eq 0 ] && touch /var/lock/subsys/mysqld
return $ret
}

stop(){
MYSQLPID=`cat "$mypidfile" 2>/dev/null `
if [ -n "$MYSQLPID" ]; then
/bin/kill "$MYSQLPID" >/dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
STOPTIMEOUT=60
while [ $STOPTIMEOUT -gt 0 ]; do
/bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
sleep 1
let STOPTIMEOUT=${STOPTIMEOUT}-1
done
if [ $STOPTIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to stop MySQL Daemon."
ret=1
action $"Stopping $prog: " /bin/false
else
rm -f /var/lock/subsys/mysqld
rm -f "$socketfile"
action $"Stopping $prog: " /bin/true
fi
else
action $"Stopping $prog: " /bin/false
fi
else
ret=1
action $"Stopping $prog: " /bin/false
fi
return $ret
}

restart(){
stop
start
}

condrestart(){
[ -e /var/lock/subsys/mysqld ] && restart || :
}

# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status mysqld
;;
restart)
restart
;;
condrestart)
condrestart
;;
*)
echo $"Usage: $0 {start|stop|status|condrestart|restart}"
exit 1
esac

exit $?

November 3, 2008

SQL commands

Filed under: mysql, sql — Tags: , — lancevermilion @ 1:40 pm
mysql> select table2.name, phone_number, email_address, count(t1.field1_id) from table1 t1 right outer join table2 t2 on (t1.field1_id = t2.field1_id) group by t2.name;

This will create an output similar to this.

The “Table2 Name” is just a distinct list from the name field of table2 table.
The “# of entries per name in table2” is just a result from count( where field1_id(from
table1 table) = field1_id (from table2 table) )

+————————————-+————————————————–+
| Table 2 Name               | # of entries per name in table2 |
+————————————-+————————————————–+
| xxxx.xxx.1.S000018      |                                          43 |
| x.xxx.xxxx.1.s000032    |                                           5 |
| xxxx01                        |                                           13 |
| xxxx03                        |                                           15 |
| xxx.xxx.xx.1.s000031   |                                           17 |
| xxx.x.xxxx.s00017       |                                           28 |
| xxx.xxx.s000003         |                                           47 |
| xxx.xxx.aaaabbb         |                                            0 |
+————————————-+————————————————–+

October 28, 2008

Show live mysql queries

Filed under: mysql, perl — Tags: , , , — lancevermilion @ 1:10 pm

Original code found at “http://iank.org/querysniffer/&#8221;. I have stripped much of it and inserted a log of code from “http://www.perlmonks.org/?node_id=170648&#8221; to make it a little nicer. I also added a lot of stuff via variables now.

Reference “http://www.redferni.uklinux.net/mysql/MySQL-Protocol.html&#8221; for more info on reading the pcap info.

The code will return something like this. Keep in mind the question mark has a value that is passed in with the command. I do not know how to strip out the binary around it so I choose to exclude it so I can at least get the SQL commands executed. For me this told me that the application was doing a nice SQL statement to start with then doing a not so friendly one after. I was able to go back to the developer and ask him to chase this down.

SELECT * FROM sys_log  WHERE  field1 = ?  AND  timestamp >= ? AND timestamp <= ? ORDER BY timestamp DESC LIMIT 20000
INSERT INTO log_entry (timestamp,log_type,source,description) VALUES (?, ?, ?, ?)
SELECT * FROM sys_log  WHERE  field1 = ? ORDER BY timestamp DESC LIMIT 20000
INSERT INTO log_entry (timestamp,log_type,source,description) VALUES (?, ?, ?, ?)

You will need libpcap-devel, libpcap, and perl modules (Net::Pcap, Net::PcapUtils, NetPacket::Ethernet, NetPacket::IP, NetPacket::TCP, Socket). Make sure you run “perl -c” on the script.

#!/usr/bin/perl -w
use strict;
use Net::Pcap;
use Net::PcapUtils;
use NetPacket::Ethernet qw(:strip);
use NetPacket::IP qw(:strip);
use NetPacket::Ethernet;
use NetPacket::TCP;
use Socket;
use constant MYSQL_PORT => 3306;

#   Script Version
my $ver = 0.11;

#   Remove the numbers for the command type codes you
#   do not want to see.
#   Beware binary data is not filtered just yet so you
#   might end up screwing up ascii display to your terminal
#   if the right set of binary characters pass through your screen.
#use constant COM_QUERY_TYPES => qw( 2 3 5 6 11 16 22 23 24 25 );
use constant COM_QUERY_TYPES => qw( 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 );
my @COM_QUERY_TYPES = (COM_QUERY_TYPES);

#   If you do not want to filter lines that only contain binary data
#   then change the value to 0;
my $filterbinary = 1;

#   MySQL Command codes
my %cmdcode = (
  0 => 'SLEEP',
  1 => 'QUIT',
  2 => 'INIT_DB',
  3 => 'QUERY',
  4 => 'FIELD_LIST',
  5 => 'CREATE_DB',
  6 => 'DROP_DB',
  7 => 'REFRESH',
  8 => 'SHUTDOWN',
  9 => 'STATISTICS',
  10 => 'PROCESS_INFO',
  11 => 'CONNECT',
  12 => 'PROCESS_KILL',
  13 => 'DEBUG',
  14 => 'PING',
  15 => 'TIME',
  16 => 'DELAYED_INSERT',
  17 => 'CHANGE_USER',
  18 => 'BINLOG_DUMP',
  19 => 'TABLE_DUMP',
  20 => 'CONNECT_OUT',
  21 => 'REGISTER_SLAVE',
  22 => 'PREPARE',
  23 => 'EXECUTE',
  24 => 'LONG_DATA',
  25 => 'CLOSE_STMT',
  26 => 'RESET_STMT',
  27 => 'SET_OPTION',
);

my $err;

#   Use network device passed in program arguments or if no
#   argument is passed, determine an appropriate network
#   device for packet sniffing using the
#   Net::Pcap::lookupdev method

my $dev = $ARGV[0];
unless (defined $dev) {
    $dev = Net::Pcap::lookupdev(\$err);
    if (defined $err) {
        die 'Unable to determine network device for monitoring - ', $err;
    }
}

#   Specify the MTU you want to use on teh interface
my $mtu = 1500;

#   Source/Destination filter
my $filter_string = "(dst 127.0.0.1)";

#   Do not print these SQL query commands when displaying SQL queries;
#   Change $do_not_print to = ''; if you do not want to filter the printing
#   of the stuff listed below
my $do_not_print = "^SET NAMES|^SET character|^SHOW VARIABLE|^SHOW COLLATION|^SET autocommit|^SET sql_mode";

#   Number of packets to capture
#   Zero or a negative number means capture packets indefinitely
#   (or until an error occurs if the $to_ms argument of
#   the open_live method is set to 0)
#   100 = 100 packets to capture
my $count = -1;

#   Optimize the capture filter
#   0 = false (will not optimize)
#   1 = true  (will optimize)
my $optimize = 0;



#                             #
# DO NOT EDIT BELOW THIS LINE #
#                             #




#   Look up network address information about network
#   device using Net::Pcap::lookupnet - This also acts as a
#   check on bogus network device arguments that may be
#   passed to the program as an argument.
#
#   Note: The address and netmask are returned as integers
#         so you will need to convert them


my ($address, $netmask);
if (Net::Pcap::lookupnet($dev, \$address, \$netmask, \$err)) {
    die 'Unable to look up device information for ', $dev, ' - ', $err;
}
#   Convert the integer returned for the addresses to decimal ip addr format
my $Address = inet_ntoa(pack "N", $address);
my $Netmask = inet_ntoa(pack "N", $netmask);

#   Create packet capture object on device

my $object;
$object = Net::Pcap::open_live($dev, $mtu, 0, 0, \$err);
unless (defined $object) {
    die 'Unable to create packet capture on device ', $dev, ' - ', $err;
}

#   Compile and set packet filter for packet capture
#   object - For the capture of TCP packets with the SYN
#   header flag set directed at the external interface of
#   the local host, the packet filter of '(dst IP) && (tcp
#   [13] & 2 != 0)' is used where IP is the IP address of
#   the external interface of the machine.  For
#   illustrative purposes, the IP address of 127.0.0.1 is
#   used in this example.

my $filter_compiled;
Net::Pcap::compile(
    $object,
    \$filter_compiled,
    "$filter_string",
    $optimize,
    $netmask
) && die 'Unable to compile packet capture filter';
Net::Pcap::setfilter($object, $filter_compiled) &&
    die 'Unable to set packet capture filter';

my $mysqlport = MYSQL_PORT;

# Print the header before the output is displayed
print "+------------------------------------------------+\n";
print "|   MySQL Live SQL command monitor (Ver. $ver)   |\n";
print "+------------------------------------------------+\n";
print "| Filter params:
|   Interface: $dev
|   IP Address: $Address
|   NetMask: $Netmask
|   MTU: $mtu
|   Src/Dst Filter: $filter_string
|   Read # of Packets: $count
|   SQL Command Types: @COM_QUERY_TYPES
|   MySQL Port: $mysqlport
";
print "+------------------------------------------------+\n";

#   Set callback function and initiate packet capture loop
Net::Pcap::loop($object, $count, \&process_packets, '' ) ||
    die 'Unable to perform packet capture';

Net::Pcap::close($object);


sub process_packets {
  my ($user_data, $header, $packet) = @_;

  ## Strip the ethernet and IP headers
  my $tcp_obj = NetPacket::TCP->decode(ip_strip(eth_strip($packet)));

  ## Strip ethernet encapsulation of captured packet
  my $tcp_obj1 = NetPacket::Ethernet::strip($packet);

  ## Decode contents of TCP/IP packet contained within
  ## captured ethernet packet
  my $ip = NetPacket::IP->decode($tcp_obj1);
  my $tcp = NetPacket::TCP->decode($ip->{'data'});

  ## If dest_port (mysql port), grab the payload and parse it
  if ($tcp_obj->{dest_port} == MYSQL_PORT)
  {
     my $data = $tcp_obj->{data};
     return unless $data;

     my $len = unpack('C3',$data);
     return unless $len > 0;

     my $type = unpack('C',substr($data,4));
     foreach (@COM_QUERY_TYPES)
     {
       if ($type == $_)
       {
          my $Data = substr($data,5);
          if ( $do_not_print ne '' )
          {
            printf ( "SQL_CMD from %s to %s: \n  %s: %s\n\n", "$ip->{'src_ip'}", "$ip->{'dest_ip'}", "$cmdcode{$type}", "$Data" ) if $Data !~ /$do_not_print/;
          }
          else
          {
            printf ( "SQL_CMD from %s to %s: \n  %s: %s\n\n", "$ip->{'src_ip'}", "$ip->{'dest_ip'}", "$cmdcode{$type}", "$Data" );
          }
      }
    }
  }
}


#
#   POD info for perldoc
#

=head1 NAME

mysqlsniff.pl: MySQL query sniffer

=head1 VERSION

 0.11 Lance V added support for all command types.
      Added filtering
      Added exit after n packets
      Added Compiled packet filter
      Added many features from http://www.perlmonks.org/?node_id=170648
 0.10

=head1 USAGE

    mysqlsniff.pl [interface]

interface is optional, defaulting to the interface returned by
Net::Pcap::lookupdev()

Note: PCAP will require root/sudo access.

=head1 DESCRIPTION

mysqlsniff.pl is a query sniffer for mysql.  It sniffs the network with
pcap, extracts queries from mysql packets, and prints them on standard
output.

=head1 PROTOCOL

see: L
COM_QUERY packets look like this:

    0f 00 00 00
    03
    "show databases"

The first three bytes are length, the fourth is the packet number for
this transaction.  I'm ignoring the packet number and only looking at
the length, to make sure it's nonzero before continuing.

The fifth byte is the command type.  QUERY is 03.  (A complete list
can be found in mysql header files).

The rest (in the case of QUERY packets) is the query string.

=head1 AUTHOR

 Lance Vermilion 
 Ian Kilgore 

=head1 COPYRIGHT & LICENSE

Copyright 2007 iContact, all rights reserved.

This program is free software; you can redistribute it and/or modify it
under the same terms as Perl itself.

=head1 TODO

1. Fix the binary + ascii output generated with the EXECUTE query. it should only show ascii ouput and filter the binary output.

2. Find out why the CLOSE_STMT does not show any statment for closing.

3. Inspect the errors that are returned convert them to human readable.

=head1 SEE ALSO

L

=cut

October 13, 2008

MySQL, InnoDB rename/copy data from one table to another

Filed under: mysql — Tags: , — lancevermilion @ 4:32 pm

Create a new table with exactly the same table structure as sys_log.
CREATE TABLE `sys_tmp` (
`timestamp` bigint(20) unsigned NOT NULL,
`field1` varchar(255) NOT NULL,
`field2` varchar(255) default NULL,
`field3` varchar(255) default NULL,
`field4` varchar(255) default NULL,
`field5` varchar(255) default NULL,
`field6` varchar(255) default NULL,
`field7` varchar(255) default NULL,
`field8` varchar(255) default NULL,
`time_sent` varchar(255) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Rename the original “sys_log” table to a new name “syslog_backup_table” and rename your temp syslog table (sys_tmp) to the orignal name syslog table name “sys_log”;
rename table sys_log TO syslog_backup_table, sys_tmp to sys_log;

insert data into sys_log from “syslog_backup_table” where the timestamp field is greater than x time.
insert into sys_log select * from syslog_backup_table where timestamp > 1220227200000;

Older Posts »

Blog at WordPress.com.