Learning Mysql

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 therein 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 database 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.

Download 4.24 Mb.

Share with your friends: