These exercises include several questions that ask which programs can be used to perform particular tasks. The questions require that you know only about programs developed by MySQL AB. There might be third-party programs available that have similar capabilities, but they aren't covered here.
| Question 1: | Name a utility program that accesses database tables directly, without communicating with the server. Why do you have to use this type of program with extra care? |
| Question 2: | Name two programs that access tables by communicating with the server. |
| Question 3: | Does mysql allow you to create a database? Does mysqladmin? |
| Question 4: | Does mysql allow you to drop a table? Does mysqladmin? |
| Question 5: | Does mysql allow you to find out which clients are connected to the server? Does mysqladmin? |
| Question 6: | Does mysql allow you to restart the server? Does mysqladmin? |
| Question 7: | Does mysql allow you to change the contents of the my.cnf or my.ini option file? Does mysqladmin? |
| Question 8: | Does mysql allow you to change the password of a MySQL user? Does mysqladmin? |
| Question 9: | Does mysql allow you to find out whether the server is running? Does mysqladmin? |
| Question 10: | Using mysqlshow, what command would you issue to find out which tables in the test database have names starting with my? |
| Question 11: | Using mysqlshow, how can you see the indexes of the table mytable in the database test? Can you retrieve information about the indexes of multiple tables issuing a single command? |
| Question 12: | You can use mysqladmin to signal the local server to shut down. Does mysqladmin have any platform dependencies? Can the program also signal a remote server to shut down? |
| Question 13: | You can use mysql.server to signal the local server to shut down. Does mysql.server have any platform dependencies? Can the program also signal a remote server to shut down? |
| Question 14: | You can use MySQLCC to signal the local server to shut down. Does MySQLCC have any platform dependencies? Can the program also signal a remote server to shut down? |
| Question 15: | How could you find out what an error number such as 13 means?
Can't find file: './mysql/host.frm' (errno: 13)
|
| Question 16: | Which client or utility programs can be used to create a database named landmarks? |
| Question 17: | Which client or utility programs can be used to grant all privileges to user landm_editor with a password of Sakila? |
| Question 18: | Which client or utility programs can be used to create the table buildings from a batch file /tmp/buildings.sql that contains a CREATE TABLE statement that names the table but not the database in which the table should be created? |
| Question 19: | Which client or utility programs can be used to display the structure of the buildings table, including any indexes it has? |
| Question 20: | Which client or utility programs can be used to populate the table buildings with information stored in the data file /tmp/buildings.txt? Assume that the contents of the file are in the default format expected by the program. |
| Question 21: | Consider the FLUSH PRIVILEGES SQL statement, which you can issue using mysql. What command-line program could you use to accomplish the same task? What would the command look like? |
| Question 22: | Consider the SHOW PROCESSLIST SQL statement, which you can issue using mysql. What command-line program could you use to accomplish the same task? What would the command look like? |
| Question 23: | Consider the SHOW DATABASES SQL statement, which you can issue using mysql. What command-line program could you use to accomplish the same task? What would the command look like? |
| Question 24: | Consider the SHOW DATABASES LIKE 'w%' SQL statement, which you can issue using mysql. What command-line program could you use to accomplish the same task? What would the command look like? |
| Question 25: | Consider the SHOW TABLES FROM world SQL statement, which you can issue using mysql. What command-line program could you use to accomplish the same task? What would the command look like? |
| Question 26: | Consider the SHOW TABLES FROM world LIKE 'C%' SQL statement, which you can issue using mysql. What command-line program could you use to accomplish the same task? What would the command look like? |
| Question 27: | Consider the SHOW COLUMNS FROM City FROM world SQL statement, which you can issue using mysql. What command-line program could you use to accomplish the same task? What would the command look like? |
| Question 28: | Consider the SHOW KEYS FROM City FROM world SQL statement, which you can issue using mysql. What command-line program could you use to accomplish the same task? What would the command look like? |
| Question 29: | Consider the following list of ways to connect to the MySQL server. Which are operating system–dependent? Which will work only for connections to the local server, and which will also work for remote connections to a remote server?
|
| Question 30: |
Will mysql -h localhost establish a connection to a local server or a remote server? What type of connection will mysql -h localhost use (Unix socket file, Windows named pipe, or TCP/IP)? Will mysql -h localhost work only for a specific operating system?
|
| Question 31: |
Will mysql -h . establish a connection to a local server or a remote server? What type of connection will mysql -h . use (Unix socket file, Windows named pipe, or TCP/IP)? Will mysql -h . work only for a specific operating system?
|
| Question 32: |
Will mysql -h 127.0.0.1 establish a connection to a local server or a remote server? What type of connection will mysql -h 127.0.0.1 use (Unix socket file, Windows named pipe, or TCP/IP)? Will mysql -h 127.0.0.1 work only for a specific operating system?
|
| Question 33: |
Will mysql -h 192.168.10.1 establish a connection to a local server or a remote server? What type of connection will mysql -h 192.168.10.1 use (Unix socket file, Windows named pipe, or TCP/IP)? Will mysql -h 192.168.10.1 work only for a specific operating system?
|
| Question 34: |
Will mysql establish a connection to a local server or a remote server? What type of connection will mysql use (Unix socket file, Windows named pipe, or TCP/IP)? Will mysql work only for a specific operating system?
|
| Question 35: | For what kinds of files and directories does MySQL use disk space? |
| Question 36: | For what kinds of information does the MySQL server allocate memory? |
| Question 37: | How would you start the server so that it logs errors? How do you access the information in the error log? |
| Question 38: | How would you start the server so that it logs two things: queries that take longer than 10 seconds to perform and queries that use no indexes? How can you access the information logged this way? |
| Question 39: | How would you start the server so that it logs all operations that change data in tables? How can you access the information logged this way? |
| Question 40: | You know that MyISAM is the default table type in MySQL. So, what's the explanation for the following?
mysql> CREATE TABLE defaulttype (id INT);
mysql> SHOW TABLE STATUS LIKE 'defaulttype';
+-------------+--------+------------+------+-
| Name | Type | Row_format | Rows |
+-------------+--------+------------+------+-
| defaulttype | InnoDB | Fixed | 0 |
+-------------+--------+------------+------+-
|
| Question 41: | What's the explanation for the following?
mysql> CREATE TABLE bdbtable (id INT) TYPE=BDB;
mysql> SHOW TABLE STATUS LIKE 'bdbtable';
+----------+--------+------------+------+-
| Name | Type | Row_format | Rows |
+----------+--------+------------+------+-
| bdbtable | MyISAM | Fixed | 0 |
+----------+--------+------------+------+-
|
| Question 42: | Two SQL statements tell you what table type a given table mytable has. What are they? |
| Question 43: | Assume that you want a table to include a column that has a FULLTEXT index. What table types could you use? |
| Question 44: | In an application, you expect to have a high number of reads as well as a high number of writes at the same time. Which table type is best suited to handle this? |
| Question 45: | How can you change the server's default table type for new tables from MyISAM to InnoDB at server startup time? |
| Question 46: | How can you change the server's default table type for new tables from MyISAM to InnoDB if the server is already running? |
| Question 47: | How can an individual client change its own default table type to InnoDB? |
| |
| Answer 1: | myisamchk is one example of a utility program that accesses table data directly. You have to take care when using programs that access tables directly, which usually means you have to make sure that the server won't access those tables at the same time. If two or more programs access the same tables at the same time, this could lead to incorrect results or even table damage. |
| |
| Answer 2: | mysql and mysqlimport are two examples of programs that access tables by communicating with the server. There are others, such as MySQLCC, mysqlcheck, and mysqldump. |
| |
| Answer 3: | Both programs can do this task: You can create a database with either mysql or mysqladmin. (Both programs also allow you to drop a database.) |
| |
| Answer 4: | Only mysql can do this task. You cannot perform table operations with mysqladmin. To create or drop a table, use mysql. |
| |
| Answer 5: | Both programs can do this task. To find out which clients are connected to the server using the mysql client program, you can issue a SHOW PROCESSLIST SQL statement. To perform the same task with mysqladmin, you can use mysqladmin processlist. |
| |
| Answer 6: | You cannot restart the server with either program. However, mysqladmin will allow you to signal the server to shut down. |
| |
| Answer 7: | You cannot change the contents of the my.cnf or my.ini option file with either mysqladmin or mysql. You must use a text editor or some other program that provides configuration-editing capabilities. |
| |
| Answer 8: | Both programs can do this task: You can change the password of a MySQL user with either mysql or mysqladmin. Both programs allow you to change your own password, and, if you have sufficient privileges, passwords for other users as well. |
| |
| Answer 9: | Only mysqladmin is able to explicitly do this task. Although mysql has no specific "is the server running?" command, you can deduce the fact by invoking mysql: If the server allows mysql to connect, the server must be running. With mysqladmin, you can send the server a ping request (mysqladmin ping). If the server is running, it will respond saying mysqld is alive. In both cases, only an affirmative response is definitive. Failure to get a response might indicate that the server is down, but it's possible that there are other reasons. For example, the network connection between the client and server hosts might be down. |
| |
| Answer 10: | To list the tables in the test database starting with my, you would issue a mysqlshow test my% or mysqlshow test my* command. However, your command interpreter might treat the % or * character as special. It can be helpful to quote the pattern argument or to use whichever character your command interpreter does not consider special. my* may be used without quoting on Windows, and my% without quoting on Unix. |
| |
| Answer 11: | The --keys or -k option instructs mysqlshow to display table index information in addition to column information. For the mytable table in the test database, use mysqlshow --keys test mytable. You can show the indexes for only one table per command. |
| |
| Answer 12: | mysqladmin can shut down both the local server and remote servers. The program has no platform dependencies. |
| |
| Answer 13: | mysql.server cannot be used to shut down a remote server, just the local server. The program runs under Unix-like operating systems only. |
| |
| Answer 14: | MySQLCC can shut down both the local server and remote servers. The program has no platform dependencies. |
| |
| Answer 15: | You could use the perror utility to find an error message for an error number, like this:
c:\mysql\bin>perror 13
Error code 13: Permission denied
|
| |
| Answer 16: | To create the database from the command line, run mysqladmin:
shell> mysqladmin create landmarks
From within the mysql client, use the CREATE DATABASE statement:
mysql> CREATE DATABASE landmarks;
|
| |
| Answer 17: | There's no command-line program other than mysql that you could use to grant privileges to a user. However, you could use the graphical client program MySQLCC to accomplish that task. |
| |
| Answer 18: | To create a table from a statement in a batch file, you must use mysql. To specify both the filename and the database name on the command line, use this command:
shell> mysql landmarks < /tmp/buildings.sql
If you're already running mysql, you can select the database first and then use SOURCE to execute the statement in the batch file:
mysql> USE landmarks;
mysql> SOURCE /tmp/buildings.sql;
|
| |
| Answer 19: | You can display the structure of the table and its indexes using the mysqlshow command:
shell> mysqlshow --keys landmarks buildings
The same can be accomplished from within the mysql client by using the DESCRIBE and SHOW INDEX statements:
mysql> DESCRIBE landmarks.buildings;
mysql> SHOW INDEX FROM landmarks.buildings;
|
| |
| Answer 20: | To populate the table from data stored in a text file, use mysqlimport. You must specify the database name. The table name is determined implicitly from the final component of the filename (excluding any filename extension):
shell> mysqlimport landmarks /tmp/buildings.txt
The same can be accomplished from with the mysql client program using the LOAD DATA INFILE statement. Here, you must specify the table name explicitly:
mysql> LOAD DATA INFILE '/tmp/buildings.txt' INTO landmarks.buildings;
For details about the LOAD DATA INFILE command, see section A.1.20, "LOAD DATA INFILE." |
| |
| Answer 21: | You could use mysqladmin flush-privileges to accomplish the same task. |
| |
| Answer 22: | You could use mysqladmin processlist to accomplish the same task. |
| |
| Answer 23: | You could use mysqlshow to accomplish the same task. |
| |
| Answer 24: | You could use mysqlshow "w*" to accomplish the same task. |
| |
| Answer 25: | You could use mysqlshow world to accomplish the same task. |
| |
| Answer 26: | You could use mysqlshow world "C*" to accomplish the same task. |
| |
| Answer 27: | You could use mysqlshow world City to accomplish the same task. |
| |
| Answer 28: | You could use mysqlshow --keys world City to accomplish the same task. (This command will display the table columns, too, so it's not exactly equivalent to SHOW KEYS FROM City FROM world. There's no way to display only the table's indexes using mysqlshow.) |
| |
| Answer 29: | Named pipes are available only under Windows, and Unix socket files are available only under Unix-like operating systems. TCP/IP is not operating system-dependent. ODBC is not operating system–dependent in itself, but is available for MySQL only on operating systems where MySQL Connector/ODBC is supported. Any of the connection methods can be used to connect to a local server. TCP/IP and ODBC can be used to connect to a remote server. |
| |
| Answer 30: | mysql -h localhost establishes a local connection. On Unix-like operating systems, the connection is established through a Unix socket file. Under Windows, the connection is made using TCP/IP. mysql -h localhost can be used on any platform. |
| |
| Answer 31: | mysql -h . establishes a local connection using a named pipe. Named pipes are available only under Windows, so the command will work only on Windows, and only if the server was started with the --enable-named-pipe option. |
| |
| Answer 32: | mysql -h 127.0.0.1 establishes a local connection that uses TCP/IP on any operating system. |
| |
| Answer 33: | A connection to a server running on a machine specified by an IP number will establish a connection via TCP/IP on any operating system. Whether this is a local or remote connection depends on whether 192.168.10.1 is the IP number of the local host. |
| |
| Answer 34: | If no host is specified, the client will try to connect locally. Under Unix, this means it will use a Unix socket file. Under Windows, it will try to use a named pipe first, and TCP/IP if that fails. |
| |
| Answer 35: | MySQL uses disk space to store the following:
The server and client programs, and their libraries Log files and status files Databases Table format (.frm) files for all storage engines, and datafiles and index files for some storage engines InnoDB tablespace files (if the InnoDB storage engine is enabled) Internal temporary tables that have crossed the size threshold for being converted from in-memory tables to on-disk tables
|
| |
| Answer 36: | MySQL allocates memory for the following:
Connection handlers (every connection uses memory) Buffers and caches A copy of the grant tables Internal temporary tables that are below the size threshold for being converted from in-memory tables to on-disk tables The host cache and the table cache The query cache HEAP (memory) table contents (note that each HEAP table also requires disk space to store its .frm file)
|
| |
| Answer 37: | On Windows, the server logs errors to a file by default. You need do nothing to enable the error log.
On Unix, the server does not create an error log. mysqld_safe creates the error log by starting the server with its output redirected to the log file. Therefore, to log errors, start the server using mysqld_safe. You can also use mysql.server, because that invokes mysqld_safe.
To see the error log, look for a file with a .err suffix in the data directory. The error log is written in text format and can be viewed using any program that displays text files. |
| |
| Answer 38: | To log slow queries, enable the slow query log by starting the server with the --log-slow-queries option. This logs queries that take a long time to perform. By default, a long time is defined as 10 seconds. Using the --log-long-format option, in addition to the --log-slow-queries option, instructs the server to also write queries that use no indexes to the slow query log.
The slow query log is in text format and can be viewed with any program that displays text files. For convenience, you can also use mysqldumpslow to view the slow query log. This utility is available under Unix-like systems only. |
| |
| Answer 39: | Enable the binary log by starting the server with the --log-bin option. The server writes to this log all queries that modify data. Its contents are in binary format, but can be viewed using the mysqlbinlog program. |
| |
| Answer 40: | The default table type has been changed to InnoDB. For example, this could have happened at server startup (using the --default-table-type option). |
| |
| Answer 41: | If you try to use a table type that is not compiled in or has been disabled, MySQL instead creates a MyISAM table. Because the request to create the table as a BDB table was not honored by the server, this means that the server either was not compiled with support for the BDB storage engine or that BDB was disabled at server startup time (using the --skip-bdb option). |
| |
| Answer 42: | You can use either SHOW CREATE TABLE mytable or SHOW TABLE STATUS LIKE 'mytable'. See sections A.1.36, "SHOW CREATE TABLE," and A.1.43, "SHOW TABLE STATUS." |
| |
| Answer 43: | MyISAM is the only table type that supports FULLTEXT indexing. |
| |
| Answer 44: | InnoDB provides the best concurrency in an environment consisting of mixed reads and writes. Of the MySQL storage engines available, it provides the most fine-grained locking (row-level locking), and it also uses multi-versioning to give each transaction its own view of the database. |
| |
| Answer 45: | A default table type of InnoDB can be specified at server startup time by using the --default-table-type=InnoDB option, either on the command line or in an option file. For example, you could start the server like this:
mysqld --default-table-type=InnoDB
To use an option file, put these lines in the file:
[mysqld]
default-table-type=InnoDB
|
| |
| Answer 46: | A MySQL user with sufficient privileges can change the server's default table type to InnoDB while the server is running by using this statement:
SET GLOBAL table_type=InnoDB;
This setting affects all clients that connect after the statement executes. |
| |
| Answer 47: | Any client can change its connection-specific default table type by issuing this statement:
SET SESSION table_type=InnoDB;
This setting will not affect other client connections. |
