domingo, 25 de janeiro de 2009

GtkSharp TreeView Tutorial - Mono

GtkSharp TreeView Tutorial - Mono

GtkSharp TreeView Tutorial

Introduction

The GTK TreeView widget is used to display data in one of the most basic and intuitive ways possible: a list. Each row in the list can be seporated into multiple columns, and rows in the list can contain child rows to create an expandable-list, or tree.

The TreeView can be extremely intimidating at first, but it is extremely powerfull - especially when compared to the ListBox and ListView controls from the Windows Forms toolkit.

Model, View, Controller

The TreeView uses the Model-View-Controller (MVC) design pattern. Components of the TreeView are seporated into these three categories: The Model, which stores data to be displayed, the View, which controls how to display the data, and the controller, which controls what data is displayed, how it is sorted, etc.

Model

The TreeView has two basic models: ListStore, which stores a flat set of data, and TreeStore, which stores data that can contain sub-items (used for creating a Tree). All TreeView Models implement the Gtk.TreeModel interface.

View

The View is made up of three different parts as well - The column, the cell renderers, and the widget it's self.

The TreeView widget is responsable for laying out the columns and rows of data, as well as providing all the basic user-interaction functionality such as selecting items, etc.

Each TreeViewColumn contains at least one CellRenderer. Cell renderers are what actually display the data - items in the model are mapped to cell renderers.

The following cell renderers are avaliable:

  • CellRendererText - Used to display text
  • CellRendererPixbuf - Used to display images
  • CellRendererProgress - Used to display a progress bar
  • CellRendererCombo - Used to display a drop-down box
  • CellRendererToggle - Used to display a check box

CellRenderers are seporate from TreeViewColumns for added flexibility, allowing you to have an extremely fine-tuned treeview tailored to your application. For example you can pack an image and text into the same column, which often makes much more sense than creating a seporate column for each.

Controller

Controllers modify how the data in the model is passed off to the View, and let you do things such as sorting and filtering the data.

Your first TreeView

Setting it up

Here is a basic example of how to use the TreeView and all its related components. In our example, we will show a simple list of song titles and artist names:

#file: TreeViewExample.cs
//mcs -pkg:gtk-sharp TreeViewExample.cs
public class TreeViewExample
{
public static void Main ()
{
Gtk.Application.Init ();
new TreeViewExample ();
Gtk.Application.Run ();
}

public TreeViewExample ()
{
// Create a Window
Gtk.Window window = new Gtk.Window ("TreeView Example");
window.SetSizeRequest (500,200);

// Create our TreeView
Gtk.TreeView tree = new Gtk.TreeView ();

// Add our tree to the window
window.Add (tree);

// Create a column for the artist name
Gtk.TreeViewColumn artistColumn = new Gtk.TreeViewColumn ();
artistColumn.Title = "Artist";

// Create a column for the song title
Gtk.TreeViewColumn songColumn = new Gtk.TreeViewColumn ();
songColumn.Title = "Song Title";

// Add the columns to the TreeView
tree.AppendColumn (artistColumn);
tree.AppendColumn (songColumn);

// Create a model that will hold two strings - Artist Name and Song Title
Gtk.ListStore musicListStore = new Gtk.ListStore (typeof (string), typeof (string));


// Assign the model to the TreeView
tree.Model = musicListStore;

// Show the window and everything on it
window.ShowAll ();
}
}

Compile and run the application, and you will end up with this:

Image:GtkSharpTreeViewTutorial1.png

Cool! So we have our TreeView displaying our two desired columns, now lets add some data in there.

Adding some data

 // Add some data to the store
musicListStore.AppendValues ("Garbage", "Dog New Tricks");

This inserts a new row into the Model. We specify the same number of arguments here as we defined when we constructed the Gtk.ListStore above.

As mentioned in the introduction, the TreeViewCells don't actually render any of your data themselfs - they just contain the Cell Renderers that do, so we need to create two Cell Renderers, one for each column:

  // Create the text cell that will display the artist name
Gtk.CellRendererText artistNameCell = new Gtk.CellRendererText ();

// Add the cell to the column
artistColumn.PackStart (artistNameCell, true);

// Do the same for the song title column
Gtk.CellRendererText songTitleCell = new Gtk.CellRendererText ();
songColumn.PackStart (songTitleCell, true);

The TreeView doesnt automatically know which cells are supposed to display which items from the Model, so we need to link it up:

  // Tell the Cell Renderers which items in the model to display
artistColumn.AddAttribute (artistNameCell, "text", 0);
songColumn.AddAttribute (songTitleCell, "text", 1);

The first argument specifies which Cell Renderer we want to assign something to, the second specifies which of the cell renderer's properties (converted to lowercase) we want to assign something to (CellRendererText.Text in our case), and the third argument specifies the position in the Model that the value should be obtained from. Above when we added a row to the model, we specified the artist first, then the song title, so we use that here. Remember the order of items in the Model is not automatically linked to the order of your columns in the TreeView in any way, so while keeping everythign in the same order makes things a lot easier to manage and understand, it is not required.

You are not limited to assigning one property to the store per CellRenderer, you can call AddAttribute for the same CellRenderer as many times as you would like, to control the different properties of the CellRenderer.

WOOHOO! We now have this:

Image:GtkSharpTreeViewTutorial2.png

Here's the complete code:

public class TreeViewExample
{
public static void Main ()
{
Gtk.Application.Init ();
new TreeViewExample ();
Gtk.Application.Run ();
}

public TreeViewExample ()
{
// Create a Window
Gtk.Window window = new Gtk.Window ("TreeView Example");
window.SetSizeRequest (500,200);

// Create our TreeView
Gtk.TreeView tree = new Gtk.TreeView ();

// Add our tree to the window
window.Add (tree);

// Create a column for the artist name
Gtk.TreeViewColumn artistColumn = new Gtk.TreeViewColumn ();
artistColumn.Title = "Artist";

// Create the text cell that will display the artist name
Gtk.CellRendererText artistNameCell = new Gtk.CellRendererText ();

// Add the cell to the column
artistColumn.PackStart (artistNameCell, true);

// Create a column for the song title
Gtk.TreeViewColumn songColumn = new Gtk.TreeViewColumn ();
songColumn.Title = "Song Title";

// Do the same for the song title column
Gtk.CellRendererText songTitleCell = new Gtk.CellRendererText ();
songColumn.PackStart (songTitleCell, true);

// Add the columns to the TreeView
tree.AppendColumn (artistColumn);
tree.AppendColumn (songColumn);

// Tell the Cell Renderers which items in the model to display
artistColumn.AddAttribute (artistNameCell, "text", 0);
songColumn.AddAttribute (songTitleCell, "text", 1);

// Create a model that will hold two strings - Artist Name and Song Title
Gtk.ListStore musicListStore = new Gtk.ListStore (typeof (string), typeof (string));

// Add some data to the store
musicListStore.AppendValues ("Garbage", "Dog New Tricks");

// Assign the model to the TreeView
tree.Model = musicListStore;

// Show the window and everything on it
window.ShowAll ();
}
}

Creating a Tree

To create a tree instead of a flat list, we use a Gtk.TreeStore as our model.

Gtk.TreeStore musicListStore = new Gtk.TreeStore (typeof (string), typeof (string));

Then, when we add a value into the model we specify the parent iter as the first argument:

Gtk.TreeIter iter = musicListStore.AppendValues ("Dance");
musicListStore.AppendValues (iter, "Fannypack", "Nu Nu (Yeah Yeah) (double j and haze radio edit)");

iter = musicListStore.AppendValues ("Hip-hop");
musicListStore.AppendValues (iter, "Nelly", "Country Grammer");

And now we end up with this:

Image:GtkSharpTreeViewTutorial-Tree1.png

Here's the complete example:

public class TreeViewExample
{
public static void Main ()
{
Gtk.Application.Init ();
new TreeViewExample ();
Gtk.Application.Run ();
}

public TreeViewExample ()
{
Gtk.Window window = new Gtk.Window ("TreeView Example");
window.SetSizeRequest (500,200);

Gtk.TreeView tree = new Gtk.TreeView ();
window.Add (tree);

Gtk.TreeViewColumn artistColumn = new Gtk.TreeViewColumn ();
artistColumn.Title = "Artist";

Gtk.CellRendererText artistNameCell = new Gtk.CellRendererText ();

artistColumn.PackStart (artistNameCell, true);

Gtk.TreeViewColumn songColumn = new Gtk.TreeViewColumn ();
songColumn.Title = "Song Title";

Gtk.CellRendererText songTitleCell = new Gtk.CellRendererText ();
songColumn.PackStart (songTitleCell, true);

tree.AppendColumn (artistColumn);
tree.AppendColumn (songColumn);

artistColumn.AddAttribute (artistNameCell, "text", 0);
songColumn.AddAttribute (songTitleCell, "text", 1);

Gtk.TreeStore musicListStore = new Gtk.TreeStore (typeof (string), typeof (string));

Gtk.TreeIter iter = musicListStore.AppendValues ("Dance");
musicListStore.AppendValues (iter, "Fannypack", "Nu Nu (Yeah Yeah) (double j and haze radio edit)");

iter = musicListStore.AppendValues ("Hip-hop");
musicListStore.AppendValues (iter, "Nelly", "Country Grammer");

tree.Model = musicListStore;

window.ShowAll ();
}
}

Filtering Data

The TreeView makes it very easy to prevent certain rows from being displayed, without having to remove them from the model.

Lets say we are starting out with this set of data:

Image:TreeViewExample6.png

We place a TreeModelFilter between the View (TreeView) and the model (ListStore) that filters what data is passed from the model to the view.

// Create the filter and tell it to use the musicListStore as it's base Model
filter = new Gtk.TreeModelFilter (musicListStore, null);


// Specify the function that determines which rows to filter out and which ones to display
filter.VisibleFunc = new Gtk.TreeModelFilterVisibleFunc (FilterTree);

// Assign the filter as our tree's model
tree.Model = filter;

We then create the FilterTree method, which determines which rows are visible and which are hidden:

private bool FilterTree (Gtk.TreeModel model, Gtk.TreeIter iter)
{
string artistName = model.GetValue (iter, 0).ToString ();
if (artistName == "BT")
return true;
else
return false;
}

And now we end up with this:

Image:TreeViewExample7.png

Here is a complete example demonstrating how you can use a text entry widget to control the filter.

public class TreeViewExample
{
public static void Main ()
{
Gtk.Application.Init ();
new TreeViewExample ();
Gtk.Application.Run ();
}

Gtk.Entry filterEntry;

Gtk.TreeModelFilter filter;

public TreeViewExample ()
{
// Create a Window
Gtk.Window window = new Gtk.Window ("TreeView Example");
window.SetSizeRequest (500,200);

// Create an Entry used to filter the tree
filterEntry = new Gtk.Entry ();

// Fire off an event when the text in the Entry changes
filterEntry.Changed += OnFilterEntryTextChanged;

// Create a nice label describing the Entry
Gtk.Label filterLabel = new Gtk.Label ("Artist Search:");

// Put them both into a little box so they show up side by side
Gtk.HBox filterBox = new Gtk.HBox ();
filterBox.PackStart (filterLabel, false, false, 5);
filterBox.PackStart (filterEntry, true, true, 5);

// Create our TreeView
Gtk.TreeView tree = new Gtk.TreeView ();

// Create a box to hold the Entry and Tree
Gtk.VBox box = new Gtk.VBox ();

// Add the widgets to the box
box.PackStart (filterBox, false, false, 5);
box.PackStart (tree, true, true, 5);

window.Add (box);

// Create a column for the artist name
Gtk.TreeViewColumn artistColumn = new Gtk.TreeViewColumn ();
artistColumn.Title = "Artist";

// Create the text cell that will display the artist name
Gtk.CellRendererText artistNameCell = new Gtk.CellRendererText ();

// Add the cell to the column
artistColumn.PackStart (artistNameCell, true);

// Create a column for the song title
Gtk.TreeViewColumn songColumn = new Gtk.TreeViewColumn ();
songColumn.Title = "Song Title";

// Do the same for the song title column
Gtk.CellRendererText songTitleCell = new Gtk.CellRendererText ();
songColumn.PackStart (songTitleCell, true);

// Add the columns to the TreeView
tree.AppendColumn (artistColumn);
tree.AppendColumn (songColumn);

// Tell the Cell Renderers which items in the model to display
artistColumn.AddAttribute (artistNameCell, "text", 0);
songColumn.AddAttribute (songTitleCell, "text", 1);

// Create a model that will hold two strings - Artist Name and Song Title
Gtk.ListStore musicListStore = new Gtk.ListStore (typeof (string), typeof (string));

// Add some data to the store
musicListStore.AppendValues ("BT", "Circles");
musicListStore.AppendValues ("Daft Punk", "Technologic");
musicListStore.AppendValues ("Daft Punk", "Digital Love");
musicListStore.AppendValues ("The Crystal Method", "PHD");
musicListStore.AppendValues ("The Crystal Method", "Name of the game");
musicListStore.AppendValues ("The Chemical Brothers", "Galvanize");

// Instead of assigning the ListStore model directly to the TreeStore, we create a TreeModelFilter
// which sits between the Model (the ListStore) and the View (the TreeView) filtering what the model sees.
// Some may say that this is a "Controller", even though the name and usage suggests that it is still part of
// the Model.
filter = new Gtk.TreeModelFilter (musicListStore, null);

// Specify the function that determines which rows to filter out and which ones to display
filter.VisibleFunc = new Gtk.TreeModelFilterVisibleFunc (FilterTree);

// Assign the filter as our tree's model
tree.Model = filter;

// Show the window and everything on it
window.ShowAll ();
}

private void OnFilterEntryTextChanged (object o, System.EventArgs args)
{
// Since the filter text changed, tell the filter to re-determine which rows to display
filter.Refilter ();
}

private bool FilterTree (Gtk.TreeModel model, Gtk.TreeIter iter)
{
string artistName = model.GetValue (iter, 0).ToString ();

if (filterEntry.Text == "")
return true;

if (artistName.IndexOf (filterEntry.Text) > -1)
return true;
else
return false;
}
}

Image:GtkSharpTreeViewTutorial8.png

Controlling how the model is used

The TreeView allows you write methods that extract specific data from your Model, and link your CellRenderers to them, rather than directly to the model.

This is one of the extremely useful features of TreeModel because it means you can store a reference any .NET object in the model that contains all the data you want your TreeView to display, instead of storing each piece of data individually as a string, so you don't have to maintain it in two places.

In the preveous examples we inserted both the song and artist into each row of the ListStore. If the TreeView is displaying a large amount of data this can use a lot of memory, and if the backend data changes (song title changes, etc.) the TreeModel doesnt know about it and you have to keep it in-sync manually.

Lets take the following example, and say we want to create a TreeView to display the data:

using System.Collections;

public class Song
{
public Song (string artist, string title)
{
this.Artist = artist;
this.Title = title;
}

public string Artist;
public string Title;
}

public class TreeViewExample
{
public static void Main ()
{
Gtk.Application.Init ();
new TreeViewExample ();
Gtk.Application.Run ();
}

ArrayList songs;

public TreeViewExample ()
{
songs = new ArrayList ();

songs.Add (new Song ("Dancing DJs vs. Roxette", "Fading Like a Flower"));
songs.Add (new Song ("Xaiver", "Give me the night"));
songs.Add (new Song ("Daft Punk", "Technologic"));

Gtk.Window window = new Gtk.Window ("TreeView Example");
window.SetSizeRequest (500,200);

Gtk.TreeView tree = new Gtk.TreeView ();
window.Add (tree);

Gtk.TreeViewColumn artistColumn = new Gtk.TreeViewColumn ();
artistColumn.Title = "Artist";
Gtk.CellRendererText artistNameCell = new Gtk.CellRendererText ();
artistColumn.PackStart (artistNameCell, true);

Gtk.TreeViewColumn songColumn = new Gtk.TreeViewColumn ();
songColumn.Title = "Song Title";
Gtk.CellRendererText songTitleCell = new Gtk.CellRendererText ();
songColumn.PackStart (songTitleCell, true);

Gtk.ListStore musicListStore = new Gtk.ListStore (typeof (Song));
foreach (Song song in songs) {
musicListStore.AppendValues (song);
}

tree.Model = musicListStore;

tree.AppendColumn (artistColumn);
tree.AppendColumn (songColumn);

window.ShowAll ();

}
}

The TreeView doesnt automatically know how to link up the fields in the Song class to the different CellRenderers, so instead of using AddAttribute as we did in our first example, we use SetCellDataFunc:

artistColumn.SetCellDataFunc (artistNameCell, new Gtk.TreeCellDataFunc (RenderArtistName));
songColumn.SetCellDataFunc (songTitleCell, new Gtk.TreeCellDataFunc (RenderSongTitle));

We then create two methods, which extract the information that we want from the Song object and set the appropriate property of the CellRenderer, "Text" in our case:

private void RenderArtistName (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
{
Song song = (Song) model.GetValue (iter, 0);
(cell as Gtk.CellRendererText).Text = song.Artist;
}

private void RenderSongTitle (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
{
Song song = (Song) model.GetValue (iter, 0);
(cell as Gtk.CellRendererText).Text = song.Title;
}

We now have only one item per row in the store, but we display two columns in the tree!

Image:GtkSharpTreeViewTutorial3.png

Both of the example methods above demonstrate how to modify one property of the CellRenderer, but you are certainly not limited to only modifing one.

This example is fairly pointless, but it demonstrates how to modify multiple properties. A more practical use for this would be to change the color based on the state of an object:

private void RenderArtistName (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
{
Song song = (Song) model.GetValue (iter, 0);
if (song.Artist.StartsWith ("X") == true) {
(cell as Gtk.CellRendererText).Foreground = "red";
} else {
(cell as Gtk.CellRendererText).Foreground = "darkgreen";
}
(cell as Gtk.CellRendererText).Text = song.Artist;
}


Image:GtkSharpTreeViewTutorial4.png

Here is the complete example:

using System.Collections;

public class Song
{
public Song (string artist, string title)
{
this.Artist = artist;
this.Title = title;
}

public string Artist;
public string Title;
}

public class TreeViewExample
{
public static void Main ()
{
Gtk.Application.Init ();
new TreeViewExample ();
Gtk.Application.Run ();
}

ArrayList songs;

public TreeViewExample ()
{
songs = new ArrayList ();

songs.Add (new Song ("Dancing DJs vs. Roxette", "Fading Like a Flower"));
songs.Add (new Song ("Xaiver", "Give me the night"));
songs.Add (new Song ("Daft Punk", "Technologic"));

Gtk.Window window = new Gtk.Window ("TreeView Example");
window.SetSizeRequest (500,200);

Gtk.TreeView tree = new Gtk.TreeView ();
window.Add (tree);

Gtk.TreeViewColumn artistColumn = new Gtk.TreeViewColumn ();
artistColumn.Title = "Artist";
Gtk.CellRendererText artistNameCell = new Gtk.CellRendererText ();
artistColumn.PackStart (artistNameCell, true);

Gtk.TreeViewColumn songColumn = new Gtk.TreeViewColumn ();
songColumn.Title = "Song Title";
Gtk.CellRendererText songTitleCell = new Gtk.CellRendererText ();
songColumn.PackStart (songTitleCell, true);

Gtk.ListStore musicListStore = new Gtk.ListStore (typeof (Song));
foreach (Song song in songs) {
musicListStore.AppendValues (song);
}

artistColumn.SetCellDataFunc (artistNameCell, new Gtk.TreeCellDataFunc (RenderArtistName));
songColumn.SetCellDataFunc (songTitleCell, new Gtk.TreeCellDataFunc (RenderSongTitle));

tree.Model = musicListStore;

tree.AppendColumn (artistColumn);
tree.AppendColumn (songColumn);

window.ShowAll ();

}

private void RenderArtistName (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
{
Song song = (Song) model.GetValue (iter, 0);

if (song.Artist.StartsWith ("X") == true) {
(cell as Gtk.CellRendererText).Foreground = "red";
} else {
(cell as Gtk.CellRendererText).Foreground = "darkgreen";
}

(cell as Gtk.CellRendererText).Text = song.Artist;
}

private void RenderSongTitle (Gtk.TreeViewColumn column, Gtk.CellRenderer cell, Gtk.TreeModel model, Gtk.TreeIter iter)
{
Song song = (Song) model.GetValue (iter, 0);
(cell as Gtk.CellRendererText).Text = song.Title;
}

}

Shortcuts - Writing Less Code

The Gtk TreeView API provides several convinence methods that makes it possible to create a baisc tree using much less code than we used above.

Here is the same demo as first example on this page, using these methods, note that it is significantly less code:

public class TreeViewExample
{
public static void Main ()
{
Gtk.Application.Init ();
new TreeViewExample ();
Gtk.Application.Run ();
}

public TreeViewExample ()
{
Gtk.Window window = new Gtk.Window ("TreeView Example");
window.SetSizeRequest (500,200);

Gtk.TreeView tree = new Gtk.TreeView ();
Gtk.ListStore musicListStore = new Gtk.ListStore (typeof (string), typeof (string));

tree.AppendColumn ("Artist", new Gtk.CellRendererText (), "text", 0);
tree.AppendColumn ("Title", new Gtk.CellRendererText (), "text", 1);

musicListStore.AppendValues ("Garbage", "Dog New Tricks");

tree.Model = musicListStore;

window.Add (tree);
window.ShowAll ();
}
}

Editable Text Cells

Making an editable text cell (so the user can click on the cell and modify the value) is extremely easy.

First we must mark the cell as editable, and assign a method to handle the Edited event, which fires when the user is done editing:

artistNameCell.Editable = true;
artistNameCell.Edited += artistNameCell_Edited;

Our event handler method needs to update the model with the new value that the user entered:

private void artistNameCell_Edited (object o, Gtk.EditedArgs args)
{
Gtk.TreeIter iter;
musicListStore.GetIter (out iter, new Gtk.TreePath (args.Path));

Song song = (Song) musicListStore.GetValue (iter, 0);
song.Artist = args.NewText;
}

And that's all there is to it!

Image:TreeViewTutorial-Editing1.png

Drawing icons in rows

For using Pixbuf you need, first to change the ListStore constructor, (notice the Gdk.Pixbuf parameter)

Gtk.ListStore musicListStore = new Gtk.ListStore (typeof (Gdk.Pixbuf), typeof (string),  typeof (string));

add the column that will render the pixbuf

tree.AppendColumn ("Icon", new Gtk.CellRendererPixbuf (), "pixbuf", 0);

the image to add

Image:TreeViewRupertIcon.png

and the AppendValues method with the correct parameters

musicListStore.AppendValues (new Gdk.Pixbuf ("TreeViewRupertIcon.png"), "Rupert", "Yellow bananas");

And then

Image:TreeViewTutorial-Pixbuf.png

Complete sample

public class TreeViewExample
{
public static void Main ()
{
Gtk.Application.Init ();
new TreeViewExample ();
Gtk.Application.Run ();
}

public TreeViewExample ()
{
Gtk.Window window = new Gtk.Window ("TreeView Example");
window.SetSizeRequest (500,200);

Gtk.TreeView tree = new Gtk.TreeView ();
Gtk.ListStore musicListStore = new Gtk.ListStore (typeof (Gdk.Pixbuf),
typeof (string), typeof (string));

tree.AppendColumn ("Icon", new Gtk.CellRendererPixbuf (), "pixbuf", 0);
tree.AppendColumn ("Artist", new Gtk.CellRendererText (), "text", 1);
tree.AppendColumn ("Title", new Gtk.CellRendererText (), "text", 2);

musicListStore.AppendValues (new Gdk.Pixbuf ("TreeViewRupertIcon.png"),
"Rupert", "Yellow bananas");

tree.Model = musicListStore;

window.Add (tree);
window.ShowAll ();
}
}

segunda-feira, 19 de janeiro de 2009

Linux: Acesso ao servidor MySQL [Dica]

Acesso ao servidor MySQL

Fala seus linuxeros...

Resolvi postar essa dica porque procurei muito sobre o assunto, achei coisas próximas ao meu problema, mas nenhuma resolvia.

Eu uso o Debian 4.1 (acho que é esse, ultima versão né?). Instalei o mysql-server-5.0 e criei um usuário com todos os privilégios (digita no Google "criar usuário + mysql + linux" e você aprende como criar um), e na hora de acessar o servidor de outra máquina não rolava.

Conversando com um amigo ele me disse para rodar o comando "netstat -ln | grep 3306" (3306 = porta padrão mysql). E a saída do comando foi:
tcp        0      0 127.0.0.1:3306            0.0.0.0:*               OUÇA

O problema está todo aí. Nesse "127.0.0.1". A porta 3306 só aceita conexões internas. Como mudar isso?

Algumas dicas diziam para ir no arquivo de configuração /etc/mysql/my.cnf e comentar a linha "skip-network(s)"... Não lembro se tem o S.

E ao ir no meu arquivo my.cnf não achei essa bendita linha. Procurei durante muito tempo e nada.

Então me veio uma luz... uhauaHuaha.. Onde está configurado 127.0.0.1?

Achei a linha... Agora chega de história, vamos pra dica.

Primeiro faça um backup do arquivo de configuração:

# cp /etc/mysql/my.cnf /etc/mysql/my.cnf.backup

Depois abra o arquivo de configuração com qualquer editor:

# gedit /etc/mysql/my.cnf

Procure por uma linha escrito:

bind-address = 127.0.0.1

Mude o IP para 0.0.0.0, a linha ficará:

bind-address = 0.0.0.0

Salve e saia do editor.

Reinicie o mysql-server:

# cd /etc/init.d
# ./mysql restart
ou
# ./mysqld restart

Espere subir e tente o acesso novamente.

Obs.: Se não achar a linha de "bind-address" e tiver a linha "skip-network(s)" comente essa linha e vê se funciona.

Sou novato em Linux, e dadas as dificuldades de um novato se virar resolvi postar essa dica, espero ter ajudado.

Abraço seus linuxeros!


Outras dicas deste autor
Nenhuma dica encontrada.

Leitura recomendada
Dica Linux recomendada Convertendo varchar em date no MySQL
Dica Linux recomendada Automatizando o processo de backup do MySQL
Dica Linux recomendada Criando e associando usuários à bancos de dados específicos
Dica Linux recomendada Erro ao iniciar o MySQL
Dica Linux recomendada Erro ao conectar no servidor MySQL

Comentários
Comentário enviado por heitor.mejias em 24/06/2008 - 14:48h:

Mando bem, vlwww...

Comentário enviado por ccesarbh em 30/07/2008 - 12:48h:

Se eu fizer esta mudança consigo acessar o Mysql via Windows? Tipo usar o Mysql administrato's no Windows para administrar o mysql rodando no Linux?

Comentário enviado por eduardompozzi em 18/08/2008 - 01:37h:

Aí ccesarbh...
Eu não sei se dá pra fazer isso... Eu nunca precisei usar dessa forma... Valeu

How Do I Enable Remote Access To MySQL Database Server?

How Do I Enable Remote Access To MySQL Database Server?

How Do I Enable Remote Access To MySQL Database Server?

by LinuxTitli [Last updated: September 15, 2008]

By default, MySQL database server remote access disabled for security reasons. However, some time you need to provide the remote access to database server from home or from web server.

MySQL Remote Access

You need type the following commands which will allow remote connections:

Step # 1: Login over ssh if server is outside your IDC

First, login over ssh to remote MySQL database server

Step # 2: Enable networking

Once connected you need edit the mysql configuration file my.cfg using text editor such as vi.

  • If you are using Debian Linux file is located at /etc/mysql/my.cnf location
  • If you are using Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnf location
  • If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf

# vi /etc/my.cnf

Step # 3: Once file opened, locate line that read as follows

[mysqld] 

Make sure line skip-networking is commented (or remove line) and add following line

bind-address=YOUR-SERVER-IP

For example, if your MySQL server IP is 65.55.55.2 then entire block should be look like as follows:
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
bind-address = 65.55.55.2
# skip-networking
....
..
....
Where,

  • bind-address : IP address to bind to.
  • skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should removed from file or put it in comment state.

Step# 4 Save and Close the file

Restart your mysql service to take change in effect:# /etc/init.d/mysql restart

Step # 5 Grant access to remote IP address

# mysql -u root -p mysqlGrant access to new database
If you want to add new database called foo for user bar and remote IP 202.54.10.20 then you need to type following commands at mysql> prompt:mysql> CREATE DATABASE foo;
mysql> GRANT ALL ON foo.* TO bar@'202.54.10.20' IDENTIFIED BY 'PASSWORD';

How Do I Grant access to existing database?

Let us assume that you are always making connection from remote IP called 202.54.10.20 for database called webdb for user webadmin, To grant access to this IP address type the following command At mysql> prompt for existing database:mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';

Step # 5: Logout of MySQL

Type exit command to logout mysql:mysql> exit

Step # 6: Open port 3306

You need to open port 3306 using iptables or BSD pf firewall.

A sample iptables rule to open Linux iptables firewall

/sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your web server located at 10.5.1.3:

/sbin/iptables -A INPUT -i eth0 -s 10.5.1.3 -p tcp --destination-port 3306 -j ACCEPT

OR only allow remote connection from your lan subnet 192.168.1.0/24:

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.0/24 -p tcp --destination-port 3306 -j ACCEPT

A sample FreeBSD / OpenBSD pf rule ( /etc/pf.conf)

pass in on $ext_if proto tcp from any to any port 3306

OR allow only access from your web server located at 10.5.1.3:

pass in on $ext_if proto tcp from 10.5.1.3 to any port 3306  flags S/SA synproxy state

Step # 7: Test it

From remote system or your desktop type the command:
$ mysql -u webadmin –h 65.55.55.2 –p
Where,

  • -u webadmin: webadmin is MySQL username
  • -h IP or hostname: 65.55.55.2 is MySQL server IP address or hostname (FQDN)
  • -p : Prompt for password

You can also use telnet to connect to port 3306 for testing purpose:$ telnet 65.55.55.2 3306

Want to read Linux tips and tricks, but don't have time to check our blog everyday? Subscribe to our daily email newsletter to make sure you don't miss a single tip/tricks. Subscribe to our weekly newsletter here!

{ 1 trackback }

links for 2006-10-28 at Amy Stephen
10.28.06 at 11:25 pm

{ 40 comments… read them below or add one }

1 van 05.04.06 at 4:23 am

Could you tell us how to setup proper MySQL client program on remote machine first?
As far as I now, even if we ONLY install MySQL client program on remote machine, it will generate a my.cnf file. Whenever you issue mysql command on remote machine, this file will be consulted and thus, the client will attempt to connect to a non-exist local MySQL sever rather than your remote server.

2 nixcraft 05.04.06 at 11:29 am

To be frank you don’t need to setup my.cnf for client configuration. All you need to do is specify remote mysql host with –h option. For example to connect remote mysql server called dbserver.nixcraft.in you need to type command as follows:
$ mysql –u vivek –h dbserver.nixcraft.in -p

OR if you with to use MySQL server ip address (192.168.1.101):

$ mysql –u vivek –h 192.168.1.101 -p

3 Abject Eminence 09.23.06 at 8:18 am

nixcraft said…
To be frank you don’t need to setup my.cnf for client configuration. All you need to do is specify remote mysql host with –h option. For example to connect remote mysql server called dbserver.nixcraft.in you need to type command as follows:
$ mysql –u vivek –h dbserver.nixcraft.in -p
OR if you with to use MySQL server ip address (192.168.1.101):
$ mysql –u vivek –h 192.168.1.101 -p
5/04/2006 11:29 AM

+—————————————————-+
This didn’t work at all. BTW, who is “vivek”? Is that your client machine?
Anyhow, the host server keeps telling me that my client computer is not allowed to connect. There must be more to it that I am missing.
+—————————————————–+

4 nixcraft 09.24.06 at 3:15 am

vivek is username and 192.168.1.101 is server IP. You need to setup correct permission using GRANT command (see above for an example).

5 Anonymous 09.25.06 at 3:34 pm

I’m having a problem accessing the file:

~
“/etc/my.cnf” [New File]

[1]+ Stopped vi /etc/my.cnf
[mysql5@serv mysql5]$ vi /etc/my.cnf

please help.

6 Anonymous 09.25.06 at 3:34 pm

I’m having a problem accessing the file:

~
“/etc/my.cnf” [New File]

[1]+ Stopped vi /etc/my.cnf
[mysql5@serv mysql5]$ vi /etc/my.cnf

please help.

7 nixcraft 09.25.06 at 11:42 pm

/etc/my.cnf is standard location. But location is depend upon mysql version and Linux distro. Use find command to find out my.cnf. Type the following command as root user:

find / -iname my.cnf

8 mac 10.21.06 at 1:30 am

Before I have this response from
> mysql -h hostname -u username -p
ERROR 2003 (HY000): Can’t connect to MySQL server on (113)

After following your solution. I got this response

mysql Ver 14.12 Distrib 5.0.22, for redhat-linux-gnu (i686) using readline 5.0
Copyright (C) 2002 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Usage: mysql [OPTIONS] [database]
-?, –help Display this help and exit.
-I, –help Synonym for -?
–auto-rehash Enable automatic rehashing. One doesn’t need to use

I check connectivity using ping host
and got response
icmp_seq=0 ttl=64 time=0.542 ms
meaning i have connectivity

Is there something i missed?

9 mac 10.21.06 at 1:45 am

I tried telnet for connectivity

$telnet ipaddr
Trying ipaddr…
telnet: connect to address ipaddr: No route to host
telnet: Unable to connect to remote host: No route to host

do i have a problem with my ipaddr?

10 matt 01.18.07 at 12:01 am

thanks, this helped me out.

11 Jimaco 03.07.07 at 7:10 am

Do not forget to adjust your iptables file (/etc/sysconfig/iptables usually) to allow connections on that port. Typically you will find that TCP connections are enabled on port 22 (ssh) and port 80 (http). Add an entry for port 3306

12 CptBeluga 04.10.07 at 9:10 pm

Using Fedora 6 as host and added port 3306 to
/etc/sysconfig/ip6tables and /etc/sysconfig/iptables

For the ip6table file the formatted line is;
-A RH-Firewall-1-INPUT -m tcp -p tcp –dport 3306 -j ACCEPT
-A RH-Firewall-1-INPUT -m tcp -p tcp –sport 3306 -j ACCEPT

13 nixcraft 04.11.07 at 2:23 pm

CptBeluga,

Yup, you need to open port 3306 for communication.

Appreciate your post!

14 Frankp 07.24.07 at 12:19 am

I was able to access mysql after changing these tables but when I restarted the computer the files were changed back. There must be another way to do this without manually changing them.

15 amin 08.17.07 at 3:55 am

mysql> update user set Host=’202.54.10.20′ where user=’webadmin’;

I always receiving error when performing above command. Could u advise

16 amin 08.17.07 at 4:01 am

I also cannot find my iptables file on redhat enterprise

17 amin 08.17.07 at 7:35 am

you know what, now am not be able to login as root on localhost. How can I revoke those update above

18 George P. 08.26.07 at 12:07 am

Bless your soul for writing this. I was endlessly modifying permissions in mysql until I read your post and realized I had to change the bind-address.

– George

19 hari vishnunu 09.04.07 at 2:52 am

Hello,

Simply great,expect same again.

20 Everah 09.07.07 at 4:03 pm

This is an awesome article. The only thing I wish it covered is how to name your server (so when you connect you could do it through `myserver.mydomain.com` and whether you can specify a range of allowed remote IPs. But still, this is a very good write up and one that I learned a lot from.

21 pamchi 10.12.07 at 3:49 am

Look, what if a want that all my lan have acces to the db?

22 Seo Freelancer India 01.26.08 at 12:48 am

Hey i am having a problem with Bad Handshake!!
Can any body help me please!!

23 SEM Expert 02.17.08 at 5:11 am

Nice addition. Simply great,expect same again.it will help me a lot.

24 pathan 03.26.08 at 7:36 am

How can i access mysql database running on windows pc from linux server, on both machine i am using mysql 5 and perl 5.8.8 , ip of windows machine ie. 192.168.0.50 and linux server has 192.168.0.10 on single LAN.

25 kokki 04.16.08 at 6:32 am

heloo admin ,
i want to find the ip address of the my sqlserver in fedoro 5 how to find it can u guide me .

26 kenneth 04.30.08 at 2:19 am

I have 2 mysql servers #1 and #2 in a LAN. I want to remotely access either 1 of the 2 servers from box #3. How do I specify that server #2 is to respond and not server #1 to my request. thanks a lot.

27 kenneth 04.30.08 at 1:06 pm

Is there a way to make a mysql server accessible both locally AND remotely?
When I change bind-address to some ip address the ubuntu LAMP server will fail to start mysqld. When I change the bind-address to 127.0.0.1, I can’t access it remotely.

28 MyIkram 05.31.08 at 12:00 pm

Nice tutorial up there !

Keep up the good work

29 Adrian Lozano 06.05.08 at 6:14 pm

Hi there,thankyou for this article..

I made all steps and when I try to login in the WEB application that i’m setting up, there is an error:

MySQL error, Connection Lost during query

Does this mean that conection was done, but something kicked me out ?

I think yes, and I will be very glad to know if you have a clue of what it is causing this.

Thank you

30 Martin 06.23.08 at 1:02 pm

The MySQL manual has some information on this which you might find useful. It goes about it a slightly different way which some people might find easier.

MySQL remote Access Control

31 khawar 07.04.08 at 7:18 pm

Hi,

what if I could not find the file my.cnf on my server?…..I am actually using a web hosting server.

Regards,

KK

32 ali 07.10.08 at 8:21 am

I have problem to connect to remote server thought telnet I made and configure a user in Linux and in mysql also.
I want that the user get in directly to mysql trough Linux shell giving password not to use Linux shell, just telnet the ip address and enter password to go to mysql
For database use and when the user wants to exit the session must close. Would you please help.

33 Veggie 08.11.08 at 11:14 am

Every time I try and restart Mysql I ge this error. Everything to be working write I can get into mysql but can’t log on to it remotely. Please help.
Thanks,
-Veggie

veggie@Server:~$ sudo /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ]
* Starting MySQL database server mysqld [ OK ]
* Checking for corrupt, not cleanly closed and upgrade needing tables.

34 mohammed Falah 09.01.08 at 5:20 pm

Dear All;
to access to remote computer you need to select (Enable root access from remote machines) during the instsllation of Mysql server.
after that you must change the localhost to the IP adress for the remote computer and you will get the conection.
EX: you use the A PC and need to conect to B PC in B PC you have the database and it’s IP 1.1.1.1 so you need to change the do the following in A pc to get the conection to B PC:
“jdbc:mysql://1.1.1.1:3306/yorDBName”
good luck for all

35 n 09.05.08 at 7:46 am

great article. I could setup the remote mysql admin by editing the my.cnf fie.
Thanks.

36 Henry 09.15.08 at 9:22 am

Thanks to commenters’ tips to open port 3306 - fixed my problem.

Alternative to directly editing the iptables file:

system-config-securitylevel
–>advanced
–>add the port at the bottom

Thanks

37 Max 09.24.08 at 7:01 pm

Thank you, just what I was looking for

38 aphplearner 09.25.08 at 9:19 pm

Thanks Google to send me to this page. Thanks for the article. I have two questions. In CPanel I think there is a remote access option. Can u not use to set up remote access? Is there any way I can create desktop application to access database in host

39 Alex1980 10.20.08 at 8:53 pm

Just to say: Great!

Solved my problem at all!
Thanks for writing it!

40 Sivan 12.18.08 at 3:39 pm

Yes this is help me out and thanks a lot