Archive for July, 2009

On more than one occasion I have had to figure out how to restore data from a mysql-binlog and I do it so infrequently that I decided to put the command here so I know where to find it in the future. The first thing to do is to export the binlog. Since it is in binary, you will need to use the mysqlbinlog command.

Let's assume we have a file called mysql-binlog.000111. There are a number of ways this can be exported, based on your needs, and those choices are available via command line flags (man mysqlbinlog for details). Let's say you need data from a certain start time up to a certain end time. This can be specified on the command line. You can also specify the database that you wish to export the data for. This is because if you run multiple databases, they are all logged to the same set of binlogs. A command to do all of this would look like (it doesn't show, but the flags are all preceeded by double dashes):

That command says pull all of the transactions from the mysql-binlog.000111 file for the 'mydatabase' database starting from 7-31-2009 at 5am up until 7-31-2009 at 10:05am and output them to the output.txt file.

This file can simply be input directly into the database using the following command:

Some notes here. First, there is a 'use ' statement in the output file. If you are restoring a database to a backup database and not the original you will want to remove the 'use' statement from that file (or all of the use statements if there is more than one). Otherwise, you will run the binlog right into the original database. One easy way to do this is run grep -v and remove any line that starts with use, outputting that to a new file.

Finally, you would modify your mysql statement to use the backup database name and the file without the 'use' statements.

Assuming no errors, this will run all of your queries into the database.

Final notes: This binlog replay assumes that you have restored the original database up until the 5am time from some backup file you had. The binlog will then recreate all of the transactions from the time the backup ends until 10:05am. If you try to replay from the binlog and there is already data in the database for those transactions, you could potentially run into duplicate key errors, etc.

I was installing APC on a server and ran across this error:

Fatal error: Allowed memory size of 8388608 bytes exhausted (tried to allocate 92160 bytes) in /usr/share/pear/PEAR/PackageFile/v2/Validator.php on line 1831

One would assume that fixing this would be to edit the php.ini file and change the memory settings. However, that is not the case here. In order to fix this, you would edit /usr/share/pear/pearcmd.php and add the following line to the very top of the file (after the opening PHP tag):

Adjust the memory limit to what you need. For good measure, I remove that line once I am done installing whatever it is I need to install.

A simple and quick way to get a status of the adapters and devices in the RAID array using megacli is to run the following command:

It will output a LOT of information for the adapter, but you should see the Device Present section that shows the status of the drives. For example, the following shows a failed drive in the array:

=====
Device Present
================
Virtual Drives : 1
Degraded : 1
Offline : 0
Physical Devices : 3
Disks : 2
Critical Disks : 0
Failed Disks : 1
=====

To get more detailed information on the drives in the array, issue the command:

Which results in the following output.

=====
Adapter #0

Number of Virtual Disks: 1
Virtual Disk: 0 (target id: 0)
Name:
RAID Level: Primary-1, Secondary-0, RAID Level Qualifier-0
Size:237464MB
State: Degraded
Stripe Size: 64kB
Number Of Drives:2
Span Depth:1
Default Cache Policy: WriteBack, ReadAdaptive, Cached, No Write Cache if Bad BBU
Current Cache Policy: WriteBack, ReadAdaptive, Cached, No Write Cache if Bad BBU
Access Policy: Read/Write
Disk Cache Policy: Disk's Default
Number of Spans: 1
Span: 0 – Number of PDs: 2
PD: 0 Information
Enclosure Device ID: 252
Slot Number: 0
Device Id: 4
Sequence Number: 2
Media Error Count: 0
Other Error Count: 0
Predictive Failure Count: 0
Last Predictive Failure Event Seq Number: 0
Raw Size: 238475MB [0x1d1c5970 Sectors]
Non Coerced Size: 237963MB [0x1d0c5970 Sectors]
Coerced Size: 237464MB [0x1cfcc000 Sectors]
Firmware state: Online
SAS Address(0): 0xb221c046788723f
Connected Port Number: 0(path0)
Inquiry Data: ATA ST3250620AS K 6QE1DRKL

PD: 1 Information
=====

Notice that it shows a state of degraded. The above output is that of the degraded variety. There are two PDs (physical disks) in this span but there is only one showing in the the output.

In order to find out what drive is failed, it is actually the absence of information that shows this. Issue the following command:

This results in an output similar to the following:

=====
Adapter #0

Enclosure Device ID: 252
Slot Number: 0
Device Id: 4
Sequence Number: 2
Media Error Count: 0
Other Error Count: 0
Predictive Failure Count: 0
Last Predictive Failure Event Seq Number: 0
Raw Size: 238475MB [0x1d1c5970 Sectors]
Non Coerced Size: 237963MB [0x1d0c5970 Sectors]
Coerced Size: 237464MB [0x1cfcc000 Sectors]
Firmware state: Online
SAS Address(0): 0xb221c046788723f
Connected Port Number: 0(path0)
Inquiry Data: ATA ST3250620AS K 6QE1DRKL
=====

In the case of the above, there are two drives, one each in slot 0 and slot 1. Since slot 1 is not listed above, the assumption is that the bad drive is slot 1. Slot 0 is showing with no errors. Note that the command

also shows the missing slot 1 information, indicating that slot 1 is the failed drive.

I ran across a server in which all the inodes were used and this was causing myriad issues on the server.

I needed to find out where all the files were so my colleague Kale provided me with this little script to do what I needed.

This is not recursive so you'll have to drill down through the directories to find the root source.