MySQL and Python

As you will find out by reading this post, I like to give my projects nicknames that can easily be turned into acronyms. Maybe its due to my days spent in the Navy or maybe I’m just a little crazy at times. This post involves a project I refer to as PEB, once again I will reveal what it stands for when it is complete. Unlike POL I expect PEB to be complete in the coming weeks. I am more excited about PEB than any of my other projects only because it has been rattling around in my brain for years and is finally coming to fruition. On to the meat of this post.

 

The backend for PEB relies on 3 specific components, Ubuntu Server, Python 2.7, and MySQL DB. I’m not going to do a walkthrough of installing Ubuntu server, there are tons of guides on the web and it’s not all that difficult. So once we get our server up and running we need to do a bit of maintenance

 sudo apt-get update –y && sudo apt-get upgrade -y 

 

Let it do its thing for a few minutes, always a good idea to give it a reboot after an update just in case

 sudo shutdown –r now

 

Ok now we can get started with the guts of our server. First let’s make sure Python is ready to go, there is a good chance that python is already installed on your system, let’s check

 python -V

If you get an output similar to

 Python 2.7.12 

you are good to go, if not let’s get Python installed along with a few other items we may need.

 sudo apt-get install python

Once this completes let’s get PIP installed. PIP is the python package manager used for installing most dependencies you will end up needing.

 sudo apt install python-pip 

For some reason, at least for me, it never installs the most recent version of PIP. So let’s fix that.

 sudo pip install --upgrade pip 

Ok, so Python is ready to go, the other part of this project entails using Python to get data to a MySQL database. We need two things, the MySQL Python Connector and a MySQL Database with a table. Let’s start with the MySQL Connector, at one point this could be installed directly from PIP, but that has changed so we have to do it manually. Go to http://dev.mysql.com/downloads/file/?id=465651 and download the connector. I chose to download it on my Windows machine and the SCP it over with MobaXterm. You can very well just use wget. Now we have it on the server, time to install it.

 sudo dpkg -i mysql-connector-python_2.1.4-1ubuntu16.04_all.deb 

Now we need a MySQL DB to put all that awesome useful data into, time to install and configure.

 sudo apt-get install mysql-server 

While it is installing it will ask you to set a root password, make it something strong, you know the drill. Once the install has completed let’s add a bit more security to our MySQL instance, this part is optional but highly recommended. It will ask you a few questions about the security settings of MySQL. Setting things such as remote access by root to false is generally a good idea. You can decide when it comes to the other options.

 sudo mysql_secure_installation 

So we’ve got MySQL running and secure, let’s get it setup to handle our data. First thing, log in.

 mysql -p -u root 

Great, lets create a Database

 create database newdbname; 

Select the Database so we can create a table in the database

 use newdbname; 

We need a table in that database

 CREATE TABLE newtablename (field1 INT(13), field2 VARCHAR(255) CHARACTER SET utf8mb4, field3 VARCHAR(255) CHARACTER SET utf8mb4); 

Good, now how do we go about seeing this Database? I prefer to use a GUI based tool called My SQL Workbench, it’s great for new users as it gives you the ability to use a tabbed interface to run different queries remotely. At this point you’re saying “But we disable remote root access?”. That is a correct statement, we need to make a few changes to allow remote access to the database from a non root user. Since we are still in MySQL let’s get it set first. We are going to add a new user and give it permissions for remote access. This can be done in one line of code.

 GRANT ALL PRIVILEGES ON newdbname.* To 'user'@'%' IDENTIFIED BY 'password'; 

Let break this down so you can properly fill in the required fields.

newdbname.* = This identifies the DB we want to gives access to  along with all of the tables within it which is identified by the wildcard character

‘user’@’%’ = This identifies which user and from where they can connect. In this case replace user with the desired username. The % character represents any remote IP address.

IDENTIFIED BY ‘password’ = This sets the password for the user, you know the drill.

At this point we need to flush the permissions so that MySQL knows what the hell is going on, after the permissions flush exit MySQL.

 flush privileges; exit; 

The last step to gaining remote access to your MySQL install is to comment out the Bind Address in mysqld.conf. If we don’t comment this out only users on the localhost will be able to access the database, by default the bind address is set to 127.0.0.1. So let’s comment it out.

 sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf 

Once in the file you will see the bind address, go ahead and hit insert and add a # to the beginning of the line. Let’s save and quit vi, hit the escape key, select shift + semicolon, WQ and enter.

 

At this point you should be able to open MySQL Workbench and remotely access your server and get to work. Have fun and good luck. Let me know if I missed something or if you find this useful.

%d bloggers like this: