How to Backup MySQL Database
- Open phpMyAdmin from your web hosting control panel like cPanel.
- Select the database from the sidebar navigation panel of phpMyAdmin.
- Click the Export link from the top navigation bar.
- Choose Custom option in the Export page.
- Select the tables that you want to backup.
- Choose gzipped option for Compression and keep the rest as is.
- Click the Go button and then your backup file will automatically be downloaded.
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.
1. Automatically backup MySQL database to Amazon S3
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
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'; ?>
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
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.
2. Select the database from the sidebar navigation panel of phpMyAdmin.
3. Click the Export link from the top navigation bar.
4. Choose Custom option in the Export page.
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.
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.
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.
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.
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.