Hướng dẫn sao lưu cơ sở dữ liệu MySQL

2999

12-10-2017

MySQL là một hệ quản trị cơ sở dữ liệu quan hệ mã nguồn mở được sử dụng trong nhiều hệ thống, phần lớn được sử dụng như là một thành phần của LAMP Stack. Hệ thống cơ sở dữ liệu cũng dễ sử dụng, có tính cơ động cao và rất hiệu quả trong nhiều ứng dụng. Vì MySQL thường là kho lưu trữ tập trung số lượng lớn các dữ liệu quan trọng nên việc sao lưu thường xuyên database là một trong những nhiệm vụ quan trọng nhất mà một system admin phải thực hiện.

Qua bài viết này, Bizfly Cloud đề cập đến một số phương pháp để sao lưu database cũng như việc restore nó một cách tốt nhất.

1. Backup cơ sở dữ liệu

Đầu tiên chúng ta hãy đề cập đến các phương pháp sao lưu. Hầu hết việc sao lưu databases của MySQL trong bài viết này được thực hiện bằng cách sử dụng công cụ mysqldump, được đi kèm với cấu hình MySQL mặc định.

Chúng ta nên sử dụng mysqldump bất cứ khi nào có thể vì nó thường là cách dễ dàng và hiệu quả nhất để sao lưu cơ sở dữ liệu. Các phương pháp khác chi tiết trong bài viết này được cung cấp cho các tình huống khi mà chúng ta không có quyền truy cập vào công cụ mysqldump, như trong môi trường khôi phục ví dụ như Finnix hoặc trong các trường hợp mà MySQL không thể khởi động.

Lưu ý:

Các tập tin * .sql được tạo ra với mysqldump có thể được phục hồi bất cứ lúc nào Chúng ta thậm chí có thể chỉnh sửa cơ sở dữ liệu các tập tin .sql bằng tay. Sử dụng trình soạn thảo văn bản như vim, nano…

Nếu cơ sở dữ liệu của chúng ta chỉ sử dụng MyISAM engine, chúng ta có thể thay thế lệnh mysqldump bằng mysqlhotcopy để đạt tốc độ nhanh hơn.

1.1. Tạo các bản sao lưu của toàn bộ MySQL

Thông thường, chúng ta cần phải sao lưu (hoặc "dump") của toàn bộ cơ sở dữ liệu cùng với tất cả các dữ liệu và bảng, bao gồm các cơ sở dữ liệu hệ thống chứa người sử dụng, phân quyền và mật khẩu.

1.1.1. Sử dụng mysqldump

Cú pháp tạo ra một database dump với thời gian hiện tại như sau:

#mysqldump --all-databases > dump-$( date ' %Y-%m-%d_%H-%M-%S' ).sql -u root -p

Quá trình này có thể mất từ vài giây đến vài giờ tùy thuộc vào kích thước cơ sở dữ liệu.

Tự động quá trình này bằng cách thêm một dòng vào crontab:

0 1 * * * /usr/bin/mysqldump --all-databases > dump-$( date ' %Y-%m-%d_%H-%M-%S' ).sql -u root -pPASSWORD

Đối với ví dụ trên, chúng ta sử dụng mysqldump để xác nhận chính xác đường dẫn đến lệnh, thay thế root với người dùng mysql mà chúng ta muốn chạy sao lưu và PASSWORD với mật khẩu chính xác cho người dùng đó.

Chú ý: Trong ví dụ crontab, hãy đảm bảo rằng không có khoảng trống giữa cờ -p và mật khẩu.

1.1.2. Sử dụng bản sao của thư mục dữ liệu MySQL

Có một vài trường hợp cần một cách khác vì mysqldump chỉ hoạt động khi MySQL server có thể truy cập và đang chạy. Nếu MySQL không thể khởi động hoặc máy chủ không thể truy cập, chúng ta có thể sao chép cơ sở dữ liệu của MySQL trực tiếp. Phương pháp này thường rất cần thiết trong trường hợp chúng ta chỉ có quyền truy cập vào môi trường khôi phục như Finnix với đĩa hệ thống được gắn trên hệ thống tệp đó.

