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```

**<font size="3">**Compress the output of mysqldump :**</font>**

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

**<font size="3">Backuping a single table from a database</font>**

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

<font size="3">**<u>MySQL Database Restore</u>**</font>

****

**<font size="3">Restoring the table into another database</font>**

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

**<font size="3">Extract single table from a mysqldump :</font>**

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.

**Assumptions:**

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

or,

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

<font color="#008040">**Update : This seems to be easier way to extract single table from MySQL Dump :**</font>

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

**<font size="3">Convert a Insert into MySQL Dump into Replace Into :</font>**

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