Skip to main content
Version: 1.0.16

MySQL Mode

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 database for MySQL mode
mysql.default_tenant = 'halo0root'

Restart the Database and Create the Extension​

# Restart the database
pg_ctl restart

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

# Create the MySQL extension
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​

# If mysql.default_tenant = 'mysql' is set
mysql -h xxx.xxx.xxx.xxx -d xxx -u mysqltest -p

# If mysql.default_tenant = ''
mysql -h xxx.xxx.xxx.xxx -d xxx -u mysqltest@dbname -p

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

Restart the Database and Create the Extension​

# Restart the database
pg_ctl restart

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

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

Create a MySQL Mode User​

psql
-- 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).