Setting Up MySQL on Your Machine

1. From SQLite to a Client-Server Database

If you have been following along with this course, you already have hands-on experience with a real database. Back in Lesson 14, you added SQLite to your Spring Boot application and saw how a database stores data permanently — even when you stop and restart your server. SQLite served us perfectly for learning. It is simple, requires no installation, and stores everything in a single file.

Now it is time to level up. In this lesson, you are going to install and set up MySQL, one of the most widely used databases in the professional world. MySQL powers some of the biggest websites and applications you use every day — Facebook, Twitter, YouTube, Netflix, Airbnb, and thousands more. When companies here in the Lansing area post job listings for backend developers, MySQL experience is one of the most frequently requested skills.

So what makes MySQL different from SQLite? The big difference is architecture. SQLite is an embedded database — it runs inside your application as a library. There is no separate database program; your Spring Boot app reads and writes directly to a file. MySQL is a client-server database. It runs as its own program (a server) that is always on, waiting for connections. Your application (the client) connects to the MySQL server over a network and sends it SQL commands. The server processes those commands and sends results back.

Here is the encouraging part: the SQL you already know from Lesson 14 works in MySQL too. CREATE TABLE, INSERT, SELECT, UPDATE, DELETE — all of these commands are the same. You are not starting from scratch. You are taking skills you already have and applying them to a more powerful tool. Think of it like this: if SQLite was like cooking in your kitchen at home, MySQL is like working in a professional restaurant kitchen. The recipes (SQL) are the same, but the kitchen (infrastructure) is built to handle much more.

By the end of this lesson, you will have MySQL running on your computer, a graphical tool to manage it, and a database ready for the Resumator project that starts in Lesson 23. Let us get started.

2. Installing MySQL Server

The first thing we need to install is the MySQL Server itself. This is the core database engine — the program that actually stores your data, processes your SQL queries, and manages connections from clients. Once it is installed, it runs quietly in the background as a service (sometimes called a daemon), always ready to accept connections.

Choose your operating system below and run the command in your terminal (Command Prompt or PowerShell on Windows, Terminal on Mac or Linux):

Setting the Root Password

On Mac and Linux, the installation may not prompt you for a root password. You should secure your installation by running the built-in security script:

sudo mysql_secure_installation

This interactive script will walk you through several security settings. When it asks you to set a root password, choose something memorable and write it down. For the other questions (removing anonymous users, disabling remote root login, removing test databases), answering yes to all of them is the safest option for a learning environment.

On Windows, if you used the MySQL Installer (graphical version), you already set the root password during installation. If you used winget, you may need to run the MySQL Installer separately to configure the root password.

Verifying the Installation

Close your terminal and open a new one (this ensures your system picks up the newly installed commands). Then run:

mysql -u root -p

The -u root flag tells MySQL you want to connect as the root user (the administrator), and -p tells it to prompt you for a password. After you type your password and press Enter, you should see a prompt that looks like this:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.36 MySQL Community Server - GPL

mysql>

If you see that mysql> prompt, congratulations — MySQL is installed and running! Type exit; to leave the MySQL shell for now. We will come back to it soon.

Understanding the Three Pieces

3. Installing MySQL Workbench

While you can do everything from the command-line mysql client, having a graphical tool makes life much easier — especially when you are learning. MySQL Workbench is the official GUI for MySQL. It lets you write and run SQL queries in a nice editor with syntax highlighting, visually browse your tables and data, create and modify tables with a point-and-click interface, and manage database users and permissions.

Choose your operating system below to install Workbench:

Connecting Workbench to Your Local Server

Once Workbench is installed, open it. You should see a home screen with a section called "MySQL Connections." There may already be a connection listed called "Local instance 3306" or similar. If so, click on it. If not, create one:

1. Click the + button next to "MySQL Connections."
2. Give the connection a name like "Local MySQL".
3. Leave Hostname as 127.0.0.1 and Port as 3306 (these are the defaults).
4. Set Username to root.
5. Click "Test Connection" and enter your root password when prompted.
6. If the test succeeds, click OK to save the connection.

Now double-click your connection to open it. You will see a SQL editor where you can type and run queries. There is a lightning bolt icon (or you can press Ctrl+Enter / Cmd+Enter) to execute the current query. This is where we will do most of our work for the rest of the lesson.

4. Creating Your First Database

A single MySQL server can hold many databases. Each database is a separate container for tables, like separate filing cabinets in an office. Your first step is always to create a database for your project and then tell MySQL you want to use it.

Open Workbench (or the mysql command-line client) and run these two commands:

CREATE DATABASE codersfarm;
USE codersfarm;

The first command creates a new, empty database called codersfarm. The second command tells MySQL: "For all the commands I type from now on, work with the codersfarm database." You must run USE before creating tables or inserting data, otherwise MySQL will not know which database you are talking to.

