binary log encryption feature

• It can be set at run time.
• It requires a keyring plugin.

---

mysql_config_editor program

• It will use client options by default unless you provide –login-path.
• It manages the configuration of client programs.

---

mysqlbackup | will be backed up during a full backup

• *.ibd file.
• ibd_logfile* file.

---

Cluster:dba.dropMetadataSchema() | innodb

• The mysql innodb_cluster_metadata_schema is dropped from all reachable memebers of the cluster.
• Group replication is still operational but innoDB Cluster must be reimported under MYSQL Shell.

---

using backups of the binary log

• Multiple binary logs can be used to restore data.
• They allow for point-to-time recovery of the data.

---

A clean shutdown was performed……DB to restart cleanly

• ibddata1.
• mysql.ibd.

---

systemd tablespace

• change buffer.
• doublewrite buffer.

---

binary logs used in asynchronous replication

• They contain events that describe database changes on the master.
• They are pulled from the master to the slave.

---

monitor the global status of InnoDB locking

• INFORMATION_SCHEMA.INNODB_METRICS.
• SHOW ENGINE INNODB STATUS.

---

plain text

• PAM
• LDAP

---

types of information stored in the MySQL data dictionary

• access control list
• view definitions
• strored procedure definitions
• table definitions

---

general tablespace

• A new table can be create explicitly in a general tablespace.
• An existing table can be moved into a general tablespace.

---

connect without entering his or her username and password

• /etc/my.cnf file
• $HOME/.mylogin.cnf file
• $HOME/.my.cnf file

---

SHOW SLAVE STATUS \G

• a large
• wait for more data

---

binary logs userd in asysnronous repication

• They are pulled from the master to the slave.
• They contain events that describe database changes on the mastar.

---

shut down the MySQL server

• mysqladmin shutdown
• mysql>SHUTDOWN
• systemctl stop mysqld

---

dba.rebootClusterFromCompleteOutage()

• It performs InnoDB Cluster instances rolling restart.
• It is not mandatory that all instances are running and reachable before running the command.

---

cluster.addInstance('\<user\>@\<host\>:\<port\>',recoveryMethod:'clone')

- needs the BACKUP_ADMIN privilege.
- InnoDB tablespaces outside the datadir are able to be cloned.
- A target instance must exist.……joined to the cluster.

---

visible in the mysql system database

• time zone
• plugins
• help topics

---

binary log to rotate

• max_binlog_size exceeded
• FLUSH LOGS executed

---

MySQL replication

• Binary logging must be enabled on the master in order to replicate to other instances.
• Replication can use only TCP/IP connections.
• Each instance in a replication topology must have a unique ID.

---

InnoDB data-at-rest encryption

• It supports all indexes transparently.
• It decrypts data for use in memory.

---

MySQL Enterprise Firewall

• System tables named firewall_user and firewall_whitlist in the mysql database provide persistent storage of firewall data.
• It is available only in MySQL Enterprise version.
• It provides INFORMATION_SCHEMA tables that enable views into firewall data.

---

MySQL Enterprise Firewall

• blocking of potential threats by configuring pre-approved whitlists.（通过配置预先批准的白名单来阻断潜在威胁）

• recording incoming SQL statement to facilitate the creation of a whitlist of permitted commands.（记录传入的 SQL 语句，以方便创建允许命令的白名单）

---

storage engines provide a view of the data consistent with the storage system at any moment

• NDB
• InnoDB

---

• 题库答案：AE | AI答案：AF

---

MySQL data directory binary

……

mysqldump –delete-master-logs –all-database > /backup/db_backup.sql

• All databases excluding master metadata,are backed up to the output file.
• All non-active binary logs are removed from the master.

---

tarball binaries

• basedir=/app/mysql
• datadir=/app/data

---

semi-synchronous replication

• No committed transcations are lost.
• Reads from the slave can return outdated data for some time,until it applies all transcations from its relay log.(从库读取的数据在一段时间内可能会是过时数据，直到它应用完中继日志中的所有事务)

---

file-system snapshot to back up MySQL

