How to Install and Use MySQL Workbench

How to Install and Use MySQL Workbench
Hostman Team
Technical writer
MySQL
11.12.2023
Reading time: 7 min

To create a small database, you only need to write a short SQL script. It may be enough for learning or testing purposes. However, "real" databases, even in small projects, consist of dozens of tables and views and can be very difficult to work with using only SQL. It is hard to keep dozens of entities in your head without getting confused. That's when MySQL Workbench becomes a solution.

MySql Workbench is software for creating and designing databases using schemas and other visual tools. Today, we will show you what Workbench is, how to install it and connect to a cluster, how to create tables and models, and how to import and export data.

How to install MySQL WorkBench

To install MySQL Workbench, go to the official website and choose MySQL Enterprise Edition ->Workbench among the products. Or you can follow this link.

Image7 (1)

Click on "Download Now" and go to the page with options:

Image5 (2)

Here, select the operating system. In our case it is Windows 10 64-bit. After downloading and installing, the application is ready to work. 

How to create a database in MySQL Workbench

Before creating databases, let's define the entities and their attributes. Let's think of a database for an abstract hosting service with three tables: clients, servers, services.

The "Clients" will have the following columns: contract number as primary key, client's full name, service ID, and server ID.

The columns of the "Servers" table will display information about the servers themselves:

  • Server ID

  • IP address

  • Size of both RAM and permanent memory

  • Processor and video card parameters

In the "Services" table, we will specify their cost, name, and description.

Of course, our database will cover only a few of the theoretical hosting activities, but for introduction purposes, such a database will be enough.

To create a database, choose "File"->"New Model" in the main menu:

Image9 (1)

Now we can move on to creating the database.

Clients

In managed MySQL, table creation is implemented in several ways. We will create the "Clients" table without using the visual model. 

To do this, click "Add Table" in our database menu, after which a menu with settings will open.

  • Table Name;

  • Column Name is our attribute name;

  • Datatype is attribute type (number, time, letters, etc.);

  • PK, NN, UQ, etc. are column constraints. PK stands for primary key, NN stands for Not Null. The rest of the constraints are not important to us yet.

19d2f68f 6f50 482e 9bb3 2b81225ff88d

Servers

We create the "Servers" table in the same way. Here, we will focus on column attributes and their selection.

  • Server ID is a primary key with the Integer type. It is essentially a regular number; in practice, the server ID would be the server number;

  • IP is a set of up to 15 ASCII characters. We assume storage of an IPv4 address, for example 255.255.255.255.255; 

  • RAM, GB is the size of RAM expressed using the Float data type (floating point numbers);

  • Disk space, GB is similar to RAM;

  • Processor and GPU (video card) are where we'll place equipment names with a maximum length of 45 characters.

8be54a44 B772 405b 9559 56293058e976

Services

For a change, let's create "Services" using the visual functionality of MySQL Workbench. 

To do this, click on the "Add Diagram" button above the name of our database, "mydb."

88736c0c 4976 4ca9 Af80 Bdff7cbc51a9

Now click on the diagram that will appear, and you'll get to a new tab.

Bb1c960c 18c5 4f4e B4de C1b0797b4b9f

In the left menu of the "Catalog Tree" we can see the already created "Clients" and "Servers" tables. We can transfer them to the visual diagram:

F5d68b88 Ecee 49ac A55e Ec74b2686767

To add a new table to the diagram, select "Place a New Table" from the left menu or press "T". 

652d1de0 Ccf5 4bcb A4fa B61888d9aa88

By clicking twice on the window that appears, we go to the settings. Our "Services" table will contain the columns "Service ID", "Name", "Description" and "Price".

D2204ee4 Ecc1 4856 8b93 13eed33e5d39

Creating relations

The database we are creating is called a relational database. Tables do not exist in a vacuum and are connected to each other: a client rents a certain server or buys a certain service. It is necessary to trace this relationship in our database. 

In practice, it is realized with the help of the foreign key (FK) or foreign key column attribute. FK defines the set of possible column values in a row as those from another table. Suppose we have two tables: "Employees" and "Shifts". "Shifts" has columns "Time" and "Employee (FK)". So, in the column "Employee (FK)," we can put only the employee who is in the table "Employees".   

