Introduction

MySQL Error 1133 (Can't find any matching row in the user table) occurs when you try to modify a user account that does not exist in the mysql.user table, or when the username and host combination does not match any existing row. This typically happens with SET PASSWORD, GRANT, ALTER USER, or RENAME USER statements when the specified 'user'@'host' pair is not found. The fix is to verify the exact username and host stored in the user table and use the correct combination, or create the user first if it does not exist.

When This Error Occurs

1-- These all can trigger Error 1133
2SET PASSWORD FOR 'admin'@'localhost' = 'newpassword';
3-- ERROR 1133 (42000): Can't find any matching row in the user table
4
5GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';
6-- ERROR 1133 (42000): Can't find any matching row in the user table
7
8ALTER USER 'admin'@'localhost' IDENTIFIED BY 'newpassword';
9-- ERROR 1133 (42000): Can't find any matching row in the user table

Diagnosing the Problem

Check which users actually exist:

1-- List all users and their hosts
2SELECT User, Host FROM mysql.user;
3-- +------------------+-----------+
4-- | User             | Host      |
5-- +------------------+-----------+
6-- | root             | localhost |
7-- | admin            | %         |  <-- host is %, not localhost
8-- | mysql.sys        | localhost |
9-- +------------------+-----------+
10
11-- The user 'admin'@'localhost' does not exist
12-- The user 'admin'@'%' exists instead

The most common cause: the user was created with 'admin'@'%' (any host) but you are trying to modify 'admin'@'localhost' (localhost specifically). MySQL treats these as different user accounts.

Fix 1: Use the Correct Host

1-- Check the actual host value
2SELECT User, Host FROM mysql.user WHERE User = 'admin';
3-- admin | %
4
5-- Use the correct user@host combination
6SET PASSWORD FOR 'admin'@'%' = 'newpassword';
7
8-- Or with ALTER USER (MySQL 5.7+)
9ALTER USER 'admin'@'%' IDENTIFIED BY 'newpassword';
10
11-- Grant with correct host
12GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'%';
13FLUSH PRIVILEGES;

Fix 2: Create the Missing User

If the user does not exist at all, create it first:

1-- Create the user
2CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password123';
3
4-- Then grant privileges
5GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';
6FLUSH PRIVILEGES;
7
8-- Or create and grant in MySQL 8+ with IF NOT EXISTS
9CREATE USER IF NOT EXISTS 'admin'@'localhost' IDENTIFIED BY 'password123';
10GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'admin'@'localhost';
11FLUSH PRIVILEGES;

Fix 3: After Modifying mysql.user Directly

If you manually inserted or updated rows in the mysql.user table, you must flush privileges:

1-- If you manually modified mysql.user
2INSERT INTO mysql.user (User, Host, authentication_string)
3VALUES ('admin', 'localhost', '');
4
5-- Tell MySQL to reload the privilege tables
6FLUSH PRIVILEGES;
7
8-- Now SET PASSWORD will find the user
9ALTER USER 'admin'@'localhost' IDENTIFIED BY 'newpassword';

Understanding MySQL Host Matching

MySQL authenticates users by both username AND host. These are all different accounts:

1-- These are three separate user accounts
2CREATE USER 'admin'@'localhost';      -- Connections from localhost only
3CREATE USER 'admin'@'192.168.1.%';   -- Connections from 192.168.1.* subnet
4CREATE USER 'admin'@'%';             -- Connections from any host
5
6-- Check which account a connection is using
7SELECT CURRENT_USER();
8-- admin@% (the matched user@host pair)

MySQL matches from most specific to least specific host:

1. Exact hostname or IP ('admin'@'192.168.1.10')
2. Wildcard pattern ('admin'@'192.168.1.%')
3. Any host ('admin'@'%')

Resetting Root Password

Error 1133 often appears when trying to reset the root password:

1-- Check root's actual host
2SELECT User, Host FROM mysql.user WHERE User = 'root';
3-- root | localhost
4
5-- MySQL 5.7+
6ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
7
8-- MySQL 8.0+ with auth plugin
9ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';
10FLUSH PRIVILEGES;

If root does not exist (corrupted installation):

1# Stop MySQL and start in safe mode
2sudo mysqld_safe --skip-grant-tables &
3
4# Connect without password
5mysql -u root
6
7# In MySQL shell
8FLUSH PRIVILEGES;
9CREATE USER IF NOT EXISTS 'root'@'localhost' IDENTIFIED BY 'newpassword';
10GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
11FLUSH PRIVILEGES;

Renaming Users

1-- Rename changes both user and host
2RENAME USER 'admin'@'%' TO 'admin'@'localhost';
3
4-- Verify the change
5SELECT User, Host FROM mysql.user WHERE User = 'admin';
6-- admin | localhost

Common Pitfalls

• Confusing 'user'@'%' with 'user'@'localhost': These are separate accounts in MySQL. A user created with @'%' does not match operations targeting @'localhost'. On many systems, localhost connections use a Unix socket and match @'localhost' specifically, not @'%'. Check with SELECT User, Host FROM mysql.user before modifying.
• Not running FLUSH PRIVILEGES after direct table edits: If you modify the mysql.user table directly with INSERT or UPDATE instead of using CREATE USER or ALTER USER, MySQL does not reload the privilege tables. Always run FLUSH PRIVILEGES after direct table modifications.
• Anonymous user accounts interfering: An anonymous user (''@'localhost') can take priority over 'admin'@'%' for localhost connections. Check for anonymous accounts with SELECT User, Host FROM mysql.user WHERE User = '' and drop them if they cause issues.
• Using GRANT to create users in MySQL 8+: In MySQL 5.7 and earlier, GRANT implicitly creates users. In MySQL 8.0+, you must CREATE USER first, then GRANT. Running GRANT for a non-existent user triggers Error 1133 in MySQL 8.0+.
• Host resolution differences: If MySQL resolves 127.0.0.1 to localhost or vice versa, the stored host may differ from what you expect. Use skip-name-resolve in my.cnf to disable DNS resolution and always use IP addresses for consistency.

Summary

• Error 1133 means the 'user'@'host' combination does not exist in mysql.user
• Always check the exact host value with SELECT User, Host FROM mysql.user WHERE User = 'username'
• 'user'@'%' and 'user'@'localhost' are different accounts — use the correct one
• Create the user with CREATE USER before granting privileges in MySQL 8.0+
• Run FLUSH PRIVILEGES after any direct modifications to the mysql.user table