You can verify your database was created by running:

SHOW DATABASES;

You should see codersfarm in the list, along with some system databases like information_schema, mysql, performance_schema, and sys. Those system databases are used internally by MySQL — leave them alone.

5. Creating a Practice Table

Now that you have a database, let us create a table inside it. We will make a students table to practice with. This table will track students enrolled in the Coders Farm program. Run the following SQL:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

That single command packs a lot of information into a small space. Let us break down every piece so you understand exactly what each constraint does and why it matters.

id INT AUTO_INCREMENT PRIMARY KEY

INT means this column stores whole numbers (integers). AUTO_INCREMENT tells MySQL to automatically assign the next number every time you insert a new row. The first student gets id 1, the second gets id 2, and so on. You never have to specify the id yourself — MySQL handles it for you. PRIMARY KEY means this column uniquely identifies each row. No two rows can have the same id, and this column can never be empty. Every table should have a primary key.

If you remember from Lesson 14, this is exactly what @Id and @GeneratedValue did in your Spring Boot entity class. Same concept, just expressed differently — here in raw SQL versus there in Java annotations.

name VARCHAR(100) NOT NULL

VARCHAR(100) means "variable-length character string, up to 100 characters." This is how MySQL stores text. The number in parentheses is the maximum length. If someone's name is "Alice" (5 characters), MySQL only stores 5 characters, not 100 — that is the "variable" part. NOT NULL means this column is required. If you try to insert a row without providing a name, MySQL will reject it with an error. This prevents incomplete data from slipping into your database.

email VARCHAR(150) NOT NULL UNIQUE

This column also stores text (up to 150 characters) and is required (NOT NULL). The new constraint here is UNIQUE. This tells MySQL that no two rows in the table can have the same email address. If you try to insert a second student with alice@example.com when that email already exists, MySQL will reject the insert. This is extremely important for data integrity — you do not want duplicate accounts.

enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

TIMESTAMP stores a date and time value. DEFAULT CURRENT_TIMESTAMP means that if you do not provide a value for this column when inserting a row, MySQL will automatically fill it in with the current date and time. This is perfect for tracking when something happened without making the user (or your code) specify it manually.

You can verify your table was created by running:

DESCRIBE students;

This shows you the structure of the table — all the column names, their data types, and their constraints. It is a quick way to remind yourself what a table looks like without looking at the original CREATE TABLE statement.

6. Basic Operations: INSERT, SELECT, UPDATE, DELETE

Now comes the fun part — putting data into your table and working with it. These four operations are called CRUD (Create, Read, Update, Delete), and they are the foundation of virtually every application in existence. Let us walk through each one.

INSERT — Adding Data

To add students to your table, use the INSERT INTO command. Notice that we do not specify id or enrolled_at — MySQL fills those in automatically:

INSERT INTO students (name, email) VALUES ('Alice Johnson', 'alice@example.com');
INSERT INTO students (name, email) VALUES ('Bob Smith', 'bob@example.com');
INSERT INTO students (name, email) VALUES ('Carol Lee', 'carol@example.com');
INSERT INTO students (name, email) VALUES ('David Park', 'david@example.com');
INSERT INTO students (name, email) VALUES ('Eva Martinez', 'eva@example.com');

In Workbench, you can highlight all five lines and run them at once (click the lightning bolt or press Ctrl+Enter / Cmd+Enter). You should see a message confirming that five rows were inserted.

SELECT — Reading Data

To see what is in your table, use SELECT. The asterisk (*) means "give me all columns":

-- Get all students
SELECT * FROM students;

-- Get only names and emails
SELECT name, email FROM students;

-- Find a specific student
SELECT * FROM students WHERE name = 'Alice Johnson';

-- Find students enrolled after a certain date
SELECT * FROM students WHERE enrolled_at > '2025-01-01';

In Workbench, the results appear in a grid below your SQL editor. You can resize columns, sort by clicking headers, and scroll through rows. This visual feedback is one of the biggest advantages of using Workbench over the command line.

UPDATE — Modifying Data

To change existing data, use UPDATE. Always include a WHERE clause to specify which row(s) to change. Without WHERE, the update applies to every row in the table — which is almost never what you want:

-- Update Bob's email address
UPDATE students SET email = 'bob.smith@example.com' WHERE id = 2;

-- Verify the change
SELECT * FROM students WHERE id = 2;

DELETE — Removing Data

To remove rows, use DELETE. Like UPDATE, always use a WHERE clause:

-- Delete a specific student
DELETE FROM students WHERE id = 5;

-- Verify the deletion
SELECT * FROM students;

