Làm cách nào để sao lưu cơ sở dữ liệu mysql lớn?

Gần đây tôi đang làm việc trên một ứng dụng web lớn và phải chuyển nó sang máy chủ mới. Thách thức chính là làm thế nào để sao lưu cơ sở dữ liệu khổng lồ và khôi phục nó trên máy chủ mới. Phpmyadmin không thể sao lưu cơ sở dữ liệu do sự cố hết thời gian chờ với Apache. Vì vậy, tôi quyết định sử dụng tiện ích mysqldump của mysql để sao lưu cơ sở dữ liệu. Mình cũng muốn chuyển dữ liệu sang server mới nên phải nén kết xuất database. nó khá dễ dàng

Sao lưu cơ sở dữ liệu MySQL

$mysqldump -u {username} -p{password} {database} | gzip -9 > [database.sql.gz]

Chúng ta có thể sử dụng tiện ích scp để chuyển kết xuất cơ sở dữ liệu này sang máy chủ mới. nếu bạn không biết về scp thì hãy google và bạn sẽ tìm thấy rất nhiều thông tin về cách sử dụng

Chúng tôi có thể dễ dàng trích xuất dữ liệu bằng cách sử dụng gzip

$gzip database.sql.gz

Khôi phục cơ sở dữ liệu MySQL
Phần này phức tạp vì kích thước kết xuất mysql tăng lên. Một lệnh đơn giản để khôi phục cơ sở dữ liệu là

$mysql -u {username} -p{password} {database} < database.sql

hoặc sử dụng cái này trong trường hợp bạn có tệp nén

gunzip < {database.sql.gz} |mysql -u {username} -p{password} {database}

Điều này lẽ ra phải thực hiện được điều kỳ diệu là khôi phục cơ sở dữ liệu nhưng KHÔNG cho cơ sở dữ liệu lớn hơn. Để khôi phục cơ sở dữ liệu lớn, bạn phải thay đổi tệp cấu hình my. cnf hoặc chỉ định –max_allow_packet =1G trong lệnh khôi phục

[mysqld] section
max_allowed_packet= 1G

Xin lưu ý rằng mysql cho phép tối đa 1G ​​cho max_allowed_packet

Nếu kích thước gói của bạn (không phải kích thước cơ sở dữ liệu) lớn hơn 1G thì hãy xem xét –extended-insert off trong khi kết xuất cơ sở dữ liệu. Sẽ mất nhiều thời gian hơn để khôi phục cơ sở dữ liệu nhưng nó sẽ hoạt động

Nếu bạn có cơ sở dữ liệu mysql ( mariadb ) lớn – giả sử 2GB hoặc hơn – 20GB hoặc 50GB dữ liệu, bạn không thể sao lưu thông qua phpmyadmin hoặc bất kỳ công cụ thông thường nào. Tùy chọn duy nhất ở đây là sử dụng trực tiếp mysqldump

mysqldump -u USER -p --single-transaction --quick --skip-lock-tables DATABASE | gzip > "$(date +"%FT%H%M").sql.gz"

Hãy chia nhỏ điều này thành từng mảnh

–giao dịch đơn lẻ cho mysql biết rằng chúng tôi sắp kết xuất cơ sở dữ liệu và do đó, tất cả các thay đổi lớn đối với bảng có cấu trúc sẽ bị từ chối – e. g. , khi kết xuất với –single-transation đang được xử lý, bạn không thể thực hiện các truy vấn chứa ALTER, CREATE, DROP, RENAME hoặc TRUNCATE TABLE

–nhanh chóng, đây là tùy chọn, nhưng rất khuyến khích. Đây không phải là nhiều về kích thước tổng thể của cơ sở dữ liệu, mà là kích thước của các bảng riêng lẻ. Lệnh này sẽ sao lưu từng hàng, thay vì kéo toàn bộ bảng cùng một lúc – nghĩa là nếu bạn có các bảng có 50 triệu hàng, sử dụng –quick sẽ giúp sao lưu toàn bộ nhanh chóng hơn nhiều

–skip-lock-tables đây là điều bắt buộc. Nếu bạn đang chạy máy chủ sản xuất và bạn không thể đặt nó xuống để thực hiện quá trình sao lưu, bạn cần bao gồm tùy chọn bỏ qua khóa – nếu không thì quá trình kết xuất sẽ bị lỗi ngay khi một số nội dung bảng bị thay đổi trong quá trình xử lý

gzip > sẽ tự động nén bản sao lưu một cách nhanh chóng – nó sử dụng nhiều CPU hơn nhưng kết quả sẽ là một tệp gzip nhỏ hơn nhiều

“$(ngày +”%FT%H%M”). sql. gz” tạo tên tệp nhanh chóng – thay vì sử dụng tên tệp cố định, chẳng hạn như sao lưu. sql. gz – sẽ trực tiếp ghi đè lên bản sao lưu cũ hơn, thay vào đó, lệnh này sẽ tạo một tên tệp có tên 2019-11-20T0843. sql. gz – để bạn có được ngày và giờ hiện tại

Tùy chọn bổ sung

Có một tùy chọn bổ sung mà bạn có thể muốn đưa vào nếu muốn bỏ qua một số bảng, e. g. các bảng bộ đệm mà bạn không cần sao lưu hoặc nội dung tạm thời. Để làm được điều đó, bạn có thể sử dụng –ignore-table=DATABASE. some_cache_table để lệnh đầy đủ sẽ như thế này

mysqldump -u USER -p --single-transaction --quick --skip-lock-tables --ignore-table=DATABASE.some_cache_table DATABASE | gzip > "$(date +"%FT%H%M").sql.gz"

Nếu bạn muốn tự động hóa quá trình sao lưu thông qua cron, thì việc nhập mật khẩu theo cách thủ công không phải là tùy chọn dành cho bạn. Bạn cũng không muốn đưa mật khẩu trực tiếp vào lệnh – bạn có thể, nhưng nó không an toàn. Đi như thế này

mysqldump -u USER -pPASSWORD --single-transaction --quick --skip-lock-tables DATABASE | gzip > "$(date +"%FT%H%M").sql.gz"

Không có khoảng cách giữa -p và MẬT KHẨU

Như đã nói, đây không phải là một câu trả lời an toàn, không chuyên nghiệp. Cái đúng sẽ là tạo một tệp mà chỉ bạn mới có thể truy cập, e. g

Chỉnh sửa /. sqlpwd và đặt thông tin đăng nhập thực tế của bạn vào đó, e. g

[mysqldump]
user=USER
password=password

Và bây giờ chỉ cần sửa đổi lệnh mysqldump để xem tệp này để biết thông tin xác thực. Lưu ý rằng nhờ có chmod và chown mà bạn mới có thể truy cập tệp

$gzip database.sql.gz

Tệp mặc định-thêm mở rộng tệp cấu hình mysql, vì vậy bạn thực sự có thể sử dụng nó cho nhiều mục đích hơn là chỉ chỉ định tên người dùng và mật khẩu. Bạn cũng có thể bao gồm các bảng nhanh, bỏ qua khóa và các tham số khác trong đó. Xem https. // nhà phát triển. mysql. com/doc/refman/8. 0/vi/tệp tùy chọn. html để được hướng dẫn

Tôi có nên sao lưu cơ sở dữ liệu MySQL của mình không?

MySQL có thể xử lý cơ sở dữ liệu lớn không?

MySQL có giới hạn kích thước không?

Một số tùy chọn để tạo bản sao lưu MySQL là gì?