Back Up and Restore MySQL Databases

MySQL databases can be backed up from the Command Prompt or from phpMyAdmin. It is a good idea to back up your MySQL data occasionally as a precautionary measure. It is also a good idea to create a back up before making any major changes, in case something goes wrong and you need to revert to the unmodified version. Database backups can also be used to transfer your database from one server to another if you change web hosts.

01
of 04

Back Up Database From the Command Prompt

From a command prompt, you can back up an entire database using this line:

mysqldump -u user_name -p your_password database_name > File_name.sql

Example:
Assume that:
Username = bobbyjoe
Password = happy234
Database Name = BobsData

mysqldump -u bobbyjoe -p happy234 BobsData > BobBackup.sql

This backs up the database to a file called BobBackup.sql

02
of 04

Restore Database From the Command Prompt

If you are moving your data to a new server or you have removed the old database completely, you can restore it using the code below. This only works when the database does not already exist:

mysql - u user_name -p your_password database_name < file_name.sql

or using the previous example:

mysql - u bobbyjoe -p happy234 BobsData < BobBackup.sql

If your database already exists and you are just restoring it, try this line instead:

mysqlimport -u user_name -p your_password database_name file_name.sql

or using the previous example again:

mysqlimport -u bobbyjoe -p happy234 BobsData BobBackup.sql
03
of 04

Back Up Database From phpMyAdmin

backup mysql database with phpmyadmin
  1. Log in to phpMyAdmin.
  2. Click on your database name.
  3. Click on the tab labeled EXPORT.
  4. Select all the tables you want to back up (usually all of them). Default settings usually work, just make sure SQL is checked.
  5. Check the SAVE FILE AS box.
  6. Click GO.
04
of 04

Restore Database From phpMyAdmin

restore mysql database from phpMyAdmin
  1. Login to phpMyAdmin.
  2. Click on the tab labeled SQL.
  3. Unclick the Show query here again box
  4. Choose your backup file
  5. Click GO
Format
mla apa chicago
Your Citation
Bradley, Angela. "Back Up and Restore MySQL Databases." ThoughtCo, Aug. 26, 2020, thoughtco.com/backup-and-restore-mysql-databases-2693879. Bradley, Angela. (2020, August 26). Back Up and Restore MySQL Databases. Retrieved from https://www.thoughtco.com/backup-and-restore-mysql-databases-2693879 Bradley, Angela. "Back Up and Restore MySQL Databases." ThoughtCo. https://www.thoughtco.com/backup-and-restore-mysql-databases-2693879 (accessed March 28, 2024).