Learned a little bit about importing data from MySQL into HDFS using Sqoop

I have a chance to read a book - Hadoop Real-World Solutions Cookbook(Thank you ^______^). It pops up in my head, why I have never tested about Sqoop. As you know sqoop is a tool designed for efficiently transferring bulk data between Apache Hadoop and structured datastores such as relational databases. So, I wanna test a little bit about it. I choose to read data from mysql into HDFS.
I assume my data in mysql

[surachart@centos ~]$ mysql -u surachart mydb -p < a.sql
Enter password:
[surachart@centos ~]$ mysql -u surachart mydb -p
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.1.69 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select count(*) from mytable;
+----------+
| count(*) |
+----------+
|  1295462 |
+----------+
1 row in set (0.00 sec)

mysql>

After I have my data, I download Sqoop (binary) and test it.

[surachart@centos ~]$ ls sqoop-1.4.3.bin__hadoop-0.20.tar.gz
sqoop-1.4.3.bin__hadoop-0.20.tar.gz
[surachart@centos ~]$ tar zxf sqoop-1.4.3.bin__hadoop-0.20.tar.gz

[surachart@centos ~]$ cd  sqoop-1.4.3.bin__hadoop-0.20
[surachart@centos sqoop-1.4.3.bin__hadoop-0.20]$ cd bin/
[surachart@centos bin]$ pwd
/home/surachart/sqoop-1.4.3.bin__hadoop-0.20/bin

[surachart@centos bin]$ ./sqoop
Error: /usr/lib/hadoop does not exist!
Please set $HADOOP_COMMON_HOME to the root of your Hadoop installation.
[surachart@centos bin]$ export HADOOP_COMMON_HOME=/usr
[surachart@centos bin]$ ./sqoop
Error: /usr/lib/hadoop-mapreduce does not exist!
Please set $HADOOP_MAPRED_HOME to the root of your Hadoop MapReduce installation.
[surachart@centos bin]$ export HADOOP_MAPRED_HOME=/usr
[surachart@centos bin]$ ./sqoop
Warning: /usr/lib/hbase does not exist! HBase imports will fail.
Please set $HBASE_HOME to the root of your HBase installation.
Try 'sqoop help' for usage.
[surachart@centos bin]$ export HBASE_HOME=/home/surachart/hbase
[surachart@centos bin]$ ./sqoop
Try 'sqoop help' for usage.

[surachart@centos bin]$ ./sqoop help
usage: sqoop COMMAND [ARGS]

Available commands:
  codegen            Generate code to interact with database records
  create-hive-table  Import a table definition into Hive
  eval               Evaluate a SQL statement and display the results
  export             Export an HDFS directory to a database table
  help               List available commands
  import             Import a table from a database to HDFS
  import-all-tables  Import tables from a database to HDFS
  job                Work with saved jobs
  list-databases     List available databases on a server
  list-tables        List available tables in a database
  merge              Merge results of incremental imports
  metastore          Run a standalone Sqoop metastore
  version            Display version information

See 'sqoop help COMMAND' for information on a specific command.

[surachart@centos bin]$ hadoop fs -mkdir /user/surachart/import
[surachart@centos bin]$ hadoop fs -ls /user/surachart/import
[surachart@centos bin]$
[surachart@centos bin]$ ./sqoop import -m 1 --connect jdbc:mysql://localhost:3306/mydb --username surachart --password password --table mytable --target-dir /user/surachart/import
13/05/08 12:11:42 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/05/08 12:11:42 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/05/08 12:11:42 INFO tool.CodeGenTool: Beginning code generation
13/05/08 12:11:43 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeException: Could not load db driver class: com.mysql.jdbc.Driver
java.lang.RuntimeException: Could not load db driver class: com.mysql.jdbc.Driver
        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:716)
        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.java:52)
        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:605)
        at org.apache.sqoop.manager.SqlManager.execute(SqlManager.java:628)
        at org.apache.sqoop.manager.SqlManager.getColumnTypesForRawQuery(SqlManager.java:235)
        at org.apache.sqoop.manager.SqlManager.getColumnTypes(SqlManager.java:219)
        at org.apache.sqoop.manager.ConnManager.getColumnTypes(ConnManager.java:283)
        at org.apache.sqoop.orm.ClassWriter.getColumnTypes(ClassWriter.java:1255)
        at org.apache.sqoop.orm.ClassWriter.generate(ClassWriter.java:1072)
        at org.apache.sqoop.tool.CodeGenTool.generateORM(CodeGenTool.java:82)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:390)
        at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:476)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:238)

Note: On Sqoop no mysql lib, So Download "mysql-connector-java-5.1.25" on http://dev.mysql.com/downloads/connector/j/ and copy it to sqoop lib path.

[surachart@centos ~]$ ls mysql-connector-java-5.1.25.zip
mysql-connector-java-5.1.25.zip
[surachart@centos ~]$ unzip mysql-connector-java-5.1.25.zip
[surachart@centos ~]$ cp mysql-connector-java-5.1.25/mysql-connector-java-5.1.25-bin.jar sqoop-1.4.3.bin__hadoop-0.20/lib/

After everything should be fine. Try again.

[surachart@centos ~]$ cd sqoop-1.4.3.bin__hadoop-0.20/bin/
[surachart@centos bin]$ ./sqoop import --direct -m 1 --connect jdbc:mysql://localhost:3306/mydb --username surachart --password password --table mytable --target-dir /user/surachart/import
13/05/08 12:27:54 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/05/08 12:27:54 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/05/08 12:27:54 INFO tool.CodeGenTool: Beginning code generation
13/05/08 12:27:56 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `mytable` AS t LIMIT 1
13/05/08 12:27:56 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `mytable` AS t LIMIT 1
13/05/08 12:27:56 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr
Note: /tmp/sqoop-surachart/compile/7d652c85de6562a56d07c0b4017e3cd4/mytable.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/05/08 12:28:04 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-surachart/compile/7d652c85de6562a56d07c0b4017e3cd4/mytable.jar
13/05/08 12:28:04 INFO manager.DirectMySQLManager: Beginning mysqldump fast path import
13/05/08 12:28:04 INFO mapreduce.ImportJobBase: Beginning import of mytable
13/05/08 12:28:10 INFO mapred.JobClient: Cleaning up the staging area hdfs://centos:8020/user/surachart/.staging/job_201305081226_0002
13/05/08 12:28:10 ERROR security.UserGroupInformation: PriviledgedActionException as:surachart cause:org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory /user/surachart/import already exists
13/05/08 12:28:10 ERROR tool.ImportTool: Encountered IOException running import job: org.apache.hadoop.mapred.FileAlreadyExistsException: Output directory /user/surachart/import already exists
        at org.apache.hadoop.mapreduce.lib.output.FileOutputFormat.checkOutputSpecs(FileOutputFormat.java:137)
        at org.apache.hadoop.mapred.JobClient$2.run(JobClient.java:949)
        at org.apache.hadoop.mapred.JobClient$2.run(JobClient.java:912)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:396)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1136)
        at org.apache.hadoop.mapred.JobClient.submitJobInternal(JobClient.java:912)
        at org.apache.hadoop.mapreduce.Job.submit(Job.java:500)
        at org.apache.hadoop.mapreduce.Job.waitForCompletion(Job.java:530)
        at org.apache.sqoop.mapreduce.ImportJobBase.doSubmitJob(ImportJobBase.java:173)
        at org.apache.sqoop.mapreduce.ImportJobBase.runJob(ImportJobBase.java:151)
        at org.apache.sqoop.mapreduce.ImportJobBase.runImport(ImportJobBase.java:221)
        at org.apache.sqoop.manager.DirectMySQLManager.importTable(DirectMySQLManager.java:92)
        at org.apache.sqoop.tool.ImportTool.importTable(ImportTool.java:403)
        at org.apache.sqoop.tool.ImportTool.run(ImportTool.java:476)
        at org.apache.sqoop.Sqoop.run(Sqoop.java:145)
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:65)
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:181)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:220)
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:229)
        at org.apache.sqoop.Sqoop.main(Sqoop.java:238)

[surachart@centos bin]$ ./sqoop import --direct -m 1 --connect jdbc:mysql://localhost:3306/mydb --username surachart --password password --table mytable --target-dir /user/surachart/import/mytable
13/05/08 12:28:23 WARN tool.BaseSqoopTool: Setting your password on the command-line is insecure. Consider using -P instead.
13/05/08 12:28:24 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset.
13/05/08 12:28:24 INFO tool.CodeGenTool: Beginning code generation
13/05/08 12:28:26 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `mytable` AS t LIMIT 1
13/05/08 12:28:26 INFO manager.SqlManager: Executing SQL statement: SELECT t.* FROM `mytable` AS t LIMIT 1
13/05/08 12:28:26 INFO orm.CompilationManager: HADOOP_MAPRED_HOME is /usr
Note: /tmp/sqoop-surachart/compile/98909e84f7e2215902ebdbcca40cfa9f/mytable.java uses or overrides a deprecated API.
Note: Recompile with -Xlint:deprecation for details.
13/05/08 12:28:33 INFO orm.CompilationManager: Writing jar file: /tmp/sqoop-surachart/compile/98909e84f7e2215902ebdbcca40cfa9f/mytable.jar
13/05/08 12:28:34 INFO manager.DirectMySQLManager: Beginning mysqldump fast path import
13/05/08 12:28:34 INFO mapreduce.ImportJobBase: Beginning import of mytable
13/05/08 12:28:42 INFO mapred.JobClient: Running job: job_201305081226_0003
13/05/08 12:28:43 INFO mapred.JobClient:  map 0% reduce 0%
13/05/08 12:29:34 INFO mapred.JobClient:  map 100% reduce 0%
13/05/08 12:29:56 INFO mapred.JobClient: Job complete: job_201305081226_0003
13/05/08 12:29:56 INFO mapred.JobClient: Counters: 18
13/05/08 12:29:56 INFO mapred.JobClient:   Job Counters
13/05/08 12:29:56 INFO mapred.JobClient:     SLOTS_MILLIS_MAPS=54352
13/05/08 12:29:56 INFO mapred.JobClient:     Total time spent by all reduces waiting after reserving slots (ms)=0
13/05/08 12:29:56 INFO mapred.JobClient:     Total time spent by all maps waiting after reserving slots (ms)=0
13/05/08 12:29:56 INFO mapred.JobClient:     Launched map tasks=1
13/05/08 12:29:56 INFO mapred.JobClient:     SLOTS_MILLIS_REDUCES=0
13/05/08 12:29:56 INFO mapred.JobClient:   File Output Format Counters
13/05/08 12:29:56 INFO mapred.JobClient:     Bytes Written=23687032
13/05/08 12:29:56 INFO mapred.JobClient:   FileSystemCounters
13/05/08 12:29:56 INFO mapred.JobClient:     HDFS_BYTES_READ=87
13/05/08 12:29:56 INFO mapred.JobClient:     FILE_BYTES_WRITTEN=36476
13/05/08 12:29:56 INFO mapred.JobClient:     HDFS_BYTES_WRITTEN=23687032
13/05/08 12:29:56 INFO mapred.JobClient:   File Input Format Counters
13/05/08 12:29:56 INFO mapred.JobClient:     Bytes Read=0
13/05/08 12:29:56 INFO mapred.JobClient:   Map-Reduce Framework
13/05/08 12:29:56 INFO mapred.JobClient:     Map input records=1
13/05/08 12:29:56 INFO mapred.JobClient:     Physical memory (bytes) snapshot=73830400
13/05/08 12:29:56 INFO mapred.JobClient:     Spilled Records=0
13/05/08 12:29:56 INFO mapred.JobClient:     CPU time spent (ms)=16210
13/05/08 12:29:56 INFO mapred.JobClient:     Total committed heap usage (bytes)=29818880
13/05/08 12:29:56 INFO mapred.JobClient:     Virtual memory (bytes) snapshot=1179168768
13/05/08 12:29:56 INFO mapred.JobClient:     Map output records=1295462
13/05/08 12:29:56 INFO mapred.JobClient:     SPLIT_RAW_BYTES=87
13/05/08 12:29:56 INFO mapreduce.ImportJobBase: Transferred 22.5897 MB in 81.613 seconds (283.4336 KB/sec)
13/05/08 12:29:56 INFO mapreduce.ImportJobBase: Retrieved 1295462 records.

Check data in HDFS.

[surachart@centos bin]$ hadoop fs -ls /user/surachart/import
Found 1 items
drwx------   - surachart surachart          0 2013-05-08 12:29 /user/surachart/import/mytable
[surachart@centos bin]$ hadoop fs -ls /user/surachart/import/mytable
Found 2 items
-rw-------   3 surachart surachart          0 2013-05-08 12:29 /user/surachart/import/mytable/_SUCCESS
-rw-------   3 surachart surachart   23687032 2013-05-08 12:29 /user/surachart/import/mytable/part-m-00000

Note: data in /user/surachart/import/mytable/part-m-00000 file, that is CSV type.

Learn ulogd - set ulogd with mysql

In this post, it looks like nothing. It was something fun for me. Because i was interested in shorewall and ulogd. So, I just thought how I keep log in mysql. This post wasn't value much, but helped me to learning something new.
I installed what packages were necessary (disabled iptables service). It was easy for you to use "yum" and "systemctl". First of all, I just ensured I have all packages.

[root@fedora ~]# yum list shorewall shorewall-core  ulogd ulogd-mysql mysql-server
Loaded plugins: langpacks, presto, refresh-packagekit
Installed Packages
mysql-server.i686                                                    5.5.24-1.fc17                                                      @updates
shorewall.noarch                                                     4.5.4-1.fc17                                                       @updates
shorewall-core.noarch                                                4.5.4-1.fc17                                                       @updates
ulogd.i686                                                           2.0.0-2.beta4.fc17                                                 @fedora
ulogd-mysql.i686                                                     2.0.0-2.beta4.fc17                                                 @fedora

In shorewall configuration. I just set policy for logging to NFLOG. If you are interested about it. You can read on http://www.shorewall.net/  (I did not emphasize how to configure shorewall, I just figured ulogd with mysql).

[root@fedora ~]# cat /etc/shorewall/policy
.
.
.
$FW            net                ACCEPT NFLOG
net            all                ACCEPT  NFLOG
all            all                REJECT  NFLOG

Then I configured ulogd with mysql.  
Note: mysql-ulogd2.sql file, If you downloaded "ulogd" from source. You will see it in compress file. 

[root@fedora ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database nulog;
Query OK, 1 row affected (0.00 sec)
mysql>mysql> grant all privileges on nulog.* to surachart@localhost identified by 'password';

Query OK, 0 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec) 

[root@fedora ~]# mysql -h localhost -u surachart -p nulog
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.24 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> source mysql-ulogd2.sql

After mysql no error, I configured ulogd.conf. 

[root@fedora ~]# cp /etc/ulogd.conf /etc/ulogd.conf-orig
[root@fedora ~]# vi /etc/ulogd.conf
[root@fedora ~]# diff /etc/ulogd.conf-orig /etc/ulogd.conf
45c45
< #plugin="/usr/lib/ulogd/ulogd_output_MYSQL.so"
---
> plugin="/usr/lib/ulogd/ulogd_output_MYSQL.so"
77c77
< #stack=log2:NFLOG,base1:BASE,ifi1:IFINDEX,ip2bin1:IP2BIN,mac2str1:HWHDR,mysql1:MYSQL
---
> stack=log1:NFLOG,base1:BASE,ifi1:IFINDEX,ip2bin1:IP2BIN,mac2str1:HWHDR,mysql1:MYSQL
166c166
< user="nupik"
---
> user="surachart"
168c168
< pass="changeme"
---
> pass="password"

In configuration file, I used  log1 in stack.
After I ensured configuration, that's fine. I just started ulogd and shorewall.

[root@fedora ~]# service ulogd start
Starting ulogd (via systemctl):                            [  OK  ]
[root@fedora ~]# service shorewall start
Redirecting to /bin/systemctl  start shorewall.service

[root@fedora ~]# /bin/systemctl  status  ulogd.service
ulogd.service - LSB: start and stop ulogd
          Loaded: loaded (/etc/rc.d/init.d/ulogd)
          Active: active (running) since Thu, 12 Jul 2012 15:10:28 +0700; 17min ago
         Process: 26609 ExecStop=/etc/rc.d/init.d/ulogd stop (code=exited, status=0/SUCCESS)
         Process: 26632 ExecStart=/etc/rc.d/init.d/ulogd start (code=exited, status=0/SUCCESS)
          CGroup: name=systemd:/system/ulogd.service
                  â”” 26638 /usr/sbin/ulogd -d
Jul 12 15:10:28 fedora.surachartopun.com ulogd[26632]: [33B blob data]
[root@fedora ~]# /bin/systemctl  status  shorewall.service
shorewall.service - Shorewall IPv4 firewall
          Loaded: loaded (/usr/lib/systemd/system/shorewall.service; enabled)
          Active: active (exited) since Thu, 12 Jul 2012 15:10:36 +0700; 17min ago
         Process: 26475 ExecStop=/sbin/shorewall $OPTIONS stop (code=exited, status=0/SUCCESS)
         Process: 26649 ExecStart=/sbin/shorewall $OPTIONS start (code=exited, status=0/SUCCESS)
          CGroup: name=systemd:/system/shorewall.service
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Setting up Martian Logging...
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Setting up Proxy ARP...
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Setting up Traffic Control...
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Preparing iptables-restore input...
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Running /sbin/iptables-restore...
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: IPv4 Forwarding Enabled
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Processing /etc/shorewall/start ...
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: Processing /etc/shorewall/started ...
Jul 12 15:10:36 fedora.surachartopun.com logger[26915]: Shorewall started
Jul 12 15:10:36 fedora.surachartopun.com shorewall[26649]: done.

 [root@fedora ~]# iptables -LChain INPUT (policy DROP)