Nếu bạn sử dụng phương pháp này trên hệ thống của mình, hãy đảm bảo rằng cơ sở dữ liệu đang không hoạt động, sử dụng lệnh:

#/etc/init.d/mysqld stop

Trên bản MySQL mới nhất, thư mục dữ liệu nằm trong /var/lib/mysql. Nếu thư mục này không tồn tại, kiểm tra tệp /etc/mysql/my.cnf để tìm đường dẫn đến thư mục dữ liệu. Ngoài ra, bạn có thể tìm kiếm hệ thống tập tin của bạn cho thư mục dữ liệu bằng cách sử dụng lệnh sau đây:

#find / -name mysql

Một khi bạn tìm thấy thư mục lưu trữ dữ liệu MySQL của bạn, bạn có thể sao chép nó vào một vị trí sao lưu. Trong trường hợp này, chúng ta sẽ sao chép các nội dung của thư mục dữ liệu (ví dụ: /var/lib/mysql/) vào thư mục trong /opt/(ví dụ: /opt/database/backup-23092017/).

#/etc/init.d/mysql stop

#mkdir -p /opt/database/backup-23092017/

#cp -R /var/lib/mysql/* /opt/database/backup-23092017/

Lệnh cp không tạo ra kết quả ghi ra shell và có thể mất một thời gian để hoàn thành tùy thuộc vào kích thước cơ sở dữ liệu của bạn. Khi quá trình sao chép kết thúc, bạn có thể nén nó lại với "tar" để dễ quản lý và di chuyển giữa các máy:

#cd /opt/database/backup-23092017

#tar -czfv * > /opt/mysqlbackup-23092017.tar.gz

Đừng quên khởi động lại dịch vụ của máy chủ MySQL một lần nữa nếu cần:

#/etc/init.d/mysql start

1.2. Tạo sao lưu của một cơ sở dữ liệu đơn

Trong nhiều trường hợp, việc tạo sao lưu toàn bộ máy chủ cơ sở dữ liệu là không cần thiết. Ví dụ như nâng cấp ứng dụng web, trình cài đặt có thể đề nghị sao lưu cơ sở dữ liệu trong trường hợp nâng cấp ảnh hưởng xấu đến cơ sở dữ liệu. Tương tự, nếu bạn muốn tạo ra một "dump" của một cơ sở dữ liệu cụ thể để di chuyển cơ sở dữ liệu đó đến một máy chủ khác, bạn có thể xem xét các phương pháp sau đây.

Sử dụng công cụ mysqldump để tạo 1 bản "dump" của 1 cơ sở dữ liệu duy nhất. Lệnh này sẽ giống như sau:

#mysqldump -u username -ps3cr1t -h localhost vccloud> 23092017-vccloud.sql

Ví dụ trên giống như ví dụ trong phần trước, ngoại trừ việc sử dụng tùy chọn --all-databases, ví dụ này xác định một tên cơ sở dữ liệu cụ thể. Trong trường hợp này, chúng ta tạo ra một bản sao lưu của cơ sở dữ liệu vccloud. Cú pháp của lệnh này:

#mysqldump -u [username] -p[password] -h [host] [databaseName] > [backup-name].sql

Để thêm một ví dụ, chúng ta sẽ sao lưu cơ sở dữ liệu có tên customer bằng tài khoản root bằng cách thực thi lệnh sau:

#mysqldump -u root -p -h localhost customer > customerbackup.sql

Bạn sẽ được nhắc nhập mật khẩu trước khi mysqldump bắt đầu quá trình sao lưu. Như mọi khi, tệp tin sao lưu, trong trường hợp này là customerbackup.sql, được tạo ra trong thư mục nơi bạn phát hành lệnh này.

Lệnh mysqldump có thể hoàn thành trong vài giây hoặc vài giờ phụ thuộc vào kích thước của cơ sở dữ liệu và tải trên máy chủ khi chạy bản sao lưu.

1.3. Tạo các bản sao lưu của một bảng đơn

1.3.1. Tạo bản sao lưu của một bảng đơn bằng cách sử dụng tiện ích mysqldump

Thao tác này cho phép bạn tạo một bản sao lưu của một bảng cơ sở dữ liệu đơn. Ví dụ sau hướng dẫn cho bạn sao lưu bảng usernameRecords trong cơ sở dữ liệu vccloud.

#mysqldump -u username -ps3cr1t -h localhost vccloud usernameRecords \> 23092017-vccloud-usernameRecords.sql

Ví dụ trên giống như ví dụ trong phần trước, ngoại trừ việc chúng ta đã thêm một tên bảng vào lệnh để chỉ định tên của bảng mà chúng ta muốn sao lưu. Cú pháp câu lệnh cụ thể như sau:

mysqldump -u [username] -p[password] -h [host] [databaseName] [tableName] > [backup-name].sql

Đối với một ví dụ khác, chúng ta sẽ sao lưu bảng có tên "order" từ cơ sở dữ liệu có tên customer bằng tài khoản cơ sở dữ liệu root bằng cách dùng lệnh sau:

#mysqldump -u root -p -h localhost customer order > customerbackup-order.sql

Bạn sẽ được nhắc nhập mật khẩu trước khi mysqldump bắt đầu quá trình sao lưu. Như thường lệ, tệp sao lưu (trong trường hợp này là customerbackup.sql) được tạo ra trong thư mục nơi bạn đưa ra lệnh này.

Lệnh mysqldump có thể hoàn thành trong vài giây hoặc vài giờ phụ thuộc vào kích thước của cơ sở dữ liệu và tải trên máy chủ khi chạy bản sao lưu.

1.3.2. Tạo các bản sao lưu của một bảng đơn sử dụng MySQL client và OUTFILE Statement

MySQL client chính nó có một số khả năng sao lưu. Nó rất hữu ích khi bạn đã đăng nhập và bạn không muốn thoát khỏi phiên hiện tại. Nếu bạn đang sử dụng một hệ thống đang chạy và không thể đủ thời gian để giảm tải, bạn nên cân nhắc tạm thời khóa bảng mà bạn đang sao lưu.

Cần lưu ý rằng khi sao lưu một bảng duy nhất bằng cách sử dụng MySQL client, cấu trúc của bảng không được duy trì trong bản sao lưu. Chỉ bản thân dữ liệu được lưu khi sử dụng phương pháp này.

Trước khi bắt đầu, chúng tôi khuyên bạn nên thực hiện LOCK TABLES trên các bảng bạn dự định sao lưu, tiếp theo là FLUSH TABLES để đảm bảo rằng cơ sở dữ liệu nằm trong một khoảng nhất quán trong quá trình sao lưu. Bạn chỉ cần khóa “read”. Điều này cho phép các client khác tiếp tục truy vấn các bảng trong khi bạn đang tạo một bản sao của các tệp tin trong thư mục dữ liệu MySQL.

LOCK TABLES trên bảng lệnh của cơ sở dữ liệu client:

#mysql -u root -p -h localhost

> USE customer;

> LOCK TABLES order READ;

> FLUSH TABLES;

Bây giờ chúng ta có thể bắt đầu hoạt động sao lưu. Sử dụng câu lệnh SELECT * INTO OUTFILE. Cú pháp của câu lệnh này như sau:

SELECT * INTO OUTFILE 'file_name' FROM tbl_name;

Trong ví dụ này, chúng ta sẽ tạo một bản sao lưu dữ liệu từ bảng lệnh của cơ sở dữ liệu client:

> USE customer;

> LOCK TABLES order READ;

> FLUSH TABLES;

> SELECT * INTO OUTFILE 'customerOrderbackup.sql' FROM order;

> UNLOCK TABLES;

Thư mục dữ liệu MySQL thường là /var/lib/mysql/, OUTFILE sẽ là /var/lib/mysql/customer/customerOrderbackup.sql. Tuy nhiên, vị trí của thư mục và tệp tin này có thể thay đổi giữa các bản phân phối Linux.

Khi đã hoàn thành các hoạt động sao lưu, mở lại các bảng:

UNLOCK TABLES;

Bạn có thể tiếp tục sử dụng cơ sở dữ liệu của bạn như bình thường từ thời điểm này.

1.4. Các lưu ý sao lưu hiệu quả

