Posts Tagged ‘MySQL’

Optimizing MassTransit Databases

Tuesday, September 15th, 2009

From time to time you may need to repair or optimize your MassTransit databases. You can do so easily by using the MySQL Administrator that came with your MySQL installer package.

Optimizing For Windows

  • Log in to the MySQL Administrator
  • Select Catalogs
  • Select the mtdatabase schema
  • Highlight the table(s) you wish to optimize

MySQL Admin - Catalogs

  • Click the Maintenance button at the bottom right
  • The Tables Maintenance box will pop up
  • Select Optimize Tables
  • Click Next
  • Click Optimize

Tables Maintenance

Once completed you will see a list of tables and what optimizations took place.

admin 3

Upgrading MySQL from 4.x to 5.0 – Mac OS X

Monday, September 10th, 2007

Summary:

MassTransit 5.1 introduces support for MySQL versions 5.0 and greater, which allows for faster performance, better management, enhanced data integrity, improved security and additional replication services. MySQL 5 is the default database for MassTransit 5.1 and later. Group Logic recommends that customers using previous versions upgrade to take full advantage of these added capabilities.

Description:

Please see the attached document for information on updating MySQL to version 5.0 for use with your MassTransit installation on Mac OS X.

Related Document:

Upgrading MySQL from 4.x to 5.0 – Microsoft Windows

Monday, September 10th, 2007

Summary:

MassTransit 5.1 and later introduce support for MySQL versions 5.0 and greater, which allows for faster performance, better management, enhanced data integrity, improved security and additional replication services. MySQL 5 is the default database for MassTransit 5.1 and later. Group Logic recommends that customers using previous versions upgrade to take full advantage of these added capabilities.

Description:

Please see the attached document for information on updating MySQL to version 5.0 for use with your MassTransit installation on Microsoft Windows.

Related Document:

MassTransit Database Backup Procedures for Windows

Tuesday, October 3rd, 2006

Summary:

This article describes how to configure and use the MySQL Administrator* application in order to automate the backup of MassTransit Professional, Enterprise,  Premier and Standard server databases.

Description:

The MySQL Administrator application is used to control and configure many aspects of the databases that MassTransit uses for file transfer operations. One of the more powerful and often overlooked features of the administrator is the ability to schedule automated backups of the MassTransit databases. At a specified interval, the Administrator will make complete copies of the MassTransit data, creating a .SQL file containing all database contents. In the event that database recovery is necessary, the contents of this .SQL file may be imported through a simple restoration process via the same Administrator tool.

Database Backup Process:

  1. Launch the MySQL Administrator tool.
  2. A dialog box will appear, prompting you to enter the Server Host, Username, and password.
    1. Server Host is usually localhost, or the IP address of the server that is running both MySQL Server and MassTransit Server.
    2. The username and password fields should be populated with the MassTransit MySQL user information that was specified at initial installation time.
  3. Once the MySQL Administrator tool has launched, click on the Backup option which appears in the left-hand window pane.
  4. In the right-hand window pane, three tabs should appear: Backup Project, Advanced Options, and Schedule.
  5. Make sure that the Backup Project tab is selected, and then click the New Project button located towards the bottom of the Administrator window.
  6. Once the MySQL Administrator tool has launched, click on the Backup option which appears in the left-hand window pane.
  7. Next, provide the Project Name field with a name for the MassTransit Database backup project. For example, MT_Backup.
  8. Then, under the Schemata column, select mtdatabase, which is your MassTransit data schema, followed by the > button.
  9. The mtdatabase schema should now appear to the left, within the Backup Content window.
  10. Next, select the Advanced Options tab, appearing in the right window pane.
  11. Select the Lock all tables option if it is not already selected.
  12. In order to make a backup of all tables, make sure that the Complete backup option is also selected.
  13. Within the Output File Options window, make sure that SQL Files is selected in the Backup Type pulldown menu.
  14. Now, select the Schedule tab, appearing in the right window pane, towards the top of the MySQL Administrator user interface.
  15. Click the checkbox next to Schedule this backup project
  16. If a warning dialog regarding password storage appears, click the Open Options button for additional configuration. If this dialog does not appear, skip to step 16.
    1. A configuration options window will appear.
    2. Select the General Options icon.
    3. Under password storage, select the checkbox next to Store Passwords.
    4. In the Password storage method pulldown, select Obscured.
    5. Click the Apply button to register your changes.
    6. Click the Close button to return to the backup configuration window.
  17. Within the Target folder field, select a directory in which your backup .SQL files are stored. NOTE: It is highly recommended that the location specified here be a drive or volume other than the volume that which contains your “live” MySQL data and MassTransit installation. This is to insure that backup data is not lost in the event of storage hardware failure.
  18. Within the Filename field, provide a name (i.e. MTBackup) for your database backup files.
  19. Under the Execution time pulldown, select the Execute backup daily option. If you do not wish to have daily backups of your MassTransit data, you may select another option.
  20. In the Time field, enter a time in which the scheduled backup will occur. Optimally, the time specified will be a time when the MassTransit server is in minimal production. Note: The time entered in this field must be in 24-hour or “military time” format.
  21. Select the Save Project button to retain the changes made to your new backup project.
  22. When the Account Information tab appears, provide the username and password under which this backup task will be executed. In most cases, this is the machine Administrator account, or another domain account with administrative privileges. Then, click the OK button to store this user account information.
  23. Optionally, you may wish to click the Execute Backup Now button, to confirm that a database backup has completed successfully.
  24. After successful configuration, the backup project that you have just created should appear under the Backup Projects column, in the left window pane.

