[bitnami/mysql] How secondary replication works?

[elwood218]

Name and Version

bitnami/mysql 9.4.4

What is the problem this feature will solve?

I thought and hoped that mysql secondary is also automatically synced when there is a primary which had already data. So that there is automatically a dump pulled and then the replication started. But it seems that the setup only works if there is an empty database. This would also prevent an raising of "replicaCount" later which would automatically set up the secondary.

What is the feature you are proposing to solve the problem?

Maybe this could be documented anywhere at least. Also a documentation would be nice how to bring it in sync - because at the moment I cannot even connect to the secondary to put a restore a recent dump from the primary.

But best would be that the secondary automatically gets synced. Maybe doing a mysqldump from the primary automatically if the server is out of sync?

Maybe I am missing something? But maybe also other got that problem because it's not documented well enough?!

What alternatives have you considered?

No response

[elwood218]

The MYSQL_MASTER_HOST, MYSQL_MASTER_ROOT_USER and MYSQL_MASTER_ROOT_PASSWORD parameters are used by the slave to connect to the master. It also takes a dump of the existing data in the master server.

This is what the docker image says. https://github.com/bitnami/containers/tree/main/bitnami/mysql#setting-up-a-replication-cluster

But I cannot see that in the scripts of the Docker image and also not that this is happening.

[javsalgar]

Hi.

Do you have an example docker-compose that triggers the issue? In principle, the replication should take place so we would like to reproduce the issue.

[elwood218]

Hi @javsalgar thank you for the quick response :) What do you mean by example docker-compose? We use your helm chart and I thought at least that the link I posed is the docker image you also use in the helm chart?!

We use helmfile to deploy the chart:

  - name: mysql-{{.Values.environment}}
    namespace: services-{{.Values.environment}}
    chart: bitnami/mysql
    version: 9.4.4
    labels:
      app: mysql
    values:
      - mysql.gotmpl

architecture: replication

auth:
  database: magento
  username: magento
  existingSecret: mysql-magento-credentials

primary:
  persistence:
    enabled: true
    size: 80Gi
    storageClass: ssd-rpd

  configuration: |
    {{- readFile "magento.cnf" | nindent 4 }}

  pdb:
    create: false

  resources:
    requests:
      memory: 18G
      cpu: 600m

secondary:

  replicaCount: 1

  persistence:
    enabled: true
    size: 80Gi
    storageClass: ssd-rpd

  livenessProbe:
    enabled: false
  readinessProbe:
    enabled: false
  startupProbe:
    enabled: false

  pdb:
    create: false

  resources:
    requests:
      memory: 18G
      cpu: 600m

The probes on the secondary are disabled because we tried to manually dump from primary to secondary. I would think that the dump is happen automatically.

Further facts: As I mentioned we had an "standalone" setup (so a setup which already had data) but which I destroyed and re-used the PV for the primary - but now I thought the secondary would automatically dump the data from the primary. As when increase the replicaCount this should also be take place automatically - otherwise when adding secondaries someone needs to take manually a dump everytime.

[elwood218]

PS: where I can find the mysqldump command or how the docker image takes the dump from the primary? Wasn't able to find such a code line.

[elwood218]

We got same problem as stated here: https://github.com/bitnami/bitnami-docker-mysql/issues/98

But this is not really a solution because it means a longer downtime. I don't really understand that because if the secondary would pull a dump from primary it should run a replication correctly.

[elwood218]

Probably the same problems as discussed here: https://github.com/bitnami/bitnami-docker-mariadb/issues/177

[jotamartos]

Hi @elwood218,

This is where replication is configured in the different nodes (if they have not been initialized before)

https://github.com/bitnami/containers/blob/main/bitnami/mysql/8.0/debian-11/rootfs/opt/bitnami/scripts/libmysql.sh#L186

Please note that no mysqldump command or similar is needed for the database to be in sync. MySQL itself takes care of copying the data to the secondary node.

What's your use case? If you are moving from a single-node deployment to a replication one, the database is already initialized so the master node won't be configured to accept replication and the connection will fail. In that case, we suggest you take a backup of the database and launch a new deployment with that customization

https://github.com/bitnami/charts/tree/main/bitnami/mysql#customize-a-new-mysql-instance

Thanks

[elwood218]

Hi @jotamartos ,

These are the logs:

