📢📢📢📣📣📣
哈喽！大家好，我是【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