Ramblings from MostlyChris

Using awk, it is possible to extract a single table from a mysql dump file. You will need to know the name of the table you want AND the name of the table immediately after the table you want. I was able to find this info quicker than loading the whole dump file by going to mysql and looking at the table order and finding the table after the one I was looking for.

After getting the required information, apply some awk magic like such.

The awk statement above is printing all text from the location of the first match to the location of the second match. Matches are denoted by the text inside the forward slashes. In the above, search criteria one (starting point) is "Table structure for table .firstTable." and search criteria two (stopping point) is "Table structure for table .secondTable.". Make sure you replace "firstTable" and "secondTable" with the tables discussed earlier.

Check over your new output file and make sure it has the table you wanted and then you can import it using standard mysql commands.

The following command will optimize all databases in mysql (excpect those that don't support it due to their storage engine).

You can throw the

--auto-repair

flag in if you are brave.

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.

Here is a handy little snippet for deleting all the tables in a database. Replace 'uname' and 'dbname' as required.

If your .FRM file gets hosed up, you'll need to repair it or your database(s) will be worthless. You'll still need to make sure you make regular backups of your databases so you can use them to rebuild the .FRM file.

1. Get last good backup of database (assumes the backup is an .sql formatted single file).

2. Grep out the schema from the .sql file (grep -v 'INSERT INTO' [.sql file] > somefile.sql)

3. Locate the schema for the broken table.

4. Go into a mysql shell and create a new table with a different name and same schema as the broken table (located in step 3) in the affected database.

5. Stop mysqld.

6. Copy the [newtable].frm file in /var/lib/mysql/[database] to the [brokentable].frm, overwriting [brokentable].frm

7. Remove the [newtable].MYI/.MYD files from /var/lib/mysql/[database].

8. Start mysqld.

9. Run on a count on the newly fixed table and make sure there are records.

10. Run a mysqldump of the database and make sure it completes successfully.

11. Clean up any unneeded files created in the process of the fix.