لطفا منتظر باشید ...
Backing up MySQL Databases
Standard Backups Aren't Enough
Like Web servers, most MySQL servers are expected to remain online
24 hours a day, 7 days a week. This makes backups of MySQL database files
problematic. Because the MySQL server uses memory caches and buffers to improve
the efficiency of updates to the database files stored on disk, these files
may be in an inconsistent state at any given time. Since standard backup procedures
involve merely copying system and data files, backups of MySQL data files
cannot be relied upon, as they can't guarantee that the files that are copied
are in a fit state to be used as replacements in the event of a crash.Furthermore, as many databases receive new information at all hours
of the day, standard backups can provide only “snapshots” of database
data. Any information stored in the database that's changed after the last
backup will be lost in the event that the MySQL data files are destroyed or
become unusable. In many situations, such as when a MySQL server is used to
track customer orders on an ecommerce site, this is an unacceptable loss.Facilities exist in MySQL to keep up-to-date backups that are not adversely
affected by server activity at the time at which the backups are generated.
Unfortunately, they require you to set up a backup scheme specifically for
your MySQL data, completely apart from whatever backup measures you have established
for the rest of your data. As with any good backup system, however, you'll
appreciate it when the time comes to use it.In this chapter, the instructions I'll provide will be designed for
use on a computer running Linux, or some other UNIX-based operating system.
If you're running your MySQL server under Windows, the methods and advice
provided here will all apply equally well, but you'll have to come up with
some of the specific commands yourself. If you have any trouble, don't hesitate
to post your questions in the SitePoint
Forums.
Database Backups using mysqldump
In addition to mysqld, the MySQL server, and mysql,
the MySQL client, a MySQL installation comes with many useful utility programs.
We have seen mysqladmin, which is responsible for the
control and retrieval of information about an operational MySQL server, for
example.mysqldump is
another such program. When run, it connects to a MySQL server (in much the
same way as the mysql program or the PHP language does)
and downloads the complete contents of the database you specify. It then outputs
these as a series of SQL CREATE TABLE and INSERT commands
that, if run in an empty MySQL database, would create a MySQL database with
exactly the same contents as the original.If you redirect the output of mysqldump to a file,
you can store a “snapshot” of the database as a backup. The following
command (typed all on one line) connects to the MySQL server running on myhost as
user root with password mypass, and
saves a backup of the database called dbname into the
file dbname_backup.sql:
shell%mysqldump -h myhost -u root -pmypass dbname >
dbname_backup.sql
To restore this database after a server crash, you would use these
commands:
shell%mysqladmin -h myhost -u root -pmypass create dbname
shell%mysql -h myhost -u root -pmypass dbname < dbname_backup.sql
The first command uses the mysqladmin program to
create the database; alternatively, you can do this at the MySQL command line.
The second connects to the MySQL server using the usual mysql program,
and feeds in our backup file as the commands to be executed.In this way, we can use mysqldump to create backups
of our databases. mysqldump connects through the MySQL
server to perform backups, rather than by directly accessing the database
files in the MySQL data directory. So the backup it produces is guaranteed
to be a valid copy of the database, and not a snapshot of the database files,
which may be in a state of flux as long as the MySQL server is online.But how do we bridge the gap between these snapshots to maintain a backup
of a database that is always up to date? The solution is simple: instruct
the server to keep an update log.
Incremental Backups using Update Logs
As
I mentioned above, many situations in which MySQL databases are used would
make the loss of data—any data—unacceptable. In cases like these,
we need some way to bridge the gap between the backups we made using mysqldump as
described above. The solution is to instruct the MySQL server to keep an update
log. An update log is a record of all SQL queries that were received by the
database, and which modified the contents of the database in some way. This
includes INSERT, UPDATE, and CREATE
TABLE statements (among others), but doesn't include SELECT statements.The basic idea is that you can restore the contents of the database
at the very moment at which a disaster occurred, with the application of a
backup (made using mysqldump), followed by the application
of the contents of the update logs that were generated after that backup was
made.You can also edit update logs to undo mistakes that may have been made.
For example, if a co-worker comes to you after having accidentally issued
a DROP TABLE command, you can edit your update log to remove
that command before you restore your database using your last backup and the
log application. In this way, you can even keep changes to other tables that
were made after the accident. And, as a precaution, you
should probably also revoke your co-worker's DROP privileges
(see the next section to find out how).To tell the MySQL server to keep update logs, simply add an option to
the server command line:
shell%safe_mysqld --log-update=update
The above command starts the MySQL server and tells it to create files
named update.001, update.002, and
so on, in the server's data directory (/usr/local/mysql/var if
you set up the server according to the instructions in "Installation"). A new file will then be created each time the server flushes
its log files; in practice, this occurs whenever the server is restarted.
If you want to store your update logs somewhere else (usually a good idea—if
the disk that contains your data directory dies, you don't want it to take
your backups with it!), you can specify the full path to the update files.However, if you run your MySQL server full time, you probably have your
system set up to launch the MySQL server at start-up. The addition of command-line
options to the server can be difficult in this case. A simpler way to have
update logs created is to add the option to the MySQL configuration file, my.cnf,
which you should have created in your system's /etc directory
as part of the procedure to auto-start the MySQL server (see "Installation"). To set MySQL to create update logs by default, simply add
a log-update line below [mysqld] in
your my.cnf file. For Windows users, the my.cnf file
should be located in the root of your C: drive; alternatively, it may be named my.ini and
placed in your Windows directory.
[mysqld]
log-update=/usr/backups/mysql/update
Feel free to specify whatever location to which you'd like the server
to write the update logs. Save the file and restart your MySQL server. From
now on, the server will behave by default as if you'd specified the --log-update option
on the command line.Obviously, update logs can take up a lot of space on an active
server. For this reason, and because MySQL will not automatically delete old
log files as it creates new ones, it's up to you to manage your update log
files. The following UNIX shell script, for example, tells MySQL to flush
its log files, and then deletes all update files that were last modified more
than a week ago.
#!/bin/sh
/usr/local/mysql/bin/mysqladmin -u root –pmypasswd flush-logs
find /usr/backups/mysql/ -name "update.[0-9]*" -type f -mtime +6 | xargs rm -f
This first step (flushing the log files) creates a new update log in
case the current one is about to be deleted. This deletion will occur if the
server has been online, and has not received any queries that changed database
contents, for over a week. If you're an experienced user, setting up a script
that uses cron[1] or Windows' Task
Scheduler to periodically
(say, once a week) perform a database backup and delete old update logs should
be fairly easy. If you need a little help with this, speak to your Web host,
system administrator, or local guru, or post a message to the SitePoint
Forums (we'll be glad to help!).If you have a backup and a copy of the update logs since the backup
was made, then the restoration of your database should be fairly simple. After
you create the empty database and apply the backup as described in the previous
section, apply the update logs, using the --one-database command-line
option for mysql. This command instructs the server to
run only those queries in the update log that pertain to the database you
want to restore (db in this example):
shell%mysql -u root -pmypasswd --one-database db < update.100
shell%mysql -u root -pmypasswd --one-database db < update.102
...
[1]cron is a well-known task scheduling utility
available on most Linux and UNIX-based systems. To learn how to set up cron
tasks, begin by typing man crontab at your server's command
prompt.
