11.Skill Check Performing Backups

How does the – – master-data option of the mysqldump command ensure data consistency during a database backup? (Choose two)

It starts a new binary log.

It acquires a global lock at the beginning of the backup operation.

It records the binlog position in the backup file.

It creates database and table structures but does not dump the data.

It locks tables during backup.

Which are features of the mysqlpump utility? (Choose three)

dumping InnoDB CREATE TABLESPACE statements

restoring databases using dumped data files

parallel processing of the database dump process

dumping user accounts as CREATE USER/GRANT statements

taking physical backups of databases

faster reloading of secondary indexes for InnoDB tables

Which command forces the current binary log to close and the next incremental binary log to open?

mysql> GRANT RELOAD ON . TO ‘backupuser’@’localhost’;

mysql> FLUSH LOGS;

mysql> PURGE BINARY LOGS TO ‘binlog.000048’;

mysql> FLUSH BINARY LOGS;

Which are features or capabilities of multisource replication? (Choose three)

consolidating data from multiple servers to a single server

preventing slaves from making backups

backing up files not requiring additional storage or processing on the master

merging table shards

adding processing load on the master

backing up multiple servers to a single server

Which privileges does a user need to execute the mysqldump command? (Choose three)

SELECT for dumped tables

CREATE on each of the dumped objects

TRIGGER for dumped triggers

SHOW VIEW for dumped views

RELOAD on all databases and tables

ALTER on the database

Which commands back up the “employees” database to a single SQL script? (Choose two)

# mysql -uemployee -p employees < /labs/firewall_training.sql

# mysqldump -uroot -p –tab=/backups employees

# mysqlpump -uroot -p –databases employees \ > /backups/pump/pump.sql

# mysqldump -uroot -p –single-transaction –master-data=2 \ employees > /backups/employees_full.sql

# mysqlimport -uroot -p emps2 /backups/dept_manager.txt

---

11.Skill Check Performing Backups

How does the – – master-data option of the mysqldump command ensure data consistency during a database backup? (Choose two)

It starts a new binary log.

It acquires a global lock at the beginning of the backup operation.

It records the binlog position in the backup file.

It creates database and table structures but does not dump the data.

It locks tables during backup.

答案：

It records the binlog position in the backup file.

It locks tables during backup.

说明:
Ensuring Data Consistency with mysqldump
Ensuring consistency:
• –master-data option alone
– Locks all tables during backup with FLUSH TABLES WITH READ LOCK
– Records the binlog position as a CHANGE MASTER TO statement in the backup file
— –master-data=2 records the position as a comment
• –master-data and –single-transaction options used together
– Does not lock tables – only InnoDB table consistency is guaranteed
– Acquires a global lock at the beginning of the backup operation to obtain a consistent
binary log position
• –lock-all-tables
– Satisfies consistency by locking all tables for the duration of the whole dump
• –flush-logs
– Starts a new binary log

Which are features of the mysqlpump utility? (Choose three)

dumping InnoDB CREATE TABLESPACE statements

restoring databases using dumped data files

parallel processing of the database dump process

dumping user accounts as CREATE USER/GRANT statements

taking physical backups of databases

faster reloading of secondary indexes for InnoDB tables

答案:
parallel processing of the database dump process

dumping user accounts as CREATE USER/GRANT statements

faster reloading of secondary indexes for InnoDB tables

说明:
mysqlpump
mysqlpump is very similar to mysqldump, but with the following enhancements:
• Provides better performance than mysqldump by extracting data in parallel threads
– mysqlpump divides the dump process into several subtasks and then adds these
subtasks to a multithreaded queue.
– This queue is then processed by N threads (two by default).
• Enables better control over which database objects to dump on each thread
• Dumps users as CREATE USER/GRANT statements instead of as INSERTs into the
mysql system database
• Enables compressed output
• Displays a progress indicator
• Provides faster secondary index reloading for InnoDB tables

Which command forces the current binary log to close and the next incremental binary log to open?

mysql> GRANT RELOAD ON . TO ‘backupuser’@’localhost’;

mysql> FLUSH LOGS;

mysql> PURGE BINARY LOGS TO ‘binlog.000048’;

mysql> FLUSH BINARY LOGS;

答案：
mysql> FLUSH LOGS;

Which are features or capabilities of multisource replication? (Choose three)

consolidating data from multiple servers to a single server

preventing slaves from making backups

backing up files not requiring additional storage or processing on the master

merging table shards

adding processing load on the master

backing up multiple servers to a single server

答案：
consolidating data from multiple servers to a single server
merging table shards
backing up multiple servers to a single server

说明：
Backing Up from Multiple Sources to a Single Server
• Multisource replication can be used to:
– Back up multiple servers to a single server
– Merge table shards
– Consolidate data from multiple servers to a single server
• The slave creates a replication channel for each master from which it receives
transactions.
– Use the CHANGE MASTER TO … FOR CHANNEL channelname syntax:
• Replicate from all channels concurrently or start and stop individual channels:
CHANGE MASTER TO …
MASTER_LOG_FILE=’binlog.000006′,
MASTER_LOG_POS=143 FOR CHANNEL ‘shard-1’;
START SLAVE IO_THREAD FOR CHANNEL ‘shard-1’;

Which privileges does a user need to execute the mysqldump command? (Choose three)

SELECT for dumped tables

CREATE on each of the dumped objects

TRIGGER for dumped triggers

SHOW VIEW for dumped views

RELOAD on all databases and tables

ALTER on the database

答案：
SELECT for dumped tables

TRIGGER for dumped triggers

SHOW VIEW for dumped views

说明：
Privileges Required for mysqldump
You must have the following privileges to use mysqldump:
• SELECT for dumped tables
• SHOW VIEW for dumped views
• TRIGGER for dumped triggers
• LOCK TABLES (unless you use the –-single-transaction option)
• Other options might require extra privileges. For example:
– To use the –flush-logs or –master-data options, you must have the RELOAD
privilege.
– To create a tab-delimited output with the –tab option, you must have the FILE
privilege.
– To use the –routines option to back up stored functions and procedures, you
must have the global SELECT privilege.

Which commands back up the “employees” database to a single SQL script? (Choose two)

# mysql -uemployee -p employees < /labs/firewall_training.sql

# mysqldump -uroot -p –tab=/backups employees

# mysqlpump -uroot -p –databases employees \ > /backups/pump/pump.sql

# mysqldump -uroot -p –single-transaction –master-data=2 \ employees > /backups/employees_full.sql

# mysqlimport -uroot -p emps2 /backups/dept_manager.txt

答案：
# mysqlpump -uroot -p –databases employees \ > /backups/pump/pump.sql

# mysqldump -uroot -p –single-transaction –master-data=2 \ employees > /backups/employees_full.sql