• The backup window is almost zero from the perspective of the application.(从应用程序的角度来看，备份窗口几乎为零)
• They work best for transcation storage engines that can perform their own recovery when restored.(对于那些在恢复时能够自行执行恢复操作的事务型存储引擎而言，它们效果最佳)
• There is a slight performance cost while the snapshot is activ.

---

display indexes on the part table

• SHOW INDESES FROM manufacturing.parts
• DESCRIBE manufacturing.parts

---

requirements must be enabled for group replication

• binary log ROW format.
• slave updates logging.
• primary key or primary key equivalent on every table.

---

raw binary backups

• The data format is identical to how MySQL stores the data on disk.
• They are faster than logical backups because the process is a simple file or file system copy.

---

hash join algorithm

• EXPLAINFORMAT=JSON.
• EXPLAIN ANALYZE.

---

Cannot update GTID_PURGED with the Group Replication plugin

• Remove the @@GLOBAL.gtid_purged statement from the dump file.
• Create the backup bi using the –set-gtid-purged=OFF option.

---

types if InnoDB tablespaces

• temporary table tablespace.
• undo tablespaces.
• data tablespaces.

---

implementing GTID replication

• On the slave,alter the MySQL master connection setting with:CHANGE MASTER TO MASTER_AUTO_POSITION=1;
• Restart MySQL(master and slave) with thest options enabled:–gtid_mode=ON –log-bin –log-slave-updates –enforce-gtid-consistency

---

a newly created role

• It is created as a locked account
• It can be dropped using the DROP ROLE statement

---

mysqld-auto.cnf

• This file is for storing MySQL server configuration options in JSON format.
• It is read and processed at the end of startup configuration.

---

low on space due to binary logs.

• Use SET GLOBAL binlog_expire_logs_seconds=\<value> and run the FLUSH BINAEY LOGS command.
• Use PURGE BINARY LOGS to \<binlog_name>

---

The disk space occupied by the binary log files continues to grow.

• Execute the PURGE BIARY LOGS statement.
• Set the binlog_expire_logs_seconds variable.

---

report all the current connections

• SELECT * FROM performance_schema.threads.
• SELECT * FROM information_schema.processlist.

---

MySQL Enterprise Monitor

• Determine the availability of monitored MySQL servers.
• Create customized alerts and provide notification alters.

---

monitored using MySQL Enterprise Monitor’s

• security-related advisor warnings.(与安全相关的顾问警告)
• MySQL Query Analysis data.
• MySQL Replication monitoring.

无代理的情况下，只能监控mysql数据库自己的状态

---

multi-source replication

• It relies on relay_log_recovery for resilient operations.
• It dose not attempt to detect or resolve replication conflicts.

---

MySQL Enterprise Monitor Query Analyzer

• It is possible to configure the Query Analysis built-in advisor to get notified about slow query execution.
• It is possible to list and analyze statements in an arbitrary graph range selection from timeseries graphs.

---

benefits of using mysqlbackup instead of mysqldump

• mysqlbackup restores data from physical backups,which are faster than logical backups
• mysqlbackup intergrates tape backup and has the virtual tape option.
• mysqlbackup can back up tables with the InnoDB engine without blocking reducing wait timens due to contention.

---

configure InnoDB Cluster to use SSL for group communication

• An existing InnoDB Cluster must be dissolved and created from scratch to enable SSL for group communicaiton.（若要为组通信启用 SSL，必须解散现有 InnoDB 集群并重新从头创建）
• SSL group communication must be enable at cluster creation time by specifying create Cluster.

SSL mode of a cluster can only be set at the time of creation.

---

valid uses for binary logs on a MySQL instance

• point-in-time recovery.
• replication.

---

display the complete table definition of an InnoDB table

• mysqldump –no-data schema table
• Query the Information Schema

---

differences between logical and physical upgrades

• Physical upgrades leave data in place,whereas logical upgrades require data to be restored from mysqldump-type backups taken before the upgrades. （物理升级会将数据保留在原地，而逻辑升级则需要从升级前通过类似 mysqldump 方式创建的备份中恢复数据）

