# Mysql Recipe
## Install MySQL 5.7 on Ubuntu 20.04 LTS
https://www.fosstechnix.com/how-to-install-mysql-5-7-on-ubuntu-20-04-lts/
Ngoài ra có cách khác
### Cài đặt mysql server(với quyền root)
```
$ sudo apt-get update
$ sudo apt-get install mysql-server
$ sudo mysql_secure_installation
```
## Install Mysql 8.0 on Ubuntu 22.04
https://www.cyberciti.biz/faq/installing-mysql-server-on-ubuntu-22-04-lts-linux/
## Kiểm tra trạng thái mysql cài bằng brew trên Macos
```
$ brew services info mysql
$ brew services start mysql
```
## Sửa port của mysql localhost trên MacOS
```
$ nano /opt/homebrew/etc/my.cnf --- Sau đó sửa nội dung port ở dưới rồi restart
# Default Homebrew MySQL server config
[mysqld]
# Only allow connections from localhost
bind-address = 127.0.0.1
mysqlx-bind-address = 127.0.0.1
port = 3310
```
---
# Usage Mysql
## Đăng nhập vào mysql
```
$ sudo mysql -u root -p
```
## Thao tác với mysql
### Check version
```
mysql> SHOW VARIABLES LIKE "%version%";
```
### Thao tác với user
```
### Select
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
### Create/Drop user
mysql> create user 'mysql'@'localhost' identified by 'installation';
mysql> DROP USER 'linuxconfig'@'localhost';
### Grant user to Databases
mysql> GRANT ALL PRIVILEGES ON learn_spark.* to longdh@localhost;
mysql> FLUSH PRIVILEGES; # Để thay đổi được thực hiện ngay lập tức
### ===> Tạo 1 User có full quyền access DB cụ thể từ bên ngoài
CREATE USER 'longdh'@'%' IDENTIFIED WITH mysql_native_password BY 'secret@123';
GRANT ALL PRIVILEGES ON sn6_ans_fetcher.* TO 'longdh'@'%';
FLUSH PRIVILEGES;
```
### Create Databases with CHARACTER SET
```
mysql> CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```
### Drop Databases
```
mysql> DROP DATABASE IF EXISTS tutorial_database;
```
### Chấp nhận connect tới mysql bằng tool (Navicat or datagrip) từ máy localhost
```
$ sudo mysql -u root -p
Enter password: <enter password>
mysql> GRANT ALL ON *.* to root@'123.123.123.123' IDENTIFIED BY 'put-your-password';
# nếu muốn allow all IP thì dùng %
mysql> GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'put-your-password';
mysql> FLUSH PRIVILEGES;
mysql> exit;
```
## Thao tác với systemctl
```
$ systemctl start/restart mysql
$ systemctl status mysql
```
## Export/Import
> Note: path to mysqldump/mysql on Macos install by homebrew:
> `/opt/homebrew/opt/mysql@8.1/bin/mysqldump`
> `/opt/homebrew/opt/mysql@8.1/bin/mysql`
### Export ra file .sql
```
$ mysqldump -u <db_user> -p <db_name> > file-name.sql
```
### Import từ file .sql
```
$ mysql -u <db_user> -p <db_name> < file-name.sql
```
## Mysql với Docker
Pull image
```
$ docker pull mysql:5.7.31
```
Run image với data được lưu trữ tại local, mapping port local 3307 = container 3306
```
$ docker run --name local-mysql -v "$PWD/mysql-data":/var/lib/mysql -e MYSQL_ROOT_PASSWORD=secret123 -p 3307:3306 -d mysql:5.7.31 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
```
Với
--name: tên container
-v: mapping folder $PWD/mysql-data tại local với folder data của container
-p: mapping port
-e: setup MYSQL_ROOT_PASSWORD
Run image xong thì thử kết nối từ tool vào mysql xem có được không với
**DB_USER** = root
**DB_PASSWORD** = secret123
**DB_HOST** = localhost
**DB_PORT** = 3307
## Các câu lệnh quan trọng trong Mysql
* **UNION**
Nối 2 bảng có cùng số lượng cột và trả về kết quả sau khi bỏ đi các row duplicate
```
select * from full_year
union
select * from half_year
```
* **DISTINCT**
Từ khóa DISTINCT trong SQL được sử dụng kết hợp với câu lệnh SELECT để loại bỏ tất cả các bản ghi trùng lặp và chỉ lấy các bản ghi duy nhất
```
select DISTINCT country from diary order by country
```
* **GROUP_CONCAT**
lấy tất cả record của cột và cộng chúng lại với nhau trả về một chuỗi, các record cách nhau bởi dấu phẩy
| id | country |
| -------- | -------- |
| 1 | Ireland |
| 2 | France |
| 3 | Australia |
| 4 | Japan |
```
select GROUP_CONCAT(country) as countries from (
select DISTINCT country from diary order by country
) as aaa;
```
Kết quả:
| countries |
| -------- |
| Australia;France;Ireland;Japan|
Options:
SEPARATOR - chọn ký tự để nối các row data
```
select GROUP_CONCAT(country SEPARATOR ' ') as countries from (
select DISTINCT country from diary order by country
) as aaa;
```
Kết quả:
| countries |
| -------- |
| Australia France Ireland Japan|
# Setup Master Slave
1. Tạo User replicator trên Master
2. dump data từ master
3. Import data vào slave và start slave
4. Create User readonly trên slave, set slave chỉ read_only
```
============================================
1. Tạo User replicator trên Master
======================
# Show User & create replica User
SELECT user, host, plugin FROM mysql.user;
DROP USER 'replicator'@'%';
CREATE USER 'replicator'@'%' IDENTIFIED WITH mysql_native_password BY 'Zbj7O8kBT63s';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;
SELECT user, host, plugin FROM mysql.user WHERE user = 'replicator';
# Set slave trong config của slave
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
server-id = 2
relay-log = /var/log/mysql/mysql-relay-bin.log
sudo systemctl restart mysql
============================================
2.dump data từ master
======================
# keep read only cho DB master trc khi dump
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
# Open new terminal, dump DB
mysqldump -u root -p --all-databases \
--single-transaction \
--master-data=1 \
--routines --triggers --events \
--disable-keys \
> dump.sql
# Add FOREIGN_KEY_CHECKS=1 to dump file
echo "SET FOREIGN_KEY_CHECKS=0;" > head.sql
echo "SET FOREIGN_KEY_CHECKS=1;" > tail.sql
cat head.sql dump.sql tail.sql > dump_with_fkcheck.sql
grep "SET FOREIGN_KEY_CHECKS=1" dump_with_fkcheck.sql # Re-Check
# Send dump file to slave
scp test_scp.txt ubuntu@172.31.21.196:/home/ubuntu/
============================================
3. Import data vào slave và start slave
======================
# Import dump file in Slave
mysql -u root -p < dump_with_fkcheck.sql
# Setup replica in Slave, MASTER_PASSWORD is password of replicator in sever Master
CHANGE MASTER TO
MASTER_HOST='172.31.27.117',
MASTER_USER='replicator',
MASTER_PASSWORD='Zbj7O8kBT63s',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=28706168;
START SLAVE;
SHOW SLAVE STATUS\G
# Nếu check thấy slave ok thì quay lại master unlock table
UNLOCK TABLES;
# Run/stop slave
stop slave;
reset slave;
start slave;
```
## Cách bật chế độ chỉ đọc trên Slave
```
============================================
4. Create User readonly trên slave, set salve chỉ read_only
======================
# Edit mysql config
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
# Add this row
read_only = ON
super_read_only = ON
# Restart
sudo systemctl restart mysql
# Check slave is only read
SHOW VARIABLES LIKE '%read_only%';
```
## Cách để reset lại toàn bộ DB slave nhằm import lại data
```
sudo systemctl stop mysql
sudo rm -rf /var/lib/mysql/*
sudo mysqld --initialize-insecure
sudo systemctl start mysql
```
Sign in with Wallet
Connect another wallet