MySQL on Linksys NSLU2

MySQL

Return to index

MySQL

MySQL is a open source database program. This means it can be used and modified by anyone without having to pay or acknowledge the owner of the software. Open source doesn't mean it isn't as good as paid software, especially in this case because even Google Inc relies on this software. In this documentation I am showing how to get MySQL running on the slug, including some C++ source on how to remotely read your databases over tcp/ip.

First step is to install MySQL on the NSLU2, this is easy because we will use ipkg for this. Login to your slug and enter:

ipkg install mysql

After installing, there is no 'root' password set for the program. To improve security we can change the 'root' password for mySQL. There is more then one way to set the password, but here is one of them:

shell> mysql -u root mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password');
mysql> FLUSH PRIVILEGES;

Delete the standard user from the user table in mysql database. This step seems to be nessesary if you want to connect remotely to MySQL, because this user seems to be blocking other ip adresses then localhost.

DELETE FROM mysql.user WHERE Host='localhost' AND User='';
FLUSH PRIVILEGES;

Ok, let us create a new database with tables

mysql -u root mysql -p
Enter password: *******
CREATE DATABASE foo;
USE foo;
CREATE TABLE contacts(
contact_id int unsigned not null auto_increment primary key,
first_name varchar(30),
last_name varchar(30));

Now we can fill up the database with values, for that we use the next command:

INSERT into contacts (first_name, last_name) values ('John','Doe');
INSERT into contacts (first_name, last_name) values ('Jane','Doe');

And we have to tell MySQL who is allowed to access the database:

grant all on foo.* to me@'%' identified by 'mypassword';

If we want to use MySQL over the network or over the internet we have to edit the configuration file for MySQL. Edit /opt/etc/my.cnf and change the line with skip-networking by putting an # in front of it. This will disable this command and thus making networking possible. After changing the config file it is neccesary the stop and restart the server, this can be done with the next 2 commands:

/opt/etc/init.d/S70mysqld stop
/opt/etc/init.d/S70mysqld start

To make communication possible between a computer on the internet and the slug's MySQL server, we have to open a port on our router/firewall. The TCP port MySQL uses is 3306. So make sure that packets send to this port are forwarded to the slug/NSLU2. Next we will write a little c program to read the values from any other computer in the world (this program I found on the internet, I just modified it slightly to make it compile).

#include "/opt/include/mysql/mysql.h"
#include <stdio.h>

int main() {
   MYSQL *conn;
   MYSQL_RES *res;
   MYSQL_ROW row;

   char *server = "teune.homeip.net";
   char *user = "me";
   char *password = "mypassword";
   char *database = "foo";

   conn = mysql_init(NULL);

   /* Connect to database */
   if (!mysql_real_connect(conn, server,
         user, password, database, 0, NULL, 0)) {
      fprintf(stderr, "%s\n", mysql_error(conn));
      return 0;
   }

   /* send SQL query */
   if (mysql_query(conn, "SELECT * FROM contacts")) {
      fprintf(stderr, "%s\n", mysql_error(conn));
      return 0;
   }

   res = mysql_use_result(conn);
   
   /* output fields 1 and 2 of each row */
   while ((row = mysql_fetch_row(res)) != NULL)
      printf("%s %s\n", row[1], row[2]);

   /* Release memory used to store results and close connection */
   mysql_free_result(res);
   mysql_close(conn);
   return 0;
}

Install the development enviroment with ipkg install unslung-devel(see other articles on this site).Download the program and compile it with:

gcc readmysqldb.c -L/opt/lib/mysql -lmysqlclient -Xlinker -rpath -Xlinker /opt/lib/mysql -Xlinker -rpath -Xlinker /opt/lib -o readmysqldb

And execute it with ./readmysqldb. And now it reads the values out of MySQL foo database! Change the variabele *server in the source into your computers own ip address or domain name!

Here is an other example. This is the C++ code I used for my guestbook. Have fun experimenting with MySQL.

#include <iostream>
#include <string>

#include "/opt/include/mysql/mysql.h"

#include "util.c"

void getword(char *word, char *line, char stop);

char x2c(char *what);
void unescape_url(char *url);

void plustospace(char *str);

using namespace std;

int main(int argc, char *argv[])

{
	cout <<
      "Content-type: text/html\n\n"
      "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">"

   	"<html>"
	  	"<head>"
	  		"<meta http-equiv=\"Content-Type\" content=\"text/html; charset=iso-8859-1\"><title>Guestbook</title>"

	  		"<base target=\"_blank\">"
	  		"<link rel=\"stylesheet\" type=\"text/css\" href=\"/style_main.css\">"

	  	"</head>"
	  	"<body>"

   	"<h2>Guestbook</h2>";

   char Str[350];

   cin.width (350);
   cin >> Str;

   char w[350];
   string comment, alias;

   while(Str[0] != '\0')
   {

      getword(w,Str,'=');
      plustospace(w);

      unescape_url(w);
      if(!strcmp(w,"comment"))

      {
    	  	getword(w,Str,'&');
        	plustospace(w);

        	unescape_url(w);
        	comment=w;
		}
      else if(!strcmp(w,"alias"))

      {
      	getword(w,Str,'&');
         plustospace(w);

         unescape_url(w);
         alias=w;
      }
	}

	MYSQL *conn;
   MYSQL_RES *res;
   MYSQL_ROW row;

   conn = mysql_init(NULL);

   /* Connect to database */
   if (!mysql_real_connect(conn, "domain", "user", "password", "database", 0, NULL, 0))

   {
      cerr << mysql_error(conn);
      return 0;

   }
   /* send SQL query */
   string Str2="INSERT INTO guestbook (comment, alias) VALUES ('"+comment+"','"+alias+"')";

   mysql_query(conn,Str2.c_str());
   mysql_close(conn);

   cout <<
    	"<p> Thank you " << alias << " for contributing to my guestbook.</p>"

   	"<h4><a href=\"/\" target=\"_top\"><img src=\"/img/rolodex1.gif\" alt=\"Index\" border=0/>Return to index</a></h4>"

   	"<hr><br><font size=\"1\">This site is brought to you by the <a href=\"http://www.nslu2-linux.org/\">\"slug\"</a>.</font>"

  		"</body></html>\n\n";

	return (0);
}

Return to index

80x51
[ Join Now \| Ring Hub \| Random \| << Prev \| Next >> ]

Embedded Technology
[ Join Now \| Ring Hub \| Random \| << Prev \| Next >> ]

This site is brought to you by the "slug".