How To Backup and Restore Mysql Database

How to backup and restore mysql database is very question in mind why some will see it as how to dump and restore mysql database which is almost the same.

Let us say moving from one host to another is sometimes a very hard and tasking issues, consider when you are on pythonanywhere then you want to move to AWS , then you wouldn’t want to start from afresh which is very hard and sometimes a hard task.

You may also like this
How to backup and restore mysql database in pythoanywhere
How to set allowed host

In this guide we are going to see how we can only dump or backup our database then move it to another host , with MySQL restore function.

A quick guide on how to backup and restore mysql database

• sudo mysqldump -u [user] -p > [filename].sql
• mysql -u [user] -p [database_name] < [file_name].sql

A quick guide on ow to backup and restore mysql database in pythonanywhere

• cd mysqldump -u yourusername -h yourusername.mysql.pythonanywhere-services.com –set-gtid-purged=OFF –no-tablespaces –column-statistics=0 ‘yourusername$dbname’ > db-backup.sql
• mysql -u yourusername -h yourusername.mysql.pythonanywhere-services.com ‘yourusername$dbname’ < db-backup.sql

I’m currently in Pythonanywhere but in this guide we will be dumping or backing up our database then after we will go to AWS and load out database.

If you are on pythonanywhere then this command is very important to you as well

cd mysqldump -u yourusername -h yourusername.mysql.pythonanywhere-services.com --set-gtid-purged=OFF --no-tablespaces 'yourusername$dbname' > db-backup.sql

If you’re doing the backup using MySQL Distrib 8.0, you may add –column-statistics=0 option as well:

cd mysqldump -u yourusername -h yourusername.mysql.pythonanywhere-services.com --set-gtid-purged=OFF --no-tablespaces --column-statistics=0 'yourusername$dbname' > db-backup.sql

The ‘single-quotes’ around the database name are required, because of the ‘$’ character in the full database name.

You will be able to see your db-backup.sql file in your home folder , where you can download it to a safe place.

To backup from other host , you can use

sudo mysqldump -u [user] -p > [filename].sql

NOTE:

• You should replace [User] with your username and password if needed
• The database is the path name and file name of the database
• while the > specify the output
• [filename] is the name youwant to name your file

TO RESTORE YOUR DATABASE IN UBUNTU OR WINDOWS OR AWS

mysql -u [user] -p [database_name] < [file_name].sql

How to Restore from a backup file in pythonanywhere

To restore your database , run the following command:

mysql -u yourusername -h yourusername.mysql.pythonanywhere-services.com 'yourusername$dbname' < db-backup.sql

You should be aware that this will completely delete any existing data in the database though!

.