Thank you kiran for the detailed information. For me the replication is fine, its failing only when new install or upgrade of cloudstack, which calls for a many schema changes. I think during install or upgrade we may need to disable percona replication.
Regards Joan On Mon, 26 Feb, 2024, 18:10 Kiran Chavala, <kiran.chav...@shapeblue.com> wrote: > Hi Joan > > You can refer this article > > > https://severalnines.com/blog/how-deploy-high-availability-cloudstackcloudplatform-mariadb-galera-cluster/ > > > I had these in my notes when I tried setting it up percona-xtradb, hope > its useful to you. > > ---- > Install 2 ubuntu nodes for percona-xtradb cluster > > On ubuntu node 1 > > $ sudo apt update > > $ sudo apt install gnupg2 > > $ wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release > -sc)_all.deb > > $ sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb > > $ sudo apt update > > $ sudo apt install percona-server-server-5.7 > > > > cat >>/etc/mysql/my.cnf<<EOF > > [mysqld] > > wsrep_provider=/usr/lib/libgalera_smm.so > wsrep_cluster_name=democluster > wsrep_cluster_address=gcomm:// > wsrep_node_name=ubuntuvm01 > wsrep_node_address=172.42.42.101 > wsrep_sst_method=xtrabackup-v2 > wsrep_sst_auth=repuser:reppassword > pxc_strict_mode=ENFORCING > binlog_format=ROW > default_storage_engine=InnoDB > innodb_autoinc_lock_mode=2 > > EOF > > > > $systemctl start mysql > > login to mysql on node 1 and execute the following commands > > > mysql -uroot -p -e "create user repuser@localhost identified by > 'reppassword'" > mysql -uroot -p -e "grant reload, replication client, process, lock tables > on *.* to repuser@localhost" > mysql -uroot -p -e "flush privileges" > > > > On Ubuntu Node 2 > > > $ sudo apt update > > $ sudo apt install gnupg2 > > $ wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release > -sc)_all.deb > > $ sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb > > $ sudo apt update > > $ sudo apt install percona-server-server-5.7 > > > cat >>/etc/mysql/my.cnf<<EOF > > [mysqld] > > wsrep_provider=/usr/lib/libgalera_smm.so > > wsrep_cluster_name=democluster > > wsrep_cluster_address= gcomm://172.42.42.101,172.42.42.102 > > wsrep_node_name=ubuntuvm02 > > wsrep_node_address=172.42.42.101 > > wsrep_sst_method=xtrabackup-v2 > > wsrep_sst_auth=repuser:reppassword > > pxc_strict_mode=ENFORCING > > binlog_format=ROW > > default_storage_engine=InnoDB > > innodb_autoinc_lock_mode=2 > > EOF > > > > $systemctl start mysql > > > > > Login back to node 1 check the status of the xtradb cluster > > mysql >show status like 'wsrep%'; > > > mysql>use mysql > mysql>GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'password'; > mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' > WITH GRANT OPTION; > mysql>FLUSH PRIVILEGES; > mysql> SELECT host FROM mysql.user WHERE User = 'root'; > mysql>SET GLOBAL pxc_strict_mode=PERMISSIVE > > > > Regards > Kiran > > From: Joan g <joang...@gmail.com> > Date: Saturday, 24 February 2024 at 12:29 AM > To: users@cloudstack.apache.org <users@cloudstack.apache.org> > Subject: Cloudstack DB using 3 Node Galrea Cluster. > Hi Community, > > I need some suggestions on using 3 node Mariadb *Galera Cluster or percona > xtradb* for Cloudstack Databases. > > In My setup the Databases are behind a LB and write happens only to a > single node > > With new Cloudstack 4.18.1 install initial database migration is always > failing because of schema update/sync issues with other DB nodes. > > Logs in Mysql err:: > 2024-02-23T12:55:15.521278Z 17 [ERROR] [MY-010584] [Repl] Replica SQL: > Error 'Duplicate column name 'display'' on query. Default > database: 'cloud'. Query: 'ALTER TABLE cloud.guest_os ADD COLUMN display > tinyint(1) DEFAULT '1' COMMENT 'should this guest_os b > e shown to the end user'', Error_code: MY-001060 > > Due to this Cloudstack initialisation is always failing. > > Can someone point me with a suggested method for DB HA > > Jon > > > >
