MySQL Incremental Backup - Sao lưu và phục hồi theo thời gian của cơ sở dữ liệu InnoDB và MyIsam

[theanh]

Thực hiện sao lưu gia tăng là một yêu cầu quan trọng đối với các cơ sở dữ liệu sản xuất lớn. Nếu không có bản sao lưu gia tăng an toàn, bạn không thể tự nhủ rằng mình có một cơ sở dữ liệu sản xuất đáng tin cậy. Bởi vì bạn phải có đủ dữ liệu để khôi phục cơ sở dữ liệu của mình trong trường hợp khẩn cấp. Sau một số tìm kiếm trên Internet, tôi không thể tìm thấy bất kỳ công cụ nào có thể thực hiện sao lưu gia tăng hoàn chỉnh cho MyISAM và InnodB trong môi trường hỗn hợp khi các ứng dụng sử dụng cả hai công cụ cơ sở dữ liệu cùng một lúc (có thể tôi không phải là chuyên gia tìm kiếm trên Google và Internet). Vì vậy, tôi quyết định viết bài này, nhưng để tránh lãng phí thời gian và hưởng lợi từ các giải pháp nguồn mở khác, tôi thích thêm tính năng này vào tập lệnh -automysqlbackup-, đây là tập lệnh tốt nhất để sao lưu toàn bộ vì tính đơn giản và sử dụng rộng rãi.

Cơ chế​

Chúng tôi sử dụng tính năng Post- và Pre của automysqlbackup để thực hiện sao lưu gia tăng. Trước khi bắt đầu sao lưu đầy đủ, mysql-backup-pre thực thi truy vấn để khóa toàn bộ cơ sở dữ liệu trong quá trình sao lưu vì chúng tôi phải đóng băng binlog để tránh bất kỳ thay đổi nào trong khi đang chạy sao lưu. Tên và vị trí binlog có thể không thay đổi trong quá trình sao lưu. Vị trí nhật ký nhị phân rất quan trọng trong quá trình sao lưu gia tăng tiếp theo và sẽ được sử dụng làm điểm khởi đầu để bắt đầu sao lưu gia tăng tiếp theo. Sau khi hoàn tất quá trình sao lưu đầy đủ, mysql-backup-post sẽ xóa khóa cơ sở dữ liệu.

Truy vấn khóa: FLUSH TABLES WITH READ LOCK; SELECT SLEEP(86400)

Truy vấn tìm khóa:mysql -u[username] -p[pass] -e "show processlist" | grep "SELECT SLEEP(86400)" | awk '{print $1}'

Yêu cầu​

• quyền root để cài đặt gói và cập nhật mysql.conf
• gói mysql-community-client
• cài đặt automysqlbackup và mysql-incremental

Cài đặt​

Cài đặt gói mysql-community-client cho bản phân phối của bạn.

Lưu ý: sau khi cài đặt MySQL, bạn phải có lệnh 'mysqlshow'.

Cài đặt automysqlbackup:

tải xuống gói từ https://sourceforge.net/projects/automysqlbackup/

tar -xzf [PathYouSavedTarFile] -C /tmp/

cd /tmp/

./install.sh

Trong quá trình cài đặt automysqlbackup, bạn sẽ được hỏi về đường dẫn của automysqlbackup.conf và tệp nhị phân của nó, bạn có thể để mặc định mà không cần thay đổi bất kỳ điều gì.

rm /etc/automysqlbackup/myserver.conf

Cài đặt mysql-incremental: Tải xuống gói từ https://sourceforge.net/projects/mysqlincrementalbackup/

cd /tmp
wget http://downloads.sourceforge.net/project/mysqlincrementalbackup/mysql-incremental.tar.gz
tar xfzmysql-incremental.tar.gz

cp mysql-incremental /etc/automysqlbackup/

chmod 755 /etc/automysqlbackup/mysql-incremental

cp mysql-backup-post /etc/automysqlbackup/

chmod 755 /etc/automysqlbackup/mysql-backup-post

cp mysql-backup-pre /etc/automysqlbackup/

chmod 755 /etc/automysqlbackup/mysql-backup-pre

Cập nhật automysqlbackup.conf:

Tìm các tham số bên dưới, bỏ chú thích và thay đổi chúng:

CONFIG_mysql_dump_username='Tên người dùng Mysql. Nó phải có quyền để lấy Khóa' CONFIG_mysql_dump_password='Mật khẩu' CONFIG_backup_dir='Thư mục sao lưu bạn muốn lưu trữ bản sao lưu đầy đủ và gia tăng' CONFIG_db_names=('databaseName1' 'databaseName2' ) CONFIG_db_month_names=('databaseName1' 'databaseName2' ) CONFIG_mysql_dump_master_data=2 CONFIG_prebackup="/etc/automysqlbackup/mysql-backup-pre" CONFIG_postbackup="/etc/automysqlbackup/mysql-backup-post"

Cập nhật my.cnf:​

Chỉnh sửa tệp cấu hình MySQL:

nano /etc/mysql/my.cnf

1- Định dạng BinLog

Do một số hạn chế về định dạng STATEMENT, tôi khuyên bạn nên đặt định dạng dựa trên ROW. Để biết thêm thông tin, vui lòng xem phần 'khắc phục sự cố' trong hướng dẫn này. Bạn có thể kiểm tra loại định dạng nhật ký nhị phân bằng cách thực hiện truy vấn "select @@binlog_format;". Để sửa đổi định dạng logbin, bạn phải thêm binlog_format = ROW vào mysql.conf hoặc my.cnf.

2- binlog_do_db

Bạn phải chỉ định các cơ sở dữ liệu mà bạn định có các thay đổi liên quan trong nhật ký nhị phân. Xin lưu ý rằng nếu bạn không chỉ định bất kỳ cơ sở dữ liệu nào, bất kỳ thay đổi nào trên bất kỳ cơ sở dữ liệu nào cũng sẽ được ghi vào nhật ký nhị phân. Trong trường hợp này, nếu bạn chọn định dạng STATEMENT, có thể bạn gặp một số sự cố khi khôi phục từ bản sao lưu gia tăng và các tệp binlog. Bạn có thể thêm cơ sở dữ liệu vào tùy chọn này:

binlog_do_db = DATABASENAME1binlog_do_db = DATABASENAME2

3- expire_logs_days

Để có tệp nhật ký nhị phân trong thời gian dài hơn, bạn có thể tăng tham số này lên giá trị cao hơn. Tôi khuyên bạn nên để 60 ngày. Vì vậy, bạn phải thêm hoặc thay đổi nó thành "expire_logs_days = 60".

4- log-bin

Thư mục nơi các bản ghi nhị phân sẽ được lưu trữ. Trong các phiên bản MySQL cũ, mysql-incremental có thể không tìm thấy đường dẫn chính xác. Vì vậy, nếu bạn gặp lỗi về điều này sau khi thực thi mysql-incremental, bạn phải cập nhật tập lệnh mysql-incremental và đặt đường dẫn nhật ký nhị phân.

5- log_slave_updates

Nếu bạn đang thiết lập sao lưu mysql-incremental trên máy chủ phụ, bạn phải bật tùy chọn này. Thông thường, máy phụ không ghi nhật ký cập nhật vào nhật ký nhị phân của riêng nó vì chúng được nhận từ máy chủ chính. Tùy chọn này yêu cầu máy phụ ghi nhật ký các bản cập nhật do luồng SQL của nó thực hiện vào nhật ký nhị phân của riêng nó. http://dev.mysql.com/doc/refman/5.1...ns-slave.html#option_mysqld_log-slave-updates

Chạy automysqlbackup​

Chạyautomysqlbackup theo cách thủ công để có ít nhất một bản sao lưu đầy đủ từ cơ sở dữ liệu bạn chỉ định.

automysqlbackup

Sau khi thực hiện lệnh thành công, hãy kiểm tra tệp /[BackupDirInAutomysqlbackup]/status/backup_info để biết thông tin mới được thêm về bản sao lưu hàng ngày. Để biết chi tiết về lỗi, hãy kiểm tra /var/log/Backup_Post_Pre_log . Tệp sao lưu sẽ được lưu trữ trong thư mục/[BackupDirInAutomysqlbackup]/daily/[DatabaseName]/ .

Chạy mysql-incremental​

Chạymysql-incremental theo cách thủ công ngay bây giờ để có ít nhất một bản sao lưu mỗi giờ.

mysql-incremental

Trong trường hợp xảy ra lỗi, thông tin chi tiết sẽ được ghi vào tệp"/var/log/Backup_Incremental_Log". Các tệp sao lưu gia tăng sẽ được lưu trữ trong thư mục/[BackupDirInAutomysqlbackup]/IncrementalBackup/ .

Chỉnh sửa crontab gốc​

