Novice MySQL users usually have many questions surrounding MySQL Server: what’s the MySQL hostname? Why is the name of the default MySQL user “root” and not something else? Can we rename the primary user of MySQL? Where does MySQL get its host?
In this blog, we dig into one of the primary questions in that realm: where does MySQL get its host and how does the database do it?
Where Does MySQL Get Its Host?
So, where does MySQL get its host? In other words, where are the MySQL host names stored, what’s stored together with them, and why are they the way that they are?
Thankfully, the answer to that question is quite simple and straightforward: all MySQL users, no matter what flavor of MySQL is in use, are stored in the mysql database, in the user table. All information pertaining to these users is stored together with them too.
You can find out that this is indeed the case by issuing a query like so:
SELECT * FROM mysql.user;
Let this query execute, and observe the list of all of the users privileged to access MySQL Server!
So, to answer the question to where does MySQL get its host is from the user table.
The user table in the MySQL database contains not only the host names acted on by your database. It also contains various other types of information including, but not limited to:
- The usernames of all of the users within MySQL.
- The hashed passwords pertaining to the users of MySQL Server.
- All of the definitions of privileges that a specific user has. Privileges include Select, Insert, Update, Delete, Create, Drop, Reload, Shutdown, Process, File, Grant, References, Index, Alter, Show database (Show_db), SUPER, Create_tmp_table, Lock_tables, Execute, privileges exclusive to the Replication slave and replication client (repl_slave and repl_client), privileges for views (Create_view and Show_view), privileges specific to routines, events, triggers, tablespaces, and even history deletion.
- The same table also contains SSL information. Information on SSL includes information like the SSL type being in use for a specific user, what MySQL plugin is the user using to authenticate itself, and whether his password is expired or not.
- The user table also includes statistics on queries that can be run by the user (e.g. the max_connections variable being set to 50 for a specific user would signify that the user cannot exceed 50 connections, etc.)
So, the answer to the question where does MySQL get its host would be specific and straightforward: MySQL gets information about all hosts from the users table in the MySQL database.
The users table contains a lot of information specific to the hosts and usernames, and as such, is capable of informing MySQL how to act in regards to a specific user.
The user table within the MySQL database can be queried directly, but MariaDB does advise using queries like CREATE USER and GRANT PRIVILEGES to create users and grant privileges to them.
Summary
MySQL gets the information about its hosts, users related to them and their privileges from the users table within the MySQL database. The table can be queried directly, but it’s advisable to make use of queries like CREATE USER and GRANT PRIVILEGES to achieve your goals.
FAQ
Where does MySQL get its host and information related to it?
MySQL gets its host and information related to the hosts within MySQL from the users table in the MySQL database.
Can I add users, privileges, and other parameters to the MySQL table myself?
Yes, even though it’s always advisable to make use of queries like CREATE USER and GRANT PRIVILEGES – that way, MySQL would populate the table automatically.
Where can I learn more about MySQL and other database management systems?
To learn more about MySQL, MariaDB, Percona Server, and other database management systems like SQL Server, PostgreSQL, TimescaleDB and others, follow our blog, as well as database-related YouTube channels.