Databases Reference
In-Depth Information
path would be
/tmp/music.sql
under Linux or Mac OS X, and
C:\music.sql
under
Windows.
mysqlhotcopy
If you want to create a copy of a database on the same host as the server, and all the
tables in your database are of the MyISAM (or the older ISAM) type, then you may find
mysqlhotcopy
handy. This is a Perl script file that's in the
scripts
directory, and differs
from
mysqldump
in that it's a
binary
copy, so you get the MySQL database files, not a
text file of SQL statements, after copying. It's also faster.
You may wonder why you need a special command to copy the database files. After
all, they're already there in the
data
directory, and you could use the operating system
copy command (e.g.,
cp
or
copy
) to copy them. The problem is that if the server is
running, what you have on disk is not always consistent with the status according to
the MySQL server. The
mysqlhotcopy
command takes care of the locking needed to
ensure that the copies are consistent, even if the server is running.
Let's look at an example that copies the database
music
to the database
music_bak
:
#
mysqlhotcopy --user=root --password=
the_mysql_root_password
music music_bak
Locked 4 tables in 0 seconds.
Flushed tables (`music`.`album`, `music`.`artist`, `music`.`played`, `music`.`track`)
in 0 seconds.
Copying 13 files...
Copying indices for 0 files...
Unlocked tables.
mysqlhotcopy copied 4 tables (13 files) in 1 second (1 seconds overall).
There are two things worth mentioning here. First, the server has to be running when
you run
mysqlhotcopy
. Second, you must have operating-system-level access to the da-
tabase files. For example, you would need to be logged in as the user who owns the
MySQL
data
directory (this could be you, or the
mysql
user), or as the system root user.
Note that
mysqlhotcopy
is a Perl script, and you'll need to follow the instructions in
“Installing Perl modules under Windows” in Chapter 2 to use this on Windows. Linux
and Mac OS X users should be able to use this script without problems.
To restore a database from the backup copy, you should stop the server, copy the
backup directory to the MySQL data directory, and restart the server. To restore all
databases on a server, you'll need backups of all the individual databases, as well as the
mysql
grants database.
Scheduling Backups
We all forget to do backups, and as Murphy's Law would have it: “The hard drive on
your computer will crash only when it contains vital information that has not been
backed up” (for this and other interesting variations on Murphy's Law, see
http://www
