Ok it was pretty straightforward in the end, I was confusing things by limiting connections to 1 .. and then using that connection to test locally
/kick
Creating a MySQL user:
If using Navicat;
- Click 'User' on the main ribbon
- Right click 'root@localhost'
- Choose 'duplicate user'
- In the username field, change it to something preferable to root_Copy (or keep it if you think it has a nice ring to it)
- In the host field, replace 'localhost' with '69.168.254.244'
- In the password/confirm password fields, enter a secure password. Note: A long password (e.g. dogsandcatslikerawfishfordinner) is exponentially more secure than one with uppercase, lowercase, numbers, and symbols while also being much easier to remember
- Under the advanced tab, change 'max user connections' to 1
- Under the server privileges tab, uncheck the privileges 'File', 'Process', 'Drop', 'Shutdown', 'Super', and 'Create user' (there may be others worth disabling)
- Click Save
If you're using mysql command line you're either a) clever enough to do this part by yourself, or b) you should really get a copy of Navicat as it will improve your quality of life.
To create a filewall rule:
In windows firewall
- Click new rule
- Select 'Port' and click next
- Select 'TCP and specific local ports', enter 3306, click next
- Select 'Allow the connection', click next
- Select the appropriate network type ('domain', 'private', 'public'). This will typically be private but if in doubt tick them all. Then click next
- Enter a name for the rule (e.g. Incoming Mysql), click finish
For extra security:
- Refresh the list of rules (F5)
- Locate the new rule (e.g. Incoming Mysql) in the list, right click and choose Properties
- Under the scope tab, find Remote IP address and select 'These IP addresses'
- Click Add, select 'This IP or subnet'
- Enter 69.168.254.244
- Click Ok, and then click Ok again