5

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

4
  • 1
    Let us know if this works for you: unix.stackexchange.com/a/99099/6622
    – poige
    Nov 7, 2013 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, 2013 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 Nov 29, 2013 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, 2013 at 23:41

4 Answers 4

6
+50

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.

5
  • unfortunately, this does not seem to have any effect. My .mysql_history has still precisely 300 lines Dec 3, 2013 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? Dec 3, 2013 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) Dec 4, 2013 at 8:09
  • 2
    @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. Dec 4, 2013 at 8:43
  • @StéphaneChazelas : Please add this information in your answer. Sep 22, 2015 at 11:18
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

1
  • That is very clever. Thanks. But unfortunately, this is not very practical. Nov 29, 2013 at 20:03
1

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

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

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)

2
  • 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. Nov 29, 2013 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, 2013 at 23:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .