Hướng dẫn cài đặt sử dụng MySQL và một số kinh nghiệm giúp tối ưu hiệu năng tới database

Đã đăng Bởi Lập Trình Blockchain trong 2022-11-28 0 Phản hồi

Hướng dẫn cài đặt và sử dụng MySQL

Chia sẻ bài viết

MySQL là một hệ quản trị cơ sở dữ liệu phổ biến và được sử dụng khá nhiều hiện nay. Các ứng dụng web lớn nhất như Facebook, Twitter, YouTube, Google, và Yahoo! đều dùng MySQL cho mục đích lưu trữ dữ liệu. Kể cả khi ban đầu nó chỉ được dùng rất hạn chế nhưng giờ nó đã tương thích với nhiều hạ tầng máy tính quan trọng như Linux, macOS, Microsoft Windows, và Ubuntu.

Mục lục

Cài đặt

MySQL là hệ quản trị cơ sở dữ liệu mã nguồn mở được sử dụng khá rộng rãi.

Trên Windows

Bạn tải trực tiếp về và cài đặt bình thường: Tải MySQL cho Windows

User mặc định cho MySQL sau khi cài đặt là root và không có mật khẩu.

Trên Ubuntu

Cài đặt MySQL server bằng lệnh sau:

sudo apt-get install mysql-server -y

Cài đặt thêm mysql-cli để kết nối đến bằng lệnh:

sudo apt-get install mysql-client -y

Cài đặt xong bật/tắt bằng lệnh của service:

sudo service mysql start
sudo service mysql stop
service mysql status

Tùy từng phiên bản, có trường hợp sau:

Mặc định mysql chạy ở chế độ không kiểm tra quyền, bạn muốn bật chế độ xác thực quyền thì sử dụng lệnh sau:
sudo mysql_secure_installation
Một số trường hợp, User mặc định cho MySQL sau khi cài đặt là root và không có mật khẩu. Nếu vẫn không thể truy cập được, bạn có thể xem hướng dẫn xử lý tại mục: ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

Kết nối tới MySQL

Kết nối qua Terminal

Yêu cầu bạn phải cài gói mysql-client. Bật Terminal, kết nối bằng lệnh sau:

mysql -u root -p

Nhập password của root. Sau đó bạn có thể sử dụng tất cả các lệnh SQL. Ví dụ:

SHOW databases;
CREATE DATABASE dbname character set UTF8 collate utf8_bin;
USE dbname;
CREATE TABLE example ( id smallint unsigned not null auto_increment, name varchar(20) not null, constraint pk_example primary key (id) );
INSERT INTO example ( id, name ) VALUES ( null, 'Sample data' );

Kết nối qua MySQL Workbench

MySQL Workbench là công cụ giao diện rất mạnh để thao tác với CSDL MySQL.

Bạn có thể sử dụng để xuất biểu đồ EER: Create ER Diagram of an existing database using MySQL Workbench

Backup/Restore dữ liệu

Backup dữ liệu

Lệnh sau thực hiện Backup dữ liệu của database có schema db_test:

mysqldump -u root -p db_test > db_test.sql

Bạn có thể xuất dữ liệu của một bảng bằng lệnh sau:

mysqldump -u root -p db_test tbl_users_info > users_info.sql

Lệnh sau sẽ xuất cấu trúc dữ liệu với tham số sử dụng tương đối đầy đủ, không xuất dữ liệu:

mysqldump --host=localhost --port=3306 --user=root --password=123456a@ --protocol=tcp --default-character-set=utf8 --no-tablespaces --routines --events --no-data --result-file=db_test_structure.sql "db_test"

Lệnh sau sẽ xuất chỉ dữ liệu:

mysqldump --host=localhost --port=3306 --user=root --password=123456a@ --protocol=tcp --default-character-set=utf8 --no-tablespaces --routines --events --no-create-info=TRUE --result-file=db_test_data.sql "db_test"

Nếu bạn muốn khi backup nén luôn tệp sql này cho giảm dung lượng:

mysqldump -u root -p db_test| gzip > gb_test.sql.gz

Thông thường để cho tiện khi backup, tôi thường tạo ra Shell Script như sau để giúp:

Biết thời gian bắt đầu và kết thúc của backup
Tự động tạo tên file theo ngày

Tệp backup_db.sh:

#!/bin/bash

CURR_DAY=$(date +"%Y-%m-%d")
OUTPUT_FILE="db_test_dump_${CURR_DAY}.sql.gz"
echo "Output filename: ${OUTPUT_FILE}"

date
mysqldump -u root -p db_test | gzip > ${OUTPUT_FILE}
date

Khi chạy shell này nên chạy trong TMUX.

Restore dữ liệu

