Create a MySQL Super User

A ‘super user’ in this context is set up for convenience and has full access to everything, the same as the MySQL root account.

This should only be done after you have considered the consequences of the account becoming compromised. Use a strong password and limit which hosts can be used with the account. This is not for production environments but that’s your decision; we’re all adults here.

With the MySQL daemon running, open Terminal, this is using the existing root account:

mysql -u root -p

Enter your root password at prompt. As long as MySQL is running and you entered the correct credentials, you should be presented with the mysql> prompt.

Enter the following, substituting the placeholders user_namehostyour_password with your values.


CREATE USER 'user_name'@'host' IDENTIFIED BY 'your_password';
GRANT ALL ON *.* TO 'user_name'@'host' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit

Notes:

  • host can be a wildcard using the ‘%’ character, e.g., to specify a network by IP & wildcard, i.e., 10.0.0.% or %.local
  • The password can be specified using the hashed value, if it is known, by prefixing the hash with an ‘*’ asterisk.
  • ‘GRANT ALL’ does not give the GRANT privilege (by design) so we need to use ‘GRANT OPTION’ as well. More info on GRANT: http://dev.mysql.com/doc/refman/5.6/en/grant.html
  • Enter help create user at the mysql prompt to see all options.
  • Passwords entered as part of the CREATE USER statement are logged in .mysql_history and server logs. Make sure these files are secured. MySQL 5.6.3 no longer writes these passwords to server logs: http://dev.mysql.com/doc/refman/5.6/en/password-logging.html

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

This site uses Akismet to reduce spam. Learn how your comment data is processed.