In this post I will show how to install MySQL on Windows 7 and the required steps to create a database with remote access for use with Arduino data logging projects. The first step is to download MySQL from http://dev.mysql.com/downloads/windows/installer/. I used the MySQL 5.6.21 installer full package, which is about 287 Mb in size. There is no need to create an account, just go to the bottom of the page and click on “No, thanks, just start my download”.
To install MySQL I choose “Server only”. After this, when configuring the MySQL server I choose “Development machine” for config type, and TCP/IP port 3306 for connectivity. I also check “Open firewall port for network access” – if you don’t you will have to do it manually.
In the next dialog window I input the root password. I will also create a new user for the Arduino projects by clicking on the “Add user” button. I baptized the new user “arduino”, with the password also being “arduino”. For host I choose “<All Hosts (%)>” as to allow for connections from anywhere. The role is “DB Admin”.
As I plan to use MYSQL often I will install it as a windows service, and I decide to leave it to start automatically at system startup. Don’t forget to check the log after completing all the steps to ensure that everything is OK.
After installing MySQL all I need is to create a new database for the Arduino data logger. This is done by starting the MySQL command line client, and inputting the root password. To create a database named ardulogger at the MySQL prompt I enter:
Enter password: // just enter the root password you have chosen during the instalation mysql> create database ardulogger; Query OK, 1 row affected (0.00 sec)
Next I have to create database table. For an application that logs the time, the temperature and the humidity I choose to create a table named ardudata, with the fields time, temp and humi. The time field is in TIME format, the two other are of FLOAT type. I will also insert some test data to start with.
mysql> use ardulogger; database changed mysql> create table ardudata (id float(6,0) not null auto_increment primary key, date date, time time, temp float (5,2), humi float(4,2) ); Query OK, 0 rows affected (0.01 sec) mysql> insert into ardudata (date, time, temp, humi) values ('2014-11-10','10:30:00', 23.4, 95.2); Query OK, 1 row affected (0.00 sec)
Now I have the database ardulogger , with the table ardudata and some values in the time, temp and humi fields. Next I will grant access to the user arduino to this database:
mysql> grant all privileges ON ardulogger.* to 'arduino'@'192,168.1.%' identified by 'arduino' with grant option; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> quit;
This will enable the user “arduino” to connect to the “ardulogger” database from the IP range 192.168.1.1 – 192.168.1.254
To visualize data I will use DBPlot, which can be downloaded from http://sourceforge.net/projects/dbplot/
After installing DBPlot I will create a connection to my database by clicking on “Add data source”. In the next window I make the following choices: The connection name is “Arduino data viewer”, server address is 127.0.0.1 (localhost) port 3306 (standard port for MySQL. User name is “arduino”, password being also “arduino”, as I have previously defined them. I also check the “save passord” option.
After clicking “save connection”, I right-click in the Data sources tab and select “connect”.
At this moment I have a complete MySQL installation on Windows 7, ready to accept remote connections from Arduino boards, plus a database visualization tool.
4 nov 2014 – First version
11 nov 2014 – Added date field to the database. After running the test datalogger for several days I realized that I was not able to tell when a particular data entry was made. Adding the date solves this problem