it limits the accepted connections to a pattern of IP addresses. The "Limit to Hosts Matching" field is what disallows you to connect non-locally. You can do this by opening a local connection to the server with MySQL Workbench, then going to Server>Users and Privileges from the menu bar and finding the user account you want to connect with. While it isn't recommended, you can instead put % in that field for testing purposes. You can find this file in C:\ProgramData\MySQL\MySQL Server 8.0 on Windows.Īfterwards, check that the user account you are establishing the connection with does not have localhost in the Limit to Hosts Matching field. is at least commented out in your my.ini or my.cnf file. The following three steps should do it.Įnsure that the line starting with bind-address. It would be preferable if you were to set up user accounts with more restrictive permissions. Resources: How to Allow Remote Connections to MySQLĮnabling remote root access can be dangerous. Type '\c' to clear the current input statement. Other names may be trademarks of their respective Oracle is a registered trademark of Oracle Corporation and/or itsĪffiliates. Server version: 5.7.31 MySQL Community Server (GPL)Ĭopyright (c) 2000, 2020, Oracle and/or its affiliates. You should get this output depending on your MySQL server version: Welcome to the MySQL monitor. So in my case it was: mysql -u root -h 34.69.261.158 -p Where u represents user, h represents mysql-server-ip-address and p represents password. In my case the user is root: SELECT host FROM er WHERE user = "root" įinally, you can try connecting to the MySQL server from another server using the command: mysql -u username -h mysql-server-ip-address -p You can specify the Ip-Address of the individual hosts that you want to grant the user access from using the command - GRANT ALL PRIVILEGES ON *.* TO IDENTIFIED BY 'my-password' Īfterwhich I checked the hosts that the user now has access to. Note: % grants a user remote access from all hosts on a network. GRANT ALL PRIVILEGES ON *.* TO IDENTIFIED BY 'my-password' Next, I ran the command below to grant the root user remote access to the database named my_database: USE my_database In my case the user is root: SELECT host FROM er WHERE user = "root" Next, log into the MySQL server console on the server it was installed: mysql -u root -pĬheck the hosts that the user you want has access to already. Apply the changes made to the MySQL config file by restarting the MySQL service: sudo systemctl restart mysql Once you make the necessary changes, save and exit the configuration file. Scroll down to the bind-address line and ensure that is either commented out or replaced with 0.0.0.0 (to allow all remote connections) or replaced with Ip-Addresses that you want remote connections from. Use your preferred text editor to open the MySQL server configuration file: sudo nano /etc/mysql//mysqld.cnf I had to this challenge when working on a Java Project with MySQL server as the database.įirst, confirm that your MySQL server configuration to allow for remote connections.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |