Setting Up MySQL Workbench with AWS RDS

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. It provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, and backup management.

In this guide, we will interact with relational databases inside the cloud (AWS in this scenario), specifically those residing in the AWS RDS service. Using MySQL Workbench, we will have a tool that visually displays tables inside the database.

---

Step 1: Installing MySQL Workbench

1. Open your browser and search for "SQL Workbench" on Google.
2. Click on SQL Workbench Download.
3. Under the Downloads tab, use the below link to download it:
   • Generic package for all systems without support for importing or exporting Excel or OpenOffice spreadsheets (SHA1)
4. In your local system, create a folder named "Big Data" and move the downloaded file into this folder.
5. Extract the file, go inside the extracted folder, and right-click on SQLWorkbench 64.
6. Under "Show More Options," create a shortcut on the desktop for quick access.

Note: We need Java 11 to be installed. Use the command java -version in the command prompt to confirm. If Java is not installed, refer to a YouTube video for installation and ensure the Java path is set in environment variables. https://youtu.be/R6MoDMASwag?si=NVU00sjl6QHuxsXN

Step 2: Connecting to a Database

1. Click on the SQL Workbench shortcut on your desktop.
2. Assign a name, e.g., "mysql" (refer to the screenshot for guidance).
3. Since we are connecting to a MySQL database, select the MySQL driver under the Driver section.
4. If this is your first time, you will be prompted to download the required libraries. Click Yes to proceed.
5. Click on Download Driver and select an older version, such as 8.0.28 from the available versions, then click OK.
6. Enter your database username and password (this information should be collected while creating the database in AWS RDS).
7. Under the URL section, update the hostname & port number (you can find this information inside the AWS RDS service).
8. Remove the name_of_database property if you're unsure what to enter.
9. Click on Test Connection to verify connectivity.
10. If successful, click OK.
11. If using SQL Workbench for the first time, go to Tools > Show DB Tree to view databases.
    
    
    

    

Step 3: Creating a New Database

To create a new database, use the following SQL command:

CREATE DATABASE <database_name>;

To execute the query, use Ctrl+Enter or click the Run button (top-right corner).

To use this database:

USE <database_name>;

---

Step 4: Creating Tables and Inserting Data

Creating the 'dept' Table

CREATE TABLE dept (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100),
    location VARCHAR(100)
);

Inserting Data into 'dept' Table

INSERT INTO dept (dept_id, dept_name, location) VALUES
(1, 'HR', 'New York'),
(2, 'Finance', 'Los Angeles'),
(3, 'Engineering', 'San Francisco'),
(4, 'Sales', 'Chicago'),
(5, 'Marketing', 'Boston'),
(6, 'Support', 'Seattle'),
(7, 'IT', 'Austin'),
(8, 'R&D', 'San Diego'),
(9, 'Operations', 'Houston'),
(10, 'Legal', 'Dallas');

Creating the 'emp' Table

CREATE TABLE emp (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    job_title VARCHAR(100),
    salary DECIMAL(10, 2),
    FOREIGN KEY (dept_id) REFERENCES dept(dept_id)
);

Inserting Data into 'emp' Table

INSERT INTO emp (emp_id, emp_name, dept_id, job_title, salary) VALUES
(1, 'John Doe', 1, 'Manager', 75000.00),
(2, 'Jane Smith', 2, 'Accountant', 65000.00),
(3, 'Bob Johnson', 3, 'Engineer', 85000.00),
(4, 'Alice Brown', 4, 'Sales Representative', 55000.00);

Creating the 'orders' Table

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    emp_id INT,
    order_date DATE,
    order_amount DECIMAL(10, 2),
    FOREIGN KEY (emp_id) REFERENCES emp(emp_id)
);

Inserting Data into 'orders' Table

INSERT INTO orders (order_id, emp_id, order_date, order_amount) VALUES
(1, 1, '2023-01-10', 1000.00),
(2, 2, '2023-01-15', 2000.00),
(3, 3, '2023-01-20', 1500.00),
(4, 4, '2023-02-10', 1200.00);

---

Step 5: Connecting MySQL Workbench with Databricks

Once MySQL Workbench is set up, we can use a Databricks account to create a Spark cluster and connect to the MySQL database to read and write data.

Using PySpark in Databricks to Read MySQL Data

jdbc_url = "jdbc:mysql://<rds-endpoint>:3306/<database-name>"
properties = {
    "user": "<username>",
    "password": "<password>",
    "driver": "com.mysql.cj.jdbc.Driver"
}
df = spark.read.jdbc(jdbc_url, "emp", properties=properties)
df.show()

Performing ETL in Spark

• Use PySpark to process data before writing back to MySQL.
• Schedule jobs in Databricks to automate data extraction and analysis.

---

Conclusion

By following these steps, we have successfully set up MySQL Workbench, connected it to an AWS RDS MySQL database, and performed basic database operations. Additionally, we explored Databricks integration, which allows us to leverage big data processing with Spark.

This setup will be useful for database management, data analytics, and ETL pipelines in cloud environments.

Happy Coding! 🚀