Connecting to an AWS RDS Instance via an AWS EC2 to Convert MyISAM Tables to InnoDB

So you’ve moved over your MySQL DB to RDS and you need to do some maintenance on it. Maybe you are noticing alerts in the RDS portal like this one
screen-shot-2016-12-12-at-11-13-24-pm
Maybe you are migrating an existing MySQL DB to RDS and its setup using the MyISAM DB engine. AWS recommends to only use the InnoDB storage engine so if you have a MySQL DB that has MyISAM you should consider converting those tables to InnoDB. Its okay if you do this after moving to RDS. AWS mentions that keeping MyISAM tables may lead to unreliable behavior when restoring from backups as MyISAM does not support reliable crash recovery.

In my case, I had already moved my WordPress DB to Amazon RDS. I do not have the DB publicly accessable and you shouldn’t either especially if the front end server is on ec2. If you are wondering about RDS security, Hexatier has a good article on securing Amazon RDS here. Keep in mind your RDS and EC2 security configuration when attempt to connect to do maintenance on your DB. My first
step was to connect to my EC2 instance in the same security group as my RDS DB to use the mysql client to modify the MyISAM tables.

To connect to a remote MySQL DB:
mysql -u dbusername -p -h dbhost

After connecting run this command to convert all dbname MyISAM Tables to InnoDB

SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;') 
FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'dbname' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE'

ref here

mysql> SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME,' ENGINE=InnoDB;') 
-> FROM Information_schema.TABLES WHERE TABLE_SCHEMA = 'dbname' AND ENGINE = 'MyISAM' AND TABLE_TYPE = 'BASE TABLE'
-> ;
+--------------------------------------------------------------+
|   |
+--------------------------------------------------------------+
| ALTER TABLE dbname.wp_commentmeta ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_comments ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_contact_form_7 ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_links ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_ngg_album ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_ngg_gallery ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_ngg_pictures ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_options ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_postmeta ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_posts ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_term_relationships ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_term_taxonomy ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_terms ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_usermeta ENGINE=InnoDB; |
| ALTER TABLE dbname.wp_users ENGINE=InnoDB; |
+--------------------------------------------------------------+

So now just run the above MySQL commands to convert each table to InnoDB.
After converted in the event of a DB crash you should now be supported by the InnoDB DB engine reliable crash recovery feature.

Testing the AWS IoT Button

Screen Shot 2016-05-22 at 7.49.35 PM
I was able to get my hands on an AWS IoT button when it went on sale last week. I had been looking for one of these since they gave them out at re:invent last year. I believe this was the first time it was on sale to the public. I wanted the button to keep next to my bed and would program it to shut off my hue lights and change the temp on my Nest before bed. Sure, I could set each of these independently on a schedule to power off or change temp at a certain time but having one button to take care of both of these actions was what I wanted.

So the button came and I was eager to get started. AWS has a Lambda blueprint for getting started with IoT and the button. It basically uses AWS IoT button to trigger a Lambda function which creates a SNS topic and sends an email. With little configuration you can setup this demo and get some basic experience with AWS IoT.

Check the AWS IoT button page here.
Click the Configure your AWS IoT Button to get started.
Screen Shot 2016-05-22 at 7.18.10 PM

After you login with your AWS creds, what this does is kicks off a Lambda blueprint called iot-button-email that has all of the code and IoT functions associated. While creating from the blueprint make sure of a few things.
1. On Step 2 make sure to add the SQL statement ‘SELECT * FROM ‘iotbutton/+”
2. On Step 3 make sure to update your email and the IoT button serial number in the Lambda function code. Also make sure the Lambda function handler and role is set to the basic execution role.
3. On Step 4 enable the event source and create the function.

A few more tips:
1. After my function was created there was an option to test it, it did not run. Checking the log there was an issue with the lambda basic execution role, not having authorization to create an SNS topic.
1lambda-error
To resolve, go into Identity and access management, modify that role and add the SNS full access policy.
2iam-lambda-role
Try running the Lambda function again it should work fine.
3-lambda-success

2. So now that your function runs you need to configure your button to your wifi and IoT details. The button will not work without an IoT certificate and private key.
button-configurme
The Lambda blueprint should create some things policies and a certificate in AWS IoT, however I was not able to pull the private key from the already created certificate.iot-overview
To resolve this I recreated the certificate and attached the thing and policy to it and now I had everything needed alongside my wifi details to configure the button.

