MySQL Mode

Method 1: cluster_net_services (Recommended)

Halo 16 supports running multiple compatibility modes simultaneously in the same instance via the cluster_net_services parameter. This method is recommended.

Modify the Configuration File

Edit $PGDATA/postgresql.conf and set the following parameters:

# Enable MySQL mode
cluster_net_services = ':3306:mysql'

# Specify the default tenant database for MySQL mode (the database the mysql client connects to by default)
# Note: This database must have the aux_mysql extension installed in the "Create the Extension" step below,
# otherwise the mysql client will report Unknown database when connecting.
mysql.default_tenant = 'mysql'

Important: The default tenant database specified by mysql.default_tenant, as well as every database that needs to be accessed via the mysql protocol, must have the aux_mysql extension installed inside it by running create extension aux_mysql cascade;. Databases without this extension cannot be accessed by the mysql client. Both mysql.default_tenant and cluster_net_services are startup parameters; you must run pg_ctl restart for changes to take effect.

Restart the Database and Create the Extension

# Restart the database (to apply the parameters above)
pg_ctl restart

# Create the mysql database
psql -c "create database mysql;"

# Create the MySQL extension inside the mysql database (aux_mysql also installs plmyssql)
psql -d mysql -c "create extension aux_mysql cascade;"

Create a MySQL Mode User

psql

-- Set the password encryption method
SET password_encryption='mysql_native_password';

-- Create a MySQL user
CREATE USER mysqltest SUPERUSER PASSWORD '123456';

Connection Test

When connecting with the mysql client, note the following:

• You must use an IP address (-h 127.0.0.1 or a remote IP) over TCP. The MySQL compatibility service listens on the TCP port configured in cluster_net_services (3306 in this example), not on a Unix socket. Using localhost produces the error ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'.
• The option to specify a database is uppercase -D. Lowercase -d is invalid and produces the error mysql: unknown option '-d'.
• The target database must have the aux_mysql extension installed. Databases without the extension cannot be accessed via the mysql protocol and produce the error Unknown database or schema "mysql" does not exist.

There are two ways to specify the database when connecting:

Option 1: user@database (recommended, most universal)

This directly specifies the database to connect to. It is not affected by mysql.default_tenant and can connect to any database that has the aux_mysql extension installed. For day-to-day use, we recommend always using this form, so you don't have to worry about the value of default_tenant.

# Connect to any database with aux_mysql installed; replace dbname with the target database
mysql -h 127.0.0.1 -P 3306 -u 'mysqltest@dbname' -p

Option 2: -D database (default tenant database only)

The lookup scope of -D database is the database pointed to by mysql.default_tenant, so it can only be used to connect to the default tenant database itself. When mysql.default_tenant = '' (no default tenant specified), this form does not work and produces the error auth failed: do not specify or config database for current user; in that case you must use Option 1.

# Connect to the default tenant database mysql, when mysql.default_tenant = 'mysql'
mysql -h 127.0.0.1 -P 3306 -D mysql -u mysqltest -p

Tip: mysql.default_tenant is a startup parameter; changes only take effect after pg_ctl restart. Because the behavior of -D database depends on this parameter, repeatedly changing default_tenant can cause inconsistent connection behavior (for example, a database that connected fine before suddenly reports Unknown database). To avoid confusion, we recommend consistently using Option 1 (user@database) to connect to target databases.

Method 2: database_compat_mode

Halo 14 enables MySQL mode using the database_compat_mode parameter combined with second_listener_on and second_port. This method is still supported in Halo 16, but only one compatibility mode can be set at a time.

Modify the Configuration File

Edit $PGDATA/postgresql.conf and set the following parameters:

# 1. Set the database runtime mode to mysql
database_compat_mode = 'mysql'

# 2. Enable the MySQL second listener service
second_listener_on = true

# 3. Specify the MySQL service listening port; 3306 is recommended
second_port = 3306

Important: About the psql connection port (read carefully)

Under Method 2, after setting database_compat_mode to mysql and restarting, the instance's native port (1921 by default) is switched to the MySQL protocol. At this point, running psql directly (which connects to 1921 by default) produces the error expected authentication request from server or server closed the connection unexpectedly, and you cannot enter the database.

Therefore, all subsequent database operations performed via psql (creating databases, schemas, granting privileges, creating users, etc.) must explicitly specify -p 9001 to connect through this PostgreSQL-protocol port. database_compat_mode, second_listener_on, and second_port are all startup parameters; you must run pg_ctl restart for changes to take effect.

Restart the Database and Create the Extension

# Restart the database (to apply the parameters above)
pg_ctl restart

# Create the database (must use -p 9001 to use the PostgreSQL-protocol port)
psql -p 9001 -c "create database test;"

# Create a schema and grant privileges
psql -p 9001 -d test -c "create schema test;"
psql -p 9001 -d test -c "GRANT ALL PRIVILEGES ON SCHEMA test TO halo1;"

Create a MySQL Mode User

psql -p 9001

-- Set the password encryption method
SET password_encryption='mysql_native_password';

-- Create a user
CREATE USER halo1 WITH PASSWORD 'halo1';

-- Grant privileges
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO halo1;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO halo1;
ALTER USER halo1 LOGIN;
ALTER USER halo1 CREATEDB;
ALTER USER halo1 SUPERUSER;

Connection Test

# Local users do not need a password
mysql -h 127.0.0.1 -P 3306 -u halo1 -D test

# Remote users need a password
mysql -h xxx.xxx.xxx.xxx -P 3306 -u halo1 -D test -p

Note:

• When using MySQL mode, a MySQL database corresponds to a schema. You only need to create a schema under the halo0root database.
• After creating new tables, you must grant privileges to the target user; otherwise, the target user will not be able to access the table.
• With this method, the entire instance can only run one compatibility mode. It cannot enable Oracle, MySQL, and other modes simultaneously.
• To use Oracle, MySQL, and other modes simultaneously in Halo 16, use Method 1 (cluster_net_services).