Để import dữ liệu, đầu tiên bạn phải tạo mới schema, sau đó sử dụng lệnh sau để import:

mysql -u root -p db_test < db_test.sql

Trong trường hợp tệp đã nén bằng gzip thì bạn sử dụng thêm tiện ích zcat hoặc pv để import. Bạn nên cài đạt nó trước nếu chưa có:

sudo apt install pv

sudo apt install zcat

Nếu bạn muốn import từ tệp gz thì bạn sử dụng lệnh sau:

zcat db_test.sql.gz | mysql -u root -p db_test

pv db_test.sql.gz | gunzip | mysql -u root -p db_test

Cũng giống như backup, tôi hay tạo ra tệp Shell để tiện sử dụng
Tệp restore_db.sh:

#!/bin/bash

INPUT_FILE=$1
echo "Input filename: ${INPUT_FILE}"

date
# Use pv to show progress (sudo apt install pv)
pv ${INPUT_FILE} | gunzip | mysql -u root -p db_test
date

Sử dụng Xtrabackup

Giới thiệu

MySQL có cơ chế “Checkpoint” nhưng cơ chế này chỉ làm việc trong 01 transaction, nên không thể sử dụng cho việc restore được. Để tăng tốc độ Backup/Restore, đặc biệt với CSDL lớn, chúng ta sử dụng công cụ của bên thứ ba, đó là Xtrabackup.
Đây là phần mềm miễn phí mã nguồn mở cho phép sao lưu realtime mà không chặn giao dịch. Phần mềm này có ưu điểm sau:

Làm việc với CSDL InnoDB và XtraDB.
Sao lưu đáng tin cậy và thời gian sao lưu nhanh
Xử lý giao dịch không bị gián đoạn trong quá trình sao lưu
Tiết kiệm không gian đĩa và băng thông mạng
Xác minh sao lưu tự động
Thời gian khôi phục nhanh (Với CSDL dữ liệu lớn, restore bằng MySQL mất 2h nhưng dùng XtraBackup chỉ mất 10 phút).

Về bản chất, Xtrabackup thực hiện sao lưu trực tiếp các tệp dữ liệu của mysql.

Cài đặt trên Ubuntu

Sử dụng lệnh sau để cài đặt:

wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release_sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release_sc)_all.deb
sudo apt-get update
sudo apt-get install percona-xtrabackup-24

Nếu bạn muốn remove, sử dụng lệnh sau:

sudo apt-get remove percona-xtrabackup-24

Backup database

Lệnh backup CSDL sử dụng Xtrabackup như sau:

innobackupex --user=DBUSER --password=SECRET --parallel=4 --use-memory=4G /path/to/backup/dir/
innobackupex --user=root --password=123456a@ --parallel=4 --use-memory=4G /home/mysql/backup

Lệnh này nên chạy trên user chạy mysql để tránh việc gặp lỗi liên quan đến permission khi truy cập vào các folder của mysql.

Nếu bạn muốn backup chỉ 01 schema nào đó thì thêm tham số “–databases”:

innobackupex --user=root --password=123456a@ --parallel=4 --use-memory=4G --databases='db_test' /home/mysql/backup

Về bản chất, quá trình backup là quá trình copy dữ liệu trong thư mục “/var/lib/mysql” sang thư mục mới.

Restore database

Nếu bạn chưa sử dụng thành thạo tiện ích, thì bạn nên backup trước bằng lệnh:
sudo cp -r /var/lib/mysql /var/lib/mysql_old

Trước khi restore thì cần stop dịch vụ bằng lệnh:
sudo service mysqld stop

Sau đó sử dụng lệnh sau để restore:
sudo innobackupex –copy-back /home/mysql/backup

Cuối cùng start lại dịch vụ
sudo service mysqld start

Chú ý: Trên môi trường cấu hình Master-Master, restore chỉ có tác dụng trên server thực hiện restore chứ dữ liệu không được đồng bộ sang server còn lại. Vì thế trước khi thực hiện cần:

Dừng cấu hình Master – Master
Thực hiện restore trên cả hai server
Sau đó bật lại cấu hình Master – Master.

Cấu hình Master-Master, Master-Slave

Cấu hình Master-Master

…

Cấu hình Master-Slave

…

Thủ thuật cải thiện hiệu năng

Để ý tới việc đánh INDEX

Đây là vấn đề cơ bản nhất ai cũng biết. Về thao tác đánh INDEX thì tôi không nói ở đây. Ở đây tôi đưa ra một số chú ý:

