لطفا منتظر باشید ...
15.7 Backup and Recovery
It''s happened to
all of us: your hard disk crashes, your machine dies, somebody steals
your box, or you get horribly hacked. In these cases, the only way to
recover your application is to restore a backup copy of your database
(after you''ve reinstalled MySQL!). There are also
less catastrophic events that can occur from which you need to
recover: indexes and tables can become corrupt because of a power
failure or MySQL unexpectedly dying, your operating system might
crash, or a disk may become unreliable.
To protect against catastrophic events, you should make regular
backups and store these offsite. There are many different ways you
can backup your MySQL installation, and different ways you can
automate the process. The simplest technique is to automate the
dumping of your database as SQL statements into a file using the
mysqldump utility, and this is the approach we
focus on in this section.
In a Unix environment, you can also use
mysqlhotcopy
to do backups; it''s a
Perl script that works only for MyISAM tables and is described in
more detail in Section 4.8.7 of the MySQL manual. Other approaches
you can use in all environments are the BACKUP
TABLE and RESTORE TABLE statements from
within the command interpreter or a PHP script, and simply copying
the database files when the database is offline. We
don''t discuss these approaches here.
All backup techniques have in common that they result in one or more
files that are the backup of the database. You could burn these files
onto a CD or other media (if they''ll fit), copy them
across a network to a backup server, or use a tape or removable disk
backup unit to make a copy (and perhaps also backup other user data
and the operating system). Ideally, you should then take the backup
offsite.
To recover from less catastrophic events, such as a power failure,
MySQL has utilities for repairing tables. We also discuss these in
this section.
15.7.1 Backup
The
simplest
way to backup all of your databases is to run the following command
in a Unix environment from a shell prompt:
% /usr/local/mysql/bin/mysqldump -uroot -ppassword --all-databases
--opt > /tmp/backup
This writes the backup to the file /tmp/backup.
In Microsoft Windows, choose the Run option in the Start menu and
type:
"C:\Program Files\EasyPHP1-7\mysql\bin\mysqldump.exe" -uroot -ppassword
--all-databases --opt > c:\windows\temp\backup
This writes the backup to the file
C:\windows\temp\backup.
Leave your MySQL server running while you issue these commands. Both
commands assume you''ve followed our installation
instructions in Appendix A through Appendix C.
These commands write everything you need into the backup file and you
can then copy this file elsewhere for safe keeping; you could
automate the backup and copying process using the techniques
discussed in Section 15.5. If you
inspect the backup file, you''ll find
it''s the SQL statements that create and insert all
of the databases, tables, and data that''s in your
MySQL installation.
The --opt option to mysqldump
writes a file that''s fast to load when restored, and
it also locks all tables in a database before dumping it (and so
avoids concurrency issues within a database). If you want to lock all
tables across all databases then add the
--first-slave option to the
mysqldump command; we don''t
recommend this if your application is online, and it
isn''t necessary if your applications have only one
database each.
15.7.2 Selective Backups
The winestore
database that you''ve loaded into your MySQL
installation is stored in the file
winestore.database. The file was created by
dumping the data from our MySQL server using
mysqldump (and then neatening up the file so
it''s organized a little better). We dumped it
initially with MySQL 3.23, and we''ve maintained it
manually since.
You can dump individual databases using the
mysqldump
command line utility. For example, to
dump the winestore database to the file
ws-dump in a Unix environment, you can use:
% /usr/local/mysql/bin/mysqldump --opt -uroot -ppassword
--databases winestore > /tmp/ws-dump
To do the same thing in Microsoft Windows, type the following in the
Run dialog that''s accessible from the Start menu:
"C:\Program Files\EasyPHP1-7\mysql\bin\mysqldump.exe" --opt -uroot
-ppassword --databases winestore > C:\windows\temp\ws-dump
The --databases option automatically adds a
CREATE DATABASE IF NOT EXISTS
winestore and USE winestore to
the beginning of the file. These statements make loading of the file
straightforward using the techniques we discuss next.
Leave MySQL running while you issue these commands. Both commands
assume you''ve followed our installation instructions
in Appendix A through Appendix C.
15.7.3 Restore
You can load a backup produced with
mysqldump into MySQL to restore a database. On
any platform, run the command interpreter and then type:
mysql> SOURCE filename
For example, on a Unix-based MySQL, if the file is stored as
/tmp/ws-dump use:
mysql> SOURCE /tmp/ws-dump
For Microsoft Windows, if the file is stored as c:\windows\temp\ws-dump, you can use:
mysql> SOURCE C:\temp\ws-dump
Be careful: this will overwrite the database or databases in your
MySQL installation. Also, make sure your application is offline when
you do this, as it''s likely to result in
unpredictable results for unsuspecting users.
As an alternative, you can pipe a file to the command interpreter.
This has the advantage that it can be added to a script file. For
example, on a Unix system, you could type:
% /usr/local/mysql/bin/mysql -uroot -ppassword
< /tmp/ws-dump
In a command window on a Microsoft Windows machine you can use:
C:\> type c:\windows\temp\ws-dump | c:\progra~1\easyph~1\mysql\bin\mysql -uroot -
ppassword
These commands assume you''ve followed our
installation instructions in Appendix A
through Appendix C.
15.7.4 Checking and Fixing Tables
Sometimes, your MySQL
server may stop without being able to carry out its normal shutdown
processes. Possible causes include machine and power failures,
operating system errors, and MySQL internal problems. If your MySQL
server does die, you should take two basic steps: first, check the
error log to see if information has been recorded that can help you
fix the problem; and, second, check your databases and tables for
errors.
The error log is a text file and you can open it in your text editor
to inspect it; to do this, you usually need to log in as the root or
administrator user. The file has a .err
extension and is found in the var or
data subdirectory of your MySQL installation.
The name of your machine usually precedes the
.err extension. If you''ve
followed our Linux installation instructions, you''ll
find the error file in /usr/local/mysql/var. On
Mac OS X, it is in /usr/local/mysql/data, and on
Microsoft Windows in C:\Program
Files\EasyPHP1-7\mysql\var. You''ll find
that the textual explanations usually explain clearly what problem
has caused your MySQL to stop or fail to start, and
it''s obvious what actions to take to rectify the
problem.
There are other situations in which you should check your databases
and tables. You should check your tables if strange results begin
appearing from queries, such as unexpected end of file,
can''t find file, or table handler errors.
It''s possible that tables haven''t
been closed properly, that the indexes are corrupted, or that data
modifications to tables weren''t completed.
It''s also possible that a component in your system
is about to fail, such as a hard disk. If so, you''ll
need to carry out repairs. If your system is about to fail, after
this you should attempt to backup using the techniques
we''ve discussed so far.
The CHECK TABLE statement checks a table, and
works for MyISAM and InnoDB tables; since Heap tables are an
in-memory structure, they don''t need to be checked
and repaired. For example, to check the customer
table, use:
CHECK TABLE customer;
It''ll report a message such as:
+--------------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| winestore.customer | check | status | OK |
+--------------------+-------+----------+----------+
1 row in set (0.06 sec)
Everything is fine if the Msg_Type reported is
status and the Msg_text is one
of OK or Table is already up to
date. If an error is found through CHECK
TABLE, it''ll probably report more than one
row in the results, and the rows will list information, warnings, and
errors in the Msg_type column and explanatory text
in the Msg_text column. If this happens, you need
to repair the table as discussed later in this section.
The CHECK TABLE statement has an optional
parameter that adjusts how quick and superficial, or slow and
thorough, the checks are. By default, it uses the
MEDIUM setting, but you can specify
QUICK, FAST,
MEDIUM, EXTENDED, or
CHANGED. We recommend using the default of
MEDIUM, and then rerunning the
EXTENDED option if the default reports errors.
EXTENDED does a slow and thorough test of the
table and its indexes. For example, to use
EXTENDED on the customer
table, type:
CHECK TABLE customer EXTENDED;
If you find an error in a MyISAM table, you can use the
REPAIR TABLE statement to attempt a repair.
Here''s an example that repairs the
customer table:
REPAIR TABLE customer;
You can also use a more thorough EXTENDED option
that recreates the indexes in a slow but careful manner:
REPAIR TABLE customer EXTENDED;
In almost all cases, this should repair a MyISAM table. If it
doesn''t, or errors keep occurring, you should look
elsewhere for the problem: perhaps your hard disk has become
unreliable and is about to crash. Section 4.4.6.9 of the MySQL manual
discusses table repair in more detail, and discusses what to do in
the unlikely event that REPAIR TABLE
doesn''t fix your problem.
You can''t use REPAIR TABLE on an
InnoDB table. However, they don''t usually have
errors. Because of InnoDB''s transactions and logging
(and checkpointing) discussed in Section 15.6. it''s very
robust in recovering from power and database server failures, and
this is a key feature of InnoDB. However, in the unlikely event that
something does go wrong and an error is reported by CHECK
TABLE, Section 7.5.4.1 of the MySQL manual shows you how to
get InnoDB to boot safely. This''ll maximize the
chances of you being able to export your data from the database
before (probably) your hard disk crashes or system fails.
15.7.5 Exporting Data to Other Environments
Data can also be dumped from a database
using SQL. MySQL supports the SELECT ... INTO
OUTFILE
statement that allows you to write
out data in a regular format, such as a comma-delimited file that can
be read into a spreadsheet program. Consider an example query that
exports a report on customer orders into the file
/tmp/orders-file:
SELECT customer.cust_id, surname, firstname, orders.order_id, sum(price)
INTO OUTFILE "/tmp/orders-file"
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY ''"'' ESCAPED BY ''\\''
LINES TERMINATED BY "\n"
FROM customer INNER JOIN orders USING (cust_id)
INNER JOIN items USING (cust_id, order_id)
GROUP BY cust_id, order_id;
On Microsoft Windows, you could replace
/tmp/orders-file with a Windows path and
file such as c:\windows\temp\orders-file.
In part, the output file contains the following output:
1,"Rosenthal","Joshua",1,11.56
1,"Rosenthal","Joshua",2,375.58
1,"Rosenthal","Joshua",3,51.31
1,"Rosenthal","Joshua",4,487.35
2,"Serrong","Martin",1,367.04
2,"Serrong","Martin",2,532.12
2,"Serrong","Martin",3,251.62
2,"Serrong","Martin",4,75.57
2,"Serrong","Martin",5,308.72
The statement is complementary to the LOAD DATA
INFILE statement discussed in Section 15.3.2. More detail on both
statements can be found in Sections 6.4.1 and 6.4.9 of the MySQL
manual.