It is easy to create MySQL links. In order to link two tables, you need to define the type of link between them. Our tables will be linked, but they will not identify each other. In practice, this means that an FK is not a PK. Therefore, we will use the Non-Identifying relationship. Let's move directly to creating the relationships.

Clients-Servers

The linking column here will be the "Server ID". Can the same server be in several clients in our database? Yes, it can, but not at the same time. In order to create such a relationship, we select "Place a New 1:n Non-Identifying Relationship" in the left menu and click first on "Clients" (where the relation goes to) and then on "Servers" (where the relation comes from).

02e532e2 Ba36 4809 942b 41cfb10b7ec5

We have a new column in "Clients," but we would like to link the relation to an existing column. To do this, right-click on "Clients" and select "Edit Clients".

203cb732 17f3 4073 Bb3a Ec137bb50291

Then go to the "Foreign Keys" tab below. Here we can select the "Server ID" column as the "Foreign key" and then delete the resulting column. We perform the same procedure with the Clients-Services link and eventually have the following result:

D961d309 0bae 4bf1 8b5b 7324f8033bf4

Views

In the menu on the left, you might have noticed the "Views" section. Views are the display of the required data based on the available tables. For example, you may need information about clients and the cost of services they use. In this case, you will need a View. Let's try to create one. 

First, we need to fill the database with information. To do this, go to the table parameters and the "Inserts" tab. 

Ed46938f C220 40b6 Bd6e C9d2365f583f

Then fill in the "Servers" and "Clients" in the same way and add a View to the diagram using the "Place a New View" button or the "V" key.

5eb59988 Fed2 4689 81ea 6d4e0800ccfe

Double-click on "view1" and in the window that appears, write the SQL query that corresponds to our mapping.

CREATE VIEW `Full Name + Services` AS
SELECT Name, ServiceName, Price From Clients
JOIN Services ON Clients.service_id = Services.service_id;

E467987f D19f 4a4e B272 46728818be91

But to see the result of the query, we need to connect to the cluster and import our database there.

Connecting to a cluster

The first thing you need is a cluster. We already have one, so we will connect to it. To create a connection, you need to go to the main page and click the plus sign.

7d959594 1171 4e34 A440 21caa0f27498

In the window that appears, we will need the following settings:

  • Connection name is useful if there are several connections;

  • Hostname is our cluster IP address;

  • Port is the cluster port. The default is 3306;

  • Username is the name of your account;

  • Password (if available)

51111fb8 D432 4d9e Bb33 89a9af8fa395

Click "OK," and you will connect to the cluster.

Importing and exporting the database

To work with the database and execute SQL queries, you must first import it into the cluster. To do this, click on the "File" button on the diagram tab and select "Export"->"Forward Engineering SQL CREATE Script" in the pop-up menu.

4b31ecc5 Ecbf 466f 9571 5bff5e5d23ff

Remember to click the checkboxes in the corresponding fields to avoid losing the filled tables. The resulting script will allow us to import the database into the cluster.

Go back to the cluster tab, select "Data Import/Restore" in the "Management" menu.

164d216a 9918 4fd9 B57f 46beee6932e0

Here, we need to select the "Import from Self-Contained File" option, select the script, and click "Start Import".

How to dump a MySQL WorkBench database

To create a dump, you need to click "Data Export" in the "Management" section and select the required parameters.

9844bdc1 B56f 46c0 8107 32b96fdde49b

We will save the entire database: its logical structure and stored data. To complete the process, click "Start Export".

Conclusion

In this article, we have learned the program's basic functionality, created a small database and imported it to the server. Of course, we could only cover some of the features. But this should be enough to start creating your own MySQL databases from scratch and study this tool more deeply.

MySQL
11.12.2023
Reading time: 7 min

Similar

MySQL

How to Secure MySQL Server

