展开查看详情
1 .Database Backup and Recovery
Best Practices
(with a focus on MySQL)
• Manjot Singh, Data & Infrastrustructure Architect
2 .About Manjot Singh
• MySQL Fanatic
• Long time user (~16 years)
• Database and Systems Administrator for a
number of years
• Oracle, SQL Server, MySQL
• Now an Architect at Percona, helping many
companies scale databases and systems
and implement best practices
3 .About Percona
• Percona Delivers enterprise-class software, support and consulting
solutions for MySQL and MongoDB
• We have a global 24x7x365 consulting team
• Percona consults with some of the largest companies on the internet
as well as new startups
• Percona Consultants have decades of experience solving complete
database and performance issues
4 . Do I Need to Take
Database Backups?
6 .Why Backup and Recovery?
• Backup and Recovery processes
are a critical part of any
application infrastructure.
• A well tested backup and
recovery system can be the
difference between a minor
outage and the end of your
business.
7 .Recovery Requirements Determine Backups
Three important questions define backup and
recovery systems for organizations:
• Recovery Time Objective
• Recovery Point Objective
• Risk Mitigation
8 .Recovery Time Objective (RTO)
• The amount of time that may pass
during a disruption before it exceeds
the maximum allowable time
specified in the Business Continuity
Plan
• How long does it take you to get back
up?
9 .Recovery Point Objective (RPO)
• Duration of time and service
level within which a business
process must be restored after a
disaster in order to avoid
unacceptable consequences
associated with a break in
continuity
• “How much data can I lose?”
• When was the last backup
before failure?
10 .Risk Mitigation
What failure scenarios must the
data be protected against?
11 .Risk Mitigation
Backups:
• Can help mitigate failure scenarios
• (Multiple) Host Failure
• (Multiple) Data Center Failure
• Data Corruption or Loss
• Satisfy Legal Regulations
• Legislation
• Regulation
• Fulfill Industry Standards
• PCI
• HIPAA
• etc
12 .Designing a Backup and Recovery System
• Based on the RTO, RPO, and address
whatever risks are applicable
• Not all data should have the same
requirements
• For example HR database could have a longer
RTO than your customer facing product database.
Cost of 1 hour outage for HR is small compared
to not accepting orders for 1 hour.
14 .Physical or Binary Backup
• A physical backup typically refers to a copy of the entire on disk
database
• Used to mitigate a single or multiple host failure
• Can build replicas
• Quick Full Recovery Time
• Slow to recover single row or table (user error)
15 .Physical or Binary Backup
• MySQL
• Percona XtraBackup (takes a copy of the data directory and applies
logs)
• MySQL Enterprise Backup
• LVM or VM Snapshot
16 .XtraBackup - Backup
Standard use:
[mysql-master]# xtrabackup --backup --target-dir=/backup
--user=root --password=X
[mysql-master]# xtrabackup --prepare --target-dir=/backup
Prepare was previously apply-log
17 .XtraBackup - Restore
Standard use:
[mysql-restore]# scp –r mysql-master:/backup
[mysql-restore]# xtrabackup --copy-back --target-dir=/backup/
[mysql-restore]# chown –R mysql.mysql /var/lib/mysql
18 .Logical Backup
• Generates SQL files containing data that can regenerate a database
• Easily restore single row, table, or database
• Works across versions
• SQL (such as mysqldump) can be parsed with standard UNIX tools
• Restore process automatically replicated
• Long Full Restore Time
19 .Logical Backup
• MySQL
• mysqldump
• mysqlpump (5.7+)
• mydumper
• Delayed slave can also fit similar use cases
20 .mysqldump - Backup
Standard use:
[mysql-master]# mysqldump –triggers –routines –events
–single-transaction –all-databases -r dump.sql
21 .mysqldump - Restore
Standard use:
[mysql-restore]# scp mysql-master:dump.sql
[mysql-restore]# mysql < dump.sql
[mysql-restore]# # OR:
[mysql-restore]# mysql
mysql> source dump.sql
22 .Which is best? BOTH!
Binary Backups Address Host Logical Backups address data
Failure corruption and user error
23 .Binary Logs
• Addresses RPO
• Logs should be rotated and backed up
• Combine with a Binary Backup to allow Point in Time recovery
• If you backup logs every hour, your RPO is 1 hour in case of a failure
• Can be reduced, but not to the point where the i/o and network traffic interfere
with database operations
• Can stream logs for real-time RPO
• Adding complexity increases recovery time due to more complicated
recovery procedures
24 .Binary log streaming
mysqlbinlog --raw --read-from-remote-server --stop-never --host mysql-
master --port 3306 -u repl –p somepass
27 .Binary log streaming
nohup livebinlog.sh server2.conf 2>&1 > livebinlog-server2.log &
More information:
https://www.percona.com/blog/2012/01/18/backing-up-binary-log-files-with-mysqlbinlog/
29 .Retention
• Protect Against Data Center Failure
• Keep multiple backups
• Counting on a single backup is a risk
• Corruption
• Malicious data
• User error
• Store important data offsite
§ Cloud storage – Google Nearline,
Amazon Glacier
§ Vaulting – Ship drives/tapes
§ DR site
热门城市
