MYSQL-TOOLS

Comparison of graphical tools for MySQL

Home Articles How To Daily mysql backup remote database(s) via SSH.
Monday, 25 October 2010 10:19

Daily mysql backup remote database(s) via SSH. Featured

Written by  Administrator
Rate this item
(4 votes)

In the world of 95% of web projects using RDBMS MySQL. Of course, every serious project suggests that periodically create a backup copy of all data used in this work. This is a de facto standard approach. Moreover, without this it is impossible to guarantee visitors uptime of your site. If the site provides commercial services, the availability of backup is simply is a necessity.

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.

Today would be desirable to tell a fairly simple and quite convenient way to automate the daily, weekly and monthly backups process of MySQL databases, which are located on a remote server.

Raw data:

MySQL database located on a remote server hosting provider. Access to MySQL database is permitted through the SSH tunnel.

Settings for the SSH server:

Schema ssh tunnel  remote access to MySQL database

  • SSH host name = ssh.webhosting.com
  • SSH user name = ssh_user_name
  • SSH password = password *****
  • SSH port = 22

Settings for the MySQL server:

  • Host = localhost
  • Port = 3306
  • Login = root
  • Password = password *****

EMS DB Extract for MySQL is an impressive and easy-to-use tool for creating MySQL database backups in a form of SQL scripts. This database script utility allows you to save metadata of all MySQL database objects as well as MySQL table data as database snapshot.

The latest version is available here:  http://www.sqlmanager.net/en/products/mysql/extract

Running EMS DB Extract wizard opens the backup database MySQL.

First Step. - Specify the settings for connecting to a server MySQL.

db Extract setup connection to database

 

To connect to remote MySQL server, select the "Connect througt the SHell (SSH) tunnel":

 

Setup  SSH tunnel

 

Second Step. - Select the database, which will be backup.

Step 2 select mysql  database for backup

Third Step. - Specify the object types for backup.

Allows you to choose to export only the structure or just the data or both structure and data.

You can also include or exclude groups of database objects: tables, views, procedures, UDF_s ... etc.

If necessary, you can select specific objects for DB backup.

Step 3 Types of objects to backup

 

Fourth Step. - Specify the script generation option scheme during the backup.

  1. This switch control allows you to specify the version of MySQL server the extracted metadata is to be compatible with: Server version, 3.23, 4.0, 4.1, 5.0, 5.1.
  2. Generates the DELETE FROM statements before the INSERT INTO statements.
  3. Here you can specify the WHERE clauses for data extraction from each of the tables.

Step 6 Setup options backup

 

Fifth Step. - At this step you should specify the file name and the directory where the result script will be saved.

  1. Check this option to add current date, time or datetime (can be specified at the corresponding drop-down list) to the folder name. (1,2,3)
  2. If compression in a single archive is enabled you can split the archive to volumes of defined size. The size of an archive volume is in bytes by default, kilobyte if the value is followed by 'K' and megabyte if the value is followed by 'M'. (4)

Specify file name and directory for extract

Sixth Step. - Specify the option to backup database. Scheduling options

  1. Choose here if the extraction task is to be run within the current session of the GUI wizard, or whether the task is to be scheduled for running later, or both.
  2. Set the Run once option to execute the extraction task one time, or select a  daily, a  weekly or a  monthly Recurrence Rule to repeat the extraction task periodically.
  3. This box allows you to set the time of the task execution.
  4. In these fields you must specify Windows Domain (if a domain login is being used), User name and User password for the task execution. It is recommended to run the task as current Windows user.

Sheduled daily backup wizard

 

After specifying all the parameters and options of the backup click Extract button which will result in running the backup database.

backup progress

 

The Save template item allows you to save current configuration for future use.

Please note that a configuration file (template) can be saved only on Step 8 (Scheduling options) and the succeeding steps of the wizard.

Save backup template

Previously saved DB Extract templates are loaded within the Open template dialog. To call this dialog, press the Tools button and select the Load template popup menu item.

Please note that you can reopen a template at any step of the wizard using the corresponding popup menu item of the Tools menu.

DB Extract for MySQL includes a graphical wizard that will guide you through the MySQL extract process step by step, and a command-line utility for creating MySQL backups in one-touch.

Last modified on Tuesday, 26 October 2010 05:58

Add comment


Security code
Refresh