MySQL is the best choice for relational database management, but its widespread use also makes it an appealing target for potential attackers. This tutorial outlines essential steps to fortify your MySQL server, protecting your data from unauthorized access and activities that are malicious. Prerequisites Prior to diving into security configurations, ensure the prerequisites mentioned below: MySQL is Installed and Running: Consider reading the official MySQL documentation if needed.  Access to Administrator Privileges: sudo or root access is required for many steps. Familiarity with MySQL Commands: Basic command-line knowledge will be helpful. Understanding Threat Landscape MySQL databases face various threats, including:   Unauthorized Logins: Weak credentials or misconfigurations can allow attackers to access your data.   SQL Injection Attacks: Unvalidated inputs in applications can expose your database to manipulation.   Insufficient Network Security: Open ports and weak firewalls leave the server vulnerable.   Proactively securing the database reduces the likelihood of such incidents. Step 1: Update the Server and MySQL As new updates provide new security features and also fixes bugs, it is highly important that your system stays updated because if will fix known vulnerabilities and leverage latest security enhancements.  Utilize the commands mentioned below to update both the operating system and MySQL. sudo apt updatesudo apt upgrade -y Verify the current MySQL version: mysql --version Keep an eye on official release notes to understand updates and security patches. Step 2: Secure Initial Configuration   The mysql_secure_installation script is an essential tool for strengthening the security of your MySQL server's. It streamlines sensitive tasks such as: Setting a root password Removing anonymous user accounts Disabling remote root logins Deleting the test database To run the script: sudo mysql_secure_installation Follow the prompts to implement the recommended security measures. Step 3: Configure User Authentication   Effective user management is essential for database security.   Use Unique Users for Applications Create separate users for each application, and assign only the permissions they need. For example:   CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';GRANT SELECT, INSERT ON app_database.* TO 'app_user'@'localhost';FLUSH PRIVILEGES; This ensures that a breach in one application doesn’t compromise the entire database.   Enable Authentication Plugins MySQL provides plugins like caching_sha2_password for secure authentication. To enable it:   ALTER USER 'root'@'localhost' IDENTIFIED WITH 'caching_sha2_password' BY 'SecurePassword!'; Step 4: Enforce Password Policies   If passwords are strong then the possibility of brute force attacks may not be of any help for the hackers. Install the password validation plugin if it isn’t already enabled: INSTALL PLUGIN validate_password SONAME 'validate_password.so'; Configure password strength rules in the MySQL configuration file: validate_password_policy=STRONGvalidate_password_length=12 These settings enforce strong, hard-to-guess passwords. Step 5: Restrict Remote Access   Unnecessary remote access exposes the database to risks. Disable Remote Root Access By default, MySQL allows root logins from remote systems. Disable this feature by editing the MySQL configuration file: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf Set the bind address to localhost: bind-address = 127.0.0.1 Restart the MySQL service:   sudo systemctl restart mysql Use Firewalls for Remote Connections If remote access is necessary then configure firewalls. This will only allow traffic from trusted IPs. sudo ufw allow from <trusted_ip> to any port 3306sudo ufw enable Step 6: Secure Data Transmission   Encryption will make sure that data transferred between the client and server is safe from eavesdropping. Enable SSL/TLS Certificates for MySQL are generated automatically at the time of installation.  Add the following lines to /etc/mysql/mysql.conf.d/mysqld.cnf:   [mysqld]ssl-ca=/var/lib/mysql/ca.pemssl-cert=/var/lib/mysql/server-cert.pemssl-key=/var/lib/mysql/server-key.pemrequire_secure_transport = ON Restart MySQL to apply changes. Use tools like OpenSSL to verify encrypted connections. Step 7: Audit and Monitor Database Activity   Monitoring user activity can help detect unauthorized actions. Enable Logs Activate general logs and error logs in the configuration file:   general_log=1log_error=/var/log/mysql/error.log Analyze Access Patterns Periodically review logs to identify anomalies or suspicious activity. Use automated tools like Percona Monitoring and Management for advanced analytics. Step 8: Protect Against SQL Injection   SQL injection is a common attack vector for web-based MySQL applications. Mitigate this risk by: Using Parameterized Queries: This prevents attackers from injecting malicious SQL commands Validating User Inputs: Sanitize and validate inputs at the application level. Implementing a Web Application Firewall (WAF): Tools like ModSecurity can block malicious queries. Step 9: Regular Backups and Disaster Recovery   Prepare for the unexpected by setting up automated backups. Full Database Backups Use mysqldump to create complete backups:   mysqldump -u root -p --all-databases > backup.sql Incremental Backups For large databases, consider incremental backups using tools like Percona XtraBackup. Step 10: Disable Unused Features   Reducing the database’s attack surface is crucial.   Disable Unused Plugins Run the following command to list active plugins:   SHOW PLUGINS; Unload any unnecessary plugins UNINSTALL PLUGIN plugin_name; Remove Sample Databases Delete test databases that come pre-installed with MySQL:   DROP DATABASE test; Step 11: Use Security Updates and Best Practices   Regularly update MySQL to address vulnerabilities and ensure you’re using the most secure version. Stay connected with the MySQL Community for updates, news, and security alerts. Advance Practices: Segmentation Isolate your database server from other services using network segmentation to reduce the risk of unauthorized access. Intrusion Detection Systems (IDS). Use tools like Snort to monitor for unusual activity and detect potential intrusions in real-time. Two-Factor Authentication (2FA). Enable 2FA for administrative accounts to add an extra layer of protection against unauthorized access. Conclusion Securing a MySQL server requires a comprehensive approach, combining careful configuration, continuous monitoring, and proactive management. If you utilize these practices then your database can be safeguarded very effectively, protecting sensitive data as well as mitigating the chance of unauthorized access. You must evaluate and update the security measures regularly so that you can stay prepared for emerging threats.
24 January 2025 · 6 min to read
Debian

