Is it possible to remove the prompt history on MariaDB?

Question

The main problem is that I wrote a security sensitive statement on MariaDB (among many other statements), so I would like to remove the prompt history (like the command history -c on Linux):

jordiba90@lts:~$ sudo mariadb -u root -p
[sudo] contraseña para jordiba90: 
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 36
Server version: 10.3.25-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
    
MariaDB [mysql]> UPDATE user SET password=PASSWORD("PASSWORD") WHERE user="root";
Query OK, 0 rows affected (0.001 sec)
Rows matched: 1 Changed: 0 Warnings: 0

MariaDB [mysql]> FLUSH privileges;
Query OK, 0 rows affected (0.001 sec)

So, when I click the up&down buttons on my keyboard I see all the prompt history.

When I try to remove the prompt history on MariaDB, it does not work:

MariaDB [(none)]> clear

MariaDB [(none)]> \c

MariaDB [(none)]> quit
Bye

So, I have checked on the internet this script (that does not work either):

jordiba90@lts:~$ rm ~/.mysql_history

jordiba90@lts:~$ export MYSQL_HISTFILE=/dev/null

jordiba90@lts:~$ set | grep MYSQ
MYSQL_HISTFILE=/dev/null
_=MYSQL_HISTFILE=/dev/null

REFERENCE > https://www.thegeekstuff.com/2010/01/disable-mysql-history-clear-mysql_history-and-mysql_histfile/

After that, I have also checked on the internet another script (that does not work either, again):

jordiba90@lts:~$ ~/.mysql_history
bash: /home/jordiba90/.mysql_history: No existe el archivo o el directorio

jordiba90@lts:~$ rm $HOME/.mysql_history
rm: no se puede borrar '/home/jordiba90/.mysql_history': No existe el archivo o el directorio

jordiba90@lts:~$ ln -s /dev/null $HOME/.mysql_history

REFERENCE > https://www.cyberciti.biz/faq/howto-clear-mysql-command-history/

One the one hand, there are too many files on my OS related to "mysql" but none related to mysql history, so I have checked one by one all the files related to mysql configuration and I did not find the one I should remove:

jordiba90@lts:~$ locate mysql | wc
    421     421   25918

jordiba90@lts:~$ locate mysql | grep history

jordiba90@lts:~$ locate mysql | grep conf
/etc/mysql/conf.d
/etc/mysql/conf.d/mysql.cnf
/etc/mysql/conf.d/mysqldump.cnf
/etc/mysql/mariadb.conf.d
/etc/mysql/mariadb.conf.d/50-client.cnf
/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf
/etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf
/etc/mysql/mariadb.conf.d/50-server.cnf
/usr/share/mysql-common/configure-symlinks
/usr/share/mysql/systemd/use_galera_new_cluster.conf
/var/lib/dpkg/info/mysql-common.conffiles

One the other hand, the same happens with the mariadb files:

jordiba90@lts:~$ locate mariadb | wc
    101     101    5042

jordiba90@lts:~$ locate mariadb | grep history

jordiba90@lts:~$ locate mariadb | grep conf
/etc/insserv.conf.d/mariadb
/etc/mysql/mariadb.conf.d
/etc/mysql/mariadb.conf.d/50-client.cnf
/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf
/etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf
/etc/mysql/mariadb.conf.d/50-server.cnf
/usr/lib/systemd/system/mariadb@bootstrap.service.d/use_galera_new_cluster.conf
/var/lib/dpkg/info/mariadb-client-10.3.conffiles
/var/lib/dpkg/info/mariadb-common.conffiles
/var/lib/dpkg/info/mariadb-server-10.3.conffiles
/var/lib/dpkg/info/mariadb-server-10.3.config

Could you please give me a hint? You can check that I have tried it. Thanks in advance.

PS_1: if there is any other entry about this topic on Stack* (I have checked it and I did not see one) or if my entry breaks any rule on Stack* (I have checked the rules and I think I do not break any), please send me a PM and I will delete this entry. I am trying to learn without losing reputation points for asking a question.

