development:database:mysql
This is an old revision of the document!
Table of Contents
1. Các lệnh cơ bản trong MySQL
1.1. Kiểm tra version MySQL
mysql> SELECT version(); +-----------+ | version() | +-----------+ | 8.3.0 | +-----------+ 1 ROW IN SET (0.00 sec)
1.2. Thao tác với Database
1.2.1. Hiển thị danh sách Databases
mysql> SHOW DATABASES; +------------------------+ | DATABASE | +------------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +------------------------+ 8 ROWS IN SET (0.04 sec)
1.2.2. Hiển thị thông tin database đã tạo
mysql> SHOW CREATE DATABASE test; +----------+--------------------------------------------------------------------------------------------------------------------------------+ | DATABASE | CREATE DATABASE | +----------+--------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ | +----------+--------------------------------------------------------------------------------------------------------------------------------+ 1 ROW IN SET (0.00 sec)
1.2.3. Tạo database mới
mysql> CREATE DATABASE `thutycoi` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; Query OK, 1 ROW affected (0.08 sec) mysql> SHOW DATABASES; +------------------------+ | DATABASE | +------------------------+ | bank_crawler | | information_schema | | mysql | | performance_schema | | sys | | template.1site.vn | | test | | test-template.1site.vn | | thutycoi | +------------------------+ 9 ROWS IN SET (0.03 sec)
1.2.4. Chọn Database cần sử dụng
mysql> USE thutycoi; DATABASE changed
1.2.5. Xoá database đã tạo
mysql> DROP DATABASE thutycoi; Query OK, 0 ROWS affected (0.12 sec) mysql> SHOW DATABASES; +------------------------+ | DATABASE | +------------------------+ | bank_crawler | | information_schema | | mysql | | performance_schema | | sys | | template.1site.vn | | test | | test-template.1site.vn | +------------------------+ 8 ROWS IN SET (0.39 sec)
1.3. Thao tác với table
1.3.1. Hiển thị thông tin table
mysql> SHOW TABLES; +----------------+ | Tables_in_test | +----------------+ | users | +----------------+ 1 ROW IN SET (0.02 sec) mysql> SHOW CREATE TABLE users\G; *************************** 1. ROW *************************** TABLE: users CREATE TABLE: CREATE TABLE `users` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci 1 ROW IN SET (0.01 sec) mysql> DESC users; +-------+--------------+------+-----+---------+----------------+ | FIELD | TYPE | NULL | KEY | DEFAULT | Extra | +-------+--------------+------+-----+---------+----------------+ | id | INT | NO | PRI | NULL | AUTO_INCREMENT | | name | VARCHAR(255) | YES | | NULL | | +-------+--------------+------+-----+---------+----------------+ 2 ROWS IN SET (0.11 sec)
1.3.2. Tạo table mới
CREATE TABLE `users` ( `id` INT NOT NULL AUTO_INCREMENT, `name` VARCHAR(255) COLLATE utf8mb3_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci 1 ROW IN SET (0.01 sec)
1.3.3. Tạo table mới giống bảng đã có
mysql> CREATE TABLE users_001 LIKE users; Query OK, 0 ROWS affected (0.12 sec) mysql> INSERT INTO users_001 SELECT * FROM users; Query OK, 3 ROWS affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0
1.3.4. Tạo bảng tạm
mysql> CREATE TEMPORARY TABLE temp_users AS SELECT * FROM users WHERE id = 1; Query OK, 1 ROW affected (0.07 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM temp_users; +----+--------+ | id | name | +----+--------+ | 1 | TungNT | +----+--------+ 1 ROW IN SET (0.00 sec)
1.3.5. Đổi tên bảng
mysql> RENAME TABLE users_001 TO users_002; Query OK, 0 ROWS affected (0.07 sec)
1.3.6. Xoá bảng đã có
mysql> DROP TABLE users_002; Query OK, 0 ROWS affected (0.04 sec)
1.4. Update AUTO_INCREMENT
mysql> ALTER TABLE TABLE_NAME AUTO_INCREMENT = 1;
2. Tạo tài khoản và phân quyền MySQL
2.1.1. Tạo tài khoản
mysql> CREATE USER 'tungzero'@'localhost' IDENTIFIED BY '***'; Query OK, 0 ROWS affected (0.02 sec)
2.1.2. Gán quyền truy cập database cho tài khoản
Cú pháp:
GRANT ALL PRIVILEGES ON `db_name`.* TO 'user_name'@'localhost';
Ví dụ:
mysql> GRANT ALL PRIVILEGES ON `test`.* TO `tungzero`@'localhost'; Query OK, 0 ROWS affected (0.03 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 ROWS affected (0.02 sec)
Chú ý: Không nên cho phép tài khoản sử dụng tất cả các quyền như trên, vì như thế họ sẽ có toàn quyền kiểm soát database trên server.
Nên giới hạn quyền như sau:
mysql> CREATE USER 'blue001'@'localhost' IDENTIFIED BY 'TungNT123312##'; Query OK, 0 ROWS affected (0.10 sec) mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD ON *.* TO 'blue001'@'localhost' WITH GRANT OPTION; Query OK, 0 ROWS affected (0.11 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 ROWS affected (0.02 sec) mysql> SHOW GRANTS FOR `blue001`@'localhost'; +---------------------------------------------------------------------------------------------------------------------------------+ | Grants FOR blue001@localhost | +---------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, REFERENCES, ALTER ON *.* TO `blue001`@`localhost` WITH GRANT OPTION | +---------------------------------------------------------------------------------------------------------------------------------+ 1 ROW IN SET (0.00 sec)
2.1.3. Xem quyền truy cập database đã gán tài khoản
mysql> SHOW GRANTS FOR `tungzero`@'localhost'; +------------------------------------------------------------+ | Grants FOR tungzero@localhost | +------------------------------------------------------------+ | GRANT USAGE ON *.* TO `tungzero`@`localhost` | | GRANT ALL PRIVILEGES ON `test`.* TO `tungzero`@`localhost` | +------------------------------------------------------------+ 2 ROWS IN SET (0.00 sec) mysql> SHOW GRANTS; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants FOR root@localhost | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION | | GRANT ALLOW_NONEXISTENT_DEFINER,APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_ANY_DEFINER,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,TRANSACTION_GTID_TAG,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION | | GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 ROWS IN SET (0.00 sec) mysql> SHOW GRANTS FOR CURRENT_USER; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants FOR root@localhost | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION | | GRANT ALLOW_NONEXISTENT_DEFINER,APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_ANY_DEFINER,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,TRANSACTION_GTID_TAG,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION | | GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 ROWS IN SET (0.00 sec)
4. Backup & Restore
4.1. Backup
mysqldump database_name > database_name.sql mysqldump --databases database_one database_two > two_databases.sql mysqldump --all-databases > all_databases.sql mysqldump -u root -p --all-databases --skip-lock-tables > alldb.sql
4.2. Restore
mysql database_name < database_name.sql mysql --one-database database_name < all_databases.sql
4.3. Copy table từ file .ibd
4.4. Tham khảo
5. Tham khảo
development/database/mysql.1722951096.txt.gz · Last modified: 2024/08/06 13:31 by tungnt