To get MySql Community Server hosted on Ubuntu to get connected to my MySQL Workbench installed on Windows 10, here are the things that I did to connect via TCP/IP which was also useful for me to connect to MySQL via my code:
Step 1:Changing the bind address in MyMySQL
On the Ubuntu machine (where my MySQL Community Server is installed) using Putty, I changed the mysqld.cnf file with the command “sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf”. Using arrow keys, I scrolled down to the row with “bind-address” and I changed it from “127.0.0.1” to “0.0.0.0”. I used ctrl+x to close and entered “Y” to save the file.
Step 2: Restart the MySQL Server
You have to restart the MySQL server. To do that, you can restart using the command “sudo service mysql restart”. If you are unable to restart, kill MySQL Process. First find the Process ID of MySQL using “ps ax | grep mysql” then kill the process using “sudo kill [process_id]”
Step 3:Changing the Firewall Settings of Ubuntu
To allow outside connections, you should change the settings of the firewall. I used UFW (Uncomplicated FireWall) to change the settings. Start the UFW if it is not yet enabled using the command “sudo ufw enable”. Check the status using “sudo ufw status”. Now, enable connections to the MySql port 3306 (default) from a particular IP address using the command “sudo ufw allow from [ip_address] to any port 3306” or you can allow all connections to the port from any IP address is your IP address keeps changing using the command “sudo ufw allow mysql”
Step 4: Creating a user in MySQL
I created a user in MySQL to avoid logging in using the root using the command “CREATE USER ‘user_name’@’your_IP_Address’ IDENTIFIED BY ‘password’;
Step 5: Connect via the MySQL workbench
- Click Database
- Click Connect to Database
- In the Connection Method field, select “Standard TCP/IP”
- In the Hostname, enter your server IP address
- In the Server Port, leave as it is “3306” unless you have changed the port of MySql in your server
- In the Username, enter your server user name
- In the Password, enter your server password
- Click Ok
Step 6: If you are still not able to connect
- On your server, disable the firewall and check if you are able to connect
- If you are able to connect, reset the firewall with the command “sudo ufw reset” (Make sure you take a copy or screenshot of all the connections in the firewall. You can get all the ports with the command “sudo ufw status”). If you are unable to connect go to step 9.
- Now allow each and every port that was earlier present with the command “sudo ufw allow 80”, “sudo ufw allow mysql”, etc.
- Disable and enable the firewall using the commands “sudo ufw disable” and “sudo ufw enable” respectively.
- Check if you are able to connect via the workbench
- If you are still not able to connect, just restart the server using the command “sudo reboot”
- Once the reboot is complete, you should be able to connect to the MySQL server.
- If you are still not able to connect, use something like nmap software to check for all the open ports and see if you can see 3306. If you can’t see the port, you have to debug what is stopping the firewall from blocking your MySQL connections.
- Check your MySQL privileges with the command “show grants for ‘user_name’@’localhost’;”. If you are able to see the grants, that means your IP address is not allowed. Try to update the grants with your IP address with the following command “GRANT ALL PRIVILEGES ON database.* TO ‘user’@’yourremotehost’ IDENTIFIED BY ‘newpassword’;” or GRANT ALL PRIVILEGES ON database.* TO ‘user’@’yourremotehost’ IDENTIFIED BY ‘newpassword’; (To allow connections from everywhere)
If the above steps don’t work and still you want to connect via a quick hack
Premium Hack Step: Logging in using MySQL Workbench via SSH
- Click Database
- Click Connect to Database
- In the Connection Method field, select “Standard TCP/IP over SSH”
- In the SSH Hostname, enter your server IP address
- In the SSH Username, enter your server user name
- In the SSH Password, enter your server password
- In the SSH Key file, don’t do anything
- In the MySQL Hostname, leave it as it is “127.0.0.1”
- In the Server Port, leave as it is “3306” unless you have changed the port of MySQL in your server
- In the Username, enter your MySQL username that you created in the previous step
- In the Password, enter your MySQL password that you created in the previous step
- Click Ok
Now, you should be able to enter your MySQL Workbench
Other useful commands:
- To check if MySQL is running: systemctl status mysql
- Entering MySQL: sudo mysql -u [username] -p (Default username is root and password is nothing (just press enter key))
- To check the status of ufw: sudo ufw status
- To check the ufw status numbered for deleting a port access: sudo ufw status numbered
- To delete a ufw port access: sudo ufw delete [number_of_the_port_to_be_deleted_from_previous_command]
- To start MySQL service: sudo service mysql start
- To update all packages: sudo apt update
- To uninstall MySQL: sudo apt-get remove mysql*
- To install MySQL
Step 1: sudo apt update
Step 2: sudo apt install mysql-server
Step 3: sudo systemctl start mysql.service
Step 4 (if something is broken during installation): sudo apt –fix-broken install
Viplav's Blog 