Backup and restore MySQL database to and from the remote server is very essential and basic necessary task as a freelance developer to create test server, migrate/upgrade to the new server.
For codebeautify, I have been doing this every month to test data with the new code on a test server.
Here are steps requires to backup, send and restore the database on new servers.
Creating A Backup
First, we need to create a backup of existing database using MySQL’s dump command. this command will help you to the backup single database
mysqldump database-name > database-name.sql
For multiple databases,
mysqldump --databases database_1 database_2 > multi_databases.sql
if the backup file size is large enough, we can zip/archive the file using tar command. This will also save bandwidth and time.
tar -czvf db.tar.gz database.sql
Transfer file to new server
Using sftp command it’s very faster way to transfer the file to remote server.
Here is the list of command to transfer file.
this command will login to remote server via sftp.
sftp [email protected]_hostname_or_IP
Enter the password to login to the server. Once the login is successful, move to a directory where would you like to copy the backup file using the cd command.
Now to send the file to remote server use put command.
put database.sql
If you have zip/archive the file use this command to unzip.
tar -xvzf database.tar.gz
Restore a database backup on the new server and you are all set.
mysql database_name < database.sql
These are very useful commands to migrate MySQL database to other servers to do the backup and restore of MySQL database.
I hope this article will help beginners to backup and restore MySQL database.