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:
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:
- Backup MySQL Database with phpMyAdmin
AutoMySQLBaackup is an impressive script that does the backup of the MySQL automatically. After the basic configuration, the script offers many amazing features.
- 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.
- Login to your Webhosting panel or cpanel and open phpMyAdmin
- Find the database from the sidebar navigation panel of phpMyAdmin
- Find the Export function at the header menu
- Choose an Export method to proceed
- Pick the tables that you want to backup or let it take the backup of all
- Compress the file and convert them into gZip file
- 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.
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.
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.