ssh tunneling to aws instance

Tunneling to AWS instance using pem file

How to tunnel to bastion instance using ssh tunneling

Usually you are going to need this on jump server or what we bastionwhitelist your IP to the corresponding server before you attempt to ssh tunnel to it.

How do I do that?

Go to your terminal and issue

ssh -i /PATH/TO/YOUR-PEM-FILE.pem -N -L 3308:YOUR-RDS-SERVER:3306 ec2-user@YOUR-AWS-SERVER-IP-OR-NAME

What is happening?
You will get your pem file and use that as a token to jump to your server. In this example I gave and RDS of mysql which by default has port of 3306. And I have given 3308 which will be used from my machine to jump to the server as needed.

what then?

Now, your local machine is configured to relay – forward – the port to the destination and whatever you throwing to your local machine’s 3308 port will be forwarded to the remote servers port and you can access the RDS.

Group by week mysql

How to group by week on mysql

Group by week in mysql is common specially for reporting. Here is how to do it.

If the column is with format yyyy-mm-dd


SELECT COUNT(*), YEARWEEK(__DATE_COLUMN__)
FROM __TABLE__ 
WHERE (__DATE_COLUMN_) > '2018-03-01 00:00:00'
GROUP BY YEARWEEK(__DATE_COLUMN__)

If the column is unixtime stamp format


SELECT COUNT(*), YEARWEEK(FROM_UNIXTIME(__DATE_COLUMN__))
FROM __TABLE__ 
WHERE FROM_UNIXTIME(__DATE_COLUMN__) > '2018-03-01 00:00:00'
GROUP BY YEARWEEK(FROM_UNIXTIME(__DATE_COLUMN__))

Where the __TABLE__ is he mysql table and __DATE_COLUMN__ is the column you are interested.

Add scrollbar to mysql result in terminal

MySQL command line scrollbar

MySQL command line provides a full fledged application that allows interaction with mysql engine so easy.

In MySQL command one thing that would annoy is when there is larger result set and scrolling is static or not even accessible.

If you have larger result coming from mysql and you want to scroll through the result on mysql terminal?

You might ask

How to add scrollbar to mysql result in mysql command line?

Reading larger result set in mysql command

Or have you asked how to scroll on results of terminal mysql?

Solution

Go to your terminal and do


mysql> pager less

This would allow you to have that feeling of being on vim

See how to configure LAMP on Amazon here

mysql command not found on mac after installation

After downloading mysql dmg on mac if you go to terminal and try to issue mysql command and get mysql command not found there keep reading.

This, in most cases, is the terminal not knowing the mysql command unless you go to the actual installation folder.

To check that out, go to

/user/local/mysql/bin/mysql --version

If this is giving you the right mysql information, then it is a matter of telling the terminal what the mysql command is.

Temporary fix

export PATH=$PATH:/usr/local/mysql/bin

The above command would allow the command files to be recognized by the current issuing terminal. But you won’t be able to access those on new terminal

Permanent fix

echo "export PATH=$PATH:/usr/local/mysql/bin" >>~/.bash_profile

The above command would simply append the path command you exported to the .bash_profile file.

Screen Shot 2016-04-10 at 9.21.59 PM

Upgrade mysql version from 5.5 to 5.6

Upgrade mysql version

This would probably make sense on Amazon EC2 instance where it comes by default with
mysql 5.5, as of this writing, and you want to upgrade mysql to the next version.

In this example I have used version 5.5 for the upgrade mysql example. But, technically the methodology would work for other versions as well unless mysql drastically changes folder locations.

How to upgrade mysql from 5.5 to 5.6

First go ahead and move the mysql 5.5 to new folder

sudo mv /var/lib/mysql /var/lib/mysql55

Then grab the tar from mysql download page [Here]

Once you download the tar file containing the new mysql version do the following commands on your terminal.

sudo tar -xvf yourDownloadedTar.tar
sudo rpm -ivh MySQL-shared-5.6.17-1.el6.x86_64.rpm
sudo rpm -ivh MySQL-client-5.6.17-1.el6.x86_64.rpm
sudo rpm -ivh MySQL-server-5.6.17-1.el6.x86_64.rpm

This would upgrade your current mysql version to mysql 5.6 or which ever is the new mysql version.

Then restart mysql for the change to take effect.

error duplicate entry for key when creating new unique key mysql

If you got the above message when you try to add a new unique key on multiple column in mysql as

ALTER TABLE table_name ADD UNIQUE (col1, col2, col3);

Mysql would alert you if there is an already existing row in your table that is violating the newly added constraint.
Say you want to have uniqueness on col1 and col2 and assume col1 has value of “one” and col2 has value of “love”

Before this constraint you can have another row with the values of “one” and “love” for col1 and col2 respectively. But now that is not possible so you have to take care of that before applying

database

Connecting to Vagrant [PuPHPet] MySql from host

Probably here with – I can’t connect to mysql on vagrant machine Yup I have been there

If you are php developer and are using vagrant you would want to use mysql tool to access the vagrant mysql server.

Here is the step by step procedure.

I will assume the PuPHPet configuration
First, get the user which can connect from any host

Log into your vagrant

vagrant ssh

Then open the mysql configuration file

vi /etc/mysql/my.cnf

And update the bind-address part to be 0.0.0.0. This will allow address to be used from any host.
If you want to specifically access from some host only, then put IP over there.

Then being in your vagrant machine run

mysql -uroot -p123

This will be depending on if you have provided a password for root the default on puPHPet would ask password so you would have a password

After this allow root user to access everything from everywhere.. or you can create another user and grant that

mysql > GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123';
mysql > FLUSH PRIVILEGES;

Then to make the sql pickup the changes made so far

sudo service mysql restart

Then being on your host machine open your sequel pro – if you are on mac.. or other clients you would use for mysql.

Mysql Host: 127.0.0.1
Mysql user: root
Mysql Password: 123

SSH Host: 127.0.0.1
SSH Username: vagrant – this is the default one
SSH key -> point the key in puphpet/files/dot/ssh/id_rsa
SSH Port: 2222 – this is also the default ssh port forwarding, if you change this change it here too..

See how to Setting up vegrant machine from scratch here

Installing php-mysql-driver using mysqlnative driver

PHP has been providing us starting form 5.3 the mysqlnd [mysql native driver] that will liberate us from using the mysql client library. And it will be shipped along with the php
But if we remove the mysqllib for some reason and wanted to load it again..
This will be for linux fedora – I have done this on Amazon EC2 server

sudo yum shell
remove php-mysql
install php-mysqlnd
run
quit

you will see the new mysql ini files being added in /etc/php.d folder
Then restart httpd

sudo service restart httpd

Upgrade mysql 5.5 to mysql 5.6

This would probably make sense on EC2 instance where it comes by default with mysql 5.5

First shutdown the mysql server and then go ahead and move the mysql 5.5 to new folder

sudo mv /var/lib/mysql /var/lib/mysql55

Then grab the tar from Here

Once you download the tar file

sudo tar -xvf yourDownloadedTar.tar
sudo rpm -ivh MySQL-shared-5.6.17-1.el6.x86_64.rpm
sudo rpm -ivh MySQL-client-5.6.17-1.el6.x86_64.rpm
sudo rpm -ivh MySQL-server-5.6.17-1.el6.x86_64.rpm

yup.. that would be it.
Then restart the mysql and it shall be updated