In this BLOG post I will concentrate on the achievements with data synchronization with SQLyog 7.0 (beta 1 released just before the weekend).

SQLyog 7 provides 2-8 times speed improvement (depending on data) with a ‘mixed-type’ of syc-job (involving both INSERTS, UPDATES and DELETES) as compared to SQLyog 6. A few comparison examples (SQLyog 6, SQLyog 7 and Maatkit) with 4 testcases:

a)
Source Rows: 3950400, Target Rows: 3950400, Inserted Rows: 49599, Updated Rows: 49500, Deleted Rows:49599. Primary Col(INT). InnoDB
SQLyog 6: 1120 sec
SQLyog 7: 267 sec
Maatkit : 530 sec

b)
Source Rows: 48025 , Target Rows: 48775, Inserted Rows: 1225 , Updated Rows:1225, Deleted Rows : 1975. Primary Col(INT), InnoDB.
SQLyog 6: 30 sec
SQLyog 7 : 8 sec
Maatkit : 19 sec

c)
Source Rows:150404 , Target Rows: 152864, Inserted Rows: 12136, Updated Rows: 16236, …

Features:

* New GRID design (’zebra’ Office 2007 style) for easier navigation.
* Multiple selection in GRIDs with SHIFT+CLICK
* Improved Data Synchronisation. Now typically 2-8 times faster than before depending on data. Option to generate a SQL sync script was added.
* New more user friendly design for the Schema Synchronisation (renamed from Structure Synchonisation). Various filter (’hide and ignore’) options were added.

Bug fixes:

* A backup script backing up all databases could fail to restore VIEWs referencing another database than the one where the VIEW was.
* Around 50 other small (mostly GUI-related) fixes for issues reported by users.

Downloads: http://webyog.com/en/downloads.php
Purchase: …

SQLyog 7.0 beta 1 is about to be released very soon (we are ‘polishing’ on details at the moment). This release includes completely new code for Data Synchronization that addresses user requests we have had since Data Synchronization was first introduced with SQLyog.

Probably we did not fully understand the needs of some users before. Users have told us

* It is not always desirable or possible to run the sync directly. Audit and access policies may prevent that. A SQL sync script is required in those situations.

* Also some users have wanted a detailed log for every (INSERT/UPDATE/DELETE) operation - for simple documentation or for comparing - in various ways - sync operations between different executions.

* Some users wanted sync scripts for ‘differential backup’ purposes or for auditing purposes.

* Even some users wanted not necessarily to actually perform any sync at all - they only wanted a …

Bug fixes:

* Structure Sync could miss the concluding quote around column comments. This bug was introduced in 6.5.
* Migration from SQL Server could fail with empty tables.
* Migration (UPDATE and DELETE) triggers could fail with specific schemas on source (afffected were small tables with short column types only - like integers and very short string types). This bug was introduced early in 6.x with the full Unicode support added here.
* Migration could generate non-matching columns-count with specific schemas. This was also introduced early in 6.x
* Message about ?Successful rows? could display twice in Migration (sja.log). Note: This was a cosmetical issue with the message only. Rows were only migrated once!

Downloads: http://webyog.com/en/downloads.php
Purchase: …

• Tom Hanlon of MySQL presents
• monitoring tool basics
  • SHOW FULL PROCESSLIST
  • SHOW GLOBAL STATUS
  • SHOW GLOBAL VARIABLES
• basic tools
  • mysqladmin is provided with the server
    • mysqladmin -i 10 extended status: will repeat the same command every 10 seconds. Pipe through grep "and smoke it" (bad pun, hah hah)
    • -r: show only changed values
  • MySQL Administrator
• cacti
  • rrdtool based network graphing tool
  • uses snmp
  • PHP apache and MySQL based solution
  • MySQL plugins, download and install
  • "poller" gathers data and populates the graphs
  • someone offers …