(Shortcut) Install MariaDB Server 10.3 on Ubuntu 20.04

# update package and install
sudo apt update
sudo apt install mariadb-server mariadb-client

sudo mysql_secure_installation

sudo mysql -u root -p
# system will ask 2 password
# 1 linux user password
# 2 mysql root password

[MySQL] Config Basic

การ Config ไฟล์ cnf ของ MySQL Server

เราใช้ MySQL Server 5.7 ในหัวข้อนี้พูดถึงการใช้ไฟล์ cnf ในการ Config ค่าต่าง ๆ
ถ้าอยากรู้ Version ให้พิมพ์ mysqld --version ที่ terminal

ไฟล์ conf จะเก็บไว์ที่ path /etc/mysql/my.cnf, /etc/mysql/mysql.cnf
- /etc/mysql/my.cnf
- /etc/mysql/mysql.cnf

และอีก 2 ไฟล์ที่
- /etc/mysql/conf.d/mysql.cnf
- /etc/mysql/mysql.conf.d/mysql.cnf

มาเปิดดู 2 ไฟล์แรกก่อน

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/mysql.conf
!includedir /etc/mysql/mysql.conf.d/

[MySQL] Command Line Basic

Login to MySQL

mysql -u [username] -p

MySQL User Management

DESC mysql.user:
SELECT host, user FROM mysql.user;

CREATE USER 'username'@'hostname' IDENTIFIED BY 'password';
-- Hostname % or Localhost

DROP USER 'username'@'hostname';

ERROR 1819 (HY000)
Your password does not satisfy the current policy requirements

SHOW VARIABLES LIKE 'validate_%';
SET GLOBAL validate_password_policy=LOW;

---

Database Management

-- List all DATABASE 
SHOW DATABASES;

-- List database with DEFAULT_CHARACTER_SET_NAME and DEFAULT_COLLATION_NAME
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA;

-- Create DATABASE and set CHARACTER_SET and COLLATION_NAME
CREATE DATABASE db_name [DEFAULT] CHARACTER SET utf8 [DEFAULT] COLLATE utf8_general_ci;

-- Change CHARACTER_SET and COLLATION_NAME
ALTER DATABASE db_name [DEFAULT] CHARACTER SET utf8 [DEFAULT] COLLATE utf8_general_ci;

-- Grant user to database
GRANT ALL PRIVILEGES ON *.* TO 'username'@'host';
GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'host';
GRANT ALL|SELECT|INSERT|UPDATE|DELETE ON db_name.* TO 'username'@'host';
FLUSH PRIVILEGES;

-- Show user grant;
SHOW GRANTS FOR 'username'@'hostname';

-- Revoke
REVOKE ALL PRIVILEGES ON *.* TO 'username'@'host';
REVOKE ALL PRIVILEGES ON db_name.* TO 'username'@'host';
REVOKE ALL|SELECT|INSERT|UPDATE|DELETE ON db_name.* TO 'username'@'host';

Table Management

-- Create table and set CHARACTER_SET and COLLATION_NAME
CREATE TABLE tbl_name (column_list) [DEFAULT] CHARACTER SET charset_name COLLATE collation_name;

-- Change table and set CHARACTER_SET and COLLATION_NAME
ALTER TABLE tbl_name (column_list) [DEFAULT] CHARACTER SET charset_name COLLATE collation_name;

-- Temp
SHOW VARIABLES LIKE 'collation%';

Quit

QUIT;

---

https://dev.mysql.com/doc/refman/8.0/en/show-grants.html
https://dev.mysql.com/doc/refman/8.0/en/show-collation.html
https://dev.mysql.com/doc/refman/8.0/en/charset-database.html
https://dev.mysql.com/doc/refman/8.0/en/charset-table.html
https://stackoverflow.com/questions/5906585/how-to-change-the-character-set-and-collation-throughout-a-database

Install MySQL 5.7 on Ubuntu 18.04

Install MySQL Server 5.7

การติดตั้ง MySQL Server 5.7

Reference: Install MySQL | Install phpMyAdmin

# Install MySQL Server
sudo apt update
sudo apt install mysql-server

# Check MySQL Status
sudo systemctl status mysql

# Initial Server
sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No:
Please set the password for root here.

New password:

Re-enter new password:
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL 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? (Press y|Y for Yes, any other key for No) :

 ... skipping.


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? (Press y|Y for Yes, any other key for No) :

 ... skipping.
By default, MySQL 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? (Press y|Y for Yes, any other key for No) :

 ... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) :

 ... skipping.
All done!