MySQL 5.6 InnoDB Full Text Search Index

What is full text search Index in MySQL???

It is one of the index type FULL TEXT in MySQL.

If we want to search for a word we use LIKE ‘%word%’, if more than one word we use like ‘%word1%word2%’, what many people don’t know is for this kind of search is expensive and not optimized to our MySQL, in this cases we solve our problems with Full Text Index.

In MySQL 5.6 the InnoDB storage engines have the features of Full text search index option.
 

Full-text indexes can be used only with InnoDB or MyISAM tables, and can be created only for CHAR, VARCHAR, or TEXT columns. 

Full-text searching is performed using MATCH()...AGAINST() syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST() takes a string to search for, and an optional modifier that indicates what type of search to perform.

There are three ways of search option in full text index,

                                              I) Natural language
                                             II) Boolean mode
                                            III) Query expansion

I)Natural Language Full text searches:
           

      It search the given word alone in the table.If there is no the given word means it will show as empty sets.The search string is given as the argument to AGAINST(). For each row in the table, MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

 

Eg:

Create a database and table in it.

Here the engine is InnoDB and next insert the values.

See all the datas in the table.

Now we check the natural language full text search.Here,we need to search the data containing the word 'India' so the natural language mode shows the resemblance world of India in the table.It scan the full rows and shows it below.

In the above,three rows are affected among five rows because the word India contains only in those rows alone.If we need to search more than two word means see below,

 

The default minimum length is 4 characters. You can change the min and max word length with the variables ft_min_word_len and ft_max_word_len.

II)Boolean Mode full text searches:

Sometimes we need to see particular word containing rows but it should not have some other words too during that time we can use this type of searching option.The + and - operators indicate that a word is required to be present or absent, respectively, for a match to occur. 

Eg:

Here,I shown the table containing the word as India but it should not have the word South. 

Here,I shown the table which contain the word city and it should not have the word India.

III) Query expansion full text searches




In this search it will show the resemblance word from the given word.In this it first scan and show the given word and again scan to see any resemblance word like this to show in the table.It will show the words that are commonly found with the words in the query.




Eg:

Here its shows the resemblance word and the given word from the table.



These are the types of Full text search Engines in MySQL and its functions.


Thank you...

DIAGRAMMATIC REPRESENTATION OF MySQL REPLICATION PROCESS

All of them know how does the MySQL replication works.But here I gave a diagrammatic representation of MySQL Replication It looks something different to you at first sight.

Replication is a process that allows you to easily maintain multiple copies of a MySQL data by having them copied automatically from a master to a slave database.In replication two logs are used to read the events in master server and in slave server.

Binlog :

 

In master server the changes happen in every database will be recorded in binlog file if it is enabled and by default it is disable.The binary log file stores the data that replication slave will read when its get connected to master server.

Relay log :
 

The relay log is like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files.It records the events from master using a slave thread called IO thread.

Threads :

Threads are use to connect master to slave and do changes in slave databases.There are two types of slave threads at here,

                                                   I)  IO Thread
                                                  II)  SQL Thread

IO Thread :

It use to read the binlog events from master to slave relay log when it gets connected to master server.We can see that which file is reading from master to slave by seeing variables like Master_Log_File and Read_Master_Log_Pos.

SQL Thread :

It reads the events from relay log to do the changes in slave databases and keep it up to date the databases like master databases.Once the event processed by the SQL thread the relay log will delete the processed event.We can read the currents process in sql thread by seeing Relay_Master_Log_File and Exec_Master_Log_Pos.

Master_Slave :

Here In the above figure the arrows indicates the process step by step in replication process.

Thanks...

GTID(GLOBAL TRANSACTION ID) FOR MYSQL 5.6 REPLICATION

Replication: Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous - slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database. 

In MySQL version 5.6 Oracle released one of the enhancement is GTID for Replication.

Early we have to set master_log_position and master_log_file in slave instead of these both,we can use this GTID for master slave replication in MySQL 5.6.

The Global Transaction IDentifier (GTID) is a unique identifier created and associated with each transaction when it is committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup.

We need to add and enable some variables in cnf file. The gtid_mode and enforce-gtid-consistency.Starting the server with gtid_mode=ON requires that the server also be started with the log_bin and log_slave_updates options as well.

gtid_mode  : It is the main option that needs to be enabled for global transaction IDs.

enforce-gtid-consistency : It allows execution of only those statements that can be logged in a transactionally safe manner.

For master slave replication we need to enable gtid_mode basically.

Master cnf:

Slave cnf:

Now create the replication user in Master:

 

When we see the master status we see the Executed_Gtid_Set (set of all transaction logged in the binary log).

 Now do the changes in slave server but no need to give master_log_position and master_log_file anymore. Start the slave nowCheck the replication is running or not  by using slave status.

 

MASTER_AUTO_POSITION = 1 is used when the slave attempts to connect to the master using the GTID-based replication protocol. In this case, the coordinates represented by MASTER_LOG_FILE and MASTER_LOG_POS are not used, and global transaction identifiers are used instead. Thus the use of either or both of these options together with MASTER_AUTO_POSITION causes an error.

gtid_mode must also be enabled before issuing CHANGE MASTER TO ... MASTER_AUTO_POSITION = 1. Otherwise, the statement fails with an error.

Retrieved_Gtid_set : It displays the transaction that we read from master.
Executed_Gtid_set : It displays the transaction that we already executed.

Now we check the replication is works fine if the command executed in master that is replicate on slave or not.
In Master: Check it in Slave: Got successfully replicated by GTID.

So here after no need to worry about log file and position in slave while doing change master to statement.