1萄窜、A simplemaster-to-slave replication is currently being used. The following informationis extracted from the SHOW SLAVE STATUS output:?
Last_SQL_Error: Error 'Duplicate entry '8' for key 'PRIMARY'' on query. Defaultdatabase: 'mydb'.
Query: 'insert into mytable VALUES ('8', 'George')'
Skip_Counter: 0
Retrieved_Gtid_Set: 38f32e23480a7-32a1-c323f78067fd37821: 1-8
Auto_Position: 1
You execute a "SHOW CREATE TABLE mytable" on the slave:
CREATE TABLE 'mytable' (
'ID' int(11) NOT NULL DEFAULT '0',
'name' char(10) DEFAULT NULL,
PRIMARY KEY ('ID')
)
The table mytable on the slave contains the following:
You have issued a STOP SLAVE command. One or more statements are requiredbefore you can
issue a START SLAVE command to resolve the duplicate key error.
Which statement should be used?
A)
SET GLOBAL SQL_SKIP_SLAVE_COUNTER=1
B)
SET GTID_NEXT="CONSISTENCY";
BEGIN; COMMIT;
SET GTID_NEXT=" AUTOMATIC
’;
C)
SET GLOBAL enforce_gtid_consistency=ON
D)
SET GTID_EXECUTED="38f32e23480a7-32a1-c323f78067fd37821 : 9";
E)
SET GTID_NEXT="38f32e23480a7-32a1-c323f78067fd37821 : 9";?
BEGIN; COMMIT;
SET GTID_NEXT="AUTOMATIC";
--------------------------------------------------------------------
答案：E
分析：此題中使用的Replication是通過GTID實現(xiàn)的恨樟，因此
A
錯拓挥，因此GLOBAL SQL_SKIP_SLAVE_COUNTER=1對使用GTID進行的Replication無效
C
錯，因為GLOBAL enforce_gtid_consistency=ON是實現(xiàn)的前提。由于GTID_NEXT的有效值為：
AUTOMATIC / ANONYMOUS / :
因此 B錯由于Retrieved_Gtid_Set:38f32e23480a7-32a1-c323f78067fd37821: 1-8
因此已經(jīng)收到主庫事務(wù)1-8瓢娜，因此報錯是從第9個事務(wù)重復(fù)記錄導(dǎo)致的醇滥，很有可能slave上的第8行被人為錄入了黎比，導(dǎo)致同步問題。
D
錯鸳玩，因為GTID_EXECUTED表示已經(jīng)執(zhí)行完成的事務(wù)阅虫。
為了臨時繞過這個問題，使用注入空事務(wù)(BEGIN; COMMIT; ) 代替完成第9個事務(wù).
完成后GTID_EXECUTED才會變?yōu)?38f32e23480a7-32a1-c323f78067fd37821: 9"
這時候重新SET GTID_NEXT="AUTOMATIC"; 重啟slave后怀喉，開始從第10個事務(wù)開始同步书妻。
2.Consider the following
statement on a RANGE partitioned table:?
ALTER TABLE orders DROP PARTITION p1, p3;
What is the outcome of executing the above statement?
A.Only the first partition (p1) will be dropped as only one can be dropped atany time.
B.All data in p1 and p3 partitions are removed, but the table definitionremains unchanged.
C.A syntax error will result as you cannot specify more than one partition inthe same statement.
D.All data in pi and p3 partitions are removed and the table definition ischanged.
--------------------------------------------------------------------
答案：D
在刪除部分分區(qū)后，可以使用show create table查看其定義也一并改變了
3.You inherit alegacy database system when the previous DBA, Bob, leaves the company. You are
notified that users are getting the following error:
mysql> CALL film_in_stock (40, 2, @count);
ERROR 1449 (HY000): The user specified as a definer ('bob'@'localhost') doesnot exist
How would you identify all stored procedures that pose the same problem?
A.Execute SELECT * FROM mysql.routines WHERE DEFINER='bob@localhost';.
B.Execute SHOW ROUTINES WHERE DEFINER='bob@localhost'.
C.Execute SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHEREDEFINER='bob@localhost';.
D.Execute SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER='bob' andHOST='localhost';.
E.Examine the Mysql error log for other ERROR 1449 messages.
----------------------------------------------------------------------------------
答案：C?
分析：routines表在庫INFORMATION_SCHEMA下躬拢，因此A錯躲履。可以登陸MySQL后聊闯，使用? show命令查看show語法工猜。可知show無routine語句菱蔬，B錯篷帅。
可使用以下命令來查看routines:
pager less;
select * from information_schema.routines\G
可知C正確
INFORMATION_SCHEMA.PROCESSLIST
表中僅顯示了當(dāng)前正在運行的線程信息,D錯。
Mysql error log
是對報錯信息的記錄拴泌，并不會有所有存儲過程的記錄魏身，E錯。
4.When designing anInnoDB table, identify an advantage of using the BIT datatype Instead of one
of the integer datatypes.
A.BIT columns are written by InnoDB at the head of the row, meaning they arealways the first to
be retrieved.
B.Multiple BIT columns pack tightly into a row, using less space.
C.BIT(8) takes less space than eight TINYINT fields.
D.The BIT columns can be manipulated with the bitwise operators &, |, ~, ^,<<, and >>. The other
integer types cannot.
------------------------------------------------
答案：C
分析：關(guān)于數(shù)據(jù)類型的存儲蚪腐，可查看http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html
A, B
都沒有特別在guide中提到箭昵。
BIT(8)
大致長度為1個Byte, 8個tinyint的存儲長度相當(dāng)于一個bigint了, 請注意并不是說tinyint(8)，括號中為可顯示的長度回季，由于一個tinyint為1個Byte家制，因此8個自然要更長。因此C正確泡一。
D
錯颤殴，int類型值夜可以進行bit操作符的操作
5.ROW-based replication has stopped working.
You investigate the error log file and find the following entries:?
2013-08-27 14:15:47 9056 [ERROR] Slave SQL: Could not execute Delete_rows eventon table test.t1; Can
’t find record in ‘t1’, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log 56_master-bin.000003, end_log_pos 851, Error_code: 1032
2013-08-27 14:15:47 9056 [warning] Slave: Can
’t find
record in ‘t1’Error_code: 1032
2013-08-27 14:15:47 9056 [ERROR] Error running query, slave SQL thread aborted.Fix the problem, and restart the slave SQL thread with
“SLAVE START”. We stopped at log ‘56_masterbin.000003’position 684
Why did you receive this error?
A.The slave SQL thread does not have DELETE privileges to execute on test.t1table.s
B.The table definition on the slave litters from the master.
C.Multi-threaded replication slaves can have temporary errors occurring forcross database?
updates.
D.The slave SQL thread attempted to remove a row from the test.t1 table, butthe row did not
exist.
-------------------------------------------------------------------------------------------
答案：D
分析：報錯中說的非常明確Could not execute Delete_rows event on
table test.t1; Can’t find record in ‘t1’，這說明slave上這條記錄已經(jīng)被人為刪除了鼻忠，導(dǎo)致Row-Based
Replication進行同步刪除的時候涵但，找不到這條記錄。ABC選項都和此報錯以及所問問題無關(guān)。
6.mysqldump was used to create a single schemabackup;
Shell> mysqldump
–u root –p sakila> sakila2013.sql
Which two commands will restore the sakila database without interfering withother running
database?
A.Mysql> USE sakila; LOAD DATA INFILE 'sakila2013.sql';
B.Shell> mysql
–u root –psakila < sakila2013.sql
C.Shell> mysqlimport
–u root –p sakila sakila2013.sql
D.Shell> mysql
–u root -p –e'use sakila; source sakila2013.sql'
E.Shell> mysql
–u root –p –silent < sakila2013.sql
--------------------------------------------------------------------------------
答案：BD
分析：
A
錯贤笆，load data infile針對的是select
... into oufile輸出的表數(shù)據(jù)文件蝇棉，其文件中不含有插入執(zhí)行語句，僅含有數(shù)據(jù)芥永。而mysqldump導(dǎo)出的文件包含的數(shù)據(jù)是以可執(zhí)行sql語句實現(xiàn)的篡殷。
C
錯，因此mysqlimport是類似于load data infile語句功能的shell命令行工具埋涧，因此對應(yīng)倒入的文件都應(yīng)該是非sql語句執(zhí)行的純表數(shù)據(jù)文件板辽。我們看到mysqldump在未使用--database項導(dǎo)出時，并未在文件中使用create database語句棘催。
當(dāng)導(dǎo)入數(shù)據(jù)庫dump文件劲弦，你需要在命令中指定數(shù)據(jù)庫名，即use
db_name進入此庫：
shell> mysql db_name < dump.sql
因此B正確
mysql -e
可用于執(zhí)行語句醇坝，但是mysql客戶端語句需要使用分號作為終止符發(fā)給服務(wù)端邑跪，因此每個語句后都需要使用分號，D錯誤呼猪。
如果D為Shell> mysql –u root -p –e 'use sakila; source
sakila2013.sql;' 則正確画畅。
E
錯. mysql命令項使用中，短項使用單橫杠宋距，長命令項使用雙橫杠 -silent項應(yīng)該時候雙橫杠轴踱，因此錯。
7.Consider the Mysql Enterprise Audit plugin.
You are checking user accounts and attempt the following query:
Mysql> SELECT user, host, plugin FROM mysql.users;
ERROR 1146 (42S02): Table
‘mysql.users’ doesn’t exist
Which subset of event attributes would indicate this error in the audit.logfile?
A.NAME=
”Query”?
STATUS=
”1146”?
SQLTEXT=
”select user,host from users”/>
B.NAME=
”Error”?
STATUS=
”1146”?
SQLTEXT=
”Error 1146 (42S02): Table ‘mysql.users’ doesn’t
exist”/>
C.NAME=
”Query”?
STATUS=
”1146”?
SQLTEXT=
” Error 1146 (42S02): Table ‘mysql.users’ doesn’t
exist”/>
D.NAME=
”Error”?
STATUS=
”1146”?
SQLTEXT=
”select user,host from users”/>
E.NAME=
”Error”?
STATUS=
”0”?
SQLTEXT=
”Error 1146 (42S02): Table ‘mysql.users’ doesn’t
exist”/>
---------------------------------------------------------
答案：A
分析：注意：MySQL Enterprise Audit是包含在MySQL企業(yè)版中的一個擴展插件谚赎，因此如果你在學(xué)習(xí)時使用的是社區(qū)版的MySQL,那你是無法實驗的淫僻。因為它需要在環(huán)境變量plugin_dir對應(yīng)目錄下存在audit_log.so插件文件。從選擇答案中可知壶唤，Audit log中使用的是舊格式進行的記錄雳灵。由于SQLTEXT僅在NAME為Query或Execute時，才會有出現(xiàn)闸盔，且NAME不存在Error狀態(tài)细办。因此B,D,E錯。而SQLTEXT僅存放所使用的SQL語句蕾殴。而返回的狀態(tài)存放在STATUS下，0為成功岛啸，非0為報錯號钓觉，因此A對C錯。
8.Which query would you use to find connectionsthat are in the same state for longer than 180 seconds?
A.SHOW FULL PROCESSLIST WHERE Time > 180;
B.SELECT * FROM INFORMATION_SCHEMA.EVENTS SHERE STARTS < (DATE_SUB(NOW(),INTERVAL 180 SECOND));
C.SELECT * FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE STATE <(DATE_SUB(NOW(), INTERVAL 180 SECOND));
D.SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE TIME > 180;
-------------------------------------------------
答案：D
分析：你可以使用 help show;命令來查看其語法可知：
SHOW [FULL] PROCESSLIST
此語法后面不可以跟where語句坚踩，因此A錯荡灾。
INFORMATION_SCHEMA.EVENTS
表顯示的是計劃的作業(yè)，和連接保持的狀態(tài)時間無關(guān)，B錯批幌。
INFORMATION_SCHEMA.SESSION_STATUS
表顯示的是當(dāng)前會話的變量及其變量值础锐，和狀態(tài)信息無關(guān)，C錯荧缘。
INFORMATION_SCHEMA.PROCESSLIST
顯示了當(dāng)前的連接情況皆警，狀態(tài)，以及狀態(tài)保持的時間截粗，實際上show processlist也是查看的這張表信姓，不過直接使用select可以使用where語句，D正確绸罗。
9．A database exists as aread-intensive server that is operating with query_cache_type =
DEMAND.
The database is refreshed periodically, but the resultset size of the queriesdoes not fluctuate.
—-Note the following details about this environment:?
A web application uses a limited set of queries.
The Query Cache hit rate is high.
All resultsets fit into the Query Cache.
All queries are configured to use the Query Cache successfully.
The response times for queries have recently started to increase. The cause forthis has correctly
been identified as the increase in the number of concurrent users accessing theweb service.
Based solely on the information provided, what is the most likely cause forthis slowdown at the
database level?
A.The Query Cache is pruning queries due to an increased number of requests.
B.Query_cache_min_res_unit has been exceeded, leading to an increasedperformance
overhead due to additional memory block lookups.
C.Mutex contention on the Query Cache is forcing the queries to take longer dueto its singlethreaded nature.
D.The average resultset of a query is increasing due to an increase in thenumber of users
requiring SQL statement execution.
---------------------------------------------------------------------------------------------------------
答案：C
分析：這是一個讀密集型數(shù)據(jù)庫意推，數(shù)據(jù)庫會在一段時間后刷新，但是其查詢的結(jié)果集大小波動不大珊蟀。而所有結(jié)果集都在Query
Cache中菊值，且網(wǎng)頁應(yīng)用使用一套有限的查詢語句。且Query Cache hit rate很高育灸。因此A腻窒，D錯，請求通過的應(yīng)用查詢描扯，查詢語句數(shù)量有限定页，結(jié)果集都能放在Query Cache中，相同查詢語句的請求不會增多Query Cache中的資源的占用绽诚，因此清理查詢并非主要矛盾典徊。
B
也錯，因此Query_cache_min_res_unit設(shè)置過大恩够，僅會造成Query Cache中碎片過多卒落。如果請求的結(jié)果集都能在Query Cache中，這就和碎片沒什么關(guān)系了蜂桶。
C
正確儡毕，盡管官方文檔中未大量解釋Query Cache Mutex爭用問題，在線程運行查詢語句時扑媚，會在Query Cache中先獲取Mutex鎖腰湾，之后開始查詢匹配的查詢語句和結(jié)果集。如果找到后返回結(jié)果疆股。如果未找到匹配费坊，在執(zhí)行查詢后，需要將查詢語句和結(jié)果集插入Query Cache中旬痹，這也會需要獲取鎖附井。盡管這個時間所需非常短讨越，但是在讀密集的情況下，資源爭用會導(dǎo)致線程排隊等待現(xiàn)象永毅。
10.You have a login-path named"adamlocal" that was created by using the mysql_config_editor
command.
You need to check what is defined for this login_path to ensure that it iscorrect for you
deployment.
You execute this command:
$ mysql_config_editor print
–login-path=adamlocal
What is the expected output of this command?
A.The command prints all parameters for the login-path. The password is printedin plain text.
B.The command prints all parameters for the login-path. The password is shownonly when you?
provide the --password option.
C.The command prints all parameter for the login-path. The password is replacedwith stars.
D.The command prints the encrypted entry for the login-path. The is onlypossible to see if an
entry exists.
---------------------------------------------------
答案：C
分析：
mysql_config_editor
工具命令用于建立外部登陸文件把跨，一般由mysql客戶端或應(yīng)用來使用，好處在于登陸時免去輸入登陸密碼,密碼已經(jīng)被保存在了登陸文件中沼死。在環(huán)境變量MYSQL_TEST_LOGIN_FILE未設(shè)置的情況下着逐，mysql_config_editor默認(rèn)文件名為.mylogin.cnf，且文件保存在執(zhí)行此命令的用戶home目錄下漫雕。登陸文件建立后滨嘱，可直接使用以下命令登陸：
shell> mysql --login-path=login-path
當(dāng)然，--login-path的默認(rèn)值為client浸间，因此如果你使用mysql_config_editer set --login-path=client 來進行用戶密碼設(shè)置設(shè)置太雨，那么登陸所設(shè)用戶的時候，連--login-path也可不用了：
shell> mysql
A, D
錯魁蒜，因為不管如何囊扳，你都看不到密碼的，密碼被加密保存后兜看，使用mysql_config_editor
print會將密碼替代以星號顯示锥咸。
B
錯，使用mysql_config_editor --help可知在參數(shù)項中沒有此--password項细移，且通過參考文檔可知--password是用于設(shè)置密碼而非顯示密碼的搏予。
C
正確。
