Setting up MySQL on Linux and connecting using MySQL Administrator

There are a few “gotchas” if you are trying to setup MySQL on Linux server, Ubuntu in this case, and connect to it using MySQL Administrator from somewhere else. Here is what worked for me…

Install MySQL server and MySQL GUI Tools

• To install MySQL on a Ubuntu server use:
  sudo apt-get install mysql-server
• If you are going to be using MySQL with PHP then you will need the PHP module for MySQL 5:
  sudo apt-get install php5-mysql
• Download and install the MySQL GUI Tools Bundle for the OS you will be connecting from.
• Start MySQL Administrator. Enter any name you like for “Stored Connection:”, the IP or DNS name of your MySQL Server for “Server Host:”, “root” for the “Username:” and the root password for “Password:”. The default MySQL port is 3306.

If you try to connect to your server using MySQL Administrator from a remote host without configuring your MySQL server bindings you will likely see something similar to the following error:

The problem is that by default MySQL server is only bound to the localhost/loopback IP of 127.0.0.1. This was probably done so MySQL is locked down by default which is a good security feature.

Configure MySQL bindings

• To bind MySQL to the real IP address on your server, you will need to comment out the “bind-address = 127.0.0.1” line in the /etc/mysql/my.cnf file using your favorite text editor. This file is usually locked down so only someone with root can write to it. To work around this use “sudo” before your text editor command:
  sudo nano /etc/mysql/my.cnf
  or
  sudo vi /etc/mysql/my.cnf

  Change:

  bind-address = 127.0.0.1
  to
  #bind-address = 127.0.0.1
• Save the file and then restart MySQL Server:
  sudo /etc/init.d/mysql restart

Now MySQL Administrator will make a connection but will likely return the following error:

It still doesn’t work but we are a bit closer. MySQL associates IP addresses and/or DNS names with users. By default, root can only connect from the localhost so we will need to fix this.

Grant users remote access

• From your MySQL server enter the following to start up the command line MySQL client:
  mysql -u root -p

  When prompted, enter your root password and then you should see an “mysql>” prompt.

• Now we need to grant root the ability to connect from the host you want to use MySQL Administrator on. You can do this by DNS or by IP address with ‘%’ as a wildcard. I like to do it by IP so I would use ‘192.168.%.%’. This will allow me to connect from any computer with an IP that starts with 192.168 which is the typical default private IP range used by most broadband routers that geeks have in their homes. Here is an example (replace ‘yourpassword’ with your password):
  mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’192.168.%.%’ IDENTIFIED BY ‘yourpassword’ WITH GRANT OPTION;

  The GRANT statement is granting all permission to the ‘root’ user connecting from the IP range or DNS name you have defined. If you wanted to do this by a DNS name you might do something like:

  mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%.example.com’ IDENTIFIED BY ‘yourpassword’ WITH GRANT OPTION;
• Now if everything is configured correctly you should be able to connect using MySQL Administrator.

All this is well and good but usually it is bad practice to do everything with your root account so you might want to create another user. This is particularly the case if you are going to use it for connecting from PHP.

• Assuming you are still in the MysQL command line client on your server, here is how you would add a user named ‘web_user’ with a password of ‘yourpassword’ with enough privileges to do nearly everything:
  GRANT ALL PRIVILEGES ON *.* TO ‘web_user’@’192.168.%.%’ IDENTIFIED BY ‘yourpassword’;

  Note the IP range. This is set so that you can connect via this user from MySQL Administrator running on your remote host if you like. Depending on what you are going to do with this user, you may want to restrict the permissions further to a specific database or even specific tables. You can also restrict the user to only read. It is best practice to only give your SQL user the minimum permissions needed to the job.

Create your first database and table

Now that you are able to connect with MySQL Administrator you can close out of the command client on the server (“\q” -> Enter) and use MySQL Administrator to create your first database and table.

• In MySQL Administrator click on “Catalogs” in the menu on the left. This should reveal the default internal databases that MySQL uses for itself. To create your own database right click on the empty area where the current databases are showing and choose “Create New Schema”. Type in the name for your new database and press enter and you should now see your database below the others.
• To create a table, right click on your newly create database/schema/catalog (or whatever it is called) and click “Create New Table”. From there you can enter a table name, columns, etc. On the “Table Options” tab there is a “Storage Engine” option. MySQL supports several different storage engines that are often trade offs between data integrity and performance. The two engines that are probably most well known are MyISAM and InnoDB. MyISAM sacrifices data integrity features like foreign key constraints for performance. InnoDB sacrifices performance for data integrity features such as FK constraints and ACID compliant transactions. You will need to decide which is best for your application.

Well that’s it for this post. I hope someone finds it useful.