Mysql – Difference between host ‘*’ and host ‘%’

mariadbMySQL

When I run the following command:

SELECT user, host, password, plugin FROM mysql.user WHERE user='user_123';

this is what I get:

+----------+---------------------------------------+-------------------------------------------+-----------------------+
| user     | host                                  | password                                  | plugin                |
+----------+---------------------------------------+-------------------------------------------+-----------------------+
| user_123 | IPv4-address-1                        | *775F9B443B3F45EC0042CE69872E7CF2D20D7FB4 | mysql_native_password |
| user_123 | IPv4-address-2                        | *775F9B443B3F45EC0042CE69872E7CF2D20D7FB4 | mysql_native_password |
<snip>
| user_123 | domain-1                              | *775F9B443B3F45EC0042CE69872E7CF2D20D7FB4 | mysql_native_password |
| user_123 | domain-2                              | *775F9B443B3F45EC0042CE69872E7CF2D20D7FB4 | mysql_native_password |
<snip>
| user_123 | %                                     | *775F9B443B3F45EC0042CE69872E7CF2D20D7FB4 | mysql_native_password |
| user_123 | *                                     | *775F9B443B3F45EC0042CE69872E7CF2D20D7FB4 | mysql_native_password |
+----------+---------------------------------------+-------------------------------------------+-----------------------+

Q) I know that % stands for the wildcard, but what is the difference between % and *?

Best Answer

Per create user % has a wildcard meaning. _ also matches a single character like the like expression.

* has no meaning and was probably put there by someone not familiar with what they are doing. Recommend dropping this use.

Related Solutions

Thesql user creation with % to host , can only log on from remote host

When connecting to your localhost it's going you use sockets instead of ports to connect. Those grants need a specific 'localhost' grant. To use your % grant locally connect specifying you want to connect via TCP

mysql -u user -p --proto=TCP

MySQL: Show full processlist has empty Host name. Why

The system user is the user defined to execute MySQL Replication.

There are two DB Connections dedicated to performing MySQL Replication

IO Thread : This thread is responsible for downloading the latest Binary Log Entries from the Master and Storing those Entries in the Slave's Relay Logs.
SQL Thread : This thread is responsible for processing the relay logs like a FIFO queue. It basically reads the next available Relay Log Entry and processes the SQL of that Entry.

The system user is referred to as a non-client thread in the MySQL Documentation about SHOW PROCESSLIST;

As far seeing localhost:#####, that number after the colon is a really a port number within mysqld assigned to localhost.

UPDATE 2012-04-27 18:00 EDT

Questions from your comment

Can I rename the system user? Or any other properties of the system user? Also, is the system user is dedicated only for replication? Or any other MySQL processes are spawned by System User? I understand that system user cannot be accessed by a client, its an internal process spawned by MySQL.

Answers to Questions from your comment

No, you cannot rename the system user. It is dedicated to handle MySQL Replication only. The only way to manipulate properties of of the system user would be throught the GRANT command issued to create the replication user.

For example, when you setup a replication user, you issue a command like this:

GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* to 'repl'@'%';

When START SLAVE is issued on a Slave, the Master authenticates the DB Thread coming from the Slave and assign one thread on the Master. The thread on the Master will ship binary log entries to the I/O Thread on the Slave. The I/O Thread on the Slave is assigned to system user for handling communcation between Master and Slave. The SQL Thread on the Slave is also assigned to system user for handling intracommunication of local relay log entries to be processed FIFO (Frist In, First Out) by mysqld running on the Slave. No direct access is permitted via the MySQL Client on the Slave except for

STOP SLAVE; (Kills both I/O Thread and SQL Thread)
STOP SLAVE IO_THREAD;
STOP SLAVE SQL_THREAD;
START SLAVE; (Creates both I/O Thread and SQL Thread)
START SLAVE IO_THREAD;
START SLAVE SQL_THREAD;

Of course, you could issue KILL ####; where #### is the process ID of either the I/O Thread or SQL Thread. You would be totally respsonsible for reestablishing replication at the risk of losing the correct log file and position if the KILL command misses any communication because of an unnatural stoppage of a replicaton thread.