TungNT (Blue)

You've loaded an old revision of the document! If you save it, you will create a new version with this data.

====== 1. Các lệnh cơ bản trong MySQL ======

===== 1.1. Kiểm tra version MySQL =====

<file sql>
mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 8.3.0     |
+-----------+
1 row in set (0.00 sec)
</file>

===== 1.2. Thao tác với Database =====

==== 1.2.1. Hiển thị danh sách Databases ====

==== 1.2.2. Hiển thị thông tin database đã tạo ====

<file sql>
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)
</file>
==== 1.2.3. Tạo database mới ====

<file sql>
mysql> create database `thutycoi` character set utf8mb4 collate utf8mb4_unicode_ci;
Query OK, 1 row affected (0.08 sec)

<file sql>
mysql> use thutycoi;
Database changed
</file>
==== 1.2.5. Xoá database đã tạo ====

<file sql>
mysql> drop database thutycoi;
Query OK, 0 rows affected (0.12 sec)

==== 1.3.1. Hiển thị thông tin table ====

<file sql>
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)

==== 1.3.2. Tạo table mới ====

<file sql>
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)
</file>

==== 1.3.3. Tạo table mới giống bảng đã có ====

<file sql>
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
</file>

==== 1.3.4. Tạo bảng tạm ====

<file sql>
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)
</file>
==== 1.3.5. Đổi tên bảng ====

<file sql>
mysql> rename table users_001 to users_002;
Query OK, 0 rows affected (0.07 sec)
</file>
==== 1.3.6. Xoá bảng đã có ====

<file sql>
mysql> drop table users_002;
Query OK, 0 rows affected (0.04 sec)
</file>

===== 1.4. Update AUTO_INCREMENT =====

<file sql>
mysql> ALTER TABLE table_name AUTO_INCREMENT = 1;
</file>

===== 1.5. Một số lệnh khác =====

<file sql>
mysqld --verbose --help # https://dev.mysql.com/doc/refman/8.4/en/server-options.html
</file>

====== 2. Tạo tài khoản và phân quyền MySQL ======

==== 2.1.1. Tạo tài khoản ====

<file sql>
mysql> CREATE USER 'tungzero'@'localhost' IDENTIFIED BY '***';
Query OK, 0 rows affected (0.02 sec)
</file>

==== 2.1.2. Gán quyền truy cập database cho tài khoản ====

**Cú pháp:**

<code>
GRANT ALL PRIVILEGES ON `db_name`.* TO 'user_name'@'localhost';
</code>

**Ví dụ:**

<file sql>
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)
</file>

>> **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:**

<file sql>
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)
</file>
==== 2.1.3. Xem quyền truy cập database đã gán tài khoản ====

<file sql>
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)
</file>

====== 4. Backup & Restore ======

===== 4.1. Backup =====

<code bash>
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
</code>

===== 4.2. Restore =====

<code bash>
mysql database_name < database_name.sql
mysql --one-database database_name < all_databases.sql
</code>

===== 4.3. Copy table từ file .ibd =====

Giả sử có database test và table users như sau:

<file sql>
CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

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
</file>

Vào thư mục data lưu trữ MySQL, ví dụ: /opt/homebrew/var/mysql

