Q101.which two queries are examples of successful SQL injection attacks?

A.SELECT id, name FROM backup_before WHERE name=‘; DROP TABLE injection; --’;
B. SELECT id, name FROM user WHERE id=23 oR id=32 OR 1=1;
C. SELECT id, name FROM user WHERE user.id= (SELECT members.id FROM members);
D.SELECT email, passwd FROM members
WHERE email= ‘INSERT INTO members (‘email’, ‘passwd’) VALUES
(‘bob@example.com’, ‘secret’);–’;
E. SELECT user,passwd FROM members WHERE user=‘?’;INSERT INTO members (‘user’,"passwd’) VALUES ("bob@example.com’, ‘secret’);–';
F. SELECT id, name FROM user WHERE id=23 oR id=32 AND 1=1;
Answer:BE

SQL注入我不懂
參考：https://blog.csdn.net/DBA_ChenJR/article/details/147816607

Q102.You must run multiple instances of MySQL Server on a single host.Which three methods are supported?
A)Use system tools to lock each instance to its own CPU.
B)Run MySQL Server docker containers.
C)Use systemd with different settings for each instance.
D)Start mysqld or mysqld safe using different option files for each instance.
E)Run mysqld with --datadir defined for each instance.
F)Use resource groups to lock different instances on separate CPUs.
Answer:CDE

Q103.Which statement enables all roles granted to all users automatically?
A) SET ROLE ALL;
B) SET DEFAULT ROLE ALL TO ‘*’@‘1’%';
C) SET PERSIST activate_all_roles_on_login=ON;
D) SET PERSIET mandatory_roles=ALL;
Answer:C

將 activate_all_roles_on_login 系統變量默認值為OFF，若設置為ON，服務器在登錄時將激活授予每個賬戶的角色。

Q104.Which two situations will cause the binary log to rotate?
A)FLUSH HOSTS executed
B)max_binlog_size exceeded
C)max_binlog_cache_size exceeded
D)SET sql_log_bin=1 executed
E)SET sync_binlog=1 executed
F)FLUSH LOGS executed
Answer:BF

選項A：FLUSH HOST語句用于清除主機緩存表，以便重新加載主機名和IP地址的映射。
選項B：max_binlog_size表示binlog的最大文件大小，日志寫入超過這個參數的設置的大小后，會關閉這個文件，然后重新創建一個新的binlog寫入，進行輪轉
選項C：max_binlog_cache_size 是一個 MySQL 系統變量，用于限制二進制日志（binlog）緩存的大小。，這個超出了會報錯
選項D：sql_log_bin參數控制在當前會話中是否寫binlog
選項E：sync_binlog控制binlog刷寫到磁盤的方式，比如sync_binlog=0，不將日志寫入磁盤，根據操作系統走，sync_binlog=1，每次提交都刷寫到磁盤，sync_binlog=N，N次提交刷寫到磁盤
F：FLUSH LOGS會新建一個日志，進行輪轉

Q105.Examine this statement, which executes successfully:
CREATE TABLE world.city (
ID int NOT NULL AUTO_INCREMENT,
Name char (35) NOT NULL DEFAULT ‘’,
CountryCode char (3) NOT NULL DEFAULT ‘’,
District char (20) NOT NULL DEFAULT ‘’,
Population int NOT NULL DEFAULT ‘0’,
PRIMARY KEY (ID),
KEY (CountryCode (CountryCode)) ENGINE InnoDB;

SELECT Name
FROM world. city
WHERE Population BETWEEN 1000000 AND 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 SPATIAL INDEX (Population);
E)ALTER TABLE world.city ADD INDEX (Population);
F)ALTER TABLE world.city ADD FULLTEXT INDEX (Population);
Answer:E

選項ABC，跟本次查詢沒有關系，并且population為int類型，所以就普通的二級索引就可以，沒必要使用空間索引和全文索引

Q106.Examine this configuration:
You have a corporate private network, which uses its own Certificate Authority (CA) using an industry standard
2048-bit RSA key length.
? All MySQL Server and client certificates are signed using the central corporate CA.
? All clients are known, controlled,and exist only on the private LAN.
? The private network uses its own private authoritative DNS.
? The private network also uses other nominal enterprise services.
? An end-to-end encrypted connection for a MySQL client to MySQL server has been established on this LAN.
How does the MySQL Servers’ self signed certificate compare to one that would be signed by a known public,third party trusted Certificate Authority?
A)The self-signed certificate is equally secure and equally trusted.
B)The self-signed certificate is more secure and less trusted.
C)The self-signed certificate is less secure and equally trusted.
D)The self-signed certificate is equally secure and less trusted.
E)The self -signed certificate is more secure and equally trusted.
F)The self-signed certificate is less secure and less trusted
Answer:F

自簽證書的安全性和信任度較低

Q107.Which two are valid uses for binary logs on a MySQL instance?
A)logging the duration and locks for all queries //記錄日志期間不產生鎖
B)replication
C)audit of all queries
D)point-in-time recovery
E)recording the order in which queries are issued
Answer:BD

選項A：不記錄SELECT和鎖信息
選項B:復制期間是需要binlog的
選項C：查詢的審計記錄在審計日志，跟binlog沒關系
選項D：基于時間點的恢復，是需要binlog支撐的
選項E：參考選項A

Q108.Which two are characteristics of snapshot based backups?
A)There is no need for InnoDB tables to perform its own recovery when restoring from the snapshot
backup.
B)Snapshot backups can be used only in virtual machines.
C)Snapshot based backups greatly reduce time during which the database and applications are unavailable.
D)The frozen file system can be cloned to another virtual machine immediately into active service.
E)A separate physical copy must be made before releasing the snapshot backup
Answer:AC

選項A：快照恢復完成后，啟動時會自己進行Crash Recovery
選項C：快照備份會減少數據庫和應用的不可用時間

Q109.Examine this output:

You plan to add this parameter to the configuration:
innodb_ directories=’ /innodb_ extras ‘
Which statement is true?
A)It defines all innodb tablespace options relative to a starting parent directory.
B)It adds more temporary workspace in addition to the innodb_ tmpdir location.
c)It moves all innodb tablespaces to the 1 innodb_ extras directory to enable a new
innodb_ data_ home_ dir to be defined.
D)It is not necessary because innodb data_ home_ dir is already defined.
E)It allows scanning of other locations to discover more innodb tablespaces .
Answer:E

innoDB_directories變量，它定義了在啟動時掃描以查找表空間文件的目錄。innodb_directories默認設置為NULL，但當innodb構建啟動時要掃描的目錄列表時，innodb_data_home_dir、innodb_undo_directory和datadir定義的目錄總是附加到innodb_directories參數值之后。無論是否顯式指定了innodb_directories設置，都會附加這些目錄。
使用絕對路徑定義的表空間文件或位于附加到innodb_directories設置的目錄之外的表空間應添加到innodd_directories設置中。參考：
https://dev.mysql.com/doc/refman/8.0/en/innodb-moving-data-files-offline.html
https://blog.csdn.net/qq_29519041/article/details/139078303

Q110.Examine this command, which executes successfully:
mysqldump --user=root --password > full_ backup.sql
Which two databases will be excluded from this dump?
A)world
B)employee
C)information schema
D)mysql
E)sys
Answer:CE

參考：https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html