14.7 Exercises
| Question 1: | To copy a MyISAM table to another database server on another host, you can either copy the table files directly, or you can use mysqldump to dump the table contents into a file and then import that file into the other server. Suppose that a table named mytable exists in the test database of server 1, but not server 2. You want to copy the table from server 1 to server 2 without stopping either server for the copy operation. How might you do that? | | Question 2: | On a Windows server host, you want to compress the MyISAM table City, found in the GuideProf database. Assuming that MySQL is installed in C:\mysql and that MySQL's data directory is in the default location, what commands would you issue to compress the table if your current location is the C:\mysql\bin directory? | | Question 3: | On a Windows server host, you want to compress the MyISAM table City, found in the GuideProf database. After you've done so, how would you verify that the table has actually been compressed? | | Question 4: | On a Windows server host, you want to compress the MyISAM table City, found in the GuideProf database. What operations will you no longer be able to perform on City after the table has been compressed? | | Question 5: | On a Windows server host, you want to compress the MyISAM table City, found in the GuideProf database.
After you've done so, how can you unpack the compressed table? What alternatives to using myisamchk --unpack could you use to unpack the table after it has been compressed?
| | Question 6: | You want to compress and then unpack the MyISAM table City, found in the GuideProf database. After you've unpacked the compressed table, how would you verify that the table has actually been unpacked? | | Question 7: | Why would you specify the MAX_ROWS option for a table? | | Question 8: | Where does MySQL store the format, data, and index files for a MyISAM table by default? What options do you use to tell the server to place the files in locations different than the default? Are there restrictions to the use of these options? | | Question 9: | Which of the following alternatives correctly describes what the server does when it finds that the disk is full during an update operation on a MyISAM table?
It cancels the update operation silently. It cancels the update operation with an error message. It waits until free space becomes available. It deletes rows in the table until there's enough free space to complete the operation. It replaces existing rows with the data the update operation would insert or change.
| | Question 10: | Assume that you've locked the City table with a READ lock. If you now try to select data from the table, what will happen? | | Question 11: | Assume that you've locked the City table with a READ lock. If another client now tries to select data from the table, what will happen? | | Question 12: | Assume that you've locked the City table with a READ lock. If you now try to insert data into the table, what will happen? | | Question 13: | Assume that you've locked the City table with a READ lock. If another client now tries to insert data into the table, what will happen? | | Question 14: | Assume that you've locked the City table with a READ lock. Who can release the lock? | | Question 15: | Assume that a client has locked the City table with a READ lock in a connection with ID 2098. Both the client with connection ID 2098 and a client with connection ID 2099 have tried to insert data into City. What will happen when the lock is released? | | Question 16: | The connection with ID 2098 requests a WRITE lock on the table City. However, the connection with ID 2099 has already obtained a READ lock on the same table. Which of the following statements correctly describes how the server handles the WRITE lock request?
The WRITE lock request results in an error. The server automatically releases the READ lock of connection 2099 and gives connection 2098 a WRITE lock. The server makes the WRITE lock request of connection 2098 wait until connection 2099 releases its READ lock. Connection 2098 cannot obtain the WRITE lock as long as connection 2099 has the READ lock, so the server silently converts the WRITE lock request by connection 2098 to a READ lock request.
| | Question 17: | Under what circumstances is a lock on a table released? | | Question 18: | Are there circumstances under which an INSERT LOW_PRIORITY statement might never be performed? | | Question 19: | What table or tables will the following statements lock, if any?
mysql> LOCK TABLES City READ; LOCK TABLES Country WRITE;
| | Question 20: | You want to use INSERT to add data into a table, but you suspect that the table is locked by a lock request of another client. You're using an interactive client (for example mysql), so you would have to wait for that lock to be released before you can continue with your work. How can you solve this problem, and still insert the data? | | Question 21: | According to the following session listing, it appears that the SELECT query took longer than two minutes to execute. What is the most probable reason for that?
mysql> INSERT DELAYED INTO City (ID, name) VALUES (20000, 'Delayne');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT ID, name FROM City WHERE ID = 20000;
+-------+---------+
| ID | name |
+-------+---------+
| 20000 | Delayne |
+-------+---------+
1 row in set (2 min 5.61 sec)
| | Question 22: | Assume that you have the following tables in the project database:
mysql> SHOW TABLES;
+-------------------+
| Tables_in_project |
+-------------------+
| auth |
| lang |
| project |
+-------------------+
3 rows in set (0.00 sec)
You backed up all tables in that database as follows:
shell> mysqldump project --tab=/tmp
Someone accidentally dropped that database. Using mysql (and no other program), what statements do you have to issue to restore the database and its tables? | | Question 23: | Which backup programs and backup strategies require the server to be running, and which do not? | | Question 24: | Which backup programs and backup strategies work when used on the local host? Which can be used from a remote host? | | Question 25: | What does the SQL statement BACKUP TABLE do? | | Question 26: | How can you restore tables backed up with BACKUP TABLE? What limitations must you observe when using this restore method? | | Question 27: | Assume that you want to back up all tables in the project database using mysqldump on the local host. You do not want to dump the table structure, just the data. The data should be stored in text files in the /tmp directory, with columns separated by tabs and records separated by \r\n. What command would you issue? | | Question 28: | Assume that you've issued the following command to back up all table data in the project database:
shell> mysqldump --no-create-info --tab=/tmp --lines-terminated-by="\r\n" project
Could you use a similar command to back up all table data for multiple databases? | | Question 29: | Assume that you've issued the following command to back up all table data in the project database:
shell> mysqldump --no-create-info --tab=/tmp --lines-terminated-by="\r\n" project
How would you restore the tables? | | Question 30: | The test database contains tables named tbl1 and tbl2. Assume that you want to back up the data contained in the tables. The data should be written into text files in the /backup directory. In the text files, you want columns to be separated by commas and lines to be separated by DOS line endings (\r\n). What command do you issue? What will be the names of the resulting output files? | | Question 31: | Assume that you've issued the following command to back up the data contained in the test database's tbl1 and tbl2 tables:
shell> mysqldump --tab=/backup --fields-terminated-by=,
--lines-terminated-by="\r\n" test tbl1 tbl2
Can you use a similar, single command to back up the data of all databases on the server? If so, how? | | Question 32: | What SQL statement would you use to update a table's index statistics? | | Question 33: | What command-line program would you use to update a table's index statistics? | | Question 34: | What is the effect of a table analysis? | | Question 35: | What precautions should you observe when analyzing a table? | | Question 36: | What does it mean to optimize a MyISAM table, and how would you do it? |
Answers to Exercises | | | Answer 1: | Two methods for copying the mytable table to another server are shown here. Other methods are possible.
To copy mytable directly, you have to make sure that the server (and any other program) doesn't access the table files, and that the table is entirely flushed to disk. This would be done by issuing these statements:
mysql> LOCK TABLES mytable READ;
mysql> FLUSH TABLES;
Now, you can copy the table files (mytable.frm, mytable.MYD, and mytable.MYI) to the other server host. The location to copy them to would be the test directory in that server's data directory. Afterward, you have to unlock the table on the first server:
mysql> UNLOCK TABLES;
Using mysqldump, you would issue this command:
shell> mysqldump test mytable > mytabledump.sql
After that, you can copy mytabledump.sql to the other server host, and import it by issuing this statement:
shell> mysql test < mytabledump.sql
This assumes that mytabledump.sql is located in the directory from which you invoke mysql.
See sections A.1.21, "LOCK TABLES," and A.1.16, "FLUSH TABLES." | | | | Answer 2: | The City table is located in the GuideProf directory under the MySQL data directory, C:\mysql\data. To refer to that directory from within the C:\mysql\bin directory, you can use either the absolute pathname C:\mysql\data\GuideProf or the relative pathname ..\data\GuideProf. The commands shown here use the latter.
To compress the City table, bring down the server, and then issue this command:
shell> myisampack ..\data\GuideProf\City.MYI
Compressing ..\data\GuideProf\City.MYD: (4079 records)
- Calculating statistics
- Compressing file
70.94%
Remember to run myisamchk -rq on compressed tables
As indicated by the last output line, you should now run myisamchk with the --recover (or -r) and --quick (or -q) options:
shell> myisamchk --recover --quick ..\data\GuideProf\City.MYI
- check key delete-chain
- check record delete-chain
- recovering (with sort) MyISAM-table '..\data\GuideProf\City.MYI'
Data records: 4079
- Fixing index 1
Now you can restart the server. | | | | Answer 3: | The City table is located in the GuideProf directory under the MySQL data directory, C:\mysql\data. To refer to that directory from within the C:\mysql\bin directory, you can use either the absolute pathname C:\mysql\data\GuideProf or the relative pathname ..\data\GuideProf. The commands shown here use the latter.
To verify that the table has actually been compressed, you could issue this command:
shell> mysql -e "SHOW TABLE STATUS LIKE 'City'" GuideProf
+------+--------+------------+------+----------------+-------------+-
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | ...
+------+--------+------------+------+----------------+-------------+-
| City | MyISAM | Compressed | 4079 | 19 | 79419 | ...
+------+--------+------------+------+----------------+-------------+-
See section A.1.43, "SHOW TABLE STATUS." | | | | Answer 4: | You can read from a compressed table, but you can no longer modify it, as these examples show:
mysql> USE GuideProf;
mysql> INSERT INTO City (ID, name) VALUES (10000, 'Test City');
ERROR 1036: Table 'City' is read only
mysql> ALTER TABLE City ADD testcolumn INT;
ERROR 1036: Table 'City' is read only
See section A.1.1, "ALTER TABLE." | | | | Answer 5: | The City table is located in the GuideProf directory under the MySQL data directory, C:\mysql\data. To refer to that directory from within the C:\mysql\bin directory, you can use either the absolute pathname C:\mysql\data\GuideProf or the relative pathname ..\data\GuideProf. The commands shown here use the latter.
To unpack the compressed table, use this command:
shell> myisamchk --unpack ..\data\GuideProf\City.MYI
- recovering (with keycache) MyISAM-table '..\data\GuideProf\City.MYI'
Data records: 4079
There are also other ways to unpack the table. One alternative is to use mysqldump to dump the table, followed by mysql to reimport it, as shown here:
shell> mysqldump --opt GuideProf City > CityDump.sql
shell> mysql GuideProf < CityDump.sql
Another alternative is to execute these statements with mysql:
mysql> CREATE TABLE CityCopy TYPE=MyISAM SELECT * FROM City;
Query OK, 4079 rows affected (0.03 sec)
Records: 4079 Duplicates: 0 Warnings: 0
mysql> DROP TABLE City;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE CityCopy RENAME TO City;
Query OK, 0 rows affected (0.00 sec)
See sections A.1.9, "CREATE TABLE," A.1.1, "ALTER TABLE," and A.1.14, "DROP TABLE." | | | | Answer 6: | To verify that the table has actually been uncompressed, use SHOW TABLE STATUS, as shown here:
shell> mysql -e "SHOW TABLE STATUS LIKE 'City'" GuideProf
+------+--------+------------+------+----------------+-------------+-
| Name | Type | Row_format | Rows | Avg_row_length | Data_length | ...
+------+--------+------------+------+----------------+-------------+-
| City | MyISAM | Fixed | 4079 | 67 | 273293 | ...
+------+--------+------------+------+----------------+-------------+-
See section A.1.43, "SHOW TABLE STATUS." | | | | Answer 7: | Reasons to specify MAX_ROWS for a table are as follows:
For a MyISAM table, specifying MAX_ROWS allows the server to make a better estimate of how large to make the table's internal row pointers. A large MAX_ROWS value is useful when you expect a table to become very large and to contain more rows than the default pointer size will allow. For a table that you know will remain small, a small MAX_ROWS value saves space because the row pointers can be smaller than the default size. MAX_ROWS can be used for a HEAP table to make sure that it doesn't grow unexpectedly large and use up all available memory.
See section A.1.9, "CREATE TABLE." | | | | Answer 8: | By default, MySQL places the format, data, and index files for a MyISAM table under the data directory, in the database directory for the database that contains the table. There is no option for creating the format file anywhere else. It is always stored in the database directory. The datafile and index file can be placed elsewhere by using the table options DATA DIRECTORY='directory' and INDEX DIRECTORY='directory'. One restriction on these options is that directory must be a full pathname to the directory, not a relative path. Another is that these options work only on operating systems that support symlinks, and only when you have not started the server with the --skip-symlink option.
See section A.1.9, "CREATE TABLE." | | | | Answer 9: | The server waits until free space becomes available. | | | | Answer 10: | You acquired the READ lock, so you can read data from the table:
mysql> LOCK TABLES City READ;
mysql> SELECT * FROM City LIMIT 1;
+----+-------+---------+----------+------------+
| ID | name | Country | District | Population |
+----+-------+---------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
+----+-------+---------+----------+------------+
See section A.1.21, "LOCK TABLES." | | | | Answer 11: | The READ lock that you acquired does not prevent other reads. All other clients can still read data from the table:
mysql> SELECT * FROM City limit 1;
+----+-------+---------+----------+------------+
| ID | name | Country | District | Population |
+----+-------+---------+----------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
+----+-------+---------+----------+------------+
See section A.1.21, "LOCK TABLES." | | | | Answer 12: | Trying to insert data after acquiring a READ lock will result in an error:
mysql> INSERT INTO City (ID, name) VALUES (10000, 'Test City');
ERROR 1099: Table 'City' was locked with a READ lock and can't be updated
See section A.1.21, "LOCK TABLES." | | | | Answer 13: | When you are holding a READ lock on a table, attempts by any other client to insert data into the table result in that client waiting for the lock to be released:
mysql> INSERT INTO City (ID, name) VALUES (10000, 'Test City');
(nothing happens here until you release the lock)
See section A.1.21, "LOCK TABLES." | | | | Answer 14: | Only the client that acquired the lock can release it. One way to do so is with the UNLOCK TABLES statement:
mysql> UNLOCK TABLES;
Query OK, 0 rows affected
The lock also is released if the client acquires another lock with LOCK TABLES, closes the connection, or is aborted. (An administrator who has appropriate privileges can abort the connection by using a KILL statement.)
See sections A.1.21, "LOCK TABLES," and A.1.49, "UNLOCK TABLES." | | | | Answer 15: | When the lock is released, the INSERT statement of connection 2099 will be executed.
See sections A.1.21, "LOCK TABLES," and A.1.49, "UNLOCK TABLES." | | | | Answer 16: | The WRITE lock request of connection 2098 waits until connection 2099 releases the READ lock.
See sections A.1.21, "LOCK TABLES," and A.1.49, "UNLOCK TABLES." | | | | Answer 17: | A lock can be released several ways:
When the client that acquired the lock issues an UNLOCK TABLES statement. When the client that acquired the lock issues another LOCK TABLES statement (no matter whether for the same or different tables). When the client that acquired the lock is terminated (either by ending it normally, or when the client is killed by another client). An administrator can kill a client connection. This releases locks held by the client.
See sections A.1.21, "LOCK TABLES," and A.1.49, "UNLOCK TABLES." | | | | Answer 18: | Yes. An INSERT LOW_PRIORITY statement waits until there are no read requests or normal-priority update requests in progress or pending on that table. This includes new requests that arrive while the INSERT LOW_PRIORITY is waiting. If a server is so busy that there is never a time when no read requests are in progress or pending, the INSERT LOW_PRIORITY might wait forever.
See sections A.1.21, "LOCK TABLES," and A.1.18, "INSERT." | | | | Answer 19: | The first statement locks table City, but the lock is released immediately with the next lock request. As a result, table Country will be locked for read and write requests by other clients. If you want to lock both tables at the same time, you have to issue this statement:
mysql> LOCK TABLES City READ, Country WRITE;
See section A.1.21, "LOCK TABLES." | | | | Answer 20: | You can insert the data using an INSERT DELAYED statement. This allows you to proceed immediately. The rows to be inserted will be buffered by the server and added to the table when it becomes free.
See section A.1.18, "INSERT." | | | | Answer 21: | Most probably, there was a lock on that table obtained by another client. Only when that other client released its lock was the INSERT DELAYED statement performed. After that, the SELECT statement was performed. With no further information given, you can assume that the lock was in effect for at least two minutes and five seconds.
See sections A.1.21, "LOCK TABLES," A.1.18, "INSERT," and A.1.29, "SELECT." | | | | Answer 22: | First, re-create the database:
mysql> CREATE DATABASE project;
Query OK, 1 row affected (0.00 sec)
Next, re-create the tables using the .sql files stored in /tmp:
mysql> SOURCE /tmp/auth.sql;
Query OK, 0 rows affected (0.00 sec)
mysql> SOURCE /tmp/lang.sql;
Query OK, 0 rows affected (0.00 sec)
mysql> SOURCE /tmp/project.sql;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW TABLES;
+-------------------+
| Tables_in_project |
+-------------------+
| auth |
| lang |
| project |
+-------------------+
3 rows in set (0.00 sec)
Finally, load the data stored in the .txt files in /tmp:
mysql> LOAD DATA INFILE '/tmp/auth.txt' INTO TABLE auth;
Query OK, 1 row affected (0.02 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0
mysql> LOAD DATA INFILE '/tmp/lang.txt' INTO TABLE lang;
Query OK, 9 rows affected (0.00 sec)
Records: 9 Deleted: 0 Skipped: 0 Warnings: 0
mysql> LOAD DATA INFILE '/tmp/project.txt' INTO TABLE project;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0
By default, mysqldump used the same column and line formatting options as the LOAD DATA INFILE statement, so you don't have to specify those explicitly when reloading the tables.
See sections A.1.7, "CREATE DATABASE," A.1.44, "SHOW TABLES," and A.1.20, "LOAD DATA INFILE." | | | | Answer 23: |
To back up data, you can use mysql to issue these SQL statements: BACKUP TABLE SELECT … INTO OUTFILE
mysql requires the server to be running. You can also use mysqldump to back up data. The server must be running. Another way to perform data backup is to use the Perl script mysqlhotcopy. The server must be running for mysqlhotcopy to work. In addition, the script requires the Perl DBI module to be installed. If the server is not running, the only way to back up a MyISAM table is to copy its .frm, .MYD, and .MYI files directly.
See sections A.1.3, "BACKUP TABLE," and A.1.29, "SELECT." | | | | Answer 24: |
mysql:
To back up data, you can run the BACKUP TABLE or SELECT … INTO OUTFILE statements under mysql. The statements can be run from any client host that can connect to the server, so you don't necessarily have to connect from the local host. The backup files, however, will always be located on the server host. This means that you must have the FILE privilege to perform backup operations using mysql.
mysqldump:
To back up data, you can also run mysqldump, from either a local client or from a remote client. The first of the following commands performs a dump that connects to the local server. The second command connects to a remote server.
shell> mysqldump test > dump_db_test.sql
shell> mysqldump test -h remote_host -u username -p > dump_db_test.sql
In both cases, the backup file is created on the client host where mysqldump is invoked.
mysqlhotcopy:
To back up data, you can also run the Perl script mysqlhotcopy. To do so, you must ensure that the script is run on the same host as the server (mysqlhotcopy can only run on the same host as the server itself).
| | | | Answer 25: | BACKUP TABLE performs a number of operations:
First, it acquires a READ lock on the table or tables to be copied. It flushes any pending table changes to disk. It copies the .frm and .MYD files of the specified MyISAM tables to the specified directory. Finally, it releases the READ lock.
See section A.1.3, "BACKUP TABLE." | | | | Answer 26: | To restore tables backed up with the BACKUP TABLE statement, you would use the RESTORE TABLE statement. Before doing so, you must ensure that any tables you want to restore do not already exist. Otherwise, the restore operation will fail. RESTORE TABLE uses the .frm and .MYD files to re-create the table, then rebuilds the indexes to re-create the .MYI index file.
See sections A.1.3, "BACKUP TABLE," and A.1.26, "RESTORE TABLE." | | | | Answer 27: | To dump the table data to text files, you can use the --tab option to mysqldump. By default, this produces output files in which columns are separated by tabs and records are separated by \n. The tab separators match the requirements stated but the record separators do not, so a --lines-terminated-by option is needed. By default, mysqldump will also produce SQL statements that can be used to re-create table structures. With the --tab option, this information will be stored in .sql files. To prevent creation of these files, use the --no-create-info option. To dump only the table data in the format required, you would issue this command:
shell> mysqldump --no-create-info --tab=/tmp --lines-terminated-by="\r\n" project
| | | | Answer 28: | No, this is not possible. The --tab option can be used to dump only a single database. To back up multiple databases using this method, it's necessary to issue multiple mysqldump commands. | | | | Answer 29: | To restore tables that have been backed up with the --tab option to mysqldump, you can use LOAD DATA INFILE. Because the records are terminated by \r\n, it will be necessary to use a LINES TERMINATED BY option. LOAD DATA INFILE must be issued for every table that was backed up. For example, to restore a table table_1, you would issue this statement:
mysql> LOAD DATA INFILE '/tmp/table_1.txt' INTO TABLE table_1 LINES TERMINATED BY '\r\n';
See section A.1.20, "LOAD DATA INFILE." | | | | Answer 30: | To back up the data as stated, you would issue this command:
shell> mysqldump --tab=/backup --fields-terminated-by=,
--lines-terminated-by="\r\n" test tbl1 tbl2
The resulting files will be named tbl1.sql, tbl1.txt, tbl2.sql, and tbl2.txt. | | | | Answer 31: | mysqldump can back up multiple databases (or even all databases), but not when invoked with the --tab option. This option causes the output files for all tables to be written into a single directory. That would make it impossible to tell which database each table came from. | | | | Answer 32: | Use ANALYZE TABLE to update a table's index statistics:
mysql> ANALYZE TABLE City;
+----------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| GuideProf.City | analyze | status | OK |
+----------------+---------+----------+----------+
See section A.1.2, "ANALYZE TABLE." | | | | Answer 33: | To update a table's statistics from the command line, use either mysqlcheck or myisamchk:
shell> mysqlcheck --analyze world City
world.City OK
shell> myisamchk --analyze c:\mysql\data\GuideProf\City.MYI
Checking MyISAM file: c:\mysql\data\GuideProf\City.MYI
Data records: 4083 Deleted blocks: 0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
| | | | Answer 34: | The effect of analyzing a table is twofold: A check is performed on the table and the table's index statistics are updated. Analyzing can thus help the query optimizer better process joins involving the table. | | | | Answer 35: |
When using the ANALYZE TABLE statement or the mysqlcheck program, there are no special precautions to consider. The server automatically locks the table before performing the analysis. Before using myisamchk --analyze, you must ensure that no other program can access the table to be analyzed. This could be done as follows:
mysql> LOCK TABLES City READ;
mysql> FLUSH TABLES;
Alternatively, stop the server so that it isn't running when you use myisamchk.
See sections A.1.2, "ANALYZE TABLE," A.1.21, "LOCK TABLES," and A.1.16, "FLUSH TABLES." | | | | Answer 36: | Optimizing a MyISAM table means reclaiming unused space that results when rows are deleted from the table. Deleted rows can cause wasted space within a table, thus slowing down table reads. To optimize a MyISAM table, use the OPTIMIZE TABLE statement. For example:
mysql> OPTIMIZE TABLE City;
+----------------+----------+----------+-----------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+-----------------------------+
| GuideProf.City | optimize | status | Table is already up to date |
+----------------+----------+----------+-----------------------------+
You can also use mysqlcheck --optimize.
See section A.1.22, "OPTIMIZE TABLE." |
|