mysql 09:41:59.05
mysql 09:41:59.05 Welcome to the Bitnami mysql container
mysql 09:41:59.06 Subscribe to project updates by watching https://github.com/bitnami/containers
mysql 09:41:59.06 Submit issues and feature requests at https://github.com/bitnami/containers/issues
mysql 09:41:59.06
mysql 09:41:59.06 INFO  ==> ** Starting MySQL setup **
mysql 09:41:59.08 INFO  ==> Validating settings in MYSQL_*/MARIADB_* env vars
mysql 09:41:59.09 INFO  ==> Initializing mysql database
mysql 09:41:59.11 WARN  ==> The mysql configuration file '/opt/bitnami/mysql/conf/my.cnf' is not writable. Configurations based on environment variables will not be applied for this file.
mysql 09:41:59.11 INFO  ==> Installing database
mysql 09:42:06.14 INFO  ==> Starting mysql in background
mysql 09:42:08.17 INFO  ==> Configuring authentication
mysql 09:42:08.19 INFO  ==> Configuring replication in slave node
mysql 09:42:08.29 INFO  ==> Running mysql_upgrade
mysql 09:42:08.30 INFO  ==> Stopping mysql
mysql 09:42:10.32 INFO  ==> Starting mysql in background
find: '/docker-entrypoint-startdb.d/': No such file or directory
mysql 09:42:20.36 INFO  ==> Stopping mysql
mysql 09:42:23.38 INFO  ==> ** MySQL setup finished! **

mysql 09:42:23.47 INFO  ==> ** Starting MySQL **
2022-12-01T09:42:23.790911Z 0 [Warning] [MY-011068] [Server] The syntax 'log_slave_updates' is deprecated and will be removed in a future release. Please use log_replica_updates instead.
2022-12-01T09:42:23.790929Z 0 [Warning] [MY-011069] [Server] The syntax '--master-info-repository' is deprecated and will be removed in a future release.
2022-12-01T09:42:23.790952Z 0 [Warning] [MY-011069] [Server] The syntax '--relay-log-info-repository' is deprecated and will be removed in a future release.
2022-12-01T09:42:23.791100Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2022-12-01T09:42:23.791127Z 0 [System] [MY-010116] [Server] /opt/bitnami/mysql/bin/mysqld (mysqld 8.0.31) starting as process 1
2022-12-01T09:42:23.794418Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2022-12-01T09:42:23.794427Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8mb3_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
2022-12-01T09:42:23.801070Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-12-01T09:42:24.009386Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-12-01T09:42:24.303233Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-12-01T09:42:24.303278Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-12-01T09:42:24.340978Z 5 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2022-12-01T09:42:24.344136Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2022-12-01T09:42:24.344264Z 0 [System] [MY-010931] [Server] /opt/bitnami/mysql/bin/mysqld: ready for connections. Version: '8.0.31'  socket: '/opt/bitnami/mysql/tmp/mysql.sock'  port: 3306  Source distribution.
2022-12-01T09:42:24.347110Z 5 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'replicator@mysql-dev-primary:3306',replication started in log 'FIRST' at position 4
2022-12-01T09:42:24.430258Z 7 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000002, end_log_pos 807; Error 'Unknown database 'magento'' on query. Default database: 'magento'. Query: 'CREATE DEFINER=`magento`@`%` TRIGGER trg_catalog_category_entity_after_insert AFTER INSERT ON catalog_category_entity FOR EACH ROW BEGIN INSERT IGNORE INTO `vsbridge_category_indexer_cl` (`entity_id`) VALUES (NEW.`entity_id`); END', Error_code: MY-001049
2022-12-01T09:42:24.430607Z 6 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000002' position 157

[elwood218]

@jotamartos Is it new to mysql 8 that there is no data needed and mysql can just replicate? Or what you mean by your statement??? Furthermore could you please elaborate launch a new deployment with that customization? With what customization? What should the customization look like that it will work? And yes I was moving from standalone to replication setup.. as I mentioned it already before.

As seeing in the logs Configuring replication in slave node tells me that secondary should have been configured correctly, right?! And the primary is only getting a user configured which I added manually into the database. So what is missing?

[jotamartos]

Hi @elwood218,

Furthermore could you please elaborate launch a new deployment with that customization? With what customization? What should the customization look like that it will work?

The customization will be the database backup. You can use a .sql file to populate the database.

And the primary is only getting a user configured which I added manually into the database. So what is missing?

If MySQL purged the information automatically (https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds), the slave node won't be able to sync the information properly. You can try to manually backup the magento database and restore it in the secondary node and purge the binlog to start syncing the information from that time on.

[elwood218]

Hey @jotamartos ,

thanks for the quick response. :) So you mean a script which is then doing a recent dump. But then the information in the README is quite confusing because it says that the secondary will get a dump from the primary -> It also takes a dump of the existing data in the master server.. But maybe I am the only one who misunderstood that. But that is the reason why secondary needs a dump sometimes because you can't really have all binlogs and default of 30 days are already much. Also if I would increase the replicaCount then I always have to do that manual dump to bring it on a recent level to catch up the newest binlogs.

