10 Ways to Automatically & Manually Backup MySQL Database

10 Ways to Automatically & Manually Backup MySQL Database

Last Update Date: 

How to Backup MySQL Database

  1. Automatically backup MySQL database to Amazon S3
  2. Automatically backup MySQL Database on Linux
  3. Automatically backup MySQL Database with AutoMySQLBackup
  4. Back Up with MySQLDump
  5. Backup into an XML File Using PHP
  6. Use PHP to backup
  7. Backup via SSH
  8. Backup with Ubuntu Linux Backup MySQL Server Shell Script
  9. Backup MySQL database and web server files to an FTP server automatically
  10. Backup MySQL Database with phpMyAdmin

MySQL is one of the most popular open source database management system for the development of interactive Websites.

If your site stores its sensitive data in a MySQL database, you will most definitely want to backup that information so that it can be restored in case of any disaster (we all have been there).

There are several ways to backup MySQL data. In this article we’ll look at how to backup your databases using different methods, we will also learn how to achieve an automatic backup solution to make the process easier. Starting with the mysqldump utility that comes with MySQL, we will review several examples using mysqldump, including the backup of your database to a file, another server, and even a compressed gzip file and send it to your email.

Youtube Embed Poster: ML8KDbsFkU0

Just so you know

Sign up for a free Jotform account to create powerful online forms in minutes — with no coding required.

1. Automatically backup MySQL database to Amazon S3

MySQL, AWS and Django Logo

Many of users use Amazon S3 to backup their mysql databases. Here is an automated script which does this task of taking the backup of a mysql database and then moving it to the Amazon S3.
Automatically backup mysql database to Amazon S3

2. Automatically backup MySQL Database on Linux

15 2 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`data ' %m-%d-%Y'`.sql.gz

This post will show you how to backup MySQL Database automatically if you are a linux user. You can use cron to backup your MySQL database automatically.”cron” is a time-based scheduling utility in Unix/Linux
operating system.
How to Backup MySQL Database automatically (for Linux users)

3. Automatically backup MySQL Database with AutoMySQLBackup

AutoMySQLBackup has some great features to: backup a single database, multiple databases, or all the databases on the server; each database is saved in a separate file that can be compressed (with gzip or bzip2); it will rotate the backups and not keep them filling your hard drive (as normal in the daily backup you will have only the last 7 days of backups, the weekly if enabled will have one for each week, etc.).
Backup your MySQL databases automatically with AutoMySQLBackup

4. Back Up with MySQLDump

mysqldump ---user [user name] ---password=[password]  
[database name] > [dump file]

In this article we’ll look at how to backup our databases using the mysqldump utility that comes with MySQL. Several examples will be reviewed using mysqldump, including the backup of your database to a file,
another server, and even a compressed gzip file.
Backing Up With MySQLDump

5. Backup into an XML File Using PHP

XML isn’t the easiest format to restore a table but it can be easier to read.
Backup Your Database into an XML File Using PHP

6. Use PHP to backup

Execute a database backup query from PHP file. Below is an example of using SELECT INTO OUTFILE query for creating table backup:

<?php
include 'config.php';
include 'opendb.php';
$tableName  = 'mypet';
$backupFile = 'backup/mypet.sql';
$query      = "SELECT * INTO OUTFILE '$backupFile' FROM $tableName";
$result = mysql_query($query);
include 'closedb.php';
?>

To restore the backup you just need to run LOAD DATA INFILE query like this :

<?php
include 'config.php';
include 'opendb.php';
$tableName  = 'mypet';
$backupFile = 'mypet.sql';
$query      = "LOAD DATA INFILE 'backupFile' INTO TABLE $tableName";
$result = mysql_query($query);
include 'closedb.php';
?>

How to – Using PHP To Backup MySQL Database

7. Backup via SSH

A simple solution to backup your large MySQL databases through SSH. You will need to enable shell access inside your Plesk control panel and use a utility such as PuTTY to log into your server via SSH.
Backup MySQL Database Via SSH

8. Backup with Ubuntu Linux Backup MySQL Server Shell Script

If you have a dedicated VPS server running Ubuntu Linux. Here is how to backup all your mysql server databases to your ftp server
Ubuntu Linux Backup MySQL server Shell Script

9. Backup MySQL database and web server files to an FTP server automatically

This is a simple backup solution for people who run their own web server and MySQL server on a dedicated box or VPS. The main advantage of using FTP or NAS backup is a protection from data loss.First you will need to backup each database with mysqldump command, Automating tasks of backup with tar, Setup a cron job and generate FTP backup script.

$ mysqldump -u root -h localhost -pmypassword faqs | gzip -9 > faqs-db.sql.gz

How to backup MySQL databases, web server files to a FTP server automatically

10. Backup MySQL Database with phpMyAdmin

You can easily create a dump file(export/backup) of a database used by your account. In order to do so you should access the phpMyAdmin tool available in your cPanel.

1. Open phpMyAdmin from your web hosting control panel like cPanel.

Open phpMyAdmin

2. Select the database from the sidebar navigation panel of phpMyAdmin.

phpMyAdmin DB Selection

3. Click the Export link from the top navigation bar.

phpMyAdmin Export Tab

4. Choose Custom option in the Export page.

phpMyAdmin Custom Export

In the Export page, there will be two options; Quick and Custom. Let’s choose the Custom option.

5. Select the tables that you want to backup.

phpMyAdmin Table Selection

When you select Custom option, the page will allow us to select tables that we want to take backups. If you are not sure what to select, keep it as it; all tables are selected by default.

6. Choose gzipped option for Compression and keep the rest as is.

Choose gzipped option

Next step is compression. In the center of the page, you will see the compression option that will gain us lots of time. Choose the gzipped option and keep the rest as is.

7. Click the Go button and then your backup file will automatically be downloaded.

phpMyAdmin Take Backup

We are ready to get our compressed database backup. Just click to the Go button and then your backup file will automatically be downloaded. The file name will be NameOfYourDB.sql.gz file. Depending on your database size, this procedure may take some time.

MySQL Export: How to backup your MySQL database?

Are you using someone else’s backup solution for your MySQL data? Do you care a lot about your data? Are you sure you’re getting a reliable, recoverable backup that’ll work for your business and your application, and won’t impact your critical processes while it runs? Here are ten questions you need to be able to answer.

10 things you need to know about backup solutions for MySQL

Jotform Tables Screenshot

RECOMMENDED ARTICLES

Send Comment:

Jotform Avatar
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Comments:

Subscribe and get our newsletter! This site is protected by reCAPTCHA.