mysqldump

mysqldump - Take data dump

export MYSQL_HOME=/u01/app/mysql/product/5.6.16/bin
export MYSQL_CONF=/u01/app/mysql/practice/etc
export CONF_FILE=practice.cnf
export MYSQL_CONF_FILE=$MYSQL_CONF/$CONF_FILE
export MYSQL_CONNECT=$MYSQL_HOME/mysql
export MYSQL_DUMP=$MYSQL_HOME/mysqldump
export DB_USER=test
export DB_PASSWORD=test
export DB_NAME=practice
export TABLES="t1 t2"

### Comments ###
### opt -> --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset ###
### compact -> --skip-add-drop-table, --skip-add-locks, --skip-comments, --skip-disable-keys, and --skip-set-charset ###

1) Particular table structure
$MYSQL_DUMP --defaults-file=$MYSQL_CONF_FILE -u$DB_USER -p$DB_PASSWORD $DB_NAME $TABLES --skip-opt --compact --skip-triggers --no-data --create-options --add-drop-table --log-error=/tmp/only_table_structure.err > /tmp/only_table_structure.sql

2) Particular table data
$MYSQL_DUMP --defaults-file=$MYSQL_CONF_FILE -u$DB_USER -p$DB_PASSWORD $DB_NAME $TABLES --skip-opt --compact --skip-triggers --extended-insert --no-create-info --log-error=/tmp/only_table_data.err > /tmp/only_table_data.sql

3) Particular table structure and data
$MYSQL_DUMP --defaults-file=$MYSQL_CONF_FILE -u$DB_USER -p$DB_PASSWORD $DB_NAME $TABLES --skip-opt --compact --skip-triggers --create-options --add-drop-table --extended-insert --log-error=/tmp/table_str_data.err > /tmp/only_table_str_data.sql

4) All tables structure (no data) and NO procs/functions/triggers
$MYSQL_DUMP --defaults-file=$MYSQL_CONF_FILE -u$DB_USER -p$DB_PASSWORD $DB_NAME --skip-opt --compact --skip-triggers --no-data --create-options --add-drop-table --log-error=/tmp/all_table_structure.err > /tmp/all_tables_structure.sql

5) All tables data (no create table) and NO procs/functions/triggers
$MYSQL_DUMP --defaults-file=$MYSQL_CONF_FILE -u$DB_USER -p$DB_PASSWORD $DB_NAME --skip-opt --compact --skip-triggers --extended-insert --no-create-info --log-error=/tmp/all_table_data.err > /tmp/all_tables_data.sql

6) Whole Schema structure including procs/functions/triggers but NO data
$MYSQL_DUMP --defaults-file=$MYSQL_CONF_FILE -u$DB_USER -p$DB_PASSWORD $DB_NAME --skip-opt --compact --no-data --create-options --add-drop-table --routines --events --log-error=/tmp/whole_schema_structure.err > /tmp/whole_schema_structure.sql

7) Whole Schema backup including data.
$MYSQL_DUMP --defaults-file=$MYSQL_CONF_FILE -u$DB_USER -p$DB_PASSWORD $DB_NAME --skip-opt --compact --create-options --add-drop-table --extended-insert --routines --events --log-error=/tmp/schema_str_data.err > /tmp/schema_str_data.sql

Note: Add "COMMIT;" at the last line of dump file
echo "COMMIT;" >> /tmp/<file_name>.sql

To Import data from mysqldump:
$MYSQL_CONNECT --defaults-file=$MYSQL_CONF_FILE -u$DB_USER -p$DB_PASSWORD -D$DB_NAME --show-warnings
mysql> set FOREIGN_KEY_CHECKS=0;
mysql> SET sql_warnings=1;
mysql> source /tmp/<file_name>.sql
mysql> SET sql_warnings=0;
mysql> set FOREIGN_KEY_CHECKS=1;
mysql> exit