OCP真题精简版--你值得拥有

真题个人总结,精简版！！！部分题目因为重复跳过。

1. mysqldump minimal impact D --single-transaction E --master-data
2. key buffer A It caches index blocks for MyISAM tables only C It is a global buffer
3. 主从 Slave_IO_Runing:YesSlave_SQL_Runing:Notwo reasons B to allow a backup to be created under reduced load C to allow for point-in-time recovery on the slave
4. MySQL InnoDB Cluster (MGR+MySQL Router + MySQL Shell) A The cluster can be operated in multimaster mode with conflict detection for DML statements B All MySQL client programs and connectors can bu used for executing queries D There is supporrt for automatic failover when one node fails
5. Innodb handle deadlocks C One tansactions rolled back, the other allowed to proceed
6. allocate memory per thread C read buffer D internal temporary table E sort buffer
7. which step before defining your databases and table A mysqld --initialize
8. MySQL Server allocates memory A Each connection may have its own per-thread memory allocations D Global memory resources are allocated at server startup
9. auto-increment locking D The auto-increment lock can be a table-level lock E Some settings for innodb_autoinc_lock_mode can help reduce locking
10. backup and operation before it is ready to be restored B Backup State=Raw Backup; Operation=apply-log
11. mysql-config_editor C There is no password defined in login path
12. which step do you perform to create the contents of the data directory C Invoke mysqld with the --initialize option
13. which two backup methods can be used to restore the single table without stopping the MySQL instance? A a backup created with mysqldump --all-databases B a backup created using FLUSH TABLES ... FOR EXPORT
14. mysqldump --opt world > dump.sql import data B shell> mysql test < dump.sql E mysql> USE test; mysql>SOURCE dump.sql
15. mutiple instance ---> 指定配置文件 E --defaults-file=/etc/mysql/instance2.cnf
16. USE sales;DROP PROCEDURE IF EXISTS get_reports C The stored procedure named get_reports will be dropped
17. prevent SQL injection A rewriting your client code to properly escape all user input B giving limited privileges to accouts used by application servers to interact with their backing databases F validating all user input before sending it to database server
18. best method for monitoring Group Replication conflict resolution A the PERFORMANCE_SCHEMA tables
19. best describes the purpose of the InnoDB buffer pool？ C It caches data and indexes for InnoDB tables
20. Which method could be used to restrict access as required? A using GRANT ... REQUIRE X509 AND REQUIRE ISSUE '/C=...' AND REQUIRE SUBJECT '/C=...'
21. stop LOAD DATA LOCAL INFILE '/etc/password' E setting the --local-infile=0 option when starting mysqld
22. three key advantages of standard MySQL replication C provides arbitrary geographic redundancy with minial overhead to master E is easy to configure and has low performance overhead F can easily add slaves for read scaling
23. Slow query Log utility A mysqldumpslow
24. master-slave thread B The slave is configured with slave_parallel_workers=4 C At most, two schemas are being updates concurrently
25. 主从不一致日志 C enforce_gtid_consistency=ON and consistency is broken between the master and the slave E sync_binlog=0 and the master server crashed (只有一个选项的话，选E)
26. FLUSH FOR EXEPORT B Table only exports when the table has its own tablespace C The InnoDB Storage engine must be used for the table being exported
27. master-slave B server_id is not unique
28. InnoDB:a long semaohore wait, two avoid method D Set the value of innodb_adaptive_hash_index to zero E Deactive the query cache
29. update not used index D five
30. big backup load, slowed down A The MySQL server has stopped inserting data to check index consistency
31. skip tansaction B SET GTID_NEXT ="xxxxx:8";BEGIN;COMMIT;SET GTID_NEXT="AUTOMATIC"
32. mysql_config_editor E It is an alternative to storing the MySQL login details in my.cnf file. F It provides means to help avoid accidentally exposing the MySQL login datails
33. expalin --> possible_keys A if it is possible for you to include any indexes in your query
34. MySQL phical and logical backups B logical backups are human-readable whereas physical backups are not E Phical backups are usually faster than text backups
35. It is true than binary backups always take less space than text backups? C No, beacuse if InnoDB tables contain many empty pages, they could take more space than the INSERT statements
36. purpose of changing ownship of datadir to 'mysql' user D MySQL requires correct file ownship while reamaining secure
37. create user with SHA_256 password(配置文件默认此密码插件) B CREATE USER 'webdesign'@'192.0.2.10' IDENTIFIED BY 'iambatman'; C CREATE USER 'webdesign'@'192.0.2.10' IDENTIFIED with sha256_password BY 'imbatman';
38. CREATE USER ... PASSWORD EXPIRE; C When 'erika'@'localhost' tries to log in with MySQL command-line client, the user will be permitted to log in but will not be able to issue an statements until the user changes the password
39. What is the order of tables shown in an EXPLAIN output? B It lists tables in the order in which their data will be read
40. Which three tasks are handled by the optimizer? A Decide which indexes to use B Rewrite the WHERE clause D Change the order in which the tables are joined
41. MyISAM --datadir location runs out of disk space B The server suspends that INSERT operation until space becomes available
42. Which two methods can be used to allow the query to use an index? A Change the where clause to Birthday BETWEEN 1980-01-01 AND 1980-12-31 D Add a generated column calculating YEAR(Birthday) and index that column
43. mysqldump point-in-time recovery configuration B log-bin
44. which two conclusions can be made based on the output of the query? A There are three indexes on the table F There is a redundant index in the emp_no_column ---primary key contains
45. ALTER TABLE orders DROP PARTITION p1,p3; A All data in p1 and p3 partitions is removed and the table definition is changed
46. which statement best describes the meaning of value for the key_len column? A It shows how many bytes will be used from each index row
47. Which two other steps can help avoid data loss in a major catastrophe? A Implement a failover strategy to another geographic location C Have a second data centre in a different region or country
48. X509 D Distribute client digital certificates to the client computers being used to log in by the user accounts
49. mysqldumpslow which two options could explain the slow query? B No index has been defined on the filtered column E A full table scan is being used
50. which additional three steps help in avoiding conflicts in group replication? B Use the binary log row format F Gurantee a primary key on every table G Set multiple slave parallel worker threads(启用并行复制)
51. four configuration file edits cause the negative DB performance? A table_open_cache = 64 C log_bin=mysql-bin;Innodb_flush_log_at_trx_commit=1 F max_heap_table_size=2G; tmp_table_size=2G G query_cache_size = 2G; query_cache_enabled=1
52. add some configuration into my.cnf and restart server[mysqld]defaults-authentication-plugin=sha256_password A They are not affected by this configuration change
53. which two changes would ensure that the temporary table does not propagate(复制) to the slave? C Change the binlog_format option to ROW and restart mysqld before you create the OLD_INVENTORY table E Use the --replicate-ignore-table option with the value equal to OLD_INVENTORY.OLD_INVENTORY and restart mysqld before creating the temporary table
54. Duplicate ERROR A The applications have the SUPER privilege, which allows them to update rows D The slave was created with mysqldump -uroot -p --skip-lock-tables -all-databases > /data/data.sql
55. root password not working C Start the MySQL Server with --init-file pointing to SQL that executes an ALTER USER statement to change the root user password D Start the MySQL Server with --skip-grant-tables and execute SQL, which will update the root password
56. grant statement --> activation of that access C The access is available immediately
57. master-slave delay 1h master crashed which command set would make the slave current? C STOP SLAVE; CHANGE MASTER TO MASTER_DELAY=0;START SLAVE;
58. binlog-format=STATMENTlog-binwhich database updates are logged on the master server to the binary log by default? B all updates except to the PERFORMANCE_SCHEMA database
59. security enforce , what are two remaining security concerns? C The dictionary file word list is too short F The dictionary file is an insecure location
60. SET SESSION max_connections=200;Why does the command fail? A max_connections requires the GLOBAL scope
61. Move InnoDB UNDO tablespace to a SSD B Yes. Shut down,copy the UNDO tablespaces to the new location, and change the innodb_undo_directory value in your my.cnf
62. Thread , which two conclusions can be made from the output? C The thread cache has been configured with thread_cache_size set to at least 6 D There are more connections being idle than executing queries
63. UPDATE without where D Nothing is logged because you are executing an UPDATE statement that will cause changes to more than one row, and you do not have the --binlog-format value set to STATEMENT
64. Which two statements descibe how InnoDB recovery works? A InnoDB handles most crash recoveries automatically B InnoDB blocks some operations when innodb_force_recovery is set to greater than 0
65. Which are three facts about backups with mysqldump? B can back up a remote database server C allow a consistent backup to be taken D are able to back up specific items within a database
66. join_buffer_size B The join buffer is set per connection D The value should be increased from the default if the query joins large rows without using an index
67. high-end OLTP service, slow query, two most likely reasons? (table engine is MyISAM) C The engine type is not appropriate to the application use E No indexes are defined
68. which two methods would allow the mysql client to connect to the server and send clear text passwords? C export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN='Y' E mysql --enable-cleartext-plugin -uroot -p -h dbhost.example.com
69. which three options are most likely to be changed for production form their default values? A innodb_buffer_pool_size E innodb_log_file_size G port (if not port, choose max_connections)
70. USER which set of statements would match the accounts shown? D mysql> CREATE USER ''@'' IDENTIFIED WITH authentication_pam ACCOUNT LOCK; mysql> CREATE USER 'employee'@'localhost' IDENTIFIED BY 'more_secret'; mysql> GRANT PROXY ON 'employee'@'localhost' TO ''@'';
71. start server, error: Address already in use A The mysql service has already started on the same port
72. my.cnf order questions D MySQL starts but Binary logging is disabled
73. configure a MySQL Server to act as replication master, two option must be configured? C server-id E log-bin
74. ibdata1 meanings D it contains the system tablespace F it contains the undo log
75. Which two methods accurately monitor the size of your total database size over time? C monitoring the information_schea.tables table D monitoring datadir size in the operating system
76. ERROR 2059 : Authentication plugin 'mysql_clear_password' cannot be loaded:plugin not enabled D the mysql client with the --enable-cleartext-plugin option
77. big table delete A change the replication mode to mixed before issuing any delete statements when the limit clause is used D Use the limit clause in conjunction with the order by clause
78. nologin or bin/false D This prevents creation of a command shell with the mysql account, while allowing mysqld to run
79. mysqldump ,big table,limited memory ,which option will ensure mysqldump will process a row instead of a set of rows? C --quick
80. Which statement is true about using Microsoft Windows Cluster as a platform for MySQL? A It relies on the shared disk architecture being visible to both servers
81. CHECK TABLE command, which situations be used? B to make sure a table has no structura problems E to make sure that no table indexes are corrupted
82. replication binlog consume disk, which two steps should you perform to safely remove some of the older binary log files? C Ensure that none of the attanched slaves are using any of binary logs you want to delete. E Use the command PURGE BINARYLOGS and specify a binary log file name or a date and time to remove unused files
83. What are three methods to reduce MySQL server exposure to remote connections? A Setting --skip-networking when remote connections are not required C Setting specific GRANT privilege to limit remote authentication E using SSL when transporting data over remote networks
84. Which two are considered good security practices when using passwords? A Use one-way encryption for storage of passwords E Do not use dictionary-based words
85. SQL_Delay B the slave was configured for delayed replication with a delay of six minutes
86. which MySQL utility copies the master instance to a slave instance in the same host? A mysqlservercloene
87. SQL injection, which two methods would help protect against this risk? A using prepared statements to handle unsecured values D using stored procedures to validate values that are input
88. 题目看不清 D 'mike'@'%'
89. which two methods will provide the total number of partitions on a table? C Use the command: SHOW CREATE TABLE E Query the INFORMATION_SCHEMA.PARTITIONS table
90. mysqldump only data A --no-create-info to skip writing CREATE TABLE statements
91. ERROR 1218：error connecting to master...not allowed C add the user replicatation@192.168.0.25 with the correct password to the master
92. ERROR:1236 B sync_binlog=0 and the master server crashed
93. EXPLAIN range meanings A you can use an index and return rows that fall with a range of values
94. --join-buffer-size C the size of the largest table in any JOIN query
95. USE prices;Update sales.january set amount=amount+1000; E nothing is ignored because you are executing an UPDATE statement
96. Why should you be selective when granting the PROCESS privilege to an account? C It allows a clint to see another user's queries with the SHOW PROCESSLIST command
97. In which two situations would you use unencrypted data transfers from the MySQL service? C when highest possible speed of transfer is required regardless of security D when the service is using socket only connections and the host is security
98. Which two statements are true regarding MySQL security? D The mysqld process owner should own all files and directories to which the server writes E The mysqld process should not be run as root or administrator
99. restore the table 'sales' from mysqldump backup? A shell> mysql -u root -p sales < /backup/orders.sql shell> mysqlimport -u root -p --local sales /backup/orders.txt D mysql> use sales; mysql> SOURCE /backup/orders.sql mysql> LOAD DATA LOCAL INFILE '/backup/orders.txt' INTO TABLE orders;
100. connections E key is already connected elsewhere and attempting to log in again.
101. What are two benefits of using the --tab option with mysqldump? A the schema and data are automatically dumped to separate files C it is possible to restore the data in parallel
102. max_allowed_packet=16M B [mysql] and [mysqld] and [mysqladmin]
103. which statement is correct about how InnoDB storage engine uses disk space? B It stores data, indexes and undo information in tablespace file(s)
104. Which extra GRANT permissions are required for adam to take mysqldump backups? A the 'adam' user must also have SELECT on the football databases for backups to work
105. --single-tansaction, which mysqldump option can be used when dumping data from the master service in order to include the information? B master-data
106. Which statement describes how the relay log works? D when a slave receives a change from the master, it is recorded in the relay log first and processed later
107. store function year_to_date B the function does not execute because the account 'joe'@'localhost' does not have the EXECUTE privileges on the year_to_date function
108. ERROR 1045：access denied for user 'application'@'localhost', which two changes to the process can fix the issue? B use the --flush-privileges with mysqldump D after the restore, log in to the database and execute FLUSH PRIVILEGES
109. stall 阻塞, which 2 things should you investigate? A check the rate of changes in the status value Qcache_hits and compare that to the rate of change of Qcache_not_cached E check the rate of change in the status value select_scan and compare to the rate of change in com_select
110. datadir size D The departments and dept_manager tables are empty
111. Warning: Using a password on the command-line interface can be insecure B Use mysql_config_editor, which allows you store encrypted login credentials in your home directory. C Store your password in an option file eg:~/.my.cnf and use -defaults-file so that it is read and used [client] password = t100043va
112. what are three facts about backups with mysqldump? A can back up a remote database server B allow a consistent backup to be taken E are able to back up specific items within a database
113. Global status variables B The thread cache has been configured with thread_cache_size set at least 6 C There are more connections being idle than executing queries
114. GTID, create table datatarget select * from datasource; E The statement cannot be logged in a transactionally safe manner
115. frequent sorted client errors B A client was killed during a sort operation C The sort was too large for sort_buffer_size, causing an error
116. The InnoDB tablespace is corrupted A A text backup. A binary backup will still contain the corruoted segments
117. which method will reduce the number of file handles in use? C activating the mysql enterprise thread pool plugin
118. analyze table command A when you want to update index distribution statistics D after large amounts of table data have changed
119. reset password B --skip-networking, to prohibit access from remote locations
120. dump.sql A mysql> USE test; mysql>SOURCE dump.sql D mysql> USE test; mysql>LOAD DATA INFILE 'dump.sql';
121. free tablespace ;becuase a table consuming amounts disk D Take a backup, stop the server, remove the data files, and restore the backup
122. What is it import to remove snapshots after completing a raw backup in this way? A The snapshots take a significant amount of disk space because they are a duplicate copy of the data
123. SQL injection A Resend form data submitted using the POST method B Include single and double quotes in the submitted data D Modify the URL using percent encoding
124. which two statements are true about MySQL when it has been started using the --skip-grant-tables option? A all users have unrestricted acess C All connections succeed regardless of the username and password
125. validation B whether a new password is rejected if it contains a word found in a dictionary file
126. GRANT PROXY ON 'a' FOR 'b'; B User account 'a' has not been granted permission to acess the data being queried
127. which two wl optmze the secuty of this step A enabling and using SSL for connections to the MySQL database B disabling connections from named pipes or socket files(depending on the operating system of the server)
128. which two methods provide a consistent backup of InnoDB tables? C file system snapshots D MySQL Enterprise Backup
129. which two actions can you take to stop any acess from user? A Use DROP USER 'mike'@'client.example.com'; E Use ALTER USER 'mike'@'client.example.com' ACCOUNT LOCK;
130. For tables using persistent statistics, what is the outcome of this change? B InnoDB no longer automatically updates index statstics after table structure is altered.
131. where cluase add index B ALTER TABLE 'countryLanquago' ADD INDEX 'idx_Lang';
132. which three changes will allow the use of an index to perform this kind of filtering(%def%)? A Normalize the data, for example, into a linked table and filter on that table instead E Add an R-TREE index on the val column and use MBR contains function for the filtering F Convert data type to binary and add a normal index. This avoids rewriting the query.
133. At which stage during query processingg does MySQL create a queryos execution plan? D Optimizing
134. In which order does MySQL process an incoming INSERT statement? D it checks whether the user is authorized to perform the query, optimizes it, and then wites to the binary log.
135. RPM install , where errors written? B in the /var/log/mysqld.err file location
136. tmp_table_size=16Msort_buffer_size=256K --- > 修改增大数值，重启，问题复现which three best describe this scenario? A Global variables are not persistent across server restarts C The query benefited from sort_buffer_size and tmp_table_size increase D Session variables are not persistent across server restarts
137. EXPLAIN ref columns meanings C No indexed columns are used to select rows from City table. The world.City CountryCodo column is used to select rows in the Country table
138. mysqldump contain master_information option D master-data
139. which statement is true about tablespaces? D General tablespace can be configured to span multiple files
140. which two changes to the process can fix the issue? A after the restore, log in to the database and execute FLUSH PRIVILEGES B Use the --flush-privileges with musqldump
141. Why does the message "Waiting for an event from Coordinator" show up on the slave_worker threads? A The slave thread is waitings for another event to finish in another slave thread
142. /etc/my.cnf C MySQL igones world-readable configuration files as a security measure
143. DML taransactions per second D Sort Buffer size F Buffer Pool size
144. storage workload A iSCSI Lun
145. what four steps must be included in the backup strategy? C Copy the backup to a remote host E Include the keyring data and/or configuration in the backup H Include the operating system disk encrption key in the backup I Restore the backup to a clean MySQL instance
146. datadir permission A The owner of /var/lib/mysql is root. Change the ownership to mysql and retry the command
147. which two capabiliese are granted with the SUPER privilege? A allowing a client to shut down the server D allowing a client to kill other client connections
148. [mysqld] port.3306 port-3307#3308 F MySQL starts and listens for connections only on port 3307
149. slowdowns D increase the size of redo logs
150. 多源复制现象show processlist A The replication slave uses multisource replication with eight sources
151. GRANT ---根据账户权限判断，有GRANT OPTION权限的才能GRANT which statement will succeed? A GRANT SELECT ON world.* to xxx; B GRANT ... C ...
152. what are three measures to maintain a stable MySQL system? B Ensure redundancy of all parts of the system C Monitor with data collection every second E Establish a performance baseline
153. mysqldump extra privilege A select
154. which statement best discribes a 'warm' backup D It backs up the binary log, which contains the most recent "warm" change
155. Data import C mysql >load data local infile D shell >mysql -uroot -p < data.sql
156. what are three typical cause of mysql becoming suddenly slow or unavailable? B a configuration change was made D The hardware includes a single point of failure E The application executes a new untested query
157. my.cnf server 端配置为[mysqld]下 B 3306
158. error log very large, operation A mv /var/log/host_name.err /var/log/host_name.err_old; mysqladmin flush-logs
159. Where Does MySQL linux RPM install the mysqld binary? D /use/sbin/
160. master-slave skip tansaction A STOP SLAVE; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SALVE;;
161. mysqldumpslow, slow query log, N meanings? D the number of times the statement was executed
162. mysqldump only data table C --NO-create-info skip writing CREATE TABLE statements
163. which statement is true about the output? A The query read data from the data file rather than directly from the buffer pool