Innodb_file_per_table mysql 8

Trong MySQL, khi bạn đang sử dụng InnoDB, tất cả các bảng và chỉ mục được lưu trữ theo hệ thống bảng MySQL. Hệ thống bảng MySQL là ibdata1, mà trong thư mục /var/lib/mysql

Tập tin ibdata1 đơn chứa tất cả các bảng và chỉ mục trong cơ sở dữ liệu MySQL của bạn. Vì vậy, nếu bạn có một cơ sở dữ liệu lớn, kích thước tập tin này sẽ thực sự lớn.

Trong hướng dẫn này, chúng tôi sẽ giải thích cách làm thế nào để xây dựng lại toàn bộ cơ sở dữ liệu MySQL của bạn và phá vỡ bảng hệ thống tệp MySQL thành các tệp .

​

1. Hệ thống bảng Big MySQL (và MariaDB)
Có một nhược điểm lớn với hệ thống bảng MySQL mặc định khi tiếp cận.
Please xem kịch bản sau đây là một ví dụ. Bạn đã tải lên 100GB dữ liệu vào nhiều bảng trong MySQL của bạn.
Hiện tại, kích thước tệp ibdata1 sẽ có khoảng 100GB +.

Mã.

cd /var/lib/mysql
ls -lh ibdata1
-rw-r-----. 1 mysql mysql 101G Jan 21 21:10 ibdata1

Sau vài ngày, bạn đã xóa khoảng 50GB dữ liệu từ tất cả các bảng. Kích thước tệp ibdata1 sẽ không giảm xuống khoảng 50GB+, nó vẫn sẽ ở khoảng 100GB+.

Trong trường hợp lệ trên, sau khi bạn thêm 10GB dữ liệu vào các bảng, kích thước tệp ibdata1 không tăng lên 110GB và vẫn giữ nguyên 100GB. Bởi vì, vẫn còn không có khoảng thời gian chưa sử dụng bên trong từ 50GB dữ liệu đã bị xóa ở trên.

Vấn đề là, bạn không thể đòi lại những không gian không sử dụng sau khi bạn xóa 50GB dữ liệu từ tập tin ibdata1. Có một cách để làm điều đó, nhưng quá phức tạp (giải pháp dưới đây), và yêu cầu phải dừng cơ sở dữ liệu MySQL.

Vì vậy, làm thế nào để chúng ta tránh lưu trữ tất cả các bảng và chỉ mục trong một tập tin ibdata1 đơn; . Thiết lập tham số innodb_file_per_table

2. Thiết lập tham số innodb_file_per_table
Đối với điều này, bạn nên sử dụng tham số innodb_file_per_table bên trong tệp /etc/my. cnf của bạn bên dưới phần "mysqld" như dưới đây.

Mã.

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

Lưu ý. Nếu bạn đang sử dụng MySQL 5. 6. 6 (hoặc MariaDB) và cao hơn, ở trên là thiết lập mặc định.
Trong ví dụ này, trên CentOS 6, mặc định của MySQL mà bạn nhận được khi cài đặt từ kho yum vẫn là 5. 1. 73 như dưới đây.

Mã.

# mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

Vì vậy, trong trường hợp này, chúng ta nên thiết lập innodb_file_per_table trong tệp của tôi. cnf.
Bất cứ lúc nào bạn thực hiện bất kỳ thay đổi nào đối với tệp của tôi. cnf, bạn nên khởi động lại cơ sở dữ liệu MySQL hoặc MariaDB.

Mã.

service mysqld restart

Lưu ý. Đối mặt với một số lý do nếu bạn muốn thiết lập thông số này trong khi cơ sở dữ liệu đang chạy và không muốn dừng cơ sở dữ liệu lại, bạn có thể thực hiện thiết lập từ dấu nhắc mysql sau đây.

Mã.

mysql> set global innodb_file_per_table=1;

3. Các bảng mới (và chỉ mục) như các tệp cá nhân
Từ bây giờ, khi bạn tạo một bảng MySQL mới, bạn sẽ nhận được các tệp cá nhân.
Trong ví dụ này, tôi tạo ra một bảng gọi là nhân viên và tải lên khoảng 20GB dữ liệu vào đó.

Mã.

mysql> use aluhost
mysql> create table employee ...
mysql> insert into employee ..

Hai điều sẽ xảy ra ở đây.
Đầu tiên, nó sẽ tạo ra một thư mục con với tên cơ sở dữ liệu "aluhost" trong thư mục /var/lib/mysql.

Mã.

# ls -l /var/lib/mysql/
drwx------. 2 mysql mysql   266240 Jan  5 12:11 aluhost
...

