Để 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
Mục lục
- Sử dụng mysqladmin quản trị MySQL Server
- Đặt lại password cho root
- Trạng thái MySQL Server
- Kiểm tra phiên bản MySQL Server
- Hiện thị các tiến trình đang kết nối với MySQL
- Kill tiến trình truy cập MySQL
- Xem các biến trạng thái, thiết lập MySQL
- Xóa một số thông tin cache
- Tạo một Database
- Xóa một Database
- Kiểm tra, sửa chửa, tối ưu database với mysqlcheck
- Sao lưu database với mysqldump
- Phục hồi database từ file với mysqlimport
- Thi hành lệnh SQL với mysql
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
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%";
