How to Automatically Backup MySQL Database
Do you have to backup the very important business data or online data from MySQL database minutely, hourly, daily, weekly, monthly or yearly? If you have some online websites, web applications or systems base on MySQL database, you have to make some backups every day and night for avoiding data-loss.
Do you still make the backups from MySQL database by your hands every time and have to give up all of holidays? You need a solution can be used to automatically backup online MySQL database on schedule with Hot Backup ( The online website / system and MySQL database can be accessed during backing up).
It's very easy to make some automatic backup and restoring tasks for MySQL with Auto Backup for MySQL Professional Edition, a professional MySQL backup software.
First of all, please free download Auto Backup for MySQL Professional Edition
And then please install the software on your computer with Windows 7, Vista, XP, 2008 Server, 2003 Server or 2000. The software can be used to backup MySQL database from the remote server runs under Any Operating Systems, including Unix, Linux, Mac OS, Windows and all. You can install the software on the MySQL server or a client computer. It's very easy to install the software, you just need to follow some steps by an installation wizard.
After installing, please start up Auto Backup for MySQL Professional Edition, choose the menu Task -> New Task to create a new task for automatically backing up or restoring. An easy-to-use Task Wizard will help you to create a task.
Create a new task - You can create multiple scheduled task to automatically
Backup MySQL database to the SQL or PHP files, Backup MySQL database to the other server ( from master server to slave ), Backup one MySQL database to the other database ( transfer all of items from a MySQL database to another existent database, or create a new database for storing ), or Restore MySQL database from all of the backups. And there are two accessories in the software, you can Export the records from some MySQL tables and views to CSV, XML, HTML, TXT files, and automatically Execute MySQL script on the destination MySQL server.
Choose a task type and click on Create Task button to start up the Task Wizard, for example, we create a task to Backup MySQL database to the SQL or PHP files.
Step 1 - Enter task name and comment
Please enter an unique task name.
Step 2 - Choose or enter the source MySQL server information
Enter the host name or IP address, Port number, Username and Password of the source MySQL server. Please note that this is the information of the Source MySQL Server which will be backed up, is not the information of the Web Server or Destination Server.
Click New to add a new MySQL server information to the connection list. When you create task next time, you can choose a server in the connection list for avoiding duplicate input.
Click Test connection to connect to the source MySQL server and display the MySQL version.
Please grant the user account enough privileges to access the remote MySQL database (% or a specified IP address).
The software can automatically detect the Default Character Set of every database, and you can select an existent character set from the list, or enter a new one if it does not exist. The Auto-detect option is recommended. All MySQL character sets are supported, including UTF8, UCS2, LATIN1 and all.
Click Next to connect to the MySQL server and get all of information of databases and tables. It will take some time to getting these information, please wait some minutes if there are a lot of databases and tables on the MySQL server.
Step 3 - Choose databases and tables for backing up
The software lists the databases and tables which can be accessed by the user account, you can select some databases, tables or views which you would like to backup in the list. And the software can be used to backup MySQL Databases, Tables ( Table Structures and Records ), Views, Index, Stored Procedures, Triggers, Functions and Events.
All MySQL storage engines and table types are supported, including InnoDB, MyISAM, BDB and all.
All MySQL field types are supported, including BLOB, DATE, VARCHAR, DECIMAL and all.
If you do not have SHOW DATABASES privilege, please click the button Enter a database to entering the databases which will be backed up.
Backup all newly-created tables of all chosen databases: If there are some new tables are created in the chosen databases when the task running, the software will backup these new tables. If the option was not selected, these new tables will be ignored.
Backup all newly-created databases on the MySQL server: If there are some new databases with some tables are created on the MySQL server when the task running, the software will backup these new database. If the option was not selected, these new databases will be ignored.
The software can be used to backup and restore the unlimited size databases (greater than 100GB).
Click Advanced Settings to set the advanced options of the task, for example, Flush Tables, Lock Tables, Use REPLACE statements, InnoDB options, Use multiple-row INSERT syntax, HEX codes, etc.
Step 4 - Backup files settings
Please select a folder or file for storing the backup files.
Backup data to the new file(s) every time: Create the new files to storing the data when backing up every time, the old files will not be overwritten.
Always backup data to the same file: Select a file and always store the backup data to the same file, overwrite the old file every time.
Click Settings to set the advanced options of the backup files.
One file contains all selected databases and tables: The software will backup all of selected MySQL databases and tables into one file if the option is selected.
Each file contains a selected database: The software will backup all of selected MySQL databases into multiple files, each file contains one selected database if the option is selected.
Each file contains a selected table: The software will backup all of selected MySQL tables into multiple files, each file contains one selected table if the option is selected.
File Name Format contains some MACRO variables:
%HOST% - This variable contains the host name or IP address of the source MySQL server.
%DATABASE% - This variable contains the name of the database, from which the backup copy is being made.
%TABLE% - This variable contains the name of the table, from which the backup copy is being made.
%DATE% - This variable contains the date of start of the backup process in the "YYYY-MM-DD" format.
%TIME% - This variable contains the time of start of the backup process in the "HH-MM-SS" format.
The default value of the file name format is recommended.
The software can create a new folder for storing the backup files each time.
The software can automatically delete the older backup files based upon dates or the number of backups.
Step 5 - Compression Options
Compress a SQL backup file to a GZIP (*.gz) file for decreasing disk usage.
Lowest level compression gives best speed and lowest compression rate.
Highest level compression gives best compression rate and slowest speed.
The Default level is recommended.
Step 6 - Buffer Mode Settings
Use buffer mode to speed up the task.
The records will be stored in the buffer and then write the buffer to files on hard disk.
Step 7 - Task Scheduler
Task Scheduler is used to schedule the task automatically backup or restore MySQL database Minutely ( Time Interval ), Hourly ( Time Interval ), Daily ( Every Day ), Weekly ( Every Week ), Monthly ( Every Month ) or on Special Date.
Step 8 - View task information and click Finish to create a new task
Main Window of Auto Backp for MySQL Professional Edition
The software will work for the MySQL database backup automatically as an agent.
You can run the program as a Windows Service and you don't need to log in Windows for automatic backing up.
There is a powerful backup files manager in the software. You can select any one of the backups and Open Folder of it or Create a Restoring task easily.
You will find more and more functions and features in the software.
Please free download Auto Backup for MySQL Professional Edition
Click here to learn more about How to Automatically Backup MySQL Database from Master Server to Slave Server