Skip to main content

selective table restore from mysqldump

I recently had to restore a corrupted table that had resulted from a full disk. I had been using automysqlbackup as my backup script. Each database had a corresponding backup file. It would have been easy to use a text editor to edit out the tables that you didn't want to restore but this database had grown to 1G.

STEP 1: Restore table structure

If the table structure is corrupted, you need to retrieve the CREATE TABLE statements from the backup. To do this you can use grep:

zcat backup.sql.gz | grep -A 20 'CREATE TABLE `tablename1`' > tablestructure.sql

This command will create tablestructure.sql containing the CREATE TABLE statement and 20 lines after that. You may have to change the parameter from 20 to whatever to get the entire statement.

Once tablestructure.sql had been edited to only contain the proper CREATE TABLE statement, you may now restore the table structure.

mysql -u user -p dbname < tablestructure.sql

STEP 2: Extract and restore the table data

This command will create a sql file for the table.

zcat backup.sql.gz | grep 'INSERT INTO `tablename1`' > tabledata.sql

If the resulting sql file is small enough, you may load the table straight back into the database.

mysql -u user -p dbname < tabledata.sql

OPTIONAL: If you have a huge table and want to partially restore or batch things, you may want to split the table data into multiple lines.

split -l 100 tabledata.sql

This will create multiple files of 100 lines each named xaa, xab, xac, etc. You may then load each file separately.

If some rows are already on the table and you just don't want the INSERT to fail on a duplicate primary key, you can use sed to add the IGNORE option to the INSERT statement. The command below simply does a search and replace on the source file and writes it to a new file.

sed 's/INSERT/INSERT IGNORE/' < tabledata.sql > newtabledata.sql

That should do the trick.

Comments

Post a Comment

Popular posts from this blog

iReport Default Date Parameter

If you want to set a default date for a jasperreports parameter, you can enter the following in the default value expression of the parameter. new Date() However, if you wanted the default to be relative from today, for example, 3 days ago, you will need to do go outside of the standard java date libraries (Date, Calendar) because JR parameter does not allow you to have multiple statements. You can write your own utility package or you can use Joda , a replacement library for the JDK date and time library. To use Joda in iReports, you need to extract the jar file and add it to Options -> Classpath. You may also drop the jar file in iReports' lib directory. You will also need to add this library to your application or report server. For setting a default date 5 days prior from today, use this: new org.joda.time.DateTime().minusDays(5).toDate() or this if you want the time part set to the beginning of the day: new org.joda.time.LocalDate().toDateTimeAtStartOfDay().minusDays(5).to...
5 comments
Read more

Migrating Moniwiki to MoinMoin

I had been using Moniwiki for our company for a while because the installation was quite easy as it was PHP based. However, I found out that document versioning didn't really work. It may have been a configuration issue but I took this opportunity to take a look at MoinMoin, as it was being used by Ubuntu and looked well supported. The server was running Debian stable and MoinMoin was in the repositories. Installing was simple using aptitude. Configuration was a bit more involved as it required copying and editing files. The new wiki site was pleasantly more responsive. The next task was to move the data in Moniwiki to MoinMoin. Upon inspecting the data repositories, I realized that Moniwiki stored the data in individual files with the WikiWord as the filename while MoinMoin organized that data with the WikiWord as a directory with the actual content under the revisions directory. I cooked up a quick script to migrate the data. I also ran the previous file through a 'tr' to...
Post a Comment
Read more

CI/CD for .NET Core

If you have a .NET API that you want to containerize, here are the steps to prepare it. Create docker image Push to repository Setup ECS The TLS 1.0 option is needed if your SQL Server database does not support TLS 1.2. The recommended option is to update your database to the secure protocol. You may also change sdk and aspnet version to 5.0 if you haven't updated to 6.0 yet. This Dockerfile uses a multi-stage build to get an optimal image size. Dockerfile FROM mcr.microsoft.com/dotnet/sdk:6.0 AS build-env WORKDIR /app # Copy csproj and restore as distinct layers COPY *.csproj ./ RUN dotnet restore # Copy everything else and build COPY . ./ RUN dotnet publish -c Release -o out # Build runtime image FROM mcr.microsoft.com/dotnet/aspnet:6.0 WORKDIR /app # Enable TLS 1.0 RUN sed -i 's/DEFAULT@SECLEVEL=2/DEFAULT@SECLEVEL=1/g' /etc/ssl/openssl.cnf RUN sed -i 's/MinProtocol = TLSv1.2/MinProtocol = TLSv1/g' /etc/ssl/openssl.cnf RUN sed -i 's/DEFAULT@SECLEVEL=2/DEFAULT@...
Post a Comment
Read more