<code bash>
tungnt@MacBook-Pro-cua-Nguyen-2 mysql % cd /opt/homebrew/var/mysql
tungnt@MacBook-Pro-cua-Nguyen-2 mysql % ls -la
total 1277248
-rw-r-----    1 tungnt  admin     196608 Aug  6 20:30 #ib_16384_0.dblwr
-rw-r-----    1 tungnt  admin    8585216 Aug  6 20:29 #ib_16384_1.dblwr
drwxr-x---   34 tungnt  admin       1088 Aug  6 17:50 #innodb_redo
drwxr-x---   12 tungnt  admin        384 Aug  6 17:50 #innodb_temp
drwxr-xr-x   41 tungnt  admin       1312 Aug  6 18:08 .
drwxrwxr-x   15 tungnt  admin        480 Nov 15  2023 ..
-rw-r-----    1 tungnt  admin       1435 Jun  8  2021 MBP-cua-Nguyen.err
-rw-r-----    1 tungnt  admin          4 Jun  5  2021 MBP-cua-Nguyen.pid
-rw-r-----    1 tungnt  admin     135021 Aug  6 20:29 MacBook-Pro-cua-Nguyen-2.local.err
-rw-r-----    1 tungnt  admin          6 Aug  6 17:50 MacBook-Pro-cua-Nguyen-2.local.pid
-rw-r-----    1 tungnt  admin   11601861 Nov  5  2023 MacBook-Pro-cua-Nguyen.local.err
-rw-r-----    1 tungnt  admin         56 May 25  2021 auto.cnf
drwxr-x---   13 tungnt  admin        416 Jul 28  2022 bank_crawler
-rw-r-----    1 tungnt  admin       6250 Jul 26 17:15 binlog.000208
-rw-r-----    1 tungnt  admin        883 Aug  6 17:38 binlog.000209
-rw-r-----    1 tungnt  admin        158 Aug  6 17:47 binlog.000210
-rw-r-----    1 tungnt  admin        181 Aug  6 17:49 binlog.000211
-rw-r-----    1 tungnt  admin       1279 Aug  6 20:29 binlog.000212
-rw-r-----    1 tungnt  admin         80 Aug  6 17:50 binlog.index
-rw-------    1 tungnt  admin       1676 May 25  2021 ca-key.pem
-rw-r--r--    1 tungnt  admin       1112 May 25  2021 ca.pem
-rw-r--r--    1 tungnt  admin       1112 May 25  2021 client-cert.pem
-rw-------    1 tungnt  admin       1680 May 25  2021 client-key.pem
-rw-r-----    1 tungnt  admin       4433 Aug  6 17:49 ib_buffer_pool
-rw-r-----    1 tungnt  admin   79691776 Aug  6 20:29 ibdata1
-rw-r-----    1 tungnt  admin   12582912 Aug  6 17:50 ibtmp1
drwxr-x---    8 tungnt  admin        256 Jun 15 10:14 mysql
-rw-r-----    1 tungnt  admin   71303168 Aug  6 20:29 mysql.ibd
-rw-r-----    1 tungnt  admin          5 Jun 15 10:14 mysql_upgrade_info
drwxr-x---  114 tungnt  admin       3648 Jun 15 10:14 performance_schema
-rw-------    1 tungnt  admin       1676 May 25  2021 private_key.pem
-rw-r--r--    1 tungnt  admin        452 May 25  2021 public_key.pem
-rw-r--r--    1 tungnt  admin       1112 May 25  2021 server-cert.pem
-rw-------    1 tungnt  admin       1680 May 25  2021 server-key.pem
drwxr-x---    3 tungnt  admin         96 May 25  2021 sys
drwxr-x---  118 tungnt  admin       3776 May  7 15:08 template@002e1site@002evn
drwxr-x---    3 tungnt  admin         96 Aug  6 17:45 test
drwxr-x---   89 tungnt  admin       2848 Jul 19  2023 test@002dtemplate@002e1site@002evn
-rw-r-----    1 tungnt  admin  234881024 Aug  6 20:30 undo_001
-rw-r-----    1 tungnt  admin  234881024 Aug  6 20:30 undo_002
</code>

Trong thư mục data trên, có thể nhìn thấy các database:

Vào trong từng thư mục database, có thể nhìn thấy các file table:

<code bassh>
tungnt@MacBook-Pro-cua-Nguyen-2 mysql % cd test
tungnt@MacBook-Pro-cua-Nguyen-2 test % ls -la
total 224
drwxr-x---   3 tungnt  admin      96 Aug  6 17:45 .
drwxr-xr-x  41 tungnt  admin    1312 Aug  6 18:08 ..
-rw-r-----   1 tungnt  admin  114688 Jul 25 18:04 users.ibd
</code>

