Can I restore a single table from a full mysql mysqldump file

Question

I have a mysqldump backup of my mysql database consisting of all of our tables which is about 440 megs  I want to restore the contents of just one of the tables from the mysqldump  Is this possible  Theoretically  I could just cut out the section that rebuilds the table I want but I don t even know how to effectively edit a text document that size

User · Answer

You can use vi editor  Type   vi -o mysql dump mytable dump   to open both whole dump mysql dump and a new file mytable dump  Find the appropriate insert into line by pressing   and then type a phrase  for example   insert into  mytable    then copy that line using yy  Switch to next file by ctrl w then down arrow key  paste the copied line with pp  Finally save the new file by typing  wq and quite vi editor by  q   Note that if you have dumped the data using multiple inserts you can copy  yank  all of them at once using Nyy in which N is the number of lines to be copied   I have done it with a file of 920 MB size

User · Answer

Get a decent text editor like Notepad   or Vim  if you re already proficient with it    Search for the table name and you should be able to highlight just the CREATE  ALTER  and INSERT commands for that table   It may be easier to navigate with your keyboard rather than a mouse   And I would make sure you re on a machine with plenty or RAM so that it will not have a problem loading the entire file at once   Once you ve highlighted and copied the rows you need  it would be a good idea to back up just the copied part into it s own backup file and then import it into MySQL

User · Answer

I used a modified version of uloBasEI s sed command  It includes the preceding DROP command  and reads until mysql is done dumping data to your table  UNLOCK   Worked for me  re importing wp users to a bunch of Wordpress sites   sed -n -e   DROP TABLE   mytable    UNLOCK TABLES p  mydump sql  gt  tabledump sql

User · Answer

I tried a few options  which were incredibly slow  This split a 360GB dump into its tables in a few minutes   How do I split the output from mysqldump into smaller files

User · Answer

sed -n -e   -- Table structure for table  my table name    UNLOCK TABLES p  database file sql  gt  table file sql   This is a better solution than some of the others above because not all SQL dumps contain a DROP TABLE statement  This one will work will all kinds of dumps

User · Answer

Table should present with same structure in both dump and database    zgrep -a   INSERT INTO   table name  DbDump-backup sql tar gz   mysql -u lt user gt  -p lt password gt  database name    or   zgrep -a   INSERT INTO   table name  DbDump-backup sql   mysql -u lt user gt  -p lt password gt  database name

User · Answer

Most modern text editors should be able to handle a text file that size  if your system is up to it   Anyway  I had to do that once very quickly and i didnt have time to find any tools   I set up a new MySQL instance  imported the whole backup and then spit out just the table I wanted   Then I imported that table into the main database   It was tedious but rather easy  Good luck

User · Answer

This tool may be is what you want  tbdba-restore-mysqldump pl  https   github com orczhou dba-tool blob master tbdba-restore-mysqldump pl  e g  Restore a table from database dump file      tbdba-restore-mysqldump pl -t yourtable -s yourdb -f backup sql

User · Answer

This may help too     mysqldump -u root -p database0  gt   tmp database0 sql   mysql -u root -p -e  create database database0 bkp    mysql -u root -p database0 bkp  lt   tmp database0 sql   mysql -u root -p database0 -e  insert into database0 table you want select   from database0 bkp table you want

User · Answer

This can be done more easily  This is how I did it   Create a temporary database  e g  restore       mysqladmin -u root -p create restore   Restore the full dump in the temp database      mysql -u root -p restore  lt  fulldump sql   Dump the table you want to recover      mysqldump restore mytable   mytable sql   Import the table in another database      mysql -u root -p database  lt  mytable sql

User · Answer

Back in  08 I had a need to do this too  I wrote a Perl script that ll do it  and it s now my method of choice  Also summarized how to do it in awk or how to restore elsewhere and extract   Recently I added this sed method to the list as well   You can find the script and the other methods here  http   blog tsheets com 2008 tips-tricks extract-a-single-table-from-a-mysqldump-file html

User · Answer

You can import single table using terminal line as given below  Here import single user table into specific database  mysql -u root -p -D my database name   lt  var www html myproject tbl user sql

User · Answer

You should try  bryn command but with the   delimiter otherwise you will also extract the tables having a prefix or a suffix  this is what I usually do   sed -n -e   DROP TABLE   mytable    UNLOCK TABLES p  dump sql  gt  mytable sql   Also for testing purpose  you may want to change the table name before importing   sed -n -e  s  mytable   mytable restored  g  mytable sql  gt  mytable restored sql   To import you can then use the mysql command   mysql -u root -p password  mydatabase  lt  mytable restore sql

User · Answer

A simple solution would be to simply create a dump of just the table you wish to restore separately   You can use the mysqldump command to do so with the following syntax   mysqldump -u  user  -p password   database   table   gt   output file name  sql   Then import it as normal  and it will only import the dumped table

User · Answer

One possible way to deal with this is to restore to a temporary database  and dump just that table from the temporary database   Then use the new script

User · Answer

You can try to use sed in order to extract only the table you want  Let say the name of your table is mytable and the file mysql dump is the file containing your huge dump    sed -n -e   CREATE TABLE   mytable    Table structure for table p  mysql dump  gt  mytable dump  This will copy in the file mytable dump what is located between CREATE TABLE mytable and the next CREATE TABLE corresponding to the next table  You can then adjust the file mytable dump which contains the structure of the table mytable  and the data  a list of INSERT

User · Answer

One way or another  any process doing that will have to go through the entire text of the dump and parse it in some way  I d just grep for  INSERT INTO  the table i want    and pipe the output into mysql  Take a look at the first table in the dump before  to make sure you re getting the INSERT s the right way   Edit  OK  got the formatting right this time

User · Answer

The  sed  solutions mentioned earlier are nice but as mentioned not 100  secure   You may have INSERT commands with data containing      CREATE TABLE    whatever    mytable    or even the exact string  CREATE TABLE  mytable     if you are storing DML commands for instance     and if the table is huge you don t want to check that manually   I would verify the exact syntax of the dump version used  and have a more restrictive pattern search   Avoid      and use     to ensure we start at the begining of the line  And I d prefer to grab the initial  DROP    All in all  this works better for me   sed -n -e    DROP TABLE IF EXISTS   mytable       UNLOCK TABLES  p  mysql dump  gt  mytable dump

User · Answer

Backup    mysqldump -A   gzip  gt  mysqldump-A gz  Restore single table    mysql -e  truncate TABLE NAME  DB NAME   zgrep   INSERT INTO   TABLE NAME  mysqldump-A gz   mysql DB NAME

User · Answer

The chunks of SQL are blocked off with  Table structure for table my table  and  Dumping data for table my table    You can use a Windows command line as follows to get the line numbers for the various sections  Adjust the searched string as needed   find  n  for table    sql txt  The following will be returned   ---------- SQL TXT   4384 -- Table structure for table my table   4500 -- Dumping data for table my table   4514 -- Table structure for table some other table      etc   That gets you the line numbers you need    now  if I only knew how to use them    investigating

[mysql] Can I restore a single table from a full mysql mysqldump file?

Examples related to mysql

Examples related to backup

Examples related to restore