How to log input/output from the MySQL command-line tool

In some cases, it could be nice to look back for previous executed MySQL commands from the CLI. I recently had a customer who needed this, and first i thought this were not possible, but i came up with a simple solution, when i figured out that the tee-flag existed:

o   --tee=file_name

Append a copy of output to the given file. This option works only in interactive mode. the section called "MYSQL COMMANDS", discusses tee files further.

The best part is, that it automatically appends the output to the desired file, so that makes it even more simple. The best way i could come up with was to just create a bash-alias.

Open up your ~/.bashrc file with your desired editor, in my case vim:

$ vim ~/.bashrc

Then i write the following alias, under the other defined aliases:

alias mysql='mysql --tee="/var/log/mysql-cli.log"'

This will log your output to file mysql-cli.log under the /var/log/ directory, everytime you enter (Logging to file '/var/log/mysql-cli.log'):

$ mysql -uroot -p
Logging to file '/var/log/mysql-cli.log'
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 136
Server version: 5.1.73-1 (Debian)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show full processlist;
+-----+------+-----------+------+---------+------+-------+-----------------------+
| Id  | User | Host      | db   | Command | Time | State | Info                  |
+-----+------+-----------+------+---------+------+-------+-----------------------+
| 136 | root | localhost | NULL | Query   |    0 | NULL  | show full processlist |
+-----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

mysql>

Now if i cat the file:

$ cat /var/log/mysql-cli.log
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 137
Server version: 5.1.73-1 (Debian)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show full processlist;
+-----+------+-----------+------+---------+------+-------+-----------------------+
| Id  | User | Host      | db   | Command | Time | State | Info                  |
+-----+------+-----------+------+---------+------+-------+-----------------------+
| 137 | root | localhost | NULL | Query   |    0 | NULL  | show full processlist |
+-----+------+-----------+------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)

mysql> Ctrl-C -- exit!
Aborted

Logrotation

Eventually, do some logrotation, to make the file use as small diskspace as possible. Create a mysql-cli file under the /etc/logrotate.d/ directory:

$ vi /etc/logrotate.d/mysql-cli

Use the following content, to make a simple rotation of the file:

"/var/log/mysql-cli.log" {
  missingok
  copytruncate
  rotate 7
  compress
  notifempty
}

Menu

Logrotation