Skip to content

openHalo

MySQL-compatible Postgres 14 branch

OpenHalo is an open-source PostgreSQL kernel that provides MySQL wire-protocol compatibility.

openHalo is based on PostgreSQL 14.18 and provides wire-level compatibility with MySQL 5.7.32-log / 8.0. Pigsty delivers it through pg_mode: mysql and the openhalo package alias.

Pigsty supports OpenHalo deployment on all supported Linux platforms.


Quick Start

Prepare the workspace through Pigsty’s standard installation, then select fork/halo (conf/fork/halo.yml). -c halo and -c fork/halo are equivalent; -c mysql selects the native MySQL pilot conf/demo/mysql.yml:

./configure -c fork/halo # use the OpenHalo template
./deploy.yml            # Install (change passwords in pigsty.yml before production use)

Cluster Config

pg-meta:
  hosts:
    10.10.10.10: { pg_seq: 1, pg_role: primary }
  vars:
    pg_cluster: pg-meta
    pg_users:
      - {name: dbuser_meta ,password: DBUser.Meta   ,pgbouncer: true ,roles: [dbrole_admin]    ,comment: pigsty admin user }
      - {name: dbuser_view ,password: DBUser.Viewer ,pgbouncer: true ,roles: [dbrole_readonly] ,comment: read-only viewer for meta database }
    pg_databases:
      - {name: postgres, extensions: [ aux_mysql ]} # mysql-compatible database
      - {name: meta ,baseline: cmdb.sql ,comment: pigsty meta database ,schemas: [pigsty]}
    pg_hba_rules:
      - {user: dbuser_view , db: all ,addr: infra ,auth: pwd ,title: 'allow grafana dashboard access cmdb from infra nodes'}
    pg_crontab: [ '00 01 * * * /pg/bin/pg-backup full' ] # full backup at 1 AM daily

    # OpenHalo specific settings
    pg_mode: mysql
    pg_version: 14
    pg_packages: [ openhalo, pgsql-common ]

OpenHalo’s aux_mysql extension supplies MySQL compatibility functions and types. Enable it for the postgres database in pg_databases. At runtime, aux_mysql uses PGDATA to locate data-directory resources. With pg_mode: mysql, Pigsty injects Environment="PGDATA={{ pg_data }}" into patroni.service (default /pg/data). Preserve this variable when starting manually or using custom systemd units.


Usage

MySQL connections use the postgres database. A MySQL database maps to a PostgreSQL schema. Thus, use mysql selects the mysql schema in the postgres database.

Use the PostgreSQL usernames and passwords for MySQL access.

Client Access

OpenHalo listens on port 3306 for MySQL wire protocol clients.

The fork/halo template installs the OpenHalo server. Install a compatible MySQL client separately before using the command below.

mysql -h 127.0.0.1 -u dbuser_dba

OpenHalo upstream currently reports that Navicat works normally on this port. IntelliJ DataGrip may fail.


Compatibility Parameters

Pigsty sets database_compat_mode to mysql by default. To adjust compatibility behavior, use settings such as:

mysql.listener_on = true                        # enable MySQL listener; restart required
mysql.port = 3306                               # second_port for MySQL mode; restart required
mysql.halo_mysql_version = '5.7.32-log'         # restart required
mysql.ci_collation = true                       # restart required
mysql.explicit_defaults_for_timestamp = false   # restart required
mysql.auto_rollback_tx_on_error = false         # restart required

Patch Notes

Pigsty packages the OpenHalo kernel from HaloTech-Co-Ltd/openHalo with these adjustments:

  • Restore default database name from halo0root to postgres
  • Remove 1.0. prefix in the default version string, keeping 14.18
  • Adjust default config to enable MySQL compatibility and listen on 3306

Pigsty does not provide warranty coverage for OpenHalo kernel behavior. Contact the upstream vendor about kernel-specific issues.

Warning: This kernel is currently in beta1. Evaluate its risks carefully before production use.


Available Extensions

The table lists selected non-Contrib extensions for reference. Query the target instance to confirm actual availability and versions.

ExtensionVersionDescription
aux_mysql1.5MySQL Supplementary Extension
hstore_plpython2u1.0transform between hstore and plpython2u
hstore_plpythonu1.0transform between hstore and plpythonu
jsonb_plpython2u1.0transform between jsonb and plpython2u
jsonb_plpythonu1.0transform between jsonb and plpythonu
ltree_plpython2u1.0transform between ltree and plpython2u
ltree_plpythonu1.0transform between ltree and plpythonu

Was this page helpful?