MySQL

Phần 1: Giới thiệu MySQL

1.1. Thành phần và các công cụ phổ biến

1.1.1. Chương trình Client

• mysql: Công cụ CLI (Giao diện dòng lệnh) tương tác hoặc không tương tác.
• mysqldump: Công cụ sao lưu, dựa trên giao thức MySQL gửi yêu cầu truy vấn đến mysqld và chuyển đổi tất cả dữ liệu thu được thành các câu lệnh ghi như INSERT, lưu vào tệp văn bản.
• mysqladmin: Quản lý mysqld dựa trên giao thức MySQL.
• mysqlimport: Công cụ nhập dữ liệu.
• Công cụ quản lý cho bộ lưu trữ MyISAM:
  • myisamchk: Kiểm tra cơ sở dữ liệu MyISAM.
  • myisampack: Nén bảng MyISAM, chỉ đọc.

1.1.2. Chương trình Server

• mysqld_safe
• mysqld
• mysqld_multi: Hỗ trợ nhiều phiên bản, ví dụ: mysqld_multi --example

1.1.3. Cú pháp lệnh mysql

mysql [Tùy chọn] [tên_cơ_sở_dữ_liệu]

Các tùy chọn phổ biến:

-A, --no-auto-rehash: Tắt tự động hoàn thành
-u, --user= Tên người dùng, mặc định là root
-h, --host= Host máy chủ, mặc định là localhost
-p, --password= Mật khẩu người dùng, nên sử dụng -p, mặc định là mật khẩu trống
-P, --port= Cổng máy chủ
-S, --socket= Chỉ định đường dẫn tệp socket
-D, --database= Chỉ định cơ sở dữ liệu mặc định
-C, --compress: Bật nén
-e "SQL": Thực thi lệnh SQL
-V, --version: Hiển thị phiên bản
-v --verbose: Hiển thị thông tin chi tiết
--print-defaults: Lấy cấu hình mặc định mà chương trình sử dụng

1.1.4. Cú pháp lệnh mysqladmin

mysqladmin [Tùy chọn] lệnh lệnh...

Ví dụ:

# Kiểm tra dịch vụ MySQL có đang hoạt động không, nếu có sẽ hiển thị "mysqld is alive"
mysqladmin -uroot -psecurepass123 ping
# Tắt dịch vụ MySQL, nhưng lệnh mysqladmin không thể khởi động lại
mysqladmin -uroot -psecurepass123 shutdown
# Tạo cơ sở dữ liệu mydb
mysqladmin -uroot -psecurepass123 create mydb 
# Xóa cơ sở dữ liệu mydb
mysqladmin -uroot -psecurepass123 drop mydb
# Thay đổi mật khẩu root
mysqladmin -uroot -psecurepass123 password 'newSecurePass'
# Ghi log, tạo tệp mới /var/lib/mysql/mariadb-bin.00000N
mysqladmin -uroot -psecurepass123 flush-logs

1.1.5. Cấu hình phía Server

1.1.5.1. Tệp cấu hình phía Server

Máy chủ (mysqld) có nhiều cách cấu hình đặc tính làm việc:

1. Tùy chọn dòng lệnh
2. Tệp cấu hình: Định dạng kiểu ini, cung cấp thông tin cấu hình tập trung cho các ứng dụng MySQL.

Các tệp cấu hình phía server:

/etc/my.cnf   # Tùy chọn toàn cục
/etc/mysql/my.cnf # Tùy chọn toàn cục
~/.my.cnf # Tùy chọn riêng cho người dùng

Định dạng tệp cấu hình:

[mysqld]
[mysqld_safe]
[mysqld_multi]
[mysql]
[mysqldump]
[server]
[client]
Định dạng giá trị cấu hình:
parameter = value

Ghi chú:

_ và - có ý nghĩa giống nhau
1, ON, TRUE có ý nghĩa giống nhau, 0, OFF, FALSE có ý nghĩa giống nhau, không phân biệt chữ hoa chữ thường

1.1.5.2. Kết nối Socket

Máy chủ lắng nghe hai loại địa chỉ socket:

• IP socket: Lắng nghe trên cổng TCP 3306, hỗ trợ giao tiếp từ xa, có thể lắng nghe trên cổng 3306/tcp trên một hoặc tất cả các giao diện IP.
• unix sock: Lắng nghe trên tệp socket, chỉ hỗ trợ giao tiếp cục bộ, ví dụ: /var/lib/mysql/mysql.sock

Ghi chú: Khi host là localhost, hệ thống sẽ tự động sử dụng unix sock. Ví dụ: Cổng MySQL

mysql> SHOW VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port         | 3306 |
+---------------+-------+
1 row in set (0.01 sec)
# MySQL 8.0 thêm một cổng 33060/tcp
# Cổng 33060 là cổng mặc định cho Giao diện Mở rộng Cơ sở dữ liệu MySQL (Giao thức X của MySQL).
mysql> SHOW VARIABLES LIKE 'mysqlx_port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| mysqlx_port   | 33060 |
+---------------+-------+
1 row in set (0.00 sec)

1.1.5.3. Tắt kết nối mạng của mysqld

Chỉ lắng nghe các cổng cục bộ, tất cả tương tác giữa client và server đều thông qua một tệp socket, đường dẫn socket được cấu hình trong /var/lib/mysql/mysql.sock và có thể được sửa đổi trong /etc/my.cnf. Ví dụ:

vim /etc/my.cnf
[mysqld]
skip-networking=1
bind_address=127.0.0.1

Phần 2: Cài đặt MySQL

2.1. Cài đặt nhị phân (Binary Installation)

2.1.1. Cài đặt định dạng nhị phân chung MySQL 8

2.1.1.1. Chuẩn bị người dùng

groupadd -r -g 306 mysql_db
useradd -r -g 306 -u 306 -d /var/lib/mysql/data mysql_db

2.1.1.2. Chuẩn bị thư mục dữ liệu

# Tùy chọn, script mysql_install_db có thể tự động tạo thư mục này
mkdir /var/lib/mysql/data
chown mysql_db:mysql_db /var/lib/mysql/data

2.1.1.3. Chuẩn bị chương trình nhị phân

admin@db-server:~# tar -xvf mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz -C /opt/
cd /opt
ln -sv mysql-VERSION mysql_server
chown -R root:root /opt/mysql_server/

2.1.1.4. Chuẩn bị tệp cấu hình

admin@db-server:/opt/mysql_server# vim /etc/my.cnf
[mysqld]
# Đặt cổng 3306
port=3306
 
# Đặt thư mục cài đặt MySQL
basedir=/opt/mysql_server
 
# Đặt thư mục chứa dữ liệu của MySQL
datadir=/var/lib/mysql/data
 
# Cho phép số kết nối tối đa
#max_connections=151
 
# Bộ ký tự mặc định trên server là UTF8MB4
#character-set-server=utf8mb4
 
# Bộ lưu trữ mặc định khi tạo bảng mới
#default-storage-engine=INNODB
 
# Đặt vị trí tệp socket của MySQL
socket=/var/lib/mysql/data/mysql.sock
 
# Đặt vị trí tệp pid của dịch vụ MySQL
pid-file=/var/lib/mysql/data/mysql.pid
 
# Bật bộ nhớ đệm truy vấn
#query_cache_type=1
#query_cache_size=16M
 
# Đặt múi giờ mặc định
#default-time-zone='+08:00'
 
# Đặt giá trị tối đa của bảng tạm trong bộ nhớ
#tmp_table_size=16M
 
# Đặt kích thước tối đa của bảng tạm trong bộ nhớ
#max_heap_table_size=16M
 
# Bật ghi log truy vấn chậm
#slow_query_log=1
#slow_query_log_file=/opt/mysql_server/data/mysql-slow.log
 
# Thời gian truy vấn chậm
#long_query_time=10
 
# Cấu hình log nhị phân
log_bin=/var/lib/mysql/data/mysql-bin
server-id=1
skip_name_resolve = on    # Không phân giải tên host, khuyến nghị sử dụng
 
#[mysql]
# Đặt bộ ký tự mặc định cho client
#default-character-set=utf8mb4
 
[client]
# Đặt cổng và socket mặc định khi client kết nối server
#port=3306
socket=/var/lib/mysql/data/mysql.sock
#default-character-set=utf8mb4

2.1.1.5. Chuẩn bị biến môi trường

admin@db-server:/opt/mysql_server# echo 'PATH=/opt/mysql_server/bin:$PATH' > /etc/profile.d/mysql.sh
admin@db-server:/opt/mysql_server# . /etc/profile.d/mysql.sh 

2.1.1.6. Khởi tạo tệp cơ sở dữ liệu và trích xuất mật khẩu root

2.1.1.6.1. Tạo mật khẩu ngẫu nhiên

admin@db-server:/opt/mysql_server# mysqld --initialize --user=mysql_db --datadir=/var/lib/mysql/data
2024-06-12T06:19:03.447111Z 0 [System] [MY-013169] [Server] /opt/mysql_server-8.0.19-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.19) initializing of server in progress as process 55880
2024-06-12T06:19:05.715056Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: )=r=u>j;K0i) # Mật khẩu ngẫu nhiên

2.1.1.6.2. Tạo mật khẩu trống

mysqld --initialize-insecure --user=mysql_db --datadir=/var/lib/mysql/data 

2.1.1.7. Trình hướng dẫn bảo mật

admin@db-server:~# mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 

The existing password for the user account root has expired. Please set a new password.

New password: 

Re-enter new password: 

VALIDATE PASSWORD COMPONENT 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 component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Using existing password for root.

Estimated strength of the password: 100 
Change the password for root ? ((Press y|Y for Yes, any other key for No) : no 

 ... skipping.
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) : y
Success.


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) : 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) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

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) : y
Success.

All done! 

Phần 3: Đa phiên bản MySQL (MySQL Multi-Instance)

3.1. Giới thiệu đa phiên bản

• Đa phiên bản cơ sở dữ liệu là gì?

  Đa phiên bản tương tự như chạy hai ứng dụng chat cùng lúc, cổng tương tự như tài khoản chat, cơ sở dữ liệu tương tự như cửa sổ chat, bảng tương tự như bản ghi chat.

  Đa phiên bản MySQL là chạy nhiều tiến trình dịch vụ MySQL khác nhau trên cùng một máy chủ, mỗi tiến trình lắng nghe trên một cổng dịch vụ khác nhau (ví dụ: 3306, 3307) và sử dụng các socket khác nhau. Các phiên bản có thể khác nhau về phiên bản MySQL hoặc cùng một phiên bản.

• Lợi ích của đa phiên bản

  Có thể tận dụng hiệu quả tài nguyên máy chủ. Khi tài nguyên của một máy chủ còn dư, có thể sử dụng phần dư này để cung cấp nhiều dịch vụ hơn, và có thể thực hiện cách ly logic tài nguyên để tiết kiệm tài nguyên. Ví dụ, tài nguyên máy chủ của công ty khan hiếm, nhưng cơ sở dữ liệu cần cung cấp dịch vụ độc lập, và cần sao chép master-slave, đa phiên bản là lựa chọn tốt nhất.

• Nhược điểm của đa phiên bản

  Tồn tại vấn đề tranh giành tài nguyên. Ví dụ, khi một phiên bản cơ sở dữ liệu có độ đồng thời cao hoặc truy vấn SQL chậm, phiên bản đó sẽ tiêu thụ nhiều tài nguyên CPU, I/O đĩa, làm giảm chất lượng dịch vụ của các phiên bản khác trên máy chủ. Do đó, nhu cầu cụ thể phụ thuộc vào tình hình thực tế.

3.2. Triển khai đa phiên bản

Sử dụng ít trong môi trường sản xuất.

Phần 4: Ngôn ngữ SQL

1.1. Các thành phần phổ biến của cơ sở dữ liệu quan hệ

• Cơ sở dữ liệu: databases
• Bảng: table, hàng: row, cột: column
• Chỉ mục: index
• View: view
• Thủ tục lưu trữ: procedure
• Hàm lưu trữ: function
• Trigger: trigger
• Lên lịch sự kiện: event scheduler
• Người dùng: user
• Quyền: privilege

1.1. Quy tắc ngôn ngữ SQL

• Tiêu chuẩn SQL:

  -- Ghi chú một dòng, chú ý có khoảng trắng
  -- Nội dung ghi chú   
  /* Ghi chú nhiều dòng
  Nội dung ghi chú
  Nội dung ghi chú*/
  

• Ghi chú trong MySQL:

  #Nội dung ghi chú