10 Tutorials To Take MySQL Database Backup
MySQL is the most famous open source database management system for the development of interactive Websites. If you use MySQL Databases in your websites then you should always make backup of your data regularly to recover it in case of loss.
Here are 10 methods to automatically or manually backup MySQL databases. Check them out and pick the one which is best suited to you.
1. Backing Up Using MySQLDump
Backup of data can be made using mysqldump utility that comes with MySQL. Various examples are given using mysqldump, including the backing up your database to a file, another server, and even a compressed gzip file.
2. MySQL Export: How to Backup Your MySQL Database?
You can backup by making a dump file (export / backup) of a database used by your account. To do so you have to head over to phpMyAdmin tool in your cPanel.
3. Automatically Backup Mysql Database to Amazon S3
You can also use Amazon S3 to backup your mysql databases. An automated script is present here, which takes backup of a mysql database and then moves it to Amazon S3.
4. How to Backup MySQL Databases, Web Server Files to an FTP Server Automatically
It is an easy way to backup data for users who run their own web server and MySQL server on a dedicated box or VPS. The best thing when using FTP or NAS backup is the fact that your data is secure. First of all you have to backup every single database with mysqldump command, Automating tasks of backup with tar, Setup a cron job and create FTP backup script.
5. How to E-Mail Yourself an Automatic Backup of Your MySQL Database Table with PHP
It will deliver an e-mail to you with an .sql file attached, which lets you to back up particular tables easily. You can even create an e-mail account specifically to get these backup.
6. How to Backup MySQL Database Using PHP
Carry out a database backup query from PHP file. In order to restore the backup all you have to do is to run LOAD DATA INFILE query.
7. Backup Your Database Into an XML File By Using PHP
It will display a PHP snippet that outputs your database as XML. XML is not one of the easiest format to restore a table but it is definitely easier to read.
8. Backup MySQL Database Through SSH
SSH can be used to backup large MySQL data. You will have to enable shell access in your cPanel or Plesk control panel and utilize a tool like PuTTY to log into your server through SSH.
9. How to Backup MySQL Database Automatically (For Linux Users)
If you are a Linux user you can backup MySQL Database automatically by using cron. “cron” is a time-based scheduling utility in Unix/Linux OS.
10. Ubuntu Linux Backup MySQL Server Shell Script
You can backup all your MySQL server databases to your ftp server, if you have a dedicated VPS server with Ubuntu Linux.
You are welcome to share any more methods in comments below.
To backup automatically a MySQL DB, you can simply add a small script in /etc/logrotate.d like this one:
/usr/bin/mysqldump –defaults-extra-file=/etc/mysqlbackup.cnf –opt –flush-logs –all-databases > /var/log/mysql/dbdump.sql
and create /etc/mysqlbackup.cnf (don’t forget to chmod it 600) containing something like
password = aDm1Npa$swD
This way you’ll get a 14-day rolling backup of all your databases.