~/.mysql_history size limit

Question

My .mysql_history file size is limited to 300 lines.

I would like to keep the mysql history unlimited. However, I couldn't find any way to do it (in man page, and in google)

Can somebody please advise how to increase the size limitof .mysql_history from the default 300 to more (or unlimited)

I am using Debian Wheezy

Answer 1

mysql uses readline for the command line interface and for the history handling.

readline is configured in the ~/.inputrc file and in /etc/inputrc system-wide.

You probably have a

set history-size 300

somewhere in there. Just remove that setting, or add a:

$if Mysql
set history-size -1
$endif

to your ~/.inputrc.

Answer 2

You can start a tail -F on the history file and pipe the output into a new file:

tail -F ~/.mysql_history >> ~/.mysql_complete_history

you will get all the commands, (although the file will grow quite a lot each time: the whole history file is added to the ~/.mysql_complete_history file time you quit the mysql console)

---

EDIT:

To correctly fill ~/.mysql_complete_history with the exact output of ~/.mysql_history use this script by Stephane Chazelas

Answer 3

Not sure whether it would work for you, but man chattr reveals there's a flag a which stands for "append only", so I'd give it a try and see. ;)

Answer 4

If you want to see all queries that you used in all time you can enable the general-log variable (see also How to enable MySQL Query Log?).

Be aware that this will cause a lot of cpu and slow down mysql)

You could then create a new history file from that log each time before you enter the console by

cat /var/lib/mysql/yourhost.log | cut -f4-99|while read i; do echo $i\;; done | > ~/.mysql_history

this will put all commands that are executed on your server in your history file.

It is not perfect, cause commands that were over several lines are in a single history row now (but maybe you can fix that too, if you need it)