Việc đánh INDEX giúp truy vấn nhanh hơn nhưng cũng làm thao tác INSERT hoặc UPDATE chậm đi, đồng thời làm tăng dung lượng lưu trữ. Nên tối ưu INDEX, chỉ dùng trong trường hợp cần thiết.
Nếu đánh INDEX cho nhiều trường ví dụ (A, B, C) thì trong lệnh WHERE các điều kiện phải theo đúng thứ tự này. Nếu lệnh WHERE sai thứ tự thì index này sẽ không được sử dụng.
Nếu đánh INDEX cho nhiều trường (A, B, C) thì index này có thể sử dụng cho điều kiện chỉ có trường A, hoặc có trường A, B theo đúng thứ tự. Index này sẽ không được sử dụng cho B hoặc C.
Trong MySQL có lệnh EXPLAIN rất hay, để kiểm tra xem lệnh SQL theo sau sử dụng INDEX nào (Chi tiết xem bài: Sử dụng EXPLAIN để tối ưu câu lệnh MySQL):
EXPLAIN <Lệnh_SQL>

Xử lý theo lô

Đối với hệ thống lớn, giao dịch nhiều, các lệnh INSERT/UPDATE lên gom lại xử lý theo lô thông qua TRANSACTION. Việc xử lý này tăng tốc độ của hệ thống lên hàng trăm đến hàng nghìn lần, đặc biệt là với hệ thống có dữ liệu giao dịch liên tục.

Kết hợp INSERT/UPDATE trong cùng một lệnh

Nhiều trường hợp bạn cần kiểm tra một giao dịch đã tồn tại chưa, nếu chưa thực hiện insert, nếu đã có thì thực hiện update, theo luồng thông thường khi lập trình mọi người sẽ làm như sau:

SELECT trong CSDL
Kiếm tra dữ liệu sau SELECT:
- Có bản ghi thực hiện UPDATE
- Không có bản ghi thực hiện INSERT

Việc này làm chậm thao tác đi rất nhiều. Trong trường hợp này bạn nên sử dụng kết hợp INSERT với UPDATE trong một lệnh theo cú pháp dạng:
INSERT INTO … ON DUPLICATE KEY UPDATE …

Ví dụ 1: Hoặc là INSERT hoặc thực hiện UPDATE

INSERT INTO `customer_orders` (
	`sequence_number`,`order_id`, `order_book_time`, `order_time`, `update_time`,
	`currency_pair_cd`, `side`, `open_close`, `order_type`, `fill_condition`,
	`order_quantity`, `filled_quantity`, `remaining_quantity`, `order_price`, `order_status`,
	`created_time`, `is_new`, `client_order_id`)
VALUES (
	223456754, '2019091710200084239', '2019-09-17 11:59:34.501', '2019-09-17 11:59:34.501', '2019-09-17 12:01:25.801',
	'BTC_USD', '1', '1', '2', '3',
	10000, 9977.4818, 22.5182, 9500, '1',
	'2019-09-17 11:59:34.501', '1', 'HA-1568721573572-00'
)
ON DUPLICATE KEY UPDATE
	`sequence_number` = VALUES(`sequence_number`),
	`order_book_time` = VALUES(`order_book_time`),
	`order_time` = VALUES(`order_time`),
	`update_time` = VALUES(`update_time`),
	`currency_pair_cd` = VALUES(`currency_pair_cd`),
	`side` = VALUES(`side`),
	`open_close` = VALUES(`open_close`),
	`order_type` = VALUES(`order_type`),
	`fill_condition` = VALUES(`fill_condition`),
	`order_quantity` = VALUES(`order_quantity`),
	`filled_quantity` = VALUES(`filled_quantity`),
	`remaining_quantity` = VALUES(`remaining_quantity`),
	`order_price` = VALUES(`order_price`),
	`order_status` = VALUES(`order_status`),
	`created_time` = VALUES(`created_time`),
	`is_new` = '0',
	`client_order_id` = VALUES(`client_order_id`);

Ví dụ 2: Thực hiện INSERT, nếu lỗi thì thực hiện UPDATE với điều kiện trường sequence_number phải lớn hơn dữ liệu đang có:

INSERT INTO `customer_orders` (
	`sequence_number`,`order_id`, `order_book_time`, `order_time`, `update_time`,
	`currency_pair_cd`, `side`, `open_close`, `order_type`, `fill_condition`,
	`order_quantity`, `filled_quantity`, `remaining_quantity`, `order_price`, `order_status`,
	`created_time`, `is_new`, `client_order_id`)
VALUES (
	223456754, '2019091710200084239', '2019-09-17 11:59:34.501', '2019-09-17 11:59:34.501', '2019-09-17 12:01:25.801',
	'BTC_USD', '1', '1', '2', '3',
	10000, 9977.4818, 22.5182, 9500, '1',
	'2019-09-17 11:59:34.501', '1', 'HA-1568721573572-00'
)
ON DUPLICATE KEY UPDATE
	`sequence_number` = IF(VALUES(`sequence_number`) < sequence_number, sequence_number, VALUES(`sequence_number`)),
	`order_book_time` = IF(VALUES(`sequence_number`) < sequence_number, order_book_time, VALUES(`order_book_time`)),
	`order_time` = IF(VALUES(`sequence_number`) < sequence_number, order_time, VALUES(`order_time`)),
	`update_time` =IF(VALUES(`sequence_number`) < sequence_number, update_time, VALUES(`update_time`)),
	`currency_pair_cd` = IF(VALUES(`sequence_number`) < sequence_number, currency_pair_cd, VALUES(`currency_pair_cd`)),
	`side` = IF(VALUES(`sequence_number`) < sequence_number, side, VALUES(`side`)),
	`open_close` = IF(VALUES(`sequence_number`) < sequence_number, open_close, VALUES(`open_close`)),
	`order_type` = IF(VALUES(`sequence_number`) < sequence_number, order_type, VALUES(`order_type`)),
	`fill_condition` = IF(VALUES(`sequence_number`) < sequence_number, fill_condition, VALUES(`fill_condition`)),
	`order_quantity` = IF(VALUES(`sequence_number`) < sequence_number, order_quantity, VALUES(`order_quantity`)),
	`filled_quantity` = IF(VALUES(`sequence_number`) < sequence_number, filled_quantity, VALUES(`filled_quantity`)),
	`remaining_quantity` = IF(VALUES(`sequence_number`) < sequence_number, remaining_quantity, VALUES(`remaining_quantity`)),
	`order_price` = IF(VALUES(`sequence_number`) < sequence_number, order_price, VALUES(`order_price`)),
	`order_status` = IF(VALUES(`sequence_number`) < sequence_number, order_status, VALUES(`order_status`)),
	`created_time` = IF(VALUES(`sequence_number`) < sequence_number, created_time, VALUES(`created_time`)),
	`is_new` = '0',
	`client_order_id` = IF(VALUES(`sequence_number`) < sequence_number, client_order_id, VALUES(`client_order_id`));

Ví dụ 3: Trong lệnh có thể sử dụng nhiều dữ liệu

INSERT INTO `customer_orders` (
	`sequence_number`,`order_id`, `order_book_time`, `order_time`, `update_time`,
	`currency_pair_cd`, `side`, `open_close`, `order_type`, `fill_condition`,
	`order_quantity`, `filled_quantity`, `remaining_quantity`, `order_price`, `order_status`,
	`created_time`, `is_new`, `client_order_id`)
VALUES 
	(223456754, '2019091710200084239', '2019-09-17 11:59:34.501', '2019-09-17 11:59:34.501', '2019-09-17 12:01:25.801', 'BTC_USD', '1', '1', '2', '3', 10000, 9977.4818, 22.5182, 9500, '1', '2019-09-17 11:59:34.501', '1', 'HA-1568721573572-00'),
	(223456755, '2019091710200084239', '2019-09-17 11:59:34.501', '2019-09-17 11:59:34.501', '2019-09-17 12:01:25.808', 'BTC_USD', '1', '1', '2', '3', 10000, 9992.7884, 7.2116, 9500, '1', '2019-09-17 11:59:34.501', '1', 'HA-1568721573572-00'),
	(223456756, '2019091710200084239', '2019-09-17 11:59:34.501', '2019-09-17 11:59:34.501', '2019-09-17 12:01:25.819', 'BTC_USD', '1', '1', '2', '3', 10000, 10000, 0, 9500, '2', '2019-09-17 11:59:34.501', '1', 'HA-1568721573572-00')
ON DUPLICATE KEY UPDATE
	`sequence_number` = IF(VALUES(`sequence_number`) < sequence_number, sequence_number, VALUES(`sequence_number`)),
	`order_book_time` = IF(VALUES(`sequence_number`) < sequence_number, order_book_time, VALUES(`order_book_time`)),
	`order_time` = IF(VALUES(`sequence_number`) < sequence_number, order_time, VALUES(`order_time`)),
	`update_time` =IF(VALUES(`sequence_number`) < sequence_number, update_time, VALUES(`update_time`)),
	`currency_pair_cd` = IF(VALUES(`sequence_number`) < sequence_number, currency_pair_cd, VALUES(`currency_pair_cd`)),
	`side` = IF(VALUES(`sequence_number`) < sequence_number, side, VALUES(`side`)),
	`open_close` = IF(VALUES(`sequence_number`) < sequence_number, open_close, VALUES(`open_close`)),
	`order_type` = IF(VALUES(`sequence_number`) < sequence_number, order_type, VALUES(`order_type`)),
	`fill_condition` = IF(VALUES(`sequence_number`) < sequence_number, fill_condition, VALUES(`fill_condition`)),
	`order_quantity` = IF(VALUES(`sequence_number`) < sequence_number, order_quantity, VALUES(`order_quantity`)),
	`filled_quantity` = IF(VALUES(`sequence_number`) < sequence_number, filled_quantity, VALUES(`filled_quantity`)),
	`remaining_quantity` = IF(VALUES(`sequence_number`) < sequence_number, remaining_quantity, VALUES(`remaining_quantity`)),
	`order_price` = IF(VALUES(`sequence_number`) < sequence_number, order_price, VALUES(`order_price`)),
	`order_status` = IF(VALUES(`sequence_number`) < sequence_number, order_status, VALUES(`order_status`)),
	`created_time` = IF(VALUES(`sequence_number`) < sequence_number, created_time, VALUES(`created_time`)),
	`is_new` = '0',
	`client_order_id` = IF(VALUES(`sequence_number`) < sequence_number, client_order_id, VALUES(`client_order_id`));

Tách CSDL khi thực hiện báo cáo

Nói chung báo cáo đa dạng, và thường phải thao tác với lượng dữ liệu rất lớn và phải liên kết nhiều bảng. Thao tác này cực chậm, ảnh hưởng cực lớn tới hiệu năng của hệ thống.
Mặt khác thông thường báo cáo không nhất thiết cần dữ liệu realtime. Vì thế bạn nên:

Đồng bộ dữ liệu sang database khác hàng ngày thông qua các công cụ trung gian
Cài đặt Database theo cơ chế Master-Slave (Kiểu không đồng bộ) và thực hiện truy vấn trên CSDL Backup này.

Xử lý report theo cơ chế cache hàng ngày

Ngoài ra bạn có thể xử lý theo cơ chế cache dữ liệu tính toán hàng ngày:

Đầu ngày tiếp theo (Thời điểm ít người dùng) thực hiện xử lý tính toán của ngày hôm trước và lưu lại
Ngày sau khi tính realtime thì lấy dữ liệu tính ngày hôm trước và xử lý thêm với dữ liệu ngày hiện tại.

Backup dữ liệu cũ và không cần sử dụng

Thông thường với hệ thống lớn, chạy càng lâu thì dữ liệu càng lớn, hệ thống càng ì ạch. Để tăng tốc cho các thao tác Realtime chúng ta cần thực hiện:

Xác định các dữ liệu cũ (Như trước 3 năm), các dữ liệu không cần sử dụng lại, hoặc ít sử dụng => Thực hiện chuyển nó sang CSDL mới
Như vậy CSDL của chúng ta chia làm hai:
- CSDL Dữ liệu hiện tại:
  - Chứa dữ liệu hiện tại và hay được sử dụng
  - Có cơ chế backup thường xuyên.
- CSDL cũ ít dùng:
  - Chứa các dữ liệu cũ, dữ liệu ít dùng => Chỉ truy vấn liên quan tới history.
  - Chỉ cần sao lưu 1 lần, chứ ko cần backup thường xuyên.

Khi tác CSDL thì ứng dụng nên có cơ chế truy cập tới 2 DB này để đảm bảo không thay đổi thao tác của khách hàng.

Tối ưu cấu hình của MySQL

Có khá nhiều cấu hình nhưng tôi thấy cấu hình hiệu quả nhất là …

Một số lệnh SQL hay dùng

Lệnh SQL thao tác với Account

Tạo mới user

Tạo xong nên grant quyền luôn:

CREATE user 'test'@'%' identified by '123456a@';
GRANT ALL PRIVILEGES ON *.* TO 'test_db'@'%';
FLUSH PRIVILEGES;

Cập nhật thông tin user

Thông thường bạn hay phải thay đổi thông tin password hoặc host:

UPDATE mysql.user SET authentication_string=PASSWORD('123456a@'), host='%' WHERE user='root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Nếu host là ‘%’ tức là mọi nơi đều có thể truy cập vào được. Thường với account root chỉ nên để host=’localhost’ để đảm bảo an toàn.

Lệnh thao tác với Database

Hiển thị database bằng lệnh:

SHOW databases;

Tạo database bẳng lệnh:

CREATE DATABASE db_test character set UTF8 collate utf8_bin;

Lệnh sau thực hiện chọn một database để làm việc:

USE db_test;

Lệnh sau thực hiện xóa database:

DROP database db_test;

Lệnh thao tác thông tin Tables

Lệnh thao tác cơ bản với bảng

Hiển thị các bảng trong database hiện tại:

SHOW TABLES&nbsp;;

Hiển thị các bảng trong một database xác định:

SHOW TABLE FROM {database name}&nbsp;;

Xem cấu trúc một bảng:

DESC {table name}&nbsp;;

Xóa một bảng:

DROP {table name}&nbsp;;

Các lệnh CREATE/INSERT/SELECT/UPDATE/DELETE:

