CHAPTER 10 and 11 and 12 Answers from Rita Colafella

Exercises for Chapter 10

1) SQL dump files are often very large; why is this generally not a cause for worry?
There is no need to worry about the size of SQL dump files because they are only SQL statement text files which can be reduced in size before saving to a storage place. Also a full backup of the dump is not required since

frequent incremental backups can be made by saving the binary log files. Incremental backup are smaller and less likely to interupt user access.

2) Set up a weekly backup of all databases on your server. (using a Mac)

export EDITOR=pico
crontab -e
30 2 * * sun /usr/local/mysql/bin/mysqldump\
--user=root \
--password=password \
--result-file=libararies\documents\mysqlbackup.txt


3) For a production server, what time would you choose for your regular backups?

I would choose to do regular backups during the weekends when users are less likely to be on since the server has to be stopped. I would probably sometime after midnight on a weekend night.

4) How can you recover modifications that have been made to your data since the last dump?

To recover modification that have been made since the last data dump, one can use the MySQL binaryr logs. The mysqlbinlog can be used to retreive all the SQL statements since the last data dump.

5) For an application where any loss of data is unacceptable, how would you choose the
location of your binary logfile?
 I would chose to export the binary logfile to an external backup harddrive since that would give me the most control of any type of access is lost.


CHAPTER 11
1) What issues would you consider before storing your password in an options file?
If I stored my password in an options file, then I have to limit access to the access file.  Maybe I would restrict who could read or write to the file or encrypt the file.

2) On a Linux or Mac OS X system, under what circumstances would the [server] section
of the ~/.my.cn options file be read?
Mysql server willl read USER-SPECIFIC options in the server section after it processes global options in /etc/my.cnf" and "SYSCONFDIR/my.cnf", and then it goes to ~/.my.cnf.

3) How can you tell what options a program uses by default?
Use the -help option.  Help will tell you which files it looks for and which option groups it recognizes.

CHAPTER 12 
1) Why is it important to allocate a large value to read_buffer_size?
The read_buffer_size needs to be large because not all files are indexed. Therefore, every record must read.  A large read_buffer_size will make it faster to read a small number of large data chunks from a disk.

2) What is the advantage of caching query results?
Frequently used unchanged queries can be accessed much faster if caching is used.

3) What does the OPTIMIZE TABLE command do?
OPTIMIZE TABLE can find the unused space from deleted rows and defragment the data file. If a large part of a table has been deleted, OPTIMIZE TABLE can improve performance of statements that use the table.

4) If you set read_buffer_size too large, what do you think would happen?
If the read_buffer_size is too large, then I think the disk will focus on reading for a long time, which could lock it up and prevent other actions from occurring.

5) Set the slow query log on and set the long_query_time to 1. Run the following query:
SELECT SLEEP(2);

If the slow query log is set to 1 second, and then the SELECT SLEEP(2) is run it will take longer than 1 second because sleep command is set at 2.