4,914
2020-08-30 05:13:52
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 > 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).
That's even nicer than my answer since it takes care of the CREATE TABLE too, but you should search with the backquotes so as not to get another table called "thisismytabletoo".
True. I was not sure if the table names in all mysql dumps are always surrounded by backquote or if "CREATE TABLE mytable" could also be possible. We can easily adapt the first regexp if we know how the dump looks like. A second problem could be if the table mytable is not unique (one in the database db1, and another one in the database db2). Both will be exported in the file mytable.dump. If the table is not unique, we can use the same sed command, first with CREATE DATABASE in order to extract only the right database. Then, use the sed command with CREATE TABLE.
Sweet! Remember to add DROP TABLE at the top and remove the DROP TABLE [next table] at the bottom of the file.
For not adding/removing DROP TABLE, it is more useful to match by
Table structure for tablecomment, instead of matching by CREATE TABLE. This will ensure that next table would not be dropped if one forgets to remove its DROP TABLE statement, and allows piping for single-command table restore (gzip | sed | mysql). However, this solution is dependent on mysqldump comment syntax (I don't know how standard it is).With Olexa's modification it's perfect: sed -n -e '/Table structure for.*`mytable/,/Table structure for/p' whole.sql > mytable.sql