So I will then try with this initScript which runs a dump. Because after secondary started I wasn't able to login into secondary. - so no manually dump was possible.

I will let you know. Thanks!

[elwood218]

Hello @jotamartos,

for now it is not yet working. Still debugging why. I have put an initdbScript which shall be run on Slave and following logs I got on slave. It not really runs the command and then it stops the mysql. I have set set -x to see what happens.

mysql 16:10:06.29
mysql 16:10:06.29 INFO  ==> ** Starting MySQL setup **
mysql 16:10:06.31 INFO  ==> Validating settings in MYSQL_*/MARIADB_* env vars
mysql 16:10:06.31 INFO  ==> Initializing mysql database
mysql 16:10:06.33 WARN  ==> The mysql configuration file '/opt/bitnami/mysql/conf/my.cnf' is not writable. Configurations based on environment variables will not be applied for this file.
mysql 16:10:06.34 INFO  ==> Installing database
mysql 16:10:13.24 INFO  ==> Starting mysql in background
mysql 16:10:15.26 INFO  ==> Configuring authentication
mysql 16:10:15.28 INFO  ==> Configuring replication in slave node
mysql 16:10:15.39 INFO  ==> Running mysql_upgrade
mysql 16:10:15.39 INFO  ==> Stopping mysql
mysql 16:10:17.42 INFO  ==> Starting mysql in background
mysql 16:10:27.45 INFO  ==> Loading user's custom files from /docker-entrypoint-initdb.d
mysql 16:10:27.45 WARN  ==> Sourcing /docker-entrypoint-initdb.d/initial_dump.sh as it is not executable by the current user, any error may cause initialization to fail
++ [[ -n slave ]]
++ [[ slave = \s\l\a\v\e ]]
++ echo 'Pull recent dump of primary'
++ mysqldump -h mysql-dev-primary -u root -p xxxxxxxx --all-databases
Pull recent dump of primary
+ mysql_stop
+ local -r retries=25
+ local -r sleep_time=5
+ is_mysql_running
+ local pid
++ get_pid_from_file /opt/bitnami/mysql/tmp/mysqld.pid
++ local pid_file=/opt/bitnami/mysql/tmp/mysqld.pid
++ [[ -f /opt/bitnami/mysql/tmp/mysqld.pid ]]
++ [[ -n 194 ]]
++ [[ 194 -gt 0 ]]
++ echo 194
+ pid=194
+ [[ -z 194 ]]
+ is_service_running 194
+ local pid=194
+ kill -0 194
+ info 'Stopping mysql'
+ log '\033[38;5;2mINFO \033[0m ==> Stopping mysql'
++ date '+%T.%2N '
+ stderr_print '\033[38;5;6mmysql \033[38;5;5m16:10:27.46 \033[0m\033[38;5;2mINFO \033[0m ==> Stopping mysql'
+ local bool=false
+ shopt -s nocasematch
+ [[ false = 1 ]]
+ [[ false =~ ^(yes|true)$ ]]
+ printf '%b\n' '\033[38;5;6mmysql \033[38;5;5m16:10:27.46 \033[0m\033[38;5;2mINFO \033[0m ==> Stopping mysql'

This is the script I have configured:

initdbScripts:
  initial_dump.sh: |
    #!/bin/sh
    set -x
    if [[ -n "$DB_REPLICATION_MODE" ]]; then
      if [[ "$DB_REPLICATION_MODE" = "slave" ]]; then
        echo "Pull recent dump of primary"
        mysqldump -h $MYSQL_MASTER_HOST -u $MYSQL_MASTER_ROOT_USER -p $MYSQL_MASTER_ROOT_PASSWORD --all-databases > /bitnami/mysql/test.dmp # | mysql_execute "mysql"
      fi
    fi

[jotamartos]

Hi @elwood218,

To show you an example about how to migrate your databases to a new deployment with replication, I created an "application" database in a standalone deployment. After that, I did back up that database

mysqldump -u root -p application > backup.sql

and created a configMap with that sql file

kubectl create configmap init-db-scripts --from-file=backup.sql

In the new deployment, I configured the architecture as "replication" and configured this parameter initdbScriptsConfigMap: "init-db-scripts". Once the deployment is ready, I confirmed the database was created properly

$ mysql -u root -p -e "select * from application.users;"
+------+----------+
| id   | username |
+------+----------+
|    1 | jota     |
+------+----------+

