How to Backup MySQL Database (3 Simple Methods)

how to backup mysql database

How to Backup MySQL Database from Commandline

Follow the process to backup mysql database via CLI

1. Connect to MySQL server via command line

mysql -username -p

2. Enter the password and hit enter

3. Take a backup of the database named ABC-database with this command

mysqldump mysql-database > ABC-database-backup.sql

These commands will backup database ABC-database into a SQL dump with the name ABC-database-backup.sql.

You need to know the name of the database you want to make a backup of.

There is a simple command that can display all the database name:

show databases

You can also multiple backup databases with mysqldump command.

Enter this command:

mysqldump --databases database_ABC database_XYZ > ABCXYZ_databases.sql

This is the command for backing up all the MySQL Database at once:

mysqldump --all-databases > all_databases.sql

Restoring the MySQL Database through Command line

1. Enter this command to restore the MySQL Backup

mysql database_name < database_name.sql

This is only one way to backup and restores the MySQL Database, among many.

Ways to Backup the MySQL Database:

  1. AutoMySQLBackup
  2. Backup MySQL Database with phpMyAdmin

AutoMySQLBackup

automysqlbackup backup mysql database

AutoMySQLBaackup is an impressive script that does the backup of the MySQL automatically. After the basic configuration, the script offers many amazing features.

It can:

  • Backup a single database
  • Do backup multiple databases
  • Backup of all the databases on the server

You can enable the gZip function. It will create a separate compressed gZip file for the databases. The AutoMySQL allows you to set up the (recurring) time for the backups. 

Get AutoMySQLBackup Script from here

Backup MySQL Database with phpMyAdmin

You can also backup MySQL Database with phpMyAdmin in a few steps.

  1. Login to your Webhosting panel or cpanel and open phpMyAdmin
  2. Find the database from the sidebar navigation panel of phpMyAdmin
  3. Find the Export function at the header menu
  4. Choose an Export method to proceed
  5. Pick the tables that you want to backup or let it take the backup of all
  6. Compress the file and convert them into gZip file
  7. Your backup will download automatically

Login to your Webhosting panel or cpanel and open phpMyAdmin

All the quality hosting services provide cPanel or a custom panel for the various functions.

You will find the phpMyAdmin easily.

1 backup mysql database

Find the database from the sidebar navigation panel of phpMyAdmin

On the left side, the sidebar displays all the databases you have on the server. Select the one you want to make a backup of.

2 backup mysql database

Find the Export function at the header menu

3 backup mysql database

Choose an Export method to proceed

There are two methods:

  • Quick: does not show all the tables
  • Custom: Display all the tables in the database
4 backup mysql database

Select a method to continue.

Pick the tables that you want to backup or let it take the backup of all.

The next step is to select the tables to start creating the backup of MySQL Database.

5 backup mysql database

All the tables are selected by default. If any of the table is broke, you need to repair the tables.

If you don’t know which one to select, make a backup of all the tables.

Compress the file and convert them into gZip file

Compress the file before backing up the tables. It will save time and space, as well.

6 backup mysql database

All the other options will remain the same.

Your backup will download automatically

7 backup mysql database

This is the final step. 

Click on the Go button, and the phpMyAdmin will take care of the rest process. Your backup will download automatically once the process is done.

The name of the Backup MySQL Database would be databasename.sql.gz. Longer size files would take longer to backup. 

Quick Questions

How do I backup MySQL database using the command line?

You can backup the MySQL database by using mysqldump command. Login into your server and run the command mysql dump with root access. 

How do I view a MySQL database?

You can view a MySQL database with the command show databases. Another method is to log in to your web hosting panel and opening up the phpMyAdmin panel.

What is MySQL?

MySQL is an RDBMS based on SQL. However, MySQL has multiple arrays of uses; the most common method is working as a web database.

How do I schedule a backup in MySQL?

You can schedule a backup in MySQL though the AutoMySQLBackup script. The script needs some tuning to sync with your server, and it will perform various functions to make the backup an easy task.

Latest Magento Tips, Guides, & News

Stay updated with new stuff in the Magento ecosystem including exclusive deals, how-to articles, new modules, and more. 100% Magento Goodness, a promise!

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top

We can help you. Right now.

Fast growing merchants depend ServerGuy for high-performance hosting. Experience counts. Let's get started.

Talk to a sales representative

USA / Worldwide

+1.714.2425683

India

+91.9852704704