![]() A server running MySQL that is accessible via SSH.To complete this tutorial, you will need: This brief tutorial will show you how to connect to a remote database using MySQL Workbench. Using tools like HeidiSQL for Windows, Sequel Pro for macOS, or the cross-platform MySQL Workbench, you can connect securely to your database over SSH, bypassing those cumbersome and potentially insecure steps. And allowing connections to MySQL directly can be a security concern. But connecting remotely to your database server usually entails configuring MySQL to listen on every interface, restricting access to port 3306 with your firewall, and configuring user and host permissions for authentication. Querying this data graphically on your local computer is the easiest way to interact with your database. User1 is now able to access yourDB from a remote location identified by the IP 133.155.44.103.Your database server contains tables full of important data. Granting remote access to a user for an existing database requires a set of two commands: update db set Host='133.155.44.103' where Db='yourDB' How to Grant Remote Access to Existing MySQL Database The name of the database, the username, remote IP, and password need to match the information you want to use for the remote connection. ![]() To grant remote user access to a specific database: GRANT ALL PRIVILEGES ON yourDB.* TO '133.155.44.103' IDENTIFIED BY 'password1' If you do not have any databases yet, you can easily create a database by typing the following command in your MySQL shell: CREATE DATABASE 'yourDB' You should see an output similar to the one below: Connection to mysql_server_ip 3306 port succeeded! How to Grant Remote Access to New MySQL Database? The -p option prompts you to enter the password for the MySQL username. The -h mysql_server_ip is the IP or the hostname of your MySQL server. The -u username in the command represents your MySQL username. Use the following command to establish a connection with your remote MySQL server: mysql -u username -h mysql_server_ip -p Your remote server is now ready to accept connections. In an Ubuntu-based distribution type the following commands: sudo netfilter-persistent save It is necessary to save the changes made to the iptables rules. You would need to substitute it with the IP for your remote connection. To limit access to a specific IP address, use the following command instead: sudo iptables -A INPUT -p tcp -s 133.155.44.103 -dport 3306 -j ACCEPT Type the following command to open MySQL port 3306 to unrestricted traffic: sudo iptables -A INPUT -p tcp -dport 3306 -j ACCEPT The iptables utility is available on most Linux distributions by default. ![]() You have successfully opened port 3306 on your firewall. Sudo firewall-cmd -permanent -zone=mysqlrule -add-port=3306/tcp Sudo firewall-cmd -permanent -zone=mysqlrule -add-source=133.155.44.103 ![]() The name of the zone in our example is mysqlrule, and we used the IP address from our previous example 133.155.44.103: sudo firewall-cmd -new-zone=mysqlrule -permanent The firewalld management tool in CentOS uses zones to dictate what traffic is to be allowed.Ĭreate a new zone to set the rules for the MySQL server traffic. The system confirms that the rules were successfully updated. This IP limits MySQL connections to the local machine. The current default IP is set to 127.0.0.1. Scroll down to the bind-address line and change the IP address. You now have access to the MySQL server configuration file. If the MySQL configuration file is not it its default location try using the Linux find command to detect it. The location of the file may vary based on the distribution and version in use. Enter the following command in your command-line interface to access the MySQL server configuration file: sudo nano /etc/mysql//mysqld.cnf This example uses the nano text editor in Ubuntu 18.04. Use your preferred text editor to open the mysqld.cnf file. MySQL Server Remote ConnectionĪllowing connections to a remote MySQL server is set up in 3 steps:ģ. This article a must-read for anyone new to the process. In case you need assistance, we have prepared a comprehensive tutorial on how to use SSH to connect to a remote server. Note: If you do not have direct access to your MySQL server, you need to establish a secure SSH connection.
0 Comments
Leave a Reply. |