MySQL .mysql_history File

If you create a MySQL user something like this:

 

mysql> create user 'andrew'@'localhost'

    -> identified by 'reid';

Query OK, 0 rows affected (0.00 sec)

 

mysql>

 

A record of the SQL is stored in the .mysql_history file:

 

create\040user\040'andrew'@'localhost'\040identified\040by\040'reid';

 

This is a hidden file in the mysql user’s home directory:

 

$ pwd

/usr/local/mysql

$ ls -l .mysql_history

-rw-------   1 mysql    mysql      31375 Jan 11 16:46 .mysql_history

$

 

It has permissions of 600 but, even so, you may regard it as a security risk. You can remove its contents, if you wish:

 

$ > .mysql_history

$ ls -l .mysql_history

-rw-------   1 mysql    mysql          0 Jan 11 17:16 .mysql_history

$

 

... and MySQL will carry on as before:

 

$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 53597

Server version: 5.0.67 Source distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> select count(*) from mysql.user;

+----------+

| count(*) |

+----------+

|       18 |

+----------+

1 row in set (0.00 sec)

 

mysql> exit

Bye

$ ls -l .mysql_history

-rw-------   1 mysql    mysql         55 Jan 11 17:17 .mysql_history

$

 

If that is not sufficient, you can direct your MySQL history to /dev/null by setting the MYSQL_HISTFILE environment variable as follows:

 

$ > .mysql_history

$ ls -l .mysql_history

-rw-------   1 mysql    mysql          0 Jan 11 17:21 .mysql_history

$ export MYSQL_HISTFILE=/dev/null

$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 53608

Server version: 5.0.67 Source distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> select count(*) from mysql.user;

+----------+

| count(*) |

+----------+

|       18 |

+----------+

1 row in set (0.00 sec)

 

mysql> exit

Bye

$ ls -l .mysql_history

-rw-------   1 mysql    mysql          0 Jan 11 17:21 .mysql_history

$

 

... and if you decide this is a good idea, you can make the change permanent by including it in your login script.

 

Another option is to link the MySQL history file to /dev/null. You only need to do this once:

 

$ rm $HOME/.mysql_history

$ ln -s /dev/null $HOME/.mysql_history

$ mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 53628

Server version: 5.0.67 Source distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> select count(*) from mysql.user;

+----------+

| count(*) |

+----------+

|       18 |

+----------+

1 row in set (0.00 sec)

 

mysql> exit

Bye

$ ls -l $HOME/.mysql_history

lrwxrwxrwx   1 mysql    mysql          9 Jan 11 17:42 /usr/local/mysql/.mysql_history -> /dev/null

$