Solutions to IT problems

Solutions I found when learning new IT stuff

Manage MySQL running on Ubuntu Server with a GUI

leave a comment »


Introduction

In my daily work I often use databases as a tool for cleaning, reorganizing or visualizing different kind of research data. For these task a GUI gives you so much more compared to the command line tool.

The easy way

Install and configure phpMyAdmin, a web based MySQL Administration GUI. You must have a web server (eg. apache) installed to be able to use it. Depending on your usage I highly recommend to take certain security aspects into account.

Connect using a Client GUI Tool

An alternative to MySQL Workbench is HeidiSQL a small, lightweight client (Windows) for MySQL and MS SQL. Following steps will work for either product. I assume you followed my previous tutorial on how to connect to Ubuntu Server and hence be able to use your editor of choice if you do not want to do it in vi.

  1. Open /etc/mysql/my.cnf
  2. Find the line with the entry bind-address
  3. Comment that line
  4. Restart mysql: sudo service mysql restart
  5. In the terminal connect to mysql: mysql -u root -p
  6. You be prompted for the password of user root in mysql
  7. In mysql run: USE mysql; Update user set host='%' where user='root'; FLUSH PRIVILEGES;

The last line allows the user “root” to connect from any (%) host. It is advisable to replace it with the IP Address of the computer you are trying to connect from. It is also advisable that you create a new mysql user, give that user the required privileges and only allow remote login for that user.

To remove host ‘%’ from root, delete that line in the GUI or run DELETE FROM user where user='root' and host='%'; If you need to add multiple hosts i suggest using the mysql host table and follow the mysql access control reference guide. I also suggest to setup Ubuntus Uncomplicated Firewall to limit access to your server also on mysql port.

Advertisements

Written by kienerj

October 28, 2011 at 13:44

Posted in Database, Linux, MySQL, Operating Systems

Tagged with ,

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: