MySQL Allow Remote Connection
MySQL is undoubtedly one of the best database server exists, it is widely used by many of companies or even for just personal project around the world, because it is free and yet capable of doing something bigger.
For this tutorial we will discuss about how you can allow your MySQL server to be managed by local computer. The reason sometimes, especially for me for doing this approach is, when we want to use an advanced database management tools on our local computer, let's say we have Navicat or Workbench.
Without further ado, these are steps how you can do that. My server is Ubuntu and my local computer is Windows, but it think there's no much different.
1. Change bind address on MySQL configuration from localhost to your server IP. Login ssh to your server, and you can use vim for editing "mysqld.cnf" file.
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# Replace xxx with your IP Address
bind-address = xxx.xxx.xxx.xxx
Restart MySQL.
sudo /etc/init.d/mysql start
sudo /etc/init.d/mysql stop
2. Now identify your local IP address. You can use Google, just search for "My IP" then it will show your IP address like for example the picture below:
3. Create a new MySQL user for your local computer, for example, i will create my local user with username 'adam' and my computer IP address is '45.77.235.95'.
On ssh terminal, login MySQL as root, you can do
su - root
and then just type
mysql
Using this SQL command to create a new MySQL user
CREATE USER 'adam'@'45.77.235.95' IDENTIFIED BY 'U5e-Str0n6-p455sword';
You can use '%' instead of using specific IP address, but it's not recommended for security reason, it allows everyone to access your server database, and it's so dangerous, you can be brute-forced or any other type of attacks to steal your server credential, don't do that, play safe.
4. Just adding user is not enough, you should make privileges for your new user, this below MySQL command will allow my new user to have all privileges for managing my databases.
GRANT ALL PRIVILEGES ON *.* TO 'adam'@'45.77.235.95' WITH GRANT OPTION;
FLUSH PRIVILEGES;
5. That's all, now you can use Workbench, Navicat or any other advanced GUI based database management tools.
6. In case your server has a firewall on, an not allowing access for MySQL default port which is 3306, then do this command to open port 3306.
sudo ufw allow 3306
7. In case your IP address somehow changed, for example in my case, my ip has changed from 45.77.235.95 to 45.77.235.96, so i must update our database host. To do that, login via SSH, login with your root MySQL, then.
UPDATE mysql.user SET Host='45.77.235.96' WHERE Host='45.77.235.95' AND User='adam'
FLUSH PRIVILEGES;
8. If you want to change password, login ssh using root, then type mysql, then:
SET PASSWORD FOR 'adam'@'45.77.235.96' = '@AnotherStrongP455W0rd';
FLUSH PRIVILEGES;
I think it's clear enough, but if you still struggling or unclear about this tutorial about allowing remote MySQL connection, you can post a comment below. I am glad to help.
For this tutorial we will discuss about how you can allow your MySQL server to be managed by local computer. The reason sometimes, especially for me for doing this approach is, when we want to use an advanced database management tools on our local computer, let's say we have Navicat or Workbench.
Without further ado, these are steps how you can do that. My server is Ubuntu and my local computer is Windows, but it think there's no much different.
1. Change bind address on MySQL configuration from localhost to your server IP. Login ssh to your server, and you can use vim for editing "mysqld.cnf" file.
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# Replace xxx with your IP Address
bind-address = xxx.xxx.xxx.xxx
Restart MySQL.
sudo /etc/init.d/mysql start
sudo /etc/init.d/mysql stop
2. Now identify your local IP address. You can use Google, just search for "My IP" then it will show your IP address like for example the picture below:
3. Create a new MySQL user for your local computer, for example, i will create my local user with username 'adam' and my computer IP address is '45.77.235.95'.
On ssh terminal, login MySQL as root, you can do
su - root
and then just type
mysql
Using this SQL command to create a new MySQL user
CREATE USER 'adam'@'45.77.235.95' IDENTIFIED BY 'U5e-Str0n6-p455sword';
You can use '%' instead of using specific IP address, but it's not recommended for security reason, it allows everyone to access your server database, and it's so dangerous, you can be brute-forced or any other type of attacks to steal your server credential, don't do that, play safe.
4. Just adding user is not enough, you should make privileges for your new user, this below MySQL command will allow my new user to have all privileges for managing my databases.
GRANT ALL PRIVILEGES ON *.* TO 'adam'@'45.77.235.95' WITH GRANT OPTION;
FLUSH PRIVILEGES;
5. That's all, now you can use Workbench, Navicat or any other advanced GUI based database management tools.
6. In case your server has a firewall on, an not allowing access for MySQL default port which is 3306, then do this command to open port 3306.
sudo ufw allow 3306
7. In case your IP address somehow changed, for example in my case, my ip has changed from 45.77.235.95 to 45.77.235.96, so i must update our database host. To do that, login via SSH, login with your root MySQL, then.
UPDATE mysql.user SET Host='45.77.235.96' WHERE Host='45.77.235.95' AND User='adam'
FLUSH PRIVILEGES;
8. If you want to change password, login ssh using root, then type mysql, then:
SET PASSWORD FOR 'adam'@'45.77.235.96' = '@AnotherStrongP455W0rd';
FLUSH PRIVILEGES;
I think it's clear enough, but if you still struggling or unclear about this tutorial about allowing remote MySQL connection, you can post a comment below. I am glad to help.
