How to connect to the MySQL server on your cloud computer from your local laptop

ssh to your droplet:

ssh mcnulty-irmak

If you don't have emacs there, you can install it with this (or you can just use vi as an editor):

sudo apt-get install emacs

We will edit the configuration file for mysql:

sudo emacs /etc/mysql/my.cnf

In this file, there is a line:

bind-address = 127.0.0.1

That tells mysql to only accept connections from 127.0.0.1, in other words localhost, in other words only the cloud server itself.

Comment that out

#bind-address = 127.0.0.1

Now the mysql server will be open to connections from other ips as well. But we need to restart it so that the changes take effect.

sudo /etc/init.d/mysql restart

Alright. Now, generally, the mysql server is open to listening connections from other ips. However, the tables in your databases specifically do not have permissions for outside users to mess with them. We need to grant those privileges. Start your mysql client:

mysql -u root -p

Generally, this should work, but if you cannot connect and get an error message instead, try this:

sudo pkill mysql

This will end all of the currently running mysql processes and a new one will start running. Again, you shouldn't have to do this, but do it if you get an error, and then try running the mysql client again with

mysql -u root -p

Once in there, do this:

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

This is granting all privileges on *.* (all tables in all databases) to the 'root' user connecting from any ip (% stands for any ip)

You are done configuring mysql! Let's go back to your own laptop. Locally, install the pymysql module:

pip install pymysql

You're done. The python code below should work now:

import pymysql
db = pymysql.connect(host="104.131.124.114",  #your cloud ip
                     user='root',
                     passwd='yourrootpassword',
                     db='tennis')

cursor = db.cursor()

cursor.execute("SELECT * FROM french_men_2013")
for i in cursor.fetchall():
    print i

cursor.close()
db.close()