If you want to configure a custom user/password to access that database, you can add a new .sql file in the configmap so the database is configured properly.

I hope this information helps

[github-actions[bot]]

This Issue has been automatically marked as "stale" because it has not had recent activity (for 15 days). It will be closed if no further activity occurs. Thanks for the feedback.

[github-actions[bot]]

Due to the lack of activity in the last 5 days since it was marked as "stale", we proceed to close this Issue. Do not hesitate to reopen it later if necessary.

[elwood218]

Hello @jotamartos ,

sorry for the late response I have been on vacation and the customer had postponed this topic...

I am not sure if my problem is clear.

It is not about the migration - the migration to the new master/slave setup is clear. Your example is only describing a way where I would ONCE manually do a backup and read all the time from the same file. But if the master changes that would not help future slaves.

The problem I got is that if I would add new slaves then there is no automatic way. I don't want to read from a file within the init-db-script - I want that it will read from the master node.

That means I don't want a sql-script (taken by manual backup) - I need a shell script which is doing the dump from the master and imports into the slave when the slave is initialized the first time.

That mechanism would then also work if I raise "replicaCount" in the future. At least this is my understanding/wish ;)

[elwood218]

I have found the error at least for my script.. When I tested the mysqldump command it was working but the I put it in the config like that -p $PASSWORD but it must be -p$PASSWORD.

But anyway what is if later in a working live database which is replicated and then the replication stops because of anything.. to resolve that it would only be possible to set up a new deployment and import the data again? That cannot be the solution. Why there is no user to import something into the slave while init of pod?

[jotamartos]

Hi @elwood218,

The problem I got is that if I would add new slaves then there is no automatic way. I don't want to read from a file within the init-db-script - I want that it will read from the master node.

The mariadb.initdbScriptsConfigMap configuration is only used in the primary node to recover a previous backup. If you add new slaves, they all should read from the master as it has all the information (the same happens when you deploy the solution the first time, the master gets the information and the slaves copy it after that).

You can get more info about how that parameter is used here

I have found the error at least for my script.. When I tested the mysqldump command it was working but the I put it in the config like that -p $PASSWORD but it must be -p$PASSWORD.

Remember that you can create a configmap with the content of the sql file

kubectl create configmap init-db-scripts --from-file=./bitnami_magento.sql

and then use it during the initialization of the database

helm install ... --set mariadb.architecture=replication,mariadb.secondary.replicaCount=2,mariadb.initdbScriptsConfigMap=init-db-scripts ...

[elwood218]

The mariadb.initdbScriptsConfigMap configuration is only used in the primary node to recover a previous backup. If you add new slaves, they all should read from the master as it has all the information (the same happens when you deploy the solution the first time, the master gets the information and the slaves copy it after that).

@jotamartos For example if I would now have a running master with 1 slave and add later a second slave with replicaCount to 2 should it then be synced without any new import? If you say yes then I am not understanding it ;)

[jotamartos]

That's correct!

I supposed you deployed Magento using a standalone deployment of MariaDB (default configuration) and you now want to move the database to a replication (primary/secondary) architecture. Is that correct?

Please note that you can't directly move from standalone to replication and that's why I mentioned you needed to generate a backup and migrate it to a new deployment where you have the primary/secondary nodes. Once you have that architecture, you can increase the number of secondary nodes and they should read from the master to copy the data. Did you find any issue when adding new secondary nodes?

[elwood218]

No that is not my problem.

The way I have migrated to "replication" is:

• set PV on "Retain"
• Set Helm Chart config to "replication"
• Destroy Helm Chart
• Delete PVC
• Set PV to "Available" (delete claim-ref)
• Install Helm Chart

After that I got the new setup with "replication" but without a slave. Then I set replicaCount from 0 to 1. So what is the difference between setting it from 0 to 1 than later from 1 to 2 ? So why that replica is not getting recent data from master and fails with error in logs which I said at the beginning of this issue?

[jotamartos]

Hi @elwood218,

If you persist the data and use the old volume again in the new deployment, MariaDB won't be configured correctly. That's why I mentioned you need to create the backup of your database, and deploy the solution from scratch and using that backup.

https://github.com/bitnami/containers/blob/main/bitnami/mariadb/10.6/debian-11/rootfs/opt/bitnami/scripts/libmariadb.sh#L284

You can generate the backup and deploy a new Magento/MariaDB database with the replication configuration and with your backup to confirm everything works. Once you confirm the data is correct, you can remove the old deployment and start using that new one.

[elwood218]

Hi @jotamartos ,

now it is working.. I don't know why but I will find out.. Thank you!!