How to Extract a Table from DMP

MySQL dump files serve as backup files for large database structures. These files contain the data for database organization, so that administrators can restore databases to earlier states should something go wrong. But databases can get large, so dump files can get just as large. Updating a single table, rather than the entire database, can become a chore. However, using either some Linux scripting commands or a backup database, you can use a dump file to selectively manage database restoration.

Things You'll Need

  • MySQL database


  1. Linux Shell

    • 1

      Open a command terminal by clicking on Start menu icon, then selecting Utilities followed by Terminal.

    • 2

      Navigate to the directory of the dump file in the terminal:

      $>cd /directory/of/dumpfile.sql

    • 3

      Extract the table from the SQL dump file using the Linux awk command, and put it into another file. In order to pull the table you must look for some sort of identifier for the table so awk can locate it. In this example the table is surrounded by comments that say "command for tablename 1" and "command for tablename 2." You can reset the table in the main database with this command:

      $>awk ‘/command for tablename 1/,/command for tablename2/{print}’ dumpfile.sql > /tmp/singe_table.sql

    Backup Database

    • 4

      Create a backup database at the SQL command:

      mysql> create database bu

    • 5

      Load the backup database from the dump file:

      $ mysql -u user -ppassword fakedb < dumpfile.sql

    • 6

      Select the table data from the backup database, and write it to a backup file:

      mysql> select * from targettable into outfile “/tmp/backup.bak”;

    • 7

      Load the table data into the production server:

      mysql> delete from oldtable;
      mysql> load data infile “/tmp/mytablebackup.bak” into table oldtable;

Related Searches


Related Ads

Check It Out

Prep for Black Friday Checklist [Infographic]