MySQL allow remote connection (For root and other users)

Know the context:

You can run the following SQL to check the current status of your database:

mysql> select * from mysql.user;
mysql> select * from mysql.db;

For all

Ensure firewall allows 3306 port:

sudo ufw allow 3306

Allow MySQL to bind to all ports:

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

Then set bind-address to 0.0.0.0.

bind-address            = 0.0.0.0

For root user

Log in root locally first:

mysql -u root -p

Replace the host value.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';
mysql> UPDATE mysql.user SET host='%' WHERE user='root';

Finally restart service:

sudo systemctl restart mysql.service

For other users

Create an user if doesn't exist

Sign in MySQL first:

sudo mysql -u root -p

Create the new database with the command:

CREATE DATABASE nextcloud;

Create a new user with the command:

CREATE USER 'nextcloud'@'localhost' IDENTIFIED BY 'YOUR_STRONG_PASSWORD';
CREATE USER 'nextcloud'@'%' IDENTIFIED BY 'YOUR_STRONG_PASSWORD';

Where YOUR_STRONG_PASSWORD is a unique and strong password.

Give the new user the necessary permissions with the command:

GRANT ALL PRIVILEGES ON nextcloud.* TO 'nextcloud'@'localhost';
GRANT ALL PRIVILEGES ON nextcloud.* TO 'nextcloud'@'%';

Flush the privileges and exit the console with the commands:

FLUSH PRIVILEGES;
exit;

Existing user:

mysql> update user set Host='%' where user='user1';