Quản trị MySQL cơ bản

Sử dụng các công cụ cơ bản để quản trị, giám sát hoạt động của MySQL như mysqladmin, mysqlcheck, mysql, mysqldump, mysqlimport

Để quản trị MySQL server có nhiều công cụ, tùy mục đích có thể sử dụng ngay các trình quản lý đi cùng với hệ thống MySQL như mysql, mysqladmin, mysqldump, mysqlimport ..., những công cụ này tương tác với MySQL Server trên giao diện dòng lệnh (CLI), giống nhau cả trên Linux và Windows.

Nếu muốn tương tác với MySQL Server mà chưa có những công cụ này (MySQL Client) thì cài đặt như sau: (MySQL Client chứ không phải MySQL Server)

# CÀI ĐẶT TRÊN CENTOS / RHEL
yum install mysql -y

# CÀI ĐẶT TRÊN UBUNTU / DEBIAN ...
apt install mysql-client

Phần này tìm hiểu cơ bản sử dụng các công cụ ở trên để cấu hình, quản trị MySQL. Những lệnh này thi hành bằng cách gõ lệnh với tham số phù hợp (gõ tại dòng lệnh cmd của Linux, Windows), thường các công cụ trên có yêu cầu cung cấp user/password với quyền phù hợp để giao kết nối đến MySQL, để chỉ ra user thêm vào tham số -u theo sau là tên user, để chỉ ra password thêm vào tham số -p theo sau (không khoảng trắng) là password (nếu sau -p không có chuỗi password thì khi thi hành nó sẽ yêu cầu nhập.

Ví dụ, dùng mysql kết nối, có chỉ ra đầy đủ user/password là root/123456

mysql -u root -p123456

Khi thi hành lệnh trên, nếu đúng user/password thì sẽ có dòng nhắc nhập các truy vấn mong muốn đến MySQL. Nếu thi hành lệnh mà không chỉ ra password ở dòng lệnh, thì nó sẽ nhắc để nhập vào ví dụ:

mysql -u root -p
Enter password:

Nếu muốn thi hành bằng cách kết nối tới một Remote thì thêm vào tham số -h tiếp theo là IP (host) của Server MySQL

mysql -u root -p -h 123.123.123.12
Enter password:

Thậm chỉ cần chỉ ra cổng kết nối thì dùng tham số -P 3306

Sử dụng mysqladmin quản trị MySQL Server

mysqladmin là công cụ hữu ích dành cho việc quản trị, nó có thể thi hành nhiều loại tác vụ với MySQL như: tạo, xóa một CSDL, đổi password ... Các thiết lập với tool này xem bằng lệnh:

mysqladmin --help

Trong thông tin hiện thị có cả đường dẫn đến các file thiết lập my.cnf

Đặt lại password cho root

Password mới đặt cho root nhập trong cặp '' sau tham số password

mysqladmin -u root -p password 'newpassword'

Thông tin về MySQL Server

Sử dụng tham số status để hiện thị trạng thái tóm tắt về MySQL Server, ví dụ kết nối với user là root, pass của nó là rootpassword

mysqladmin -u root -prootpassword status

--Kết quả
Uptime: 1087632  Threads: 3  Questions: 11088072  Slow queries: 0  Opens: 114138  Flush tables: 3  Open tables: 3826  Queries per second avg: 10.194

Kiểm tra phiên bản MySQL Server

mysqladmin -u root -prootpassword version

Các tiến trình đang tương tác với MySQL

mysqladmin -u root -pyourpassword processlist

Kết quả dạng bảng

+-------+----------+-------------+------+---------+-------+-------+------------------+
| Id    | User     | Host        | db   | Command | Time  | State | Info             |
+-------+----------+-------------+------+---------+-------+-------+------------------+
| 1453  | root     | localhost   |      | Sleep   | 15386 |       |                  |
| 4687  | user1    |      :60230 | mydb | Sleep   | 561   |       |                  |
| 4688  | user2    |      :60231 | mydb | Sleep   | 561   |       | select * from pos|
| 5407  | user2    |      :60309 |      | Sleep   | 14525 |       |                  |
| 22792 | root     | localhost   |      | Query   | 0     | init  | show processlist |
+-------+----------+-------------+------+---------+-------+-------+------------------+

Các cột thông tin như Id là ID của tiến trình đang tương tác với MySQL, có thể kill tiến trình thông qua ID này. Info chứa thông tin lệnh truy vấn, ví dụ các câu lệnh Select, Insert ...

Nếu muốn cứ 1 giây lại cập nhật thông tin này một lần thì thêm tham số -i 1

mysqladmin -u root -pyourpassword processlist -i 1

Kill process đang truy cập MySQL

Lấy ID các process bằng lệnh trên, nếu muốn kill cái nào (sleep hoặc đang làm quá tải ...), ví dụ kill tiến trình có ID 1453g

mysqladmin -u root -pyourpassword kill 1453

Lấy giá trị các biến MySQL

Hiện thị giá trị các biến lưu trạng thái dùng lệnh:

mysqladmin -u root -pyourpassword extended-status

Hiện thị giá trị các biến thiết lập (các biến này bạn có thể thiết lập trong file cấu hình, bằng lệnh ...):

mysqladmin -u root -pyourpassword variables

Xóa một số thông tin cache

Khi MySQL hoạt động có nhiều thông tin lưu tạm, cache chờ xử lý, nếu muốn xử lý ngay và xóa khỏi cache thì thi hành một số tham số như:

mysqladmin -u root -pyourpassword   flush-hosts       # xóa cached host
mysqladmin -u root -pyourpassword   flush-logs        # xóa logs
mysqladmin -u root -pyourpassword   flush-status      # xóa các biến trạng thái
mysqladmin -u root -pyourpassword   flush-tables      # xóa cached bảng
mysqladmin -u root -pyourpassword   flush-threads     # cache thread
mysqladmin -u root -pyourpassword   flush-privileges  # tải lại phân quyền

Tạo Database

Tạo một CSDL có tên mydb:

mysqladmin -u root -pyourpassword create mydb

Có thể liệt kê các database có trong server bằng lệnh:

mysqlshow -u root -pyourpassword

Xóa Database

Xóa một CSDL có tên mydb:

mysqladmin -u root -pyourpassword drop mydb

mysqlcheck - kiểm tra, sửa chữa, tối ưu Database

Trong quá trình vận hành MySQL server, có thể có nhiều nguyên nhân dẫn tới lỗi CSDL, ví dụ bị crash ..., để kiểm tra, sửa chữa hay tối ưu ta dùng đến công cụ mysqlcheck

Kiểm tra tình trạng CSDL

Nếu muốn kiểm tra một database có tên mydb thi hành lệnh sau:

mysqlcheck -u root -pyourpassword -a mydb

Thi hành lệnh trên, nó sẽ kiểm tra tất cả các bảng của mydb và hiện thi thông tin cho biết có lỗi gì trên các bảng đó hay không

Nếu muốn kiểm tra tất cả các database thi hành lệnh sau:

mysqlcheck -u root -pyourpassword --all-databases

Tối ưu cơ sở dữ liệu

Để tối ưu (optimize) thêm vào tham số -o, ví dụ tối ưu database có tên mydb

mysqlcheck -u root -pyourpassword -o mydb

Để kiểm tra và tối ưu toàn bộ các database trong server

mysqlcheck -u root -pyourpassword -o --all-databases

Sửa chữa, tối ưu

Để kiểm tra, tự động sửa chữa và và tối toàn bộ các database chạy lệnh sau:

mysqlcheck -u root -pyourpassword --auto-repair -o --all-databases

mysqldump - sao lưu database

Công cụ này sẽ sao database ra một file để lưu trữ, file lưu trữ có các dòng lệnh SQL và dữ liệu mà việc chạy các lệnh đó sẽ phụ hồi lại dữ liệu ban đầu (sử dụng mysql hoặc mysqlimport).

Ví dụ để sao lưu một database có tên mydb lưu ra file có tên backup.sql chạy lệnh như sau:

mysqldump -u root -pyourpassword mydb > backup.sql

Lưu ý nếu file backup.sql đang tồn tại nó sẽ bị ghi đè bởi dữ liệu mới, nếu muốn mỗi lần sao lưu thêm vào tên file backup thành phần là năm, tháng, ngày, giờ, phút, giây lúc sao lưu thì thêm vào tên file thành phần sau -$(date +%Y-%m-%d_%H_%M_%S)

mysqldump -u root -pyourpassword mydb > backup-$(date +%Y-%m-%d_%H_%M_%S).sql

Có thể backup toàn bộ các database có trong server ra một file với tham số --all-databases

mysqldump -u root -pyourpassword --all-databases > alldb-$(date +%Y-%m-%d_%H_%M_%S).sql

Hoặc là chỉ backup một bảng nào đó của một database, ví dụ bảng user của mydb

mysqldump -u root -pyourpassword mydb user > user-$(date +%Y-%m-%d_%H_%M_%S).sql

mysqlimport - phục hồi database

mysqlimport được dùng để phục hồi, ví dụ phục hồi lại mydb từ file mybackup.sql

mysqlimport -u root -pyourpassword mydb mybackup.sql

Ngoài ra cũng có thể dùng tool mysql để phục hồi, ví dụ trên nếu dùng mysql

mysql -u root -pyourpassword mydb < mybackup.sql

mysql - thi hành câu lệnh SQL

mysql là công cụ thông dụng, có thể chạy trực tiếp các câu lệnh SQL (xem SQL cơ bản)

Kết nối đến MySQL Server bằng tiện ích mysql như sau:

# mysql -u root -pyourpassword

Sau khi kết nội, có dấu nhắc mysql> nơi bạn nhập các câu lệnh SQL (Select, Insert, ...), mỗi câu lệnh viết xong, kết thúc bằng ; và nhấn Enter để thi hành.

Muốn thoát hẳn khỏi mysql chạy lệnh exit;

Từ dấu nhắc lệnh có dạng mysql> có thể nhập bất kỳ loại câu lệnh SQL nào và thi hành nó.

Xem danh sách các database

mysql> show databases;

Chuyển database hiện tại

Trước khi thi hành được các lệnh SQL, phải cho biết các lệnh SQL đó tác động nên database nào, ở một thời điểm có thể chọn một database đang sử dụng bằng lệnh use, ví dụ chuyển sang làm việc với database có tên mydb

mysql> use mydb;

Xem các bảng có trong database hiện tại

mysql> show tables;

Xem cấu trúc bảng

Ví dụ xem cấu trúc bảng user

mysql> describe user;

+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| user_id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| username       | varchar(100) | NO   | MUL | NULL    |                |
| password       | varchar(128) | NO   | MUL | NULL    |                |
| email          | varchar(255) | NO   | MUL | NULL    |                |

Các index của bảng

mysql> show index from user;

Tương tự, bất kỳ câu lệnh SQL nào có thể thi hành ở đây theo cách tương tự, ví dụ:

mysql> select * from user;

Các thành phần hệ thống như cấu trúc bảng, các CSDL, các user ... được MySQL bố trí vào các database đặc biệt như: mysql, information_schema bằng cách truy vấn (select, update ...) đến các bảng của các DB này ta có thể thiết lập cũng như đọc tham số hệ thống chung.

Ví dụ

Các user trong MySQL

mysql> select User,Host,Password from mysql.user;

Cột Password chứa mã hóa bằng hàm PASSWORD(pass-text).

Thêm một user

mysql> CREATE USER 'newusername'@'localhost' IDENTIFIED BY 'newpassword';

LƯU Ý: Khi thêm User trên MySQL 8 - thư viện PHP có thể chưa hỗ trợ cơ chế xác thực mới - nên bạn cần tạo ra user với xác thực password cũ

mysql> CREATE USER 'newusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';

Nếu muốn đổi kiểu xác thực user đã có như kiểu cũ thì:

mysql> ALTER USER 'newusername'@'localhost' IDENTIFIED WITH mysql_native_password BY 'newpassword';

Phân quyền truy cập cho user vào mydb

mysql> GRANT ALL PRIVILEGES ON mydb.* TO 'newusername'@'localhost';

Các biến trong MySQL có thể xem bằng lệnh SHOW VARIABLES

Kiểm tra Version

mysql> SHOW VARIABLES LIKE "%version%";