Useful commands for MySQL dumps
search cancel

Useful commands for MySQL dumps


Article ID: 72606


Updated On:


CA Spectrum DX NetOps


List of useful mysql commands


Release: Any


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';