Để 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.

Phần này tìm hiểu cơ bản sử dụng các công cụ đó để 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 MySQ. 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:
,

Một lưu ý nữa là các tool trên sẽ thi hành với kết nối tới MySQL ở Local (cùng máy), 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 và phía sau là host kết nối (IP của server).

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

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

# mysqladmin -u root -pyourpassword status

--Kết quả
Uptime: 19582  Threads: 5  Questions: 145273  Slow queries: 46  Opens: 41041  Flush tables: 1  Open tables: 999  Queries per second avg: 7.418

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

# mysqladmin -u root -pyourpassword version

--Kết quả
Server version          5.6.29-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 5 hours 33 min 0 sec

Threads: 6  Questions: 147660  Slow queries: 46
Opens: 41046  Flush tables: 1  Open tables: 999
Queries per second avg: 7.390

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%";