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

  • 1
    Let us know if this works for you: unix.stackexchange.com/a/99099/6622 – poige Nov 7 '13 at 11:04
  • Can you explain your set-up? which server is connecting where and which history do you need when and why? – rubo77 Nov 29 '13 at 23:22
  • My setup is pretty standard: I am working on my local machine. From this local machine, I connect with mysql-client to a remote database. The history file is on my local machine in $HOME/.mysql_history – Martin Vegter Nov 29 '13 at 23:28
  • and both machines are wheezy? and can you connect via ssh to the other machine also? And why do you need so much history? – rubo77 Nov 29 '13 at 23:41

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

to your ~/.inputrc.

  • unfortunately, this does not seem to have any effect. My .mysql_history has still precisely 300 lines – Martin Vegter Dec 3 '13 at 15:17
  • @MartinVegter, does mysql read your ~/.inputrc? Confirm with strace -fe open mysql .... What's in your ~/.inputrc now (and before)? What does env | grep MYSQL return? – Stéphane Chazelas Dec 3 '13 at 15:38
  • you are right. It works now. It did not work before, because mysql was an alias to alias mysql='/usr/bin/rlwrap -a -pRED /usr/bin/mysql'. When I disable the alias, it works. Any chance to use your trick with my alias active? (I tried using $if rlwrap in my .inputrc, but that does not seem to work) – Martin Vegter Dec 4 '13 at 8:09
  • 1
    @MartinVegter, use rlwrap -s 99999999, see the rlwrap man page. It's rlwrap that imposes that limit on 300, you can forget about the changes in ~/.inputrc. – Stéphane Chazelas Dec 4 '13 at 8:43
  • @StéphaneChazelas : Please add this information in your answer. – Skippy le Grand Gourou Sep 22 '15 at 11:18

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)


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

  • That is very clever. Thanks. But unfortunately, this is not very practical. – Martin Vegter Nov 29 '13 at 20:03

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. ;)

  • Thanks for the clever suggestion. However this will not work for me, as I don't have EXT4_FS_XATTR support in my kernel. – Martin Vegter Nov 7 '13 at 11:08
  • @MartinVegter, do you have any other FS_XATTR? – poige Nov 7 '13 at 13:28
  • no, I don't have extended attributes support at all – Martin Vegter Nov 7 '13 at 13:36
  • @MartinVegter, is that some custom built kernel? Usual config includes xattr support even for tmpfs. – poige Nov 7 '13 at 13:38
  • yes, this is a custom build kernel – Martin Vegter Nov 7 '13 at 14:07

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)

  • This would not work, unfortunately. I don't connect to the database from the mysql server, rather I connect from another machine. Therefore I don't have access to the log on the server. – Martin Vegter Nov 29 '13 at 21:36
  • If you have ssh access, you could cat the logfile on the other mashine through ssh into your local history file – rubo77 Nov 29 '13 at 23:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.