target     prot opt source               destination
net2fw     all  --  anywhere             anywhere
ACCEPT     all  --  anywhere             anywhere
Reject     all  --  anywhere             anywhere
NFLOG      all  --  anywhere             anywhere             nflog-prefix  --nflog-prefix
reject     all  --  anywhere             anywhere            [goto]
Chain FORWARD (policy DROP)
target     prot opt source               destination
Reject     all  --  anywhere             anywhere
NFLOG      all  --  anywhere             anywhere             nflog-prefix  --nflog-prefix
reject     all  --  anywhere             anywhere            [goto]
Chain OUTPUT (policy DROP)
target     prot opt source               destination
fw2net     all  --  anywhere             anywhere
ACCEPT     all  --  anywhere             anywhere
Reject     all  --  anywhere             anywhere
NFLOG      all  --  anywhere             anywhere             nflog-prefix  --nflog-prefix
reject     all  --  anywhere             anywhere            [goto]
Chain Broadcast (1 references)
target     prot opt source               destination
DROP       all  --  anywhere             anywhere             ADDRTYPE match dst-type BROADCAST
DROP       all  --  anywhere             anywhere             ADDRTYPE match dst-type MULTICAST
DROP       all  --  anywhere             anywhere             ADDRTYPE match dst-type ANYCAST
DROP       all  --  anywhere             base-address.mcast.net/4
Chain Invalid (1 references)
target     prot opt source               destination
DROP       all  --  anywhere             anywhere             ctstate INVALID
Chain NotSyn (1 references)
target     prot opt source               destination
DROP       tcp  --  anywhere             anywhere             tcpflags:! FIN,SYN,RST,ACK/SYN
Chain Reject (3 references)
target     prot opt source               destination
           all  --  anywhere             anywhere
reject     tcp  --  anywhere             anywhere             tcp dpt:auth /* --co */
Broadcast  all  --  anywhere             anywhere
ACCEPT     icmp --  anywhere             anywhere             icmp fragmentation-needed /* --comment */
ACCEPT     icmp --  anywhere             anywhere             icmp time-exceeded /* --comment */
Invalid    all  --  anywhere             anywhere
reject     udp  --  anywhere             anywhere             multiport dports epmap,microsoft-ds /* --c */
reject     udp  --  anywhere             anywhere             udp dpts:netbios-ns:netbios-ssn /* --c */
reject     udp  --  anywhere             anywhere             udp spt:netbios-ns dpts:1024:65535 /* --c */
reject     tcp  --  anywhere             anywhere             multiport dports epmap,netbios-ssn,microsoft-ds /* --c */
DROP       udp  --  anywhere             anywhere             udp dpt:ssdp /* --co */
NotSyn     tcp  --  anywhere             anywhere
DROP       udp  --  anywhere             anywhere             udp spt:domain /* --comment */
Chain dynamic (1 references)
target     prot opt source               destination
Chain fw2net (1 references)
target     prot opt source               destination
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
NFLOG      all  --  anywhere             anywhere             nflog-prefix  --nflog-prefix
ACCEPT     all  --  anywhere             anywhere
Chain logdrop (0 references)
target     prot opt source               destination
DROP       all  --  anywhere             anywhere
Chain logreject (0 references)
target     prot opt source               destination
reject     all  --  anywhere             anywhere
Chain net2fw (1 references)
target     prot opt source               destination
dynamic    all  --  anywhere             anywhere             ctstate INVALID,NEW
ACCEPT     all  --  anywhere             anywhere             ctstate RELATED,ESTABLISHED
NFLOG      all  --  anywhere             anywhere             nflog-prefix  --nflog-prefix
ACCEPT     all  --  anywhere             anywhere
Chain reject (9 references)
target     prot opt source               destination
DROP       all  --  anywhere             anywhere             ADDRTYPE match src-type BROADCAST
DROP       all  --  base-address.mcast.net/4  anywhere
DROP       igmp --  anywhere             anywhere
REJECT     tcp  --  anywhere             anywhere             reject-with tcp-reset
REJECT     udp  --  anywhere             anywhere             reject-with icmp-port-unreachable
REJECT     icmp --  anywhere             anywhere             reject-with icmp-host-unreachable
REJECT     all  --  anywhere             anywhere             reject-with icmp-host-prohibited
Chain sfilter (0 references)
target     prot opt source               destination
LOG        all  --  anywhere             anywhere             LOG level info prefix "--log-prefix"
DROP       all  --  anywhere             anywhere
Chain shorewall (0 references)
target     prot opt source               destination

Test & Test. After that, I checked in mysql

mysql> SELECT tcp_dport,count(*) from view_tcp GROUP BY view_tcp.tcp_dport;
+-----------+----------+
| tcp_dport | count(*) |
+-----------+----------+
|        22 |        7 |
|        80 |        3 |
|       443 |        1 |
+-----------+----------+
3 rows in set (0.00 sec)

It looked like work now. I plan to learn more about it.  
so far so good :)

MySQL Troubleshooting by Sveta Smirnova

I believe a lot of people who works with Information Technology, they know MySQL. Anyway, A lot of companies use it. For example, Facebook, maybe your company!

I wrote this article. I don’t want to talking about MySQL history. I just wrote about a book, MySQL Troubleshooting What To Do When Queries Don't Work by Sveta Smirnova.
Why I interest in this book. It likes when you interest/work with something. You rather want to know how to install, upgrade, maintain, perform, manage, tune and … troubleshoot it. Troubleshooting, that’s important.
MySQL Troubleshooting by Sveta Smirnova, that provides the background, tools, and expert steps for solving problems from simple to complex - whether data you thought you inserted doesn’t turn up in a query, or the entire database is corrupt because of a server failure. With this book in hand, you’ll work with more confidence.

