How to create new database and user with the chart

MySQL & MariaDB Percona Operator for MySQL
1

Description:

We are exploring GitHub - percona/percona-xtradb-cluster-operator: Percona Operator for MySQL based on Percona XtraDB Cluster to set up mysql cluster. Now I am looking to create an user(s) and empty databases along with the installation. Is there something documented in some blog post/article somewhere that I can follow?

Steps to Reproduce:

Install the cluster, no option to create an empty database or user is there.

Version:

v1.13.0

Logs:

Expected Result:

NA

Actual Result:

NA

Additional Information:

NA

2

Hello @sohamc,
Check out our official documentation on the PXC Operator.

1 Like
3

Hi,

Thank you for replying.

I did look through the documentation but didn’t find any option to create any user or database along with the installation. Do we need to do this outside of the cluster installation?

Thanks,

1 Like
4

Hello @sohamc,
Yes, typically you would log into MySQL and CREATE DATABASE and CREATE USER.

1 Like
5

@sohamc - right now this is not supported in our MySQL Operator. But it is our top pick in the roadmap: [K8SPXC-377] Create MySQL users through operator · Issue #9 · percona/roadmap · GitHub

1 Like
6

Thank you, looking forward to it.

7

Hi @Sergey_Pronin, in Is there a built-in way to get "CREATE DATABASE app;" and app user created? - #3 by Sergey_Pronin you said that this can be easily solved by automation tools. Can you please share couple (any) examples that I can go through and try to implement?

Also is there any ETA on the expected release of [K8SPXC-377] Create MySQL users through operator · Issue #9 · percona/roadmap · GitHub? Not looking for any set in stone date, just an expectation in terms of Q1/Q2 or something like that.

8

@sohamc well, apart from just connecting to the database and executing the “create …” statements, I don’t have anything.
Also there are some teams using liquibase or similar products to create schemas.

As for ETA - Q2/Q3 more likely, unless someone from the community does it earlier.

9

do you have any update on this ? any release ?

10

Hi @Adrien_Barret , it is under development right now. We will include it as a tech preview in the next PXCO release. I hope we can release PXCO 1.16.0 next month. P.S. As soon as we merge PR you can test it (please use not a production cluster) you can test it to provide feedback.

11

Awesome, is it possible for you to update this issue when it will be release?

ty,

Adrien

12

@Adrien_Barret we will update it :slight_smile:

BTW, maybe you can help us to validate the solution. Here is how the spec is going to look like:

spec: 
  users: 
  - name: my-user
    db:
    - test1
    - test2
    hosts:
    - %
    - localhost
    - 111.111.111.111
	passwordSecretRef: 
	  name: my-user-password 
    key: my-user-password-key
  withGrantOption: yes
	grants: 
	- REPLICATION SLAVE
	- ALL

As a result, the Operator will execute the following statements in the database:

CREATE DATABASE IF NOT EXISTS test1;
CREATE DATABASE IF NOT EXISTS test2;
CREATE USER IF NOT EXISTS 'my-user'@'localhost' IDENTIFIED BY 'password1';
CREATE USER IF NOT EXISTS 'my-user'@'111.111.111.111' IDENTIFIED BY 'password1';
CREATE USER IF NOT EXISTS 'my-user'@'%' IDENTIFIED BY 'password1';
GRANT ALL, REPLICATION SLAVE ON test1.*,  test2.* TO 'my-user'@'localhost', 'my-user'@'%', 'my-user'@'111.111.111.111'  WITH GRANT OPTION ;

What do you think?

13

great for me ! :slight_smile:
May be hosts can be all * by default expet if hosts key is refered but it’s a minor thing.
Password can optional and if not secretRef is referet it would be auto-generated to kept it only into kubernetes too

Will wait for the release !