No Linux No Life – How to Creare a MySQL User and Allow Their Access

Just as I mentioned in the previous post, I’ve been building a REST API system for my new Android portfolios. And MySQL database plays a significant role here – without a database, you simply can’t create any apps. Since I’ve been really familiar with databases, here I’ll share some of the knowledge and what I did to my LAMP server.

How to install MySQL:

Forst and foremost, you need to install MySQL server on your machine, Here’s how (Ubuntu Server 20.04).

sudo apt update

sudo apt install mysql-server

sudo systemctl start mysql.service

sudo mysql_secure_installation

Then, follow the instructions…

How to force stop MySQL:

During your LAMP-building process, you may encounter some problems. Here’s how you can force kill MySQL server.

sudo pkill mysqld

Create a MySQL user:

First of all, you need to create MySQL users and allow them to access the server if they need to access it from external machines. In my environment, I built the entire LAMP server environment in my home Linux server and made users for my Windows/Android machines to access it externally.

CREATE USER 'user'@'localhost' IDENTIFIED BY '192.168.0.xxx';

GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.0.xxx';

FLUSH PRIVILEGES;

Delete MySQL users:

Contrarily, you may face a situation you need to delete a MySQL user – here’s how.

DROP USER IF EXISTS 'user'@'127.0.0.1' ;

Grant privileges to the user:

in this example, I granted all privileges to the user, but you can choose more specific roles by using instead of *(asterisk).

GRANT ALL PRIVILEGES ON *.* TO 'user'@'192.168.0.xxx';

FLUSH PRIVILEGES;

Configure mysqld.cnf

This is also a necessary part that you need to use whatever text editor (vim or nano) to add the port number, MySQL is using 3306 by default, and set bind-address to 0.0.0.0.

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

port  = 3306
bind-address  = 0.0.0.0

List users and their hosts:

When you create users and set their host IP, you sometimes need to list them all. Here’s the command. By the way, to execute this command, you may need to log in to the MySQL server as root.

SELECT user,host FROM mysql.user;

Only for DBeaver users:

If you happen to use DBevaer as your DB client, you may face a trouble accessing MySQL server, and one of the major problems I always encounter is: “Public Key Retrieval is not allowed”. Here’s how you can solve it.

Go to Driver Properties and change allowPublicKeyRetrieval drop-down to TRUE.

Allow a specific user(machine) to access the LAMP server:

I encounter a lot of network and server-security-related problems during the process. One of the reasons my other Windows/Android client machines couldn’t access it is that the Ubuntu Server, which I currently use as my home server, blocks any external IP address by default. And you can allow some specific IP address to access it by using ufw command as shown below.

sudo ufw allow from 192.168.0.xxx to any port 3306

Change the owner of PHP files.

Since it’s LAMP, you need scripting files that work as its API, and in my case I used PHP. But if the PHP files must be owned by the apache server. It depends on what distro you use, but if you happen to use Ubuntu Server 20.04 as I do, you need to change the PHP file owner to www-data, which is apache.

sudo chown -R www-data:www-data *.php

Afterthoughts:

Building a REST API with a LAMP environment is exciting, but also you may encounter lots of problems. But as I said as always, the problems you encounter in any form of computing program are a letter from your machine. Learn it, examine it, google it, and see how you can come up with any possible solutions.