• Post-upgrade table storage requirements after logical upgrades are usually smaller than that after physical upgrades.（逻辑升级后的表存储需求通常比物理升级后的更小）

---

global buffers shared by all threads

• key_buffer_size
• innodb_buffer_pool_size
• talbe_open_cache

---

mysqlcheck for server maintenance

• The mysqlcheck –optimize –all-database command reclaims free space from table files.
• The mysqlcheck command can be renamed mysqlrepair so that it repairs tables by default.（可以将 mysqlcheck 命令重命名为 mysqlrepair，这样它就会默认执行表修复操作）

---

data dictionary object cache

• LRU
• tablespace_definition_cache

---

asynchronous replication

• You can scale reads by adding multiple slaves.（可以通过添加多个从库来扩展读操作）
• It allows backup to be done on the slave without impacting the master.（它允许在从库上进行备份，而不影响主库）

---

excluded from the InnoDB Cluster creation pricedure

• cluster.forceQuorumUsingPartitionOf()
• cluster.setPrimaryInstance()

---

mysqldump –master-data=2 –single-transcation –result-file=dump.sql mydb

• It executes flush tables with read lock. （它执行带读锁的 FLUSH TABLES 操作）
• The backup created is a consisntent data dump.（创建的备份是一个一致的数据转储）

---

---

provide the most benefit to the instance

• innodb_doublewrite=0
• buffer_pool_size=24G

---

obtain informantion about deadlocks

• Run the SHOW ENGINE INNODB STATUS command from the mysql client.
• Enable the innobd_print_all_deadlocks global parameter.

---

mysqldump –no-create-info –all-database –result-file=dump.sql

• It will not wirte CREATE TABLE statments.

---

show the option files and the order in which they are read

• mysqld –help –verbose

---

the slave I/O thread do?

• connects to the master and ……

---

my.ini | windows platform

• The option file is read by the MySQL service only at start up.

---

provision the new database in datadir as /data/MEB

• …… copy-and-apply-log

---

MySQL Enterprise Monitor Query Analyzer

• Enabling the events statements_history_long consumer allows tracking the longest running query.

---

check the values of the sort_buffer_size session variables of all existing connections

• variables_by_thread（performance_schema table）

---

which feature is provide by multi-source replication

• allowing multiple servers to back up to one server.

---

MySQL Enterprise Transparent Data Encryption(TDE)

• MySQL TDE uses an appropriate keyring plugin to store the keys in a centralized location.

---

transparent data encryption with an existing InnoDB table

• ALTER TABLE t1 ENCRYPTION=’Y’；

---

binlog dump thread do

• It acquires a lock on the binary log for reading each event to be sent to the slave

---

against SQL injection attacks

• installing and configuring the Connection Control plugin.

---

mysqlsh –uri root@localhost:3306 –util check-for-server-upgrade

• It documents any problems with your 5.7 tables to make them ready to upgrade to 8.

---

replay the binary logs

• mysqlbinlog binlog.000003 binlog.000004 binlog.000002 mysql -h 127.0.0.1

---

audit_log=FORCE_LOG_PERMANENT

• It prevents the audit plugin from being removed from the running server.（它阻止了审计插件从运行中的服务器上被移除）

---

mysql –initialize-insecure

• The root password is not create allowing easy access from the same host.

---

back up the databases in parallel

• mysqlpump –include-database=% > full-backup-$(date + %Y%m%d).sql

---

enable rule-based MySQL Auditing capabilities

• mysql < audit_log_filter_linux_install.sql

---

backup-and-apply-log

• The backup can be impacted when DDL operations run during the backup.（备份可能会受到在备份期间运行的 DDL 操作的影响）

---

InnoDB persistent index statics

• Setting innodb_stats_auto_recalc=ON …

---

rotate the error log

• FLUSH ERROR LOGS

---

are most commonly used in a SQL injection attack

• ‘ and "

---

all roles granted to all users automatically

• SET PERSIST activate_all_roles_on_login=ON

---

\<AUDIT_RECORD>

……

</AUDIT_RECORD>

• You must use the audit_log_filter_set_filter() and audit_log_filter_set_user() functions to specify what to log.