CREATE TABLE {table name} ( {column1} INT (2), {column2} VARCHAR(50) )&nbsp;;
INSERT INTO {table name} ({column1}, {column2}) VALUES ({value1} , {value2} );
SELECT * FROM {table name};
UPDATE {table name} SET [colum1]=[new_value1], [colum2]=[new_value2] WHERE&nbsp;[condition]&nbsp;;
DELETE FROM {table name} WHERE [condition]&nbsp;;

Lệnh lấy thông tin bảng cho vào tài liệu thiết kế

Lệnh sau lấy thông tin của 1 bảng trong CSDL:

SELECT	
	COLUMN_NAME, IFNULL(COLUMN_DEFAULT, "") AS COLUMN_DEFAULT, IS_NULLABLE, COLUMN_TYPE, COLUMN_KEY, EXTRA, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS	
WHERE TABLE_SCHEMA = 'db_test' AND TABLE_NAME = 'tbl_users_info';

Nếu cần lấy tất cả các trường dữ liệu thì dùng lệnh sau:

SELECT	
	COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.*
FROM INFORMATION_SCHEMA.COLUMNS	
WHERE TABLE_SCHEMA = 'bpe7x_margin' AND TABLE_NAME = 'bpex_customer_orders';

Lệnh liên quan Lock/Unlock bảng

Các lệnh này đều phải chạy với quyền root.
Lệnh sau kiểm tra xem bảng nào đang được sử dụng:
SHOW OPEN TABLES WHERE In_use > 0;

Lệnh sau kiểm tra xem các process đang chạy:
SHOW PROCESSLIST;

Để unlock bảng thì ta phải kill process đang thao tác tới bảng này. Danh sách trên hiển thị có ID, sử dụng ID này để KILL:
KILL xxxxxx;

Lệnh thao tác với JSON

MySQL hỗ trợ thao tác trực tiếp với trường dữ liệu kiểu String nhưng có format ở dạng JSON. Điều này giúp thao tác dữ liệu JSON dễ dàng hơn.

Giả sử bảng tbl_configs có trường data để lưu xâu dạng JSON. Dữ liệu JSON có trường FVType, FVFixedPrice. Lệnh truy vấn dạng như sau:

SELECT currency_pair_cd, type, (data->"$.FVType") AS FVType, (data->"$.FVFixedPrice") AS FVFixedPrice, time_created, user_id	
FROM tbl_configs	
WHERE type='pe'	
HAVING FVType=1	
ORDER BY time_created DESC LIMIT 200;

Chú ý:

Các trường không tính toán có thể để trong điều kiện WHERE
Các trường tính toán phải để trong HAVING

Một số lỗi liên quan

ERROR 1045 (28000): Access denied for user ‘root’@’localhost’ (using password: YES)

Thường lỗi này do bạn không nhớ mật khẩu của tài khoản root. Chẳng lẽ lại cài lại MySQL, liệu có cách nào đổi mật khẩu. Thực ra cài lại là một giải pháp, nhưng ở đây tôi hướng dẫn bạn giải pháp không cần phải cài lại:

Sửa tệp /etc/mysql/mysql.conf.d/mysqld.cnf bằng lệnh:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Thêm skip-grant-tables vào dưới [mysqld]
Sau đó restart mysql bằng lệnh:

sudo service mysql restart

Đến đây bạn kết nối qua Terminal mà không cần password để đăng nhập. Đánh lệnh sau để vào chế độ comand:

mysql
mysql> flush privileges;
mysql> use mysql;
mysql> SELECT host, user, authentication_string FROM user;

Kiểm tra nếu chưa có tài khoản root thì tạo mới bằng lệnh sau:

CREATE USER 'root'@'%' IDENTIFIED BY '123456a@';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Nếu đã có thì đổi lại mật khẩu bằng lệnh sau:

ALTER USER 'root'@'%' IDENTIFIED BY '123456a@';
FLUSH PRIVILEGES;

Đôi khi bạn phải thay % bằng localhost tùy vào trường host truy vấn ở trên. Nếu thấy báo lỗi “Plugin ‘auth_socket’ is not loaded” thì bạn có thể cập nhật lại bằng lệnh sau:

UPDATE user SET authentication_string=PASSWORD("123456a@") WHERE user='root';
UPDATE user SET plugin="mysql_native_password" WHERE user='root';
FLUSH privileges;

Sau khi xong sửa lại tệp /etc/mysql/mysql.conf.d/mysqld.cnf, xóa hoặc comment dòng vừa thêm “skip-grant-tables“.
Thực hiện restart lại mysql.

Lỗi sai thời gian sau khi import lại

