• Skip to content
  • Skip to primary sidebar

CodeBlogMoney

Make Money using Coding and Blogging

You are here: Home / MySql / Backup and Restore MySql Database to remote server

Backup and Restore MySql Database to remote server

February 9, 2017 by Jimmy

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

with Username and Password:

mysql database_name < database.sql -u root -p

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.

Filed Under: MySql Tagged With: backup and restore, mysql, sftp, tar

Reader Interactions

Primary Sidebar

Search CodeBlogMoney

Categories

  • Blogging
  • JavaScript
  • jQuery
  • JSON
  • MySql
  • Performance
  • PHP
  • Problem Solving
  • Python
  • Testing

Archives

  • July 2018
  • June 2018
  • May 2018
  • February 2017
  • December 2016
  • November 2016
  • January 2015
  • June 2014
  • January 2014

Copyright © 2019 · Metro Pro on Genesis Framework · WordPress · Log in