Home Print  

Setting up MySQL on Windows as Backend Database

Overview

now supports MySQL as a backend database in Backup Server and Replication Server installations to store all the clients' backup metadata information. Unlike the previous versions where Server installations used SQLite embedded database as its backend database, the MySQL database Server is external to the backup server application. Using a client-server database as the backend database rather than an embedded database provides better scalability and performance. It also enables third party applications to directly query 's metadata for integration with those applications. To get to work with MySQL as a backend database, you need to install MySQL Server either in the same machine where backup server is installed or in another dedicated machine where you wish to store the 's metadata information

Note : The following steps are also applicable for Replication Server using the MySQL database as a backend database.

Installing MySQL Server on Windows

Install MySQL Server 5.1 in the machine where you want to store the Backup Server's metadata.

  1. Download MySQL 5.1 from the URL
    MySQL For 32-bit Windows
    MySQL for 64-bit Windows

  2. Run the downloaded msi file


  3. Choose the Setup type to be 'Custom'


  4. Click on MySQL Server and then click on the Change button to change the installation path


  5. Enter the location where MySQL should installed

    NOTE: 's metadata stored in the MySQL databases could use up certain amount of space. To give a rough estimation of the usage, if the backup server stores 1 million backed up files, then the MySQL DB could be of size 1.5 GB. For this disk size estimation, each version file is also considered as a separate file. Therefore, for example, if a client is backing up 1 million files and for each file there are 3 versions stored in the backup server then there will be 3 million backed up files for that client machine. For these 3 million backed up files, the MySQL DB size could be about 4.5 GB. Please make sure that you have enough space in this location for all future backups from all the client machines.



  6. Click on the MySQL Server Datafiles and click on the Change button to change the location of the MySQL data files should be stored


  7. Enter the location where MySQL data files should stored


  8. Confirm the path settings by clicking on the Install button


  9. Installation will begin and it might take a few minutes to complete depending upon the speed of the machine.


  10. Once the installation is complete, select Configure the MySQL Server now option and click on Finish


For more information of installing MySQL, you can check out the following online documents:

Configuring MySQL Database Server

  1. Launch the MySQL Configuration Wizard . It should start automatically once the MySQL installation is complete. You can also start it from Start -> All Programs -> MySQL


  2. Choose the Configuration Type as 'Detailed Configuration'

  3. Choose the Server Type as 'Server Machine'

  4. Choose the database usage as 'Transactional Database Only'

  5. Select the innoDB path from the drop down. You should have enough space in this location for MySQL to store all the metadata of the backup data. The size of the metadata will be proportional to the amount of backup data. Therefore make sure you have enough space in this location for all your future needs.

  6. Choose the Concurrent Connections as 'Online Transaction Processing (OLTP)' so that several concurrent DB connections can be created from the backup server to handle multiple concurrent backups from the client machines.

  7. In the Networking Option, make sure 'Enable Strict Mode' is selected

  8. Select 'Standard Character Set'

  9. Select 'Install as Windows Service' option

  10. Enter the password for the root user that you would like to set for MySQL

  11. Proceed with the configuration to complete the MySQL configuration

  12. Make sure the execution completes successfully. If the MySQL port (default: 3306) is inaccessible either due to a firewall or it is occupied by another application, then you will get an error alert.

  13. Open the <MySQL_HOME>\my.ini in a text editor and add the following attributes at the end of the file:
  14. [mysqld]
    slow_query_log = 1
    innodb_log_file_size = 512M
    wait_timeout=604800

  15. Go to the <MYSQL_HOME>\data folder and rename the existing ib_logfile0 and ib_logfile1 files to some other name.

  16. Restart MySQL Server.

Creating Database

Create a separate database for in the MySQL server.

  1. Make sure MySQL is already running by checking the Mysql Service from Service Console. To open the service console, execute 'service.msc' in Start -> Run. If MySQL service is not running then start the MySQL service.

  2. To create the database, open the MySQL console by 'Start -> All Programs -> MySQL -> MySQL Server 5.1 -> MySQL Command Line Client'

  3. Enter the username as root and the MySQL password that you had entered during the MySQL Database Server configuration.

  4. Now you will see the mysql> prompt. Create the database by executing 'create database [database name] collate latin1_general_cs;' where [database name] is the name of the database. Ex : 'create database DB collate latin1_general_cs;'

Note : Make sure the database name does not exceed 30 characters.

Installing MySQL Connector/ODBC

After installing MySQL successfully you have to install MySQL Connector/ODBC. Please download the MySQL Connector/ODBC 3.51 from the URL : http://dev.mysql.com/downloads/connector/odbc/3.51.html. If your Backup Server/Replication Server uses 64 bit Operating System of Windows, then you need to download and install both the 32 bit MySQL ODBC connector and the 64 bit MySQL ODBC connector.

Instructions for installing the MySQL 32-bit ODBC Connector (required for both 32-bit/64-bit Windows machines):

  1. Run the downloaded msi file

  2. Choose the installation type as Typical

  3. Click Install to start the installation of the connector

  4. Make sure the installation completes successfully


Instructions for installing the MySQL ODBC 64-bit Connector (required for 64-bit Windows machines):
  1. Unzip the downloaded zip file of 64-bit MySQL Connector/ODBC 3.51.
  2. Open a command line window and cd to the location where the unzipped files are stored.
  3. run the command

    Install.bat 0

Configuring the MySQL Connector

Configure the MySQL Connector in the backup server as follows :

  1. Open up the "Control Panel -> Administrative Tools -> DataSources (ODBC)" tool.

  2. Go to the 'Connection Pooling' tab. Scroll down and select 'MySQL ODBC 3.51 Driver' and double click on 'MySQL ODBC 3.51 Driver'.

  3. You have to set the connection pooling attribute, hence select the option 'Pool Connections to this driver' and enter 300 seconds for connection pooling.

Create System DSN

Create a system DSN in the backup server to access the database in the MySQL Server.

  1. In the ODBC Data Source, go to the tab 'System DSN' and click on 'Add' button.

  2. Scroll down and select 'MySQL ODBC 3.51 Driver' and finish.

  3. Enter the Data Source Name as 'sgserver'.

  4. Leave description as empty or enter Database, it is optional.
  5. Enter Server [the dns name of the machine in which mysql is installed]'. If you have installed MySQL server in the backup server itself, then you can specify it as 'localhost'
  6. User as 'root'.
  7. Password as entered in the MySQL Database Server Configuration.
  8. If your ODBC Driver is successfully connected to your MySQL Server then it will list the database 'DB' in which we are going to store the meta data information.
  9. Select the database as 'DB' from the drop down menu.

If you are running 64 bit version of the Windows operating system then you have to create System DSN using 32-bit MySQL ODBC Connector for the WebServices' access to the database.

  1. During the above MySQL ODBC connector installation, please install 32 bit MySQL ODBC connector also.
  2. Open 32 bit MySQL ODBC connector from the 32-bit version of the ODBC Datasources by running the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder.

  3. Please follow the above steps to create System DSN using configure 32 bit ODBC connector in your 64 bit system.

Testing the connectivity with MySQL Server

After creating the MySQL driver and DSN in the backup server, check the connectivity from backup server to the MySQL Server

  1. Open up the "Control Panel -> Administrative Tools -> DataSources (ODBC)" tool in the backup server machine.
  2. Go to the tab 'System DSN' and select the Data Source Name.
  3. Click on the 'Configure' button.
  4. Verify or change the valid values in the connector and click on the 'Test' button. If there is any problem click on 'Diagnostics' button to get the exact error message.

Specify the attributes during installation

After creating the DSN in the backup server, you need to specify the following value during the installation in the backup server. Please specify DSN as 'sgserver', database name as 'DB', user name as 'root' and password(entered during the MySQL installation).

Troubleshooting Tips

is not started (or) repeatedly crashes after startup Show/Hide

Solution : Stop application and start in command line by executing 'bin\StoreGrid.exe' from [_Home] folder. Note the console print from the terminal. Please follow the solutions when the console print are as follows :


  1. Can't connect to local MySQL server...

    Make sure that the MySQL server is running in the machine where MySQL is installed. Execute 'netstat -an | grep 3306' (for linux) or 'netstat -an | find "3306"' (for Windows) in the machine where MySQL server is running and check if the MySQL port 3306 (default mysql port) is in LISTEN state. If MySQL is not running, please start the MySQL Server and then try starting .

  2. Data source name not found...

    Make sure that you have given the correct DSN name during the installation.
    1. Open up the "Control Panel -> Administrative Tools -> DataSources (ODBC)" tool.
    2. Go to the tab 'System DSN' and select the Data Source Name.
    3. Click on the 'Configure' button.
    4. Click on the 'Test' button. If there is any problem click on 'Diagnostics' button to get the exact error message.

  3. Access denied for user...

    Check if you have given the correct user name and password for the MySQL Server during installation.
    1. Open up the "Control Panel -> Administrative Tools -> DataSources (ODBC)" tool.
    2. Go to the tab 'System DSN' and select the Data Source Name.
    3. Click on the 'Configure' button.
    4. Click on the 'Test' button. If there is any problem click on 'Diagnostics' button to get the exact error message.

  4. Host 'xxxxx' is not allowed to connect to this MySQL server...

    Provide the required permission in the MySQL to allow the backup server to connect the database. You can do this by executing 'GRANT ALL PRIVILEGES ON *.* TO '[user name]'@'[backup server machine name]' IDENTIFIED BY '[password]' WITH GRANT OPTION;' in the MySQL prompt in the machine where MySQL Server is installed.

    NOTE: If you are deploying the backup servers in a cluster, you need to execute the above command for each of the backup server in the cluster replacing the 'backup server machine name' with the machine name of the backup server. If you find it cumbersome to execute this for each backup server, you could replace the 'backup server machine name' with '%' like:
    'GRANT ALL PRIVILEGES ON *.* TO '[user name]'@'%' IDENTIFIED BY '[password]' WITH GRANT OPTION;'
Print  
Technical support-