How to install MySQL server in Ubuntu

It’s pretty simple:

sudo apt-get install mysql-server

How to connect to MySQL using Java

This is a useful and simple snippet:

import java.sql.Connection;
import java.sql.DriverManager;
public class Test {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            Connection c = DriverManager.getConnection(
              "jdbc:mysql://localhost/?user=root&password=");
            c.close();
            System.out.println("Success");
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
}

How to connect to MySQL using C#

Go to http://www.mysql.com and download the .NET connector. I used the “no installation” zip file. Uncompress it. In C# go to Project > Add Reference > Browse and select the file named MySql.Data.dll located in the bin directory of the zip file you just uncompressed. Press Ok.

Now the code:

using MySql.Data.MySqlClient;
...
MySqlConnection conn = new MySqlConnection(
    "SERVER=localhost;" +
    "DATABASE=test;" +
    "UID=username;" +
    "PASSWORD=mypassword;");

MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from table";
MySqlDataReader dr;

conn.Open();
dr = cmd.ExecuteReader();
while (dr.Read())
{
    Console.WriteLine(dr.getString(0));
}
conn.Close();
...

How to connect from other computers (different than localhost)

By default MySQL will deny connections from any computer but the localhost. This is configured this way because of security reasons.
To allow connections from other computers:

sudo nano /etc/mysql/my.cnf

Add a number sign at the beginning of the following line:

bind-address = 127.0.0.1

so it looks like this:

#bind-address = 127.0.0.1

Save your changes and restart your mysql server:

sudo /etc/init.d/mysql restart

After this, you will have to add remote users, so read the following section.

How to add/remove users

When you “grant” permissions to a user what you are telling MySQL is something like “Hey, add this user and set her permissions this way…”, so adding a user and assigning permissions becomes a one line task.

Important: MySQL grants permissions using the following syntax: username@hostname. This means if we are granting access permissions to user userx@host1 (userx connections from host named host1), then userx@host2 (userx connections from host named host2) will not go through. If you want to grant access to userx from any computer you have to use the % sign for hostname.

The following examples use the % for hostname, allowing connections from any remote computer. Be sure to include all single quotes shown here!

To grant a user permissions to connect to any database in the server from any remote computer:

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

To grant a user all permissions on database “test” from any remote computer:

grant all on test.* to 'username'@'%' identified by 'mypassword';

To grant a user permissions only to database “test” table MyTable from any remote computer:

grant all on test.MyTable to 'username'@'%' identified by 'mypassword';

To delete a user you have to follow the syntax we discussed before: username@hostname.

drop user 'username'@'%';

Running MySQL on a computer with multiple NIC’s

By default MySQL listens on localhost, and, for some reason, you cannot connect from Java using localhost or 127.0.0.1. You must specify one of your IP addresses to succeed. So, if you’re running a computer with multiple NIC’s this is what you have to do to accept connections:

• Open /etc/mysql/my.cnf with a text editor
• Look for this line:

bind-address = 127.0.0.1

• and replace it with this one:

# bind-address = 127.0.0.1

• Save the file and restart mysql:

/etc/init.d/mysql restart

• Check your /etc/hosts file includes the IP address where you are going to listen:

127.0.0.1 localhost
192.168.10.1 localhost  # use your IP address instead of 192.168.10.1

• Save (if necessary).
• Try your new settings:

mysql -u root -h 192.168.10.1

If you omit the /etc/hosts part you might get long connection times.