PS_2: I have restarted mysql and right now the status is active (running):

jordiba90@lts:~$ sudo /etc/init.d/mysql restart
Restarting mysql (via systemctl): mysql.service.

jordiba90@lts:~$ sudo /etc/init.d/mysql status
● mariadb.service - MariaDB 10.3.25 database server
     Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
     Active: active (running) since Sat 2020-11-14 13:24:47 CET; 8h ago
       Docs: man:mysqld(8)
             https://mariadb.com/kb/en/library/systemd/
    Process: 826 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
    Process: 861 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 876 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
    Process: 1026 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
    Process: 1028 ExecStartPost=/etc/mysql/debian-start (code=exited, status=0/SUCCESS)
   Main PID: 957 (mysqld)
     Status: "Taking your SQL requests now..."
      Tasks: 31 (limit: 9423)
     Memory: 93.1M
     CGroup: /system.slice/mariadb.service
             └─957 /usr/sbin/mysqld

nov 14 13:24:46 lts systemd[1]: Starting MariaDB 10.3.25 database server...
nov 14 13:24:47 lts mysqld[957]: 2020-11-14 13:24:47 0 [Note] /usr/sbin/mysqld (mysqld 10.3.25-MariaDB-0ubuntu0.20.04.1) starting as…ess 957 ...
nov 14 13:24:47 lts mysqld[957]: 2020-11-14 13:24:47 0 [Warning] Could not increase number of max_open_files to more than 16384 (request: 32186)
nov 14 13:24:47 lts systemd[1]: Started MariaDB 10.3.25 database server.
nov 14 13:24:47 lts /etc/mysql/debian-start[1030]: Upgrading MySQL tables if necessary.
nov 14 13:24:47 lts /etc/mysql/debian-start[1033]: Looking for 'mysql' as: /usr/bin/mysql
nov 14 13:24:47 lts /etc/mysql/debian-start[1033]: Looking for 'mysqlcheck' as: /usr/bin/mysqlcheck
nov 14 13:24:47 lts /etc/mysql/debian-start[1033]: This installation of MySQL is already upgraded to 10.3.25-MariaDB, use --force if y…l_upgrade
Hint: Some lines were ellipsized, use -l to show in full.

REFERENCE > https://superuser.com/questions/282115/how-to-restart-mysql

PS_3: I have tried the command stat ~/.mysql_history; rm ~/.mysql_history; stat ~/.mysql_history and the output that I have is that the file or the directory /home/jordiba90/.mysql_history does not exist

PS_4: I have tried the command strace mysql and I can not share here the output because there is a message body limit to 30.000 characters (and if I do that, I should have entered 44.321, aprox)

