ABDURROZAK
HOME ABOUT ME MICROSITE KONTAK PERSEMBAHAN HELP
ABDURROZAK.MY.ID // DEBIAN LINUX SERVER FUNDAMENTAL

DATABASE SERVERFUNDAMENTAL

Volume 12 dari seri Debian Linux Server Fundamental. Panduan komprehensif dasar database server di Debian dengan MariaDB. Setiap command dan script dijelaskan detail dengan penjelasan per baris. Dari konsep database server hingga security dasar database.

VOLUME12 / 17
SUB-BAB7
BACA75 MENIT
DATABASE100%
ABDUR ROZAK, S.Kom. Web Developer & Network Educator - abdurrozak.my.id
VOLUME 12 / 17

DASAR DATABASE SERVER DI DEBIAN

Panduan lengkap Database Server Fundamental di Debian Linux dengan MariaDB. 7 sub-bab mencakup konsep database server, instalasi MariaDB, user database, database dan table, basic SQL, backup dan restore database, hingga security dasar database. Setiap command dan script disertai penjelasan detail per baris.
SUB-BAB7 LEVELDatabase WAKTU75 MENIT
01
SUB-BAB 01 KONSEP

KONSEP DATABASE SERVER

Apa Itu Database?

Database adalah kumpulan data yang terorganisir dan terstruktur sehingga mudah diakses, dikelola, dan diperbarui. Database menyimpan data dalam bentuk tabel yang terdiri dari baris (records/rows) dan kolom (fields/columns).

Apa Itu Database Server?

Database server adalah software yang menyediakan layanan untuk menyimpan, mengelola, dan mengakses data dalam database. Database server menerima query dari client, memproses query tersebut, dan mengembalikan hasilnya ke client.

Perbandingan Database Server

DATABASETYPELICENSEUSE CASE
MariaDBRelational (RDBMS)Open Source (GPL)Default di Debian, kompatibel MySQL
MySQLRelational (RDBMS)Open Source/CommercialPopuler, banyak digunakan
PostgreSQLRelational (RDBMS)Open SourceAdvanced features, enterprise
MongoDBNoSQL (Document)Open Source/CommercialFlexible schema, JSON
SQLiteRelational (Embedded)Open SourceEmbedded, single file

Arsitektur Database

KOMPONENDESKRIPSICONTOH
DatabaseKumpulan tabelmydb, testdb
TableKumpulan records dengan struktur yang samausers, products, orders
Record/RowSatu baris data dalam tabelSatu user, satu product
Field/ColumnKolom dalam tabelid, name, email, created_at
Primary KeyKolom unik untuk identifikasi recordid
Foreign KeyKolom yang merujuk ke primary key tabel lainuser_id di tabel orders

Contoh Struktur Database

Database Structure Example
# Database: mydb # Table: users +----+----------+------------------+---------------------+ | id | name | email | created_at | +----+----------+------------------+---------------------+ | 1 | John | john@example.com | 2026-09-07 10:00:00 | | 2 | Jane | jane@example.com | 2026-09-07 10:05:00 | +----+----------+------------------+---------------------+ # Table: orders +----+---------+---------+-------+---------------------+ | id | user_id | product | price | created_at | +----+---------+---------+-------+---------------------+ | 1 | 1 | Laptop | 1000 | 2026-09-07 10:30:00 | | 2 | 2 | Phone | 500 | 2026-09-07 11:00:00 | +----+---------+---------+-------+---------------------+

Kenapa MariaDB?

DEFAULT DI DEBIAN
MariaDB adalah default database server di Debian, mudah diinstall dan dikonfigurasi
KOMPATIBEL MYSQL
Kompatibel dengan MySQL, aplikasi MySQL bisa langsung jalan di MariaDB
OPEN SOURCE
Open source dengan lisensi GPL, gratis untuk digunakan
KOMUNITAS BESAR
Komunitas besar, banyak dokumentasi dan tutorial
TIPS: MariaDB adalah fork dari MySQL yang dibuat oleh founder asli MySQL. MariaDB kompatibel dengan MySQL dan memiliki fitur tambahan yang tidak ada di MySQL.
KOMPETENSI SUB-BAB 01
  • Memahami konsep database dan database server
  • Memahami perbandingan database server
  • Memahami struktur database (database, table, record, field)
  • Memahami kenapa MariaDB dipilih untuk Debian
