mysqldump - extended options

mysqldump command dumps databases/tables to standart output. You can use pipe redirect to save it to file:

mysqldump [options] >dump.sql

When you dump a database there are some options of mysqladmin you need to understand:

• –skip-comments - Use this option if you want make a smaller file
• –compatible - if you export database from higher version to lower you will need this option. Possible values are:
  • –compatible=mysql323
  • –compatible=mysql40
  • –compatible=mysql323
• –disable-keys - this will speed up import process
• –add-drop-table - if you make backups you can assume that when you restore it, tables will be created and they need to be dropped. This option does it.
• –quote-names - if you use reserved words as comumn or table names this will prevent errors during import.

How to dump a table(s):

  mysqldump <db_name> <table1> <table2> ...

How to dump a database:

  mysqldump <db_name>

How to dump a more than one database:

  mysqldump --databases <db_name1> <db_name2> ...

How to dump all databases:

  mysqldump --all-databases

Here is good set of options.

file dumpdb

#!/bin/bash
#
#  --compatible=mysql323
#
#  --compatible=mysql40
#
#  --compatible=mysql323
#
 
prev_dir=$PWD
 
md=/usr/local/mysql/bin/mysqldump
 
$md $* -pn1cks0ft \
--extended-insert \
--skip-comments \
--disable-keys \
--add-drop-table \
--routines \
--quote-names
#--skip-extended-insert