mysqldump – Include views, routines and triggers

The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another server. The mysqldump command can also generate output in CSV, other delimited text, or XML format. Although it appears to be a little executable file, its scope is greater to accommodate a variety of use-cases.

mysqldump accepts various connection options, DDL Options, Format Options, Filtering Options, Performance Options, Transactional Options, etc.

This post will demonstrate how to use mysqldump to include and exclude views, triggers and routines from the backup or restore.

Syntax:
mysqldump -h <HostName OR IP Address OR RDS End Point> -u <UserName> -p<Password>
—-hex-blob –triggers –routines dbname > schema.sql

Examples:
mysqldump -h 127.0.0.1 -u root -p –hex-blob –triggers –routines dbname > schema.sql
mysqldump -h 127.0.0.1 -u root -pMyPassword –hex-blob –triggers –routines dbname > schema.sql
mysqldump -h instance-live.uvxyzcynxmabbaz.eu-east-2.rds.amazonaws.com -u root -p –hex-blob –triggers –routines dbname > schema.sql

Note: Since mysqldump does not distinguish between VIEWs and TABLEs, you do not need to specify –views because the command will also include the views. Since there is no such option, it will give an error if you specify it.

Another note: In the above command, –hex-blob is also specified which usually dump binary columns that are using hexadecimal notation.

Happy learning!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s