Correctly setting your mysql prompt using sudo

If you run multiple MySQL environments on multiple servers it’s a good habit to set your MySQL prompt to double check which server you are on.
however, using the MYSQL_PS1 environment variable I found this does not work under sudo (the normal way people run sudo).

I.e., the following syntax’s work.

$ mysql
$ sudo su - -c mysql
$ sudo su - ; mysql

but the following does not.

$ sudo mysql

The trick is actually to ensure via /etc/sudoers you inherit the MySQL_PS1 environment variable.

echo "export MYSQL_PS1=\"`hostname` [\d]> \"" | sudo tee /etc/profile.d/mysql.sh
echo 'Defaults    env_keep += "MYSQL_PS1"' | sudo tee /tmp/mysql
sudo chmod 400 /tmp/mysql
sudo mv /tmp/mysql /etc/sudoers.d

Tags: , , ,

One Response to “Correctly setting your mysql prompt using sudo”

  1. 1. “sudo su -” is a bad practice. Either use sudo OR su.

    ‘su – mysql’ or ‘su -’ are fine if you have the root or mysql user’s password.

    ‘sudo su -’ works, so does ‘sudo su – mysql’. But for BOTH root permissions are needed.

    It’s better to use ‘sudo -i’ or ‘sudo -i -u mysql’ as then you only need permissions for the user you specifying.

    I’ve seen setup on which a user is only allowed to do ‘sudo su – ‘ instead of permissions to run all or certain commands as that user. Then things as ‘sudo -u mysql some_script’ are denied.

    2. You could use the global /etc/my.cnf to set the prompt:
    [mysql]
    prompt=’\u@\h [\d] > ‘

    Then it doesn’t matter if you reach the root user via sudo or a direct login. But then you’re forced to have the same (default) prompt for all users.

    But other than that: Using MYSQL_PS1 and adding it to the env_keep in sudo are nice solutions. Also good to see /etc/sudoers.d instead of merging everything in the sudoers file directly.