Location>code7788 >text

Ubuntu mysql login via ip guide

Popularity：940 ℃/2025-01-17 21:52:20
1 CREATE USER 'shaun'@'%' IDENTIFIED BY 'shaun001';
2 GRANT ALL PRIVILEGES ON *.* TO 'shaun'@'%';
3 FLUSH PRIVILEGES;

Note here: In the first line of code, the '%' flag allows access from any IP. If you want to restrict IP, you can change it to

CREATE USER 'shaun'@'192.168.1.100' IDENTIFIED BY 'shaun001'; //Only allow 192.168.1.100 IP to access with shaun

 or
CREATE USER 'shaun'@'localhost' IDENTIFIED BY 'shaun001'; //only allowedlocalhost The domain name starts withshaunaccess

The second line of code *.* means all databases and can be changed to GRANT ALL PRIVILEGES ON database_name.* TO 'shaun'@'%';

If a 'shaun'@'localhost' already exists, you can change the permissions with the following code:

1 RENAME USER 'shaun'@'localhost' TO 'shaun'@'%';
2 GRANT ALL PRIVILEGES ON *.* TO 'shaun'@'%';
3 FLUSH PRIVILEGES;

The meaning of the third line of code is to refresh the permission table so that it takes effect immediately.

4. Configure mysql binding address

By default, MySQL only listens for local connections. To allow remote access, the MySQL configuration file needs to be modified:

  1. Open the MySQL configuration file (usually located at/etc/mysql//or/etc/）：

    1 sudo nano /etc/mysql//

     

  2. turn upbind-addressThis line, change its value from127.0.0.1Change to0.0.0.0(Allow access from all IPs) or specific IP addresses:
     bind-address = 0.0.0.0
  3. Save file: pressCtrl + O(The letter "O", not the number zero). This will prompt you to confirm the file name, pressEnterkey to save.
    Exit nano: PressCtrl + XExit.
  4. Restart the mysql server for the changes to take effect:
    1 sudo systemctl restart mysql

5. Firewall configuration

If the server has a firewall enabled (e.g.ufw), you need to allow access to MySQL’s default port (3306):

1 sudo ufw allow 3306/tcp

6. Test remote connection

1 mysql -h 192.168.1.100 -u shaun -p

Enter the password corresponding to shaun and confirm. Follow the result prompts to see if it is successful.

7. Revoke permissions (if necessary) 

1 REVOKE ALL PRIVILEGES ON *.* FROM 'shaun'@'%';
2 FLUSH PRIVILEGES;

This will remove the usershaunAll permissions on all databases.


 

 

 

 

<Previous Next>