How to take MySQL dump and restore the dump in AWS EC2 and RDS servers ?

November 18, 2020 / Nirav Shah

How to take MySQL dump and restore the dump in AWS EC2 and RDS servers?

What is MySQL?

MySQL is a database management system. It can store anything from a simple shopping list to a picture gallery or the vast amount of information in a corporate network. To add, access, and process data stored in a computer database, you need a database management system such as MySQL Server.

How to take backup of MySQL database from the command line from the RDS server (RDS server)

Backup of a single database

This can be used to restore a single database with all the information and table in the database eg database name is “eternal”

• mysql -u username -p databasename < databasename.sql
  • In our case, its “mysql -u username -p eternal < eternal.sql”

Then enter your password and press ENTER

Restore multiple databases or all the databases

This can be used to restore multiple databases with all the information and table in the database eg database name is “eternal” and “etenal-2019”

• mysql -u username -p –databases database1 database2 < database1_database2.sql
  • In my case, the command is “mysql -u root -p –databases eternal eternal-2019 < eternal_eternal-2019.sql”

This can be used to restore all the database all together with all the information and table in the database

• mysql -u username -p –all-databases < all_databases.sql

Restore of a specific table

This is to restore a specific table of the database

• mysql -u username -p database tablename < table.sql

How to take backup and restore MySQL database from the command line from the RDS server (RDS server)

Backup of a single database

This can be used to take the backup of the single database with all the information and table in the database eg database name is “eternal”

• mysqldump -h RDS_Endpoint -u username -p databasename > databasename.sql
  • In our case, it’s “mysqldump -h RDS_Endpoint -u username -p eternal > eternal.sql”

Then enter your password and press ENTER

Backup of multiple databases or all the databases

This can be used to backup multiple databases with all the information and table in the database eg database name is “eternal” and “eternal-2019”

• mysqldump -h RDS_Endpoint -u username -p –databases database1 database2 > database1_database2.sql
  • In our case, it becomes “mysqldump -h RDS_Endpoint -u root -p –databases eternal eternal-2019 > eternal_eternal-2019.sql”

This can be used to backup all the database all together with all the information and table in the database

• mysqldump -h RDS_Endpoint -u username -p –all-databases > all_databases.sql

Backup of a specific table

This is to take the backup of a specific table of the database

• mysqldump -h RDS_Endpoint -u username -p database tablename > table.sql

How to restore MySQL database through the command line from the RDS server ( RDS server )

Restore a single database

This can be used to restore a single database with all the information and table in the database eg database name is “eternal”

• mysql -h RDS_Endpoint -u username -p databasename < databasename.sql
  • In our case, the command is “mysql -h RDS_Endpoint -u username -p eternal < eternal.sql”

Then enter your password and press ENTER

Restore multiple databases or all the databases

This can be used to restore multiple databases with all the information and table in the database eg database name is “eternal” and “eternal-2019”

• mysql -h RDS_Endpoint -u username -p –databases database1 database2 < database1_database2.sql
  • In our case, the command is “mysql -h RDS_Endpoint -u root -p –databases eternal eternal-2019 < eternal_eternal-2019.sql”

This can be used to restore all the database all together with all the information and table in the database

• mysql -h RDS_Endpoint -u username -p –all-databases < all_databases.sql

Restore of a specific table

This is to restore a specific table of the database

• mysql -h RDS_Endpoint -u username -p database tablename < table.sql

Thus following the above command structure, one can easily take a backup of the entire database or a specific table on their localhost or AWS RDS instance running MySQL. It is evident from the commands that localhost and RDS instance commands vary very little; hence they are very useful to the developers working with MySQL on their local machines or AWS RDS instances running MySQL.

Also Read : How to migrate databases between MySQL containers?

FAQs:

1.Does RDS support data backup and restore?

2.Should I backup my MySQL database?