List or See MySQL command or query history: MySql Command Or Query History : Command Line

MySql store the history of command or queries in .mysql_history file in the user directory, suppose you want to see the history of commands or SQL queries which <username> has executed we can either login to that user or from another user we can type:

cat ~/.mysql_history

Suppose current user is logged in as root or any user if it has Sudo permission then we can give command

cat /home/username/.mysql_history

MySQL: SUBSTRING_INDEX - Select Patterns

Consider, a MySQL table having values in a column like below:

SELECT location  FROM geo LIMIT 3;

"location"
"India.Karnataka.Shimoga.Gopala"
"India.Karnataka.Bengaluru.BTM"
"India.Karnataka.Chikmaglore.Koppa"

My requirement is to take only 4th value from each of the rows(such as, Gopala,BTM,Koppa). 

I don't want to display remaining values. 

Its same as what 'cut' command will do in Linux.

For this, we can use SUBSTRING_INDEX function.

SELECT SUBSTRING_INDEX(location,'.',-1)  from geo LIMIT 3;

"location"

"Gopala"

"BTM"

"Koppa"

Syntax: SUBSTRING_INDEX(string,delimiter,count)

Here count means column number based on delimiter. 

Negative value indicates that the column numbers calculated from right side.

So, if I give '-2' instead of  '-1':

SELECT SUBSTRING_INDEX(location,'.',-2)  from geo LIMIT 3;

"location"

"Shimoga.Gopala"

"Bengaluru.BTM"

"Chikmaglore.Koppa"

MySQL Replication: Exclude an SQL from replicating

If you're using MySQL Replication, in rare cases you may require to skip a particular SQL statement from replicating - which is to be executed on Master and should not be on the Slave. Though, its not a good practice.

However, here's how to do it. 

MySQL has a variable sql_log_bin, by default its value will be 'ON' or '1', when we enable replication. That means, it replicates all the SQL statements from Master to Slave.
So, if we want to disable this for a particular SQL, do as below:

SET SESSION sql_log_bin=0; sql statement;SET SESSION sql_log_bin=1;


Example:

 
SET SESSION sql_log_bin=0; DELETE FROM tab1 WHERE col1='NULL'; 
SET SESSION sql_log_bin=1;

 However, after MySQL 5.5.5 version, no need to use SESSION. By default its a SESSION variable and it won't affect other user's transactions. 

Note: Executing this on Master would cause data inconsistency. Don't try this unless you are sure about what you are doing!

MySQL Error: Error_code: 1032

Here's something about MySQL replication error. The exact error could be as below:

Could not execute Update_rows event on table DBName.tableName; Can't find record in 'tableName', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.01, end_log_pos 218

If SQL Thread on one of your slave stops with this error code, that just mean Master and this Slave are not in sync!! There must be an inconsistency. 

In our case, slave got an Update event form master, but that particular record was not available in the slave. (Of course, that was deleted on Master n Slave separately)
You would get this error only if the binlog_format is set to ROW_BASED or MIXED mode.

So, now check that particular binlog at that position where replication stopped with this error. 
When you convert the binlog with mysqlbinlog command, you may see some junk characters  where you were expecting some DMLs which caused the error(In our case its an Update statement).

So you can use below command to translate binlog completely:


mysqlbinlog --base64-output=DECODE-ROWS --verbose  mysql-bin.01 >mysql-bin.01.txt