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 show you how to take a backup and restore of all databases using mysqldump from once RDS instance to another RDS instance in a single go.
mysqldump -f
-h awsinstance.amazonaws.com
-P 3306
-u root
-pPassword
–default-character-set=utf8mb4
–hex-blob
–triggers
–routines
–all-databases | mysql -f -u root -pPassword -h awsinstance2.amazonaws.com
Change your instance name, credentials, and preferences based on your requirement.
mysqldump backups a database with just one transaction if it is using the –single-transaction option. This ensures that the database maintains a consistent state during the backup process by executing the whole backup procedure within a single transaction.
–triggers option allows to include the triggers.
–routines option allows to include the stored procedures/user-defined functions.
–default-character-set option is to specify the character set to be considered.
–hex-blob option is to dump binary columns using hexadecimal notation
| (Pipe symbol) is a connector between backup and restoration from once instance to another instance. With this the backup will be written in the destination server on fly.
Please note that the databases mysql, information_schema, performance_schema will not be dumped with the above statement. The users information needs to be added separately.
Hope you find the article helpful. If you need more clarification, drop a message.
Big Data & SQL 