Useful commands for MySQL dumps

Products

• CA Spectrum
• DX NetOps

Issue/Introduction

List of useful mysql commands

Environment

Release: Any
Component:

Resolution

NOTE: In the following MySql commands, replace <PASSWD> with the root password for your DX NetOps Spectrum version.

The dump of event table:

mysqldump.exe -uroot -p<PASSWD> reporting event > event.sql

MySQL Backups
 
Taking backup all databases:
 
mysqldump  --defaults-file -uroot -p<PASSWD> --all-databases > dump.sql
 
Making backup only one database:
 
mysqldump  --defaults-file -uroot -p<PASSWD> --databases db1 > dump.sql
 
Making backup many databases:
 
mysqldump  --defaults-file -uroot -p<PASSWD> --databases db1 db2 db... > dump.sql
 
Making backup with triggers:
 
mysqldump  --defaults-file -uroot -p<PASSWD> --triggers --all-databases > dump.sql
 
Making backup with procedures and functions:
 
mysqldump -uroot -p<PASSWD> --routines --all-databases > dump.sql
 
Compressed dump files
 
1. Normal:  mysqldump -uroot -p<PASSWD> --all-databases > dump.sql
2. gzip:    mysqldump -uroot -p<PASSWD> --all-databases | gzip > dump.sql.gz
3. bzip2:   mysqldump -uroot -p<PASSWD> --all-databases | bzip2 > dump.sql.bz2
 
Dump normal     – 947k
Dump com gzip   – 297k
Dump com bzip2  – 205k
 
Restore the dump:
 
Normal: mysql -uroot -p<PASSWD> < dump.sql
gzip:   gunzip < dump.sql.gz | mysql -uroot -p<PASSWD> 
bzip2:  bunzip2 < dump.sql.bz2 | mysql -uroot -p<PASSWD> 
 
 
To ignore a table while dumping the database
 
./mysqldump  --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> reporting --ignore-table=reporting.event  >reporting.sql
 
To take the dump of single table:
 
./mysqldump  --defaults-file=../my-spectrum.cnf -uroot -p<PASSWD> reporting event>event.sql
 
To output the results into a file 
 
select * from v_security_string_accessibility_by_landscape  INTO OUTFILE 'results.out';