I rather interested in this book. I expected a lot for something new what I learn about troubleshooting. This book has 7 chapters. On the chapter one, It’s explained the simplest about something what I should know before; SQL syntax, MySQL command, some variables and etc. On the chapter two, “You are Not Alone: Concurrency Issues” – Concurrency is important for Database. This chapter is helpful for it. What I learn? For example: Lock and Transactions, Matadata Locking, Monitoring InnoDB Transaction and Concurrency Problems, etc. I believe a lot of DBAs see the lock/concurrency problem. It’s good chapter, that‘s helpful. Anyway, MySQL has a lot of options/variables, So It’s necessary to learn about them. The chapter three, I learned about some options/variables, that effect with database. Good!

That was a little from this book. I rather believe this book is useful for someone who works with MySQL as DBA or Developer.

In this book, it has a good paragraph arrangement. It helps readers easy for reading. It has a lot of examples. It's easy-understand. I think readers can read and practice in the same time. Anyway, I still believe this book be able to figure readers out for MySQL Troubleshooting idea. By the way, You can read Free Sampler.

What I hope to see more? On the Chapter 4 "MySQL’s Environment", I think It's better to add more examples. Maybe, about OS command-line to help for Troubleshooting :)

just installed mysql-5.6.2-labs-innodb-memcached on linux 64bits

After I installed "mysql-5.6.2-labs-innodb-memcached.tar.gz" on linux 32bits. Could not restart mysql server (error something after start Mysql), so tested it on linux 64bits. It's OK.

# mkdir build
# cd build
# CC="gcc -fPIC" cmake ../mysql-5.6.2-labs-innodb-memcached
# make
# make install
# cd /usr/local/mysql
.
.
# bin/mysqld_safe --user=mysql &
# bin/mysql < scripts/innodb_memcached_config.sql

mysql> INSTALL PLUGIN daemon_memcached SONAME 'libmemcached.so';

mysql> show plugins;
+--------------------------+--------+--------------------+-----------------+---------+
| Name | Status | Type | Library | License |
+--------------------------+--------+--------------------+-----------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| daemon_memcached | ACTIVE | DAEMON | libmemcached.so | GPL |
+--------------------------+--------+--------------------+-----------------+---------+

then test to Run "memcapable" from https://launchpad.net/libmemcached but have to install memcached http://memcached.org before:

# memcapable
ascii quit [pass]
ascii version [pass]
ascii verbosity [pass]
ascii set [pass]
ascii set noreply [pass]
ascii get [pass]
ascii gets [pass]
ascii mget [pass]
ascii flush [pass]
ascii flush noreply [pass]
ascii add [pass]
ascii add noreply [pass]
ascii replace [pass]
ascii replace noreply [pass]
ascii cas [pass]
ascii cas noreply [pass]
ascii delete [pass]
ascii delete noreply [pass]
ascii incr [pass]
ascii incr noreply [pass]
ascii decr [pass]
ascii decr noreply [pass]
ascii append [pass]
ascii append noreply [pass]
ascii prepend [pass]
ascii prepend noreply [pass]
ascii stat [pass]
binary noop [pass]
binary quit [pass]
binary quitq [pass]
binary set [pass]
binary setq [pass]
binary flush [pass]
binary flushq [pass]
binary add [pass]
binary addq [pass]
binary replace [pass]
binary replaceq [pass]
binary delete [pass]
binary deleteq [pass]
binary get [pass]
binary getq [pass]
binary getk [pass]
binary getkq [pass]
binary incr [pass]
binary incrq [pass]
binary decr [pass]
binary decrq [pass]
binary version [pass]
binary append [pass]
binary appendq [pass]
binary prepend [pass]
binary prependq [pass]
binary stat [pass]

# ./bin/mysql test

mysql> select * from demo_test;
+------+------+----------------------+------+-------------------+------+------+----------+------+------+------+
| cx | cy | c1 | cz | c2 | ca | CB | c3 | cu | c4 | C5 |
+------+------+----------------------+------+-------------------+------+------+----------+------+------+------+
| NULL | NULL | test_binary_add | NULL | þÊ­Þï¾­Þ | NULL | NULL | 0 | NULL | 103 | NULL |
| NULL | NULL | test_binary_addq | NULL | þÊ­Þï¾­Þ | NULL | NULL | 0 | NULL | 104 | NULL |
| NULL | NULL | test_binary_replace | NULL | þÊ­Þï¾­Þ | NULL | NULL | 0 | NULL | 115 | NULL |
| NULL | NULL | test_binary_replaceq | NULL | þÊ­Þï¾­Þ | NULL | NULL | 0 | NULL | 126 | NULL |
| NULL | NULL | test_binary_get | NULL | test_binary_get | NULL | NULL | 0 | NULL | 129 | NULL |
| NULL | NULL | test_binary_getq | NULL | test_binary_getq | NULL | NULL | 0 | NULL | 130 | NULL |
| NULL | NULL | test_binary_getk | NULL | test_binary_getk | NULL | NULL | 0 | NULL | 131 | NULL |
| NULL | NULL | test_binary_getkq | NULL | test_binary_getkq | NULL | NULL | 0 | NULL | 132 | NULL |
| NULL | NULL | test_binary_incr | NULL | 9 | NULL | NULL | 57580232 | NULL | 142 | NULL |
| NULL | NULL | test_binary_incrq | NULL | 9 | NULL | NULL | 57580232 | NULL | 152 | NULL |
| NULL | NULL | test_binary_decr | NULL | 0 | NULL | NULL | 57580232 | NULL | 163 | NULL |
| NULL | NULL | test_binary_decrq | NULL | 0 | NULL | NULL | 57580232 | NULL | 174 | NULL |
| NULL | NULL | test_binary_append | NULL | hello world | NULL | NULL | 0 | NULL | 176 | NULL |
+------+------+----------------------+------+-------------------+------+------+----------+------+------+------+

ld: libmemcached_utilities.a(config_parser.c.o): relocation R_X86_64_32

just installed mysql-5.6.2-labs-innodb-memcached on linux X86_64 http://labs.mysql.com

