🗃️ MySQL

MySQL

MySQL is a widely used relational database management system (RDBMS) that helps store and manage structured data efficiently. This guide will help you install MySQL Server on your system and set up a basic database.

Step 1: Download MySQL Installer

Visit the official MySQL Downloads page.
Select MySQL Community Version and choose the installer that matches your operating system (Windows, macOS, or Linux).
Download and run the installer.

Step 2: Install MySQL Server

On Windows:

Run the downloaded .msi file.
Choose the Custom installation type.
Select MySQL Server, Workbench, Shell.
Set up the root password and configure authentication settings.
Complete the installation and start the MySQL service.
Verify installation by opening Command Prompt and running:
```
mysql -u root -p
```

On macOS:

Install MySQL using Homebrew:
```
brew install mysql
```
Start the MySQL service:
```
brew services start mysql
```
Secure the installation:
```
mysql_secure_installation
```
Verify installation:
```
mysql -u root -p
```

On Linux (Ubuntu/Debian):

Update package lists and install MySQL Server:

sudo apt update
sudo apt install mysql-server

Secure the installation:
```
sudo mysql_secure_installation
```
Verify installation:
```
sudo systemctl status mysql
```

Step 3: Configure MySQL Server (Only for Linux)

Open the MySQL configuration file:
```
sudo nano /etc/mysql/my.cnf
```
Adjust settings such as port, max_connections, and bind-address if needed.
Restart MySQL Server to apply changes:
```
sudo systemctl restart mysql
```

Step 4: Create a Database and User

Log in to MySQL as root:
```
mysql -u root -p
```
Create a new database:
```
CREATE DATABASE mydatabase;
```

Create a new user and grant privileges:

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
GRANT ALL PRIVILEGES ON mydatabase.* TO 'myuser'@'localhost';
FLUSH PRIVILEGES;

Exit MySQL:
```
EXIT;
```

Step 5: Run an Example MySQL Query

Connect to MySQL as the new user:
```
mysql -u myuser -p
```
Select the database:
```
USE mydatabase;
```

Create a new table:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

Insert a record:

INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

Retrieve data:
```
SELECT * FROM users;
```

Conclusion

You have successfully installed MySQL Server, Workbench, created a database, and executed basic queries. You can now start developing applications using MySQL!

← Fullstack Tools

References

MySQL Workbench Documentation