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 transfers 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 replicate the database into a different instance.
Syntax:
mysqldump -h <HostName OR IP Address OR RDS End Point> -u <UserName> -p<Password>
dbname |
Examples:
mysqldump -h 127.0.0.1 -u root -p dbname | mysql -h HostName -u root -p dbName
mysqldump -h instance-live.uvxyzcynxmabbaz.eu-east-2.rds.amazonaws.com -u root -p dbname | mysql -h instance-live.uvxyzcynxmabbaz.eu-east-2.rds.amazonaws.com -u root -p dbname
The following will assist in moving one database from one instance (of aws rds) to another instance (of aws rds) in a single transaction without locking the tables or requiring the usage of “create database and use database commands” and including the views, triggers and routines. For the execution to be successful, the destination database must exist.
mysqldump –no-create-db dbName –single-transaction –compress –order-by-primary –host instance-live.uvxyzcynxmabbaz.eu-east-2.rds.amazonaws.com -u myUser -pMyPwd –default-character-set=utf8mb4 –hex-blob –triggers –routines -P 3306 | mysql -f –host instance-live.uvxyzcynxmabbaz.eu-east-2.rds.amazonaws.com -u myUser -pMyPwd -P 3306 dbName
Happy learning!!
Big Data and SQL 