# cmake ../mysql-5.6.2-labs-innodb-memcached
# make
/usr/bin/ld: libmemcached_utilities.a(config_parser.c.o): relocation R_X86_64_32 against `a local symbol' can not be used when making a shared object; recompile with -fPIC
libmemcached_utilities.a: could not read symbols: Bad value

So, Changed to :

# CC="gcc -fPIC" cmake ../mysql-5.6.2-labs-innodb-memcached
# make

< No Error >

# make install

just install mysql-5.6.2-labs-innodb-memcached for test

read Oracle Announces First MySQL 5.6 Development Milestone Release and
and saw new features in MySQL 5.6 development milestone release, and interested NoSQL access to InnoDB via the Memcached protocol. I checked http://labs.mysql.com

First Idea... just tested to install innodb_memcached, so downloaded "mysql-5.6.2-labs-innodb-memcached.tar.gz"
*** Install prerequisite - libevent - it should be libevent 1.4.3

# ls mysql-5.6.2-labs-innodb-memcached.tar.gz
mysql-5.6.2-labs-innodb-memcached.tar.gz

# tar zxvf mysql-5.6.2-labs-innodb-memcached.tar.gz

# cd mysql-5.6.2-labs-innodb-memcached/plugin/innodb_memcached/libevent
# sh autogen.sh
# ./configure
# make
# make install

then to SRC PATH and installed

# mkdir build

# cd build
# cmake ../mysql-5.6.2-labs-innodb-memcached
# make
# make install
# cd /usr/local/mysql
# chown -R mysql .
# chgrp -R mysql .
# scripts/mysql_install_db --user=mysql
# chown -R root .
# chown -R mysql data
# cp support-files/my-small.cnf /etc/my.cnf
# bin/mysqld_safe --user=mysql &

checked version and plugins

mysql> select version();
+-----------------------------+
| version() |
+-----------------------------+
| 5.6.2-labs-innodb-memcached |
+-----------------------------+

mysql> show plugins;
+--------------------------+--------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+--------------------------+--------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
+--------------------------+--------+--------------------+---------+---------+

then read "README-innodb_memcached" file.

# less README-innodb_memcached

had to create database "innodb_memcache" and install plugin

# ./bin/mysql < scripts/innodb_memcached_config.sql

mysql> INSTALL PLUGIN daemon_memcached SONAME 'libmemcached.so';

mysql> show plugins;
+--------------------------+--------+--------------------+-----------------+---------+
| Name | Status | Type | Library | License |
+--------------------------+--------+--------------------+-----------------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_PAGE_LRU | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_BUFFER_POOL_STATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_METRICS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_TABLESTATS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_INDEXES | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_COLUMNS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FIELDS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_SYS_FOREIGN_COLS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| PERFORMANCE_SCHEMA | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL |
| daemon_memcached | ACTIVE | DAEMON | libmemcached.so | GPL |
+--------------------------+--------+--------------------+-----------------+---------+

tested it out..

# telnet 127.0.0.1 11211
Trying 127.0.0.1...
Connected to localhost.localdomain (127.0.0.1).
Escape character is '^]'.
set a11 0 0 9
123456789
STORED

ERROR
get a11
VALUE a11 0 9
123456789
END

However.... I stopped mysql server and started again

# ./bin/mysqladmin shutdown

# ./bin/mysqld_safe --user=mysql &
110412 01:03:00 mysqld_safe Logging to '/usr/local/mysql/data/linuxtest01.err'.
110412 01:03:00 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
110412 01:03:02 mysqld_safe mysqld from pid file /usr/local/mysql/data/linuxtest01.pid ended

In /usr/local/mysql/data/linuxtest01.err file:
110412 1:03:01 InnoDB: Waiting for the background threads to start
110412 1:03:02 InnoDB: 1.2.2 started; log sequence number 1616143
Failed to open library "/usr/local/mysql/lib/plugin/innodb_engine.so<99>": /usr/local/mysql/lib/plugin/innodb_engine.so
<99>: cannot open shared object file: No such file or directory

this ""/usr/local/mysql/lib/plugin/innodb_engine.so<99>" is mistaken, after installed "daemon_memcached" plugin.
OK... it's just testing.

By the way, If you need to test... and read more... check at Get started with InnoDB Memcached Daemon plugin

Upgrade Mysql 5.5.7 to 5.5.8 from SOURCE

After I tested to install MySQL 5.5.8 from source. I have upgraded MySQL 5.5.7(installed from source) to 5.5.8

mysql> select version();
+--------------+
| version() |
+--------------+
| 5.5.7-rc-log |
+--------------+

My Install Prefix = /usr/local/MySQL-5.5
My Data Dir = /usr/local/MySQL-5.5/var
My Configuration Dir = /etc

So, I use some options.

# mkdir build
# cd build
# cmake ../mysql-5.5.8 -DCMAKE_INSTALL_PREFIX="/usr/local/MySQL-5.5" -DMYSQL_DATADIR="/usr/local/MySQL-5.5/var" -DSYSCONFDIR="/etc"
# make
# /usr/local/MySQL-5.5/bin/mysqladmin shutdown
# make install

start MySQL, but Error!!!

# /usr/local/MySQL-5.5/bin/mysqld_safe --user=mysql &
101227 12:47:02 mysqld_safe Logging to '/usr/local/MySQL-5.5/data/oratest.surachartopun.com.err'.
101227 12:47:02 mysqld_safe Starting mysqld daemon with databases from /usr/local/MySQL-5.5/data
101227 12:47:02 mysqld_safe mysqld from pid file /usr/local/MySQL-5.5/data/oratest.surachartopun.com.pid ended

Something wrong "/usr/local/MySQL-5.5/data" PATH, It should be "/usr/local/MySQL-5.5/var"
then Modify /usr/local/MySQL-5.5/bin/mysqld_safe file:

# Try where the binary installs put it
if test -d $MY_BASEDIR_VERSION/data/mysql
then
DATADIR=$MY_BASEDIR_VERSION/data

-- To

# Try where the binary installs put it
if test -d $MY_BASEDIR_VERSION/var/mysql
then
DATADIR=$MY_BASEDIR_VERSION/var

and start again.

# /usr/local/MySQL-5.5/bin/mysqld_safe --user=mysql &
101227 12:49:21 mysqld_safe Logging to '/usr/local/MySQL-5.5/var/oratest.surachartopun.com.err'.
101227 12:49:21 mysqld_safe Starting mysqld daemon with databases from /usr/local/MySQL-5.5/var

Check version and data !!!

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.8-log |
+-----------+

Everything OK :)

Just install Mysql 5.5.8 from Source

I downloaded mysql source, then installed. On previous versions, we can see "configure" file, but mysql 5.5.8 (No such file)

Remark:

Preconfiguration
# groupadd mysql
# useradd -r -g mysql mysql

- Download source and install.

# ls -l mysql-5.5.8.tar.gz
-rw-r--r-- 1 root root 24258297 Dec 25 18:26 mysql-5.5.8.tar.gz
# tar zxvf mysql-5.5.8.tar.gz
# ls -l mysql-5.5.8/configure
ls: mysql-5.5.8/configure: No such file or directory

On MySQL 5.5, CMake is used as the build framework on all platforms. Check Docs!!! for source installation.

# mkdir build
# cd build/
# cmake ../mysql-5.5.8
-bash: cmake: command not found

- Need to install cmake before.

# ls -l cmake-2.8.3.tar.gz
-rw-r--r-- 1 root root 5436543 Dec 25 18:17 cmake-2.8.3.tar.gz
# tar zxvf cmake-2.8.3.tar.gz
# cd cmake-2.8.3
# ./configure
# gmake
# gmake install

- Then try again.

# cmake ../mysql-5.5.8
# make
# make install

It's installed at /usr/local/mysql PATH.

# chown -R mysql /usr/local/mysql
# chgrp -R mysql /usr/local/mysql
# /usr/local/mysql/scripts/mysql_install_db --user=mysql
# chown -R root /usr/local/mysql
# chown -R mysql /usr/local/mysql/data
# cp /usr/local/mysql/support-files/my-small.cnf /etc/my.cnf
# /usr/local/mysql/bin/mysqld_safe --user=mysql &
# /usr/local/mysql/bin/mysql
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.8 |
+-----------+

If we don't need to install default PATH (/usr/local/mysql), we can use "-DCMAKE_INSTALL_PREFIX=dir_name" option. Example:

# cmake ../mysql-5.5.8 -DCMAKE_INSTALL_PREFIX="/usr/local/MySQL-5.5"

Check more MySQL Source-Configuration Options

OK... enjoy and Merry Christmas.

Install sqlstats plugin on MySQL

sqlstats is a project for a MySQL 5.5 and up audit plugin. We can download and install.#

# ./configure --with-mysqlsrc=/root/mysql-5.5.7-rc --with-mysql=/usr/local/MySQL-5.5
# make
# make install
/usr/bin/install -c .libs/libsqlstats.so /usr/local/MySQL-5.5/lib/plugin/sqlstats.so

# cat install.sql
INSTALL PLUGIN sqlstats SONAME 'sqlstats.so';
INSTALL PLUGIN sqlstats_topsql SONAME 'sqlstats.so';
INSTALL PLUGIN sqlstats_lastsql SONAME 'sqlstats.so';
SET GLOBAL sqlstats_enable = 1;

# mysql -u root --force < install.sql

Check variables.

mysql> show variables like 'sqlstat%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| sqlstats_enable | ON |
| sqlstats_lru_stmts | 50 |
| sqlstats_top_stmts | 50 |
+--------------------+-------+

we can read more

Just MySQL Performance Schema

After I installed MySQL. I find "Performance Schema". MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level. Performance Schema is available as of MySQL 5.5.3

mysql> select version();
+--------------+
| version() |
+--------------+
| 5.5.6-rc-log |
+--------------+

Check MySQL Performance Schema.

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | OFF |
+--------------------+-------+

It's not ON... then
# /usr/local/MySQL-5.5/bin/mysqladmin shutdown

# /usr/local/MySQL-5.5/bin/mysqld_safe --user=mysql --performance_schema &

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+

Or modify /etc/my.cnf
[mysqld]
performance_schema

# /usr/local/MySQL-5.5/bin/mysqld_safe --user=mysql &

mysql> SHOW VARIABLES LIKE 'performance_schema';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| performance_schema | ON |
+--------------------+-------+

we can check variables with LIKE.

mysql> SHOW VARIABLES LIKE 'perf%';
+---------------------------------------------------+--------+
| Variable_name | Value |
+---------------------------------------------------+--------+
| performance_schema | ON |
| performance_schema_events_waits_history_long_size | 10000 |
| performance_schema_events_waits_history_size | 10 |
| performance_schema_max_cond_classes | 80 |
| performance_schema_max_cond_instances | 1000 |
| performance_schema_max_file_classes | 50 |
| performance_schema_max_file_handles | 32768 |
| performance_schema_max_file_instances | 10000 |
| performance_schema_max_mutex_classes | 200 |
| performance_schema_max_mutex_instances | 1000 |
| performance_schema_max_rwlock_classes | 20 |
| performance_schema_max_rwlock_instances | 1000 |
| performance_schema_max_table_handles | 100000 |
| performance_schema_max_table_instances | 50000 |
| performance_schema_max_thread_classes | 50 |
| performance_schema_max_thread_instances | 1000 |
+---------------------------------------------------+--------+

then tested it.

mysql> SELECT * FROM EVENTS_WAITS_CURRENT WHERE THREAD_ID = 22 \G
*************************** 1. row ***************************
THREAD_ID: 22
EVENT_ID: 1267
EVENT_NAME: wait/synch/mutex/sql/THD::LOCK_thd_data
SOURCE: sql_class.cc:3348
TIMER_START: 869047580523600
TIMER_END: 869047580764800
TIMER_WAIT: 241200
SPINS: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 41856000
NESTING_EVENT_ID: NULL
OPERATION: lock
NUMBER_OF_BYTES: NULL
FLAGS: 0

mysql> SELECT EVENT_ID, EVENT_NAME, TIMER_WAIT FROM EVENTS_WAITS_HISTORY WHERE THREAD_ID = 22 ORDER BY EVENT_ID;
+----------+-----------------------------------------+------------+
| EVENT_ID | EVENT_NAME | TIMER_WAIT |
+----------+-----------------------------------------+------------+
| 1258 | wait/synch/mutex/sql/LOCK_open | 45225 |
| 1259 | wait/synch/rwlock/sql/MDL_lock::rwlock | 472350 |
| 1260 | wait/synch/mutex/sql/MDL_map::mutex | 190950 |
| 1261 | wait/synch/rwlock/sql/MDL_lock::rwlock | 216075 |
| 1262 | wait/synch/mutex/sql/MDL_map::mutex | 80400 |
| 1263 | wait/synch/rwlock/sql/MDL_lock::rwlock | 80400 |
| 1264 | wait/synch/mutex/sql/MDL_map::mutex | 45225 |
| 1265 | wait/synch/rwlock/sql/MDL_lock::rwlock | 60300 |
| 1266 | wait/synch/mutex/sql/MDL_map::mutex | 45225 |
| 1267 | wait/synch/mutex/sql/THD::LOCK_thd_data | 241200 |
+----------+-----------------------------------------+------------+

mysql> SELECT * FROM EVENTS_WAITS_CURRENT WHERE THREAD_ID = 22 \G
*************************** 1. row ***************************
THREAD_ID: 22
EVENT_ID: 1493
EVENT_NAME: wait/synch/mutex/sql/THD::LOCK_thd_data
SOURCE: sql_class.cc:3348
TIMER_START: 884476565663700
TIMER_END: 884476565784300
TIMER_WAIT: 120600
SPINS: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 41856000
NESTING_EVENT_ID: NULL
OPERATION: lock
NUMBER_OF_BYTES: NULL
FLAGS: 0

mysql> SELECT EVENT_ID, EVENT_NAME, TIMER_WAIT FROM EVENTS_WAITS_HISTORY WHERE THREAD_ID = 22 ORDER BY EVENT_ID;
+----------+---------------------------------------------------------+------------+
| EVENT_ID | EVENT_NAME | TIMER_WAIT |
+----------+---------------------------------------------------------+------------+
| 1484 | wait/synch/mutex/sql/Query_cache::structure_guard_mutex | 150750 |
| 1485 | wait/synch/mutex/sql/LOG::LOCK_log | 180900 |
| 1486 | wait/synch/mutex/sql/LOCK_global_read_lock | 201000 |
| 1487 | wait/synch/rwlock/sql/MDL_lock::rwlock | 386925 |
| 1488 | wait/synch/mutex/sql/MDL_map::mutex | 206025 |
| 1489 | wait/synch/rwlock/sql/MDL_lock::rwlock | 160800 |
| 1490 | wait/synch/mutex/sql/MDL_map::mutex | 95475 |
| 1491 | wait/synch/rwlock/sql/MDL_lock::rwlock | 50250 |
| 1492 | wait/synch/mutex/sql/MDL_map::mutex | 50250 |
| 1493 | wait/synch/mutex/sql/THD::LOCK_thd_data | 120600 |
+----------+---------------------------------------------------------+------------+

It's a good Engine to Diagnose Problems.
read more

SQL Developer with MySQL

How to use Oracle SQL Developer with MySQL? I hope It's work.
But I can connect and query data from tables, but can not to create or show procedure code (MySQL) from SQL Developer.
I used SQL Developer to connect MySQL, that's work. but can not get procedure code(Or I forgot some engine)
However It's work to query data from tables.
I hope someday, It'll work more...

MYSQL table partitioning ERROR 1 (HY000): Can't create/write to file '/tmp/root-tmp.xxx/files/xxx' (Errcode: 13)

After I read Inside MySQL 5.1 A DBA’s Perspective, so I created table partitionning and found error... 

mysql> CREATE TABLE part_tab

    -> ( c1 int default NULL,

    -> c2 varchar(30) default NULL,

    -> c3 date default NULL

    -> ) engine=myisam

    -> PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),

    -> PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,

    -> PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,

    -> PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,

    -> PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,

    -> PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),

    -> PARTITION p11 VALUES LESS THAN MAXVALUE );

ERROR 1 (HY000): Can't create/write to file '/tmp/root-tmp.2661f66/files/psyC3LzHV' (Errcode: 13)

this error should show about something wrong about permission on OS that PATH.

this PATH on tmpdir variable.

So, change permission 777 to that PATH

# chmod 777 -R /tmp/root-tmp.2661f66

After that I can created table partitioning...

mysql> CREATE TABLE part_tab

    -> ( c1 int default NULL,

    -> c2 varchar(30) default NULL,

    -> c3 date default NULL

    -> ) engine=myisam

    -> PARTITION BY RANGE (year(c3)) (PARTITION p0 VALUES LESS THAN (1995),

    -> PARTITION p1 VALUES LESS THAN (1996) , PARTITION p2 VALUES LESS THAN (1997) ,

    -> PARTITION p3 VALUES LESS THAN (1998) , PARTITION p4 VALUES LESS THAN (1999) ,

    -> PARTITION p5 VALUES LESS THAN (2000) , PARTITION p6 VALUES LESS THAN (2001) ,

    -> PARTITION p7 VALUES LESS THAN (2002) , PARTITION p8 VALUES LESS THAN (2003) ,

    -> PARTITION p9 VALUES LESS THAN (2004) , PARTITION p10 VALUES LESS THAN (2010),

    -> PARTITION p11 VALUES LESS THAN MAXVALUE );

Query OK, 0 rows affected (0.01 sec)

*** Another way that can change tmpdir = /tmp

How?

mysql> show variables like 'tmpdir';

| tmpdir        | /tmp/root-tmp.2661f66/files | 

stopped mysqld and modified /etc/my.cnf (set tmpdir=/tmp)... and then started again

[mysqld]

tmpdir=/tmp

mysql> show variables like 'tmpdir';

| tmpdir        | /tmp |