CMDBA战报之Part1

P.S.:嘿嘿,Part1来了，大家感到有些纳闷，怎么1没有2先出来了，其实是这样的，因为part2后考的回忆起来相对快些，就先推出的part2，part1紧接着也在准备，想呀想，回忆回忆。好了，大家瞧瞧吧！

Chapter 23

考察点：了解MySQL的整体结构。

答案中的关键词/句：1. native C client library、 MySQL Connector/OBDC, MySQL Connector/J, and MySQL Connector/NET；

2. Communication Protocols: TCP/IP、Unix socket file、Named pipe、Shared memory；

3. Only the MyISAM engine supports full-text or spatial indexes。

需要了解的：1. windows下的named-pipe连接需要启动参数—enable-named-pipe，同时需要以包mysql-nt\mysql-max-nt安装的MySQL，shared-memory连接需要启动参数—shared-memory；

2. Unix socket file连接性能好于TCP/IP连接，native C client library速度比Connector/OBDC快，Connector/J速度和native C client library大致一样；

3. MySQL使用的各种缓存。

Chapter 24

考察点：MySQL在windows和unix下个各种Distributions、安装的相关目录、启动/停止各种方法间的差别。

答案中的关键词/句：1. Windows: An Essentials distribution、A Complete distribution、A No-install distribution；

2. Unix: RPM files、TAR files、Source files；

3. mysqld –console will display diagnostic output in the console window on Windows；

4. The server reads options in the standard option files from the [my_service] group in addition to options from the [mysqld] group；

5. On Unix, the data directory is created at /var/lib/mysql using RPM files setup；

6. mysqld_safe is a shell script that invokes mysqld. The script sets up the error log, and then launches mysqld and monitors it；

7. mysql.server is a shell script that invokes mysqld_safe；

8. To stop the server manually, mysqladmin shutdown(local|remote)、mysqld_multi stop(local|remote)、invoke mysql.server script to shut down local server with argument of stop；

9. To view the contents of a binary log file, use the mysqlbinlog utility；

10. To suppress the extra information, start the server with the –log-short-format option；

11. To log queries that are not processed with the benefit of indexes, use the –log-queries-not-using-indexes option；

12. The default PID filename is host_name.pid in the data directory。

需要了解的：1. 将mysql作为windows下的一个服务，操作如下：mysql –install my_service –defaults-file=path ；

2. mysql_install_db脚本创建data目录和初始化mysql和test数据库；
3. mysqld_multi只能开启本地数据库；

4. –dasedir安装目录、–datadir数据目录、–defaults-file配置文件的路径；

5.将系统时区加载到MySQL数据库中，mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql；

6.—skip-networking选项禁止TCP/IP连接；

7. 默认情况下sql-mode为空，没有任何严格性限制。

Chapter 25

需要了解的：注意这些客户端程序都有哪些限制。

Chapter 26

需要了解的：MySQL Administrator工具的使用。

Chapter 27

需要了解的：1.命令show character set；

2.数据类型和字符集的选择。

Chapter 28

考察点：锁的总类以及相应的规则；READ LOCAL的定义。

答案中的关键词/句：1. A reader must block writers, but not other readers. A writer must block both readers and writers；

2. MyISAM, MEMORY, and MERGE tables are locked at the table level；

3. InnoDB tables are locked at the row level；

4. Table locking is not as desirable as page or row locking for concurrency in a mixed read/write environment；

5. Deadlock cannot occur with table locking as it can with page or row locking；

6. READ LOCK — In that case, a client that is reading from a table can lock it with a READ LOCAL lock to allow other clients to insert into the table while the client holding the read lock reads from it；

7. Beginning a transaction with START TRANSACTION causes an implicit UNLOCK TABLES；

8. Advisory locks will use the GET_LOCK() function to acquire locks and use the RELEASE_LOCK() function to release locks；

需要了解的：1.意向锁和明锁、暗锁都不一样，它不是由数据库来控制，而是由客户通过一些函数来控制；

2. 锁的不同级别：表级锁、页级锁、行级锁。

Chapter 30

考察点： 各个表维护命令的作用。

答案中的关键词/句： 1. The CHECK TABLE statement performs an integrity check on table structure and contents. It works for MyISAM and InnoDB tables. For MyISAM tables, it also updates the index statistics. If the table is a view, CHECK TABLE verifies the view definition；

2. The REPAIR TABLE statement corrects problems in a table that has become corrupted. It works only for MyISAM tables；

3. The ANALYZE TABLE statement updates a table with information about the distribution of key values in the table；

4. The OPTIMIZE TABLE statement cleans up a MyISAM table by defragmenting it. OPTIMIZE TABLE also sorts the index pages if they are out of order and updates the index statistics；

5. Rather than sending SQL statements to the server such as mysqlcheck and MySQL Administrator, myisamchk directly reads and modified the table files；

6. InnoDB storage engine can perform auto-recovery while restarting the server；

7. To enable automatic MyISAM table maintenance, start the server with the –myisam-recover option。

需要了解的：表维护的知识很有实用性，大家都应该好好掌握。

Chapter 31

考察点：INFORMATION_SCHEMA数据库和SHOW命令的比较；INFORMATION_SCHEMA数据库的访问语句。

答案中的关键词/句：1. SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘INFORMATION_SCHEMA’;

2. INFORMATION_SCHEMA is more portable; With SELECT and INFORMATION_SCHEMA, you have complete flexibility to choose what to retrieve；

3. SHOW is often more concise; The brevity of SHOW can make it an easier statement to issue。

需要了解的：1. INFORMATION_SCHEMA数据库是个虚拟数据库，它并不存储在任何磁盘里；

2. 命令SHOW VARIABLES、SHOW STATUS、SHOW ENGINES、SHOW PROCESSLIST、SHOW MASTER STATUS、SHOW SLAVE STATUS。

Chapter 32

考察点：各个备份和恢复工具的应用范围。

答案 中的关键词/句：1. mysql>USE world;

mysql> LOCK TABLES Country READ;

mysql> FLUSH TABLES Country;

mysql> UNLOCK TABLES;

2. mysqlhotcopy works for MyISAM tables but not InnoDB tables；

3. mysqlhotcopy must be run on the server host while the server is running；

4. mysqlhotcopy world./Country/ /var/archive；

5. MyISAM tables and InnoDB tablespaces are binary portable from one host to another if two conditions are met: Two’s-complement integer arithmetic and IEEE floating-point format；

6. lower_case_table_names=1；(Using lowercase names allows binary portability between Windows and Unix.)

7. mysqldump can back up local or remote servers；

8. mysqldump options: –extended-insert、–opt、–no-create-info、–no-data；

9. mysqldump world Country | mysql -h other.host.com world；

10. To increase the communications buffer size for both mysqldump and mysql with the –max-allowed-packet option；

11. shell>mysqldump –tab=/tmp world City

shell> cd /tmp

shell> mysql world < City.sql

shell> mysqlimport world City.txt；

12. shell> mysqlbinlog bin.000050 bin.000051 bin.000052 | mysql。

需要了解的：完全掌握！

到此，CMDBA战报的Easy-Pass系列（Part1&2）已全部介绍完毕，希望大家这些知识汇总能给大家的日后复习带来一定帮助，预祝渴望通过MySQL DBA认证的朋友顺利通过。另外，本人还将一些在复习过程中的对一些章节做的笔记，放到我们的论坛中，有感兴趣的朋友，可以下载以便复习之用。

GOOD LUCK, OUR DBAs OF THE FUTURE!