MySQL backup
There are many paths you can take to create a MySQL backup. However, no matter which application, control panel tool, or SSH script you use, all of the backups will fit into two types of backups: a dump or raw backup.
MySQL Dump
A MySQL dump is a bit slower than a raw backup because it creates all the SQL queries required to create the tables of that database, as well as all the insert queries required to place the information back into the database's tables.
If you want to perform the mysql dump manually, without the assistance of your hosts control panel, then run SSH to your web server and do the following
mysqldump --tab=/path/to/some/dir --opt db_name
If you were to open up a MySQL dump file you would see a slew of SQL queries that you would probably be able to understand (if you've already read through this whole tutorial!).
MySQL Raw Backup
A MySQL Raw Backup is quicker because it does not translate the contents of the database into human readable SQL queries. However, not many control panels support this type of backup, so do not worry if your hosting provider doesn't have this option set up for you.
MySQL Backup in Control Panel cPanel
Cpanel is the most widely used web host control panel at this time, so we thought it would make sense to provide a walkthrough specifically for cPanel.
From the application selection screen click "Backup". This will bring you to the backup application that allows you to generate and download complete backups for your site.
To back up a database individually, look for the title "Download a SQL Database Backup" or something similar. Below that title should be a listing of every database that you have created. Simply click on the name of the database you want to backup and save it to your computer.
That's it! Now just be sure that you have a regular backup schedule, just in case the unthinkable happens and your web host loses all your database information!
Zmanda Recovery Manager for MySQL
Zmanda Recovery Manager (ZRM) for MySQL simplifies the life of a Database Administrator who needs an easy-to-use yet flexible and robust backup and recovery solution for MySQL server. With ZRM for MySQL you can:
Schedule full and incremental backups of your MySQL database.
Start immediate backup or postpone scheduled backups based on thresholds defined by you.
Choose to do more flexible logical or faster raw backups of your database.
Perform backup that is the best match for your storage engine and your MySQL configuration.
Backup your remote MySQL database through a firewall.
Configure on-the-fly compression and/or encryption of your MySQL backups to meet your storage and security needs.
Get e-mail notification about the status of your backups and receive MySQL backup reports via RSS feed.
Monitor and browse your backups.
Define retention policies and delete backups that have expired.
Recover a database easily to any point in time or to any particular transaction, e.g. just before a user made an error.
Parse binary logs to search and filter MySQL logs for operational and security reasons.
Functionality and Benefits of Zmanda Recovery Manager(ZRM) for MySQL
Which versions of MySQL can I backup?
MySQL 4.1.x and MySQL 5.x
Which storage engines can I backup?
ZRM for MySQL works with any storage engine.
Is there a GUI?
ZRM Enterprise Edition provides Management Console for MySQL backup, a web based service integrated with Zmanda Network for all day-to-day backup activities of a MySQL DBA.
How does ZRM backup MySQL?
Backups are done via mysqldump, various snapshots, mysqlhotcopy or MySQL replication. Depending on your configuration of MySQL, ZRM selects the best way to do backups, but you can override and select yourself what tool to use.
Can I do hot backup of online database?
ZRM provides hot backup via snapshots. Currently we support Linux, Windows VSS, Solaris ZFS, Veritas VxFS and Network Appliance SnapManager. Community Edition provides support for LVM and ZFS snapshots only.
Does ZRM for MySQL use a proprietary data format?
Since ZRM for MySQL does not use any proprietary data formats, you can recover your data even without ZRM.
What kind of backup reports will I get?
You will receive e-mail about the success or failure of each backup. Additionally, you will get information about the amount of backup data and the time taken to run a backup. You can add your own comments about each backup job to make it easy to find the right backup version of your database for recovery.
ZRM for MySQL also provides reports about the state of MySQL during backups, e.g. how much time it took to flush memory buffers before the snapshot was taken.
Formatting for backup reports is flexible. For example you can receive reports automatically via RSS feed making it easier to consolidate reports for a dashboard or any monitoring application.
ZRM Enterprise Edition provides 9 predefined reports and allows to customize reporting on over 30 items.
Can I do backups over the Internet?
Using SSH copy or socket copy plug-in you can do either secure or not secure but more efficient backups over the Internet.
Some of my backups are done via LVM snapshots and others are done via mysqlhotcopy. Will I need different tools for recovery?
ZRM for MySQL provides a unified recovery mechanism for any technology used for backup. It makes it easy to recover databases that were backed up differently, e.g. when you have both logical and raw backups.
Do I have to replace my existing network backup with ZRM for MySQL?
ZRM for MySQL is not a replacement for your existing network backup. It is a complementary solution for backup and recovery of MySQL only, and it can co-exist with your existing Amanda Enterprise, IBM Tivoli, Symantec NetBackup, EMC NetWorker or any other network backup software.
To what point in time can I recover my database?
With ZRM for MySQL you can recover your database to any point in timeor to any particular transaction between two successful backups. For example, you can recover the database to a moment in time just before a user made an error.
What performance can I get for backup and recovery with ZRM?
Raw backup and recoveries are much faster than logical ones. Register on Zmanda network to read the white paper "Backup and recovery benchmarks for MySQL with ZRM" and see how ZRM accelerates performance for InnoDB and MyISAM storage engines.
Can I backup MySQL Cluster with ZRM?
zrm cluster demo
ZRM Cluster Edition supports backup of MySQL Cluster and MySQL Cluster Carrier-Grade Edition. ZRM consolidates backup data dispersed among all cluster nodes to a single system making it easier to recover data in case of emergency.
Where can I get ZRM?
You can download the Community Edition of ZRM for MySQL here.
Documentation, Discussion Forums, and Mailing List for ZRM Community
Documentation is available at ZRM for MySQL wiki. Please use Zmanda Forums for discussions. We also provide a ZRM for MySQL users mailing list and an announcement mailing list as well as a searchable archive for the users list.
ZRM Best Practices
How to backup over the Internet a remote MySQL server with Zmanda Recovery Manager. This document describes what to consider and how to configure ZRM for secure backup and recovery of remote MySQL server for common use cases including logical and raw backups.
How to setup and verify a backup solution for MySQL in 15 minutes - all using open source software! This document describes how to install, configure, use and verify a fast and reliable solution for MySQL backup.
Top 5 considerations for backup and recovery of MySQL. A well thought through backup plan and configuration will go a long way to ensure that you can recover your database - when a system or user error deletes important data stored in MySQL - without impacting your business.
There are many paths you can take to create a MySQL backup. However, no matter which application, control panel tool, or SSH script you use, all of the backups will fit into two types of backups: a dump or raw backup.
MySQL Dump
A MySQL dump is a bit slower than a raw backup because it creates all the SQL queries required to create the tables of that database, as well as all the insert queries required to place the information back into the database's tables.
If you want to perform the mysql dump manually, without the assistance of your hosts control panel, then run SSH to your web server and do the following
mysqldump --tab=/path/to/some/dir --opt db_name
If you were to open up a MySQL dump file you would see a slew of SQL queries that you would probably be able to understand (if you've already read through this whole tutorial!).
MySQL Raw Backup
A MySQL Raw Backup is quicker because it does not translate the contents of the database into human readable SQL queries. However, not many control panels support this type of backup, so do not worry if your hosting provider doesn't have this option set up for you.
MySQL Backup in Control Panel cPanel
Cpanel is the most widely used web host control panel at this time, so we thought it would make sense to provide a walkthrough specifically for cPanel.
From the application selection screen click "Backup". This will bring you to the backup application that allows you to generate and download complete backups for your site.
To back up a database individually, look for the title "Download a SQL Database Backup" or something similar. Below that title should be a listing of every database that you have created. Simply click on the name of the database you want to backup and save it to your computer.
That's it! Now just be sure that you have a regular backup schedule, just in case the unthinkable happens and your web host loses all your database information!
Zmanda Recovery Manager for MySQL
Zmanda Recovery Manager (ZRM) for MySQL simplifies the life of a Database Administrator who needs an easy-to-use yet flexible and robust backup and recovery solution for MySQL server. With ZRM for MySQL you can:
Schedule full and incremental backups of your MySQL database.
Start immediate backup or postpone scheduled backups based on thresholds defined by you.
Choose to do more flexible logical or faster raw backups of your database.
Perform backup that is the best match for your storage engine and your MySQL configuration.
Backup your remote MySQL database through a firewall.
Configure on-the-fly compression and/or encryption of your MySQL backups to meet your storage and security needs.
Get e-mail notification about the status of your backups and receive MySQL backup reports via RSS feed.
Monitor and browse your backups.
Define retention policies and delete backups that have expired.
Recover a database easily to any point in time or to any particular transaction, e.g. just before a user made an error.
Parse binary logs to search and filter MySQL logs for operational and security reasons.
Functionality and Benefits of Zmanda Recovery Manager(ZRM) for MySQL
Which versions of MySQL can I backup?
MySQL 4.1.x and MySQL 5.x
Which storage engines can I backup?
ZRM for MySQL works with any storage engine.
Is there a GUI?
ZRM Enterprise Edition provides Management Console for MySQL backup, a web based service integrated with Zmanda Network for all day-to-day backup activities of a MySQL DBA.
How does ZRM backup MySQL?
Backups are done via mysqldump, various snapshots, mysqlhotcopy or MySQL replication. Depending on your configuration of MySQL, ZRM selects the best way to do backups, but you can override and select yourself what tool to use.
Can I do hot backup of online database?
ZRM provides hot backup via snapshots. Currently we support Linux, Windows VSS, Solaris ZFS, Veritas VxFS and Network Appliance SnapManager. Community Edition provides support for LVM and ZFS snapshots only.
Does ZRM for MySQL use a proprietary data format?
Since ZRM for MySQL does not use any proprietary data formats, you can recover your data even without ZRM.
What kind of backup reports will I get?
You will receive e-mail about the success or failure of each backup. Additionally, you will get information about the amount of backup data and the time taken to run a backup. You can add your own comments about each backup job to make it easy to find the right backup version of your database for recovery.
ZRM for MySQL also provides reports about the state of MySQL during backups, e.g. how much time it took to flush memory buffers before the snapshot was taken.
Formatting for backup reports is flexible. For example you can receive reports automatically via RSS feed making it easier to consolidate reports for a dashboard or any monitoring application.
ZRM Enterprise Edition provides 9 predefined reports and allows to customize reporting on over 30 items.
Can I do backups over the Internet?
Using SSH copy or socket copy plug-in you can do either secure or not secure but more efficient backups over the Internet.
Some of my backups are done via LVM snapshots and others are done via mysqlhotcopy. Will I need different tools for recovery?
ZRM for MySQL provides a unified recovery mechanism for any technology used for backup. It makes it easy to recover databases that were backed up differently, e.g. when you have both logical and raw backups.
Do I have to replace my existing network backup with ZRM for MySQL?
ZRM for MySQL is not a replacement for your existing network backup. It is a complementary solution for backup and recovery of MySQL only, and it can co-exist with your existing Amanda Enterprise, IBM Tivoli, Symantec NetBackup, EMC NetWorker or any other network backup software.
To what point in time can I recover my database?
With ZRM for MySQL you can recover your database to any point in timeor to any particular transaction between two successful backups. For example, you can recover the database to a moment in time just before a user made an error.
What performance can I get for backup and recovery with ZRM?
Raw backup and recoveries are much faster than logical ones. Register on Zmanda network to read the white paper "Backup and recovery benchmarks for MySQL with ZRM" and see how ZRM accelerates performance for InnoDB and MyISAM storage engines.
Can I backup MySQL Cluster with ZRM?
zrm cluster demo
ZRM Cluster Edition supports backup of MySQL Cluster and MySQL Cluster Carrier-Grade Edition. ZRM consolidates backup data dispersed among all cluster nodes to a single system making it easier to recover data in case of emergency.
Where can I get ZRM?
You can download the Community Edition of ZRM for MySQL here.
Documentation, Discussion Forums, and Mailing List for ZRM Community
Documentation is available at ZRM for MySQL wiki. Please use Zmanda Forums for discussions. We also provide a ZRM for MySQL users mailing list and an announcement mailing list as well as a searchable archive for the users list.
ZRM Best Practices
How to backup over the Internet a remote MySQL server with Zmanda Recovery Manager. This document describes what to consider and how to configure ZRM for secure backup and recovery of remote MySQL server for common use cases including logical and raw backups.
How to setup and verify a backup solution for MySQL in 15 minutes - all using open source software! This document describes how to install, configure, use and verify a fast and reliable solution for MySQL backup.
Top 5 considerations for backup and recovery of MySQL. A well thought through backup plan and configuration will go a long way to ensure that you can recover your database - when a system or user error deletes important data stored in MySQL - without impacting your business.
Looking super subbu.for better effectiveness put more information regarding commands of my sql
ReplyDeleteYou can read my other article "MySQL backup using shell script mysqldump utility" for command line utilities
Delete