Friday, 25 October 2013

MySQL export table to csv file

SELECT id, table_id, indices,create_time
INTO OUTFILE '/usr/local/mysql/tblTest.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM tblTest WHERE 1;

Thursday, 24 October 2013

CENTOS install rlwrap for oracle sqlplus

[root@localhost6 ~]# rpm -ivh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
Retrieving http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
warning: /var/tmp/rpm-tmp.rIN0Eg: Header V3 RSA/SHA256 Signature, key ID 0608b895: NOKEY
Preparing...                ########################################### [100%]
   1:epel-release           ########################################### [100%]
[root@localhost6 ~]# rpm --import /etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-6
[root@localhost6 ~]# yum install rlwrap readline readline-devel
Loaded plugins: fastestmirror, refresh-packagekit, security
Loading mirror speeds from cached hostfile
epel/metalink                                                                                                                                             | 5.6 kB     00:00
 * base: mirrors.sin3.sg.voxel.net
 * epel: mirror.smartmedia.net.id
 * extras: mirrors.sin3.sg.voxel.net
 * updates: mirror.colocity.com
epel                                                                                                                                                      | 4.2 kB     00:00
epel/primary_db                                                                                                                                           | 5.6 MB     00:00
Setting up Install Process
Package readline-6.0-4.el6.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package readline-devel.x86_64 0:6.0-4.el6 will be installed
--> Processing Dependency: ncurses-devel for package: readline-devel-6.0-4.el6.x86_64
---> Package rlwrap.x86_64 0:0.37-1.el6 will be installed
--> Running transaction check
---> Package ncurses-devel.x86_64 0:5.7-3.20090208.el6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved
=================================================================================================================================================================================
 Package                                       Arch                                  Version                                           Repository                           Size
=================================================================================================================================================================================
Installing:
 readline-devel                                x86_64                                6.0-4.el6                                         base                                134 k
 rlwrap                                        x86_64                                0.37-1.el6                                        epel                                 90 k
Installing for dependencies:
 ncurses-devel                                 x86_64                                5.7-3.20090208.el6                                base                                642 k

Transaction Summary
=================================================================================================================================================================================
Install       3 Package(s)

Total download size: 866 k
Installed size: 2.2 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): ncurses-devel-5.7-3.20090208.el6.x86_64.rpm                                                                                                        | 642 kB     00:00
(2/3): readline-devel-6.0-4.el6.x86_64.rpm                                                                                                                | 134 kB     00:00
(3/3): rlwrap-0.37-1.el6.x86_64.rpm                                                                                                                       |  90 kB     00:00
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                                            482 kB/s | 866 kB     00:01
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
Warning: RPMDB altered outside of yum.
  Installing : ncurses-devel-5.7-3.20090208.el6.x86_64                                                                                                                       1/3
  Installing : readline-devel-6.0-4.el6.x86_64                                                                                                                               2/3
  Installing : rlwrap-0.37-1.el6.x86_64                                                                                                                                      3/3
  Verifying  : ncurses-devel-5.7-3.20090208.el6.x86_64                                                                                                                       1/3
  Verifying  : readline-devel-6.0-4.el6.x86_64                                                                                                                               2/3
  Verifying  : rlwrap-0.37-1.el6.x86_64                                                                                                                                      3/3

Installed:
  readline-devel.x86_64 0:6.0-4.el6                                                          rlwrap.x86_64 0:0.37-1.el6

Dependency Installed:
  ncurses-devel.x86_64 0:5.7-3.20090208.el6

Complete!
[root@localhost6 ~]#



#Login as oracle user
vi /home/oracle/.bash_profile

#append 2 more lines
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'

Logout and login again.

--------------------The End----------------------------------------------------------------------

Wednesday, 2 October 2013

MySQL NDB Cluster Configuration Step by Step

To set up the cluster, I create 5 Hyper-V virtual machine with CENTOS 6.4, one management node, two data node and two mysql node.
192.168.1.144 (mgm)
192.168.1.196 (ndb)
192.168.1.197 (ndb)
192.168.1.152 (mysqld)
192.168.1.193 (mysqld)
Step1:
Download mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64.tar.gz  binary file from mysql website http://dev.mysql.com/downloads/cluster/
Copy the download file to those 5 Linux server and do the next step on all 5 servers.
groupadd mysql
useradd –g mysql mysql
tar –zxvf  /usr/local/ mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64.tar.gz
mv /usr/local/ mysql-cluster-gpl-7.3.2-linux-glibc2.5-x86_64 /opt/mysql_cluster
chown mysql.mysql –R /opt/mysql_cluster

On server  192.168.1.196 and 192.168.1.197 create a new folder
mkdir  /opt/mysql_cluster/ndbdata
chown mysql.mysql –R /opt/mysql_cluster



Step2:
Mgm Node(192.168.1.144) configuration
cd /opt/mysql_cluster/
vi config.ini


[root@CENTOS2 ~]# cat /opt/mysql_cluster/config.ini
 [ndbd default]
 NoOfReplicas= 2
 [NDB_MGMD]
 nodeid=1
 HostName= 192.168.1.144
 [NDBD]
 nodeid=11
 HostName= 192.168.1.196
 Datadir= /opt/mysql_cluster/ndbdata
 [NDBD]
 nodeid=12
 HostName= 192.168.1.197
 Datadir= /opt/mysql_cluster/ndbdata
 [MYSQLD]
 nodeid=21
 HostName= 192.168.1.152
 [MYSQLD]
 nodeid=22
 HostName= 192.168.1.193



Step3:
Install and configure mysql on two mysqld node (192.168.1.152, 192.168.1.193)
cd /opt/mysql_cluster/
scripts/mysql_cluster_install --user=mysql --datadir=/opt/mysql_cluster/data --basedir=/opt/mysql_cluster/
mv /opt/mysql_cluster/my.cnf /etc/my.cnf
vi /etc/my.cnf


[mysqld]
 ndbcluster
 ndb-connectstring=192.168.1.144
 [mysql_cluster]
 ndb-connectstring=192.168.1.144


Modify the /opt/mysql_cluster/support-files/mysql.server, replace datadir and basedir
datadir=/opt/mysql_cluster/data
basedir=/opt/mysql_cluster/

Step 4:
Start all cluster and mgm node
Startup data node(192.168.1.196, 192.168.1.197)
[root@Node1]# /opt/mysql_cluster/bin/ndbd --defaults-file=/opt/mysql_cluster/my.cnf --initial
2013-10-02 13:17:36 [ndbd] INFO     -- Angel connected to '192.168.1.144:1186'
2013-10-02 13:17:36 [ndbd] INFO     -- Angel allocated nodeid: 11

Startup mysqld node(192.168.1.152, 192.168.1.193)





Startup the mgm node
/opt/mysql_cluster/bin/ndb_mgmd -f /opt/mysql_cluster/config.ini --configdir=/opt/mysql_cluster
Step 5:
Check status

-----------------------------------------The End---------------------------------------------------------------