title: Authentication Failure When Connecting to MySQL as Non-Root User
body: <p>My login attempts fail for a new user account that I have created, and I have spent several hours learning how authentication is handled by MySQL, but ultimately I think I'm just not fully understanding how all the components fit together regarding MySQL Server's authentication logic.</p>
  
  <p><strong>All on the same server:</strong></p>
  
  <ul>
  <li><p>MySQL 5.7</p></li>
  <li><p>phpMyAdmin 4.9</p></li>
  <li><p>Ubuntu 18.04</p></li>
  </ul>
  
  <p><strong>Additional info:</strong></p>
  
  <ul>
  <li><p>There are no users with blank usernames in the table mysql.user</p></li>
  <li><p>The password doesn't have special characters</p></li>
  <li><p>I've tried localhost, 127.0.0.1 and the actual hostname itself</p></li>
  <li><p>/var/log/mysql/error.log only shows the exact same error message</p></li>
  </ul>
  
  <p><strong>CREATING THE USER</strong></p>
  
  <p>This is done by either:</p>
  
  <ol>
  <li>phpMyAdmin GUI > SQL tab:</li>
  </ol>
  
  <pre><code>flush privileges;
  create user 'testuser'@'localhost' identified by 'password!';
  grant * on inventory to 'testuser'@'localhost';
  flush privileges;
  </code></pre>
  
  <ol start="2">
  <li><p>phpMyAdmin GUI > User Account tab > Add User Account</p></li>
  <li><p>ssh into the Ubuntu server and create the user from the mysql command line (as root)</p></li>
  </ol>
  
  <p>Regardless of how I do it, I can create the user successfully, but I'm unable to authenticate.</p>
  
  <p><strong>AUTHENTICATING THE USER</strong></p>
  
  <p>Here's the two ways I've tried:</p>
  
  <p>1) From an ssh session on the Ubuntu server:</p>
  
  <ul>
  <li><p>mysql -u testuser -p</p></li>
  <li><p>mysql -u testuser - h 127.0.0.1 -p</p></li>
  <li><p>mysql -u testuser - h localhost -p</p></li>
  <li><p>mysql -u testuser - h  -p</p></li>
  </ul>
  
  <p>2) Run connect.php from another machine using (IE / Chrome / FF):</p>
  
  <pre><code>&lt;?php
  
  $dbhost='localhost';
  $dbuser='testuser';
  $dbpass='password';
  $dbname='inventory';
  
  $conn = new mysqli($dbhost,$dbuser,$dbpass,$dbname);
  
  if($conn-&gt;connect_error) {
      die("Error: Couldn't connect: ".$conn-&gt;connect_error);
  }
  
  ?&gt;
  </code></pre>
  
  <p>2) 
  Depending on whether I used <code>'testuser'@'%'</code> or <code>'testuser'@'localhost'</code> I'll get one of two errors:</p>
  
  <ul>
  <li><p>Access denied for user 'testuser'@'%' (using password: YES)</p></li>
  <li><p>Access denied for user 'testuser'@'localhost' (using password: YES)</p></li>
  </ul>
  
  <p>To rule out this being a permissions issue, I assigned the user all global permissions, but the error message didn't change, so I reverted the change.</p>
  
  <p>Running <code>select host,user,plugin,authentication_string,account_locked from mysql.user where user='testuser';</code> shows:</p>
  
  <p><strong>host</strong> localhost</p>
  
  <p><strong>user</strong> testuser</p>
  
  <p><strong>plugin</strong> mysql_native_password</p>
  
  <p><strong>authentication_string</strong> blahblahblah</p>
  
  <p><strong>account_locked</strong> N</p>