**Ví dụ để clone bảng test thành bảng test_new ta làm như sau:**

<file sql>
% mysql -uroot -p

mysql> CREATE DATABASE `test_new` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */
    -> ;
Query OK, 1 row affected, 2 warnings (0.04 sec)

mysql> use test_new;
Database changed
mysql> 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;
Query OK, 0 rows affected, 3 warnings (0.05 sec)

mysql> show tables;
+--------------------+
| Tables_in_test_new |
+--------------------+
| users              |
+--------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE users DISCARD TABLESPACE;
Query OK, 0 rows affected (0.12 sec)
</file>

Sau khi DISCARD TABLESPACE, copy file table từ thư mục database test sang thư mục test_new:

<code bash>
tungnt@MacBook-Pro-cua-Nguyen-2 mysql % ls -la test_new
total 0
drwxr-x---   2 tungnt  admin    64 Aug  6 20:28 .
drwxr-xr-x  41 tungnt  admin  1312 Aug  6 18:08 ..
tungnt@MacBook-Pro-cua-Nguyen-2 mysql % 
tungnt@MacBook-Pro-cua-Nguyen-2 mysql % cp test/users.ibd test_new 
tungnt@MacBook-Pro-cua-Nguyen-2 mysql % ls -la test_new           
total 224
drwxr-x---   3 tungnt  admin      96 Aug  6 20:29 .
drwxr-xr-x  41 tungnt  admin    1312 Aug  6 18:08 ..
-rw-r-----   1 tungnt  admin  114688 Aug  6 20:29 users.ibd
</code>

Sau khi copy xong, IMPORT TABLESPACE như sau:

<file sql>
mysql> ALTER TABLE users IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.07 sec)

mysql> select * from users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | TungNT001 |
|  2 | VienLD    |
|  3 | CuongPT   |
+----+-----------+
3 rows in set (0.01 sec)
</file>

**Tham khảo:** https://dba.stackexchange.com/questions/174/how-can-i-move-a-database-from-one-server-to-another

**I recently moved a 30GB database with the following stragegy:**

* **Old Server**
    * Stop mysql server
    * Copy contents of datadir to another location on disk (~/mysqldata/*)
    * Start mysql server again (downtime was 10-15 minutes)
    * compress the data (tar -czvf mysqldata.tar.gz ~/mysqldata)
    * copy the compressed file to new server
  * **New Server**
    * install mysql (don't start)
    * unzip compressed file (tar -xzvf mysqldata.tar.gz)
    * move contents of mysqldata to the datadir
    * Make sure your innodb_log_file_size is same on new server, or if it's not, don't copy the old log files (mysql will generate these)
    * Start mysql
====== 5. Tham khảo ======

* [[development:database:mysql:structure-of-the-data-directory|Cấu trúc thư mục lưu trữ của MySQL]]
  * [[development:database:mysql:partition|Partition]]
  * [[development:database:mysql:transaction|Transaction]]
  * [[development:database:mysql:su-dung-explain-de-toi-uu-cau-lenh-mysql|Sử dụng EXPLAIN để tối ưu câu lệnh MySQL]]
  * [[development:database:mysql:giai-phap-generate-id-trong-database-instagram|Một số giải pháp generate ID]]
  * [[development:database:mysql:b-tree-indexes-va-hash-indexes-trong-mysql|B-Tree indexes và Hash indexes trong MySQL]]
  * [[https://www.youtube.com/watch?v=f-G8M_Ko4KI|4 Nguyên tắc lựa chọn Database mà tôi áp dụng trong mọi dự án triển khai của mình]]
  * [[https://www.youtube.com/watch?v=5z5_fMQn4Tc|Thiết kế Database đáp ứng 400 triệu người tại Quora]]
  * [[development:database:mysql:inverse-indexes|Inverse Indexes]]

Please fill all the letters into the box to prove you're human. P C S H Z Please keep this field empty:

Edit summary