PlanetMysql.ru — информация о СУБД MySQL

Protecting information in databases and possibility to restore databases in case of need is the highest priority task in many companies. But not all DBMSs have built-in tools for data protection (tools to backup and restore databases). And MySQL is one of such DBMSs.

Making database backups is one of the most important things in the process of administrating MySQL databases, because some critical data loss can be irreplaceable.

The task of making daily mysql backup can be solved with the help of the backup database function of dbForge Studio for MySQL. To use it, you should setup backup in the wizard manually and schedule making backups.To open the Database Backup wizard, you should choose Database → Backup Database from the main menu.

Making a MySQL database backup. Saving backup project.

On the first page of the wizard we should choose the database connection and the database we should backup. Also we should set the path where the backup files of MySQL databases will be saved.

When you are making daily database backups you should delete old irrelevant backups to optimize disk space usage and to store only a specified number of relevant backups of MySQL databases. It’s very easy to delete old backups automatically – you should only check the Auto delete old files option and set the number of backups that should be stored. In case when a scheduled backup was created successfully and the actual number of backups exceeds the set number, older backup files will be deleted.

To add the resulting file backup to an archive, check the Use compression (ZIP) option. In this case you can add comments to the archive.

On the Error Handling page of the wizard we should set the path and the name for a file into which the backup process of a MySQL database will be logged.

After performing all necessary settings press the Backup button and wait until the application finishes export.

Press the Save Project button on the Finish page of the wizard, and as a result the backup project file in which options from all wizard pages are stored will be saved (for example, sakila.backup).

Creating a MySQL database backup via Command Line. Create a scheduled MySQL database backup using Windows Scheduled Tasks.

If you are using the Command Line mode and a backup project file, you can make a MySQL database backup easily and automate daily backup making using Windows Scheduled Tasks.

Below is an example of the command line in the result of executing which a MySQL database backup will be created basing on the project saved before.

Command line:

“D:\Program Files\Devart\dbForge Studio for MySQL\dbforgemysql.com” /backup /projectfile:”d:\MyBackups\sakila.backup”

To make backups of a MySQL database every day let’s create a scheduled task.

To make our example clear, let’s create a dailybackup.cmd file with the following content:

call “%programfiles%\Devart\dbForge Studio for MySQL\dbforgemysql.com” /backup /projectfile:”d:\MyBackups\sakila.backup”

%programfiles% – is an environment variable that returns full path to the Program Files system folder.

• Open Control Panel -> Scheduled Tasks and select Add Scheduled Tasks
• Select the created dailybackup.cmd file in the wizard

• Set the task schedule

To avoid data loss you should make backups of all active databases regularly. If you have a backup, you will be able to restore the whole database or some of its objects easily.

dbForge Studio for MySQL allows to perform database backups of MySQL databases as SQL scenarios and supports making backups from Command Line, that allows to automate tedious work connected with making backups of MySQL databases regularly.

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL is frequently referred to as a database for Web applications. Partially it is really so, because MySQL became popular owing to its simplicity, high speed, and bounding with PHP. Developers of small Web projects often choose MySQL as a back end of their sites. Does this mean that MySQL can be used only for small databases? Not at all. There are lots of databases size of data in which is measured in gigabytes. Besides MySQL servers are frequently clustered to increase their performance. When a DBAs work with large amounts of data, they frequently have to make backup copies correctly and effectively, i. e. to export MySQL databases to SQL (or MySQL backup). It is exteremely important to import MySQL database from SQL correctly is when restoring a corrupted database and when migrating a database from one server to another.

What should be taken into account when exporting a large MySQL database?

Making a backup copy of a large database takes a lot of time. During this time some user can try to modify data in this database. But we want to get an all-of-a-piece database snapshot. For different tables this can be done in different ways:

• for InnoDB tables a separate transaction should be started
• MyISAM tables should be locked by FLUSH TABLES WITH READ LOCK

Except taking a lot of time export and import of a MySQL database implies transferring a large amount of data between client and server. The most effective way of reducing the amount of data transferred through the net and the amount of the used disk space is compression. All points mentioned above are taken into account in the utility for making database backups included in the set of tools available in dbForge Studio for MySQL. The rest of the article is written referring to this utility.

Step-by-step MySQL backup procedure

1. Set compression for the connection

Using compression for a connection allows to reduce net traffic owing to strings compression. The Use Compression option is available on the Advanced page of the Database Connection Properties dialog. But if the amount of string data per record is not large, you won’t gain much this way.

2. Choose a database for export and open Database Backup Wizard

In the Database Explorer tree choose the nod of the needed database or connection. Choose Backup Database from the pop-up menu. On the image below we show the main settings of MySQL backup. Pay your attention to the fact that we are using compression to reduce the disk space usage. Besides using compression allows to add comments to the backup.

3. Set options to create an all-of-a-piece database snapshot

Earlier in this article we wrote about the necessity of avoiding data corruption while creating a database backup. There are options to achieve this, and they are marked red on the image below:

4. Make a backup of your MySQL database

After performing all necessary settings press the Backup button and wait until the application finishes export. During the operation you will be able to see the progress of backup creation and its stages.

Step-by-step MySQL import procedure

So, we’ve performed database export and compressed the results into a ZIP-archive. And now we have to import this database on the new server. Will we have to decompress a large script and try to execute it manually? Not at all. Database Import can be performed neatly and easily with the help of Database Restore Wizard

1. Choose a database to import and open Database Restore Wizard

In the Database Explorer tree choose the nod of the needed database or connection. Choose Restore Database from the pop-up menu. Choose the backup file in the window that opened. After you’ve chosen the file you should see approximately the same as shown on the image below:

The program finds the *.sql file in the archive automatically and shows the comment we’ve added when creating the backup. Now we are ready to import our MySQL database.

2. Import the database

After pressing the Restore button the program will automatically restore the database from the backup.

Conclusion

In this article we’ve reviewed some aspects of making backups of MySQL databases and illustrated capabilities of dbForge Studio for MySQL regarding export/import. You can download dbForge Studio for MySQL here.

PlanetMySQL Voting: Vote UP / Vote DOWN