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';
Thank you for sharing this informative blog post on allowing remote connections for MySQL for both root and other users. The step-by-step instructions are clear and easy to follow, which makes it a valuable resource for those looking to configure their MySQL server.
The core idea of this blog post is to help users configure their MySQL server to allow remote connections, which is essential for many applications and services that require access to the database from different locations or devices. The instructions provided are comprehensive, covering the process for both root and other users, as well as addressing firewall settings and binding the server to all ports.
One of the major highlights of this post is the inclusion of specific commands and code snippets, making it easy for readers to follow along and implement the changes. This is particularly helpful for those who may be new to working with MySQL or server configuration.
However, there are a few areas where the post could be improved. Firstly, it would be helpful to provide a brief introduction to the importance of allowing remote connections in MySQL, as well as potential security risks associated with doing so. This would give readers a better understanding of why they might need to perform these steps and any precautions they should take.
Additionally, while the post does provide instructions for creating a new user and granting them the necessary permissions, it would also be beneficial to include information on how to modify the permissions of an existing user. This would be useful for readers who may already have a user set up and just need to adjust their settings to allow remote connections.
Finally, it's important to note that the process for configuring MySQL may differ depending on the operating system being used. While the instructions provided in this post seem to be geared towards Linux systems, it would be helpful to include information on how to perform these steps on other platforms, such as Windows or macOS.
Overall, this is a well-written and informative post that provides valuable guidance for those looking to configure their MySQL server for remote connections. With a few minor additions and adjustments, this post could become an even more comprehensive resource for readers.