Differences

This shows you the differences between two versions of the page.

--- development:database:mysql [2024/08/06 13:48] – [1.4. Update AUTO_INCREMENT] tungnt
+++ development:database:mysql [2025/03/19 04:14] (current) – [Kiểm tra thư mục dữ liệu] tungnt
@@ Line 190: / Line 190: @@
 </file>
+==== Kiểm tra thư mục dữ liệu ====
+<code sql>
+SHOW VARIABLES LIKE 'datadir';
+</code>
+==== Kiểm tra dung lượng bảng đã đánh partition ====
+<code sql>
+SELECT TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, ROUND(DATA_LENGTH / (1024 * 1024 * 1024), 2) AS Size_GB FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'user_transactions' AND PARTITION_NAME = 'year2025'
+</code>
 ====== 2. Tạo tài khoản và phân quyền MySQL ======
@@ Line 222: / Line 233: @@
 <file sql>
-mysql> CREATE USER 'blue001'@'localhost' IDENTIFIED BY 'TungNT123312##';
+mysql> CREATE USER 'blue001'@'localhost' IDENTIFIED BY '***';
 Query OK, 0 rows affected (0.10 sec)
@@ Line 474: / Line 485: @@
 rows in set (0.01 sec)
 </file>
-**Tham khảo thêm:** https://dba.stackexchange.com/questions/174/how-can-i-move-a-database-from-one-server-to-another
+**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 ======
@@ Line 480: / Line 507: @@
   * [[development:database:mysql:partition|Partition]]
   * [[development:database:mysql:transaction|Transaction]]
+  * [[development:database:mysql:debezium|Debezium MySQL]]
   * [[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]]
@@ Line 485: / Line 513: @@
   * [[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]]
+  * [[development:database:mysql:cluster|Cluster]]