CLI -Based Server Database Management Guide

CLI -based database server management guide is very important, especially when you work in a server environment without a graphics interface. With Mariadb, you can do various database server management guidelines through the Command Line Interface (CLI). The following is a complete guide for managing databases, tables, and data DI Mariadb using the terminal.

1. Access Mariadb through the Terminal

The first step is to access Mariadb through the terminal. Make sure Mariadb has been installed and runs on your system.

Enter Mariadb

To enter Mariadb, use the following command:

sudo mysql -u root -p

You will be asked to enter a password for users root. After that, you will enter the Mariadb prompt.

2. Database management

Create a database

To create a new database, use the command CREATE DATABASE:

CREATE DATABASE nama_database;

Example:

CREATE DATABASE toko_online;

See a list of databases

To see a list of all databases on the Mariadb server, use the command:

SHOW DATABASES;

Using a database

To start using a certain database, use the command USE:

USE nama_database;

Example:

USE toko_online;

Delete a database

If you want to delete a database, use the command DROP DATABASE. Attention: This will delete all tables and data in the database permanently.

DROP DATABASE nama_database;

Example:

DROP DATABASE toko_online;

3. TABLE MANAGEMENT

Creating a table

To create a new table in the database, use the command CREATE TABLE. You must be in the database that you want to add the table.

CREATE TABLE nama_tabel (
kolom1 tipe_data,
kolom2 tipe_data,
...
);

Example:

Creating a table pelanggan with column id, namaAnd email.

CREATE TABLE pelanggan (
id INT AUTO_INCREMENT PRIMARY KEY,
nama VARCHAR(100),
email VARCHAR(100)
);

See the table structure

To see the table structure (columns in the table), use the command DESCRIBE or SHOW COLUMNS FROM:

DESCRIBE nama_tabel;

Example:

DESCRIBE pelanggan;

See the contents of the table

To see all data in the table, use the order SELECT:

SELECT * FROM nama_tabel;

Example:

Delete tables

SELECT * FROM pelanggan;

If you want to delete a table from the database, use the command DROP TABLE. Attention: This will delete all data in the table permanently.

DROP TABLE nama_tabel;

Example:

DROP TABLE pelanggan;

Change the table (add or delete the column)

Add a new column

To add a new column to an existing table, use the command ALTER TABLE:

ALTER TABLE nama_tabel ADD nama_kolom tipe_data;

Example:

Add columns alamat to the table pelanggan:

ALTER TABLE pelanggan ADD alamat VARCHAR(255);

Delete the column

To delete the column from the table, use the command ALTER TABLE:

ALTER TABLE nama_tabel DROP COLUMN nama_kolom;

Example:

Delete the column alamat From the table pelanggan:

ALTER TABLE pelanggan DROP COLUMN alamat;

Change column data type

To change the existing column data type, use the command ALTER TABLE:

ALTER TABLE nama_tabel MODIFY nama_kolom tipe_data_baru;

Example:

Change column data type nama become TEXT:

ALTER TABLE pelanggan MODIFY nama TEXT;

4. Data management

Add data to the table

To add data to the table, use the command INSERT INTO:

INSERT INTO nama_tabel (kolom1, kolom2, ...) VALUES (nilai1, nilai2, ...);

Example:

Add new customers to the table pelanggan:

INSERT INTO pelanggan (nama, email) VALUES ('Andi', 'andi@example.com');

Change the data in the table

To change the data that is already in the table, use the command UPDATE:

UPDATE nama_tabel SET kolom1 = nilai_baru WHERE kondisi;

Example:

Change customer email with id = 1:

UPDATE pelanggan SET email="andi.baru@example.com" WHERE id = 1;

Delete data from the table

To delete data from the table, use the command DELETE:

DELETE FROM nama_tabel WHERE kondisi;

Example:

Delete customers with id = 1:

DELETE FROM pelanggan WHERE id = 1;

5. Backup and Restore Database

Backup database

To backup the database (dump database to the .sql file), use the command mysqldump From the terminal:

mysqldump -u root -p nama_database > nama_database.sql

Example:

mysqldump -u root -p toko_online > toko_online.sql

Return the database

To restore (restore) database from the .sql file, use the following command from the terminal:

mysql -u root -p nama_database < nama_database.sql

Example:

mysql -u root -p toko_online < toko_online.sql

6. Managing User Access Rights

Create new users

CREATE USER 'nama_pengguna'@'host' IDENTIFIED BY 'kata_sandi';

Example:

Provide access rights to users

Example:

CREATE USER 'user_baru'@'%' IDENTIFIED BY 'password123';
GRANT hak_akses ON nama_database.* TO 'nama_pengguna'@'host';

Give all access rights to the user user_baru for databases toko_online:

GRANT ALL PRIVILEGES ON toko_online.* TO 'user_baru'@'%';

See user access rights

SHOW GRANTS FOR 'nama_pengguna'@'host';

Delete user access rights

REVOKE hak_akses ON nama_database.* FROM 'nama_pengguna'@'host';

Delete users

DROP USER 'nama_pengguna'@'host';

7. log and monitoring

See the server status

To see Mariadb’s server status, use orders:

SHOW STATUS;

See the log error

Mariadb’s log error is usually in the directory /var/log/mysql/. You can check it with:

tail -f /var/log/mysql/error.log

Cover:

By following this CLI -based Database Server Management Guide, you have successfully managed the Database Management using the Mariadb Application on Debian 10. Database Server Management Guide in CLI is the safest method in managing databases. Hopefully this guide is useful for you!

Also read the article about: Mariadb Database Server Installation and Configuration Guide on Debian 10