Thứ hai, theo tên mục cơ sở dữ liệu này (tức là thư mục aluhost), bạn sẽ thấy tệp EMPLOYEE. IBD kernel are made out. Kích thước của tệp này sẽ giống với kích thước của dữ liệu mà bạn đã tải lên bảng đó. Trong trường hợp này, kể từ khi chúng tôi tải lên 20GB dữ liệu vào bảng này, kích thước EMPLOYEE. IBD sẽ là khoảng 20GB như dưới đây.

Mã.

# cd /var/lib/mysql/aluhost/
# ls -lh
-rw-r-----. 1 mysql mysql  21G Jan 21 21:17 employee.ibd

Lưu ý. Nếu bạn đang sử dụng cơ sở dữ liệu MyISAM, bạn sẽ thấy các tập tin. MYD,. FRM and. MYI.

4. Trích xuất bảng hiện có từ ibdata1
Tiếp theo, nếu bạn muốn trích xuất bảng hiện có từ ibdata1 đến tệp cá nhân, thì bạn phải tối ưu hóa bảng.

Chúng ta hãy nói rằng bạn có một bảng gọi là lợi ích trong nền tảng aluhost. Lợi ích bảng được tạo trước khi chúng tôi thiết lập innodb_file_per_table trong my. cnf.

Vì vậy, các lợi ích của bảng vẫn sẽ thuộc về ibdata1. Để chuyển nó từ ibdata1 sang tệp IBD riêng của nó, chúng ta phải tối ưu hóa bảng như dưới đây.

Mã.

________số 8

Điều này sẽ tạo tập tin cá nhân sau đây cho bảng lợi ích.

Mã.

# cd /var/lib/mysql/aluhost/
# ls -lh
-rw-r-----. 1 mysql mysql  21G Jan 21 21:17 benefits.ibd

Trong ví dụ này, hãy nhớ rằng các tập tin ibdata1 ban đầu vẫn không chia nhỏ. Nó vẫn còn là khoảng 100GB.

Mã.

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

0

Lưu ý. You can also like after.

Mã.

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

1

5. Chia nhỏ kích thước ibdata1
Hãy nhớ rằng ibdata1 vẫn có kích thước là 101G, nó không bị giảm kích thước.

Mã.

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

0

Để thu nhỏ tệp ibdata1, bạn cần thực hiện các bước sau đây.

5. 1. Sao lưu cơ sở dữ liệu
Đầu tiên, tạm thời dừng cơ sở dữ liệu mysql và có một bản sao lưu của toàn bộ cơ sở dữ liệu. Trong trường hợp bị sai, bạn có thể sử dụng sao lưu các tập tin này để khôi phục.

Mã.

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

3

5. 2 Sử dụng lệnh mysqldump
Thứ hai, hãy sử dụng lệnh mysqldump sao lưu tất cả các cơ sở dữ liệu.

Mã.

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

4

5. 3 Thả tất cả các cơ sở dữ liệu của bạn
Tiếp theo, thả tất cả các cơ sở dữ liệu của bạn. Để xem tất cả các cơ sở dữ liệu tiêu chuẩn bị hủy, hãy sử dụng "hiển thị cơ sở dữ liệu"

Mã.

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

5

Trong ví dụ này, chúng tôi đã bị loại bỏ hai cơ sở dữ liệu (aluhost và sales) tồn tại trong mysql.

Mã.

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

6

Lưu ý. Not drop information_schema và mysql.

5. 4. Xóa ibdata và ib_logfile
Tiếp theo, dừng cơ sở dữ liệu MySQL.

Mã.

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

7

Tiếp theo, loại bỏ các tập tin ibdata1 và tất cả các tập tin cá nhân ib_logfile *.

Mã.

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

8

5. 5. Nhập tất cả các cơ sở dữ liệu
Lưu ý. Tại thời điểm này, bạn đã cần phải có những điều sau đây trong tập tin của tôi. cnf của bạn.

Mã.

# vi /etc/my.cnf
[mysqld]
innodb_file_per_table

9

Khởi động cơ sở dữ liệu MySQL

Mã.

# mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

0

Nhập tất cả các cơ sở dữ liệu từ bản sao lưu mysqldump mà chúng tôi sao lưu trước đó.

Mã.

# mysql --version
mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

1

Ở giai đoạn này, tệp ibdata1, bảng hệ thống MySQL sẽ được tạo từ đầu và trong ví dụ của chúng tôi, nó sẽ không còn 100GB nữa.
Hiện tại ibdata1 sẽ chỉ có vài MB trong kích thước. Tất cả các bảng cơ sở dữ liệu sẽ được lưu trữ dưới dạng các tệp cá nhân trong thư mục con cơ sở dữ liệu tương ứng trong /var/lib/mysql/.