* If you had not installed the MySQL Administrator tool during the initial MassTransit and MySQL installation, you may obtain the 5.1 version from the http://dev.mysql.com/downloads website.

Database Restoration Process:

  1. Launch the MySQL Administrator tool.
  2. A dialog box will appear, prompting you to enter the Server Host, Username, and password.
    1. Server Host is usually localhost, or the IP address of the server that is running both MySQL Server and MassTransit Server.
    2. The username and password fields should be populated with the MassTransit MySQL user information that was specified at initial installation time.
  3. Once the MySQL Administrator tool has launched, click on the Restore option which appears in the left-hand window pane.
  4. Click the Open Backup File button and select a backup file.
  5. Check the Create databases if they don’t exist setting checkbox.
  6. Make sure that utf8 is set in the Character Set section.
  7. Select the Restore Content tab and click on the Analyze Backup File Content button.
  8. Expand the selection to make sure all of the tables are selected.
  9. Click on the Start Restore button.

MassTransit 5.1 and 6.0 Database Backup Procedures for Mac OS X

Tuesday, October 3rd, 2006

Summary:

The article describes how to configure and use the MySQL Administrator* application for Macintosh in order to automate the backup of MassTransit Professional, Enterprise, Premier and Standard server Databases.

Description:

The MySQL Administrator application is used to control and configure many aspects of the databases that MassTransit uses for file transfer operations. One of the more powerful and often overlooked features of the administrator is the ability to schedule automated backups of the MassTransit databases. At a specified interval, the Administrator will make complete copies of the MassTransit data, creating a .SQL file containing all database contents. In the event that database recovery is necessary, the contents of this .SQL file may be imported through a simple restoration process via the same Administrator tool.

Database Backup Process:

  1. Launch the MySQL Administrator tool.
  2. A dialog box will appear, prompting you to enter the Server Host, Username, and password.
    1. Server Host is usually localhost, or the IP address of the server that is running both MySQL Server and MassTransit Server.
    2. The username and password fields should be populated with the MassTransit MySQL user information that was specified at initial installation time.
  3. Once the MySQL Administrator tool has launched, click on the Backup option which appears in toolbar.
  4. In the right-hand window pane, three tabs should appear: Project, Advanced Options, and Schedule.
  5. Make sure that the Project tab is selected, and then click the Plus Sign button located underneath the Backup Projects window.
  6. Next, provide the Project Name field with a name for the MassTransit Database backup project. For example, MT_Backup.
  7. Then, under the Schemas column, select mtdatabase, which is your MassTransit data schema, followed by the > button.
  8. The mtdatabase schema should now appear to the left, within the Backup Content window.  Click to create a check mark on the little box next to the disk icon.  *** If this is not done, the database tables will not be selected and your backup will not contain your data.
  9. Next, select the Advanced Options tab to the right of the Project tab.
  10. Select the Lock All Tables and Backup Selected Schema Completely options if they are not already selected.
  11. Within the Output File Options window, make sure that SQL File is selected in the Backup Type pulldown menu.
  12. Now, select the Schedule tab, next to the Advanced Options tab.
  13. Click the Do not schedule backup pulldown menu to schedule your backup.  Choose to Execute this profile daily, Execute this profile weekly or Execute this profile monthly.
    1. If you choose daily, a box will appear to type in the desired time.
    2. If you choose weekly, click the day of the week you wish to execute your backup and enter the time in 24 hour military format.
    3. If you choose monthly, enter the day(s) you want your backup to execute, and then the time in 24 hour military format.  NOTE: Group Logic recommends greater frequency than just once monthly, as database updates may miss capture.
  14. Within the Target Directory field, select a directory in which you want your backup .SQL files are stored. NOTE: It is highly recommended that the location specified here be a drive or volume other than the volume that which contains your “live” MySQL data and MassTransit installation. This is to insure that backup data is not lost in the event of storage hardware failure.
  15. Within the File Name Prefix field, provide a name (i.e. MTBackup) for your database backup files.
  16. Under the Connection Name pulldown, leave the default.  This parameter is gleaned from your defined MassTransit connection information.  You should not need to change this.
  17. Lastly, click the Schedule button to schedule your automated backup.  For those familiar with the Unix cron utility, this will appear in the logged in user’s crontab.
  18. Click the Start Backup button to ensure your backup project is configured correctly.  Check to make certain the backup files are placed where you configured them in item # 14.  If the initial backup runs without errors, you have now successfully configured your MassTransit database backup.
  19. After successful configuration, the backup project that you have just created should appear under the Backup Projects column, in the left window pane.

Database Restoration Process:

  1. Launch the MySQL Administrator tool.
  2. A dialog box will appear, prompting you to enter the Server Host, Username, and password.
    1. Server Host is usually localhost, or the IP address of the server that is running both MySQL Server and MassTransit Server.
    2. The username and password fields should be populated with the MassTransit MySQL user information that was specified at initial installation time.
  3. Once the MySQL Administrator tool has launched, click on the Restore option which appears in the toolbar.
  4. Click the Choose File… button and select a backup file.
  5. Make sure that utf8 is set in the Character Set pulldown menu and click on the Open button.
  6. Check the Create schemas if necessary checkbox.
  7. Select the Selection and expand the selection to make sure all of the tables are selected.
  8. Click on the Start Restore button.

* If you had not installed the MySQL Administrator tool during the initial MassTransit and MySQL installation, you may obtain the 5.1 version from the www.mysql.com website.