Skip to content

Mysql

Install

Info

Installation flow needs an active VPN

sudo apt update
sudo apt install gnupg
cd /tmp
wget https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb
sudo dpkg -i mysql-apt-config*
sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation

Data Types

Data Type Range Storage size
CHAR(size) Fixed length, 0 to 255 chars 1 to 255 bytes
VARCHAR(size) Variable length, 0 to 65,535 chars 1 to 65,535 bytes
TEXT Up to 65,535 chars Up to 65,535 bytes
MEDIUMTEXT Up to 16,777,215 chars Up to 16,777,215 bytes
LONGTEXT Up to 4,294,967,295 chars Up to 4,294,967,295 bytes
TINYBLOB Up to 255 bytes Up to 255 bytes
BLOB Up to 65,535 bytes Up to 65,535 bytes
MEDIUMBLOB Up to 16,777,215 bytes Up to 16,777,215 bytes
LONGBLOB Up to 4,294,967,295 bytes Up to 4,294,967,295 bytes
ENUM Enumerated list of values Depends on values
SET Set of values Depends on values
Data Type Range (signed) (Default) Range (unsigned) Storage size
TINYINT(size) -128 to 127 0 to 255 1 byte
SMALLINT(size) -32,768 to 32,767 0 to 65,535 2 bytes
MEDIUMINT(size) -8,388,608 to 8,388,607 0 to 16,777,215 3 bytes
INT(size), INTEGER(size) -2,147,483,648 to 2,147,483,647 0 to 4,294,967,295 4 bytes
BIGINT(size) -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 0 to 18,446,744,073,709,551,615 8 bytes
FLOAT(size, d) -3.402823466E+38 to 3.402823466E+38 Not applicable 4 bytes
DOUBLE(size, d) -1.7976931348623157E+308 to 1.7976931348623157E+308 Not applicable 8 bytes
DECIMAL(size, d), DEC(size, d) Depends on the precision and scale Depends on the precision and scale Varies
BIT(size) Not applicable 1 to 64 Varies
Data Type Range Storage size
DATE 1000-01-01 to 9999-12-31 3 bytes
DATETIME(fsp) 1000-01-01 00:00:00 to 9999-12-31 23:59:59 8 bytes
TIMESTAMP(fsp) 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC 4 bytes
TIME(fsp) -838:59:59 to 838:59:59 3 bytes
YEAR 1901 to 2155 1 byte
Data Type Range Storage size
BOOL, BOOLEAN 0 or 1 1 byte

Full Data Types

Export/Import

mysql [-h <host>] -u <username: root> -p <db_name> < <file_path/file_name.sql>

mysqldump [-h <host>] -u <username: root> -p <db_name> > <file_path/file_name.sql>

More options:

  • --no-data: Dump only the table structure (schema); no row data is exported.
  • --single-transaction: Makes a snapshot without locking tables (InnoDB only).
  • --quick: Streams rows to the output file instead of buffering (good for large DBs).
  • --routines: Exports stored procedures and functions
  • --triggers: Exports triggers (included by default, but good to be explicit).
  • --events: Exports scheduled events.
  • --add-drop-table: Add DROP TABLE IF EXISTS before CREATE.
  • --compress: Use compression for client/server communication (saves bandwidth).
  • --result-file=path: Write directly to file (avoids shell redirection).
  • --databases db1 db2: Dump listed databases.
  • --all-databases: Dump all databases.
  • --set-gtid-purged=OFF|ON|AUTO: Include or omit GTID info.

Shell

mysql -u root -p

mysql -u root -p <<< 'SHOW DATABASES;'

SELECT * FROM <table_name>\G

User

SELECT user, host FROM mysql.user

UPDATE mysql.user SET host='<host: %>' WHERE user='<user: root>'

CREATE USER '<username>'@'localhost' IDENTIFIED BY '<password>'



# root user
ALTER USER 'root'@'%' IDENTIFIED BY '<new_password>'

GRANT ALL PRIVILEGES ON <db_name>.* TO '<user>'@'localhost'

Database

SHOW DATABASES

CREATE DATABASE <db_name>

DROP DATABASE <db_name>

SHOW processlist



USE <db_name>

SELECT table_schema "<db_name>", ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema

Tables

SHOW TABLES

# Simple
DESCRIBE <table_name>

# Full
SHOW FULL COLUMNS FROM <table_name>

# Simple
CREATE TABLE <table_name> (id INT NOT NULL AUTO_INCREMENT, <column2> <datatype>, PRIMARY KEY(id))

# Comples
CREATE TABLE <table_name> (
    id BINARY(16) PRIMARY KEY,  # UUID
    created DATETIME(6) NOT NULL,
    <column3> INT NOT NULL DEFAULT 0,
    <column4> BINARY(16) UNIQUE NOT NULL,  # FK (1:1)

    INDEX (<column3>),  # custom index
    FOREIGN KEY (<column4>) REFERENCES <other_table>(id)  # automatically will create an index
);

DROP TABLE <table_name>

SHOW INDEXES IN <table_name>

Row

UPDATE <table_name> SET <column1>=<value>

INSERT INTO <table_name> (column1, column2) VALUES (value1, value2)

DELETE FROM <table_name> WHERE <column1>=<value>

Index

CREATE [UNIQUE] INDEX <index_name> ON <table> (<column1, column2>) WHERE <conditions>

Other

  • Version:

    SHOW VARIABLES LIKE "%version%";