replication
OS copy commands
binary logging
MySQL enterprise backup
distributed replicated block device
snapshots
Updates are not possible during these backups.
When backups occurs, the server is in an inaccessible mode.
They do not impact system performance.
They permit applications to only read data.
They lock data using multiversion concurrency control (MVCC).
daily/hourly supplemental backups containing live data
backups using mysqldump with the –where option
weekly conditional backups of large transactional tables containing only fixed or historical data
multiple backups of data modifications taken each hour to minimize exposure during each day
backups using mysqlbackup from a replication slave
MySQL enterprise backup
the mysqldump utility
the mysqlpump utility
snapshots
OS copy commands
SQL statements
They use a snapshot capability that is external to MySQL.
They can be restored to MySQL databases on machines with different architectures than the one where the backup was created.
They can be taken to back up both local and remote MySQL servers.
They require the MySQL server to be running during the backups.
They generate SQL statements that can be used to recreate the orignal databases and tables.
They can identify objects that can cause damage and skip them during the backup.
It backs up both local and remote MySQL servers
It can reload a database on another server, running a different architecture
It recovers data in the event of a hardware failure
It enables the creation of live backups
It creates a SQL script that can be executed on a MySQL server
replication
OS copy commands
binary logging
MySQL enterprise backup
distributed replicated block device
snapshots
答案:
replication
distributed replicated block device
snapshots
说明:
Physical Backup Conditions
• The server must not modify data files during backup.
– If you copy the live data files, prevent writes to those files:
— For InnoDB: MySQL server shutdown is required.
— For MyISAM: Lock tables to allow reads but not changes.
• You can also minimize the effect to MySQL and applications by using techniques that
separate the data files being backed up from the MySQL server:
– Snapshots
– Replication
– DRBD or other methods that copy the whole filesystem
Updates are not possible during these backups.
When backups occurs, the server is in an inaccessible mode.
They do not impact system performance.
They permit applications to only read data.
They lock data using multiversion concurrency control (MVCC).
答案:
They permit applications to only read data.
Updates are not possible during these backups.
说明:
Warm Backups
• Take place while the data is being accessed
– In most cases, the data cannot be modified while the backup is taking place.
• Have the advantage of not having to completely lock out end users
– However, the disadvantage of not being able to modify the data while the backup is
taking place can make this type of backup not suitable for certain applications.
• Might result in performance issues because you cannot modify data during the backup.
– Updating users may be blocked for a long duration of time.
daily/hourly supplemental backups containing live data
backups using mysqldump with the –where option
weekly conditional backups of large transactional tables containing only fixed or historical data
multiple backups of data modifications taken each hour to minimize exposure during each day
backups using mysqlbackup from a replication slave
答案:
multiple backups of data modifications taken each hour to minimize exposure during each day
backups using mysqlbackup from a replication slave
说明:
More Complex Strategies
Combine multiple backup techniques to create more complex strategies:
• Example using full and binary log backups:
– Nightly backups using mysqlbackup from a replication slave
– Multiple binary log backups each hour to minimize exposure during each day
• Example using partial backups:
– Technique:
— mysqldump with –where option
— SELECT INTO OUTFILE
– Weekly conditional backups of large transactional tables containing only fixed or
historical data
— Data that does not change, for example, fulfilled orders that have passed the “return by”
date
– Daily/hourly supplemental backups containing live data
MySQL enterprise backup
the mysqldump utility
the mysqlpump utility
snapshots
OS copy commands
SQL statements
答案:
the mysqldump utility
the mysqlpump utility
SQL statements
说明:
Logical Backups
Perform a complete data dump by using SQL statements, mysqldump or mysqlpump.
• These data dumps are based on a specific point in time but are the slowest of all the
backup techniques.
• Advantage:
– The process creates a SQL script that you can
execute on a MySQL server or data files that you
can import into database tables.
– You can use the script to reload the database on
another host, running a different architecture or
different version of MySQL Server.
• Disadvantage:
– By default (and always for non-InnoDB tables), mysqldump and mysqlpump lock
tables during the dump, which prevents users from modifying data during the backup.
They use a snapshot capability that is external to MySQL.
They can be restored to MySQL databases on machines with different architectures than the one where the backup was created.
They can be taken to back up both local and remote MySQL servers.
They require the MySQL server to be running during the backups.
They generate SQL statements that can be used to recreate the orignal databases and tables.
They can identify objects that can cause damage and skip them during the backup.
答案:
They can be taken to back up both local and remote MySQL servers.
They require the MySQL server to be running during the backups.
They generate SQL statements that can be used to recreate the orignal databases and tables.
It backs up both local and remote MySQL servers
It can reload a database on another server, running a different architecture
It recovers data in the event of a hardware failure
It enables the creation of live backups
It creates a SQL script that can be executed on a MySQL server
答案:
It recovers data in the event of a hardware failure
It enables the creation of live backups
The post MySQL 8.0 for Database Administrators OCP 课程习题10 first appeared on C1G军火库.