2

I have setup a mysql server on unbuntu. The server hosts websites for my various clients. Hence, I want all of them(and any new clients) to have remote access to the mysql server. How do I configure mysql for the same?

I know about the GRANT ALL PRIVILEGES command, but that would require me to individually grant remote access to all the users.

migrated from stackoverflow.com Jul 23 '14 at 11:48

This question came from our site for professional and enthusiast programmers.

6

I highly recommend that you do not run

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';

This user has the SHUTDOWN privilege, which can allow the user shutdown mysql remotely with

mysqladmin -hIP_of_DB Server -uusername -p shutdown

You also do not want the SUPER privilege given remotely to just anyone. Why ?

The SUPER privilege enables an account to use CHANGE MASTER TO, KILL or mysqladmin kill to kill threads belonging to other accounts (you can always kill your own threads), PURGE BINARY LOGS, configuration changes using SET GLOBAL to modify global system variables, the mysqladmin debug command, enabling or disabling logging, performing updates even if the read_only system variable is enabled, starting and stopping replication on slave servers, specification of any account in the DEFINER attribute of stored programs and views, and enables you to connect (once) even if the connection limit controlled by the max_connections system variable is reached.

If you have multiple users and want them to access specific databases, you need to create the user with database-level privileges. The SHUTDOWN and SUPER privileges are not part of database-level grants (See my post MySQL user without SUPER privilege as to why)

Suppose you have a database called mydb and you want to access it remotely. Do this:

GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'%';

I have a better idea for you. You should restrict remote users by their netblocks

GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.20.%';
GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.20.30.%';
GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'10.20.30.40';

Note that you will have to drop 'username'@'%' first before creating DB-level user access.

See my post MySQL error: Access denied for user 'a'@'localhost' (using password: YES) for full clarification on MySQL's User Authentication Paradigm

After you have created the needed users, make sure your firewall has port 3306 open.

1

You can create a username and password also provide grant access to them. so that all clients can access the mysql from their local by using the username and password provided by you.

CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'username'@'%';
  • 1
    What do you mean with the "There is no remote access for mysql"? Can't I access a remote MySQL server? – ypercubeᵀᴹ Jul 23 '14 at 12:17
  • @ypercube pl check now – Karthick Jul 23 '14 at 12:21
  • 1
    Why ALL PRIVILEGES ON ALL DB's ? It may be risky ! – Abdul Manaf Jul 23 '14 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy