Database - Archive

Setup

Install MariaDB and run the initial setup script. Default port is 3306.

sudo apt update
sudo apt install mariadb-server
sudo mysql_secure_installation

Useful Commands

Creating

-- create db
CREATE DATABASE <db>;
 
-- create user
CREATE USER '<user>'@localhost IDENTIFIED BY '<pass>';
 
-- grant privileges
GRANT ALL PRIVILEGES ON <db>.* TO '<username>'@localhost;
 
-- save grant changes
FLUSH PRIVILEGES;

-- select db
USE <dbname>;
 
-- create table
CREATE table <table-name> (
    id int NOT NULL AUTO_INCREMENT,
    first int NOT NULL,
    second varchar(191) NOT NULL,
    PRIMARY KEY (id)
);

Deleting

-- drop user
DROP USER '<user-name>'@localhost;
 
-- drop database
DROP DATABASE <dbname>;

Displaying

-- display users
SELECT User FROM mysql.user;
 
-- display databases
SHOW DATABASES;
 
-- display tables
USE <dbname>;
SHOW TABLES;

-- display privileges of user
SHOW GRANTS FOR '<username>'@localhost;

-- display table columns
DESC <dbname>.<table-name>;
SHOW COLUMNS FROM <dbname>.<table-name>;
 
-- display table columns of selected table
DESC <table-name>;
SHOW COLUMNS FROM <table-name>;

Display Formats

-- display vertically
SHOW DATABASES \G;
SELECT col FROM db \G;

Altering

-- alter column type
-- doesn't replace unless values in colums are compatible
use <dbname>;
alter table <tablename> modify column <columname> <typename>;

Backing Up

There's two types of backups.

Logical Backup
Physical Backup

Physical

This is basically copying the base sql directory after stopping sql.

Logical

To backup;

# scripts
mysqldump -u <username> -p"<pass>" <dbname> > /path/to/file.sql
 
# shell
mysqldump -u <username> -p <dbname> > /path/to/file.sql
 
# full db backup
mysqldump -u root -p --all-databases > ./mariadb.dump

Try not to use the root user as much as possible. Except when making full db backups. Use a user who has deligated access to the database that is to be backed up.

To import;

# first create db in mysql
# then import data to it
 
mysql <dbname> < /path/to/file.sql

MongoDB

If you get an error like pymongo.errors.ServerSelectionTimeoutError. Check if the current ip address is allowed in the dashboard. Source (opens in a new tab)

PostgreSQL

Setting Up

First, there's a change that's needed in the config file to enable password auth for users. To do that we need to first find out where the config file is.

sudo -u postgres psql
> SHOW config_file

After that edit that file and change the following line from peer to md5.

local     all         all            peer
                                    ↓↓↓↓↓
local     all         all            md5

Now restart Postgres.

sudo service postgresql restart