Want to change the mysqld port from 3306 to any other port

MySQL & MariaDB Percona XtraDB Cluster 5.x
,
1

Please help

Want to change the mysqld port from 3306 to any other port

And successfully start the XtraDB cluster

2 Likes
2

Hi @sghose , thanks for posting!
You can change the port that Percona XtraDB Cluster uses for client connections from 3306 by setting the variable port under the [mysqld] section of your configuration. For example if you wanted to set the port to listen on 3307 you would have:

[mysqld]
port=3307
1 Like
3

Thanks Micheal,

Is this the correct location?
/etc/mysql/percona-xtradb-cluster.conf.d/client.cnf
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

Just for clarification
Steps

  1. Stop the mysql services in all the three nodes
  2. make the changes in all the nodes
  3. start the services in all the three nodes
    Or Bootstrap nodes
    Please suggest if the steps are incorrect for changing the port
1 Like
4

That is not correct. [client] referrers to the client application. In order to change mysql server you need to use the ‘[mysqld]’ section exactly as Michael said above. Since you are changing the default port for mysql, you will need to change the [client] section to match as well, otherwise you will keep getting errors about inability to connect to mysql.

  1. stop mysql on all three nodes
  2. make [mysqld] and [client] changes
  3. bootstrap node1
  4. start node2
  5. start node3
1 Like
5

Ubuntu 18.04

I have changed the client.cnf and the my.cnf but bootstraping fails
root@morpheus01-app:/etc/mysql# /etc/init.d/mysql bootstrap-pxc

  • Bootstrapping Percona XtraDB Cluster database server mysqld * The server quit without updating PID file (/var/run/mysqld/mysqld.pid).
    [fail]

location
nano /etc/mysql/my.cnf
[mysqld]
port=3307

pxc_encrypt_cluster_traffic=ON
max_connections = 300
wsrep_provider=/usr/lib/galera4/libgalera_smm.so

wsrep_cluster_name=morpheusdb-cluster
wsrep_cluster_address=gcomm://

for wsrep_cluster_address=gcomm://Enter the IP address of the primary node first then remaining nodes. Separating the ip addresses with commas

wsrep_node_name=morpheus01-app
wsrep_node_address=IP_Address

wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=sstuser:Password
pxc_strict_mode=PERMISSIVE
wsrep_sync_wait=2

skip-log-bin
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2

pxc-encrypt-cluster-traffic=OFF

So I am trying to install Morpheus on the same machine as the XtraDB but need to change the sql port 3306 to something else

If you can help in which location I should change the mysqld and is there any specific syntax?
is it port=3307 or port = 3307

1 Like
6

There is no difference between those two. Spaces don’t matter.

What do your error logs say as the reason for not starting?

1 Like
7

2021-06-23T10:05:57.058554Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-06-23T10:05:57.060225Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.33-36-57) starting as process 3235 …
2021-06-23T10:05:57.063580Z 0 [Warning] You need to use --log-bin to make --log-slave-updates work.
2021-06-23T10:05:57.063599Z 0 [Note] WSREP: Setting wsrep_ready to false
2021-06-23T10:05:57.063609Z 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
2021-06-23T10:05:57.063614Z 0 [Note] WSREP: wsrep_load(): loading provider library ‘/usr/lib/galera4/libgalera_smm.so’
2021-06-23T10:05:57.063647Z 0 [ERROR] WSREP: wsrep_load(): dlopen(): /usr/lib/galera4/libgalera_smm.so: cannot open shared object file: No such file or directory
2021-06-23T10:05:57.063654Z 0 [ERROR] WSREP: wsrep_load() failed to load the provider(‘/usr/lib/galera4/libgalera_smm.so’): Invalid argument (22). Need to abort.
2021-06-23T10:05:57.063658Z 0 [ERROR] Aborting

2021-06-23T10:05:57.063669Z 0 [Note] Binlog end
2021-06-23T10:05:57.063717Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

2021-06-23T13:47:57.370480Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-06-23T13:47:57.372150Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.33-36-57) starting as process 9478 …
2021-06-23T13:47:57.375657Z 0 [Warning] You need to use --log-bin to make --log-slave-updates work.
2021-06-23T13:47:57.375675Z 0 [Note] WSREP: Setting wsrep_ready to false
2021-06-23T13:47:57.375684Z 0 [Note] WSREP: No pre-stored wsrep-start position found. Skipping position initialization.
2021-06-23T13:47:57.375688Z 0 [Note] WSREP: wsrep_load(): loading provider library ‘/usr/lib/galera4/libgalera_smm.so’
2021-06-23T13:47:57.375714Z 0 [ERROR] WSREP: wsrep_load(): dlopen(): /usr/lib/galera4/libgalera_smm.so: cannot open shared object file: No such file or directory
2021-06-23T13:47:57.375720Z 0 [ERROR] WSREP: wsrep_load() failed to load the provider(‘/usr/lib/galera4/libgalera_smm.so’): Invalid argument (22). Need to abort.
2021-06-23T13:47:57.375723Z 0 [ERROR] Aborting

2021-06-23T13:47:57.375733Z 0 [Note] Binlog end
2021-06-23T13:47:57.375772Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

1 Like
8

This is strange. You installed PXC8 via yum/apt? Does this library exist at the noted path? Wait. This is mysql 5.7? You can’t use galera4 with 5.7. You need to remove all of this and install a proper PXC8 or install the correct PXC5.7 packages.

1 Like
9

Thanks Matthew,
I have changed the my.cnf file to galera3 and it bootstraped perfectly and the three cluster are connected successfully with the port for mysqld to 3307

Just for curosity,
if that was the issue how did it worked with the default port…

Thanks a lot for the help

1 Like