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 Commandline

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

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

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.

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.

Find the Export function at the header menu

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

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.

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.

All the other options will remain the same.

Your backup will download automatically

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 MySQL database using command line by using mysqldump command. Login in to 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.

Was this post helpful?