Tài liệu MySQL Administrator''''s Bible- P11 pptx

Backups and Recovery 13

R1Soft is that it provides what it calls near-Continuous Online Backups. It does this by perform￾ing backups very frequently (every 15 minutes or less). This provides for a very small window of

time that data can be lost. In addition, the R1Soft software also provides for complete bare-metal

restore for MySQL servers.

The homepage of R1Soft is: www.r1soft.com.

Copying Databases to Another Machine

You can copy the .frm, .MYI, and .MYD files for MyISAM tables and the .frm and data files

(.ibd or ibdata) for InnoDB between different hardware architectures that support the same

floating-point format (Endianness). This means that you can transfer InnoDB and MyISAM

tables from Windows to Linux without doing a logical export and import. Simply shut down the

database (or lock the tables involved) and use scp to copy the database. Or, restore a physical

backup to a different machine.

In cases where you need to transfer databases between different architectures, you can use

mysqldump to create a file containing SQL statements. You can then transfer the dump file to

the second machine (the destination host) and feed it as input to the mysql client.

To move a database from one machine to another, run the following from the machine currently

holding the database (the target host):

shell> mysqldump --databases sakila | mysql -h destination_host

sakila

For large tables, exporting a tab-delimited file and using mysqlimport is much faster than

using mysqldump to export INSERT statements and restoring with source or the redirection

operator (<). The --tab=/path/to/backup option to mysqldump creates a tab-delimited

ASCII data file (.txt) and schema file (.sql) for each table, when mysqldump is run locally.

First, create the backup directory and dump the database:

shell> mkdir /path/to/backup

shell> mysqldump --tab=/path/to/backup --databases sakila

Then copy the files in /path/to/backup directory to the destination machine and load the

files into mysqld there:

shell> cat /path/to/backup/*.sql | mysql sakila

shell> mysqlimport sakila /path/to/destination/copy/*.txt

The grant tables (user permissions) are stored in the mysql database. If you do not

have a mysql database, mysqld may not start up on the new machine. Make sure to

FLUSH PRIVILEGES or restart mysqld when the grant tables are imported.

467

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Part III Core MySQL Administration

Recovering from Crashes

Many administrators spend a significant amount of time on backups and then do not spend time

on their recovery strategies. However, they make a serious mistake by not planning for how they

will recover or ever testing backups and the recovery process by performing a recovery.

The recovery process is going to vary depending on your objectives. It will always begin

with the restoration of a backup. With physical backups you just copy the files to the server

where the recovery is taking place and restart the server. For a logical backup the techniques

used for recovery are going to vary — recovery may consist of loading of files with the source

command, redirecting files with the < operator, or using mysqlimport.

Often after the backup is restored you will need to restore the server to a point-in-time after the

last backup. If this is the case you need to perform what is called a point-in-time recovery.

You can perform a point-in-time recovery with any backup process because you are using incre￾mental backups (such as the binary log files) to bring the server up to a certain point-in-time

after restoring a previous backup.

MySQL server uses a binary format for the log files to save space. This means you cannot view

it directly. MySQL supplies a utility called mysqlbinlog to convert these logs to a text format

that you can view. For more on binary logging, see Chapter 16.

The process for performing a point-in-time restore is as follows:

■ Restore the database using the last backup

■ Determine the first binary log and starting position needed

■ Determine the last binary log needed

■ Convert the binary log(s) to text format with the mysqlbinlog utility, using options to

specify the start and stop time

■ Import the converted binary log(s)

As with any recovery process, the first step is to restore the last backup performed. This restora￾tion will vary depending on how the backup was performed. For this example assume a file sys￾tem snapshot was performed at midnight of the 16th of September and the logs were flushed at

the same time. This means you have a physical backup and the restoration should just be copy￾ing the files to the server and starting up mysqld again.

Once the basic restoration is complete it is time to restore the data changes since the backup

was performed.

468

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Backups and Recovery 13

Here is a listing of the binary log directory:

$ ls -lh mysql-bin*

-rw-rw---- 1 mysql mysql 257M Sep 16 23:48 mysql-bin.010309

-rw-rw---- 1 mysql mysql 257M Sep 17 00:02 mysql-bin.010310

-rw-rw---- 1 mysql mysql 257M Sep 17 03:48 mysql-bin.010311

-rw-rw---- 1 mysql mysql 257M Sep 17 19:01 mysql-bin.010312

-rw-rw---- 1 mysql mysql 162M Sep 17 19:03 mysql-bin.010313

-rw-rw---- 1 mysql mysql 8.3K Sep 17 19:01 mysql-bin.index

This means that mysql-bin.010310 is the first binary log created after the backup was per￾formed. This was determined by looking at the timestamp of the log files, which shows the last

time the log file was modified. Knowing the backup was performed at midnight you can see that

mysql-bin.010309 was the last log written before midnight. Therefore the next log file is the

one with which you want to start your restoration.

For this example, you need to restore the server through the last log listed, which is

mysql-bin.010313.

If you have a large number of binary logs (such as in this case) to convert it would probably be

beneficial to script this process. The command to convert an entire binary file will look similar

to this:

$ mysqlbinlog mysql-bin.010310 > mysql-bin.010310.sql

This would convert the mysql-bin.010310 log to text format and store it in the

mysql-bin.010310.sql file. You will have to do this for each log file needed. The final

part of the process is the import of the log files into the database server:

$ mysql --user=root --pasword < mysql-bin.010310.sql

This would need to be done for each converted binary log. Once again, scripting might be

helpful.

To create text files from parts of binary logs using mysqlbinlog, specify a starting place

with either --start-datetime=’YYYY-MM-DD’ or --start-position=# and ending

place with either --stop-datetime=’YYYY-MM-DD’ or --stop-position=#. To determine

the exact position to start or stop you have to examine the binary log contents. The problem is

that this can be a large file. To start you have to convert the log to text format:

$ mysqlbinlog mysql-bin.010312 > mysql-bin.010312.sql

469

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Part III Core MySQL Administration

Once you convert the log file you can view the text-format log with a text editor. With a binary

log of 162 MB in size this may be tricky. If you are looking to end at a specific time you can

specify a stopping time:

$ mysqlbinlog --stop-datetime=’2008-09-17 18:42:48’ mysql-bin.010312

> mysql-bin.010312.sql

Once you have trimmed the file it becomes much easier to view with the tail command. Now

you will still have to potentially look through a number of entries because a busy database

server is going to be executing hundreds, if not thousands, of queries a second. Here are the last

25 lines after trimming:

$ tail -25 mysql-bin.010312.sql

use usersession/*!*/;

SET TIMESTAMP=1221702167/*!*/;

UPDATE XXXXX /*!*/;

# at 185118382

#080917 18:42:47 server id 16 end_log_pos 185118409 Xid =

9731310851

COMMIT/*!*/;

# at 185118409

#080917 18:42:47 server id 16 end_log_pos 185118473 Query

thread_id=1273437368 exec_time=1 error_code=0

SET TIMESTAMP=1221702167/*!*/;

BEGIN/*!*/;

# at 185118473

#080917 18:42:47 server id 16 end_log_pos 185118508 Rand

SET @@RAND_SEED1=700138339, @@RAND_SEED2=45664511/*!*/;

# at 185118508

#080917 18:42:47 server id 16 end_log_pos 185119173 Query

thread_id=1273437368 exec_time=1 error_code=0

use usersession/*!*/;

SET TIMESTAMP=1221702167/*!*/;

UPDATE XXXXX /*!*/;

# at 185119173

#080917 18:42:47 server id 16 end_log_pos 185119200 Xid =

9731310854

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

$

In this case you want to execute the first COMMIT statement and then stop. The line after the

COMMIT statement shows the log position. The log position is 185118473. Now you can create

your final text format file with exactly the right information:

470

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Backups and Recovery 13

$ mysqlbinlog --stop-position=185118473 mysql-bin.010312 >

mysql-bin.010312.sql

This file (mysql-bin.010656.sql) is what you will want to import.

$ mysql --user=root --password < mysql-bin.010656.sql

It would be wise to examine the resulting file to ensure it is correct before execution of the log

file.

Table 13-6 lists common options for the mysqlbinlog program.

TABLE 13-6

mysqlbinlog Options

Option Description

--start-datetime=

"date_time"

Begins reading the binary log file at a timestamp equal to or

greater than the datetime argument.

--stop-datetime=

"date_time"

Ends reading the binary log file at a timestamp equal to or

greater than the datetime argument.

--start-position=

start_log_position

Begins reading the binary log file beginning at the first log

position equal to or greater than start_log_position.

--stop-position=stop_

log_position

Ends reading the binary log file at the first event having a

log position equal to or greater than stop_log_position.

Planning for Disasters

Database recovery is part of the disaster planning process. What to do, who does it, and how

long the recovery process takes when things break requires thought, planning, and usually coor￾dination with other people and departments. It is important that you rehearse plans and perform

drills to make sure that the proper preparations are in place.

A backup plan and corresponding periodic restores of your backups should be part of the disas￾ter preparation. An incomplete list of issues covered could include:

■ Power

■ Employee termination process

■ Data center failover plan

■ Data retention strategies

471

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.