- Run the MySQL server with logging by starting it with safe_mysqld --log-
update &. - Perform a mysqldump to create the full backup (weekly). Specifying the --
flush-logs option will automatically flush logs each time you do a dump. - The log files will record all changes to the database and its data. At any given
time, there will be one complete log file (or more) since the last full dump. For
extra security, you may choose to save these off-system on a daily basis.
Other MySQL Logs
You have already seen how to make MySQL log all changes to a database, such as INSERTs, UPDATEs,
and DELETEs, by running mysqld with the --log-update option. It creates a text log with each of the SQL
queries within it.
You can also start mysqld (or safe_mysqld) with the --log option, which will cause MySQL to
generate its main activity log. This records almost everything—SELECT queries as well as updates. The
log will usually be stored in /var/lib/mysql/hostname.log where hostname is the name of the
machine. (It may be in /usr/local/var/ on older systems.)
The --log option to mysqld can be useful in diagnosing application bugs. If you suspect that incorrect
queries are being made on the database, you can study the log to see exactly what query an application
performed and when.
If you really want detailed information about what MySQL is doing, perhaps for debugging system
problems at a deeper level, you can start mysqld with the --debug option. There are numerous sub-
options for this debugging mode that can track MySQL's activities in detail.
To turn off logging, shut down mysqld and start it up again (or safe_mysqld) without the logging
options.
Relocating a MySQL Database
There are several reasons why you may want to relocate a MySQL database. You may be upgrading your
system, or you may want to transfer the database to another machine. You may even have a "master" copy
of a database, held in a secure location, that you periodically want to take a copy of and have it available
from a Web server or other system.
Whatever the reason, the process has basically three steps: saving, transferring, and restoring.
You already learned one method for saving your database, using mysqldump. This is the preferred
method. It is possible to save a database directly from the file system by backing up the .frm, .ISD,
and .ISM files. However, this will not ensure data consistency, and is not recommended.
Your database can then be tape archived, using a utility such as tar, and compressed, using ZIP, for
speedier transfer.When you are at the target machine, unzip and untar, and then you're ready to restore.
If you did a mysqldump --tab or something similar, you will have sets of SQL and data files.
Alternatively, if you did mysqldump -opt, you will have a single large file containing both SQL CREATE
commands and the data in the form of INSERT statements. To restore your database under UNIX or
Linux, a command such as the following should do the job:
cat /home/mydata/dump.txt | /usr/local/mysql/bin/mysql -u username -ppassword database_name
The cat command combined with | (pipe) is immensely powerful. In a one-line instruction, you make
cat read through the file and send it into mysql, as referenced in the example by its full pathname on
the target system. You will usually need to add -u and -p parameters and the name of the target
database.MySQL System Functions
MySQL has a number of system functions, which provide access to administrative aspects of your database,
that can be called from within your MySQL database.
The commands are quite broad in scope, ranging from database and user-related information to
encoding and encryption methods.