MySQL adalah database management system yang paling populer. Mari pelajari cara setup untuk development.

Install MySQL di Ubuntu

Via APT

# Update package index
sudo apt update
sudo apt install mysql-server
Start MySQL service
sudo systemctl start mysql
sudo systemctl enable mysql

Secure Installation

# Run security script
sudo mysql_secure_installation
Follow prompts:
- Set root password
- Remove anonymous users
- Disallow root login remotely
- Remove test database

Basic MySQL Commands

Login to MySQL

# Login as root
sudo mysql -u root -p
Enter password when prompted

Database Operations

-- Show databases
SHOW DATABASES;
-- Create database
CREATE DATABASE myapp;
-- Use database
USE myapp;
-- Show tables
SHOW TABLES;

Create User

-- Create user
CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
-- Grant privileges
GRANT ALL PRIVILEGES ON myapp.* TO 'myuser'@'localhost';
-- Apply changes
FLUSH PRIVILEGES;

Table Operations

Create Table

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

CRUD Operations

-- Insert data
INSERT INTO users (name, email) VALUES ('John Doe', '[email protected]');
-- Read data
SELECT 
 FROM users;
SELECT  FROM users WHERE id = 1;
-- Update data
UPDATE users SET name = 'Jane Doe' WHERE id = 1;
-- Delete data
DELETE FROM users WHERE id = 1;

Connect from Node.js

Install mysql2

npm install mysql2

Basic Connection

const mysql = require("mysql2");
const connection = mysql.createConnection({
host: "localhost",
user: "myuser",
password: "mypassword",
database: "myapp",
});
connection.connect((err) => {
if (err) throw err;
console.log("Connected to MySQL!");
});

Using Connection Pool

const pool = mysql.createPool({
  host: "localhost",
  user: "myuser",
  password: "mypassword",
  database: "myapp",
  waitForConnections: true,
  connectionLimit: 10,
});
// Query with promise
const [rows] = await pool.promise().query("SELECT * FROM users");

Backup and Restore

Backup Database

# Export database
mysqldump -u root -p myapp > backup.sql
Export with data
mysqldump -u root -p --databases myapp > backup.sql

Restore Database

# Import database
mysql -u root -p myapp < backup.sql

Useful Tools

MySQL Workbench

# Install on Ubuntu
sudo apt install mysql-workbench

phpMyAdmin

# Install phpMyAdmin
sudo apt install phpmyadmin

Kesimpulan

MySQL setup yang proper adalah foundation untuk reliable database operations dalam web development.