PS_5: Having checked the specific output openat(AT_FDCWD..., there is none related to mysql_history. It does not appear that specific sentence

PS_6: I have used 'sudo' to try the command 'strace mysql'and I have got the following output related to mysql history:

openat(AT_FDCWD, "/root/.mysql_history", O_RDONLY) = 4
fstat(4, {st_mode=S_IFREG|0600, st_size=4585, ...}) = 0
read(4, "misql -u root -p\nDatascience2005"..., 4585) = 4585
close(4)                                = 0
write(1, "\33(B\33[m\33(B\33[0;1mType 'help;' or '"..., 94Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
) = 94
write(1, "\n", 1
)

PS_7: I have removed the last directory related to mysql history named /root/.mysql_history but I still see the history on the mariadb prompt

Keep in mind that you need to restart the "mysql" client. Deleting the `.mysql_history` file is correct. Please show how you know that clearing the mysql command history doesn't work (after you have started a new "mysql" client) with the steps you have posted. — Progman, Nov 14 '20 at 20:59
Aside, don't `UPDATE` tables directly, use [SET PASSWORD](https://mariadb.com/kb/en/set-password/) or [ALTER USER](https://mariadb.com/kb/en/alter-user/). It eventually (10.4+) won't work. — danblack, Nov 14 '20 at 20:59
Thanks for your reply, @Progman I restarted the "mysql" and it does not work either. Additionally, I do not see any file named *.mysql_history*, just the bash history. I think that I posted all the steps and the results that I get trying to clear the mysql command history. Could you please be more specific? I am just learning about it. — jordiba90, Nov 14 '20 at 21:16
@jordiba90 Please [edit] your question to include the console output of `stat ~/.mysql_history; rm ~/.mysql_history; stat ~/.mysql_history`. Also, this might be a little bit more tricky, run the command `strace mysql` (add the login data as required), then kill that program again. Add the output to your question, but check if you see how it tries to read the `./mysql_history` file and remove any sensitive data you have before adding it here. There might be lines like `openat(AT_FDCWD, "/home/{youruser}/.mysql_history", O_RDONLY) = 4` where you see if and how it access your file. — Progman, Nov 14 '20 at 21:23
@Progman Just for being sure, about the command that you say, should I write it on the command line on Linux or on the prompt command line on mariadb? It is quite confusing for me because of the ";" that I am not sure if there are pipelines or just mysql statements. It does not work having tried on both. Thanks — jordiba90, Nov 14 '20 at 21:33
@jordiba90 These are unix/linux commands. The `;` are just separating the commands, they are not pipelines. — Progman, Nov 14 '20 at 21:36
@jordiba90 Can you add parts of the `strace` output related to the `.mysql_history` file, including lines before and after found lines? You can run something like `strace mysql 2>errorstream; grep -C 5 -i history errorstream` and add the result to your question. — Progman, Nov 14 '20 at 21:55
There is no output when I try this last command: `strace mysql 2>errorstream; grep -C 5 -i history errorstream` — jordiba90, Nov 14 '20 at 21:57
@jordiba90 Check the content of the generated `errorstream` command if the `strace` call itself was successful and does not have other unrelated errors. It is odd that there are no lines related to `.mysql_history`. There should be at least some lines, even when it failed to read the file with the `= -1 ENOENT` status code. — Progman, Nov 14 '20 at 22:03
There are no content on `errorstream` command. By the way, when I did it using sudo I have got an output that I update on my question as PS_6 — jordiba90, Nov 14 '20 at 22:09
@jordiba90 Be careful when using "sudo". As you see it reads the `/root/.mysql_history` file, since `root` would be the current user at that time. This might not be what you want and what you want to check to debug the issue. Hopefully you don't use the "mysql" command in general as `root` via "sudo". — Progman, Nov 14 '20 at 22:16
@jordiba90 Why do you use "sudo"? The "mysql" command should be used from the normal user you are using, not from "root". — Progman, Nov 15 '20 at 10:02
@Progman Indeed, you are right. It is just that I am used to use `sudo` to manage all the package installations from the command line. Many thanks for your support but I am still have the initial main question. Maybe it is a bug on my configuration or maybe not. Idk. — jordiba90, Nov 15 '20 at 17:33

score 1 · Answer 1 · answered Mar 18 '22 at 12:18

1

Editing the history file (for example with vim, and deleting the problematic lines) worked in my case:

vim ~/.mysql_history

(mysql Ver 15.1 Distrib 10.4.14-MariaDB)

answered Mar 18 '22 at 12:18

Zael

330
3
7

ChumKui · Answer 2 · 2023-02-07T19:03:04.893

Depending on how you are set up, you'll probably find it is located at root. I had the same on a new secured install of MariaDB on Ubuntu 22.04. Mine was located at /root/.mysql_history

I found it the usual way by using the excellent locate command (remember to use the updatedb command to update the database of any new files that have been added) and searching for mysql_history:

locate -i mysql_history

Do this search at the bash (or whatever you are using) command prompt as you are searching for a file in the file system, not in the MariaDB or MySQL shell itself.

On this search, there is no actual need for the -i option as mysql_history is lowercase, but you never know!

Searching for history will also work but you'll get a few more hits.

Once you have found the file, you can use the editor of your choice to remove any sensitive command lines that may have found their way in there.

Is it possible to remove the prompt history on MariaDB?

2 Answers2