Tạo sao lưu cơ sở dữ liệu MySQL nên là một công việc thường xuyên và theo lịch trình, bạn nên xem xét lên lịch sao lưu định kỳ bằng cron, mysqldump hoặc mail. Thực hiện giải pháp sao lưu tự động có thể giúp giảm thiểu thời gian trong tình huống khắc phục sự cố.

Bạn không cần đăng nhập bằng root khi sao lưu cơ sở dữ liệu. User MySQL có quyền đọc có thể sử dụng cả hai công cụ mysqldump và mysql để thực hiện sao lưu. Thông thường, không nên sử dụng user root của MySQL bất cứ khi nào có thể để giảm thiểu nguy cơ bảo mật.

2. Khôi phục cơ sở dữ liệu từ bản sao lưu

Bạn nên kiểm tra bản sao lưu thường xuyên để đảm bảo rằng chúng có thể được phục hồi trong trường hợp cần khôi phục lại. Phương pháp sử dụng khôi phục phụ thuộc vào phương pháp bạn sử dụng để tạo bản sao lưu.

2.1. Khôi phục toàn bộ cơ sở dữ liệu

Cách 1: Khôi phục toàn bộ cơ sở dữ liệu sử dụng MySQL client và sao lưu được tạo bởi mysqldump

Phần này giả định hệ thống của bạn đang chạy một phiên bản mới được cài đặt của MySQL mà không có bất kỳ cơ sở dữ liệu hoặc bảng hiện có nào. Nếu bạn đã có các cơ sở dữ liệu và bảng trong MySQL, vui lòng thực hiện một bản sao lưu trước khi tiến hành vì quá trình này sẽ ghi đè dữ liệu MySQL hiện tại.

Bạn có thể dễ dàng khôi phục toàn bộ cơ sở dữ liệu bằng cách sử dụng lệnh mysql. Cú pháp cho câu lệnh:

#mysql -u [username] -p [password] < backupFile.sql

Trong trường hợp này, chúng ta chỉ đơn giản là khôi phục toàn bộ dữ liệu. Câu lệnh sẽ là:

#mysql -u root -p < 23092017-vccloud.sql

Quá trình này khôi phục toàn bộ cơ sở dữ liệu, nên có thể mất nhiều giờ.

Cách 2: Khôi phục toàn bộ cơ sở dữ liệu bằng cách sử dụng các tệp dữ liệu MySQL được sao chép trực tiếp từ MySQL Data Directory

Nếu bạn có một bản sao lưu đầy đủ của thư mục dữ liệu MySQL của bạn (thường là /var/lib/mysql), bạn có thể khôi phục nó từ dòng lệnh. Để đảm bảo khôi phục thành công, trước tiên bạn phải dừng máy chủ của máy chủ MySQL và xóa dữ liệu hiện tại trong thư mục dữ liệu MySQL.

#/etc/init.d/mysql stop

#rm -R /var/lib/mysql/*

Trong ví dụ sau, sao lưu thư mục dữ liệu MySQL nằm trong thư mục /opt/ database/backup-23092017. Nếu bạn tạo một tarball trong thư mục dữ liệu khi sao lưu thư mục dữ liệu của mình, bạn cần phải giải nén các tệp từ tarball trước khi sao chép bằng các lệnh sau:

#cp backup-23092017.tar.gz /var/lib/mysql/

#cd /var/lib/mysql

#tar xzvf backup-23092017.tar.gz

Trước khi chúng ta có thể khởi động lại cơ sở dữ liệu MySQL, chúng ta phải đảm bảo rằng thư mục /var/lib/mysql/ phải được phân quyền đúng.

Đối với ví dụ này, chúng ta giả sử daemon MySQL chạy với user mysql và group mysql. Để thay đổi quyền truy cập vào thư mục dữ liệu ra lệnh sau đây:

#chown -R mysql:mysql /var/lib/mysql

Thay đổi phần mysql:mysql của lệnh này nếu MySQL của bạn chạy với quyền người dùng và nhóm khác nhau.

Khởi động MySQL daemon:

#/etc/init.d/mysql start