Lỗi này thường xảy ra khi bạn import trên 1 server và restore trên một server khác có GMT khác.
Ví dụ: Bạn export database trên server ở Nhật với GMT=+9, sau đó về server Việt Nam thực hiện import lại, Việt Nam có GMT=+7, bạn sẽ thấy dữ liệu thời gian trong database bị lệch 2h.

Thông thường trong tệp SQL sau khi backup có phần header có chứa khai báo TIME_ZONE:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=’+00:00′ */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Trước khi import port bạn cần phải tăng lên tương ứng với thời gian lệch, tức cần sửa thành:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE=’-02:00′ */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Sau đó thực hiện import bình thường.

Thực ra với tệp nhỏ thì không có vấn đề gì, nhưng với tệp lớn thì sau, tôi gặp trường hợp cần xử lý với tệp SQL lên đến 35G dữ liệu, nói chung không có Editor nào chịu được file này.
Vì thế bạn phải dùng lệnh để sửa:

sed -i -e "s/TIME_ZONE='+00:/TIME_ZONE='-02:/g" test_db.sql

ERROR 1030 (HY000) at line 25: Got error 168 from storage engine

Lỗi này do permission thư mục chứa dữ liệu không đúng, cần chuyển về đúng và restart mysql bằng lệnh sau:

sudo chown -R mysql:mysql /var/lib/mysql
sudo service mysql restart

AVC apparmor=”DENIED” operation=”open”

Liên quan tới quyền các thư mục, do bạn chmod ở lần nào trước đó, khôi phục lại rất khó nên remove mysql cài lại:

Remove bằng lệnh sau:

sudo apt-get remove --purge *mysql\*
sudo apt-get autoremove
sudo apt-get autoclean

Nếu không được thì dùng lệnh sau để remove:

sudo -i
service mysql stop
killall -KILL mysql mysqld_safe mysqld
apt-get --yes purge mysql-server mysql-client
apt-get --yes autoremove --purge
apt-get autoclean
deluser --remove-home mysql
delgroup mysql
rm -rf /etc/apparmor.d/abstractions/mysql /etc/apparmor.d/cache/usr.sbin.mysqld /etc/mysql /var/lib/mysql /var/log/mysql* /var/log/upstart/mysql.log* /var/run/mysqld
updatedb
exit

Too many connections

Lỗi này do quá nhiều kết nối tới MySQL server. Mặc định số kết nối tối đa đến MySQL là 151 (Có thể thay đổi tùy từng phiên bản).
Trong trường hợp này bạn nên rà soát lại ứng dụng đảm bảo ứng dụng close kết nối. Nếu không đóng kết nối khi thoát ứng dụng, kết nối sẽ được giữ khoảng 7 ngày trước khi bị close. Điều này gây lãng phí tài nguyên của hệ thống.

Tôi từng biết đến ứng dụng NodeJs sử dụng gần 100 kết nối khi chạy, khi ứng dụng tắt lại không đóng kết nối, nên bật tắt vào lần thì hết kết nối. Trường hợp này nên bắt tín hiệu tắt ứng dụng để đóng kết nối.

Nếu ứng dụng của bạn sử dụng nhiều hơn thì cần phải cấu hình tăng số này lên. Cách thực hiện như sau (Có thể khác nhau tùy từng phiên bản):

Lệnh SQL sau kiểm tra số kết nối tối đa:
SHOW VARIABLE LIKE “max_connections”;

Lệnh sau cho phép sửa trực tiếp mà không phải restart MySQL, nhưng khi restart lại thì sẽ thành giá trị mặc định:
SET GLOBAL max_connections = 10000;

Cách khác là bạn sửa tệp /etc/mysql/mysql.conf.d:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
thay biến sau:
max_connections = 10000
max_connect_errors = 10000
Sau đó restart lại mysql.

Ngoài ra bạn có thể cấu hình biến wait_timeout để tối ưu hóa thời gian giữ kết nối. wait_timeout được tính bằng giây, nếu một connection không có hoạt động nào quá thời gian wait_time thì kết nối được server tự động đóng:

SHOW VARIABLES LIKE 'wait_timeout';
SET GLOBAL wait_timeout = 86400;

Node-Mysql throwing connection timeout

Lỗi này thường xảy ra do kết nối tới database quá chậm quá thời gian nên bị timeout. Mặc định thời gian này là 10 giây, nếu kết nối quá chậm bạn phải tăng thời gian này trong config
Ví dụ:

var config = {  
   host: '172.10.1.1',
   port: 3306,
   user: 'user',
   password: 'pwd',
   database: 'mydb',
   connectionLimit: 15,
   queueLimit: 30,
   acquireTimeout: 1000000
}

Invalid default value for ‘modified_time’

