MySQL user password vs authentication_string
All of your CREATE USER syntax is 'backwards'.  In the "native password" scheme:

mysql&gt; CREATE USER 'se211604p'@'localhost'
               IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
Query OK, 0 rows affected (0.00 sec)

mysql&gt; CREATE USER 'se211604m'@'localhost'
               IDENTIFIED BY 'mypass';
Query OK, 0 rows affected (0.00 sec)

mysql&gt; select user, host, password from user where user like 'se2%';
+-----------+-----------+-------------------------------------------+
| user      | host      | password                                  |
+-----------+-----------+-------------------------------------------+
| se211604p | localhost | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
| se211604m | localhost | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-----------+-----------+-------------------------------------------+

mysql&gt; SELECT PASSWORD('mypass');
+-------------------------------------------+
| PASSWORD('mypass')                        |
+-------------------------------------------+
| *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 |
+-------------------------------------------+