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:
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
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.
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.
2 thoughts on “How to Backup MySQL Database (3 Simple Methods)”
Here the simple intro, how my script works. Please read the script fully before you execute. Actually there is no harm there but you have to do what you doing..
Ref: https://vvcares.com/blog/post/backup-all-mysql-databases-compress
backup each mysql db into a different file, rather than one big file
as with –all-databases. This will make restores easier.
To backup a single database simply add the db name as a parameter (or multiple dbs)
mkdir -p /vv_files/backups
useradd –home-dir /var/backups/mysql –gid backup –no-create-home mysql-backup
Remember to make the script executable, and unreadable by others
chown -R mysql-backup:backup /var/backups/mysql
chmod u=rwx,g=rx,o= /var/backups/mysql/dump.sh
crontab entry – backup every 6 hours
sudo crontab -e [ set your desired timing. 6 hrs is safer for sysadmins]
Here the simple intro, how my script works. Please read the script fully before you execute. Actually there is no harm there but you have to do what you doing..
Ref: https://vvcares.com/blog/post/backup-all-mysql-databases-compress
backup each mysql db into a different file, rather than one big file
as with –all-databases. This will make restores easier.
To backup a single database simply add the db name as a parameter (or multiple dbs)
mkdir -p /vv_files/backups
useradd –home-dir /var/backups/mysql –gid backup –no-create-home mysql-backup
Remember to make the script executable, and unreadable by others
chown -R mysql-backup:backup /var/backups/mysql
chmod u=rwx,g=rx,o= /var/backups/mysql/dump.sh
crontab entry – backup every 6 hours
sudo crontab -e [ set your desired timing. 6 hrs is safer for sysadmins]