Cara Setup MySQL Database untuk Development

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

# Install MySQL Server
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', 'john@example.com');

-- 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.