02
SUB-BAB 02 INSTALASI

INSTALASI MARIADB

Instalasi MariaDB

bash
# Update package list admin@server:~$ sudo apt update Hit:1 http://deb.debian.org/debian bookworm InRelease Reading package lists... Done # Install MariaDB server admin@server:~$ sudo apt install mariadb-server Reading package lists... Done Building dependency tree... Done The following additional packages will be installed: mariadb-client mariadb-common mariadb-server-core The following NEW packages will be installed: mariadb-client mariadb-common mariadb-server mariadb-server-core 0 upgraded, 4 newly installed, 0 to remove and 0 not upgraded. Need to get 15.6 MB of archives. After this operation, 165 MB of additional disk space will be used. Do you want to continue? [Y/n] Y # Start MariaDB service admin@server:~$ sudo systemctl start mariadb # Enable MariaDB start on boot admin@server:~$ sudo systemctl enable mariadb Created symlink /etc/systemd/system/mysql.service → /lib/systemd/system/mariadb.service. Created symlink /etc/systemd/system/mysqld.service → /lib/systemd/system/mariadb.service. Created symlink /etc/systemd/system/multi-user.target.wants/mariadb.service → /lib/systemd/system/mariadb.service. # Cek status MariaDB admin@server:~$ sudo systemctl status mariadb ● mariadb.service - MariaDB 10.11.6 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; preset: enabled) Active: active (running) since Mon 2026-09-07 10:30:15 WIB; 5s ago Docs: man:mariadbd(8) https://mariadb.com/kb/en/library/systemd/ Process: 1234 ExecStartPre=/usr/bin/install -m 755 -d /var/run/mysqld (code=exited, status=0/SUCCESS) Process: 1235 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS) Process: 1236 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= || VAR=`cd /usr/bin/..; /usr/bin/galera_recovery`; [ $? -eq 0 ] && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS) Process: 1237 ExecStart=/usr/sbin/mariadbd $MYSQLD_OPTS $_WSREP_START_POSITION (code=exited, status=0/SUCCESS) Main PID: 1238 (mariadbd) Status: "Taking your SQL requests now..." Tasks: 15 (limit: 2340) Memory: 123.4M CPU: 1.234s CGroup: /system.slice/mariadb.service └─1238 /usr/sbin/mariadbd # Cek versi MariaDB admin@server:~$ mariadb --version mariadb Ver 10.11.6-MariaDB-0+deb12u1 for debian-linux-gnu on x86_64 (Debian 12) # Cek status service admin@server:~$ sudo systemctl status mariadb # Restart MariaDB admin@server:~$ sudo systemctl restart mariadb # Stop MariaDB admin@server:~$ sudo systemctl stop mariadb

Secure MariaDB Installation

bash
# Jalankan secure installation admin@server:~$ sudo mariadb-secure-installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the password for the 'root' user. Have you set a password for the root user? If you have set a password, and you want to change it, enter it now. If you have not set a password, just press ENTER. Enter current password for root (enter for none): [ENTER] OK, successfully used password, moving on... Setting the root password or using unix sockets ensures that nobody can log into the MariaDB root user without proper authorization. Set root password? [Y/n] Y New password: [ENTER PASSWORD] Re-enter new password: [ENTER PASSWORD] Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] Y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] Y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] Y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] Y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB!
TIPS: Selalu jalankan mariadb-secure-installation setelah install MariaDB untuk mengamankan instalasi. Ini akan set root password, remove anonymous users, disallow root login remotely, dan remove test database.
KOMPETENSI SUB-BAB 02
  • Mampu install MariaDB server
  • Mampu start/stop/restart MariaDB service
  • Mampu enable MariaDB start on boot
  • Mampu jalankan mariadb-secure-installation
03
SUB-BAB 03 USER

USER DATABASE

Login ke MariaDB

bash
# Login sebagai root (dengan sudo) admin@server:~$ sudo mariadb Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 12 Server version: 10.11.6-MariaDB-0+deb12u1 Debian 12 Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> # Login sebagai root (dengan password) admin@server:~$ mariadb -u root -p Enter password: [ENTER PASSWORD] # Login sebagai user tertentu admin@server:~$ mariadb -u username -p Enter password: [ENTER PASSWORD] # Login dengan host tertentu admin@server:~$ mariadb -u username -h localhost -p # Keluar dari MariaDB MariaDB [(none)]> exit Bye

