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 ignore a single or multiple tables from backing up.
Syntax:
Ignoring Single Table:
mysqldump -h <HostName OR IP Address OR RDS End Point> -u <UserName> -p<Password>
–ignore-table=dbName.table1 dbname > schema.sql
Examples:
mysqldump -h 127.0.0.1 -u root -p –ignore-table=dbName.table1 dbname > dumpfile.sql
mysqldump -h 127.0.0.1 -u root -pMyPassword –ignore-table=dbName.table1 dbName > dumpfile.sql
mysqldump -h instance-live.uvxyzcynxmabbaz.eu-east-2.rds.amazonaws.com -u root -p –ignore-table=db_test.table1 dbName > dumpfile.sql
Ignoring Multiple Tables:
mysqldump -h <HostName OR IP Address OR RDS End Point> -u <UserName> -p<Password>
–ignore-table=dbName.table1 –ignore-table=dbName.table2 dbname > schema.sql
Examples:
mysqldump -h 127.0.0.1 -u root -p –ignore-table=dbName.table1 –ignore-table=dbName.table2 dbname > dumpfile.sql
mysqldump -h 127.0.0.1 -u root -pMyPassword –ignore-table=dbName.table1 –ignore-table=dbName.table2 dbName > dumpfile.sql
mysqldump -h instance-live.uvxyzcynxmabbaz.eu-east-2.rds.amazonaws.com -u root -p –ignore-table=db_test.table1 –ignore-table=dbName.table2 dbName > dumpfile.sql
Happy learning!!
Big Data and SQL 