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 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ỡ tập tin hệ thống bảng MySQL lớn thành các tập tin bảng MySQL nhỏ. 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. Hãy xem kịch bản sau đây là một ví dụ: Bạn đã tải lên 100GB của dữ liệu vào nhiều bảng trong MySQL của bạn. Bây giờ, kích thước tập tin 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 của dữ liệu từ tất cả các bảng. Kích thước tập tin ibdata1 sẽ không được giảm xuống khoảng 50GB+, nó vẫn sẽ ở vào khoảng 100GB +. Trong trường hợp trên, sau khi bạn thêm 10GB dữ liệu vào các bảng, kích thước tập tin ibdata1 không tăng đến 110GB, và vẫn ở yên 100GB. Bởi vì, vẫn còn có không 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 các 50GB dữ liệu từ tập tin ibdata1. Có một cách để làm điều đó, nhưng là quá phức tạp (giải thích dưới đây), và đòi hỏi phải giữ 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; thay vì lưu trữ trong nhiều tập tin bảng riêng? 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 tin /etc/my.cnf của 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à các 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 tin my.cnf. Bất cứ lúc nào bạn thực hiện bất kỳ thay đổi đến tập tin my.cnf, bạn nên khởi động lại cơ sở dữ liệu MySQL or MariaDB. Mã: service mysqld restart Lưu ý: Đối 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ác 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ể làm 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 tin 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 tập tin cá nhân. Trong ví dụ này, tôi tạo ra một bảng gọi là employee và tải lên khoảng 20GB của dữ liệu vào nó. 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 thư mục cơ sở dữ liệu này (tức là thư mục aluhost), bạn sẽ thấy tập tin EMPLOYEE.IBD cá nhân được tạo ra. Kích thước của tập tin này sẽ giống kích thước của dữ liệu mà bạn đã tải lên vào 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 và .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 một bảng hiện có từ ibdata1 đến tập tin cá nhân riêng, 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à benefits trong cơ sở dữ liệu aluhost. Bảng benefits được tạo ra trước khi chúng tôi thiết lập innodb_file_per_table trong my.cnf. Vì vậy, bảng benefits vẫn sẽ thuộc ibdata1. Để di chuyển nó từ ibdata1 vào tập tin IBD riêng của nó, chúng ta phải tối ưu hóa bảng như dưới đây. Mã: mysql> use aluhost mysql> optimize table benefits; Điều này sẽ tạo ra tập tin cá nhân sau đây cho bảng benefits. 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ã: # ls -lh /var/lib/mysql/ibdata1 -rw-r-----. 1 mysql mysql 101G Jan 21 21:10 ibdata1 Lưu ý: Bạn cũng có thể làm như sau. Mã: mysql> alter table benefits engine=InnoDB; 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ã: # ls -lh /var/lib/mysql/ibdata1 -rw-r-----. 1 mysql mysql 101G Jan 21 21:10 ibdata1 Để thu nhỏ file 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 lại cơ sở dữ liệu mysql, và có một 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 lnày để khôi phục. Mã: mkdir /backup cd /var/lib cp -r mysql /backup 5.2 Sử dụng lệnh mysqldump Thứ hai, hãy dùng lệnh mysqldump sao lưu tất cả các cơ sở dữ liệu. Mã: mysqldump -u root -ptmppassword --all-databases > /backup/all-database.sql 5.3 Drop tất cả các cơ sở dữ liệu của bạn Tiếp theo, drop 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 chuẩn bị drop, sử dụng "show databases" Mã: mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | aluhost | | sales | | mysql | +--------------------+ Trong ví dụ này, chúng tôi được drop hai cơ sở dữ liệu (aluhost và sales) mà tồn tại trong mysql. Mã: # mysql -u root -ptmppassword mysql> drop database aluhost; mysql> drop database sales; Lưu ý: Không 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ã: service mysqld stop 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ã: cd /var/lib/mysql/ rm ibdata1 rm ib_logfile0 rm ib_logfile1 5.5. Import 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 my.cnf của bạn. Mã: [mysqld] innodb_file_per_table Khởi động cơ sở dữ liệu MySQL Mã: service mysqld start Import 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 -u root -ptmppassword --all-databases < /backup/all-database.sql Ở giai đoạn này, tập tin ibdata1, hệ thống bảng MySQL sẽ được tạo ra từ đầu, và trong ví dụ của chúng tôi, nó sẽ không còn 100GB nữa. Bây giờ 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ữ như các tập tin cá nhân trong thư mục con cơ sở dữ liệu tương ứng trong /var/lib/mysql/.