You should now see four students instead of five. Notice that if you insert a new student after deleting one, the new student will get id 6, not 5. MySQL does not recycle auto-increment values. This is by design — it prevents confusion and ensures that IDs are always unique, even if rows are deleted.

7. Creating a Dedicated User

So far, you have been connecting to MySQL as the root user. The root user is the administrator — it has unlimited power over every database on the server. It can create and delete databases, create and delete users, read any data, and even shut down the server. Using root for your application is like using a master key to open your bedroom door — it works, but it is far more power than you need, and if someone steals that key, they have access to everything.

The professional practice is to create a dedicated user for each application, and give that user access only to the specific database it needs. This way, even if your application is compromised, the attacker only has access to one database — not your entire MySQL server.

Run these commands (make sure you are connected as root):

CREATE USER 'codersfarm_app'@'localhost' IDENTIFIED BY 'your_password_here';
GRANT ALL PRIVILEGES ON codersfarm.* TO 'codersfarm_app'@'localhost';
FLUSH PRIVILEGES;

Let us break down each command:

• CREATE USER 'codersfarm_app'@'localhost' — Creates a new MySQL user called codersfarm_app. The @'localhost' part means this user can only connect from the same machine where MySQL is running. This is a security measure — nobody can use this account to connect from a remote computer.
• IDENTIFIED BY 'your_password_here' — Sets the password for this user. Replace your_password_here with an actual password that you will remember. In a real production environment, you would use a strong, randomly generated password.
• GRANT ALL PRIVILEGES ON codersfarm.* — Gives this user full access to every table in the codersfarm database (the .* means "all tables"). But notice: it only grants access to the codersfarm database. This user cannot touch any other database on the server.
• FLUSH PRIVILEGES — Tells MySQL to reload the privilege tables so the changes take effect immediately.

You can verify the new user works by opening a new connection in Workbench (or a new terminal session) and logging in as codersfarm_app:

mysql -u codersfarm_app -p

Enter the password you chose, and try running USE codersfarm; followed by SELECT * FROM students;. It should work. Now try USE mysql; — you should get an access denied error. That is exactly what we want. The codersfarm_app user can only work with the codersfarm database.

8. Spring Boot Connection Preview

In the upcoming Resumator project (Lessons 23–28), you will build a full Spring Boot application that connects to MySQL. Let us preview what that connection configuration looks like and compare it to the SQLite setup you used in Lesson 14.

Remember the application.properties file from Lesson 14? Here is what the SQLite configuration looked like:

# SQLite configuration (from Lesson 14)
spring.datasource.url=jdbc:sqlite:contacts.db
spring.datasource.driver-class-name=org.sqlite.JDBC
spring.jpa.database-platform=org.hibernate.community.dialect.SQLiteDialect
spring.jpa.hibernate.ddl-auto=update

And here is what the MySQL configuration looks like:

# MySQL configuration (for the Resumator project)
spring.datasource.url=jdbc:mysql://localhost:3306/codersfarm
spring.datasource.username=codersfarm_app
spring.datasource.password=your_password_here
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
spring.jpa.hibernate.ddl-auto=update

Notice the differences:

The biggest change is the URL. SQLite's URL points to a file (contacts.db). MySQL's URL points to a network address (localhost:3306) and a database name (codersfarm). This is the client-server difference in action — SQLite talks to a file, MySQL talks to a server over a network connection.

MySQL also requires a username and password, which SQLite did not. That is because MySQL is a multi-user system — it needs to know who is connecting and whether they have permission to access the requested database.

You will also need to add the MySQL JDBC driver to your pom.xml. Here is the dependency you will use in the Resumator project:

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

This replaces the sqlite-jdbc and hibernate-community-dialects dependencies from Lesson 14. Spring Boot already knows how to work with MySQL out of the box, so you do not need a special dialect library — the MySQL dialect is built into Hibernate.

Do not worry about memorizing all of this right now. When we start the Resumator project in Lesson 23, we will walk through every step of setting up the Spring Boot connection to MySQL. The point of this preview is so that nothing feels unfamiliar when we get there.

Knowledge Check

Lesson Summary

You have accomplished a lot in this lesson. Let us recap everything you did:

• Installed MySQL Server — a production-grade, client-server database that runs as a background service on your machine.
• Installed MySQL Workbench — a graphical tool for writing SQL, browsing data, and managing your databases visually.
• Created your first database (codersfarm) and a practice table (students) with proper constraints like AUTO_INCREMENT, NOT NULL, UNIQUE, and DEFAULT.
• Practiced CRUD operations — INSERT, SELECT, UPDATE, and DELETE — the four fundamental database operations.
• Created a dedicated application user (codersfarm_app) following the security principle of least privilege.
• Previewed the Spring Boot connection to MySQL and compared it side-by-side with the SQLite configuration from Lesson 14.