Speed and Secure MySQL

 

Unload, Load, Reorg, Query, Mask, and Test

Challenges

 

You may face one or more of these time-consuming issues working with MySQL:

  • Data discovery: profiling, classification, ERDs
  • Unloading and loading large tables
  • Routine utility operations (reorgs)
  • Complex queries
  • Migration or replication
  • Masking sensitive data
  • Generating smart, safe test data

 

Specific performance diagnoses and tuning also take time and may affect other users. Finally, stored SQL procedures may also be programmed inefficiently, require optimization, then still take too long to run.

Solutions

Speed MySQL Unloads

IRI FACT (Fast Extract) to speed MySQL unloads. Use SQL syntax in FACT's CLI or GUI to dump table data to flat files.

Specify SELECT * from the table so you do not encumber the unload with qualifiers like distinct, order by, and group by. But once that data are in the flat file, use the SortCL program in IRI CoSort to de-dupe, sort, join, group (and report on) the extracts far faster ... in parallel, outside the database.

Speed MySQL Loads

IRI CoSort to pre-sort flat files for bulk bcp imports.

Create the clustered index first and pre-sort the input files (on the primary index key) to shorten the create index step. Use the SORTED_DATA option when creating indexes to reflect that CoSort bypassed the slower sort in bcp.

Speed MySQL Reorgs and Queries

Offline Reorg wizard in the IRI Workbench to combine and automate the combination of the above fast extraction, sorting, and re-loading operations for classic (offline) MySQL reorgs, which facilitate the maintenance of your tables in optimal query order.

Externalize SQL Transformations

IRI CoSort to relieve the database of processing overhead. Leverage file system I/O, multi-threading, and the proven data transformation power and consolidation of the CoSort SortCL program.

Migrate and Replicate MySQL Databases

IRI NextForm-Database edition to acquire, re-map, re-format, and build/populate new tables during migrations to and from MySQL. You can also use IRI NextForm or the SortCL program in IRI CoSort to re-map and convert data in MySQL, produce custom reports, copies, and federated views of data.

Mask Data in MySQL Columns

IRI FieldShield to mask sensitive data in MySQL, like personally identifiable information (PII) or protected health information (PHI), FieldShield applies masking, encryption, and other de-identifying functions to one or more columns at a time. Use FieldShield to comply with privacy laws like HIPAA, PCI DSS, FERPA, and GDPR.

Generate MySQL Test Data

IRI RowGen to rapidly populate MySQL operations with safe test data. RowGen uses your data models to automatically generate the test data for an entire database with referential integrity.