Now we need to create a user and grant it permission, so we can be able to login with this user remotely.Ĭonnect to MySQL database as root, or any other user with the root privilege. Sudo systemctl restart rvice # for debianįinally, MySQL server is now able to accept remote connections. This depends on your operating system: sudo systemctl restart mysql # for ubuntu Now restart the MySQL server, either with systemd or use the older service command. In this case, add the directive to the bottom of the file /etc/mysql//mysqld.cnf. Note: If you’re running MySQL 8+, the bind-address directive will not be in the mysqld.cnf file by default. Search for the line starting with "bind-address" and set its value to 0.0.0.0: bind-address = 0.0.0.0 For this, open the nf file: sudo gedit /etc/mysql//mysqld.cnf To be able to connect with your user from any IP address, do the following:Īllow MySQL server to accept remote connections. If you see USAGE privilege after running REVOKE command, its fine. Mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM better to check information_er_privileges table after running REVOKE command. If you accidentally grant access to a user, then better have revoking option handy.įollowing will revoke all options for USERNAME from all machines: mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM will revoke all options for USERNAME from particular IP: If you get a mysql shell, don’t forget to run show databases to check if you have right privileges from remote machines. You can check final outcome by: SELECT * from information_er_privileges where grantee like "'USERNAME'%" įinally, you may also need to run: mysql> FLUSH PRIVILEGES įrom terminal/command-line: mysql -h HOST -u USERNAME -pPASSWORD You can also specify a separate USERNAME & PASSWORD for remote access. You can run above command many times to GRANT access from multiple IPs. (Replace USERNAME and PASSWORD by your credentials.) mysql> GRANT ALL PRIVILEGES ON *.* TO IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION Run a command like below to give access from specific IP. Run a command like below to access from all machines. (if you want to create a separate user for that purpose, you can use CREATE USER IDENTIFIED BY 'PASSWORD' ) You may be surprised to see even after above change you are not getting remote access or getting access but not able to all databases.īy default, mysql username and password you are using is allowed to access mysql-server locally. If you do not find skip-networking line, add it and comment out it. Start with editing mysql config file vim /etc/mysql/my.cnfĬomment out following lines. If you try to connect to your mysql server from remote machine, and run into error like below, this article is for you.ĮRROR 1130 (HY000): Host ‘1.2.3.4’ is not allowed to connect to this Home / Tutorials / Mysql / Enable Remote Access (Grant)
0 Comments
Leave a Reply. |