Bạn có thể lên lịch mysql-incremental trong hơn một giờ. Bạn có thể tìm tổng thời gian sao lưu đầy đủ từ backup_status và sau đó dựa trên giá trị đó, bạn đặt thời gian lên lịch chính xác. Tất nhiên, mysql-incremental backup có cơ chế tìm bất kỳ bản sao lưu đầy đủ nào đang chạy trước khi bắt đầu, do đó không có lo ngại về xung đột giữa sao lưu gia tăng và sao lưu đầy đủ.

crontab -e

5 00 * * * root /usr/local/bin/automysqlbackup25 * * * * root /etc/automysqlbackup/mysql-incremental

Khôi phục cơ sở dữ liệu​

Để khôi phục đến một thời điểm cụ thể (khôi phục tại một thời điểm), trước tiên bạn phải khôi phục một bản sao lưu đầy đủ hàng ngày và sau đó khôi phục các tệp sao lưu gia tăng có liên quan tuần tự. Để làm rõ hơn, sau đây là các bước khôi phục cơ sở dữ liệu testDB. Trong kịch bản mẫu, chúng tôi dự định khôi phục dữ liệu của mình đến ngày 01-05-2015 lúc 2 giờ sáng. Chúng tôi đã đặt /backup làm thư mục sao lưu chính và testDB làm cơ sở dữ liệu mục tiêu:

1- mysql -u root -p DatabaseName < /backup/daily/testDB/daily_DatabaseName_2015-05-16_00h05m_Saturday.sql.gz2- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_00h25m.13- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_01h25m.24- mysql -u root -p DatabaseNAme < /backup/IncrementalBackup/2015-5-01_Incremental/testDB/testDB_IncrementalBackup_2015-5-01_02h25m.3

Ghi chú quan trọng và khắc phục sự cố​

MySQL hỗ trợ các định dạng khác nhau cho nhật ký nhị phân. Một số phiên bản Mysql sử dụng 'statement-based' làm định dạng binlog mà loại binlog này có một số hạn chế mà chúng ta phải chú ý đến khi chúng ta có ý định sử dụng nó trong quy trình sao lưu gia tăng. Khi mysql được đặt thành định dạng statement-base, nó không thể lọc chính xác dựa trên cơ sở dữ liệu. Nếu bạn đặt 'USE hoặc \u' để thay đổi cơ sở dữ liệu rồi cập nhật cơ sở dữ liệu khác không có trong binlog-do-db, thì câu lệnh sẽ được ghi vào tệp binlog mà nó không phải là trạng thái mong muốn! và sẽ phát hiện ra một số vấn đề khi khôi phục dựa trên cơ sở dữ liệu cụ thể và cũng như nếu bạn thay đổi sang cơ sở dữ liệu khác không có trong binlog-do-db và cập nhật cơ sở dữ liệu có trong binlog-do-db, thì câu lệnh sẽ không được ghi vào tệp binlog. Mục đích của chúng tôi khi thêm cơ sở dữ liệu vào binlog-do-db là để lọc dựa trên cơ sở dữ liệu, nhưng nó không hoạt động như mong đợi. Nếu USE hoặc \u không được thực thi trước khi chạy truy vấn, mysqlbinlog không thể trích xuất 'truy vấn cập nhật' liên quan đến một cơ sở dữ liệu. Chúng tôi sẽ giải thích thêm vấn đề này bằng các kịch bản dưới đây:

cơ sở dữ liệu: - binlog - person (bảng) - binlog2 - person (bảng) binlog-do-db=binlog2 (giả sử chỉ có những thay đổi trong cơ sở dữ liệu này được ghi vào tệp binlog)--------Kịch bản 1---------\u binlog2chèn vào person (dữ liệu) giá trị ('17') ---> đã đăng nhập binlog *trạng thái mong muốn*chèn vào binlog.person (dữ liệu) giá trị ('25'); ---> đã đăng nhập binlog (cơ sở dữ liệu mục tiêu là 'binlog') *trạng thái không mong muốn*--------Kịch bản 2---------\u binlogchèn vào person (dữ liệu) giá trị ('17') ---> không được ghi vào binlog *trạng thái mong muốn*chèn vào binlog2.person (dữ liệu) giá trị ('25'); ---> không được ghi vào binlog (cơ sở dữ liệu đích là 'binlog2') *trạng thái không mong muốn* vì cơ sở dữ liệu binlog2đã bắt đầu thay đổi, vì vậy chúng ta muốn có thay đổi này, nhưng nó sẽ không được ghi vào tệp logbin--------Kịch bản 3---------nếu bạn chỉ kết nối với cơ sở dữ liệu mà không có bất kỳ câu lệnh USE hoặc \u nào, tất cả các bản cập nhật trên bất kỳ cơ sở dữ liệu nào sẽ được ghi vào nhật ký, nhưng mysqlbinlog không thể lọcdựa trên cơ sở dữ liệu cụ thể, vì vậy đó không phải là trạng thái mong muốn cho mục đích của chúng ta trong sao lưu gia tăng. Sử dụng USE hoặc \u trước khi thực hiện các truy vấn cập nhật là rấtquan trọng. Bởi vì mysqlbinlog tìm thấy các truy vấn cập nhật dựa trên câu lệnh USE trong tệp binlog.

