Useful commands for MySQL dumps

Document ID : KB000072606
Last Modified Date : 24/04/2018
Show Technical Document Details
Introduction:
List of useful mysql commands
Instructions:
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';