Got my SCJP-6 Certificate and Card
May 6th, 2010
1 comment
Categories: Uncategorized
Archive
Author Archive
MySQL on EBS with OpenSolaris based AMI
January 16th, 2010
No comments
Amazon Elastic Block Store (EBS) is the persistent storage which can be attached to the AMI instance. Data can be safely placed and accessed independent of the life of the instance. There are plenty of benefits using EBS for MySQL. For detailed explanation click here.
Following steps describes how to use MySQL on EBS volume with OpenSolaris based AMI.
1. Create a new EBS Volume.
$ ec2-create-volume –size 10 –availability-zone us-east-1a
VOLUME vol-688d4c01 10 us-east-1a creating 2010-01-13T21:54:57+0000
VOLUME vol-688d4c01 10 us-east-1a creating 2010-01-13T21:54:57+0000
$ ec2-describe-volumes vol-688d4c01
VOLUME vol-688d4c01 10 us-east-1a available 2010-01-13T21:54:57+0000
VOLUME vol-688d4c01 10 us-east-1a available 2010-01-13T21:54:57+0000
Click here for ec2-create-volume API help.
2. Create a new MySQL 5.1 AMI Instance based on OpenSolaris.
$ ec2-run-instances ami-752ccb1c -k my-keypair
RESERVATION r-2486124c 987654321123 default
INSTANCE i-9e6858f6 ami-752ccb1c pending my-keypair 0 m1.small 2010-01-17T00:01:24+0000 us-east-1a aki-6552b60c ari-6452b60d monitoring-disabled
RESERVATION r-2486124c 987654321123 default
INSTANCE i-9e6858f6 ami-752ccb1c pending my-keypair 0 m1.small 2010-01-17T00:01:24+0000 us-east-1a aki-6552b60c ari-6452b60d monitoring-disabled
Click here for more details on this AMI.
3. Attach EBS volume to this AMI instance
$ ec2-attach-volume -i i-9e6858f6 -d 2 vol-688d4c01
ATTACHMENT vol-688d4c01 i-9e6858f6 2 attaching 2010-01-13T22:02:51+0000
ATTACHMENT vol-688d4c01 i-9e6858f6 2 attaching 2010-01-13T22:02:51+0000
In the above command -d option specifies the device number. In OpenSolaris devices are named from 0 to 23. Device number 0 and 1 are used for ephemeral storage. Therefore, we can use the devices starting from 2 until 23.
$ ec2-describe-volumes vol-688d4c01
VOLUME vol-688d4c01 10 us-east-1a in-use 2010-01-13T21:54:57+0000
ATTACHMENT vol-688d4c01 i-9e6858f6 2 attached 2010-01-13T22:02:51+0000
VOLUME vol-688d4c01 10 us-east-1a in-use 2010-01-13T21:54:57+0000
ATTACHMENT vol-688d4c01 i-9e6858f6 2 attached 2010-01-13T22:02:51+0000
Click here for ec2-attach-volume API help.
Click here for ec2-describe-volumes API help.
4. Create a ZFS storage pool on the previously attached EBS volume.
Once the instance is up and running, login into the instance using ssh with your key-pair.
To verify new EBS volume attached, execute the format command and press Control + C to come out.
root@domU-12-31-39-03-21-E4:~# format
Searching for disks…done
Searching for disks…done
AVAILABLE DISK SELECTIONS:
0. c3d0 <DEFAULT cyl 1274 alt 0 hd 255 sec 63>
/xpvd/xdf@0
1. c3d1 <DEFAULT cyl 19464 alt 0 hd 255 sec 63>
/xpvd/xdf@1
2. c3d2 <DEFAULT cyl 1305 alt 0 hd 255 sec 63>
/xpvd/xdf@2
Specify disk (enter its number): ^C
root@domU-12-31-39-03-21-E4:~#
Create a ZFS storage pool on the EBS volume.
root@domU-12-31-39-03-21-E4:~# zpool create ebsmysql c3d2
root@domU-12-31-39-03-21-E4:~# zpool list
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
ebsmysql 9.94G 73.5K 9.94G 0% ONLINE -
mnt 149G 73.5K 149G 0% ONLINE -
rpool 9.75G 3.50G 6.25G 35% ONLINE -
root@domU-12-31-39-03-21-E4:~#
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
ebsmysql 9.94G 73.5K 9.94G 0% ONLINE -
mnt 149G 73.5K 149G 0% ONLINE -
rpool 9.75G 3.50G 6.25G 35% ONLINE -
root@domU-12-31-39-03-21-E4:~#
root@domU-12-31-39-03-21-E4:~# zpool status ebsmysql
pool: ebsmysql
state: ONLINE
scrub: none requested
config:
pool: ebsmysql
state: ONLINE
scrub: none requested
config:
NAME STATE READ WRITE CKSUM
ebsmysql ONLINE 0 0 0
c3d2 ONLINE 0 0 0
errors: No known data errors
root@domU-12-31-39-03-21-E4:~#
Move MySQL on ZFS storage pool based on EBS
Make sure MySQL service is disabled.
root@domU-12-31-39-03-21-E4:~# svcs svc:/application/mysql:default
STATE STIME FMRI
disabled 21:57:48 svc:/application/mysql:default
root@domU-12-31-39-03-21-E4:~#
STATE STIME FMRI
disabled 21:57:48 svc:/application/mysql:default
root@domU-12-31-39-03-21-E4:~#
If MySQL service is running make sure to disable it using svcadm disable svc:/application/mysql:default
Move MySQL installation directory to /ebsmysql
root@domU-12-31-39-03-21-E4:~# mv /usr/local/mysql/ /ebsmysql/
root@domU-12-31-39-03-21-E4:~#
root@domU-12-31-39-03-21-E4:~#
Create a soft link to /ebsmysql/mysql/ under /usr/local with the name ‘mysql’
root@domU-12-31-39-03-21-E4:~# cd /usr/local/
root@domU-12-31-39-03-21-E4:/usr/local# ln -s /ebsmysql/mysql/ mysql
root@domU-12-31-39-03-21-E4:/usr/local# ln -s /ebsmysql/mysql/ mysql
root@domU-12-31-39-03-21-E4:/usr/local# ls -la
total 5
drwxr-xr-x 2 root root 3 2010-01-13 22:08 .
drwxr-xr-x 32 root sys 46 2009-02-19 23:20 ..
lrwxrwxrwx 1 root root 16 2010-01-13 22:08 mysql -> /ebsmysql/mysql/
root@domU-12-31-39-03-21-E4:/usr/local#
total 5
drwxr-xr-x 2 root root 3 2010-01-13 22:08 .
drwxr-xr-x 32 root sys 46 2009-02-19 23:20 ..
lrwxrwxrwx 1 root root 16 2010-01-13 22:08 mysql -> /ebsmysql/mysql/
root@domU-12-31-39-03-21-E4:/usr/local#
Enable MySQL service
root@domU-12-31-39-03-21-E4:~# svcadm enable svc:/application/mysql:default
root@domU-12-31-39-03-21-E4:~# svcs svc:/application/mysql:default
STATE STIME FMRI
online 22:09:43 svc:/application/mysql:default
root@domU-12-31-39-03-21-E4:~#
root@domU-12-31-39-03-21-E4:~# svcs svc:/application/mysql:default
STATE STIME FMRI
online 22:09:43 svc:/application/mysql:default
root@domU-12-31-39-03-21-E4:~#
Test MySQL
root@domU-12-31-39-03-21-E4:~# /ebsmysql/mysql/bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.25-rc Source distribution
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.25-rc Source distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| sbtest |
| test |
+——————–+
4 rows in set (0.01 sec)
mysql>
5. Detach EBS volume from AMI instance
root@domU-12-31-39-03-21-E4:~# svcadm disable svc:/application/mysql:default
root@domU-12-31-39-03-21-E4:~# svcs svc:/application/mysql:default
STATE STIME FMRI
disabled 3:09:07 svc:/application/mysql:default
root@domU-12-31-39-0A-0E-64:~#
STATE STIME FMRI
disabled 3:09:07 svc:/application/mysql:default
root@domU-12-31-39-0A-0E-64:~#
root@domU-12-31-39-03-21-E4:~# zpool export ebsmysql
root@domU-12-31-39-03-21-E4:~# zpool list
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
mnt 149G 73.5K 149G 0% ONLINE -
rpool 9.75G 3.22G 6.53G 33% ONLINE -
root@domU-12-31-39-03-21-E4:~#
root@domU-12-31-39-03-21-E4:~# zpool list
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
mnt 149G 73.5K 149G 0% ONLINE -
rpool 9.75G 3.22G 6.53G 33% ONLINE -
root@domU-12-31-39-03-21-E4:~#
$ ec2-detach-volume vol-688d4c01
ATTACHMENT vol-688d4c01 i-9e6858f6 2 detaching 2010-01-17T02:58:33+0000
ATTACHMENT vol-688d4c01 i-9e6858f6 2 detaching 2010-01-17T02:58:33+0000
$ ec2-describe-volumes vol-688d4c01
VOLUME vol-688d4c01 10 us-east-1a available 2010-01-17T02:50:34+0000
divyen-patels-macbook:ec2 divyenpatel$
VOLUME vol-688d4c01 10 us-east-1a available 2010-01-17T02:50:34+0000
divyen-patels-macbook:ec2 divyenpatel$
6. Re-attach EBS volume to the AMI instance
$ ec2-attach-volume -i i-9e6858f6 -d 2 vol-688d4c01
ATTACHMENT vol-5f0ccd36 i-9e6858f6 2 attaching 2010-01-17T03:18:25+0000
ATTACHMENT vol-5f0ccd36 i-9e6858f6 2 attaching 2010-01-17T03:18:25+0000
$ ec2-describe-volumes vol-688d4c01
VOLUME vol-688d4c01 10 us-east-1a in-use 2010-01-17T02:50:34+0000
ATTACHMENT vol-688d4c01 i-9e6858f6 2 attached 2010-01-17T03:18:25+0000
VOLUME vol-688d4c01 10 us-east-1a in-use 2010-01-17T02:50:34+0000
ATTACHMENT vol-688d4c01 i-9e6858f6 2 attached 2010-01-17T03:18:25+0000
Import the pool
root@domU-12-31-39-03-21-E4:~# zpool import ebsmysql
root@domU-12-31-39-03-21-E4:~# zpool list
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
ebsmysql 9.94G 285M 9.66G 2% ONLINE -
mnt 149G 73.5K 149G 0% ONLINE -
rpool 9.75G 3.22G 6.53G 33% ONLINE -
root@domU-12-31-39-03-21-E4:~#
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
ebsmysql 9.94G 285M 9.66G 2% ONLINE -
mnt 149G 73.5K 149G 0% ONLINE -
rpool 9.75G 3.22G 6.53G 33% ONLINE -
root@domU-12-31-39-03-21-E4:~#
7. Add another EBS Volume to pool
$ ec2-create-volume –size 10 –availability-zone us-east-1a
VOLUME vol-990ccdf0 10 us-east-1a creating 2010-01-17T03:24:24+0000
VOLUME vol-990ccdf0 10 us-east-1a creating 2010-01-17T03:24:24+0000
$ ec2-describe-volumes vol-990ccdf0
VOLUME vol-990ccdf0 10 us-east-1a available 2010-01-17T03:24:24+0000
VOLUME vol-990ccdf0 10 us-east-1a available 2010-01-17T03:24:24+0000
$ ec2-attach-volume -i i-9e6858f6 -d 3 vol-990ccdf0
ATTACHMENT vol-990ccdf0 i-9e6858f6 3 attaching 2010-01-17T03:25:02+0000
ATTACHMENT vol-990ccdf0 i-9e6858f6 3 attaching 2010-01-17T03:25:02+0000
$ ec2-describe-volumes vol-990ccdf0
VOLUME vol-990ccdf0 10 us-east-1a in-use 2010-01-17T03:24:24+0000
ATTACHMENT vol-990ccdf0 i-9e6858f6 3 attached 2010-01-17T03:25:02+0000
VOLUME vol-990ccdf0 10 us-east-1a in-use 2010-01-17T03:24:24+0000
ATTACHMENT vol-990ccdf0 i-9e6858f6 3 attached 2010-01-17T03:25:02+0000
root@domU-12-31-39-03-21-E4:~# zpool list
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
ebsmysql 9.94G 285M 9.66G 2% ONLINE -
mnt 149G 73.5K 149G 0% ONLINE -
rpool 9.75G 3.22G 6.53G 33% ONLINE -
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
ebsmysql 9.94G 285M 9.66G 2% ONLINE -
mnt 149G 73.5K 149G 0% ONLINE -
rpool 9.75G 3.22G 6.53G 33% ONLINE -
root@domU-12-31-39-03-21-E4:~# zpool add ebsmysql c3d3
root@domU-12-31-39-03-21-E4:~# zpool list
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
ebsmysql 19.9G 285M 19.6G 1% ONLINE -
mnt 149G 73.5K 149G 0% ONLINE -
rpool 9.75G 3.22G 6.53G 33% ONLINE -
root@domU-12-31-39-03-21-E4:~#
NAME SIZE USED AVAIL CAP HEALTH ALTROOT
ebsmysql 19.9G 285M 19.6G 1% ONLINE -
mnt 149G 73.5K 149G 0% ONLINE -
rpool 9.75G 3.22G 6.53G 33% ONLINE -
root@domU-12-31-39-03-21-E4:~#
root@domU-12-31-39-03-21-E4:~# zpool status ebsmysql
pool: ebsmysql
state: ONLINE
scrub: none requested
config:
pool: ebsmysql
state: ONLINE
scrub: none requested
config:
NAME STATE READ WRITE CKSUM
ebsmysql ONLINE 0 0 0
c3d2 ONLINE 0 0 0
c3d3 ONLINE 0 0 0
errors: No known data errors
root@domU-12-31-39-03-21-E4:~#
Important Links:
MySQL on ZFS:
http://dev.mysql.com/tech-resources/articles/mysql-zfs.html
EBS With OpenSolaris AMI:
http://blogs.sun.com/ec2/entry/ebs_is_supported_on_opensolaris
Amazon EBS, OpenSolaris, and ZFS Guide to Getting Started
http://blogs.sun.com/ec2/resource/OpenSolaris-ZFS-EBS-v1.3.pdf
Categories: Amazon EC2, MySQL
Connecting MySQL server on Amazon EC2 instance using ssh tunnel
December 27th, 2009
No comments
SSH tunnel basically does the port forwarding via secure ssh service.
Let’s start with launching a latest hardened AMP stack AMI (ami-e0b05389) instance.
Once the instnace is up and running, open the terminal client and connect to the instance using ssh.
Edit the /etc/ssh/sshd_config file and set AllowTcpForwarding to yes
AllowTcpForwarding yes
Restart ssh service
svcadm restart svc:/network/ssh:default
Comment out skip-networking from /etc/mysql/my.cnf and save the file
#skip-networking
Restart MySQL service
svcadm restart svc:/application/database/mysql:version_51
exit the ssh connection and recreate ssh connection using following command.
ssh -i <keypair> -L 3307:localhost:3306 root@ec2-xxx-xxx-xxx-xx.compute-1.amazonaws.com
Keep the terminal opened, and start MySQL Query Browser / MySQL administrator from your local machine. and connect to the MySQL server on remote EC2 instance via the port 3307, as shown in the following sanpshots.
When done close the ssh connection and it should close the ssh tunnel.
Categories: Amazon EC2, MySQL