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
- 题库答案: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.