How to Install MySQL on Debian

Installing MySQL on Debian effectively creates a robust and flexible database (DB) infrastructure that accommodates a wide range of applications as well as services. It is renowned for its scalability, dependability, and durability. By setting it, individuals experience the operations in an efficient manner and enhance the overall efficiency of DB infrastructure. This combination is especially beneficial for administrators, DB analysts, and industries that demand a dependable database solution for dealing with huge data. Additionally, MySQL's comprehensive guide and supporters help make it simpler to troubleshoot problems and enhance operations.  In this guide, we will demonstrate the thorough procedure for installing and configuring MySQL on Debian. How to Install MySQL on Debian The default repositories do not contain the MySQL database server package on Debian. To install it on a  Linux system follow the below instructions. We will download the recent version of the MySQL. Step 1: Download MySQL Package Let us obtain the MySQL repository information package, which is in the .deb format: wget https://dev.mysql.com/get/mysql-apt-config_0.8.30-1_all.deb Note: To authenticate the most updated release, go to the MySQL repository webpage. Step 2: MySQL Configuration Package Installation Then, employ the .deb file for initializing the installation via dpkg: sudo dpkg -i mysql-apt-config_0.8.30-1_all.deb Respond to the prompt. For instance, pick MySQL Server & Cluster and hit Enter for starting configurations: For picking a version such as (mysql-8.4-lts), scroll downward and hit OK for the next step: Step 3: Refresh the System Now, update the server's package indexes to implement the updated MySQL info: sudo apt update Step 4: MySQL Installation Debian's default manager makes sure to install MySQL in an easier manner. Installing the package with this command: sudo apt install mysql-server -y You will see the interface for setting the root account. Input a stronger password to secure the database. In the end, hit the Ok button: Check the version on the server via the --version utility: mysql --version Step 5: Managing the Services Now, you can enable the MySQL service to initialize automatically at boot time: sudo systemctl enable mysql Activate the service via the systemctl utility: sudo systemctl start mysql Check if the system service is operational by viewing its status: sudo systemctl status mysql Step 6: MySQL Secure Installation The key or password that the individual created at the initialising procedure is currently protecting the root DB user on the server. MySQL also includes other insecure defaults, such as remote access to test databases and the root database user on the server.  It is vital to secure the MySQL installation after it has been completed as well as disable all unsafe default settings. There is a security script that can assist us in this procedure. Run the script: sudo mysql_secure_installation To activate the VALIDATE PASSWORD component and guarantee stringent password procedures, type Y and hit Enter. Next, you will need to configure several security settings: Set the Root Password: Select a strong password and make sure that it is correct. Configure the password policy for the DB server. For instance, type 2 to permit only the strong passwords on the server and hit Enter. When required to modify the root password, input N; alternatively, input Y to modify the password. Eliminate Anonymous Users: It is advised to eliminate the accessibility of anonymous users. For this, input Y and Enter when prompted. Prevent Accessibility of Remote Root: It is a better practice to avoid remote root login for multiple security concerns. To prevent the root user from having a remote access, input Y and hit Enter. Delete the Test DB: For enhancing security, the test database, which is utilized for testing, can be deleted. To do so, input Y and hit Enter. Refreshing Privilege Tables: It guarantees that all modifications are implemented instantly. To implement the configuration and edit the privileges table, hit Enter. Step 7: Access MySQL Utilizing the mysql client utility, MySQL establishes the connection and provides access to the database server console.  Now, access the shell interface and run general statements on the DB server. Let’s input the root and the password created at the time of the safe installation procedure: sudo mysql -u root -p Step 8: Basic MySQL Operations The creation of a DB and a new user for your applications rather than utilizing the root is a better practice. To accomplish the task, employ the given instructions: Create a Database: First, create a database. For instance, hostmandb is created via the below command: CREATE DATABASE hostmandb; Display All Databases: List all databases to make sure hostmandb is created: SHOW DATABASES; Create of a New User: Create a user and assign a strong password. In our example, we set Qwer@1234 as a password for the user  minhal. Replace these values with your data. CREATE USER 'minhal'@'localhost' IDENTIFIED BY 'Qwer@1234'; Give Permissions to the User: Give complete access to the hostmandb to the new user: GRANT ALL PRIVILEGES ON hostmandb.* TO 'minhal'@'localhost'; Flush Privileges: To implement the modifications, refresh the table: FLUSH PRIVILEGES; Exit the Shell: For closing the interface, utilize the EXIT statement: EXIT; Access MySQL Console as the Particular User For the purpose of testing hostmandb access, log in to MySQL as the new user, in our case minhal. sudo mysql -u minhal -p It accesses the console after entering the minhal user password when prompted: For verification, display all DBs and confirm that the hostmandb is available: SHOW DATABASES; Step 9: Configuration for Remote Access Setting up the server for supporting remote accessibility is necessary if an individual is required to access MySQL remotely. Follow these steps: Access the mysql.cnf file and modify the particular file for MySQL: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf Look for the line with the bind-address and change it to: bind-address = 0.0.0.0 Reload the MySQL service: sudo systemctl restart mysql Permit the user to have remote access: sudo mysql -u root -p GRANT ALL PRIVILEGES ON hostmandb.* TO 'minhal'@'localhost';FLUSH PRIVILEGES;EXIT; Step 10: Firewall Configuration If you have a firewall activated, you need to open the MySQL port 3306 to traffic. Set up the firewall following the below steps: Allow traffic through MySQL: sudo ufw allow mysql Now, activate the UFW on the system: sudo ufw enable Reload the firewall: sudo ufw reload Step 11: Restore and Backup Maintaining regular backups is crucial to avoiding data loss. The mysqldump utility is provided by MySQL for backup creation. To achieve this, consider these instructions: Backup a Single Database: This command employs mysqldump to create the backup of the hostmandb as a hostmandb_backup.sql file: sudo mysqldump -u root -p hostmandb> hostmandb_backup.sql Backup All Databases: For creating a backup of all databases as a file named all_databases_backup.sql with root privileges, utilize mysqldump: sudo mysqldump -u root -p --all-databases > all_databases_backup.sql Restore a Particular Database: Now, restore the hostmandb from the backup file hostmandb_backup.sql: sudo mysql -u root -p hostmandb < hostmandb_backup.sql Step 12: Optimize MySQL Operations (Optional) Depending on the workload and server resources, you can adjust settings to guarantee peak performance. These instructions will help you maximize MySQL's speed: Adjust InnoDB Buffer Pool Size: Caches for data and indexes are kept in the InnoDB buffer pool. Expanding its size can enhance its functionality. Edit the MySQL configuration file: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf The below line should be added or changed: innodb_buffer_pool_size = 1G Its size should be adjusted according to the amount of memory on the server. Enable Query Cache: The query cache stores the outcome of SELECT queries. Enabling it can enhance operations for repetitive queries. Modify the .cnf file: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf Add or edit the below lines: query_cache_type = 1query_cache_size = 64M Optimize Table Structure: Frequently optimize your customers table in hostmandb to recover wasted space and boost efficiency: USE hostmandb;OPTIMIZE TABLE customers; Analyze Operations: DB operations can be tracked and analyzed with tools like MySQL Workbench and mysqltuner. Using the command below, install mysqltuner: sudo apt install mysqltuner Run mysqltuner to get performance recommendations: sudo mysqltuner Conclusion Installing a MySQL environment is important in today's digital world. By following this instruction, you'll be able to safely install and connect to your MySQL database. This strategy not only increases security but also improves remote database maintenance efficiency. It helps to prevent breaches and ensures the confidentiality of your data. This article has given thorough instructions for the installation of MySQL's database environment on Debian. It is suggested that MySQL servers should be regularly monitored and optimized to guarantee optimum performance and dependability. In addition, Hostman offers pre-configured and ready-to-use cloud databases, including cloud MySQL. 
14 January 2025 · 8 min to read
MySQL

Creating an SSH Tunnel for MySQL Remote Access

Maintaining a secure database environment is vital in today's digital age. It helps prevent breaches and ensure the confidentiality of your information. A highly effective process for enhancing MySQL connection security is by implementing an SSH tunnel for remote access. This approach establishes an encrypted tunnel between your device and the server, ensuring data remains secure. SSH Tunneling SSH tunneling, also referred to as SSH port forwarding, enables the secure transmission of data between networks. By establishing an encrypted SSH tunnel, data can be safely transferred without the risk of exposure to potential threats. It possesses several benefits: Security: Encrypts data, keeping it safe from being seen or intercepted by others. Bypassing Restrictions: Allows access to services and resources blocked by firewalls. Flexibility: Can handle all network traffic types, fitting many uses. Types of SSH Tunneling SSH tunneling is of three types: Local Port Forwarding: It lets you redirect a port from your local machine to a destination machine using a tunnel. This is the method used in our guide. For example: ssh -L 3307:localhost:3306 your_username@your_server_ip Remote Port Forwarding: It lets you redirect a port from a remote machine to your local machine. This is useful for accessing local services from a remote machine. For example: ssh -R 9090:localhost:80 your_username@your_server_ip Dynamic Port Forwarding: It lets you create a SOCKS proxy to dynamically forward traffic through an SSH tunnel. This is useful for secure web browsing or bypassing firewalls. For example: ssh -R 9090:localhost:80 your_username@your_server_ip Prerequisites Before beginning, ensure you have: SSH client (OpenSSH, or PuTTY for Windows) MySQL server info SSH into the MySQL host machine securely. Setting Up Remote Access Go through these essential steps to securely set up remote access to your MySQL server through SSH tunnel: Step 1: Facilitate Connectivity For remote access, tune it to listen on an external IP. This allows SQL access from localhost to all IPs. Here’s how to do it: Access MySQL Config File Using a text editor, access the config file. On Ubuntu, it's typically located at: sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf If the file isn't in its expected place, search for it with: sudo find / -name mysqld.cnf Edit bind-address Inside the file, find bind-address line, which is set to 127.0.0.1 by default, limiting server to local connections: Change the address to allow connections from all IP addresses by setting it to 0.0.0.0. Save changes by pressing Ctrl+X, Y to confirm, and Enter to exit. Restart MySQL Restart service to apply the updated settings: sudo systemctl restart mysql Step 2: Adjust Firewall By default, 3306 is the standard port in MySQL. To permit remote access, ensure this port is opened in your firewall settings. Tailor these steps to your specific firewall service. Open Port via UFW On Ubuntu, UFW is a pre-installed firewall utility. To allow traffic on 3306: sudo ufw allow from remote_ip to any port 3306 Substitute remote_ip with actual IP. Open Port via Firewalld On Red Hat-based and Fedora systems, Firewalld is the primary firewall tool. To open port 3306 for traffic, run these commands: sudo firewall-cmd --zone=public --add-service=mysql --permanentsudo firewall-cmd --reload The first command permanently allows MySQL traffic, and the second reloads the firewall to make the changes. Step 3: Open Your SSH Client Fire up your go-to SSH client. Opt for PuTTY on Windows, or the terminal if using macOS or Linux. Using Terminal (Linux or macOS) Implement this command: ssh -L 3307:localhost:3306 your_username@your_server_ip 3307: It's the local port your computer will listen to. localhost: It's a MySQL server address used by the SSH. It's where the service runs on the machine you're connecting to. 3306: The remote port where the server listens for incoming connections. username@server_ip: Your SSH login details. When required, verify the server's fingerprint. Confirm it matches by typing "yes" and pressing Enter.  Once confirmed, enter your SSH password if asked and press Enter for tunneling. After the tunnel is up, all traffic destined to local port 3307 will be forwarded to the remote machine in a secure fashion. Using PuTTY (Windows) Windows users can use the below-given instructions to perform tunneling: Launch PuTTY. From the left menu, direct to Connection > SSH > Tunnels. Input 3307 for Source port and localhost:3306 for the Destination field. Then hit Add. Navigate back to Session menu, enter server’s IP address and start the session using the Open button. Step 4: Connect to MySQL After setting up the tunnel, seamlessly link to the server through: sudo mysql -h localhost -P 3307 -u your_mysql_user -p Step 5: Verify the Connection Log into server and check if you can run queries: Additional Safeguards for Enhanced Security To further enhance the MySQL remote access security, consider the following: Implement Robust Passwords and Authentication Ensure using strong, unique passwords for both servers accounts. Implement key-based SSH authentication for added security. Here's how to set up SSH key authentication: Generate an SSH key pair via: ssh-keygen -t rsa -b 4096 -C "[email protected]" Copy the public key to the server via: ssh-copy-id your_username@your_server_ip Regularly Update Your Software Ensure that your server, client, and all associated software are consistently updated with the latest security patches and enhancements. This practice safeguards your system against known vulnerabilities and potential threats. Supervise and Audit Access Consistently examine access logs on both your MySQL and SSH server. Watch for any unusual activities or unauthorized attempts to gain access. Set up logging for both services: Check the SSH logs via: sudo tail /var/log/auth.log Enable and check MySQL logs by adding the below-given lines in the configuration file: [mysqld]general_log = 1general_log_file = /var/log/mysql/mysql-general.log You can view the general query log via: sudo cat /var/log/mysql/mysql-general.log To continuously monitor the log file in real time, use: sudo tail -f /var/log/mysql/mysql-general.log Implement IP Whitelisting Limit access to your MySQL by applying IP whitelisting. It ensures that connections are permitted only from specified IP addresses, thereby enhancing security: sudo ufw allow from your_trusted_ip to any port 3306 Replace your_trusted_ip with the IP address you trust. Troubleshooting Issues Here are a few common problems and solutions: Unable to Connect: Check SSH configuration and firewall rules. Ensure the SSH tunnel is correctly established and the server is reachable. Port Already in Use: Change the local forwarding port from 3307 to another available port. Authentication Errors: Verify your server's credentials. Ensure that the correct user permissions are set. MySQL Server Not Listening on Correct IP: Double-check the MySQL bind-address configuration and ensure the server is listening on the correct IP. Conclusion By adhering to this guide, you'll securely connect to your MySQL database via an SSH tunnel. This method not only boosts security but also enhances remote database management efficiency.  Regularly check your SSH tunnel setup to ensure a stable, secure connection. This practice ensures your data stays protected, providing peace of mind for seamless database operations. Hostman provides pre-configured and ready-to-use cloud databases, including cloud MySQL.
27 December 2024 · 6 min to read

Do you have questions,
comments, or concerns?

Our professionals are available to assist you at any moment,
whether you need help or are just unsure of where to start.
Email us
Hostman's Support