Giải pháp cho vấn đề đã đề cập​

1) Bằng cách xác định người dùng trên cơ sở dữ liệu theo cách mà mỗi người dùng chỉ có quyền truy cập vào một cơ sở dữ liệu để cập nhật (người dùng ứng dụng) và khi kết nối với cơ sở dữ liệu, tên cơ sở dữ liệu phải được chỉ định. Tất nhiên, hầu hết các ứng dụng đều có tệp cấu hình chứa thông tin xác thực và tên cơ sở dữ liệu, do đó, trong trường hợp đó, bạn sẽ không có quyền truy cập chéo vào cơ sở dữ liệu và sẽ không phải lo lắng về việc sử dụng "\USE hoặc \u".

2) Nếu bạn sử dụng định dạng binlog theo hàng, thì tất cả các vấn đề đã đề cập sẽ biến mất. Nói cách khác, định dạng theo hàng là phương pháp phù hợp hơn nhiều cho binlog. https://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html

Tệp nhật ký​

Tôi đã cố gắng ghi lại mọi thứ trong một tệp nhật ký để bạn có thể tìm thấy đủ thông tin trong nhật ký:

/var/log/Backup_Post_Pre_log
/var/log/Backup_Incremental_Log
/[SpecifiedBackupDirInAutomysqlbackup.conf]/status/backup_info

Tệp "backup_info" chứa thông tin chi tiết thông tin về quá trình sao lưu và thời điểm sao lưu hoàn tất (Thời gian được tính theo định dạng Giờ Unix). Nó chứa tên binlog và vị trí của thời điểm bắt đầu sao lưu, loại sao lưu, số lượng bản sao lưu kể từ bản sao lưu đầy đủ cuối cùng và thời lượng sao lưu.

Mẫu backup_info:

1431043501,mysql-bin.000026,120,Hàng ngày,2015-05-08,0,241431044701,mysql-bin.000026,120,Hourly,2015-05-08,1,1

Sau đây là mô tả về các giá trị khác nhau:

1) 1431043501 : biểu thị thời gian sao lưu hoàn tất. Bạn có thể chạy lệnh date --date @1431043501 trên máy chủ đã sao lưu để xem ở định dạng có thể đọc được của con người. 2) Mysql-bin.000026: biểu thị tên nhật ký nhị phân đã sao lưu đến tệp này. 3) 120: biểu thị vị trí của binlog đã sao lưu đến vị trí này trong nhật ký nhị phân đã được thực hiện. 4) Daily/Hourly: biểu thị loại sao lưu. Daily có nghĩa là sao lưu đầy đủ bằng tập lệnh automysqlbackup và Hourly được thực hiện bằng tập lệnh mysql-incremental. 5) 2015-05-08: Ngày sao lưu đã được thực hiện. Ngày này sẽ được sử dụng để tạo thư mục sao lưu gia tăng và cũng là cơ sở để khôi phục các bản sao lưu hàng giờ. Trong quy trình khôi phục, trước tiên một bản sao lưu đầy đủ được khôi phục và sau đó tuần tự các bản sao lưu gia tăng khác được khôi phục. 6) 0: biểu thị số bản sao lưu từ bản sao lưu đầy đủ trước đó. 0 có nghĩa là bản sao lưu đầy đủ và những số khác có nghĩa là hàng giờ. Con số này rất quan trọng trong quy trình khôi phục. 7) 24: Thời gian sao lưu tính bằng giây.

Báo cáo lỗi​

Bạn có thể báo cáo lỗi hoặc đưa ra đề xuất và đánh giá của mình tại https://sourceforge.net/projects/mysqlincrementalbackup.