mysql 1z0-908 考点整理（关键词）

文章

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

20250618172757

  • 题库答案：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.（创建的备份是一个一致的数据转储）

image-20250620153627362

image-20250620153813634

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.