IxDF Developer Docs

Appearance

List of Tips ​

  1. Access MySQL from external sources
  2. Creating a database user for outside access
  3. Granting permissions to the user
  4. Creating a specific user for a specific IP address
  5. Changing the IP address of a specific user
  6. Removing the user

Access MySQL from external sources ​

The server listens internally only by default. To change that:

bash
sudo vim /etc/mysql/my.cnf

Then comment out this line:

text
# bind-address 127.0.0.1

Now let’s open the port 3306 to outside access. Run the commands below and all will be fine!

bash
sudo ufw allow 3306/tcp
sudo service ufw restart

Creating a database user for outside access ​

Run the command below by providing a valid username and password pair.

text
mysql -uusername -ppassword

Provide username and password and run the command below to create the user. % means anywhere. You can change it to localhost for internal access only, for example.

text
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

Granting permissions to the user ​

Change database to the database name which you want that user to have access on. You can also change it with * to make that user have access to all databases.

text
GRANT SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT ON database.* TO 'username'@'%' IDENTIFIED BY 'password';

The command above will grant user with read-only privileges. You can use ALL instead of SELECT, SHOW VIEW, PROCESS, REPLICATION CLIENT to grant all privileges (e.g read, write etc.) to that user.

Creating a specific user for a specific IP address ​

When you get into the MySQL terminal, run the commands below:

text
CREATE USER 'username'@'ip-address' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'username'@'ip-address';
FLUSH PRIVILEGES;

You need to change username, ip-address and password above to your liking. If you run those 3 commands one by one, your user with username and password will be able to connect to the MySQL server with ALL PRIVILEGES from the specified ip-address.

Changing the IP address of a specific user ​

When you get into the MySQL terminal, run the command below:

text
UPDATE mysql.user set Host="new-ip-address" where User="username";
FLUSH PRIVILEGES;

You need to change username and new-ip-address above to your liking.

Removing the user ​

If you ever want to remove a MySQL user, you should know that it’s a tricky job. Even with the commands below, the user might not be fully removed -- but it will do the job generally.

We need to first take all the privileges out of that user’s hands with:

text
GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY 'password';

Then drop the user:

text
DROP USER 'username'@'%';

Quoting a comment on https://dev.mysql.com/doc/refman/5.6/en/drop-user.html:

As of 5.0.37, if the user does not exists, MySQL responds with: 'ERROR 1396 (HY000): Operation DROP USER failed for ...'