Mysqldump Tips and Tricks

MySQL Database Backup

Ignore one or more tables while doing mysqldump :

mysqldump -u username -p database --ignore-table=db.table1 --ignore-table=db.table2
> mysqldump.sql

Compress the output of mysqldump :

mysqldump -u username -p database  | /bin/gzip -9 > filename.sql.gz

Backuping a single table from a database

mysqldump -u -p database_one table_name > /path/to/table_name.sql

MySQL Database Restore

Restoring the table into another database

mysql -u -p database_two < /path/to/table_name.sql

Extract single table from a mysqldump :

There are various ways around this. The way I describe here is to extract the sql entry between two tables. So first find the table name immediately below the table that you want to extract . Please make sure you look for the table name in the dump file or the mysql database. PHP MyAdmin can so erroneous results.


tbl1 = the table you want to extract

tbl2 = the table immediately after the table you want to extract.

awk '/-- Table structure for table .tb1/,/-- Table structure for table .tbl2./{print}' 
mysqldump.sql > extracted_tbl.sql


zcat backup.sql.gz | awk '/-- Table structure for table .table1/,/-- Table structure for table .table2./{print}'  > extracted_tbl.sql

Note : Space between — and Table, the period surrounding table name is wild card char

Update : This seems to be easier way to extract single table from MySQL Dump :

zcat backup.gz.1 | sed -n -e '/DROP TABLE.*tableName/,/UNLOCK TABLES/p' >> extract.sql

Convert a Insert into MySQL Dump into Replace Into :

sed -i "s/INSERT INTO/REPLACE INTO/g" filename.sql

You may also like...