Useful commands for MySQL dumps

Document ID : KB000072606
Last Modified Date : 24/04/2018
Show Technical Document Details
List of useful mysql commands
The dump of event table:

mysqldump.exe -uroot -proot reporting event>event.sql

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