Cơ bản
Login MySQL.
mysql -u root -p
Tạo user và Database.
create database dbcuaban;
create user 'usercuaban'@'localhost' identified by 'passcuaban';
grant all on dbcuaban.* to usercuaban@localhost;
FLUSH PRIVILEGES;
Tạo table.
CREATE TABLE ten_table(id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT, cot1 VARCHAR(20) NOT NULL Unique, cot2 VARCHAR(50) NOT NULL, cot3 DATETIME NOT NULL) ENGINE=InnoDB;
Chèn thêm 1 cột.
ALTER TABLE ten_table ADD COLUMN cot4 VARCHAR(300) NOT NULL;
Ghi dòng.
INSERT INTO ten_table(cot1,cot2,cot3,cot4) VALUES ('$value1','$value','$value3','$value4');
Update dòng 1
UPDATE ten_table SET cot1='$value1', cot2='$value2', cot3='$value3', cot4='$value4' where id = 1 limit 1;
Xóa dòng 1
Delete From ten_table where id = 1 limit 1;
Đếm số dòng
Select count(id) as total from ten_table;
Thêm chỉ mục Index.
ALTER TABLE ten_table ADD INDEX cot1 (cot1);
Drop Index
ALTER TABLE `Ten_Bang` DROP INDEX `Ten_cot_index`
Dump all database
mysqldump -u [uname] -p[pass] --all-databases | gzip > db_backup.sql.gz
Nâng cao.
Full text search
ALTER TABLE ten_table ADD FULLTEXT INDEX cot2 (cot2);
SELECT * FROM ten_table WHERE MATCH(cot2) AGAINST ('keyword' IN NATURAL LANGUAGE MODE);
Hoặc
Tạo Index FULLTEXT cho các cột muốn truy vấn
ALTER TABLE `TenBang` ADD FULLTEXT INDEX `CotFullText` (`Cot1`, `Cot2`);
Truy vấn
SELECT * FROM `TenBang` WHERE MATCH(`Cot1`, `Cot2`) AGAINST ('Tim cai gi do');
Xóa dữ liệu trùng nhau của dòng.
Đơn giản là add index là unique và bỏ qua lỗi, các dòng trùng nhau sẽ tự động xóa.
ALTER IGNORE TABLE ten_table ADD UNIQUE INDEX cot3 (cot3);
Replace trong MySQL
UPDATE `ten_table`
SET cot = REPLACE (cot, 'string_search', 'string_replace')
WHERE `cot` LIKE '%dieu_kien_gi_do%'
Update String Cột 1 sang Cột 2
UPDATE `ten_table` SET `Cot2`=LEFT(`Cot1`, 10);
Update nếu trùng nhau.
INSERT INTO ten_table(cot1,cot2,cot3,cot4) VALUES ('$value1','$value2','$value3','$value4') ON DUPLICATE KEY UPDATE cot1=VALUES(cot1), cot2=VALUES(cot2), cot3=VALUES(cot3), cot4=VALUES(cot4);
Lấy danh sách hàng có giá trị trùng nhau
SELECT name, COUNT(*) c FROM table GROUP BY name HAVING c > 1;
Tối ưu Select với Table hàng triệu dòng.
Thường các truy vấn có order và limit mà số dòng trong table lên đến hàng triệu thì truy vấn rất chậm, mẹo sau đây giúp tăng tốc độ truy vấn của bạn lên gấp nhiều lần.
SELECT t.* FROM (SELECT `id` FROM `ten_table` FORCE INDEX (ten_table) WHERE `cot1` like '$value1' ORDER BY `cot2` DESC LIMIT 0, 50) q JOIN `ten_table` t ON t.id = q.id;
Bình thường khi muốn tìm những dòng có timestamp là hôm nay chúng ta thường có truy vấn như sau:
SELECT * FROM Table WHERE DATE(`timestamp`) = CURDATE();
Nếu table chỉ với vài ngàn dòng thì không sao, nhưng khi đến cả triệu dòng thì truy vấn trên hơi chậm.
Chúng ta có thể đối thành truy vấn như sau:
SELECT * FROM Table WHERE timestamp BETWEEN '2018-12-07 00:00:01' AND '2018-12-07 23:59:59';
Hoặc
SELECT * FROM Table WHERE `timestamp` >= NOW() - INTERVAL 1 DAY
Tạo cache cho MySQL Query
Tối ưu cho VPS có ít ram
mysql -u root -p
Nhập mật khẩu SQL và kiểm tra tình trạng cache hiện tại bằng lệnh
show variables like 'query_cache_%';
Kết quả trả về tương tự như sau:
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
Chỉnh sửa trong file /etc/my.cnf (Red Hat) hoặc /etc/mysql/my.cnf (Debian)
nano /etc/my.cnf
Điều chỉnh lại theo ý bạn
query_cache_limit = 3M
query_cache_size = 128M
Sau khi thêm thì nội dung sẽ như sau:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
query_cache_limit = 3M
query_cache_size = 128M
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Khởi động lại mysql
service mysql restart
nếu bị lỗi mysql: unrecognized service thì sửa thành:
service mysqld restart
Kiểm tra lại bằng lệnh
show variables like 'query_cache_%';
Kết quả
+------------------------------+-----------+
| Variable_name | Value |
+------------------------------+-----------+
| query_cache_limit | 3145728 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 134217728 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+-----------+
Dòng lệnh xem và xóa user + database
Đôi khi có những website không còn hoạt động trên máy chủ và bạn cần xóa nó đi cho nhẹ nhàng thì đây là một số lệnh cơ bản.
mysql -u root -p
trong Centminmod nếu quên pass thì gõ
cat ~/.my.cnf
nhập mật khẩu của MySQL -> nếu đăng nhập thành công thì dấu nhắc sẽ giống thế này: mysql>
Xem danh sách Database:
SHOW DATABASES;
Xóa Database
DROP DATABASE ten_database_muon_xoa;
Xem danh sách User
SELECT User FROM mysql.user;
Xóa User
DROP USER 'testuser'@'localhost';
Reset mật khẩu MySQL trong CentOS
service mysql stop
sudo mysqld_safe --skip-grant-tables &
mysql -u root
use mysql;
update user set password=PASSWORD("PUT_A_NEW_PASSWD_HERE") where User='root';
flush privileges;
exit
service mysql stop
service mysql start
