MySQL is a relational database management system (DBMS). It processes database instructions and can manage multiple databases at the same time.
For example, if you want to create a new database or add data to an existing database, you send a message to the MySQL server, give it the data you want to add and tell it where to add it.
Whether your business is small or large, your data is essential. To ensure that your valuable information is protected from damage, theft, or disaster, back up your MySQL database. This article will explain several ways to do this.
Backup MySQL database using phpMyAdmin
Create an export or backup file of your MySQL database by accessing the phpMyAdmin tool from your web hosting control panel, such as cPanel. For the purposes of this article, we will be using cPanel.
- First, log into your cPanel and click phpMyAdmin.
- Select the MySQL database you want to back up from the navigation bar on the left sidebar. Then click on the export link located on the top navigation bar.
- On the Export page, you will see two options: Custom and Quick. Select the “Custom” option.
- After selecting Custom, you will see a list of your databases. You can choose one, several or all. By default, everything is backed up.
- In the Output Options, select gzipped for compression. Leave the rest of the parameters as default.
- Click the Go button to start the backup and download the file. Your file name will be YourDatabaseName.sql.gz file.
The time it takes for the backup to complete will depend on the size of your database.
Backup MySQL database using mysqldump
Use the mysqldump command to dump your database text file that MySQL will manage. A text file dump is a text file that contains the SQL commands required to rebuild the database from scratch.
- To back up one database, use the following command:
mysqldump dbname> dbname.sql
This command will create a backup and send it to the .sql file. This will only make a copy of your database and will not affect it.
- Use the following command to back up multiple databases at the same time.
mysqldump â€“databases database_one database_two> two_databases.sql
Database_one is the name of the first database and database_two is the name of the second database you want to back up. Both will be copied into a single database.
- If you want to back up all your MySQL databases on the server, use the following command to create a single .sql backup file that will contain all of your databases.
mysqldump â€“all-databases> all_databases.sql
Backup MySQL database using the Crron functions
A cron job is a Linux command used to automatically execute a task at a specified time. We will explain how to use this command to automate MySQL database backups.
- Start by logging into cPanel, scroll down to the Advanced section and click on Cron jobs.
- You will see a warning that you must be familiar with Linux commands in order to use cron jobs effectively. If not, contact your hosting administrator.
- If you want to receive an email every time a cron job runs a command that produces output, enter your email address in the field and click Refresh Email.
- Select one of the general settings from the drop-down menu to choose how often you want to run the backup.
- You can also configure other parameters such as time and day of the week.
- Now is the time to enter the following command:
/ usr / bin / mysqldump -u dbusername -pâ€™dbpassword â€™dbname> /home/username/path/backup.sql
- Replace dbusername, dbpassword and dbname with your database username, database password, and database name.
- Path means the folder or set of folders where you want to place the backup file. The backup file name appears as backup.sql in the command. You can change this name or leave it as it is. Click Add New Cron Job.
- If you scroll down a bit, you will see a list of current cron jobs.
- If you want to change any of the current cron jobs, click Change. If you no longer want to run any task or make a mistake, click “Delete”.
Use WordPress (WP) plugins to backup MySQL
If you are using WP, you can use the plugin to back up your database. UpdraftPlus will back up the database file with one click. It currently has over two million active installations.
Use BackWPup to back up your MySQL database files and even a full WP installation.
There are many more WP plugins available for MySQL backups. Choose the one that works with the current WP version and is actively updated.
To avoid losing important data, make regular backups of your MySQL database. If you have a backup, you can restore important or irreplaceable data if something breaks your site.