📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
文章目录
- 前言
- 🚀 1.用户连接
- 🚀 2.索引
- 🚀 3.参数文件
- 🚀 4.日志文件
- 🚀 5.网络
- 🚀 6.备份恢复
- 🚀 7.数据库升级
前言
最近完成了MySQL OCP考试，顺利拿到了证书，这个过程并不是想象中那么顺利， 有些点还是值得跟大家分享一下题库。
🚀 1.用户连接
Choose the best answer.
Examine these statements and output:
mysql> GRANT PROXY ON accounting@localhost TO ’ ‘@’%’ ;
mysql> SELECT USER(), CURRENT_USER(), @@proxy_user;
Which statement is true?
A) The user failed to define a username and the connecting username defaulted to ’ ‘@’%‘.
B) The user is authorized as the rsmith@localhost user.
C) The user is authenticated as the anonymous proxy user ’ ‘@’%’.
D) The user is logged in with --user=accounting as an option.
E) The user is authorized as the accounting@localhost user.Answer：E
分析：proxy 户创建构成
– create proxy account
CREATE USER ‘employee_proxy’@‘%’ identified by ‘123456’;
– create proxied account and grant its privileges;
CREATE USER ‘employee’@‘localhost’ identified by ‘123456’;
GRANTALL ON . TO ‘employee’@‘localhost’;
– grant to proxy account the
GRANT PROXY ON ‘employee’@‘localhost’ TO ‘employee_proxy’@‘%’;
CURRENT_USER()的权限是真正当前作用的用户权限 🚀 2.索引
Choose the best answer.
Examine this statement, which executes successfully
You want to improve the performance of this query:
SELECT Name
FROM world.city
WHERE Population BETWEEN 1000000AND 2000000;
Which change enables the query to succeed while accessing fewer rows?
A)ALTER TABLE world.city ADD INDEX (Name) ;
B)ALTER TABLE world.city ADD SPATIAL INDEX (Name) ;
C)ALTER TABLE world.city ADD FULLTEXT INDEX (Name) ;
D)ALTER TABLE world.city ADD FULLTEXT INDEX (Population) ;
E) ALTER TABLE world.city ADD SPATIAL INDEX (Population) ;
F) ALTER TABLE world.city ADD INDEX (Population) ;
Answer：F
🚀 3.参数文件
Choose four.
Anewly deployed replication master database has a 10/90 read to write ratio.
The complete dataset is currently 28G but will never fluctuate beyond ±10%.
The database storage system consists of two locally attached PCI- E Enterprise grade disks
(mounted as /data1 and /data2)
The server is dedicated to this MySQL Instance.
System memory capacity is 64G.
The my.cnf file contents are displayed here:
[mysqld]
datadir=/data1/
innodb_buffer_pool_size=28G
innodb_log_file_size=150M
Which four changes provide the most performance improvement, without sacrificing data
integrity?
A) innodb-doublewrite=off
B) innodb_log_group_home_dir=/data2/
C) innodb_log_file_size=1G
D) innodb_undo_directory=/dev/shm
E) log-bin=/data2/
F) innodb_flush_log_at_trx_commit=0
G) sync_binlog=0
H) innodb_buffer_pool_size=32G
I) disable-log-bin
Answer: B C E H
分析：不牺牲数据完整性的情况下提供了最大的性能改进
🚀 4.日志文件
Choose the best answer.
You want to log only the changes made to the database objects and data on the MySQL system.
Which log will do this by default?
A) slow query log
B) binary log
C) error log
D) general query log
E) audit log
Answser：B
🚀 5.网络
Choose two.
Identify two ways to significantly improve data security.
A) Configure mysqld to run as the system admin account, such as root.
B) Use a private network behind a firewall.
C) Configure mysqld to use only networked disks.
D) Configure MySQL to have only one administrative account.
E) Configure mysqld to use only local disks or attached disks and to have its own account in the
host system.
Answer:B E
分析：网络隔离 最好 ， D 的问题是 administrator 这个权限太大了，应该有多个
administrative account，每个只有部分权限。
🚀 6.备份恢复
Choose two.
Which two statements are true about MySQL Enterprise Backup?
A) It creates logical backups.
B) It supports backing up only table structures.
C) It can perform hot or warm backups.
D) It supports backup of a remote MySQL system.
E) It supports restoring to a remote MySQL system.
F) It supports the creation of incremental backups.
Answer:C F
🚀 7.数据库升级
Choose the best answer.
You have upgraded the MySQL binaries from 5.7.28 to 8.0.18 by using an in-place upgrade.
Examine the message sequence generated during the first start of MySQL 8.0.18
Which step or set of steps will resolve the errors?
A) Start mysqld again using the --upgrade=FORCE option.
B) Go to the /mysql directory and execute: myisamchk --update-state columns_priv
event proc proxies_priv tables_priv.
C) Execute: mysqlcheck --repair mysql columns_priv event proc proxies_priv tables_priv.
D) Remove the redo logs. Replace the MySQL binaries with the 5.7.28 binaries. Prepare the tables
for upgrade. Upgrade to 8.0.18 again.
E) Execute: mysqlcheck --check-upgrade mysql columns_priv event proc proxies_priv
tables_priv.Answer：C
B 这些表都是 MyISAM 的表，但 myisamchk --update-state 并没有指定修复，
要指定–force进行修复。
E mysqlcheck --check-upgrade 也没有指定修复Choose the best answer.
You plan to upgrade your MySQL 5.7 instance to version 8.
You have installed the 8 build of MySQL Shell.
Examine this command executed from the operating system shell prompt:
mysqlsh --uri root@localhost:3306 – util check-for-server-upgrade
Which statement is true?
A) It documents any problems with your 5.7 tables to make them ready to upgrade to 8.
B) It fails because the operation name must be in camelCase.
C) It fixes any problems with your 5.7 tables to make them ready to upgrade to 8.
D) It is mandatory to clear the history of prior results before executing this process a second time
or later.
E) It fails because checkForServerUpgrade must be executed only within an active shell session as
a method of the util object.
F) It is mandatory to run this command so that MySQL 8.0 software ’ s auto-upgrade process has
the details it needs to operate properly. Answer：A