Khi thực hiện một lệnh ALTER TABLE để thay đổi một số trường trong bảng, bạn thấy hiển thị lỗi này do giá trị default của trường modified_time đang là ‘0000-00-00 00:00:00.000000′ và không đúng. MySQL coi xâu toàn 0 này là sai định dạng.

Có hai cách sửa lỗi này:

C1: Thay đổi lại giá trị Default của trường modified_time
ALTER TABLE user_wallets CHANGE COLUMN `modified_time` `modified_time` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ;
C2: Bỏ chế độ NO_ZERO_DATE trong MySQL
Bạn kiểm tra bằng lênh:
SELECT @@SQL_MODE;
Bạn sẽ thấy dữ liệu trả về là:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Bạn bỏ NO_ZERO_DATE và set lại bằng lệnh:
SET @@global.sql_mode =”ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION” ;
Hoặc sửa trực tiếp trong cấu hình MySQL (/etc/mysql/mysql.conf.d/mysqld.cnf)

max_connections luôn là 214 mặc dù đã tăng giá trị max_connections lên 10000

Việc thay đổi max_connections trong mysql như sau:

C1: Thay đổi trong cấu hình
Sửa tệp /etc/mysql/conf.d/mysql.cnf, thêm cấu hình max_connections:
[mysql]
max_connections=10000
Sau đó dùng lệnh sau để restart dịch vụ mysql:
sudo service mysql status
C2: Thay đổi trực tiếp trong khi chạy
SET GLOBAL max_connections = 10000;

Nhưng trong nhiều trường hợp, việc thay đổi này không có hiệu quả, nguyên nhân chính còn do cấu hình trong Ubuntu. Nó phụ thuộc vào giá trị “max opened files limitation” trên hệ điều hành hoặc cấu hình cho từng user.
Giá trị này được lấy như sau:

Giá trị sử dụng chung:
ulimit -n
Giá trị sử dụng cho từng user:
su mysql –shell /bin/bash –command “ulimit -n”

Mặc định giá trị này là 2014, tức requested_open_files=1024, trong mysql định nghĩa hằng số TABLE_OPEN_CACHE_MIN=400:
limit = requested_open_files – 10 – TABLE_OPEN_CACHE_MIN * 2 = 1024 – 10 – 400 * 2 = 214

Như vây, giới hạn là 214, cho dù tăng max_connections tới giá trị nào thì không vượt qua được số 214 này.

Để thay đổi giá trị giới hạn này, đầu tiên bạn cần sửa tệp /etc/pam.d/common-session thêm dòng sau:
session required pam_limits.so

Sau đó bạn cần tăng “max opened files limitation” lên bằng cách sửa tệp /etc/security/limits.conf:
sudo nano “max opened files limitation”
Thêm hai dòng sau để cấu hình cho tất cả user:
* hard nofile 10000
* soft nofile 10000
Hoặc cấu hình riêng cho từng user:
mysql hard nofile 10000
mysql soft nofile 10000

Chi tiết xem: <a href=”https://github.com/docker-library/mysql/issues/598” alt=”https://github.com/docker-library/mysql/issues/598” title=”https://github.com/docker-library/mysql/issues/598“>https://github.com/docker-library/mysql/issues/598</a>

Một số lệnh kiểm tra trên mysql:
show variables like ‘%open_files_limit%’;
show variables like ‘%max_connections%’;

Hoặc dùng lệnh sau trên mysql:
SHOW STATUS;
Kiểm tra các biến sau:

Connections: Số connection hiện tại
Max_used_connections: Số connection tối đa đã sử dụng
…

Tấn công CSDL MySQL

MySQL là một trong những cơ sở dữ liệu được sử dụng nhiều nhất ngày nay. Nên có nhiều trường hợp bị Hacker tấn công.

MySQL bị tấn công mã hóa database

Vào một ngày tháng 10/2020, khi phát hiện ứng dụng có vấn đề, kiểm tra database thì thấy toàn bộ bảng dữ liệu không còn, chỉ còn 1 bảng WARNING với nội dung là gửi Bitcoin để lấy lại CSDL:

Mặc dù CSDL này không có quá quan trọng để phải dùng Bitcoin để đổi lại, nhưng cũng ảnh hưởng ít nhiều đến công việc của bạn.

Làm thế nào để tấn công thì tôi chưa rõ những search trên mạng thấy:

Có trường hợp bị tấn công tương tự website Laravel qua lỗ hổng của PhpMyAdmin: Website Laravel bị tấn công mã hóa database. PhpMyAdmin có khá nhiều lỗ hổng bảo mật nên mọi người cẩn thận khi public nó.
Bài viết khác về cách tấn công MySQL: Tấn công MySQL sử dụng Metasploit

Check log error của MySQL thì thấy bị tấn công liên tiếp nhiều ngày, hacker tấn công theo kiểu BruteForce từ máy chủ ở Bulgaria: