MySQL search database | MySQL search all tables

“What will you do if one day some one ask you to find single string in all databases, all tables and in all columns?”

– This was asked to me once. There is some string in the database but I do not know where. There was a task to find an user’s details which is in the database which consists of many tables. And all I know is only the user name and do not know any other details. How am I supposed to find that string? I do not want to write some complex SQL for this. Digging more into this, I came across few GUI tools for MySQL which have good search feature.

So I am writing this blog about search feature in those tools. I shortlisted three client application for MySQL:

1) SQLyog

2) phpMyAdmin

3) HeidiSQL

SQLyog

There is a feature called ‘Data Search‘ in SQLyog. To open this right-click on a database or connection root level. There are few filter options in this tool. You can select multiple databases and search based on a datatype.

It works like this-

  • Type a text(Do filter operations if needed).
  • Hit Search or press Enter.

That’s it! It populates 20 results in the search result and there are ‘First’, ‘Previous’ and ‘Next’ links which works like a Search Engine. The result can be viewed/edited in the same tab when clicking a search result.

phpMyAdmin

If you have phpMyAdmin installed then use the Search feature.

  • Select a database and click Search tab.
  • Enter the text to search(You may use Regular expression too).
  • Choose tables/columns to search.

Doing these will get you  to the search results where you can ‘Browse’ and view the data. The down side in this search is that it cannot search the whole server. It does a pretty neat job under a single database, but what about searching in multiple databases?

HeidiSQL

There is an option in HeidiSQL- ‘Find text on server‘. You can select multiple databases on the left hand tree and press ‘Find’. After searching it populates the results where you can press ‘See Results’ to view the text. To view the data it opens a query tab with results in the Result view.

                    It doesn’t search under particular columns. Also, if you want to see a particular table search result, clicking on ‘See results’ opens up all the search results. But this search is very good for a free software.

So the conclusion is, Sometime we need to find out a small piece of string in big Database. Like where is the configuration is saved, or where is Mark’s Date of birth is saved etc. These above mentioned amazing tools help us achieve it!

5 Comments

Filed under MySQL

MySQL Federated table | MySQL join across servers

This blog will explain you how to use Federated tables in MySQL. Just follow the steps mentioned below to use Federated table in your MySQL server:

What is Federated Engine?

About Federated Engine in MySQL:

The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.

Why to use Federated Engine?

Allows a user to create a table that is a local representation of a foreign (remote) table.
Real world example:

                  There are two servers-A and B. Both servers {AB} are on different machines and have different connection parameters (different username, different password etc). In such a case, it is possible to perform a join between a table that is in database A, to a table that is in database using Federated table.

How to use Federated Engine?

A Federated table consists of two elements:

1) Remote server-

First, you must have a table on the remote server that you want to access by using a FEDERATED table. Suppose that the remote table is in the sakila database and is defined like this:

CREATE TABLE test_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL
    PRIMARY KEY  (id)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1;

2) Local server-

Next, create a FEDERATED table on the local server for accessing the remote table:

CREATE TABLE federated_table (
    id     INT(20) NOT NULL AUTO_INCREMENT,
    name   VARCHAR(32) NOT NULL
    PRIMARY KEY  (id)
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://fed_user:fed_user@197.186.1.199:3306/sakila/test_table';

Sample connection strings:

CONNECTION='mysql://username:password@hostname:port/database/tablename'
CONNECTION='mysql://username@hostname/database/tablename'
CONNECTION='mysql://username:password@hostname/database/tablename'

The basic structure of this table should match that of the remote table, except that the ENGINE table option should be FEDERATED.

Execute:

show variables like '%federated%'; 

to check if FEDERATED storage engine is available on your local server.

The table- federated_table in localhost becomes virtual table of test_table in remote server.

Now you can use the JOIN between the tables in a DB in the localhost server. Suppose if there is a table in sakila- test and you want to JOIN with test_table which is in remote server, write a query like the one shown below:

SELECT * FROM `federated_table` JOIN `test`;

The federated_table in the query will actually refer to test_table in remote server.

Limitations of Federated tableshttp://dev.mysql.com/doc/refman/5.0/en/federated-limitations.html

Leave a comment

Filed under MySQL