Membuat User Database

MariaDB SQL
# Login sebagai root admin@server:~$ sudo mariadb # Buat user baru MariaDB [(none)]> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.001 sec) # Buat user dengan host tertentu MariaDB [(none)]> CREATE USER 'username'@'192.168.1.%' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.001 sec) # Buat user dengan password yang kuat MariaDB [(none)]> CREATE USER 'username'@'localhost' IDENTIFIED BY 'P@ssw0rd!2026'; Query OK, 0 rows affected (0.001 sec) # Lihat semua user MariaDB [(none)]> SELECT User, Host FROM mysql.user; +-------------+-----------+ | User | Host | +-------------+-----------+ | mariadb.sys | localhost | | mysql | localhost | | root | localhost | | username | localhost | +-------------+-----------+ 4 rows in set (0.001 sec) # Lihat detail user MariaDB [(none)]> SELECT * FROM mysql.user WHERE User='username';

Mengubah Password User

MariaDB SQL
# Ubah password user MariaDB [(none)]> ALTER USER 'username'@'localhost' IDENTIFIED BY 'NewP@ssw0rd!'; Query OK, 0 rows affected (0.001 sec) # Atau gunakan SET PASSWORD MariaDB [(none)]> SET PASSWORD FOR 'username'@'localhost' = PASSWORD('NewP@ssw0rd!'); Query OK, 0 rows affected (0.001 sec) # Reload privilege tables MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.001 sec)

Menghapus User

MariaDB SQL
# Hapus user MariaDB [(none)]> DROP USER 'username'@'localhost'; Query OK, 0 rows affected (0.001 sec) # Hapus user dengan host tertentu MariaDB [(none)]> DROP USER 'username'@'192.168.1.%'; Query OK, 0 rows affected (0.001 sec)

Grant Privileges

MariaDB SQL
# Grant semua privileges ke user untuk database tertentu MariaDB [(none)]> GRANT ALL PRIVILEGES ON mydb.* TO 'username'@'localhost'; Query OK, 0 rows affected (0.001 sec) # Grant semua privileges ke user untuk semua database MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost'; Query OK, 0 rows affected (0.001 sec) # Grant privilege tertentu MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE ON mydb.* TO 'username'@'localhost'; Query OK, 0 rows affected (0.001 sec) # Grant privilege untuk table tertentu MariaDB [(none)]> GRANT SELECT, INSERT ON mydb.users TO 'username'@'localhost'; Query OK, 0 rows affected (0.001 sec) # Revoke privileges MariaDB [(none)]> REVOKE INSERT, UPDATE ON mydb.* FROM 'username'@'localhost'; Query OK, 0 rows affected (0.001 sec) # Lihat privileges user MariaDB [(none)]> SHOW GRANTS FOR 'username'@'localhost'; +------------------------------------------------------------------------+ | Grants for username@localhost | +------------------------------------------------------------------------+ | GRANT SELECT ON `mydb`.* TO `username`@`localhost` | +------------------------------------------------------------------------+ 1 row in set (0.001 sec) # Reload privilege tables MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.001 sec)
TIPS: Selalu gunakan prinsip least privilege - berikan hanya privilege yang dibutuhkan. Jangan berikan ALL PRIVILEGES kecuali benar-benar dibutuhkan.
KOMPETENSI SUB-BAB 03
  • Mampu login ke MariaDB
  • Mampu membuat user database
  • Mampu mengubah password user
  • Mampu menghapus user
  • Mampu grant dan revoke privileges
04
SUB-BAB 04 DATABASE & TABLE

DATABASE DAN TABLE

Membuat Database

MariaDB SQL
# Login sebagai root admin@server:~$ sudo mariadb # Buat database MariaDB [(none)]> CREATE DATABASE mydb; Query OK, 1 row affected (0.001 sec) # Buat database dengan character set MariaDB [(none)]> CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Query OK, 1 row affected (0.001 sec) # Lihat semua database MariaDB [(none)]> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mydb | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.001 sec) # Gunakan database MariaDB [(none)]> USE mydb; Database changed # Atau gunakan database tanpa USE admin@server:~$ sudo mariadb mydb # Hapus database MariaDB [(none)]> DROP DATABASE mydb; Query OK, 0 rows affected (0.001 sec) # Hapus database jika ada MariaDB [(none)]> DROP DATABASE IF EXISTS mydb; Query OK, 0 rows affected (0.001 sec)

