how to disable mysql_history in MariaDB/MySQL
MariaDB/MySQL stores the commands typed in the mysql> prompt in the ~/.mysql_history file.
[ root @ dbversity : ~ ] ll -lhtr .mysql_history
-rw——- 1 root root 19K Jun 15 06:12 .mysql_history
[ root @ dbversity : ~ ]
Connect to mysql from the llinux command line and execute few sql commands as shown below.
[ root @ dbversity : /etc/opt/rh/rh-mariadb101 ] mysql -u root -p’password’ -h localhost –ssl
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]> show global variables like “%local%”;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| local_infile | OFF |
+—————+——-+
1 row in set (0.00 sec)
Now, if you press up arrow, you can see all the previous commands you’ve typed from the mysql prompt.
Exit from the mysql command prompt and view the ~/.mysql_history file that will contain all the sql commands you executed from the mysql command prompt.
[ root @ dbversity : ~ ] head .mysql_history
_HiStOrY_V2_
show\040databases;
show\040dbs
;
show\040databases;
use\040mysql
show\040tables;
select\040*\040from\040user;
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ]
To do it permanently
- Remove .mysql_history files in all other users.
- Login to all the OS users and add below highlighted lines in thier .bashrc/startup script -that’s it.
- Execute source ~/.bashrc
- Now check it
[ root @ dbversity : ~ ] cat .bashrc
# .bashrc
# User specific aliases and functions
export MYSQL_HISTFILE=$MYSQL_HISTFILE:/dev/null
ln -s /dev/null $MYSQL_HISTFILE
MARIADB_ROOT=/opt/rh/rh-mariadb101
. $MARIADB_ROOT/enable
# Source global definitions
if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi
[ root @ dbversity : ~ ]
Disable mysql history by pointing .mysql_history to /dev/null
=====================================================================
First, remove the ~/.mysql_history file
[ root @ dbversity : ~ ] rm -rf .mysql_history
[ root @ dbversity : ~ ]
Next, create a symbolic link of ~/.mysql_history pointing to /dev/null as shown below.
[ root @ dbversity : ~ ] ln -s /dev/null ~/.mysql_history
[ root @ dbversity : ~ ] ls -l .mysql_history
lrwxrwxrwx 1 root root 9 Jun 15 07:26 .mysql_history -> /dev/null
[ root @ dbversity : ~ ]
Now, login to the mysql and execute few sql commands. You’ll notice that ~/.mysql_history file is empty and does not store any previously typed commands.
[ root @ dbversity : ~ ] mysql -u root -p -h localhost –ssl
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+——————–+
5 rows in set (0.00 sec)
MariaDB [(none)]> use test
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 [test]>
MariaDB [test]>
MariaDB [test]> create table tbl (id int, name varchar(10));
Query OK, 0 rows affected (0.09 sec)
MariaDB [test]>
MariaDB [test]> ^DBye
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ] cat .mysql_history
[ root @ dbversity : ~ ]
Disable mysql history using MYSQL_HISTFILE environment variable
===============================================================
First, remove the ~/.mysql_history file
[ root @ dbversity : ~ ] rm ~/.mysql_history
rm: remove regular file `/root/.mysql_history’? y
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ]
Next, set the MYSQL_HISTFILE env variable to /dev/null
[ root @ dbversity : ~ ] export MYSQL_HISTFILE=/dev/null
[ root @ dbversity : ~ ] set | grep MYSQL
MYSQL_HISTFILE=/dev/null
_=MYSQL_HISTFILE
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ] env | grep MYSQL
MYSQL_HISTFILE=/dev/null
[ root @ dbversity : ~ ]
Now, login to the mysql and execute few sql commands. You’ll notice that ~/.mysql_history file is not getting created anymore.
[ root @ dbversity : ~ ] mysql -u root -p -h localhost –ssl
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.1.13-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mydb |
| mysql |
| performance_schema |
| test |
+——————–+
5 rows in set (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> use test
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 [test]> exit
Bye
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ] cat ~/.mysql_history
cat: /root/.mysql_history: No such file or directory
[ root @ dbversity : ~ ]