Kiểm tra MySQL của bạn và đảm bảo rằng tất cả các cơ sở dữ liệu và bảng phục hồi đúng. Bạn nên kiểm tra log để tìm ra lỗi tiềm ẩn, trong một số trường hợp MySQL có thể không báo lỗi mặc dù có lỗi cơ sở dữ liệu.

2.2. Khôi phục một cơ sở dữ liệu đơn từ sao lưu

Trong trường hợp bạn chỉ tạo một bản sao lưu cho một cơ sở dữ liệu, hoặc chỉ cần khôi phục lại một cơ sở dữ liệu duy nhất, quá trình khôi phục lại hơi khác.

Để khôi phục lại một cơ sở dữ liệu duy nhất bằng cách sử dụng lệnh mysql, đầu tiên chuẩn bị cơ sở dữ liệu đích. Đăng nhập vào máy chủ cơ sở dữ liệu MySQL (mới) sử dụng MySQL client:

#mysql -u root -p -h localhost

> CREATE DATABASE customer;

Tùy thuộc vào nhu cầu, bạn có thể cần phải tạo một người dùng MySQL mới hoặc tạo lại một người dùng trước đó có quyền truy cập cơ sở dữ liệu mới được tạo ra. Lệnh tạo một người dùng MySQL mới và cung cấp toàn quyền cho user:

> CREATE USER 'customeradmin'@'localhost' IDENTIFIED BY 's3cr1t';

> GRANT ALL ON customer.* TO 'customeradmin'@'localhost';

> quit

Khôi phục tệp SQL của bạn. Cú pháp câu lệnh như sau:

#mysql -u root -p -h localhost customer < customerbackup.sql

2.3. Khôi phục một bảng từ sao lưu

2.3.1. Phục hồi một bảng đơn sử dụng MySQL và các sao lưu được tạo bởi mysqldump

Trước khi bắt đầu quá trình khôi phục, chắc chắn rằng MySQL đã có một cơ sở dữ liệu có thể nhận được bảng bạn muốn khôi phục.

Để khôi phục bảng:

#mysql -u [username] -p[password] -h [host] [databaseName] < [filename].sql

Ví dụ khôi phục lại bảng order vào database customer hiện có từ một tệp sao lưu SQL có tên customerOrderbackup.sql:

#mysql -u root -p -h localhost customer < customerOrderbackup.sql

2.3.2. Phục hồi một bảng đơn sử dụng MySQL client và INFILE statement cho các sao lưu được tạo với OUTFILE

Trước khi bắt đầu quá trình khôi phục, chắc chắn rằng MySQL đã có một cơ sở dữ liệu có thể nhận được bảng bạn muốn khôi phục.

Việc sao lưu dữ liệu được sử dụng trong trường hợp này đã được tạo ra bằng cách sử dụng lệnh SELECT * INTO OUTFILE 'backupFile.sql' FROM tableName. Loại sao lưu này chỉ giữ nguyên dữ liệu nên cấu trúc bảng phải được tạo lại.

Để khôi phục lại một bảng từ bên trong MySQL client, trước tiên bạn phải chuẩn bị cơ sở dữ liệu và bảng.

#mysql -u root -p -h localhost

> USE customer;

> CREATE TABLE order (custNum INT, orderName VARCHAR(20));

Nhập dữ liệu sao lưu từ tệp sao lưu bằng lệnh LOAD DATA. Cú pháp tương tự như sau:

LOAD DATA INFILE '[filename]' INTO TABLE [tableName];

Trong ví dụ, chúng ta sẽ khôi phục lại dữ liệu từ một bảng từ một tệp có tên customerOrderbackup.sql. Để nhập dữ liệu từ tệp tin customerOrderbackupu.sqltrong /var/lib/mysql/, hãy dùng lệnh sau :

> LOAD DATA INFILE 'customerOrderbackup.sql' INTO TABLE order;

Kiểm tra MySQL của bạn và đảm bảo rằng tất cả các cơ sở dữ liệu và bảng phục hồi đúng.

Nguồn: Bizfly Cloud chia sẻ

>>> Xem thêm: Mysql là gì? Tổng hợp thông tin chi tiết nhất về Mysql