Remote MySQL Admin

I’m running MySQL on an Ubuntu server and need to do some remote admin, but I can’t get in.

Specifications:
* Ubuntu Server: 16.04.3 LTS
* MySQL Server: 5.7.19-0ubuntu0.16.04.1 (Ubuntu)

Using this Stack Overflow post.

  1. Edited /etc/mysql/mysql.conf.d/mysql.cnf

* Commented out
# bind-address = 127.0.0.1
* Added, using LAN IP
bind-address = 192.168.1.11
2. Restarted the machine
3. Attempted to connect remotely using MySQL command line and credentials for the root account on the remote MySQL system, failed:
“`bash
~: mysql -u root -h 192.168.1.11 -p
Enter password: pasted_password_from_vault
ERROR 1130 (HY000): Host ‘PrawnBookPro’ is not allowed to connect to this MySQL server
~:

4. Checked the existing users in MySQL

mysql> select Host, User from mysql.user;
+———–+——————+
” Host User ”
+———–+——————+
localhost ” conditionsUser
” localhost debian-sys-maint ”
localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+———–+——————+
5 rows in set (0.00 sec)

mysql>

5. Set up a new user for remote control: [link](https://stackoverflow.com/questions/1559955/host-xxx-xx-xxx-xxx-is-not-allowed-to-connect-to-this-mysql-server)
```sql
CREATE USER 'prawnhead'@'localhost' IDENTIFIED BY 'not_the_actual_password';
GRANT ALL PRIVILEGES ON *.* TO 'prawnhead'@'localhost' WITH GRANT OPTION;
CREATE USER 'prawnhead'@'%' IDENTIFIED BY 'not_the_actual_password';
GRANT ALL PRIVILEGES ON *.* TO 'prawnhead'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
  1. Connecting remotely:
~: mysql -u prawnhead -h 192.168.1.11 -p
Enter password: *pasted_password_from_vault*
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.7.19-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

So this is just another memory post so I don’t forget how this worked.

That is all