HƯỚNG DẪN Shrink MySQL ibdata1 Size sử dụng innodb_file_per_table

Thảo luận trong 'KIẾN THỨC VPS/SERVER' bắt đầu bởi quyet1990, 20/12/16.

  1. quyet1990

    quyet1990 New Member

    Tham gia ngày:
    22/10/16
    Bài viết:
    220
    Đã được thích:
    0
    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ỏ.

    [​IMG]
    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/.
     

Chia sẻ trang này

Đang tải...