Syncrify » Knowledge base

Document information

Document ID:2060
Subject:Plugin for MySQL database
Creation date:5/22/12 9:56 AM
Last modified on:6/3/13 1:30 PM


Plugin for MySQL Server

Syncrify can efficiently backup and restore a running instance of MySQL server. This page demonstrate how to configure the MySQL plugin in Syncrify. It also talks about some pre-requisites and other important information to keep in mind.

Prerequisites

  • MySQL Server and Syncrify client must be on the same machine
  • Permissions on MySQL must be configured correctly to allow localhost to connect to the server.
  • User that runs backup must have access to the database. Click here for instructions.

Background Info

Backups to MySQL server is run in two steps:
  • First, Syncrify issues a SELECT * INTO OUTFILE commands against every table, which creates a local files containing a backup of the database.
  • Syncrify client then copy these files to a remote machine
  • Syncrify creates two script files during backup:
    1. schema.sql - contains the schema of the entire database necessary to recreate it
    2. restoreDB.sql - contains LOAD DATA INFILE statements necessary to populate tables.

Configuration

  • Start Syncrify Client, and select your desired profile.
  • Click the right mouse button on the left hand side.
  • This invokes the following screen

    Field Definition

    Friendly name:A name that identifies this backup entry. This name is used as part of the folder to store the backup on the server. Therefore, this name must conform to the rules used to specify file names. For example, you cannot have \ / : * ? " < > | characters.
    SQL Server host:This MUST be localhost. You cannot backup a MySQL server running on a different machine.
    TCP/IP Port:Refers to the TCP/IP port where server is running. The default value is 3306.
    Login:A valid user in MySQL server that has permission to backup
    Password:User's password
    Database:Database name in MySQL server. Although you can backup any database, including mysql and test, you cannot use Syncrify to automatically restore a mysql database. See below to see what is an automatic restore
    Temp file path:This is the location where Syncrify creates a backup of the database on the local machine. 
    Auto RestoreUnintentional restores in a database can be dangerous. This checkbox provides a safeguard against such intentional restores. When this box is not checked, a restore will only cause the .BAK and .SQL files to get downloaded from the remote Syncrify server to client. An actual database restore won't occur. A DBA can then manually restore the database from the .BAK if needed.

    When this box is checked, besides restoring the .BAK file, Syncrify will run execute commands in schema.sql and restoreDB.sql file, which does the following:

    • Drops existing database
    • Creates necessary tables, views, stored procedures and triggers
    • Populates tables

    When this box is not checked, Syncrify will restore neccessary files to restore the database, but won't run the SQL statements, allowing more control to the administrator who can run SQL statements selectively. This is a useful option if you want to restore just one table rather than the entire database.
    Retain work files:Backing up MySQL Server is a two-step process. First, Syncrify runs a SELECT * INTO OUTFILE command against MySQL Server. This creates a .BAK file on the client machine contain a backup. Syncrify then backs up this local file to the remote machine in the second step.

    When this option is checked, Syncrify won't delete the .BAK file that is created in the first step. If you have enough hard-disk space to hold this temporary files, we recommend you leave this option checked.

Directions

The Direction option in Syncrify makes this plugin for MySQL Server acts differently. Following table describes this behaviour.
Client to ServerThis is the default backup direction. Syncrify create .BAK files, which are then transferred to the remote machine.
Server to ClientThis is like a restore. Syncrify pulls the .BAK file from the server and if Auto Restore is checked, runs schema.sql and restoreDB.sql scripts against the database.
Two-way syncWe discourage using two-way sync when using this plugin. Since a .BAK file is created on demand, Syncrify will always end-up pushing the local copy to the server. This can create unwanted results.
Manual restoration
When Auto Restore option is turned off, your must manually restore a database. This includes running SQL scripts in
  • schema.sql
  • restoreDB.sql
You can use any querying tool to run these scripts. We recommend you use WinSQL.

Query Terminator
Both script files (schema.sql and restoreDB.sql) contain several SQL statements. These statements are terminated by the word go on a line by itself. If you use a tool other than WinSQL, you will have to change this query terminator to a value that is understood by that tool.


User comments

Posted by James Arber on 6/10/12 7:15 AM

for those looking for the tags needed on a linux profile. hwere they are <plugins> <plugin pid="3"> <MySQLServer> <friendlyName>Examplename</friendlyName> <databaseName>exampleDB</databaseName> <fileLocation>\opt\Syncrify\temp</fileLocation> <sqlserverHost>localhost</sqlserverHost> <sqlserverPort>3306</sqlserverPort> <dbUser>root</dbUser> <dbPwd encrypted="true">Cryptedpass</dbPwd> <!-- not shure how its crypted, i used a windows box to gen the config --> <autoRestore>false</autoRestore> <enabled>true</enabled> <retainWorkFiles>true</retainWorkFiles> </MySQLServer> </plugin> </plugins>


Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users? Please add it below. Your name and tip will appear at the end of the document text.
Your name:
Your email:
Hide my email address
Verification code:
Enter the verification code you see above more submitting your tip
Tip:Please limit tips to 1000 characters

Navigation

Social Media

Powered by 10MinutesWeb.com