Membuat Table

MariaDB SQL
# Gunakan database MariaDB [(none)]> USE mydb; Database changed # Buat table users MariaDB [mydb]> 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 ); Query OK, 0 rows affected (0.002 sec) # Buat table dengan foreign key MariaDB [mydb]> CREATE TABLE orders ( id INT AUTO_INCREMENT PRIMARY KEY, user_id INT, product VARCHAR(100) NOT NULL, price DECIMAL(10,2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ); Query OK, 0 rows affected (0.003 sec) # Lihat semua table MariaDB [mydb]> SHOW TABLES; +-----------------+ | Tables_in_mydb | +-----------------+ | orders | | users | +-----------------+ 2 rows in set (0.001 sec) # Lihat struktur table MariaDB [mydb]> DESCRIBE users; +------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------------------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | email | varchar(100) | NO | UNI | NULL | | | created_at | timestamp | YES | | current_timestamp() | | +------------+--------------+------+-----+---------------------+----------------+ 4 rows in set (0.001 sec) # Atau gunakan DESCRIBE MariaDB [mydb]> DESC users; # Lihat struktur table dengan SHOW CREATE TABLE MariaDB [mydb]> SHOW CREATE TABLE users; # Hapus table MariaDB [mydb]> DROP TABLE orders; Query OK, 0 rows affected (0.002 sec) # Hapus table jika ada MariaDB [mydb]> DROP TABLE IF EXISTS orders; Query OK, 0 rows affected (0.001 sec) # Rename table MariaDB [mydb]> RENAME TABLE users TO users_backup; Query OK, 0 rows affected (0.001 sec) # Atau gunakan ALTER TABLE MariaDB [mydb]> ALTER TABLE users_backup RENAME TO users; Query OK, 0 rows affected (0.001 sec)

Data Types

DATA TYPEDESKRIPSICONTOH
INTInteger (bilangan bulat)1, 100, -50
VARCHAR(n)String dengan panjang maksimal nVARCHAR(100)
TEXTString panjang (maks 65535 karakter)TEXT
DECIMAL(p,s)Decimal dengan precision p dan scale sDECIMAL(10,2)
DATETanggal (YYYY-MM-DD)2026-09-07
DATETIMETanggal dan waktu2026-09-07 10:30:00
TIMESTAMPTimestamp (auto update)TIMESTAMP
BOOLEANBoolean (TRUE/FALSE)TRUE, FALSE
ENUMEnumerasi (pilihan)ENUM('active','inactive')
TIPS: Gunakan VARCHAR untuk string dengan panjang bervariasi, TEXT untuk string panjang. Gunakan DECIMAL untuk nilai uang, jangan gunakan FLOAT atau DOUBLE karena bisa ada masalah presisi.
KOMPETENSI SUB-BAB 04
  • Mampu membuat database
  • Mampu membuat table dengan berbagai data type
  • Mampu melihat struktur table
  • Mampu menghapus dan rename table
05
SUB-BAB 05 BASIC SQL

BASIC SQL

INSERT - Menambahkan Data

MariaDB SQL
# Gunakan database MariaDB [(none)]> USE mydb; Database changed # Insert satu record MariaDB [mydb]> INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com'); Query OK, 1 row affected (0.001 sec) # Insert multiple records MariaDB [mydb]> INSERT INTO users (name, email) VALUES ('Jane Doe', 'jane@example.com'), ('Bob Smith', 'bob@example.com'), ('Alice Johnson', 'alice@example.com'); Query OK, 3 rows affected (0.002 sec) Records: 3 Duplicates: 0 Warnings: 0 # Insert dengan semua kolom MariaDB [mydb]> INSERT INTO users (id, name, email, created_at) VALUES (NULL, 'Charlie Brown', 'charlie@example.com', NOW()); Query OK, 1 row affected (0.001 sec) # Lihat data yang sudah di-insert MariaDB [mydb]> SELECT * FROM users; +----+---------------+---------------------+---------------------+ | id | name | email | created_at | +----+---------------+---------------------+---------------------+ | 1 | John Doe | john@example.com | 2026-09-07 10:30:00 | | 2 | Jane Doe | jane@example.com | 2026-09-07 10:31:00 | | 3 | Bob Smith | bob@example.com | 2026-09-07 10:32:00 | | 4 | Alice Johnson | alice@example.com | 2026-09-07 10:33:00 | | 5 | Charlie Brown | charlie@example.com | 2026-09-07 10:34:00 | +----+---------------+---------------------+---------------------+ 5 rows in set (0.001 sec)

SELECT - Mengambil Data

MariaDB SQL
# Select semua kolom MariaDB [mydb]> SELECT * FROM users; # Select kolom tertentu MariaDB [mydb]> SELECT name, email FROM users; # Select dengan WHERE MariaDB [mydb]> SELECT * FROM users WHERE id = 1; # Select dengan WHERE multiple conditions MariaDB [mydb]> SELECT * FROM users WHERE id > 2 AND name LIKE '%Doe%'; # Select dengan ORDER BY MariaDB [mydb]> SELECT * FROM users ORDER BY name ASC; # Select dengan LIMIT MariaDB [mydb]> SELECT * FROM users LIMIT 5; # Select dengan LIMIT dan OFFSET MariaDB [mydb]> SELECT * FROM users LIMIT 5 OFFSET 5; # Select dengan COUNT MariaDB [mydb]> SELECT COUNT(*) FROM users; +----------+ | COUNT(*) | +----------+ | 5 | +----------+ # Select dengan COUNT dan WHERE MariaDB [mydb]> SELECT COUNT(*) FROM users WHERE name LIKE '%Doe%'; # Select dengan DISTINCT MariaDB [mydb]> SELECT DISTINCT name FROM users; # Select dengan GROUP BY MariaDB [mydb]> SELECT name, COUNT(*) as count FROM users GROUP BY name; # Select dengan JOIN MariaDB [mydb]> SELECT users.name, orders.product, orders.price FROM users JOIN orders ON users.id = orders.user_id; # Select dengan LEFT JOIN MariaDB [mydb]> SELECT users.name, orders.product FROM users LEFT JOIN orders ON users.id = orders.user_id; # Select dengan aggregate functions MariaDB [mydb]> SELECT COUNT(*) as total_orders, SUM(price) as total_revenue, AVG(price) as avg_price, MIN(price) as min_price, MAX(price) as max_price FROM orders;

UPDATE - Mengubah Data

MariaDB SQL
# Update satu record MariaDB [mydb]> UPDATE users SET name = 'John Smith' WHERE id = 1; Query OK, 1 row affected (0.001 sec) Rows matched: 1 Changed: 1 Warnings: 0 # Update multiple records MariaDB [mydb]> UPDATE users SET name = 'Updated Name' WHERE id > 3; Query OK, 2 rows affected (0.001 sec) Rows matched: 2 Changed: 2 Warnings: 0 # Update multiple columns MariaDB [mydb]> UPDATE users SET name = 'John Doe', email = 'john.doe@example.com' WHERE id = 1; Query OK, 1 row affected (0.001 sec) # Update dengan WHERE multiple conditions MariaDB [mydb]> UPDATE users SET name = 'Updated' WHERE id > 2 AND name LIKE '%Doe%'; Query OK, 1 row affected (0.001 sec)

DELETE - Menghapus Data

MariaDB SQL
# Delete satu record MariaDB [mydb]> DELETE FROM users WHERE id = 1; Query OK, 1 row affected (0.001 sec) # Delete multiple records MariaDB [mydb]> DELETE FROM users WHERE id > 3; Query OK, 2 rows affected (0.001 sec) # Delete dengan WHERE multiple conditions MariaDB [mydb]> DELETE FROM users WHERE id > 2 AND name LIKE '%Doe%'; Query OK, 1 row affected (0.001 sec) # Delete semua records (hati-hati!) MariaDB [mydb]> DELETE FROM users; Query OK, 5 rows affected (0.001 sec) # Truncate table (lebih cepat dari DELETE) MariaDB [mydb]> TRUNCATE TABLE users; Query OK, 0 rows affected (0.001 sec)
PERINGATAN: Hati-hati dengan DELETE tanpa WHERE! Ini akan menghapus semua records dalam table. Selalu gunakan WHERE untuk membatasi records yang dihapus.
KOMPETENSI SUB-BAB 05
  • Mampu INSERT data ke table
  • Mampu SELECT data dari table dengan berbagai kondisi
  • Mampu UPDATE data dalam table
  • Mampu DELETE data dari table
06
SUB-BAB 06 BACKUP & RESTORE

BACKUP DAN RESTORE

Backup Database

bash
# Backup satu database admin@server:~$ mysqldump -u root -p mydb > mydb_backup.sql Enter password: [ENTER PASSWORD] # Backup satu database dengan gzip admin@server:~$ mysqldump -u root -p mydb | gzip > mydb_backup.sql.gz # Backup semua database admin@server:~$ mysqldump -u root -p --all-databases > all_databases_backup.sql # Backup semua database dengan gzip admin@server:~$ mysqldump -u root -p --all-databases | gzip > all_databases_backup.sql.gz # Backup multiple databases admin@server:~$ mysqldump -u root -p --databases mydb1 mydb2 > databases_backup.sql # Backup dengan timestamp admin@server:~$ mysqldump -u root -p mydb > mydb_backup_$(date +%Y%m%d_%H%M%S).sql # Backup dengan single transaction (untuk InnoDB) admin@server:~$ mysqldump -u root -p --single-transaction mydb > mydb_backup.sql # Backup tanpa data (hanya struktur) admin@server:~$ mysqldump -u root -p --no-data mydb > mydb_structure.sql

Restore Database

bash
# Restore satu database admin@server:~$ mysql -u root -p mydb < mydb_backup.sql Enter password: [ENTER PASSWORD] # Restore database dengan gzip admin@server:~$ gunzip < mydb_backup.sql.gz | mysql -u root -p mydb # Restore semua database admin@server:~$ mysql -u root -p < all_databases_backup.sql # Restore multiple databases admin@server:~$ mysql -u root -p < databases_backup.sql # Restore dengan verbose admin@server:~$ mysql -u root -p -v mydb < mydb_backup.sql

Automated Backup dengan Cron

bash
# Buat backup script admin@server:~$ nano ~/backup_mysql.sh # Isi script: #!/bin/bash # Database credentials DB_USER="root" DB_PASS="your_password" DB_NAME="mydb" BACKUP_DIR="/backup/mysql" DATE=$(date +%Y%m%d_%H%M%S) # Create backup directory if not exists mkdir -p $BACKUP_DIR # Backup database mysqldump -u $DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_backup_$DATE.sql.gz # Delete backups older than 7 days find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete echo "Backup completed: ${DB_NAME}_backup_$DATE.sql.gz" # Set permission executable admin@server:~$ chmod +x ~/backup_mysql.sh # Test script admin@server:~$ ~/backup_mysql.sh # Edit crontab admin@server:~$ crontab -e # Tambahkan baris untuk backup harian jam 2 pagi 0 2 * * * /home/admin/backup_mysql.sh # Atau backup setiap hari Minggu jam 2 pagi 0 2 * * 0 /home/admin/backup_mysql.sh # Lihat crontab admin@server:~$ crontab -l
TIPS: Selalu backup database secara rutin. Gunakan cron job untuk otomatisasi backup. Simpan backup di lokasi yang berbeda dari server database untuk keamanan.
KOMPETENSI SUB-BAB 06
  • Mampu backup database dengan mysqldump
  • Mampu restore database dengan mysql
  • Mampu setup automated backup dengan cron
  • Mampu backup dengan compression
07
SUB-BAB 07 SECURITY

SECURITY DASAR DATABASE

Security Best Practices

PRACTICEDESKRIPSIPRIORITY
Strong PasswordGunakan password yang kuat untuk semua userHIGH
Least PrivilegeBerikan hanya privilege yang dibutuhkanHIGH
Remove Anonymous UsersHapus anonymous userHIGH
Remove Test DatabaseHapus test databaseHIGH
Disallow Root RemoteDisallow root login remotelyHIGH
Regular BackupBackup database secara rutinHIGH
Regular UpdateUpdate MariaDB secara rutinHIGH
Limit Remote AccessLimit remote access ke databaseMEDIUM
Audit LogsAudit log databaseMEDIUM

Secure MariaDB Installation

bash
# Jalankan secure installation admin@server:~$ sudo mariadb-secure-installation # Ini akan: # 1. Set root password # 2. Remove anonymous users # 3. Disallow root login remotely # 4. Remove test database # 5. Reload privilege tables

User Management Best Practices

MariaDB SQL
# Buat user dengan password yang kuat MariaDB [(none)]> CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'P@ssw0rd!2026_Strong'; # Grant hanya privilege yang dibutuhkan MariaDB [(none)]> GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'localhost'; # Jangan berikan ALL PRIVILEGES kecuali dibutuhkan # GRANT ALL PRIVILEGES ON mydb.* TO 'app_user'@'localhost'; # Revoke privileges yang tidak dibutuhkan MariaDB [(none)]> REVOKE DROP, CREATE ON mydb.* FROM 'app_user'@'localhost'; # Lihat privileges user MariaDB [(none)]> SHOW GRANTS FOR 'app_user'@'localhost'; # Reload privilege tables MariaDB [(none)]> FLUSH PRIVILEGES; # Hapus user yang tidak dibutuhkan MariaDB [(none)]> DROP USER 'old_user'@'localhost'; # Ubah password user secara rutin MariaDB [(none)]> ALTER USER 'app_user'@'localhost' IDENTIFIED BY 'NewP@ssw0rd!2026';

Firewall untuk MariaDB

bash
# Install UFW admin@server:~$ sudo apt install ufw # Enable UFW admin@server:~$ sudo ufw enable # Allow SSH admin@server:~$ sudo ufw allow ssh # Allow MariaDB hanya dari IP tertentu admin@server:~$ sudo ufw allow from 192.168.1.100 to any port 3306 # Allow MariaDB dari subnet tertentu admin@server:~$ sudo ufw allow from 192.168.1.0/24 to any port 3306 # Lihat status UFW admin@server:~$ sudo ufw status # Lihat status UFW verbose admin@server:~$ sudo ufw status verbose

MariaDB Configuration Security

bash
# Edit MariaDB configuration admin@server:~$ sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf # Tambahkan atau ubah: [mysqld] # Bind to specific IP (default: 127.0.0.1) bind-address = 127.0.0.1 # Disable remote root login skip-name-resolve # Disable symbolic links symbolic-links=0 # Enable logging log-error = /var/log/mysql/error.log log-warnings = 2 # Restart MariaDB admin@server:~$ sudo systemctl restart mariadb

Audit dan Monitoring

MariaDB SQL
# Lihat semua user MariaDB [(none)]> SELECT User, Host FROM mysql.user; # Lihat user dengan privileges MariaDB [(none)]> SELECT User, Host, Select_priv, Insert_priv, Update_priv, Delete_priv FROM mysql.user; # Lihat semua database MariaDB [(none)]> SHOW DATABASES; # Lihat semua table dalam database MariaDB [(none)]> USE mydb; MariaDB [mydb]> SHOW TABLES; # Lihat status database MariaDB [(none)]> SHOW STATUS; # Lihat variables MariaDB [(none)]> SHOW VARIABLES; # Lihat variables tertentu MariaDB [(none)]> SHOW VARIABLES LIKE 'bind_address'; # Lihat process list MariaDB [(none)]> SHOW PROCESSLIST; # Lihat full process list MariaDB [(none)]> SHOW FULL PROCESSLIST;
TIPS: Selalu jalankan mariadb-secure-installation setelah install MariaDB. Gunakan prinsip least privilege untuk user. Bind MariaDB ke 127.0.0.1 jika tidak perlu remote access. Gunakan firewall untuk limit remote access.
KOMPETENSI SUB-BAB 07
  • Mampu jalankan mariadb-secure-installation
  • Mampu apply user management best practices
  • Mampu konfigurasi firewall untuk MariaDB
  • Mampu konfigurasi MariaDB configuration security
  • Mampu audit dan monitoring MariaDB
VOLUME 12 SELESAI

SELAMAT!

Kamu telah menyelesaikan Volume 12 - Database Server Fundamental. Dari konsep database server, instalasi MariaDB, user database, database dan table, basic SQL, backup dan restore database, hingga security dasar database. Kamu sekarang menguasai Database Server Fundamental di Debian Linux dengan MariaDB. Di Volume 13, kita akan dalami Web Application Deployment. Sampai jumpa!
SUB-BAB7 LEVELDatabase NEXTWeb Application
ABDURROZAK.MY.ID // TERHUBUNG

JARINGAN SOSIAL

Temukan saya di berbagai platform digital

s.id GitHub Instagram Threads Facebook TikTok YT Teacher YT Course YT Studio Hashnode WhatsApp Email