MySQL
Friday Jan 23, 2009
Last Day at Sun
I was impacted with today's announcement. Time to move on. I have been working in Sun for past 12 years. It is simply a great company to work for. I believe Sun will Shine again. You can connect with me
http://www.linkedin.com/in/arathikrishnaiyengar
Posted at 10:07AM Jan 23, 2009 by Arathi Krishnaiyengar in MySQL | Comments[0]
Monday Jan 05, 2009
MySQL-Python Driver Installation and Setup
Introduction
This
document describes the steps required on Solaris for writing MySQL Scripts with Python DB-API.
Python Version
Make sure you have version 2.4.3 or higher.
# which python
/usr/bin/python
# python
Python 2.4.4 (#1, Nov 19 2008, 01:54:58) [C] on sunos5
Type "help", "copyright", "credits" or "license" for more information.
>>>
Download MySQL-python driver
Go
to this site
http://sourceforge.net/projects/mysql-python
Click on download link and download mysq-python 1.2.2.
Installation
#pwd
/export/home/mysql
# gzip -d MySQL-python-1.2.2.tar.gz
# tar xvf MySQL-python-1.2.2
# cd MySQL-python-1.2.2
/export/home/mysql/MySQL-python-1.2.2
Building
#pwd
/export/home/mysql/MySQL-python-1.2.2
#
python setup.py build
running build
running
build_py
copying MySQLdb/release.py ->
build/lib.solaris-2.11-sun4u-2.4/MySQLdb
running
build_ext
building '_mysql' extension
/usr/lib/python2.4/pycc
-OPT:Olimit=0 -DNDEBUG -Dversion_info=(1,2,2,'final',0)
-D__version__=1.2.2 -I/usr/local/mysql/include/mysql
-I/usr/include/python2.4 -c _mysql.c -o
build/temp.solaris-2.11-sun4u-2.4/_mysql.o -DHAVE_RWLOCK_T
-DUNIV_SOLARIS
cc: Warning: illegal option
-OPT:Olimit=0
/usr/lib/python2.4/pycc -G
build/temp.solaris-2.11-sun4u-2.4/_mysql.o
-L/usr/local/mysql/lib/mysql -lmysqlclient_r -lz -lpthread -lthread
-lgen -lsocket -lnsl -lm -lpthread -lthread -o
build/lib.solaris-2.11-sun4u-2.4/_mysql.so
Installing
#
sudo python setup.py install
running
install
running bdist_egg
running egg_info
writing
MySQL_python.egg-info/PKG-INFO
writing top-level names to
MySQL_python.egg-info/top_level.txt
writing dependency_links to
MySQL_python.egg-info/dependency_links.txt
deleting
MySQL_python.egg-info/native_libs.txt
reading manifest file
'MySQL_python.egg-info/SOURCES.txt'
reading manifest template
'MANIFEST.in'
writing manifest file
'MySQL_python.egg-info/SOURCES.txt'
installing library code to
build/bdist.solaris-2.11-sun4u/egg
running install_lib
running
build_py
copying MySQLdb/release.py ->
build/lib.solaris-2.11-sun4u-2.4/MySQLdb
running
build_ext
creating build/bdist.solaris-2.11-sun4u
.....
copying
build/lib.solaris-2.11-sun4u-2.4/MySQLdb/__init__.py ->
build/bdist.solaris-2.11-
creating dist..........
creating
'dist/MySQL_python-1.2.2-py2.4-solaris-2.11-sun4u.egg' and adding
'build/bdist.solaris-2.11-sun4u/egg' to it
removing
'build/bdist.solaris-2.11-sun4u/egg' (and everything under
it)
Processing
MySQL_python-1.2.2-py2.4-solaris-2.11-sun4u.egg
Copying
MySQL_python-1.2.2-py2.4-solaris-2.11-sun4u.egg to
/usr/lib/python2.4/site-packages
Adding MySQL-python 1.2.2 to
easy-install.pth file
Installed
/usr/lib/python2.4/site-packages/MySQL_python-1.2.2-py2.4-solaris-2.11-sun4u.egg
Processing
dependencies for MySQL-python==1.2.2
Finished processing
dependencies for MySQL-python==1.2.2
Testing the MySQL-Python setup
To test if MySQLdb module is installed, issue the import command by running Python in interactive mode from the command line prompt. Also, make sure to set LD_LIBRARY_PATH to contain MySQL Libs otherwise you might see import errors.
#
export LD_LIBRARY_PATH=/usr/local/mysql/lib/mysql
# python
Python
2.4.4 (#1, Nov 19 2008, 01:54:58) [C] on sunos5
Type "help",
"copyright", "credits" or "license" for
more information.
>>> import MySQLdb
>>
Create Simple Python DB-API script to get Mysql version
Create
a file server_version.py and add the following
contents.
#server_version.py
- retrieve and display database server version
import
MySQLdb
conn = MySQLdb.connect (host =
"localhost",
user = "testuser",
passwd = "testpass",
db = "test")
cursor = conn.cursor ()
cursor.execute
("SELECT VERSION()")
row = cursor.fetchone ()
print
"server version:", row[0]
cursor.close ()
conn.close
()
Running the DB-API script
#
python
server_version.py
/usr/lib/python2.4/site-packages/MySQL_python-1.2.2-py2.4-solaris-2.11-sun4u.egg/_mysql.py:3:
UserWarning: Module _mysql was already imported from
/usr/lib/python2.4/site-packages/MySQL_python-1.2.2-py2.4-solaris-2.11-sun4u.egg/_mysql.pyc,
but /export/home/mysql/MySQL-python-1.2.2 is being added to
sys.path
server version: 5.1.30
References
Supported Drivers, Docs, Discussion forms:
http://www.sun.com/software/products/mysql/drivers.jsp
Articles for installing drivers:
http://www.kitebird.com/articles/
Posted at 11:48AM Jan 05, 2009 by Arathi Krishnaiyengar in MySQL | Comments[3]
Ruby DBI Module Installation and Setup
Introduction
This document describes the steps required on Solaris for connecting to MySQL database using Ruby DBI module
Prerequisites
For DBI scripts to access MySQL databases, you'll need to have both the C API installed and the Ruby MySQL module.
The C API is part of MySQL source code distribution. You can refer to my blog entry "How to Build MySQL source code on Solaris".
To install Ruby MySQL module, you can refer to my blog entry "Ruby MySQL Module Installation and Setup".
Download Ruby DBI driver
Go
to this site.
http://rubyforge.org/projects/ruby-dbiClick on view all projects. Scroll down and download dbi-0.2.0.tar.gz
Installation
#pwd
/export/home/mysql
# gzip -d dbi-0.2.0.tar.gz
# tar xvf dbi-0.2.0.tar
#cd dbi-0.2.0
#
pwd
/export/home/mysql/dbi-0.2.0
Configure
After unpacking the distribution, change the location into its top-level directory and configure it using the setup.rb script in that directory.
#
pwd
/export/home/mysql/dbi-0.2.0
The most general configuration command looks like this, with no arguments following the config argument:
# ruby setup.rb config
That command configures the distribution to install all drivers by default. To be more specific, provide a --with option that lists the particular parts of the distribution you want to use. For example, to configure only the main DBI module and the MySQL DBD-level driver, issue the following command:
I used the following in my installation
# ruby setup.rb config --with=dbi,dbd_mysql
entering config phase...
config done.
Setup
After configuring the distribution, build it:
#
pwd
/export/home/mysql/dbi-0.2.0
# ruby setup.rb
setup
entering setup phase...
setting #! line to
"#!/usr/ruby/1.8/bin/ruby"
setup.rb: skip
bin/proxyserver(dbd_proxy) by user option
setup.rb: skip
ext/dbd_sybase(dbd_sybase) by user option
setup done.
Install Ruby DBI
After building it, install it with:
#
ruby setup.rb install
entering
install phase...
mkdir -p /usr/ruby/1.8/bin
install sqlsh.rb
/usr/ruby/1.8/bin
setup.rb: skip bin/proxyserver(dbd_proxy) by
user option
mkdir -p
/usr/ruby/1.8/lib/ruby/site_ruby/1.8/DBD/Mysql
install Mysql.rb
/usr/ruby/1.8/lib/ruby/site_ruby/1.8/DBD/Mysql/Mysql.rb
mkdir -p
/usr/ruby/1.8/lib/ruby/site_ruby/1.8/dbi
install columninfo.rb
/usr/ruby/1.8/lib/ruby/site_ruby/1.8/dbi
install row.rb
/usr/ruby/1.8/lib/ruby/site_ruby/1.8/dbi
install sql.rb
/usr/ruby/1.8/lib/ruby/site_ruby/1.8/dbi
install utils.rb
/usr/ruby/1.8/lib/ruby/site_ruby/1.8/dbi
install trace.rb
/usr/ruby/1.8/lib/ruby/site_ruby/1.8/dbi
install version.rb
/usr/ruby/1.8/lib/ruby/site_ruby/1.8/dbi
install dbi.rb
/usr/ruby/1.8/lib/ruby/site_ruby/1.8
setup.rb: skip
ext/dbd_sybase(dbd_sybase) by user option
install done.
Testing installation
Open
up IRB (which stands for Interactive Ruby). Type irb to invoke. Check
if “require dbi” returns true.
# irb
irb(main):001:0>
require "dbi"
=>
true
irb(main):004:0> quit
Creating DBI script file to get MySQL version
# pwd
/export/home/mysql/dbi-0.2.0
Add the following to simple.rb file
# more simple.rb
#!/usr/bin/ruby -w
# simple.rb - simple MySQL script using Ruby DBI module
require "dbi"
begin
# connect to the MySQL server
dbh = DBI.connect("DBI:Mysql:test:localhost", "testuser", "testpass")
# get server version string and display it
row = dbh.select_one("SELECT VERSION()")
puts "Server version: " + row[0]
rescue DBI::DatabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
# disconnect from server
dbh.disconnect if dbh
end
#
Run the Ruby DBI script
Run the script and the MySQL server version is displayed.
#
ruby simple.rb
Server
version: 5.1.30
#
References
Supported Drivers, Docs, Discussion forms:
http://www.sun.com/software/products/mysql/drivers.jsp
Articles for installing the drivers:
http://www.kitebird.com/articles/
The Ruby home page provides general
information about Ruby itself:
Posted at 11:48AM Jan 05, 2009 by Arathi Krishnaiyengar in MySQL | Comments[0]
Ruby MySQL Module Installation and Setup
Introduction
This
document describes the steps required on Solaris for connecting to
MySQL database using MySQL-Ruby.
Prerequisite
MySQL driver integration needs MySQL C client API header files and libraries. This is available only through the source code distribution. Please refer to my blog entry "How to Build MySQL source code on Solaris".
Ruby Version Requirement
Ruby 1.8.2-1.8.7, 1.9.0
# ruby -version
ruby 1.8.6 (2008-08-11 patchlevel 287) [sparc-solaris2.11]
Download Ruby MySQL Module
Go to this site http://www.tmtm.org/en/mysql/ruby/
Under download section, select mysql-ruby-2.8.tar.gz .
MySQL-Ruby Installation
#pwd /export/home/mysql #gzip -d mysql-ruby-2.8.tar.gz #tar xvf mysql-ruby-2.8 #cd mysql-ruby-2.8
Configure, Build and Install
After unpacking the distribution, change the location into its top-level directory and configure it using the extconf.rb script in that directory.
# pwd
/export/home/mysql/mysql-ruby-2.8 # ruby extconf.rb
If extconf.rb successfully locates your MySQL header file and library directories, you can proceed to build and install the module. Otherwise, it indicates what it could not find, and you'll need to run the command again with additional options that specify the appropriate directory locations. For example, if your header file and library directories are /usr/local/mysql/include/mysql and /usr/local/mysql/include/lib, the configuration command looks like this:
# ruby extconf.rb \
--with-mysql-include=/usr/local/mysql/include/mysql \
--with-mysql-lib=/usr/local/mysql/lib/mysql
Alternatively, tell extconf.rb where to find the mysql_config program. In that case, extconf.rb runs mysql_config to locate the header and library files:
# ruby extconf.rb --with-mysql-config=/usr/local/mysql/bin/mysql_config
In my installation, I have used the following..
#ruby extconf.rb --with-mysql-include=/usr/local/mysql/include/mysql –with-mysql-lib=/usr/local/mysql/lib/mysql
checking
for mysql_query() in -lmysqlclient... yes
checking for
mysql_ssl_set()... yes
checking for rb_str_set_len()...
no
checking for mysql.h... no
checking for mysql/mysql.h...
yes
creating Makefile
After configuring the distribution, build and install the module:
% make % make install
Create simple.rb script
Add the following contents to file simple.rb and save it.
#!/usr/bin/ruby -w
# simple.rb - simple MySQL script using Ruby MySQL module
require "mysql"
begin
# connect to the MySQL server
dbh = Mysql.real_connect("localhost", "testuser", "testpass", "test")
# get server version string and display it
puts "Server version: " + dbh.get_server_info
rescue Mysql::Error => e
puts "Error code: #{e.errno}"
puts "Error message: #{e.error}"
puts "Error SQLSTATE: #{e.sqlstate}" if e.respond_to?("sqlstate")
ensure
# disconnect from server
dbh.close if dbh
end
Execute the script to get the MySQL version
#
ruby simple.rb
Server version: 5.1.30
#
References
Supported Drivers, Docs, Discussion forms:
http://www.sun.com/software/products/mysql/drivers.jsp
Articles for installing the drivers:
http://www.kitebird.com/articles/
The Ruby home page provides general
information about Ruby itself:
Posted at 11:48AM Jan 05, 2009 by Arathi Krishnaiyengar in MySQL | Comments[0]
How to Build MySQL source code on Solaris
Introduction
This
document describes the steps required to build MySQL source code on Solaris
Installed Solaris Version Details
# uname -a
SunOS clear02 5.11 snv_104 sun4u sparc SUNW,Sun-Fire-880
# more /etc/release
Solaris Express Community Edition snv_104 SPARC
Copyright 2008 Sun Microsystems, Inc. All Rights Reserved.
Use is subject to license terms.
Assembled 02 December 2008
MySQL Download
-
As mysql user, Download Compressed gnu source archive for MySQL 5.1 from the following location:
scroll
down to Source downloads section. Download
Compressed
GNU TAR archive (tar.gz)5.1.30 which is 27.7M
MySQL Environment Setup
-
Generate Optimized binary using Sun Studio compiler and optimized compiler options.
Sun Studio Compiler can be downloaded from
-
Make sure Sun compiler, gnu Make and Sun's nm (/usr/ccs/bin) is in the path:
-
gmake:
-
gmake is under /usr/sfw/bin directory.
-
Create a sym link from gmake to make
-
cd /usr/sfw/bin ; ln -s gmake make
-
-
C compiler
-
Path to SPARC C compiler
In my installation it is under,
/shared/dp/mars/stable/sparc-S2.inst/opt/SUNWspro/bin
and can be downloaded from
-
Path to nm, ar utility /usr/ccs/bin.
-
Path is set as under:
-
SPARC :
example:
export PATH=$PATH:/usr/sbin:/usr/sfw/bin:/shared/dp/mars/stable/sparc-S2.inst/opt/SUNWspro/bin:/usr/ccs/bin
-
MySQL Source Installation
Pre-installation Checklist
Solaris comes with MySQL installed. Use pkgrm <package-name> to remove all mysql packages.
#pkginfo | grep mysql
SUNWapu13dbd-mysql/
SUNWmysql-python/
SUNWmysql5jdbc/
SUNWmysql5r/
SUNWmysql5test/
SUNWmysql5u/
SUNWmysqlr/
SUNWmysqlt/
SUNWmysqlu/
SUNWphp52r-mysql/
SUNWphp52u-mysql/
Example
# pkgrm SUNWmysql5u
MySQL Build and Installation
# echo $PATH /usr/sbin:/usr/sfw/bin:/shared/dp/mars/stable/sparc-S2.inst/opt/SUNWspro/bin:/usr/ccs/bin:/usr/sbin:/usr/bin #pwd /export/home/mysql/src #gzip -d mysql-5.1.30.tar.gz #tar xvf mysql-5.1.30.tar #cd mysql-5.1.30 #pwd /export/home/mysql/src/mysql-5.1.30
-
Invoke the configure
-
./configure --prefix=/usr/local/mysql --with-extra-charsets=complex --enable-thread-safe-client --enable-local-infile --enable-assembler
-
Then issue :
-
make all
-
-
Finally:
-
make install
-
-
This will install MySQL in /usr/local/mysql directory
MySQL Database setup
#cd /usr/local/mysql #groupadd mysql #useradd -g mysql mysql #chown -R mysql . #chgrp -R mysql . #scripts/mysql_install_db –user=mysql #chown -R root . #chown -R mysql var #bin/mysqld_safe --user=mysql & Installing MySQL system tables...
...
...
OK
Create password for the MySQL root user
In the below example, we are creating root user password and password is set to “mysql”
#./bin/mysqladmin -u root password <mysql> # bin/mysql -u root -p Enter password: <mysql>
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1332
Server version: 5.1.30 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
mysql>
Posted at 09:38AM Jan 05, 2009 by Arathi Krishnaiyengar in MySQL | Comments[0]