If you are having issues getting your button to respond, note the flashing light sequence and refer to the guide on the bottom of the main IoT button page here. While trying to get this working I had a few different errors, short short short (could not connect to network), long short long (certificate did not have permission to publish), long short short (certificate is not activated). Spend some time working through it, understanding how AWS IoT works, and you should be able to figure it out if you’ve made it this far.

You’ll need to subscribe to the SNS topic that was created for your notifications, so be sure to confirm that subscription.
sns-subscription

After that you should be able to receive emails from pressing your IoT button!

hello-from-iot-button

Moving forward from this very basic demo there are many possibilities.
I used and ifttt.com recipe to turn off my hue when an email is received in my gmail account from no-reply@sns.amazonaws.com from this hackster.io post. There is another beginners guide to using the IoT button that may be useful if you are testing.
Next ill try and use the hue and nest APIs to trigger actions instead of using email and ifttt.
Have fun!

Automate Florida Lottery Number Checking With This Bash Script

So I’m helping my dad with some automation to get him to do things differently. Once thing he does is regularly is check lottery numbers twice a week every week for the past 40+ years. To me its too routine and something that can be automated easily. I’m going to complete this task in 2 steps. First step which is done below is the application that sets up the environment and scrapes data. Second step is a cron that can be setup through the application and installed to validate saved numbers against winning published numbers. This way instead of manually matching his numbers with winning numbers he’ll get an email twice a week and the script will determine if his regular numbers had any luck.

Screen Shot 2015-06-14 at 1.23.30 PM


#!/bin/bash
# Florida Lottery number checker
# mkahnucf@gmail.com
# ver 1.0
# Requires perl and lynx
# Menu source http://bash.cyberciti.biz/guide/Menu_driven_scripts
RED='\033[0;41;30m'
STD='\033[0;0;39m'

pause(){
read -p "Press [Enter] key to continue..." fackEnterKey
}

one(){
read -p "Enter date in mm dd yy format " NUMBER1 NUMBER2 NUMBER3
echo "Ok, lets check for $NUMBER1/"$NUMBER2/$NUMBER3
lynx -dump http://flalottery.com/exptkt/l6.htm | grep "$NUMBER1"/"$NUMBER2"/"$NUMBER3" | cut -b 4-41
pause
}

two(){
YESTERDAY=$(perl -e 'use POSIX;print strftime "%m/%d/%y",localtime time-86400;')
echo "Ok, lets check numbers for" $YESTERDAY
RESULTS="$(lynx -dump http://flalottery.com/exptkt/l6.htm | grep "$YESTERDAY")"
if [ "$RESULTS" == "" ]; then
echo "There was no lottery yesterday $YESTERDAY !"
else
lynx -dump http://flalottery.com/exptkt/l6.htm | grep "$YESTERDAY" | cut -b 4-41
fi
pause
}

three(){
#Store numbers
read -p "Enter numbers in xx xx xx xx xx xx format " NUM1 NUM2 NUM3 NUM4 NUM5 NUM6
echo $NUM1 $NUM2 $NUM3 $NUM5 $NUM6 >> saved-numbers.txt
echo "Ok, i've saved your numbers $NUM1 $NUM2 $NUM3 $NUM5 $NUM6"
pause
}

four(){
SAVEDNUM=$( saved-numbers.txt
echo "Ok ive cleared your saved numbers"
pause
}

show_menus() {
clear
echo "~~~~~~~~~~~~~~~~~~~~~"
echo "Florida Lottery Number Checker"
echo "~~~~~~~~~~~~~~~~~~~~~"
echo "1. Check numbers from a specific date in the past"
echo "2. Check numbers from yesterday"
echo "3. Store numbers for auto-checking"
echo "4. List numbers saved for auto-checking"
echo "5. Clear stored numbers for auto-checking"
echo "6. Exit"
}
read_options(){
local choice
read -p "Enter choice [1 - 6] " choice
case $choice in
1) one ;;
2) two ;;
3) three ;;
4) four ;;
5) five ;;
6) exit 0;;
*) echo -e "${RED}Error...${STD}" && sleep 2
esac
}

trap '' SIGINT SIGQUIT SIGTSTP

while true
do

show_menus
read_options
done

I’ll update this post once the second half is done!

Migrating a Linux OpenVZ Virtual Server (VPS) from vePortal to SolusVM

To start export an OpenVZ virtual server from the vePortal client portal:

1.      Login to the users veportal account with the username and password:
example:  http://1.1.1.1:2407/login.php
2.      Visit the Backup Functions tab
3.     Click New Backup, make sure the Backup limit is not reached under VPS Resource Limits on the left hand information pane.
4.      Wait until backup completes (this may take awhile)
5.     Click Download Backups in the Backup Functions Tab
6.      Now you should have a tar.gz file for the VPS backup. This is the data you will be migrating. You’ll need to FTP this somewhere soon.

Important: Take note of the OS and arch of the VM you have just exported. You’ll need to create the same container in your SolusVM master.

To import (create db entries in solusvm) a OpenVZ virtual server from the admincp do the following. (Ref: http://docs.solusvm.com/importing_virtual_servers?s[]=import&s[]=openvz)

7.     First enable Import Mode from the admincp go to Configuration » Settings » Other and tick Import Mode   ON . Click Update.
8.     Select Virtual Servers » Add Virtual Server
9.     Select OpenVZ
10.     Select the Node & Plan then click Continue
11.     Enter the container id of the virtual server you want to import (as show under vzlist -a on the host node) in the Container ID field. Also go back to Virtual Servers -> List Virtual Servers and pick the next highest number in the VMID. If the greatest take number of an active VPS is 118, you will use 119.
12.     Fill in the rest of the fields, select the client, give it a hostname, and choose the OS and Architecture of the VM that you exported (important to match the same arch and close version).
13.     Click the Create Virtual Server button
14.      Save the virtual server details, click continue.
15.     Now if you are only importing this VPS,  DISABLE Import Mode from the admincp go to Configuration » Settings » Other and tick Import Mode  OFF .

Now you have the shell of the VM and its created in SolusVM! Half way there. Time to move your data over.

Move and prepare your data
16.      Take your tar.gz from the export and FTP it somewhere secure.
17.     If the file has a ‘-‘ before the filename, remove that dash. Linux doesn’t particularly like files that start with a -.
Ex:     -backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar.gz

18.     SSH to the SolusVM slave where you setup this VPS in step 10.
19.     Change directory to /vz/private

Note, there is no directory for the VPS you just created in SolusVM

[code lang=”bash”]
[root@reek ~]# cd /vz/private/
[root@reek private]# ls
113 117 118
[root@reek private]#
[/code]

20.     So lets create the folder for the VPS we are migrating.
[code lang=”bash”]
[root@reek private]# mkdir 119
[root@reek private]# ls -al
total 24
drwx——.  6 root root    4096 Jun 26 20:10 .
drwxr-xr-x.  9 root root    4096 Jun 16 23:24 ..
drwxr-xr-x  23 root root    4096 Jun 26 02:04 113
drwxr-xr-x  20 root solusvm 4096 Jun 25 10:44 117
drwxr-xr-x  21 root root    4096 Jun 25 11:05 118
drwxr-xr-x   2 root root    4096 Jun 26 20:10 119
[/code]
21.      Make sure perms and ownership are OK
22.      Change to the directory you just created and wget the tar.gz that you uploaded in step 16
[code lang=”bash”]
[root@reek private]# cd 119
[root@reek 119]# ls
[root@reek 119]# pwd
/vz/private/119
[root@reek 119]# wget source.com/backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar.gz[/code]
Again, make sure you removed the – in the front of the filename. You can do this now on the destination server if you want too.
[code lang=”bash”]
[root@reek 119]# ls
backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar.gz
[/code]

23.       I ran into some errors when trying to untar the tar.gz, so follow these commands:
[code lang=”bash”]
[root@reek 119]# gunzip backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar.gz
gzip: backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar.gz: decompression OK, trailing garbage ignored
[root@reek 119]# tar xf backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar
[root@reek 119]#
[/code]
24.      Okay, so note the VMID of the export and the new VPS are different in this case (3005136 and 119). So we need to manually move files after they have been uncompressed.[code lang=”bash”]
[root@reek 119]# ls
backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar etc vz
[root@reek 119]#
[/code]
25.      Drill down in the uncompressed files (/vz/private/YOUROLDVEID)
[code lang=”bash”]
[root@reek 3005136]# pwd
/vz/private/119/vz/private/3005136
[root@reek 3005136]# ls
aquota.group aquota.user bin boot dev etc home lib lib64 media mnt opt proc root sbin selinux srv sys tmp usr var
[root@reek 3005136]#
[/code] Here we have found what we are looking for! Our VMs data.
26.      You want to move all of these files to the /vz/private/NEWVEID directory. In our case /vz/private/119/
27.      So move those..

[code lang=”bash”]
[root@reek 3005136]# mv * /vz/private/119/
mv: overwrite `/vz/private/119/etc’? y
mv: cannot move `etc’ to `/vz/private/119/etc’: Directory not empty
[root@reek 3005136]# ls
etc
[root@reek 3005136]#
[/code] It gave me an error because there was already an etc/ in the /vz/private/119 dir. So remove that old etc and put the one from the decompressed file. Make sure files in both seems fine before removing and moving…
28.
[code lang=”bash”]
[root@reek 3005136]# rm -rf /vz/private/119/etc/
[root@reek 3005136]# ls
etc
[root@reek 3005136]# mv etc/ /vz/private/119/
[root@reek 3005136]#
[/code]
29.      Now we are in business and almost done. Drill down into our target directory (/vz/private/119). Make sure everything looks good. Compare dirs with other VMs you have that are working if you need a sanity check.
[code lang=”bash”]
[root@reek 3005136]# ls
[root@reek 3005136]# cd ..
[root@reek private]# cd ..
[root@reek vz]# cd ..
[root@reek 119]# ls
aquota.group aquota.user backups-3005136-veportal_3005136_06-26-14_75f32b95cc_vesave.tar bin boot dev etc home lib lib64 media mnt opt proc root sbin selinux srv sys tmp usr var vz
[root@reek 119]#
[root@reek 119]# pwd
/vz/private/119
[/code]
30.     Remove the vz folder, we don’t need that anymore.
[code lang=”bash”]
[root@reek 119]# rm -rf vz/
[/code]
31.      Almost there. Try and start your new VPS.
[code lang=”bash”]
[root@reek 119]# vzctl start 119
Container config file does not exist
[root@reek 119]#
[/code]

Create the VMs config file
32.      It wont start without a config file! You’ll need to make one. Its easy, lets do it now.

[code lang=”bash”]
[root@reek 119]# cd /etc/vz/conf/
[root@reek conf]# ls
0.conf              102.conf.destroyed  104.conf.destroyed  106.conf.destroyed  108.conf.destroyed  110.conf.destroyed  112.conf.destroyed  114.conf.destroyed  116.conf.destroyed  118.conf            120.conf.destroyed  ve-105.conf-sample  ve-107.conf-sample  ve-basic.conf-sample  ve-vswap-1024m.conf-sample  ve-vswap-256m.conf-sample  ve-vswap-4g.conf-sample    ve-vswap-solus.conf-sample 101.conf.destroyed  103.conf.destroyed  105.conf.destroyed  107.conf.destroyed  109.conf.destroyed  111.conf.destroyed  113.conf            115.conf.destroyed  117.conf            119.conf.destroyed  121.conf.destroyed  ve-106.conf-sample  ve-108.conf-sample  ve-light.conf-sample  ve-vswap-1g.conf-sample     ve-vswap-2g.conf-sample    ve-vswap-512m.conf-sample
[root@reek conf]#
[/code]
33.       Copy a config file of an existing running VPS. I’ll use 118.
[code lang=”bash”]
[root@reek conf]# cp 118.conf 119.conf
[/code]
34.      Edit that config file. You’ll need to update just a few fields towards the bottom. OS template, IP, and Hostname. Make sure these match with the data you gave SolusVM when you created the VM (Hostname, IP) and from the export (OS+arch).
[code lang=”bash”]
[root@reek conf]# vi 119.conf
OSTEMPLATE=”centos-6.5-x86_64″
ORIGIN_SAMPLE=”vswap-solus”
IP_ADDRESS=”5.254.114.190″
HOSTNAME=”wiki.import2.source.com”
[/code]
35.    Try and start your VPS it should come right up
[code lang=”bash”]
[root@reek conf]# vzctl start 119
Starting container…
stat(/vz/root/119): No such file or directory
stat(/vz/root/119): No such file or directory
stat(/vz/root/119): No such file or directory
Initializing quota …
Container is mounted
Adding IP address(es): 5.254.114.190
Setting CPU limit: 400
Setting CPU units: 1000
Setting CPUs: 4
Container start in progress…
[root@reek conf]# vzlist -a
CTID      NPROC STATUS    IP_ADDR         HOSTNAME
113         79 running   5.254.114.1XX   server.xxxxxxxx.com                             
117         16 running   5.254.114.1XX   xxxxxxx.com
118         86 running   5.254.114.2XX   xxxx.xxxxxx.com
119         19 running   5.254.114.190 wiki.import2.source.com
[root@reek conf]#
[/code]
36.       Check in the SolusVM master your VM should be green and running fine.
Don’t forget to remove the backup file that you used.

Comment on this post if I missed anything or if you are having any issues:).